携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第18天，点击查看活动详情
常见问题
在电商平台的商品中心系统中，若需要获取处于某状态的所有商品，并且根据创建时间排序，那针对下面 SQL 语句 如何优化？
select * from order where status = 0 order by create_at desc
可优化的点：
-
select 具体有用的字段，而不是使用
* -
增加 status 和 create_at 的组合索引。
如果只对 status 建立索引，那么在查询时只能用到 status 的索引，但对 create_at 排序的话就会用到文件排序 file_sort。使用 explain 可以看到 Extra 列值是 Using filesort。
explain select * from order where status = 0 order by create_at desc
所以避免发生 filesort ，利用索引有序性，建立组合索引是最优方案。
索引
索引是一种数据结构，官方定义：索引是存储引擎用于快速查找记录的一种数据结构。在 MySQL 数据库中，索引类型是 B+Tree 索引。
B+Tree 树
基于磁盘的平衡树，通常3-4层，能存放上千上亿数据。因层数少，所以从上亿条数据中查询一条数据，可能只需要 3-4次I/O。所以可以快速查询到数据。
- B+Tree 相对于 B Tree 优势：B+Tree ：数据存储在叶子节点，且所有节点间都有指针。BTree：所有节点都存储数据
- B+Tree 相对于 Hash 优势：Hash 只适合等值查询，不支持条件查询，模糊查询；Hash 发生某个键值重复时，可能会导致效率低
B+Tree 里的记录时已排序好的。行数据都是放在叶子节点上的。所以节点=1 时，根节点就是叶子节点，只需进行二叉查找就能快速定位到数据；节点>1 时，根节点和中间节点存放的是索引键（由索引键、指针组成）。
索引类型
- 主键索引：特殊索引，根据主键建立索引，不允许重复，不允许空值
- 唯一索引：值必须是唯一，允许空值。如果是组合索引，则组合列值必须是唯一的
- 普通索引：普通列构建索引，无任何限制
- 全文索引：通过建立到排索引，快速匹配文档的方式
索引使用原则
索引失效的情况
- sql 语句中含有 like 、or 等条件查询
- sql 语句中存在函数运算或者由表达式计算 如 where md5(ddf)='xxx'
- 反向查询将会导致无法使用索引，如Not in ，not like，!=
- 联合索引时，需遵循最左匹配原则，即需要使用第一个
什么时候不需要索引
- where 条件，group by ，order by 里用不到的字段
- 字段中存在大量重复，则不需要
- 表数据太少就无需创建索引
- 经常更新的字段不用，避免索引的维护成本高
什么时候应该使用索引
- 字段有唯一限制
- 经常用于 where 查询的字段
- 经常用于 group by ，order by 的字段
参考资料：
