【知识积累】MYSQL之SQL调优 - 实战篇

最新推荐文章于 2024-08-24 00:37:28 发布

Darren Gong

最新推荐文章于 2024-08-24 00:37:28 发布

阅读量196

点赞数

分类专栏：数据库文章标签： DB

本文链接：https://blog.csdn.net/axin1240101543/article/details/90691463

版权

数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

一、查看索引

show INDEX FROM dyf_prescriptions;
show INDEX FROM dyf_prescription_details;

二、查看表数据量

三、优化

1、无索引执行

用时：15.693s

2、索引执行（子查询）

用时：1.845s

查看优化后的SQL：

/* select#1 */
SELECT
	`new_km_dyf`.`p`.`prescri_id` AS `prescri_id`,
	`new_km_dyf`.`p`.`order_id` AS `order_id`,
	`new_km_dyf`.`p`.`username` AS `username`,
	`new_km_dyf`.`p`.`tel` AS `tel`
FROM
	`new_km_dyf`.`dyf_prescriptions` `p` semi
JOIN (
	`new_km_dyf`.`dyf_prescription_details` `d`
)
WHERE
	(
		(
			`new_km_dyf`.`d`.`medicines` = '白芍'
		)
		AND (`new_km_dyf`.`d`.`dose` = 20)
		AND (
			CONVERT (
				`new_km_dyf`.`p`.`prescri_id` USING utf8mb4
			) = `new_km_dyf`.`d`.`prescri_id`
		)
	)

3、索引执行（表联）

修改索引

用时：0.245s

查看优化后的SQL：

/* select#1 */
SELECT
	`new_km_dyf`.`p`.`prescri_id` AS `prescri_id`,
	`new_km_dyf`.`p`.`order_id` AS `order_id`,
	`new_km_dyf`.`p`.`username` AS `username`,
	`new_km_dyf`.`p`.`tel` AS `tel`
FROM
	`new_km_dyf`.`dyf_prescriptions` `p`
JOIN `new_km_dyf`.`dyf_prescription_details` `d`
WHERE
	(
		(
			`new_km_dyf`.`d`.`medicines` = '白芍'
		)
		AND (`new_km_dyf`.`d`.`dose` = 20)
		AND (
			CONVERT (
				`new_km_dyf`.`p`.`prescri_id` USING utf8mb4
			) = `new_km_dyf`.`d`.`prescri_id`
		)
	)

修改SQL执行顺序：

用时：2.089s

添加索引：

用时：1.954s

4、联合索引执行（表联）

用时：1.958s

四、总结

1、MYSQL嵌套子查询效率比较低，可以使用表联查询；
2、可以修改MYSQL的执行顺序；
3、建立合适单列索引，单列索引需建立在where、order by、join on、group by的字段上；
4、根据需要建立多列联合索引。当单个列过滤之后还有很多数据，那么索引的效率将会比较低，即列的区分度较低，那么如果在多个列上建立索引，那么多个列的区分度就大多了，将会有显著的效率提高，联合索引有最左前缀的特性，索引的第一个字段要出现在where条件中；
5、根据业务场景建立覆盖索引。只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率；
6、where条件上不要使用运算函数，以免索引失效；
7、列类型尽量定义成数值类型，长度尽量短

五、扩展

1、semi join

半连接是MySQL 5.6.5引入的，多在子查询exists中使用，对外部row source的每个键值，查找到内部row source匹配的第一个键值后就返回，如果找到就不用再查找内部row source其他的键值了。

2、慢查询

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

show variables like '%quer%';