前缀索引优化#
在字符串字段的前几位字符上建立索引。
局限性：
- order by 无法使用前缀索引
- 无法把 [前缀索引] 用作 [覆盖索引]
覆盖索引优化#
含义：指 SQL 中 select 的所有字段，在二级索引 B+Tree 的叶子节点上都能找得到，即从二级索引的 B+Tree 中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。
比如只需商品的名称、价格时，建立联合索引即 [商品 ID、名称、价格] 作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表（减少 I/O 操作）。
主键索引最好自增#
InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上（叶子节点自动排序）。
使用自增主键：每次插入一条新记录，都是追加操作，不需要重新移动数据。
使用非自增主键：由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
举个例子，假设某个数据页中的数据是 1、3、5、9，且数据页满了，现在准备插入一个数据 7，则需要把数据页分割为两个数据页：
出现页分裂时，需要将一个页的记录移动到另外一个页，性能会受到影响，同时页空间的利用率下降，造成存储空间的浪费。
而如果记录是顺序插入的，例如插入数据 11，则只需开辟新的数据页，也就不会发生页分裂：
主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。
索引列设置 NOT NULL#
为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：
- 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为 NULL 的行
- NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么 **行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表 **，如下图的紫色部分：
防止索引失效#
用上了索引并不意味着查询的时候会使用到索引。索引失效的常见情况：
- 使用左或左右模糊匹配，即
like '%xx'或like '%xx%' - where 中对索引列做了计算、函数、类型转换操作
- 不遵守联合索引最左匹配原则
- 在 where 子句中，
where 索引列 or 非索引列
实际过程中，需要查看执行计划 (explain) 来判断是否使用了索引，如下图就是全表扫描没有使用索引的查询语句：
对于执行计划的结果：
- possible_keys 字段表示可能用到的索引
- key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引
- key_len 表示索引的长度
- rows 表示扫描的数据行数
- 重点！！type 表示数据扫描类型，执行效率从低到高的顺序为：
- All（全表扫描）！！尽量避免
- index（全索引扫描）！！尽量避免
- range（索引范围扫描）：至少要到这一级别
- ref（非唯一索引扫描）：存在重复值
- eq_ref（唯一索引扫描）：eq_ref 通常用于多表联查中
- const（结果只有一条的主键或唯一索引扫描）：const 是与常量进行比较，查询效率会更快
- Extra：
- ！！避免 Using filesort：查询语句中包含 group by，且无法利用索引排序时
- ！！避免 Using temporary：使用了临时表保存的中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by
- ！！不错 Using index：所需数据在二级索引的 B+Tree 中即可全部获得，不需要再一一回表获取数据，也就是使用了覆盖索引，避免回表操作