分类

按数据结构分类：b+ tree索引、Hash索引、Full-text索引 按物理存储分类：聚簇索引(主键索引)、二级索引(辅助索引) 按字段特性分类：主键索引、唯一索引、普通索引、前缀索引 按字段个数分类：单列索引、联合索引

按数据结构分类

InnoDB是Mysql默认存储引擎,B+Tree也是使用最多的引擎 索引列选择：

• 如果有主键,默认会使用主键作为聚簇索引的索引键（key）；
• 如果没有主键,就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
• 在上面两个都没有的情况下,InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

B+ Tree

B+Tree 是一种多叉树,叶子节点存放数据,非叶子节点存放索引,叶子节点间用双向链表链接。

查询过程

select * from product where id = 5;

• 将5与跟节点点索引数据(1,10,20)比较,找到在1-10之间,继续向第二层索引数据找
• 在第二层索引数据(1,4,7)中进行查找,找到在4-7之间,继续向第三层索引查找
• 直到找到索引值为5的行数据

B+Tree对于千万级数据也只需要3-4层高度就能满足,相比于B树和二叉树来说,最大的优势在于查询效率很高,因为在数据量很大的情况下,查询一个数据的磁盘I/O依然维持在3-4次。

二级索引查询数据的过程

主键索引的B+Tree和二级索引的B+Tree的区别：

• 主键索引的B+Tree的叶子节点存放的实际数据,所有完整的用户记录都存放在主键索引的B+Tree的叶子节点里
• 二级索引的B+Tree的叶子节点存放的是主键值,而不是实际数据。

例如执行查询语句：

select * from product where product_no = '0002';

先在二级索引查询,获得主键值,再去主键索引查询行数据。这个过程叫回表,需要查询两个B+Tree才能查到数据。

但是在二级索引就能获得结果的过程叫「覆盖索引」,只需要查询一个B+Tree 例如：

select id from product where product_no = '0002';

为什么选择b+Tree

• B+Tree与B Tree
• B+Tree与二叉树：二叉树儿子节点只有2个,搜索复杂度比B+Tree高很多
• B+Tree与hash：Hash只适合做等值查询,不适合做范围查询

按物理存储分类

聚簇索引、二级索引,上面已经讨论区别。

按字段特性分类

主键索引

建立在主键字段上的索引,通常在创建表的时候一起创建,一张表最多只有一个主键索引,索引列不允许空值

唯一索引

建立在UNIQUE字段上的索引,一张表可以有多个唯一索引,索引列的值必须唯一,但是允许有空值。

普通索引

建立在普通字段上的索引,即不要求字段为主键,也不要求是UNIQUE。

前缀索引

对字符类型字段前几个字符建立索引,而不是整个字段上建立索引。

按字段个数分类

单列索引、联合索引

• 建立在单列上的索引称为单列索引
• 建立在多列上的索引称为联合索引

联合索引

联合索引将多个字段作为索引,先按照第一个key比较,在第一个key相同的情况下,再根据第二个字段比较,以此类推。 因此,联合索引 存在最左匹配原则,如果在使用时不遵循最左匹配原则就会导致索引失效。

联合索引范围查询： 并不是查询过程使用了联合索引查询,就代表联合索引中的所有字段都用到了联合索引进行索引查询,可能部分字段没有用到联合索引。 这种情况发生在范围查询。范围查询的字段可以用到联合索引,但是在范围查询字段的后面的字段无法用到联合索引。 联合索引为(a,b) 例一：

select * from tb where a > 1 and b = 2;

a>1会走索引,但是在a>1条件的二级索引范围中,b的字段是无序的,所以b=2是不会走索引的。

例二：

select * from tb where a >= 1 and b = 2;

a>=1会走索引,虽然在a>1的范围中,b是无序的,但是在a=1的范围中,b是有序的。所以在查找a>=1 and b=2时,会从a=1 and b=2这条记录开始查询,会减少一些查询范围,a和b都走了索引。

例三：

select * from tb where a between 2 and 8 and b = 2;

a和b都走了联合索引

例四：

select * from tb where name like 'j%' and age = 22;

与例二相同,字符串会以字段的值排序,所以都会走联合索引。

总结： 综上所示,联合索引的最左匹配原则,在遇到范围查询（如 >、<）的时候,就会停止匹配,也就是范围查询的字段可以用到联合索引,但是在范围查询字段的后面的字段无法用到联合索引。注意,对于 >=、<=、BETWEEN、like 前缀匹配的范围查询,并不会停止匹配,前面我也用了四个例子说明了。

索引下推

• 在 MySQL 5.6 之前,只能从 ID2 （主键值）开始一个个回表,到「主键索引」上找出数据行,再对比 b 字段值。
• 而 MySQL 5.6 引入的索引下推优化（index condition pushdown),可以在联合索引遍历过程中,对联合索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

当你的查询语句的执行计划里,出现了 Extra 为Using index condition,那么说明使用了索引下推的优化。

索引区分度

建立联合索引时,要把区分度大的字段排在前面,这样区分度大的字段越有可能被更多的sql使用到。

区分度时某个字段不同值的个数除以表的总行数。

联合索引进行排序

select * from order where status = 1 order by create_time asc

对于这个查询语句可以对status和cerate_time 建立一个联合索引,在status筛选完之后就已经排好了create_time 排好了序,提高了查询效率。

什么时候需要创建索引

索引缺点：

• 需要占用物理空间,数量越大,占用空间越大
• 创建索引和维护索引要耗时间,数据量越大,耗费时间越多
• 降低增删改的效率,每次增删改都会进行动态维护。

需要索引

• 字段有唯一性限制的,比如商品编码；
• 经常用于WHERE查询条件的字段,这样能够提高整个表的查询速度,如果查询条件不是一个字段,可以建立联合索引。
• 经常用于GROUP BY和ORDER BY的字段,这样在查询的时候就不需要再去做一次排序了,因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

不需要索引

• WHERE条件,GROUP BY,ORDER BY里用不到的字段,索引的价值是快速定位,如果起不到定位的字段通常是不需要创建索引的,因为索引是会占用物理空间的。
• 字段中存在大量重复数据,不需要创建索引,比如性别字段,只有男女,如果数据库表中,男女的记录分布均匀,那么无论搜索哪个值都可能得到一半的数据。在这些情况下,还不如不要索引,因为 MySQL 还有一个查询优化器,查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。
• 表数据太少的时候,不需要创建索引；
• 经常更新的字段不用创建索引,比如不要对电商项目的用户余额建立索引,因为索引字段频繁修改,由于要维护 B+Tree的有序性,那么就需要频繁的重建索引,这个过程是会影响数据库性能的。

优化索引

• 前缀索引优化；
• 覆盖索引优化；
• 主键索引最好是自增的；
• 防止索引失效；

前缀索引优化

以某个字段的前几个字符建立索引,减少索引字段的大小 缺点：

• order by 就无法使用前缀索引；
• 无法把前缀索引用作覆盖索引；

覆盖索引优化

将需要的字段建立成联合索引,可以直接从二级索引中查询到记录,避免回表操作,减少I/O操作。

主键索引

使用自增主键,可以让新记录按顺序添加,不用移动之前有的数据。而使用非自增主键,会导致页分裂,可能会造成大量的内存碎片,导致索引结构不紧凑,从而影响查询效率。

索引最好设置为NOT NULL

• 第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂,更加难以优化,因为可为 NULL 的列会使索引、索引统计和值比较都更复杂,比如进行索引统计时,count 会省略值为NULL 的行。
• 第二个原因：NULL 值是一个没意义的值,但是它会占用物理空间,所以会带来的存储空间的问题,因为 InnoDB 存储记录的时候,如果表中存在允许为 NULL 的字段,那么行格式中 1 字节空间存储 NULL 值列表

防止索引失效

索引失效：

• 当我们使用左或者左右模糊匹配的时候,也就是like %xx或者like %xx%这两种方式都会造成索引失效；
• 当我们在查询条件中对索引列做了计算、函数、类型转换操作,这些情况下都会造成索引失效；
• 联合索引要能正确使用需要遵循最左匹配原则,也就是按照最左优先的方式进行索引的匹配,否则就会导致索引失效。
• 在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。