Mysql的索引总结及索引ABC问题

柔情只为你懂 2023-10-07 13:46 21阅读 0赞

#### MYSQL的索引 ####

*   *   *   *  一 : 索引种类
             *   *   *  1 : 单列索引
                     *  2 : 聚合索引
                     *  3 : 普通索引
                     *  4 : 主键索引
                     *  5 : 唯一索引
                     *  6 : 全文索引
             *  二 : 索引方法
             *   *   *  1 : BTREE索引
                     *  2 : HASH索引
             *  三 : 引擎类型
             *   *   *  1 : Innodb
                     *  2 : MyISAM
             *  四 : 实现原理图
             *   *   *  1 : 索引图结构
                     *  2 : 索引图说明
             *  五 : 聚合索引 ABC 问题
             *   *   *  1 : 聚合索引原则
                     *  2 : 测试表结构
                     *  3 : 聚合索引组合顺序
                     *  4 : 聚合索引测试结果
                     *  5 : 聚合索引总结

##### 一 : 索引种类 #####

###### 1 : 单列索引 ######

即以表的单列字段创建的索引。

###### 2 : 聚合索引 ######

即以表的多列字段创建的索引，该索引在使用时，从左字段顺序才会生效，遵循最左匹配原则。

###### 3 : 普通索引 ######

即不是主键，又不是唯一的索引。

###### 4 : 主键索引 ######

表主键自动生成的索引，即在定义表结构是设置的PRIMARY KEY所生成的索引。
    如果表未定义主键，则查找该表中是否存在非空、整形、唯一索引作为其主键。
    主键可通过`select _rowid from table名称` 查看。
    若以上都不满足，mysql会生成一个rowid作为主键，无法直接查到。

###### 5 : 唯一索引 ######

表的唯一列生成的索引，可为空值。

###### 6 : 全文索引 ######

将存储于数据库的字符串中任意内容查找出来，如大量级的文字中如like %关键字%
    普通索引的效率与全文索引相比是非常低的。

##### 二 : 索引方法 #####

![在这里插入图片描述][2020061615542737.png]

###### 1 : BTREE索引 ######

mysql数据库最常用的索引算法。
    因为它不仅可以被用在=，>，>=，<，<=和between这些比较操作符上，而且还可以用于like操作符。
    只要它的查询条件不是一个以通配符开头的常量。

###### 2 : HASH索引 ######

Hash索引仅仅能满足“=”，“IN”，“<=>”查询，不能使用范围查询。
    联合索引中，Hash索引不能利用部分索引键查询。
    Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高。

##### 三 : 引擎类型 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjU0NjM1_size_16_color_FFFFFF_t_70]

###### 1 : Innodb ######

以上图为例，test表使用InnoDB引擎，生成两个文件。
    test.ibd 存储表索引及表数据。
    test.frm 存储表结构信息。

###### 2 : MyISAM ######

以上图为例，test表使用MyISAM引擎，生成三个文件。
    testa.MYI 存储表索引，文件名后缀的 I 即为index索引。
    testa.MYD 存储表数据，文件名后缀的 D 即为data数据。
    testa.frm 存储表结构信息。

##### 四 : 实现原理图 #####

###### 1 : 索引图结构 ######

Mysql索引B+ tree底层实现原理如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjU0NjM1_size_16_color_FFFFFF_t_70 1]

###### 2 : 索引图说明 ######

Mysql主键会自动添加索引，innoDB表必须有主键，并且推荐使用整型的自增主键。  
B+树，整型是因为B+树需要比较大小，整型比字符串快，占用字节小，节约磁盘空间。自增是因为UUID这种会导致树的分裂，平衡，自增的会往后顺序增  
如图所示，18 -> 24 中间有一个箭头，类似于C语言中的指针，当需要取10至40的数据的时候，就可以根据指针框定范围。

##### 五 : 聚合索引 ABC 问题 #####

###### 1 : 聚合索引原则 ######

组合索引的生效原则是  
从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用。

在MySQL中，如果创建了复合索引(A, B, C)，就相当于创建了(A, B, C)、(A, B)和(A)三个索引，这被称为复合索弓|前导列特性，因此在创建复合索索引时应该将最常用作查询条件的列放在最左边，依次递减。

###### 2 : 测试表结构 ######

CREATE TABLE `TEST` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `A` varchar(30) COLLATE utf8_bin DEFAULT NULL,
      `B` varchar(30) COLLATE utf8_bin DEFAULT NULL,
      `C` varchar(30) COLLATE utf8_bin DEFAULT NULL,
      PRIMARY KEY (`ID`) USING BTREE,
      KEY `TEST_INDEX` (`A`,`B`,`C`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

###### 3 : 聚合索引组合顺序 ######

A B C 组合顺序如下
     
     A
     A B
     A C
     A B C
     A C B
     
     B
     B A
     B C
     B A C
     B C A
     
     C
     C A
     C B
     C A B
     C B A

###### 4 : 聚合索引测试结果 ######

测试结果如下所示 Y：是，N：否

EXPLAIN select * from test t where A = 1; -- Y
    EXPLAIN select * from test t where A = 1 and B = 1; -- Y
    EXPLAIN select * from test t where A = 1 and C = 1; -- Y
    EXPLAIN select * from test t where A = 1 and B = 1 and C = 1; -- Y
    EXPLAIN select * from test t where A = 1 and C = 1 and B = 1; -- Y
    
    EXPLAIN select * from test t where B = 1; -- N
    EXPLAIN select * from test t where B = 1 and A = 1; -- Y
    EXPLAIN select * from test t where B = 1 and C = 1; -- N
    EXPLAIN select * from test t where B = 1 and A = 1 and C = 1; -- Y
    EXPLAIN select * from test t where B = 1 and C = 1 and A = 1; -- Y
    
    EXPLAIN select * from test t where C = 1; -- N
    EXPLAIN select * from test t where C = 1 and A = 1; -- Y
    EXPLAIN select * from test t where C = 1 and B = 1; -- N
    EXPLAIN select * from test t where C = 1 and A = 1 and B = 1; -- Y
    EXPLAIN select * from test t where C = 1 and B = 1 and A = 1; -- Y
    
    -- 其余查询方式
    EXPLAIN select * from test where A > 3; -- Y
    EXPLAIN select * from test where A = 3 order by B; -- Y
    EXPLAIN select * from test where A = 3 order by C; -- Y
    EXPLAIN select * from test where C = 3 order by A; -- N

![是否走索引的标志][20200616155140571.png]

###### 5 : 聚合索引总结 ######

Mysql表结构以test表为例，通过测试，得出结论：  
1.查询的条件，Mysql会根据索引排序。  
2.由于聚合索引(A, B, C)，就相当于创建了(A, B, C)、(A, B)和(A)三个索引，所以只要条件中有(A, B, C)、(A, B)或(A)，就会走索引。  
3.建议建表测试，更加直观。

[2020061615542737.png]: https://img-blog.csdnimg.cn/2020061615542737.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjU0NjM1_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200616162645219.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjU0NjM1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjU0NjM1_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200616161442924.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjU0NjM1,size_16,color_FFFFFF,t_70
[20200616155140571.png]: https://img-blog.csdnimg.cn/20200616155140571.png