Mysql 的sql语句优化
Mysql 的sql语句优化
1、LIMIT 语句优化（大偏移量情况下）
分页查询是最常用的场景之一，但也通常也是最容易出问题的地方。比如对于下面简单的语句，一般 DBA 想到的办法是在 type, name, create_time 字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。
好吧，可能90%以上的 DBA 解决该问题就到此为止。但当 LIMIT 子句变成 “LIMIT 1000000,10” 时，程序员仍然会抱怨：我只取10条记录为什么还是慢？要知道数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次。出现这种性能问题，多数情形下是程序员偷懒了。在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的。SQL 重新设计如下：在新设计下查询时间基本固定，不会随着数据量的增长而发生变化。
对于Limit大的偏移情况下， mysql并不是跳过offset行， 而是取offset+N行， 然后放弃前offset行， 返回N行， 那么当offset特别大的时候， 效率就特别低下， 要么控制返回的总页数， 要么对特定阈值的页数进行sql改写。
示例：先快速定位需要获取的id段，然后再关联
select a.* from 表1 AS a, (select * from 表1 where 条件 limit 1000000, 20) AS b where a.id = b.id
2、关联更新、删除
虽然 MySQL5.6 引入了物化特性，但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成 JOIN。比如下面 UPDATE 语句，MySQL 实际执行的是循环/嵌套子查询（DEPENDENT SUBQUERY)，其执行时间可想而知。
优化：重写为 JOIN：
多表关联删除
从MySQL数据表t1中把那些id值在数据表t2里有匹配的记录全删除掉
DELETE t1 FROM `user` AS t1 INNER JOIN user_role AS t2 WHERE t1.id = t2.user_id
从MySQL数据表t1里在数据表t2里没有匹配的记录查找出来并删除掉
DELETE t1 FROM `user` AS t1 LEFT JOIN user_role AS t2 ON t1.id = t2.user_id WHERE t2.user_id IS NULL
从两个表中找出相同记录的数据并把两个表中的数据都删除掉
DELETE u, ur from `user` AS u INNER JOIN user_role AS ur ON u.id = ur.user_id WHERE u.id=1
3、EXISTS语句
MySQL 对待 EXISTS 子句时，仍然采用嵌套子查询的执行方式。如下面的 SQL 语句：
去掉 exists 更改为 join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。
4、提前缩小范围
数为90万，时间消耗为12秒。
由于最后 WHERE 条件以及排序均针对最左主表，因此可以先对 my_order 排序提前缩小数据量再做左连接。SQL 重写后如下，执行时间缩小为1毫秒左右。
6、delete后加 limit
在业务场景要求高的数据库中，对于单条删除和更新操作，在 delete 和 update 后面加 limit 1 绝对是个好习惯。比如，在删除执行中，第一条就命中了删除行，如果 SQL 中有 limit 1；这时就 return 了，否则还会执行完全表扫描才 return。效率不言而喻。
delete from t where sex = 1 limit 100;
delete 后面是支持 limit 关键字的，但仅支持单个参数，也就是 [limit row_count]，用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。
加 limit 的的优点：
以下面的这条 SQL 为例：
delete from t where sex = 1;
-
1. 降低写错 SQL 的代价，就算删错了，比如 limit 500, 那也就丢了 500 条数据，并不致命，通过 binlog 也可以很快恢复数据。
-
2. 避免了长事务，delete 执行时 MySQL 会将所有涉及的行加写锁和 Gap 锁（间隙锁），所有 DML 语句执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。加锁都是基于索引的，如果 sex 字段没索引，就会扫描到主键索引上，那么就算 sex = 1 的只有一条记录，也会锁表。
-
3. delete 数据量大时，不加 limit 容易把 cpu 打满，导致越删越慢。
浙公网安备 33010602011771号