MySQL查询优化(分段查询优化索引的使用)

[toc]

实验环境

测试表的创建语句

CREATE TABLE `live_user_join_room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL COMMENT '用户id',
  `room_id` int(11) DEFAULT NULL COMMENT '直播间id',
  `is_out` int(11) NOT NULL DEFAULT '0' COMMENT '0 未退出 1 已退出',
  `time` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '时间',
  `course_id` bigint(20) DEFAULT NULL COMMENT '课程 id',
  `source` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '来源',
  `source_id` int(11) DEFAULT NULL COMMENT '来源ID',
  `created_by` int(11) NOT NULL DEFAULT '0' COMMENT '创建者',
  `updated_by` int(11) NOT NULL DEFAULT '0' COMMENT '更新者',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` timestamp NULL DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`),
  KEY `a` (`uid`,`room_id`,`course_id`),
  KEY `b` (`room_id`,`uid`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=8376813 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='用户加入房间'

IN条件结果数量 1920 条记录

查询索引

SHOW KEYS FROM `test`.`live_user_join_room`; 

优化前的sql, 平均查询用时 8.36s 左右

SELECT uid,COUNT(DISTINCT(room_id)) AS num FROM `live_user_join_room` FORCE INDEX(`created_at`)
WHERE room_id IN (
    SELECT id FROM live_im_rooms WHERE pid=1 AND room_status!=5 AND room_status!=6
    AND course_id NOT IN (SELECT id FROM courses WHERE pid=1 AND `type`=1 AND price=0)
)
AND created_at>'2020-11-07'
GROUP BY uid

优化前的explain的结果

优化后的sql, 平均查询用时 0.572s 左右

SELECT uid,COUNT(DISTINCT(room_id)) AS num FROM `live_user_join_room` USE INDEX(a)
WHERE room_id IN (
    SELECT id FROM live_im_rooms WHERE pid=1 AND room_status!=5 AND room_status!=6
    AND course_id NOT IN (SELECT id FROM courses WHERE pid=1 AND `type`=1 AND price=0)
)
AND id>(SELECT MIN(id) FROM `live_user_join_room` WHERE created_at>'2020-11-07')
GROUP BY uid

explain的结果

差异的原因个人理解为

MySQL 每次查询只能使用一个索引, 优化后的sql虽然扫描行数多, 但是消除了 Using filesort , 解决了慢查询的问题

• NOT IN 目标查询包含null的问题

select id from aa where id not in (select union_key from bb)
如果id为bb中的union_key不存在的值, 那么就应该可以正常数据, 但是, 但是
如果union_key包含null, 则这个查询永远没有结果了

• 视图关联的账户如果需要禁用数据库的权限, 可能需要下面的语句修改视图关联的账户, 注意: 阿里云的RDS需要修改为合适的且有所有数据库读写权限的账户,因为视图归属需要对应的视图写权限和查询的读取权限

SELECT TABLE_SCHEMA AS '库',TABLE_NAME AS '视图名',DEFINER AS '所属账户',CONCAT("alter DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") AS '修复语句' FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA!='sys' AND TABLE_SCHEMA!='mysql' AND DEFINER!='root@%';

数据表前置条件

• project_users

• user_tag_types

• 编写sql

SELECT xx.uid,tag_ids,GROUP_CONCAT(tag_title) AS 'user_tag_names' FROM 
(
SELECT uid,tag_ids,SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(REPLACE(t1.tag_ids,' ',''),'[',''),']',''),'"',''),',',t2.help_topic_id + 1),',',-1) AS tag_id
FROM user_center.`project_users` t1 JOIN mysql.help_topic t2
WHERE t2.help_topic_id< (LENGTH(t1.tag_ids) - LENGTH(REPLACE(t1.tag_ids,',','')) + 1)
) xx
INNER JOIN (SELECT id,IF(memo='',`name`,memo) AS tag_title FROM user_center.`user_tag_types` ) ut ON ut.id=xx.tag_id
GROUP BY xx.uid

• 执行结果

• sql语句

SELECT * FROM (
SELECT 日期, 人数, (@csum := @csum + 人数) AS 累计人数
FROM (
SELECT DATE_FORMAT(complete_at,'%Y-%m-%d') AS '日期',COUNT(1) AS '人数' FROM `course_order_items` 
WHERE pid=1 AND state=1 AND course_id=235 GROUP BY 日期 ORDER BY 日期 ASC)daily_pnl_view,(SELECT @csum:=0) AS it
) xx
ORDER BY 日期 DESC

• 返回结果

日期      人数  累计人数  
----------  ------  --------------
2020-06-26      16              42
2020-06-25      21              26
2020-06-24       5               5

• SQLSTATE[HY000] [2002] Host is unreachable