1、索引是什么

官方对索引的定义：

索引（Index）是帮助MySql高效获取数据的数据结构

索引的目的在于提高查询效率，可以类比字典，例如要查询“mysql”这个单词，我们肯定需要定位到m字母，然后从上往下找到y字母，再找到剩余的sql，如果没有索引，那么每次查找可能都需要从a~z之上而下的查找，查找次数多，效率低。

个人理解：

索引是排好序的快速查找的数据结构

注意两点：排序和快速查找，所以索引会影响where条件的查询和order by的排序。

图片

如果我们平常所说的索引在没有特别指明，一般都指B-Tree

2、索引的优劣

2.1、优势

①、提高数据的检索效率，降低数据库IO成本
②、降低数据排序的成本，降低了CPU的消耗

2.2、劣势

①、索引也是一张表，保存了主键与索引字段，并指向实体表的记录，占用一定的空间
②、降低更新（Insert/update/delete操作）表的效率，因为在更新操作时，可能会导致索引列字段的更新，都会调整因为更新所带来键值变化后的索引信息
③、索引只是提高效率的一个因素，如果MySQL有大量的表，需要花时间研究建立最优秀的索引，或优化查询语句

3、索引的分类

3.1、分类

| 序号 | 索引 | 描述 |
|:—-:|:—-:|:—-:|:—-:|:—-:|:—-:|
| 1 | 单值索引 | 即一个索引只包含单个列，一个表可以有多个单列索引 |
| 2 | 唯一索引 | 索引列的值必须唯一，单允许有空置 |
| 3 | 复合索引 | 即一个索引包含多个列 |

3.2、基本语法

3.2.1、创建索引

使用CREATE：

1
CREATE [UNIQUE] INDEX indexName ON tableName(columnName(length))

其中columnName可以是多个，以逗号隔开，如果列字段是char、varchar类型，length可以小于字段的实际长度，如果是BLOB和TEXT类型，必须指定length的值。

使用ALTER:
①、创建主键，这意味着索引值必须唯一

1
ALTERl TABLE table_name ADD PRMARY KEY（column_list）

②、创建索引的值必须唯一，除null外，null可能会出现多次

1
ALTERl TABLE table_name ADD UNIQUE index_name（column_list）

③、添加普通索引，索引可以出现多次

1
ALTERl TABLE table_name ADD INDEX index_name（column_list）

④、指定索引为FULLTEXT，用于全文索引

1
ALTERl TABLE table_name ADD FULLTEXT index_name（column_list）

3.2.2、删除索引

1
DROP INDEX index_name ON table_name

3.2.3、查看索引

1
SHOW INDEX FROM table_name

4、BTree索引

图片

4.1、初始化介绍

一颗b+树，浅蓝色的块是我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3。
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点，即3、5、9、10、13、15、28、29、36、68、75、79、90、99。
非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

4.2、查找过程

如果要查找数据项29，那么首先把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1和P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1和P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存中，发生第三次IO，同事内存中做二分查找知道29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示成千上百万的数据，如果上百万的数据查找只需要三次IO，性能提高是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常的高。

5、适合创建索引的场景

①、注解自动创建唯一索引
②、频繁作为查询条件的字段（where后的查询条件）应该创建索引
③、查询中于其他表关联的字段，外键关系建立索引
④、单键/组合索引的选择问题，在高并发下倾向创建组合索引
⑤、查询中排序的字段，排序字段若通过索引区访问将大大提高排序速度，意思是select查询的字段的顺序如果保持和order by排序字段的顺序一致，那么将会大大提高排序速度
⑥、查询中统计或者分组的字段（实际分组也将会用到排序）

6、不适合创建索引的场景

①、表记录太少，一般表记录不超过300万条，建不建索引没什么区别
②、频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引，加重了IO负担
③、数据重复且分布评价的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引，注意，如果每个数据列包含许多重复的内容，为他建立索引就没有太大的实际效果

假如有一个表有10万行记录，有一个字段A只有T和F两种值，且每个值得分布概率大约为50%，那么对这种表A字段创建索引一般不会提高数据库的查询速度
索引的选择性是指索引列中不同指的数据与表中记录数的比，如果一个表中2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高

最后更新： 2019年06月28日 11:16

原始链接： https://www.sunnymaple.cn/2019/06/28/MySql索引简介/

赏
× 请我吃糖~
打赏二维码