记录一次从小白开始的mysql调优（一）

已于 2022-10-24 15:04:58 修改
阅读量192 收藏
点赞数
文章标签： mysql sql 数据库 1024程序员节
于 2022-10-11 10:57:54 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cjy1575944940/article/details/127257394
版权

由于所负责的项目数据量不太大，之前较少涉及到mysql调优

首先遇到了一个例子，领导给了一条sql过来要求优化，这个还是比较简单的

一、去掉多余的字段以及函数

select count(1) from (select 'abilityCert' as refType, src.ID as refId, src.NAME as name, urdp.DEPT_ID as deptId, src.USER_ID as userId, src.ATTACHMENTS as content, src.CREATE_TIME as createTime
         
          ,(select json_extract(state.STATE_INFO, '$.countView') from itlms_stat_state state where 'abilityCert' = state.REF_TYPE and src.id = state.REF_ID )countView
         
        from itlms_pinfo_material src
        join ipanther_user_dept urdp on urdp.IS_DELETED = 'N' and src.USER_ID = urdp.USER_ID
         
          
        where src.IS_DELETED = 'N' and src.ATTACHMENTS>'' and src.type ='1' 
         
            and not exists(select 1 from itlms_promote_reply where IS_DELETED = 'N' and CODE ='abilityCert' and REF_ID = src.id and USER_ID = 'yj_18039') limit 1000
                                                
                                                ) tmp_count;

查询的速度171ms
请添加图片描述

这里由于有许多多余的字段，于是就把他去掉了，另外exists函数会导致非常慢，所以用了not in 来代替
修改后如下

select
        count(1)
from
        (
        select
                1
        from
                itlms_pinfo_material src
        join ipanther_user_dept urdp on
                urdp.IS_DELETED = 'N'
                and src.USER_ID = urdp.USER_ID
        where
                src.IS_DELETED = 'N'
                and src.ATTACHMENTS>''
                and src.type = '1'
                and src.id not in (
                        select ref_id from itlms_promote_reply
                        where is_deleted='N'
                        and CODE = 'abilityCert'
                        and USER_ID = 'yj_18039'
                )
        limit 1000
                                                
                                                ) tmp_count;

修改后的查询速度
请添加图片描述

后面查得知，in还是有局限性的，只能限定1000条数据。所以遇到大数据量还需要别的办法

二、回表导致的索引失效问题

然后接着收到了第二条需要优化的sql
一开始看到的时候还是挺懵的。执行整整执行了36秒
先上sql

SELECT
        count( 1 ) 
FROM
        (
        SELECT
                t.* 
        FROM
                (
                SELECT
                        register.id,
                        'face' CODE,
                        register.user_id,
                        iud.dept_id,
                        IU.REAL_NAME,
                        IUT.TEACHER_NO,
                        IU.MOBILE_PHONE,
                        TRAIN.NAME TRAIN_NAME,
                        train.HOURS,
                        train.TRAIN_STAGE AS TRAIN_TRAIN_STAGE,
                        train.SUBJECT_CATEGORY,
                        TRAIN.TEACH_ADDRESS,
                        TRAIN.COURSE_TYPE,
                        REGISTER.AUDIT_RESULT,
                        REGISTER.AUDIT_STATUS,
                        REGISTER.AUDIT_REMARK,
                        classroom.TEACH_DATE AS class_date,
                        classroom.TEACH_TIME AS class_time,
                        '02' AS is_replenish 
                FROM
                        itlms_train_face_register register
                        JOIN ipanther_user_teacher iut ON register.user_id = iut.user_id 
                        AND iut.is_deleted = 'N'
                        JOIN ipanther_user iu ON iut.user_id = iu.id 
                        AND iu.is_deleted = 'N'
                        JOIN ipanther_user_dept iud ON iu.id = iud.user_id 
                        AND iud.is_deleted = 'N'
                        JOIN ipanther_department ide ON iud.dept_id = ide.id 
                        AND ide.is_deleted = 'N'
                        JOIN itlms_train_face_train train ON register.train_id = train.id 
                        AND train.is_deleted = 'N'
                        LEFT JOIN itlms_train_face_classroom classroom ON classroom.ID = register.CLASSROOM_ID 
                        AND classroom.is_deleted = 'N' 
                WHERE
                        register.is_deleted = 'N' 
                        AND train.YEAR = '8a855a311b3b4e8f85c858188ce0469b' 
                        AND train.term = 'ffd3f8bb2eb849abb2fb536f91a5dd8c' 
                        AND ((
                                        register.audit_status = '02' 
                                        AND register.dept_id = 'yj_1274' 
                                        ) 
                        OR ( register.audit_status = '01' AND ide.id = 'yj_1274' )) 
                        AND register.audit_status = '01' 
                        AND ( register.audit_result IS NULL OR register.audit_result = '' ) UNION ALL
                SELECT
                        register.id,
                        'online' CODE,
                        register.user_id,
                        iud.dept_id,
                        IU.REAL_NAME,
                        IUT.TEACHER_NO,
                        IU.MOBILE_PHONE,
                        TRAIN.NAME TRAIN_NAME,
                        train.HOURS,
                        train.TRAIN_STAGE AS TRAIN_TRAIN_STAGE,
                        train.SUBJECT_CATEGORY,
                        '' TEACH_ADDRESS,
                        TRAIN.COURSE_TYPE,
                        REGISTER.AUDIT_RESULT,
                        REGISTER.AUDIT_STATUS,
                        REGISTER.AUDIT_REMARK,
                        classroom.teach_time_start AS class_date,
                        classroom.teach_time_end AS class_time,
                        train.is_replenish AS is_replenish 
                FROM
                        itlms_train_online_register register
                        JOIN ipanther_user_teacher iut ON register.user_id = iut.user_id 
                        AND iut.is_deleted = 'N'
                        JOIN ipanther_user iu ON iut.user_id = iu.id 
                        AND iu.is_deleted = 'N'
                        JOIN ipanther_user_dept iud ON iu.id = iud.user_id 
                        AND iud.is_deleted = 'N'
                        JOIN ipanther_department ide ON iud.dept_id = ide.id 
                        AND ide.is_deleted = 'N'
                        JOIN itlms_train_online_train train ON register.train_id = train.id 
                        AND train.is_deleted = 'N'
                        LEFT JOIN itlms_train_online_classroom classroom ON register.classroom_id = classroom.id 
                        AND classroom.is_deleted = 'N' 
                WHERE
                        register.is_deleted = 'N' 
                        AND train.YEAR = '8a855a311b3b4e8f85c858188ce0469b' 
                        AND train.term = 'ffd3f8bb2eb849abb2fb536f91a5dd8c' 
                        AND ((
                                        register.audit_status = '02' 
                                        AND register.dept_id = 'yj_1274' 
                                        ) 
                        OR ( register.audit_status = '01' AND ide.id = 'yj_1274' )) 
                        AND register.audit_status = '01' 
                        AND ( register.audit_result IS NULL OR register.audit_result = '' ) 
                ) t 
        ORDER BY
                t.is_replenish,
                t.audit_status,
        t.user_id 
        ) t;

可以看到，即union all 了数据，每个union的表里都join了很多个表，那么该怎么解决呢？
首先由于使用了union all 不好知道是哪部分出了问题，于是考虑每个union all的数据单独执行一遍，于是把union all里的sql拆成两部分单独执行，发现了问题出在下部分，这里直接上下部分的

 SELECT
                        register.id,
                        'online' CODE,
                        register.user_id,
                        iud.dept_id,
                        IU.REAL_NAME,
                        IUT.TEACHER_NO,
                        IU.MOBILE_PHONE,
                        TRAIN.NAME TRAIN_NAME,
                        train.HOURS,
                        train.TRAIN_STAGE AS TRAIN_TRAIN_STAGE,
                        train.SUBJECT_CATEGORY,
                        '' TEACH_ADDRESS,
                        TRAIN.COURSE_TYPE,
                        REGISTER.AUDIT_RESULT,
                        REGISTER.AUDIT_STATUS,
                        REGISTER.AUDIT_REMARK,
                        classroom.teach_time_start AS class_date,
                        classroom.teach_time_end AS class_time,
                        train.is_replenish AS is_replenish 
                FROM
                        itlms_train_online_register register
                        JOIN ipanther_user_teacher iut ON register.user_id = iut.user_id 
                        AND iut.is_deleted = 'N'
                        JOIN ipanther_user iu ON iut.user_id = iu.id 
                        AND iu.is_deleted = 'N'
                        JOIN ipanther_user_dept iud ON iu.id = iud.user_id 
                        AND iud.is_deleted = 'N'
                        JOIN ipanther_department ide ON iud.dept_id = ide.id 
                        AND ide.is_deleted = 'N'
                        JOIN itlms_train_online_train train ON register.train_id = train.id 
                        AND train.is_deleted = 'N'
                        LEFT JOIN itlms_train_online_classroom classroom ON register.classroom_id = classroom.id 
                        AND classroom.is_deleted = 'N' 
                WHERE
                        register.is_deleted = 'N' 
                        AND train.YEAR = '8a855a311b3b4e8f85c858188ce0469b' 
                        AND train.term = 'ffd3f8bb2eb849abb2fb536f91a5dd8c' 
                        AND ((
                                        register.audit_status = '02' 
                                        AND register.dept_id = 'yj_1274' 
                                        ) 
                        OR ( register.audit_status = '01' AND ide.id = 'yj_1274' )) 
                        AND register.audit_status = '01' 
                        AND ( register.audit_result IS NULL OR register.audit_result = '' )

就是这条sql执行的非常慢，30多秒的时间基本都在这里耗尽了，为什么呢？
通过领导得知，此处由于from 表里的itlms_train_online_register 数据量很庞大，有1个多g，个人查了下有百万条数据了
请添加图片描述
然后走了很多弯路，先把where 里的join表查询条件给扔回join里面去（网上看到一种说法：最后过滤的数据量会更小）

-- 修改前
JOIN itlms_train_online_train train ON register.train_id = train.id 
AND train.is_deleted = 'N'
                        LEFT JOIN itlms_train_online_classroom classroom ON ...
-- 修改后
  JOIN itlms_train_online_train train ON register.train_id = train.id 
                        AND train.is_deleted = 'N'
                        AND train.YEAR = '8a855a311b3b4e8f85c858188ce0469b' 
                        AND train.term = 'ffd3f8bb2eb849abb2fb536f91a5dd8c'

没发现执行效率上有显著差别

然后将每个join表分别去掉，看看会不会发现什么，结果还真发现了问题
还是这个表，把以下这个join表去掉后，效率有明显提升

-- 去掉的
 JOIN itlms_train_online_train train ON register.train_id = train.id 
                        AND train.is_deleted = 'N'

-- 修改后
SELECT
		            register.id,
		            'online' CODE,
		            register.user_id,
		            iud.dept_id,
		            IU.REAL_NAME,
		            IUT.TEACHER_NO,
		            IU.MOBILE_PHONE,
		--             TRAIN.NAME TRAIN_NAME,
		--             train.HOURS,
		--             train.TRAIN_STAGE AS TRAIN_TRAIN_STAGE,
		--             train.SUBJECT_CATEGORY,
		            
		            null as train_name,
		            null as hours,
		            null as TRAIN_TRAIN_STAGE,
		            null as SUBJECT_CATEGORY,
		            '' TEACH_ADDRESS,
		--             TRAIN.COURSE_TYPE,
		            null as COURSE_TYPE,
		            REGISTER.AUDIT_RESULT,
		            REGISTER.AUDIT_STATUS,
		            REGISTER.AUDIT_REMARK,
		            classroom.teach_time_start AS class_date,
		            classroom.teach_time_end AS class_time,
		            null as is_replenish
		--             train.is_replenish AS is_replenish 
		    FROM
		            itlms_train_online_register register
		            JOIN ipanther_user_teacher iut ON register.user_id = iut.user_id 
		            AND iut.is_deleted = 'N'
		            JOIN ipanther_user iu ON iut.user_id = iu.id 
		            AND iu.is_deleted = 'N'
		            JOIN ipanther_user_dept iud ON iu.id = iud.user_id 
		            AND iud.is_deleted = 'N'
		            JOIN ipanther_department ide ON iud.dept_id = ide.id 
		            AND ide.is_deleted = 'N'
		--             JOIN itlms_train_online_train train ON train.id  =register.train_id  
		--             AND train.is_deleted = "N"
		--             and train.YEAR = '8a855a311b3b4e8f85c858188ce0469b' 
		--             and train.term = 'ffd3f8bb2eb849abb2fb536f91a5dd8c' 
		            LEFT JOIN itlms_train_online_classroom classroom ON register.classroom_id = classroom.id 
		            AND classroom.is_deleted = 'N' 
		    WHERE
		            register.is_deleted = 'N' 
		            AND register.YEAR = '8a855a311b3b4e8f85c858188ce0469b' 
		            AND register.term = 'ffd3f8bb2eb849abb2fb536f91a5dd8c' 
		            AND ((
		                            register.audit_status = '02' 
		                            AND register.dept_id = 'yj_1274' 
		                            ) 
		            OR ( register.audit_status = '01' AND ide.id = 'yj_1274' )) 
		            AND register.audit_status = '01' 
		            AND ( register.audit_result IS NULL OR register.audit_result = '' )

这是为什么捏？
于是开始执行explain查看原因
这是去掉join之前的
注意这两个表，查了下type，possibleKey，key和keyLen所表示的意思，具体不写了百度很多地方查得到
其中留意到possibleKey和key，一个指的可能使用到的索引，一个指的实际使用的索引
请添加图片描述
保留itlms_train_online_train 发现register表仅仅使用了一个索引

把join itlms_train_online_train去掉，发现实际使用的索引变多了
请添加图片描述
这是为什么呢？
再看下两者后边的字段
去掉join前，而且指的留意的是，使用了join后，ref会变另一个表的id，此时触发回表
请添加图片描述
去掉join后
请添加图片描述
在extra中看到了明显的不同，其中加入了join表之后，就会变成using where，进行where过滤了。

由于二级索引连的是另一个表的主键，所以触发了回表
mysql引擎在执行sql前，会自己计算一套算法，去选择使用索引或者不使用索引
在回表数据量大的时候，可能会出现mysql觉得回表代价过大而乱用索引了
所以索引失效了

修改，使用关键字force index()强制使用索引

   SELECT
                        register.id,
                        'online' CODE,
                        register.user_id,
                        iud.dept_id,
                        IU.REAL_NAME,
                        IUT.TEACHER_NO,
                        IU.MOBILE_PHONE,
                        TRAIN.NAME TRAIN_NAME,
                        train.HOURS,
                        train.TRAIN_STAGE AS TRAIN_TRAIN_STAGE,
                        train.SUBJECT_CATEGORY,
                        '' TEACH_ADDRESS,
                        TRAIN.COURSE_TYPE,
                        REGISTER.AUDIT_RESULT,
                        REGISTER.AUDIT_STATUS,
                        REGISTER.AUDIT_REMARK,
                        classroom.teach_time_start AS class_date,
                        classroom.teach_time_end AS class_time,
                        train.is_replenish AS is_replenish 
                FROM
                        itlms_train_online_register register
                        force index(IDX_itlms_train_online_register_AUDIT_STATUS)
			             force index(IDX_itlms_train_online_register_TERM)
			              force index(IDX_itlms_train_online_register_YEAR)
			               force index(IDX_itlms_train_online_register_IS_DELETED)
                        JOIN ipanther_user_teacher iut ON register.user_id = iut.user_id 
                        AND iut.is_deleted = 'N'
                        JOIN ipanther_user iu ON iut.user_id = iu.id 
                        AND iu.is_deleted = 'N'
                        JOIN ipanther_user_dept iud ON iu.id = iud.user_id 
                        AND iud.is_deleted = 'N'
                        JOIN ipanther_department ide ON iud.dept_id = ide.id 
                        AND ide.is_deleted = 'N'
                        JOIN itlms_train_online_train train ON register.train_id = train.id 
                        AND train.is_deleted = 'N'
                        AND train.YEAR = '8a855a311b3b4e8f85c858188ce0469b' 
                        AND train.term = 'ffd3f8bb2eb849abb2fb536f91a5dd8c' 
                        LEFT JOIN itlms_train_online_classroom classroom ON register.classroom_id = classroom.id 
                        AND classroom.is_deleted = 'N' 
                WHERE
                        register.is_deleted = 'N' 
                        AND ((
                                        register.audit_status = '02' 
                                        AND register.dept_id = 'yj_1274' 
                                        ) 
                        OR ( register.audit_status = '01' AND ide.id = 'yj_1274' )) 
                        AND register.audit_status = '01' 
                        AND ( register.audit_result IS NULL OR register.audit_result = '' )

速度变为了1.89秒
在这里插入图片描述
成功优化

三、子函数做字段，留意其查询条件

先上原来的sql

 select
	t.*,
	(t.classroom_teacher_num-t.register_valid_count) rest_count
from
	(
	select
		train.id,
		train.regist_time_start regist_time_start,
		train.regist_time_end regist_time_end,
		train.course_id course_id,
		train.resource_id resource_id,
		train.train_ids train_ids,
		classroom.id classroom_id,
		classroom.is_add is_add,
		train.name name,
		train.course_type course_type,
		train.teacher_name teacher_name,
		train.teacher_link_phone teacher_link_phone,
		iu.real_name incharge_teacher_name,
		iu.office_phone incharge_teacher_link_phone,
		classroom.submit_work_time_end submit_work_time_end,
		classroom.teach_time_start teach_time_start,
		classroom.teach_time_end teach_time_end,
		train.classroom_teacher_num classroom_teacher_num,
		train.teach_subjects teach_subjects,
		train.subject_category subject_category,
		train.hours hours,
		train.is_replenish is_replenish,
		regist.id register_id,
		regist.audit_status register_audit_status,
		regist.audit_result register_audit_result,
		/*(select count(1) from itlms_train_online_register re where re.train_id = train.id and re.classroom_id = classroom.id AND re.is_deleted='N') register_count,*/
		(
		select
			count(1)
		from
			itlms_train_online_register reg
		where
			reg.train_id = train.id
			and reg.classroom_id = classroom.id
			and reg.is_deleted = 'N'
			and (reg.audit_result is null
				or reg.audit_result != '02')) register_valid_count,
		classroom.status classroom_status,
		train.term term,
		train.IS_TOP IS_TOP,
		train.train_stage,
		(
		select
			count(1)
		from
			itlms_score_temp1 sc
		where
			sc.resource_id = train.resource_id
			and sc.score > 0
			and sc.code = 'online'
			and sc.user_id = '20751CDF73D60A5CE050A8C0030145CA' ) year_register_count,
		(
		select
			resource.RESOURCE_QUALITY
		from
			itlms_online_resource resource
		where
			resource.is_deleted = 'N'
			and resource .id = train.resource_id) RESOURCE_QUALITY
	from
		itlms_train_online_train train
	join itlms_train_online_train_classroom tc on
		train.id = tc.train_id
	join itlms_train_online_classroom classroom on
		classroom.id = tc.classroom_id
		and classroom.is_deleted = 'N'
	left join ipanther_user iu on
		train.incharge_teacher_id = iu.id
		and iu.is_deleted = 'N'
	left join itlms_train_online_register regist on
		train.id = train.id
		and regist.classroom_id = classroom.id
		and regist.is_deleted = 'N'
		and regist.user_id = '20751CDF73D60A5CE050A8C0030145CA'
	where
		train.is_deleted = 'N'
		and train.audit_result = '01'
		and train.status = '01'
		and train.is_resource = '02'
		and train.year = '8a855a311b3b4e8f85c858188ce0469b'
		and (train.train_stage in ('X', 'Z', 'G', 'Q', 'ZX' , 'PR', 'JM', 'SM', 'VH', 'PR-JM-SM', 'COMMON', 'TK', 'SSZT')
			or train.train_stage is null
			or train.train_stage = '') ) t
order by
	t.term = 'ffd3f8bb2eb849abb2fb536f91a5dd8c' desc,
	t.is_top desc,
	t.course_type = 'SP' desc,
	t.is_replenish asc,
	rest_count desc,
	t.RESOURCE_QUALITY = '01' desc

执行下时间，发现是1.4秒
看到这条sql，就能想到很可能是子查询register_valid_count或者是year_register_count两者之一出现了问题，由于两者都是在列上进行子查询的，也就是from itlms_train_online_train 加上若干个join之后得到的一条数据，都会子查询一次register_valid_count和子查询一次year_register_count
一条记录就查一次子查询。很可能是两个之中的一个出现了问题。

将register_valid_count注释掉后发现速度明显的变快了，所以判断是register_valid_count的问题。
然后发现后面已经inner join itlms_train_online_classroom 表了，而register_valid_count还有

where
			reg.train_id = train.id
			and reg.classroom_id = classroom.id

这里有一次重复，吧其中的class_room_id给注释掉。发现变快了

		(
		select
			count(*)
		from
			itlms_train_online_register reg 
		where
			reg.train_id = train.id
			-- and reg.classroom_id = classroom.id
			and reg.is_deleted = 'N'
 			and (reg.audit_result is null
 				or reg.audit_result != '02')
				) register_valid_count,

这里讲一个题外话，mysql官方推荐使用count(星*)作为count的功能，虽然count(1)和count(星*)功能是一样的，但是官方写法推荐为count(星*)。
count(星*)会包括null元素而count(列）不会包含null元素
所以此处先把classroom_id给去掉，完成初步优化。此时快了一点但是不明显，接下来到进一步优化了

 select
	t.*,
	(t.classroom_teacher_num-t.register_valid_count) rest_count
from
	(
	select
		train.id,
		train.regist_time_start regist_time_start,
		train.regist_time_end regist_time_end,
		train.course_id course_id,
		train.resource_id resource_id,
		train.train_ids train_ids,
		classroom.id classroom_id,
		classroom.is_add is_add,
		train.name name,
		train.course_type course_type,
		train.teacher_name teacher_name,
		train.teacher_link_phone teacher_link_phone,
		iu.real_name incharge_teacher_name,
		iu.office_phone incharge_teacher_link_phone,
		classroom.submit_work_time_end submit_work_time_end,
		classroom.teach_time_start teach_time_start,
		classroom.teach_time_end teach_time_end,
		train.classroom_teacher_num classroom_teacher_num,
		train.teach_subjects teach_subjects,
		train.subject_category subject_category,
		train.hours hours,
		train.is_replenish is_replenish,
		regist.id register_id,
		regist.audit_status register_audit_status,
		regist.audit_result register_audit_result,
		/*(select count(1) from itlms_train_online_register re where re.train_id = train.id and re.classroom_id = classroom.id AND re.is_deleted='N') register_count,*/
		(
		select
			count(1)
		from
			itlms_train_online_register reg
		where
			reg.train_id = train.id
			-- 注释此处
			-- and reg.classroom_id = classroom.id
			and reg.is_deleted = 'N'
			and (reg.audit_result is null
				or reg.audit_result != '02')) register_valid_count,
		classroom.status classroom_status,
		train.term term,
		train.IS_TOP IS_TOP,
		train.train_stage,
		(
		select
			count(1)
		from
			itlms_score_temp1 sc
		where
			sc.resource_id = train.resource_id
			and sc.score > 0
			and sc.code = 'online'
			and sc.user_id = '20751CDF73D60A5CE050A8C0030145CA' ) year_register_count,
		(
		select
			resource.RESOURCE_QUALITY
		from
			itlms_online_resource resource
		where
			resource.is_deleted = 'N'
			and resource .id = train.resource_id) RESOURCE_QUALITY
	from
		itlms_train_online_train train
	join itlms_train_online_train_classroom tc on
		train.id = tc.train_id
	join itlms_train_online_classroom classroom on
		classroom.id = tc.classroom_id
		and classroom.is_deleted = 'N'
	left join ipanther_user iu on
		train.incharge_teacher_id = iu.id
		and iu.is_deleted = 'N'
	left join itlms_train_online_register regist on
		train.id = train.id
		and regist.classroom_id = classroom.id
		and regist.is_deleted = 'N'
		and regist.user_id = '20751CDF73D60A5CE050A8C0030145CA'
	where
		train.is_deleted = 'N'
		and train.audit_result = '01'
		and train.status = '01'
		and train.is_resource = '02'
		and train.year = '8a855a311b3b4e8f85c858188ce0469b'
		and (train.train_stage in ('X', 'Z', 'G', 'Q', 'ZX' , 'PR', 'JM', 'SM', 'VH', 'PR-JM-SM', 'COMMON', 'TK', 'SSZT')
			or train.train_stage is null
			or train.train_stage = '') ) t
order by
	t.term = 'ffd3f8bb2eb849abb2fb536f91a5dd8c' desc,
	t.is_top desc,
	t.course_type = 'SP' desc,
	t.is_replenish asc,
	rest_count desc,
	t.RESOURCE_QUALITY = '01' desc

走了很多弯路后，得出了进一步优化结果，使用联合索引

四、使用联合索引

为什么注释掉classroom_id会更快呢？
用explain关键字对比下两者，发现其中加了classroom_id之后的explain触发了回表
图1-0
请添加图片描述
而去掉之后则才会索引覆盖
图1-1
图1-1
为什么会这样呢？itlms_train_online_register 表中的索引包含了 train_id 和classroom_id 和 is_deleted ，这三个每个都是有单独索引的
请添加图片描述
由于mysql大多数情况下对每个表匹配索引都是匹配一个索引的，我们留意到图1-0和图1-1，每行的key都是仅有一个的，这是因为mysql每次使用索引都会用一个，用多个mysql计算的结果是更消耗时间（后面有例子，是使用了多个单独索引的 ），所以此处仅会使用一条。
执行以下，发现使用using where，触发了回表，但是实际上是使用了索引IDX_itlms_train_online_register_TRAIN_ID。使用了索引，但是触发了回表

explain select
			count(*)
		from
			itlms_train_online_register reg 
		where
			 reg.train_id = '0197ffc3b37f4d2e90dcefff5477b335'
			
		and reg.is_deleted = 'N'
			and (reg.audit_result is null
				or reg.audit_result != '02')
			and (reg.audit_result ='01'
				 or reg.audit_result ='02'
				)

请添加图片描述
执行explain后这句话表示什么呢？这句话就表示说：虽然你给3个字段单独添加了索引，他们也是可能使用的（最左边列为possible_key），但是实际上只使用了一个索引，也就是key为IDX_itlms_train_online_register_TRAIN_ID，其余的判断条件通过回表，再使用where 进行条件过滤
仅使用reg.tran_id的查询条件会发生什么呢？此时就会索引覆盖了

explain select
			count(*)
		from
			itlms_train_online_register reg 
		where
			 reg.train_id = '0197ffc3b37f4d2e90dcefff5477b335'

请添加图片描述
可以看到，extra里面就有using index了。
那么我们该如何去让多条件匹配也走索引呢？答案就是使用联合索引
使用联合索引，是最左匹配原则，遇到范围值则不会在匹配下一个索引条件(具体不解释了，此处只将结论)

我们对train_id ，audit_result ,is_deleted 按顺序建立联合索引
根据最左匹配原则，由于train_id此处是最容易匹配到的，所以放最左边，而audit_result有4个值，放中间，is_deleted只有两个值，所以放是最后

CREATE INDEX IDX_itlms_train_online_register_TRAIN_ID_AUDIT_RESULT_IS_DELETED USING BTREE ON itlms_zs.itlms_train_online_register (TRAIN_ID,AUDIT_RESULT,IS_DELETED);

再次explain第一条多条件的sql，发现已经走了索引了
请添加图片描述
所以在第三题的情况下优化，此时就会变快了（此处没有任何改动）

	.....	(
		select
			count(*)
		from
			itlms_train_online_register reg 
		where
			reg.train_id = train.id
			-- and reg.classroom_id = classroom.id
			and reg.is_deleted = 'N'
 			and (reg.audit_result is null
 				or reg.audit_result != '02')
				) register_valid_count,
				......

请添加图片描述
从1.4秒达到了370ms左右。优化成功

小麟有点小靈
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql 意向锁
csdn问鼎
04-25 196
介绍mysql 意向锁的作用
数据库调优经验记录【MySQL】
泯灭于众生，高歌于孤夜！
09-08 350
联合索引使用过程中遵循哪些原则?最左前缀最常使用的字段放置联合索引的前面离散度高离散度高的字段选择性将会更好.最少空间。
记录一次mysql的调优心得
weixin_33962923的博客
01-24 92
前言：这是最近刚发生在公司的一次应用系统的mysql调优过程，事情的过程是这样的：公司的一个销售系统，用的是mysql数据库，在元旦的前夕突然就宕机了。差不多导致业务系统4个小时左右使用有问题； 因为这个系统乙方公司尚未完全交付，所以数据库的运维的工作，作为甲方也还未交接到我的手上，这个事情也是元旦过后上班才...
工作中基于mysql的sql调优过程
weixin_38426431的博客
06-16 173
mysql调优前期准备 在接到任务后进行mysql相关的调优，首先先别急着去做，环境配置好，跑起来后，冲数据到一定的量，便于后面的查询和验证，此为前期准 ...
一次mysql调优_记录一次奇葩的mysql性能调优经历
weixin_42407739的博客
02-22 139
今天在写一个Koa2程序的时候无意间瞥了一眼日志, 发现某个简单的保存表单的API竟然平均耗时 900ms, 900ms 啊同学们! 这种需求的正常耗时应该再除以10一下SQL Profile分析首先需要知道sql语句在哪个阶段慢, mysql提供了profile工具来帮助我们做性能分析:MYSQL> set profiling=1MYSQL> insert into t_test_t...
mysql优化记录
funnyPython的博客
11-28 161
起因： 上周三晚上，审帖人员说系统很卡。然后我就像往常一样，登服务器查看。查了一圈发现系统负载不高，io也没问题。只查到了有人在当时的时间点做了一些别的操作。当时断定是其他人操作影响的。之后发现一个接口卡了很久。我就猜测是这个sql语句卡了。但是测了一下发现也不慢，就有点不知所措。然后开始找这个语句的慢查询，发现也就两秒多。第二天，和老大讨论,我说可能是nginx卡住了。他说nginx一般不会卡住...
mysql 一次更新(update)多条记录的思路
09-10
主要介绍了mysql 一次更新(update)多条记录的思路,需要的朋友可以参考下
MYSQL调优MYSQL调优MYSQL调优MYSQL调优MYSQL调优MYSQL调优
06-20
MYSQL调优
MYSQL中获取得最后一条记录的语句
09-11
MYSQL中获取得最后一条记录的语句，需要的朋友可以参考下。
Mysql调优.xmind
07-07
整理的mysql调优资料，适合刚入门的以及面试吹牛逼用，有些地方比较细致，有些地方比较粗糙，主要是一些理论，以及重要的知识点
88张表-Mysql
2301_80151755的博客
04-25 219
【代码】88张表-Mysql。
【Day 8】MySQL 多表查询 + Mybatis 基础
Yudiannann的博客
04-24 803
笛卡尔积：在数学中，两个集合(A集合 和 B集合)的所有组合情况在多表查询时，需要消除无效的笛卡尔积内连接：相当于查询A、B交集部分数据外连接：1）左外连接：查询左表所有数据（包括两张表交集部分数据）2）右外链接：查询右表所有数据（包括两张表交集部分数据）
一文整理完MySQL关系型数据库相关知识
打代码是一天，不打代码也是一天
04-23 1152
在开发中，数据库是专门用来存取数据的软件。数据库的职责就是管理数据的。根据存取数据的类型分为关系型数据库和非关系型数据库数据存储后呈现出来的效果类似上图的，是关系型数据库数据存储后呈现出来的效果类似上图的，是非关系型数据库。
MySQL个人复习总结
最新发布
weixin_43739821的博客
04-26 1223
SQL的优化方式有哪些？ 执行一条 select 语句,期间发生了什么？ InnoDB如何存储数据？为什么选择B+树作为索引数据结构？ 使用 like “%x“，索引一定会失效吗？ Count(\*)、Count(1)、Count(主键)、Count(字段)性能?如何优化Count(\*)？ 简单介绍一下MVCC机制和Read View。MySQL 可重复读隔离级别，完全解决幻读了吗？如何避免？ 一条update 语句的执行过程。 三种日志的作用。redo log 要写到磁盘，数据也要写磁盘，为什么要多此一
（一）Mysql创建一个博客相关的数据库
席万里的博客
04-23 958
从0开始搭建一个博客系统
MySQL 服务器权限与对象权限
ChinaLiaoTian的博客
04-23 708
MySQL 权限
深入了解MySQL：从基础到特性，全面解读关系数据库管理系统的历史与应用
lph159的博客
04-23 1132
本文全面介绍了MySQL，这是一种广泛使用的开源关系数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，目前由Oracle公司维护。作为一款具有强大后端功能的数据库软件，MySQL在网站建设、商业智能、数据分析等多个领域中发挥着关键作用。文章从MySQL的历史起源、核心特性，到其在实际应用中的广泛用途进行了详细的阐述。特别指出了MySQL的开源性、高性能、高可扩展性、安全性和灵活性等关键特性，并通过实例展示了如何使用MySQL进行数据库的创建和数据管理。
MySQL如何进行增删改操作
boluomi的博客
04-23 683
如果不需要全部的数据，就选用原表中部分数据创建副本表，如，用今年以前的 orders 创建一个副本表 orders_archived，其实就是在子查询里增加了一个WHERE orders<今年时间的 语句进行筛选。对于AI (Auto Incremental 自动递增) 的id字段，MySQL会记住删除的/用过的id，并在此基础上递增——目前有10条数据，删除了最后两条数据再进行插入数据后，会在10的基础上递增。INSERT INTO 表名 子查询 很常用，子查询替代原先插入语句中 VALUES(……
基于SSM+Jsp+Mysql的定西扶贫惠农推介系统
老卓爱爪哇 的博客
04-23 1005
本文系统地描绘了整个定西扶贫惠农推介系统的设计与实现，主要实现的主要管理员和用户两个角色，以下是管理员功能有以下几点：管理员；个人中心、用户管理、扶贫计划管理、惠农福利管理、优秀农民管理、志愿者报名管理、在线捐赠管理、管理员管理、系统管理，用户后台有以下几点：个人中心、志愿者报名管理、在线捐赠管理，前台首页：首页、扶贫计划、惠农福利、优秀农民、论坛信息、我的、跳转到后台等功能，本系统其具有简单的接口，方便的应用，强大的互动，完全基于互联网的特点。
mysql一次性写入多条记录
10-12
可以使用INSERT INTO语句一次性写入多条记录，语法如下： ``` INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...), (value1, value2, value3, ...), (value1, value2, value3, ...), ... ``` 其中，table_name为表名，column1、column2、column3等为列名，value1、value2、value3等为对应列的值。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值