MySQL的索引创建及优化

什么时候需要索引/不需要创建索引

索引的优缺点

索引的优点：

1. 提高查询速度

索引的缺点：

1. 需要占用物理空间，数量越大，占用空间越大

2. 创建索引和维护索引要耗费时间，这种事件随着数据量的增大而增大

3. 会降低表的增删改的效率，每次增删改索引，B+树为了维护索引的有序性，需要进行动态维护

索引的适用场景

1. 字段有唯一性的限制，比如商品编码

2. 经常用于where查询条件的字段，这样能提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引

3. 经常用于group by和oreder by的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在B+Tree中的记录都是排序好的

索引不适用场景

1. where条件，group by，order by里也用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是i不需要创建索引的，因为索引是会占用物理空间的

2. 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库中，男女的记录分部均匀，那么无论搜索那个值都可能得到一般的数据，在这些情况下，还不如不要索引，

   1. 理由：MySQL有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候吗，他会忽略索引，进行全表扫描

3. 表数据太少的时候，不需要创建索引

4. 经常更新的字段不用创建索引

   1. 举例：不要对电商项目的用余额建立索引，因为索引字段频繁修改，由于要维护B+Tree的有序性，那么就需要频繁的重建索引，会影响数据库的性能

索引优化

1. 前缀索引优化

2. 覆盖索引优化

3. 主键索引最好是自增的

4. 防止索引失效

前缀索引优化

前缀索引：使用某个字段中字符串的前几个字符建立索引，例如：字段：asdfgh，我们可以把asd建立为索引

优点：

1. 使用前缀索引可以减少所以字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度

2. 在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小

局限性：

1. order by无法使用前缀索引

2. 无法把前缀索引用作覆盖索引

覆盖索引优化

覆盖索引是指SQL中query(查询)的所有字段，在索引B+Tree的叶子节点上能找得到的那些索引，从而及索引中查询得到记录，而不需要通过聚簇所欲i你查询获得，可以避免回表的操作

好处：不需要查询出包含整行记录的所有信息，减少了大量的I/O操作

方法：联合索引

举例：当我们查询一个商品表，要获取商品的名称和价格，我们就可以把商品的名称和价格作为联合索引，这样在查找商品的名称和价格是，可以直接获取到数据，而不会再次检索主键索引，避免了回表操作

主键索引最好是自增的

前情：InnoDB常见主键索引默认为聚簇索引，数据被存放在B+Tree的叶子节点上，所以同一个叶子节点内的各个数据顺序是按照主键顺寻存放，所以后续数据插入，也会根据主键将其插入到对应的叶子节点中。

如果主键非自增：如果主键是非自增的，每次插入逐渐的索引值都是随机的，因此每次插入数据时，就可能插入到现有数据页中间的某个位置，此时就需要移动其他数据来满足新数据的插入，甚至可能涉及到页面级别数据迁移，这种情况通常称为页分裂，页分裂有可能造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率

自增好处体现：当使用自增主键时，每次插入的新数据会被舒徐添加到当前索引节点的位置，不需要移动已有数据，当页面写满时，会自动开辟新页面，效率更高

注意：主键字段的长度不要太大，

原因：主键字段长度越小，意味着二级索引的叶子节点越小，这样二级索引的占用的空间也就越少

索引设置NOT NULL

索引列设置为NOT NULL，可以更好的利用索引

原因：

1. 索引列存在NULL就会导致优化器在做索引选择的时候更加复杂，难以优化，因为NULL所在的列会使用索引，此时索引的统计和值比较都更复杂，比如适用count时会忽略NULL所在的行

2. NULL值是一个没有意义的值，但是会占用物理空间，因为在InnoDB存储引擎存储记录时，行格式会至少用1字节空间存储NULL值列表

防止索引失效

索引失效的场景

1. 适用左或者左右迷糊匹配的时候，例如 like %xx 或者 like %xx% 这两种方式都会造成索引失效

2. 查询条件中对索引做了计算，函数，类型转换操作，这些情况都会造成索引失效

3. 联合索引需要最寻最左匹配原则，否则也会导致索引失效

4. 在where子句中，如果or前的条件列是索引列，而在or后的条件列不是索引列，索引也会失效