《MySQL中级系列教程-第八章-其他索引》

超、凢脫俗 2023-01-02 11:28 110阅读 0赞

# 《MySQL中级系列教程-第八章-其他索引》 #

# 一、覆盖索引 #

## 1.1 覆盖索引概念 ##

**覆盖索引只是一个概念**，MySQL官方并没有说明什么是覆盖索引，只为了表达一下"该SQL语句是使用索引覆盖查询的"，仅此而已；

覆盖索引（或称索引覆盖）：即从二级索引中就可以得到要查询的记录，而不需要查询聚簇索引中的记录（回表查询），很显然，聚簇索引就是一种覆盖索引，因为聚簇索引中包含了数据行的全部数据，而非聚集索引的话，要看SQL语句查询的列是在索引树上，如果不在则需要回表查询；简单的说就是**查询的列要被所使用的索引覆盖**，换句话说就是**查询的列要在索引树上，不需要回表查询**。

## 1.2 覆盖索引应用 ##

使用覆盖索引的SQL语句：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

我们给username列加上索引：

create index idx_name on t_user(username);

*  执行如下SQL：

-- username创建的B+Tree上有值（使用了覆盖索引）
    explain select username from t_user where username='xxx';
    
    -- username创建的B+Tree上有值（使用了覆盖索引）
    explain select id,username from t_user where username='xxx';
    
    -- username创建的B+Tree上没有值,age列需要回表到聚集索引上去查询（没有使用覆盖索引）
    explain select id,username,age from t_user where username='xxx';

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

> 上面提一下，有得时候MySQL的执行计划不是非常准确，因为MySQL底层有优化器来优化SQL，所以我们看到的执行计划的信息有得时候可能不是很准确

## 1.3 覆盖索引总结 ##

**覆盖索引：查询的数据被索引树覆盖了，即：查询的数据在索引树上，不需要回表查询；**

# 二、前缀索引 #

## 2.1 前缀索引概念 ##

**前缀索引也是一种概念，或者说是操作索引的一种技巧**；当索引列的数据是非常大时，那么该列建立的索引会非常大，而且检索速度也会很慢，这个时候我们考虑能否让该列的前面几个字符拿出来建立索引，而不是整列是数据建立索引，因此前缀索引的概念就由此产生；

我们知道前缀索引其实就是拿出该列数据的前几个字符出来建立索引来降低索引的大小，以及加快索引的速度的一种技巧性索引，**但是毕竟前面几个字符不能够代替整列数据**，有可能重复，我们应该尽量的减低重复的概率，提高不重复的概率，这样的前缀索引检索速度更快；

## 2.2 前缀索引的应用 ##

*  数据准备：

CREATE TABLE `student`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `birthday` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB ;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES (1, '小明', '1999-10-20');
    INSERT INTO `student` VALUES (2, '小军', '1999-02-21');
    INSERT INTO `student` VALUES (3, '小龙', '1999-01-19');
    INSERT INTO `student` VALUES (4, '小刚', '1999-06-06');
    INSERT INTO `student` VALUES (5, '小红', '1999-02-05');

*  计算不重复率：

mysql> select 1.0*count(distinct birthday)/count(*) from student;
    +---------------------------------------+
    | 1.0*count(distinct birthday)/count(*) |
    +---------------------------------------+
    |                               1.00000 |
    +---------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select 1.0*count(distinct left(birthday,1))/count(*) from student;
    +-----------------------------------------------+
    | 1.0*count(distinct left(birthday,1))/count(*) |
    +-----------------------------------------------+
    |                                       0.20000 |
    +-----------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select 1.0*count(distinct left(birthday,2))/count(*) from student;
    +-----------------------------------------------+
    | 1.0*count(distinct left(birthday,2))/count(*) |
    +-----------------------------------------------+
    |                                       0.20000 |
    +-----------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select 1.0*count(distinct left(birthday,3))/count(*) from student;
    +-----------------------------------------------+
    | 1.0*count(distinct left(birthday,3))/count(*) |
    +-----------------------------------------------+
    |                                       0.20000 |
    +-----------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select 1.0*count(distinct left(birthday,4))/count(*) from student;
    +-----------------------------------------------+
    | 1.0*count(distinct left(birthday,4))/count(*) |
    +-----------------------------------------------+
    |                                       0.20000 |
    +-----------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select 1.0*count(distinct left(birthday,5))/count(*) from student;
    +-----------------------------------------------+
    | 1.0*count(distinct left(birthday,5))/count(*) |
    +-----------------------------------------------+
    |                                       0.20000 |
    +-----------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select 1.0*count(distinct left(birthday,6))/count(*) from student;
    +-----------------------------------------------+
    | 1.0*count(distinct left(birthday,6))/count(*) |
    +-----------------------------------------------+
    |                                       0.40000 |
    +-----------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select 1.0*count(distinct left(birthday,7))/count(*) from student;
    +-----------------------------------------------+
    | 1.0*count(distinct left(birthday,7))/count(*) |
    +-----------------------------------------------+
    |                                       0.80000 |
    +-----------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select 1.0*count(distinct left(birthday,8))/count(*) from student;
    +-----------------------------------------------+
    | 1.0*count(distinct left(birthday,8))/count(*) |
    +-----------------------------------------------+
    |                                       0.80000 |
    +-----------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select 1.0*count(distinct left(birthday,9))/count(*) from student;
    +-----------------------------------------------+
    | 1.0*count(distinct left(birthday,9))/count(*) |
    +-----------------------------------------------+
    |                                       1.00000 |
    +-----------------------------------------------+
    1 row in set (0.00 sec)

发现不重复率在`birthday`字段在第9个字符时，达到100%，也就是说，在的倒数第二个字符时，数据没有重复的。

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

计算出不重复率最高时是在该列的低9个字符，因此我们可以把第9个字符之外的数据不用建立索引，来降低索引的大小，提高索引的检索速度；

alter table student add key(birthday(9));

下次根据birthday字段查询，会发现使用了索引查询：

explain select birthday from student where birthday='1999-02-21';

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

## 2.3 如果建立的前缀索引有重复数据会怎么样？ ##

我们刚刚建立前缀索引时，首先要计算不重复率，然后再根据得出合适的位置来建立索引，那么如果不计算不重复率会怎么样？？

我们把刚刚建立的索引删除，在第4个字符位置上建立索引

alter table student drop index birthday;
    
    alter table student add key(birthday(4));

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

执行如下SQL分析执行计划：

explain select birthday from student where birthday='1999';
    
    explain select birthday from student where birthday='1999-02-21';
    
    explain select birthday from student where birthday='1999-10-21';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 4]  
我们随机执行了几条SQL语句，发现都没有走索引查询，而是全表顺序扫描，因为重复的数据太多了（占整表数据），MySQL优化器认为走索引还不如不走索引，因此选择顺序扫描查询；

我们切换个案例，把索引前缀字符切换为7：

alter table student drop index birthday;
    
    alter table student add key(birthday(7));

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 5]  
执行如下SQL：

explain select birthday from student where birthday='1999-10-21';
    
    explain select birthday from student where birthday='1999';

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

# 三、辅助索引 #

辅助索引就是辅助我们查询的所有，一般指的就是非聚集索引（二级索引）

**辅助索引就是二级索引，so，就这么简单！！！！** 关于二级索引的概念不用再说了吧？？

# 四、索引的类型 #

到这里我们差不多已经了解了学习上、工作上绝大多数索引了，包括什么B-Tree索引、B+Tree索引、R-Tree索引、Hash索引、全文索引、聚集索引、唯一索引、二级索引、一级索引、覆盖索引、前缀索引。

除此之外我们应该还有听说过复合索引、唯一索引、普通索引、主键索引、非唯一索引…

**这么多索引我们该怎么记？？以及该如何区分？？**

我们看待事物的角度不同，索引也可以分为以下角度：

*  **数据结构角度**：
    
     *  **B+Tree索引**
     *  **R-Tree索引（空间索引）**
     *  **Hash索引**
     *  **Full-text索引（全文索引）**
 *  **物理存储角度**
    
     *  **聚集索引 （一级索引）**
     *  **非聚集索引（二级索引）**
 *  **逻辑角度**
    
     *  **主键索引**
     *  **普通索引（单列索引）**
     *  **多列索引（复合索引）**
     *  **唯一索引**
     *  **非唯一索引**
     *  **空间索引**
 *  **业务角度**
    
     *  **覆盖索引**
     *  **前缀索引**
     *  **辅助索引（非聚集索引）**

**我们平常所说的索引，如果没有特别指明，都是指B+Tree结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。**

# 五、总结 #

*  **树的演变：**二叉树演变平衡二叉树（AVL树）、平衡二叉树演变多路平衡搜索树（B-Tree）、B-Tree演变B+Tree

--------------------

*  **B+Tree相对于B-Tree的改进：**将数据都存放在叶子节点，让每一个磁盘块都能够存储更多的指针，减少IO检索次数，并且叶子节点都加上循环链表，提高区间访问能力

--------------------

*  **操作系统的局部性原理：**计算机操作系统每一次IO时，不仅仅把当前磁盘地址的数据加载到内存，同时也把相邻数据也加载到内存缓冲区中

--------------------

*  **InnoDB数据页管理：**InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB，因此B+Tree数据结构可以使InnoDB每次读取一页时可以获取更多的指针，查询更多的数据

--------------------

*  **聚集索引与非聚集索引：**聚集索引的数据和指针是存储在一起的，非聚集索引的B+Tree上只存储索引列的值与主键值，当使用非聚集索引查询数据时，如查询的列包含其他列数据，则需要借助主键回表查询；

--------------------

*  **MyISAM引擎为什么不可以建立聚集索引？：**MyISAM存储引擎有单独的空间存储表数据，表数据并不是和主键建立的B+Tree存储在一起的，在MyISAM引擎中任何的索引都需要回表查询，具体参考`3.5.1`小节的：**MyISAM存储引擎B+Tree底层实现原理图**

--------------------

*  **索引组织表：**MySQL中的表的所有的数据都是按照主键排序的方式存储在一颗B+Tree上（InnoDB引擎），所以MySQL也叫索引组织表；

--------------------

*  **InnoDB能不能没有主键？：**InnoDB必须要有且只有一个主键（聚集索引），不能没有主键，具体为什么参考`3.6`小节

--------------------

*  **什么是一级索引索引？什么是二级索引？：**一级索引就是聚集索引，二级索引就是非聚集索引，MyISAM引擎不可建立一级索引，建立的都是二级索引

--------------------

*  **索引覆盖：**覆盖索引只是一个概念，即查询的数据列在二级索引上，不需要回表查询，此时就说查询的数据"被索引覆盖了"，也叫索引覆盖，或称覆盖索引。聚集索引就是一种覆盖索引，因为聚集索引上包含了整行的数据；

--------------------

*  **前缀索引：**前缀索引也是一种概念，或者说是操作索引的一种技巧，当索引列非常大时，我们可以采取索引列数据的前面几个字符来建立索引，此时建立的索引就称之为前缀索引，注意前缀索引需要计算不重复率；

--------------------

*  **辅助索引：**辅助索引，顾名思义就是辅助我们查询的所有，所有的二级索引都成为辅助索引；

--------------------

*  **索引的分类：**索引的类型有非常非常多，我们刚刚也举例了，但我们可以按照不同的角度去看待索引，把索引归类好，其实索引也不难记住；一般划分为：数据结构角度、物理存储角度、逻辑角度、业务角度等；

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70]: /images/20221119/89d54cfc09c0411a82c06439b85cb6e8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 1]: /images/20221119/9a3c150bc74a459ca2db746f5fbb5164.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 2]: /images/20221119/2f8c40a1a4dc4bcf87f1901510f7e5de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 3]: /images/20221119/aa9d16a28ba44b0cbd328b9dbf85700f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 4]: /images/20221119/5578bbf7b8744371a7608e690856bc74.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 5]: /images/20221119/d0fc16d68f184b70a2ea4a43ceff7330.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 6]: /images/20221119/4fc058d7183d4c87bf6e25a43b76c35b.png