Mysql实战优化sql（1.2s优化为0.03s）

以下示例是我在实际开发当中的一个优化示例：

obs_consultation_request这个表有720条数据，obs_consultation_appointment_assign这个表有2600条数据，obs_consultation_request和obs_consultation_appointment_assign是一对多的关系！可能数据会有重复所以用到了DISTINCT去重，由于是项目初期，以至于两张表是都没有添加索引的，执行一下目前是1.2s左右！按正常来说这么小的数据量，超过1s都属于不正常现象了。一旦数据慢慢越来越多，后果不堪设想！最后优化成了0.03s！

注意：以下sql是mybatis分页插件，自动count来求数量的，实际当中我们肯定是要查数据的！

SELECT
	COUNT(*) 
FROM
	(
	SELECT DISTINCT
		a.* 
	FROM
		(
		SELECT
			* 
		FROM
			obs_consultation_request a 
		WHERE
			a.request_datetime >= "2022-10-01 00:00:00.0" 
			AND a.request_datetime <= "2022-11-30 23:59:59.0" 
			AND a.patient_name LIKE CONCAT( '%', "北京", '%' ) 
			AND a.`status` = "2" 
			AND a.`status` IN ( 2, 3, 6, 31, 32, 33, 34, 35, 36 ) 
			AND a.request_doctor_code = "wcc" 
		) a
		LEFT JOIN obs_consultation_appointment_assign b ON a.id = b.consultation_id 
	WHERE
	( b.assign_doctor_code = "wcc" OR a.request_doctor_code = "wcc" ) 
	) total

其中这些都是条件查询，都是可能存在可能不存在的：

a.request_datetime >= "2022-10-01 00:00:00.0" 
AND a.request_datetime <= "2022-11-30 23:59:59.0" 
AND a.patient_name LIKE CONCAT( '%', "北京", '%' ) 
AND a.`status` = "2" 
AND a.request_doctor_code = "wcc"

( b.assign_doctor_code = "wcc" OR a.request_doctor_code = "wcc" ) 

刚开始我寻思的把这些条件提到一块，如下：但是实际上根本提升不了速度！所以基于sql上的优化好像并无作用！

SELECT DISTINCT
	a.* 
FROM
	(
	SELECT
		* 
	FROM
		obs_consultation_request a 
	WHERE
		a.request_datetime >= "2022-10-01 00:00:00.0" 
		AND a.request_datetime <= "2022-11-30 23:59:59.0" 
		AND a.patient_name LIKE CONCAT( '%', "北京", '%' ) 
		AND a.`status` = "2" 
		AND a.`status` IN ( 2, 3, 6, 31, 32, 33, 34, 35, 36 ) 
		AND a.request_doctor_code = "wcc" 
	) a
	LEFT JOIN ( SELECT assign_doctor_code, consultation_id FROM obs_consultation_appointment_assign ) b 
	ON a.id = b.consultation_id 
WHERE
	( b.assign_doctor_code = "wcc" OR a.request_doctor_code = "wcc" ) 
ORDER BY
	request_datetime DESC 
	LIMIT 10

第一步：分析慢的原因

1. 刚开始以为是status相关条件代码导致的慢，后来删除后，发现仍然还是慢，而且更慢了
2. 条件里面有一个or条件，然后我去除or之后仍然是慢，而且也是更慢了
3. 通过以上两点说明一个问题，查询条件并没有导致sql变慢，所以直接开始加索引！

第二步：添加索引前期准备工作

此处使用了LEFT JOIN，使用LEFT JOIN走索引的一个前提条件是两张表 排序规则置必须为一致，如果不是一致需要修改为一致，修改表排序规则，而表中的每个字段的排序规则并不会跟随表的排序规则变化, 修改后的表排序规则只对新增字段生效！ 所以我们需要将每个字段的排序规则也改为一致！这一点一定要注意

一般导致规则不一致的原因是 表是在自己机器数据库建的表, 本地数据库的排序规则和生产库不一致，以至于在本地创建的表，执行到生产库之后，排序规则是他本地库的排序规则。

查询每一列的排序规则：

show full columns from obs_consultation_request;

调整排序规则：

ALTER TABLE TABLE1 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 

第三步：分析加哪些列的索引

注意：如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

阿里巴巴索引命名规范：主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。
说明：pk_即 primary key；uk_即 unique key；idx_即 index 的简称

掌握了这些，我们直接开始添加索引：

ALTER TABLE `mdt`.`obs_consultation_appointment_assign` 
ADD INDEX `idx_consultationid_assigndoctorcode`(`consultation_id`, `assign_doctor_code`);

status,id, request_datetime这三个条件是一定存在的，所以我要放在最前面，然后条件查询都是可能存在的，所以依次在后面罗列即可！模糊查询%条件%是不会走索引的，条件%会走索引，但是俺们产品左右都要，所以这肯定不对patient_name加索引了，加了也没效果，如果数据量达到一定程度，产品肯定会后悔的哈！

ALTER TABLE `mdt`.`obs_consultation_request` 
ADD INDEX `idx_status_id_requestdatetime_doctorcode`(`status`,`id`, `request_datetime`, `request_doctor_code`);

第四步：查询执行计划，直接EXPLAIN sql语句即可

假如没有条件查询的时候查询结果如下：第一条sql查询显示的是，可能会用到索引，但是实际上没有用到索引，原因可能是obs_consultation_request数据量太小！mysql会计算最优时间，有的时候用索引还不如不用索引快！然后第二条sql当中的索引已经是用到了！

存在条件查询的时候查询结果如下：存在条件查询的时候两个索引都用到了！

经过添加索引后由原先的 1.2s 变成了 0.03s ！