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

悠悠 2024-04-03 13:30 32阅读 0赞

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

> `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当中的索引已经是用到了！

![在这里插入图片描述][bec9ea98492843e1a293370aac32722e.png]

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

![在这里插入图片描述][0b2e67a94fc148188d942cd6be0e3eb3.png]

![在这里插入图片描述][b70e844913f34edc89a3882bf0c4624f.png]

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

[bec9ea98492843e1a293370aac32722e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/b09a38de30914b988e253ec09e933b4c.png
[0b2e67a94fc148188d942cd6be0e3eb3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/a3fc367c59e34148b3876cf301d49c84.png
[b70e844913f34edc89a3882bf0c4624f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/1e45219c7de3425899d9a7ad701044a5.png