MySQL优化 之 常见规则小结

数据库在业务开发过程中是一个必不可少的环节，业务场景常常包括数据库变更、数据库表设计、SQL编写等需求。这篇文章对MySQL数据库常见优化规则进行小结。

（1）in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内；

（2）TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句；

（3）尽量选择区分度高的列作为索引；

（4）索引列不能参与计算，保持列“干净”；
举例：from_unixtime(create_time) = ‘2014-05-29’ 就不能使用到索引，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。
正确写法：create_time = unix_timestamp(‘2014-05-29’)
（5）最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配；

（6）SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内；

（7）除静态表或小表（100行以内），DML语句必须有where条件，且使用索引查找；

（8）WHERE 子句中禁止只使用全模糊的LIKE条件进行查找，必须有其他等值或范围查询条件，否则无法利用索引；

（9）减少使用or语句，可将or语句优化为union，然后在各个where条件上建立索引。

（10）分页查询，当limit起点较高时，可先用过滤条件进行过滤。
举例：如 select a,b,c from t1 limit 10000,20;
优化为: select a,b,c from t1 where id>10000 limit 20;

（11）对于有auto_increment属性字段的表的插入操作，并发需要控制在200以内；

（12）事务里包含SQL不超过5个（支付业务除外）。因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等雪崩问题；

（13）减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的；

（14）包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内；

（15）尽量使用覆盖索引（索引列和查询列保持一致），减少select *。

尾注
更多及时干货，请关注微信公众号：JAVA万维猿圈