MySQL 第五期 如何优化SQL

如何优化SQL

全表扫描还是索引扫描

先来看看下面对于大表，加索引与不加索引的区别。为何不提小表，是因为对于小表来说IO调用次数和返回相差不大,所以就不特别提出来讲了。

类型	全表	索引
查询速度	慢	快
更新速度	快	慢
插入速度	慢	快

从上面示例中可以看到，在查询方面有索引确实会效率很多，但是在更新和插入涉及到表操作的就会相对于慢一些，这是因为在操作时需要增加额外的建立索引的过程。

如何创建索引

准则：

1. 尽量选择高度列 也可以在status列上创建索引
2. 减少冗余索引。比如 index(name, age) 和 index（name）这个name的索引就是冗余的了
3. 一定要找到适合业务的索引，每张表的索引最好不要超过5个。

减少索引的修改

索引的频繁改变对于线上运行项目来说是个很大的挑战，因为索引的增加是会对表锁有影响。所以在创建索引时最好根据业务情况，将业务查询的字段正好在索引树上，这样就减少了回表的操作。有助于实现索引覆盖从而降低IO次数。

多表关联字段加索引

(inner,left,right) join 查询，最好满足以下几个原则：

1. 关联字段加上索引 （可以使用到BKA算法）
2. 关联字段字符集与类型保持一致 （具体原因可看上一期 索引失效原因.）
3. 使用结果集小的表做驱动表（结果集小就是查询出来数据小的）

全模糊匹配查询

对于这种like %% 全模糊的查询，索引来说是无法使用，所以建议使用全文搜索引擎中去解决比如ES或者solr中解决。可以在like的查询语句的同时加上其它选择高度的列或者条件 比如
select * from user where name like ‘%name%’ and phone = '13895698794’

排序/分组索引

因为order by 与 group by 是可以使用到索引的。并且排序最好是同一方向。所以最好将排序的字段加入到联合索引当中去，可以避免或减少排序次数。比如以下 index(name,age)
select * from user where name = ‘gio’ order by age desc

复杂查询还是拆分几个小查询

虽然随着技术的发展数据库自身的优化与性能都会提升，但是在复杂查询这块，我还是偏向于拆分成多个简单查询，‘分而治之’，也可以将查询的业务变得更好理解。当然除非是业务部门临时需要你在数据库中查询,这就另说了。

联合索引的索引顺序

在联合索引中 索引的顺序也是很重要的，基本原则是：where -> order by -> <>。

explain query	index(age,sex,phone)	index(sex,phone,age)
SELECT * FROM user where age > 15 AND sex = 1 ORDER BY phone	Using index condition; Using where; Using filesort	Using index condition

从上面示例中我们可以得出，联合索引中的前后顺序也会影响到查询的效率，所以建议联合索引的顺序应该是

• 第一位 精确匹配字段
• 第二位 排序字段
• 第三位 范围匹配字段/模糊匹配子弹

总结

• 重要的SQL必须被索引：update delete的where条件列、order by、group by、distinct字段、多表join字段
• 禁止使用%前导查询，例：like ‘%%’, 无法利用索引
• 禁止使用负向查询，例如: not in，!=, <>, not like
• 使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using file sort、Using Temporary 等
• 减少与数据库交互次数，尽量采用批量SQL语句
• 获取大量数据时，建议分批次获取数据，每次获取数据少于5000条，结果集要小于1M
• 拆分复杂SQL为多个小SQL，避免大事务。简单的SQL容易使用到MySQL的query cache；减少锁表时间特别是MyISAM;可以使用多核CPU