MySQLInnoDB索引介绍以及在线添加索引实例分析

引言：MySQL之所以能成为经典，不是没有道理的，B+树足矣！

 

一、索引概念

InnoDB引擎支持三种常见的索引：B+树索引，全文索引和（自适应）哈希索引。B+树索引是传统意义上的索引，构造类似二叉树，从平衡二叉树演化而来，在InnoDB中使用较多，即一般意义上的索引构建都是B+树，所以这里主要介绍B+树索引。

索引是 应用程序设计和开发一个非常重要的方面。一般情况下，索引的添加可以提高查询性能，但也不是索引创建得越多越好，多了也会对性能造成一定的影响。所以找到一个平衡点也是关键。

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。我们常用的主键索引默认就是聚集索引，聚集索引只能是一个，所以其他创建的索引都是辅助索引，辅助索引数量理论上没限制；同时辅助索引也叫做非聚集索引。

二、索引的特点和区别

InnoDB一般索引都是B+树，一般树的高度都在2-4层。从书上描述看，不管是100万行还是1000万行树的高度也在这个范围。从B+树的构造原理看，应该没毛病。那么查找一个键值的行最多只需2-4次IO，即0.02-0.04秒，速度相当快。

上面已经讲到，不管是聚集索引还是辅助索引，它们的构造都是B+树；B+树还有一个特点，叶子节点存放所有索引的数据，非叶子节点存放部分数据，一般是部分索引的某个字段。这是它们的相同点，那么他们的区别呢？除了上述说的主键外，还有哪些区别？

最主要的区别是：

聚集索引的叶子节点存放的是整行数据，也就是说如果有聚集索引，那么聚集索引的整棵树存放了所有数据。

而辅助索引的叶子节点存放的只是一行的部分数据，就是说只存放了非聚集索引定义的那一列或者几列的信息。

当然还有其他区别，构造的原则或者说约束等，但我想不是最重要的，在这里就不铺开叙述了。

三、联合索引

 当我们创建一个普通的索引存在多列时，就是联合索引。

这里单独拿出来讲一下，是因为有个非常值得注意的事项。

例如创建如下一条索引，有三列，即联合索引。

create index DevInfoIndex on FilesInfo (CamID, SliceStartTime, SliceStopTime);

当用单个CamID作为条件进行查询时，没有问题，有用到索引。

但是如果用单个SliceStartTime作为条件进行查询时，用explain工具会发现根本没有用到这个索引DevInfoIndex ！

联合索引有最左匹配原则，具体可以分解成以下三条：

 

a.如果不是按照最左开始查找，无法使用索引
b.不能跳过中间列
c.某列使用范围查询，后面的列不能使用索引

以上测试的属于a类情况。

 

四、覆盖索引

如果我们将上例的第二条语句的*改一下：

 可以发现这次用了索引。type不再是ALL全表扫描了。这就是覆盖索引。

 定义就是，查询可以从辅助索引中获得，而不需要查询聚集索引中的记录。

那么我们在索引设计时应尽量覆盖我们所需的或者经常用到的字段。而我们查询语句应尽量不用*，尽量只用我们索引定义的字段。

五、在线添加索引实例分析 

熟悉了上述原理后，我准备在本地测试下在线添加索引。据书中描述MySQL从5.6开始支持在线索引添加OnlineDDL，我机器上版本5.6.27。

1. 首先我们看下文件列表，注意大小；而当前时间3月6日的上午将近10点。

[root@localhost mysql]# pwd
/var/lib/mysql

[root@localhost mysql]# ll
总用量 197452
-rw-rw---- 1 mysql mysql 56 2月 27 13:28 auto.cnf
-rw-rw---- 1 mysql mysql 79691776 3月 6 09:40 ibdata1
-rw-rw---- 1 mysql mysql 50331648 3月 6 09:40 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 3月 2 13:20 ib_logfile1
-rw-r----- 1 mysql mysql 29436 2月 27 14:24 localhost.localdomain.err
-rw-rw---- 1 mysql mysql 5 3月 4 17:29 localhost.localdomain.pid
drwx--x--x 3 mysql mysql 4096 3月 2 13:30 mysql
srwxrwxrwx 1 mysql mysql 0 3月 4 17:29 mysql.sock
drwx------ 2 mysql mysql 4096 3月 2 11:37 NVRRecordFiles
drwx------ 2 mysql mysql 4096 2月 27 15:15 performance_schema
[root@localhost mysql]#
[root@localhost mysql]#
[root@localhost mysql]# ll NVRRecordFiles/
总用量 1376
-rw-rw---- 1 mysql mysql 8790 3月 2 11:37 BadFiles.frm
-rw-rw---- 1 mysql mysql 98304 3月 6 09:40 BadFiles.ibd
-rw-rw---- 1 mysql mysql 61 3月 2 11:37 db.opt
-rw-rw---- 1 mysql mysql 9250 3月 2 11:37 FilesInfo.frm
-rw-rw---- 1 mysql mysql 950272 3月 6 09:40 FilesInfo.ibd

.....

 

2. 我们进入mysql下查测试数据库NVRRecordFiles和测试表FilesInfo的信息。

 查表的索引，除主键目前只有一条索引包括三列：

mysql> show index from NVRRecordFiles.FilesInfo;
+-----------+------------+--------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| FilesInfo | 0 | PRIMARY | 1 | FileId | A | 1569 | NULL | NULL | | BTREE | | |
| FilesInfo | 1 | DevInfoIndex | 1 | CamID | A | 60 | NULL | NULL | | BTREE | | |
| FilesInfo | 1 | DevInfoIndex | 2 | SliceStartTime | A | 1569 | NULL | NULL | | BTREE | | |
| FilesInfo | 1 | DevInfoIndex | 3 | SliceStopTime | A | 1569 | NULL | NULL | | BTREE | | |
+-----------+------------+--------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)

mysql>

然后我们在查下当前表的长度和索引的长度。

mysql> show table status;
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| BadFiles | InnoDB | 10 | Compact | 20 | 819 | 16384 | 0 | 0 | 0 | NULL | 2020-03-02 11:37:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| ContinueTransInfo | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-03-02 11:37:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| FilesInfo | InnoDB | 10 | Compact | 1569 | 240 | 376832 | 0 | 196608 | 0 | NULL | 2020-03-02 11:37:59 | NULL | NULL | latin1_swedish_ci | NULL | | |

......
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
5 rows in set (0.00 sec)

mysql>

 

3. 发现运行没问题，从2020-3-2到现在数据和索引长度基本没有变化，因为我测试程序已经进入稳定运行的阶段。

此时，我们在线添加一条索引，此时约11点。

mysql> ALTER TABLE  FilesInfo ADD INDEX FileNameIndex (FileStartTime, CamID, DiskID, DiskPath, NVRIP, CamID, FileType);

这次我加多了些字段。也可以看出我们的字段远远不止三个。

然后我们查下索引，已经出现了。

mysql> show index from FilesInfo;
+-----------+------------+---------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+---------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| FilesInfo | 0 | PRIMARY | 1 | FileId | A | 1508 | NULL | NULL | | BTREE | | |
| FilesInfo | 1 | DevInfoIndex | 1 | CamID | A | 58 | NULL | NULL | | BTREE | | |
| FilesInfo | 1 | DevInfoIndex | 2 | SliceStartTime | A | 1508 | NULL | NULL | | BTREE | | |
| FilesInfo | 1 | DevInfoIndex | 3 | SliceStopTime | A | 1508 | NULL | NULL | | BTREE | | |
| FilesInfo | 1 | FileNameIndex | 1 | FileStartTime | A | 1508 | NULL | NULL | | BTREE | | |
| FilesInfo | 1 | FileNameIndex | 2 | DiskID | A | 1508 | NULL | NULL | | BTREE | | |
| FilesInfo | 1 | FileNameIndex | 3 | DiskPath | A | 1508 | NULL | NULL | | BTREE | | |
| FilesInfo | 1 | FileNameIndex | 4 | NVRIP | A | 1508 | NULL | NULL | | BTREE | | |
| FilesInfo | 1 | FileNameIndex | 5 | CamID | A | 1508 | NULL | NULL | | BTREE | | |
| FilesInfo | 1 | FileNameIndex | 6 | FileType | A | 1508 | NULL | NULL | | BTREE | | |
+-----------+------------+---------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.00 sec)

mysql>

我们再来一句查询命令：

mysql> explain select FileStartTime, DiskID from FilesInfo where FileStartTime <1583446613;
+----+-------------+-----------+-------+---------------+---------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------------+---------+------+------+--------------------------+
| 1 | SIMPLE | FilesInfo | range | FileNameIndex | FileNameIndex | 4 | NULL | 927 | Using where; Using index |
+----+-------------+-----------+-------+---------------+---------------+---------+------+------+--------------------------+
1 row in set (0.02 sec)

mysql>

新加的索引生效了。

那么有多大呢？

此时约11：30

mysql> show table status;
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| BadFiles | InnoDB | 10 | Compact | 20 | 819 | 16384 | 0 | 0 | 0 | NULL | 2020-03-02 11:37:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| ContinueTransInfo | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-03-02 11:37:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| FilesInfo | InnoDB | 10 | Compact | 1569 | 240 | 376832 | 0 | 196608 | 0 | NULL | 2020-03-06 11:04:00 | NULL | NULL | latin1_swedish_ci | NULL | | |

......
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
5 rows in set (0.00 sec)

mysql>

数据没变化，那是可以理解的，为啥索引还是没变化呢？不是生效了吗？ 嗯。。按书中描述，先放入缓冲，再写到文件中的。而且我们可以注意到，时间已经在更新了！再等等。

 

4. 中午午休起来，约13：05，再查：

mysql> show table status;
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| BadFiles | InnoDB | 10 | Compact | 20 | 819 | 16384 | 0 | 0 | 0 | NULL | 2020-03-02 11:37:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| ContinueTransInfo | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-03-02 11:37:59 | NULL | NULL | latin1_swedish_ci | NULL | | |
| FilesInfo | InnoDB | 10 | Compact | 1507 | 239 | 360448 | 0 | 393216 | 0 | NULL | 2020-03-06 11:04:00 | NULL | NULL | latin1_swedish_ci | NULL | | |

......
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
5 rows in set (0.00 sec)

mysql>

这次我们可以看到，时间没变了，添加的索引就是11：04分。而大小变了，可是，怎么比全表还大，而且全表怎么稍微变少了。

回答第一个问题：我们从上面看出，新加的索引有6个字段，可以说原来第一个辅助索引的两倍，而196608 *3=589824，目前的393216 还是少于这个值。当然两个表的字段加起来小于全表的字段，但是表的存储就不是1+1=2了，还有其他一些信息。

回答第二个问题：全表变少，可以看它的前两项一个数据，1507，也是变小了，这是表示行数。也就是将近减少了60行数据。所以表也相应变小了。

这两个问题恰恰说明了，在线索引创建是需要时间的，测试是真实的数据。

 

这是在线索引添加的整个过程，希望对你理解索引的原理有所帮助；有问题欢迎讨论。 

 

参考书《MySQL技术内幕InnoDB存储引擎》（第二版）姜承尧著。

 

====================add on 2020.07.31====================

索引及数据类型的使用建议：

 

 联合索引：优于多列独立索引

 索引顺序：选择性高的在前面

 覆盖索引：Key里面包含要查询的数据

 索引排序：索引同时满足查询和排序

  

　数据库字符集使用utf8mb4；

 VARCHAR按实际需要分配长度；

 文本字段建议使用VARCHAR；

 时间字段建议使用long；

 bool字段建议使用tinyint；

 枚举字段建议使用tinyint；

 交易金额建议使用long；

 禁止使用“%”前导的查询；

 禁止在索引列进行数学运算，会导致索引失效；

　　 select * from t1 where id+1 >1121 不会使用索引
　　 select * from t1 where id >1121 - 1 会使用索引

 

 表必须有主键，建议使用业务主键；

 单张表中索引数量不超过5个；

 单个索引字段数不超过5个；

 字符串索引使用前缀索引，前缀长度不超过10个字符；