学习之旅8-mysql索引（一）

• 引入： 我们平时在看一本书时，为了快速找到我们需要的章节，我们通常会通过目录来定位，对于mysql而言，索引就相当于目录，索引是存储引擎用于快速找到记录的一种数据结构。那么索引的原理以及索引有哪些，如何建立索引，使用索引呢？下面我们就具体学习关于mysql索引。

1、索引介绍

1.1、初识索引

• 什么是索引： 对应mysql而言，索引其实就本质上就是一种数据结构，目的是为了让我们高效的获取数据，提高数据查询速度。通俗来说，索引就好比一本书的目录。
• 索引文件： 索引也会很大，不可能都存放到数据内存中，因此索引往往是存储在磁盘上的文件中。对于不同的存储引擎，文件类型不同。（可能存储在单独的索引文件.myi 中，也可能和数据一起存储在数据文件）
  InnoDB存储引擎：（MySQL-5.5 以及之后版本默认存储引擎）： .frm文件：存储表结构（列，列属性） .ibd文件：存储的数据记录和索引 ibdata1文件（数据目录中）：数据字典信息
  MyISAM存储引擎： .frm文件：存储表结构（列，列属性） .ibd文件：存储的数据记录 .myi文件：存储索引
• 索引优劣： 优点：1）加快数据的查询速度，加速表与表之间的连接。2）在使用分组排序子句进行数据检索时，也可以减少查询中分组和排序时间。3）使用优化隐藏器，提高系统的性能，降低数据库IO成本，减少CPU消耗。缺点：1）索引也是数据文件，也会占用磁盘空间。2）对于增删改（insert、delete、update）操作，索引会降低速度，因为mysql要将变动的数据写入数据文件中，还有将这些变动一并写入索引文件。3）创建、维护索引也是一项耗时耗力的工程。

1.2、索引执行

• 引入： 从计算机层面开始说一下一个索引数据加载的流程（即IO）

• 关于磁盘IO： 磁盘读取数据靠的是机械运动，每一次读取数据需要寻道、寻点、拷贝到内存三步操作。寻道时间是磁臂移动到指定磁道所需要的时间，一般在5ms以下；寻点是从磁道中找到数据存在的那个点，平均时间是半圈时间，如果是一个7200转/min的磁盘，寻点时间平均是600000/7200/2=4.17ms；拷贝到内存的时间很快，和前面两个时间比起来可以忽略不计，所以一次IO的时间平均是在9ms左右。听起来很快，但数据库百万级别的数据过一遍就达到了9000s，明显不利于数据查询，消耗内存且时间漫长。
  
  综上考虑，为了减少磁盘IO，计算机一般会做预处理，即做一次数据读取（IO）时，不仅仅把当前磁盘地址的数据读取到内存缓存区，还会把相邻的数据也读取到内存缓冲区。每一次IO读取的数据称之为一页（page）（4K或8K，与操作系统有关）,一个页默认16KB，是连续的4个block（块） 4*4B，最小IO单元。也就是说我们读取一页的数据时，实际上就发生了一次IO。因此为了减少IO次数，快速查询数据，就出现了上述所讲的索引。

• BTree索引

二叉树 ：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。这样就是为了查找数据的时候折半查找，减少IO的次数，但是会出现右边不分叉的情况，这样就不稳定。
平衡二叉树：树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

时间复杂度：时间复杂度是一个函数，它定量描述了该算法的运行时间，假如不用二分法查询，对于1亿条数据，需要一条一条查找，执行1亿次IO，以现在磁盘的IO能力和CPU的运算能力基本几个月后才有结果。对于平衡二叉树用大O标记法就是O(log n)，n是记录总树，底数是树的分叉数，结果就是：$lo{g}_a^b$（a为分叉次数，b为数据记录数），若有10层，则查找次数从亿降到了个位数。因此，利用索引会使数据库查询有惊人的性能提升
B树：改造二叉树：有上述关于磁盘IO 我们可以知道，mysql每次IO就会读取一页（page）16K的数据，而对于二叉树一次IO只能获取的数据量仅有16字节，空间利用率很低。因此为了最大化IO效率，简单的做法就是在每个节点存放尽可能多的数据。每个节点存放1000个索引（1000*16K=15.625M），通过增加分叉来降低层数。100万条数据只需要2层即可（即每个节点1000条），这样2次磁盘IO即可查询到数据，查询效率进一步提高。B树的非叶子节点和叶子节点都会存储数据。

select * from table where id=10;此时查询路径为：磁盘1——>磁盘2——>磁盘6。
第1次IO：将磁盘1加载到内存，在内存中从头遍历，10<15,走左子树，寻找到磁盘2。
第2次IO：将磁盘2加载到内存，在内存中从头遍历，7<10,走右子树，寻找到磁盘6。
第3次IO：将磁盘6加载到内存，在内存中从头遍历，8<10，10=10，找到10后取出对应data，如果data存储的就是对应行记录，那直接取出data返回，查询结束；若data存储是是磁盘地址，此时则需要根据磁盘地址到对应磁盘中获取数据（回表操作），最终返回对应值，查询结束。
但是对应范围的查找，B树很明显还存在优化的地方，比如查询10-27之间的数据，查找到15后，需要回到根节点重新遍历（回表操作），这样又再次加大了IO次数。另外B树种每个节点都存放的data，若data都是行数据，那对于数据量大的行数，除了存放索引还有数据文件，这样的话一个page中真正可以存储的数据量就大大减少了，进而增加树的分叉（增加层数），磁盘IO次数也就相应变大了。

B+树： MySQL的索引就采用了B+树的数据结构。B+树与B树最大的区别就在于B+树只有叶子节点才会存储数据，非叶子节点至存储键值，叶子节点采用双向指针链接，这样能减少回表，进一步加快数据查询。

假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。
首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO。
查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。
第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。
主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。

1.3、索引管理

• 索引查询

-- 首先查询是否有索引
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   | MUL |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.05 sec)
PRI	     --->	主键索引
MUL	     --->   辅助索引
UNI	     --->	唯一索引

-- 也可以通过查询建表语句和show index来查询索引的详细信息
mysql> show create table city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  KEY `in_name` (`Name`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | in_name     |            1 | Name        | A         |        3998 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

• 索引增删

-- 建表时创建索引 
mysql> CREATE TABLE city(id INT NOT NULL,city VARCHAR(200) NOT NULL, pos VARCHAR(200),INDEX name_idx(city));

-- 建表后创建索引
mysql> CREATE INDEX id_idx ON city(id);
--单列索引
mysql> ALTER TABLE city ADD INDEX pos_idx(pos);
--多列联合索引
mysql> ALTER TABLE city ADD INDEX idx_c_o(city,pos);
--唯一索引（只能建在unique字段上）
mysql> ALTER TABLE city  ADD UNIQUE INDEX idx_uni(id);
--前缀索引（字符串字段，选择前n个字符）
mysql> ALTRT TABLE city ADD INDEX idx_pos(pos(10));

-- 通过show create table city 查看建表信息，查看索引主键等

-- 通过索引名称删除对应索引
ALTER TABLE city DROP INDEX pos_idx;

注意：一般来说索引对应的字段需要加上(length)，如果字段数据是char，varchar类型，可以指定length，其值小于字段的实际长度，如果是blob和text类型就必须指定length。

关于length： 对于普通索引，我们是可以创建在任何字段上面的，但是对于长文本的字段，若在该字段上建立索引，那这个索引会增加索引的存储空间并且降低索引的效率，此时利用length可以指定该字段中前n个字符作为索引（前缀索引），这样可以节约索引空间，节省大量磁盘空间，进而提高索引效率。

mysql> ALTRT TABLE city ADD INDEX idx_pos(pos(10));

PS： 虽然使用字段的部分数据创建前缀索引能减少索引文件大小，节省磁盘空间，提高insert操作的速度，但是MySQL无法使用前缀索引做order by 和group by 以及使用前缀索引做覆盖扫描。

覆盖扫描： 当索引中的列包含所有查询中要使用的列的时候，就会用到覆盖索引，效率比较高。
（1）就是select的数据列（即查询的列）只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
（2） 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
（3）是非聚集组合索引的一种形式，它包括在查询里的select、join和where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[where子句]中所涉及的字段，即索引包含了查询正在查找的所有数据）

1.4、索引分类

按存储引擎分： MyISAM索引和InnoDB索引，关于存储引擎的详细介绍，后面的文章会单独再分析，下面仅仅关注这两种存储引擎对应的索引实现。

1）MyISAM 索引（非聚集索引）：在 MySQL5.5之前的版本中，MyISAM 是默认的存储引擎。前面我们提到过 对应MyISAM 存储引擎，它的数据物理存储结构为：
.frm文件：存储表结构（列，列属性）；
.ibd文件：存储的数据记录 ；
.myi文件：存储索引。
即MyISAM 存储引擎数据文件和索引文件分开存放，构建B+树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。表t_user的索引存储在索引文件user.MYI中，数据文件存储在数据文件 t_user.MYD中。

关于磁盘IO情况：

-- 创建存储引擎为MYISAM的t_user 表，xh为主键（自增），name列为普通索引
CREATE TABLE t_user(
xh INT NOT NULL AUTO_INCREMENT COMMENT '学号',
name VARCHAR(200) NOT NULL COMMENT '姓名',
banji ENUM('一班','二班','三班') NOT NULL COMMENT '班级',
sex ENUM('男','女') DEFAULT '男' NOT NULL COMMENT '性别',
age INT NOT NULL COMMENT '年龄',
PRIMARY KEY (xh),
KEY idx_name(NAME))ENGINE = MYISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
mysql> select * from t_user ;
+----+------+-------+-----+-----+
| xh | name | banji | sex | age |
+----+------+-------+-----+-----+
| 26 | 郜二 | 二班  | 女  |  19 |
| 27 | 管三 | 三班  | 男  |  18 |
| 28 | 韩四 | 一班  | 女  |  19 |
| 29 | 贺五 | 二班  | 男  |  18 |
| 30 | 蒋六 | 三班  | 女  |  19 |
| 31 | 李一 | 一班  | 男  |  18 |
| 32 | 刘二 | 二班  | 女  |  19 |
| 33 | 龙三 | 三班  | 男  |  18 |
| 34 | 卢四 | 一班  | 女  |  19 |
| 35 | 鲁五 | 二班  | 男  |  18 |
| 36 | 陆六 | 三班  | 女  |  19 |
| 37 | 明一 | 一班  | 男  |  18 |
| 38 | 欧二 | 二班  | 女  |  19 |
| 39 | 潘三 | 三班  | 男  |  18 |
| 40 | 秦四 | 一班  | 女  |  19 |
| 41 | 尚五 | 二班  | 男  |  18 |
| 42 | 申六 | 三班  | 女  |  19 |
+----+------+-------+-----+-----+
17 rows in set (0.01 sec)

主键索引：
select * from t_user where id = 28;
磁盘IO次数：3次索引检索+记录数据检索

select * from t_user where xh between 23 and 27;
磁盘IO次数：4次索引检索+记录数据检索。

对于MyISAM存储引擎，不管是主键索引还是构建的辅助索引，对于叶子节点来说都不是存放的整行数据，而且仅仅存放了只存储索引列的有序值+聚集索引列值，构造辅助索引一般都是提取索引列的所有值进行排序，然后把排序好的数据均匀的存放到叶子节点，进一步生成枝节点和根节点（叶子节点中的值都会对应存储主键ID）。
对于主键索引和辅助索引不同地方在于：主键索引的键值是唯一的，而辅助索引的键值可以重复，正是由于这一点，我们在查询数据时可能会有多个相同的记录，故在做等值查询时也需要按照范围查询的方式在辅助索引树中检索数据。

2）InnoDB索引（聚集索引）：InnoDB存储引擎（MySQL-5.5 以及之后版本默认存储引擎）：
.frm文件：存储表结构（列，列属性）
.ibd文件：存储的数据记录和索引
ibdata1文件（数据目录中）：数据字典信息。
即数据和索引存放在同一个文件中，一般情况下，以表中的主键primary key 作为聚集索引（唯一一个），如果没有主键则以为一家unique 作为主键，两者都没有的话，系统会默认生成一个隐藏的6byte的int型作为主键。聚集索引使用B+树构建，叶子节点存储的数据是整行记录。除聚集索引外，其他索引都称之为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值对。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。表t_user1的索引和数据文件都存储同一个文件t_user1.ibd中。

关于磁盘IO情况：

-- 创建存储引擎为InnoDB 的t_user1 表，xh为主键（自增），name列为普通索引
CREATE TABLE t_user1(
xh INT NOT NULL AUTO_INCREMENT COMMENT '学号',
name VARCHAR(200) NOT NULL COMMENT '姓名',
banji ENUM('一班','二班','三班') NOT NULL COMMENT '班级',
sex ENUM('男','女') DEFAULT '男' NOT NULL COMMENT '性别',
age INT NOT NULL COMMENT '年龄',
PRIMARY KEY (xh),
KEY idx_age(age)) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

主键索引:
select * from t_user1 where xh=27;
磁盘IO数量：3次。

辅助索引
select * from t_user1 where age=24;
磁盘IO数：辅助索引3次+获取记录回表3次
±—±-----±------±----±----+
| xh | name | banji | sex | age |
±—±-----±------±----±----+
| 26 | 郜二 | 二班 | 女 | 19 |
| 27 | 管三 | 三班 | 男 | 24 |
| 28 | 韩四 | 一班 | 女 | 25 |
| 29 | 贺五 | 二班 | 男 | 27 |
| 30 | 蒋六 | 三班 | 女 | 21 |
| 31 | 李一 | 一班 | 男 | 22 |
| 32 | 刘二 | 二班 | 女 | 26 |
| 33 | 龙三 | 三班 | 男 | 18 |
| 34 | 卢四 | 一班 | 女 | 29 |
| 35 | 鲁五 | 二班 | 男 | 18 |

参考资料：
1、Mysql文件目录格式及存放位置.
2、Mysql基础.
3、一文搞懂MySQL索引.
4、MySQL索引原理以及查询优化.
5、MySQL索引原理以及查询优化.
6、mysql索引&性能分析.
7、CSDN数学公式