数据库索引及慢查询优化

索引是对数据库中一列或几列的数据按照特定的数据结构进行排序保存的一种方式。使用索引可以加快数据库查询或排序时的速度。如果不使用索引那么查询数据时就会进行全表扫描也就是每条数据读取一遍，而使用索引可以快速找到要查询的数据，不用扫描所有数据。

索引的优点

• 可以大大提高数据查询的速度
• 在实现数据的参考完整性方面，可以加快表和表之间的连接
• 在分组和排序时，可以减少分组和排序的时间

当然，索引也有缺点，所以我们不能在表中随意的创建索引

索引的缺点

• 创建和维护索引需要耗费时间，数据量越大耗费时间也越长
• 索引需要占用额外的磁盘空间，如果索引量很大又有大量索引，那么索引文件大小增加很快（深有体会）
• 对表中数据改动的时候，索引需要动态维护，降低了数据操作的速度

索引的设计原则

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整，=和in可以乱序。
2. 索引不是越多越好，索引太多会影响insert,delete,update的性能
3. 数据量很小的表最好不要使用索引，由于数量少，扫描表的时间可能比查询索引的时间要短，索引没有效果
4. 尽量选择区分度高的列作为索引，例如，表示性别的字段一般只有‘男’和‘女’两个不同值，如果建立索引会严重降低更新速度
5. 索引列不能参与计算，保持列“干净”，
6. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

select * from account where status = 1 and type = 2 and creator_id = 12;
select * from account where status = 0;
select * from account where status = 1 and creator_id = 10;

如果业务场景中会经常对以上的sql进行查询，根据最左前缀匹配原则应该建立（status, creator_id, type）的索引，因为可以覆盖所有情况

慢查询优化基本步骤

1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
2. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
3. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
4. order by limit 形式的sql语句让排序的表优先查
5. 了解业务方使用场景
6. 加索引时参照建索引的几大原则
7. 观察结果，不符合预期继续从1分析

没有平白无故的查询优化，深入到业务场景中，充分了解业务需求往往更能解决问题。其实不光是查询优化，任何的架构、设计、技术离开业务场景的大谈特谈都是耍流氓！

参考：MySQL索引原理及慢查询优化