MySQL-索引

1.简介

1.1索引是什么？

MySQL的索引是一种数据布局，它可以帮助数据库高效地查询，更新数据表中的数据。索引通过一定的规则分列数据表中的记载，使得对表的查询可以通过对索引的搜刮来加速速度。
MySQL索引类似于册本的目次，通过指向数据行的位置，可以快速定位和访问表中的数据，比如汉语字典的目次（索引）页，我们可以按照笔画，偏旁部首，拼音等排序目次（索引）快速查找到需要的字。

• 笔画索引
  
  
  
• 偏旁部首索引
  
  
  
• 拼音索引
  
  
  

1.2为什么要利用索引？

显⽽易⻅，使⽤索引的⽬的只有⼀个，就是提升数据检索的服从，在应⽤步调的运⾏过程中，查
询操纵的频率远远⾼于增删改的频率。
2.索引应该选择哪种数据布局

2.1HASH

时间复杂度是O(1),查询速度非常快，但是MySQL并没有选择HASH作为索引的默认数据布局，主要缘故原由是HASH不支持范围查找
2.2二叉搜刮树

⼆叉搜刮树的中序遍历是⼀个有序数组，但有⼏个问题导致它不适合⽤作索引的数据布局
1. 最坏情况下时间复杂度为O(N)
2. 节点个数过多⽆法包管树⾼
   AVL和红⿊树，虽然是平衡或者近似平衡，但是毕竟是⼆叉布局
  在检索数据时，每次访问某个节点的⼦节点时都会发⽣⼀次磁盘IO，⽽在整个数据库体系中，IO是性能的瓶颈，镌汰IO次数可以有效的提升性能。
   

2.3 N叉树

为了解决树高的问题，可以利用N叉树

通过观察，雷同数据量的情况下，N叉树的树⾼可以得到有效的控制，也就意味着在雷同数据量的情况下可以镌汰IO的次数，从⽽提升服从。但是MySQL以为N叉树做为索引的数据布局还不够好。
2.4 B+树

2.4.1简介

B+树是一种经常用于数据库和文件体系等场合的平衡查找树，MySQL索引接纳的数据布局，以4阶B+树为例，如下图所示：

2.4.2 B+树的特点

• 可以大概保持数据稳固有序，插⼊与修改有较稳固的时间复杂度
• ⾮叶⼦节点仅具有索引作⽤，不存储数据，所有叶⼦节点保真实数据
• 所有叶⼦节点构成⼀个有序链表，可以按照key排序的次序依次遍历全部数据
2.4.3 B+树与B树的对比

• 叶子节点中的数据是连续的，且相互链接，便于区间查找和搜刮。
• ⾮叶⼦节点的值都包含在叶⼦节点中
• 对于B+树⽽⾔，在雷同树⾼的情况下，查找任⼀元素的时间复杂度都⼀样，性能平衡。
3.MySQL中的页

3.1为什么要利用页

• 在 .ibd ⽂件中最重要的布局体就是Page(⻚)，⻚是内存与磁盘交互的最⼩单元，默认⼤⼩为16KB，每次内存与磁盘的交互⾄少读取⼀⻚，所以在磁盘中每个⻚内部的地址都是连续的，之所以这样做，是由于在使⽤数据的过程中，根据局部性原理，将来要使⽤的数据⼤概率与当前访问的数据在空间上是临近的，所以⼀次从磁盘中读取⼀⻚的数据放⼊内存中，当下次查询的数据还在这个⻚中时就可以从内存中直接读取，从⽽镌汰磁盘I/O提⾼性能
   局部性原理：是指步调在执⾏时出现出局部性规律，在⼀段时间内，整个步调的执⾏仅限于步调中的某⼀部分。相应地，执⾏所访问的存储空间也局限于某个内存区域，局部性通常有两种情势：时间局部性和空间局部性。
  时间局部性（Temporal Locality）：假如⼀个信息项正在被访问，那么在近期它很大概还会被再次访问。
  空间局部性（Spatial Locality）：将来要⽤到的信息⼤概率与正在使⽤的信息在空间地址上是临近的。
  • 每⼀个⻚中即使没有数据也会使⽤ 16KB 的存储空间，同时与索引的B+树中的节点对应，后续在专题七：索引中具体解说B+树的内容，查看⻚的⼤⼩，可以通过体系变量 innodb_page_size查看

1. mysql> SHOW VARIABLES LIKE 'innodb_page_size';
2. +------------------+-------+
3. |    Variable_name | Value |
4. +------------------+-------+
5. | innodb_page_size | 16384 | # 16KB 16384/1024
6. +------------------+-------+
7. 1 row in set, 1 warning (0.04 sec)

复制代码

• 在MySQL中有多种不同类型的⻚，最常⽤的就是⽤来存储数据和索引的"索引⻚"，也叫做"数据⻚"，但不论哪种类型的⻚都会包含⻚头(File Header)和⻚尾(File Trailer)，⻚的主体信息使⽤数据"⾏"进⾏添补，数据⻚的基本布局如下图所⽰：

3.2页文件头和页文件尾

页文件头和页文件尾中包含的信息，如图所示：

这里我们只关注，上一页页号和下一页页号 ，通过这两个属性可以把页与页之间链接起来，形成一个双向链表。
3.3 页主体


• ⻚主体部分是生存真实数据的主要区域，每当创建⼀个新⻚，都会⾃动分配两个⾏，⼀个是⻚内最⼩⾏ Infimun ，另⼀个是⻚内最⼤⾏ Supremun ，这两个⾏并不存储任何真实信息，⽽是做为数据⾏链表的头和尾，第⼀个数据⾏有⼀个记载下⼀⾏的地址偏移量的区域 next_record 将⻚内所有数据⾏组成了⼀个单向链表，此时新⻚的布局如下所⽰：
 

• 当向⼀个新⻚插⼊数据时，将 Infimun 连接第⼀个数据⾏，末了⼀⾏真实数据⾏连接Supremun ，这样数据⾏就构建成了⼀个单向链表，更多的⾏数据插⼊后，会按照主键从⼩到⼤的顺序进⾏链接，如下图所⽰

3.4页目次 

• 当按主键或索引查找某条数据时，最直接简单的⽅法就是从头⾏ infimun 开始，沿着链表顺序逐个⽐对查找，但⼀个⻚有16KB，通常会存在数百⾏数据，每次都要遍历数百⾏，⽆法满⾜⾼效查询，为了提⾼查询服从，InnoDB采⽤⼆分查找来解决查询服从问题；
• 具体实现⽅式是，在每⼀个⻚中加⼊⼀个叫做⻚⽬录 Page Directory 的布局，将⻚内包罗头⾏、尾⾏在内的所有⾏进⾏分组，约定头⾏单独为⼀组，其他每个组最多8条数据，同时把每个组末了⼀⾏在⻚中的地址，按主键从⼩到⼤的顺序记载在⻚⽬录中在，⻚⽬录中的每⼀个位置称为⼀个槽，每个槽都对应了⼀个分组，⼀旦分组中的数据⾏超过分组的上限8个时，就会分裂出⼀个新的分组；
• 后续在查询某⾏时，就可以通过⼆分查找，先找到对应的槽，然后在槽内最多8个数据⾏中进⾏遍历即可，从⽽⼤幅提⾼了查询服从，这时⼀个⻚的核⼼布局就完成了；
• 比方要查找主键为6的⾏，先⽐对槽中记载的主键值，定位到末了⼀个槽2，再从末了⼀个槽中的第⼀条记载遍历，第⼆条记载就是我们要查询的⽬标⾏。

3.5数据表头 

数据页头记载了当前页保持数据相干的信息，如下图所示

4.B+树在MySQL索引中的应用 

非叶子节点生存索引数据，叶子节点生存真实数据，如下图所示

 

以查找id为5的记载，完整的检索过程如下：
1. ⾸先判定B+树的根节点中的索引记载，此时 5 < 7 ，应访问左孩⼦节点，找到索引⻚2
2. 在索引⻚2中判定id的⼤⼩，找到与5相等的记载，命中，加载对应的数据⻚
 以上的IO过程，加载索引⻚1 --> 加载索引⻚2 --> 加载数据⻚3
4.1 盘算三层树⾼的B+树可以存放多少条记载

• 假设⼀条⽤⼾数据⼤⼩为1KB，在忽略数据⻚中数据⻚⾃⾝属性空间占⽤的情况下，⼀⻚可以存16条数据
• 索引⻚⼀条数据的⼤⼩为，主键⽤BIGINT类型占8Byte，下⼀⻚地址6Byte，⼀共是14Byte，⼀个索引⻚可以生存 16*1024/14 = 1170 条索引记载
• 假如只有三层树⾼的情况，综合只生存索引的根节点和⼆级节点的索引⻚以及生存真实数据的数据⻚，那么⼀共可以生存 1170*1170*16 = 21,902,400 条记载，也就是说在两千多万条数据的表中，可以通过三次IO就完成数据的检索
5. 索引分类

5.1 主键索引

• 当在⼀个表上定义⼀个主键 PRIMARY KEY 时，InnoDB使⽤它作为聚集索引。
• 保举为每个表定义⼀个主键。假如没有逻辑上唯⼀且⾮空的列或列集可以使⽤主键，则添加⼀个⾃增列。
5.2 平常索引

• 最基本的索引类型，没有唯⼀性的限制。
• 大概为多列创建组合索引，称为复合索引或组全索引
5.3 唯⼀索引

• 当在⼀个表上定义⼀个唯⼀键 UNQUE 时，⾃动创建唯⼀索引。
• 与平常索引类似，但区别在于唯⼀索引的列不答应有重复值。
5.4 全⽂索引

• 基于⽂本列(CHAR、VARCHAR或TEXT列)上创建，以加速对这些列中包含的数据查询和DML操纵
• ⽤于全⽂搜刮，仅MyISAM和InnoDB引擎⽀持。
5.5 聚集索引

• 与主键索引是同义词
• 假如没有为表定义 PRIMARY KEY, InnoDB使⽤第⼀个 UNIQUE 和 NOT NULL 的列作为聚集索
引。
• 假如表中没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB会为新插⼊的⾏⽣成⼀个⾏号并
⽤6字节的 ROW_ID 字段记载， ROW_ID 单调递增，并使⽤ ROW_ID 做为索引。
5.6 ⾮聚集索引

• 聚集索引以外的索引称为⾮聚集索引或⼆级索引
• ⼆级索引中的每条记载都包含该⾏的主键列，以及⼆级索引指定的列。
• InnoDB使⽤这个主键值来搜刮聚集索引中的⾏，这个过程称为回表查询
5.7 索引覆盖

• 当⼀个select语句使⽤了平常索引且查询列表中的列刚好是创建平常索引时的所有或部分列，这时
就可以直接返回数据，⽽不⽤回表查询，这样的征象称为索引覆盖
6. 使⽤索引

6.1 ⾃动创建

• 当我们为⼀张表加主键约束(Primary key)，外键约束(Foreign Key)，唯⼀约束(Unique)时，
MySQL会为对应的的列⾃动创建⼀个索引
• 假如表不指定任何约束时，MySQL会⾃动为每⼀列⽣成⼀个索引并⽤ ROW_ID 进⾏标识
6.2手动创建

6.2.1主键索引

1. # ⽅式⼀，创建表时创建主键
2. create table t_test_pk (
3. id bigint primary key auto_increment,
4. name varchar(20)
5. );
7. # ⽅式⼆，创建表时单独指定主键列
8. create table t_test_pk1 (
9. id bigint auto_increment,
10. name varchar(20),
11. primary key (id)
12. );
14. # ⽅式三，修改表中的列为主键索引
15. create table t_test_pk2 (
16. id bigint,
17. name varchar(20)
18. );
20. alter table t_test_pk2 add primary key (id) ;
21. alter table t_test_pk2 modify id bigint auto_increment;

复制代码

6.2.2唯一索引

1. # ⽅式⼀，创建表时创建唯⼀键
2. create table t_test_uk (
3. id bigint primary key auto_increment,
4. name varchar(20) unique
5. );
7. # ⽅式⼆，创建表时单独指定唯⼀列
8. create table t_test_uk1 (
9. id bigint primary key auto_increment,
10. name varchar(20),
11. unique (name)
12. );
14. # ⽅式三，修改表中的列为唯⼀索引
15. create table t_test_uk2 (
16. id bigint primary key auto_increment,
17. name varchar(20)
18. );
19. alter table t_test_uk2 add unique (name);

复制代码

 6.2.3平常索引

1. # ⽅式⼀，创建表时指定索引列
2. create table t_test_index (
3. id bigint primary key auto_increment,
4. name varchar(20) unique
5. sno varchar(10),
6. index(sno)
7. );
9. # ⽅式⼆，修改表中的列为普通索引
10. create table t_test_index1 (
11. id bigint primary key auto_increment,
12. name varchar(20),
13. sno varchar(10)
14. );
15. alter table t_test_index1 add index (sno) ;
17. # ⽅式三，单独创建索引并指定索引名
18. create table t_test_index2 (
19. id bigint primary key auto_increment,
20. name varchar(20),
21. sno varchar(10)
22. );
23. create index index_name on t_test_index2(sno);

复制代码

6.3创建复合索引

创建语法与创建平常索引雷同，只不过指定多个列，列与列之间用逗号隔开

1. # ⽅式⼀，创建表时指定索引列
2. create table t_test_index4 (
3. id bigint primary key auto_increment,
4. name varchar(20),
5. sno varchar(10),
6. class_id bigint,
7. index (sno, class_id)
8. );
10. # ⽅式⼆，修改表中的列为复合索引
11. create table t_test_index5 (
12. id bigint primary key auto_increment,
13. name varchar(20),
14. sno varchar(10),
15. class_id bigint
16. );
17. alter table t_test_index5 add index (sno, class_id);
19. # ⽅式三，单独创建索引并指定索引名
20. create table t_test_index6 (
21. id bigint primary key auto_increment,
22. name varchar(20),
23. sno varchar(10),
24. class_id bigint
25. );
26. create index index_name on t_test_index6 (sno, class_id);

复制代码

6.4查看索引

1. # ⽅式⼀：show keys from 表名
2. mysql> show keys from t_test_index6\G
3. *************************** 1. row ***************************
4. Table: t_test_index6
5. Non_unique: 0
6. Key_name: PRIMARY
7. Seq_in_index: 1
8. Column_name: id
9. Collation: A
10. Cardinality: 0
11. Sub_part: NULL
12. Packed: NULL
13. Null:
14. Index_type: BTREE
15. Comment:
16. Index_comment:
17. Visible: YES
18. Expression: NULL
19. *************************** 2. row ***************************
20. Table: t_test_index6
21. Non_unique: 1
22. Key_name: index_name
23. Seq_in_index: 1
24. Column_name: sno
25. Collation: A
26. Cardinality: 0
27. Sub_part: NULL
28. Packed: NULL
29. Null: YES
30. Index_type: BTREE
31. Comment:
32. Index_comment:
33. Visible: YES
34. Expression: NULL
35. *************************** 3. row ***************************
36. Table: t_test_index6
37. Non_unique: 1
38. Key_name: index_name
39. Seq_in_index: 2
40. Column_name: class_id
41. Collation: A
42. Cardinality: 0
43. Sub_part: NULL
44. Packed: NULL
45. Null: YES
46. Index_type: BTREE
47. Comment:
48. Index_comment:
49. Visible: YES
50. Expression: NULL
51. 3 rows in set (0.00 sec)
53. # ⽅式⼆
54. show index from t_test_index6;
56. # ⽅式三，简要信息：desc 表名;
57. desc t_test_index6;

复制代码

 6.5删除索引

6.5.1主键索引

1. # 语法
2. alter table 表名 drop primary key;
4. # ⽰例，删除t_test_index6表中的主键
5. mysql> alter table t_test_index6 drop primary key;
6. ERROR 1075 (42000): Incorrect table definition; there can be only one auto
7. column and it must be defined as a key
9. # 如查提⽰由于⾃增列的错误，先删除⾃增属性
10. mysql> alter table t_test_index6 modify id bigint;
11. Query OK, 0 rows affected (0.04 sec)
12. Records: 0 Duplicates: 0 Warnings: 0
14. # 重新删除主键
15. mysql> alter table t_test_index6 drop primary key;
16. Query OK, 0 rows affected (0.04 sec)
17. Records: 0 Duplicates: 0 Warnings: 0
19. # 查看结果
20. mysql> show keys from t_test_index6\G
21. *************************** 1. row ***************************
22. Table: t_test_index6
23. Non_unique: 1
24. Key_name: index_name
25. Seq_in_index: 1
26. Column_name: sno
27. Collation: A
28. Cardinality: 0
29. Sub_part: NULL
30. Packed: NULL
31. Null: YES
32. Index_type: BTREE
33. Comment:
34. Index_comment:
35. Visible: YES
36. Expression: NULL
37. *************************** 2. row ***************************
38. Table: t_test_index6
39. Non_unique: 1
40. Key_name: index_name
41. Seq_in_index: 2
42. Column_name: class_id
43. Collation: A
44. Cardinality: 0
45. Sub_part: NULL
46. Packed: NULL
47. Null: YES
48. Index_type: BTREE
49. Comment:
50. Index_comment:
51. Visible: YES
52. Expression: NULL
53. 2 rows in set (0.00 sec)

复制代码

6.5.2其他索引

1. # 语法
2. alter table 表名 drop index 索引名；
4. # ⽰例，删除t_test_index6表中名为index_name的索引
5. mysql> alter table t_test_index6 drop index index_name;
6. Query OK, 0 rows affected (0.02 sec)
7. Records: 0 Duplicates: 0 Warnings: 0
9. # 查看结果
10. mysql> show keys from t_test_index6\G
11. Empty set (0.00 sec)

复制代码

6.6创建索引的留意事项

• 索引应该创建在⾼频查询的列上
• 索引需要占⽤额外的存储空间
• 对表进⾏插⼊、更新和删除操纵时，同时也会修索引，大概会影响性能
• 创建过多或不合理的索引会导致性能降落，需要审慎选择和规划索引
 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。