mysql调优实战

EXPLAIN执行分析

id:值越大越先执行相同时，由上向下执行。
possible_key: 可能走索引的键。 key：真正走索引的键
rows:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

system > const > eq_ref > ref > range > index > all
eq_ref 唯一性索引扫描, ref：非唯一性索引扫描
index:遍历了整个索引行才查询到所需的行

range:范围查询

trace工具

可以查看mysql优化器具体的执行计划以及成本估算

小表驱动大表

驱动表：通常选择数据量较小，索引比较完备的表

• 1 LEFT JOIN 左连接,左边为驱动表,右边为被驱动表.

• 2 RIGHT JOIN 右连接,右边为驱动表,左边为被驱动表.

• 3 INNER JOIN 内连接, mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表.

索引

较频繁查询条件的字段应该创建索引
不适合：

• 字段唯一性太差不适合单独作索引
• 更新非常频繁的字段不适合
• 不会出现在where的字段
   

缺点：

• 占用物理空间
• 降低增删改的效率
   

通常建议选用联合索引：因为每增加一个索引就会增加写操作的开销和磁盘的开销

mysql自身的优化

索引覆盖

索引下堆

对于范围查询或者模糊查询，减少回表的次数

索引失效

关键看排序是否会失效
最左前缀法则

where condition = "age",此时不会走联合索引，因为走了也没意义，排序是先按照name进行排序

1.首先key一定要有值。不能是NULL
2.type应该是ref、eq_req, range、const
3.extra如果是NULL,useing index using index condition都是可以的

是否走索引是mysql的优化器通过查询成本估算决定的
失效原因：

• 未正确创建使用索引（走索引的字段使用了函数或者类型转换（varchar字段不加引号））
• 表数据量过少（此时优化器认为走索引也不会快多少）
• 索引区分度不高

避免使用SELECT *

在查询数据时，尽量避免使用SELECT *，而是明确指定需要查询的字段。这样可以减少返回的数据量，提高查询效率。
弊端：

• 增加查询解析器的成本
• 无用字段增加网络消耗，特别是text

update语句优化

UPDATE语句的优化就是为了避免表中出现表级锁，从而影响并发的性能。

当UPDATE语句更新表数据时，WHERE条件使用的是索引字段，那么此时会出现行级锁，只是锁住这一行数据，对表中其他的数据没有任何影响，性能最高，但是当WHERE条件使用的不是索引字段时，此时就会出现表级锁，只有当UPDATE语句的事务提交完毕，表级锁才会释放，大大影响并发的性能

JOIN替代子查询，减少查询的次数

注意： 该原则并不适用所有场景，一次外部查询，一次嵌套查询，使用连结查询减少数据库的查询次数，提高查询效率
子查询执行顺序：先

course_urer表中数据量大约1000条，订单表大约300条
2.7s  > 1.2s 以内

UPDATE c_course_user
SET STATUS = 0
WHERE
	user_id = 1742078314821632001
AND order_id IN (
	SELECT
		id
	FROM
		d_order
	WHERE
		post_id IN (1716355306112122881)
)
________________________

UPDATE c_course_user
JOIN d_order ON c_course_user.order_id = d_order.id
SET c_course_user.status = 0
WHERE c_course_user.user_id = 1742078314821632001
AND d_order.post_id = 1716355306112122881;

course_urer表中数据量大约1000条，订单表大约300条，授权表数据约1000条

3.8s_>

UPDATE d_workflow_check
SET check_status = 1
WHERE
	business_id IN (
		SELECT
			order_id
		FROM
			d_order o
		JOIN c_course_user cu ON o.id = cu.order_id
		WHERE
			user_id = 1742078314821632001
		AND post_id IN (1716355306112122881)
	)

批量插入

减少对数据库的请求次数，注意批量数据大于500时，考虑分批量进行查询

group by order by

grop by的列也可以使用索引，提高查询效率

监控SQL执行性能

8.0版本建议使用

对表连结  on筛选， 分库分表 微服务