MySql优化-索引篇（索引是个啥？）

MySql优化-索引篇（索引是个啥？）

本系列将分为多个章节带你感受mysql索引原理,带你感受索引的底层原理与索引的重要性与何种场景下使用何种索引以及注意事项

---

章节大纲

1. 索引是干什么的?
2. 常用的索引
3. 体验与感受索引
4. 怎么给数据表字段加索引
5. mysql索引用的是什么树？（答案：B+树 or Hash树）
6. 索引的工作原理
7. 索引在不同存储引擎下的工作流程（Innodb 与 MyISAM）
8. MySql索引用的是那棵树?为啥用B+树 or Hash树
9. 索引的分类
10. 怎么创建索引及索引创建原则
11. 索引的使用(为啥索引不能这么用)

一、索引是个啥?

• 官方版：让获取的数据更有目的性，从而提高数据库检索数据的性能
• 白话版：
  索引就是个目录，通过目录可以快速定位到你想找的数据页；
  书本的目录大家都不陌生，这里的页面就相当于我们数据表中的主键唯一自增索引，可以更具具体的页面（索引）快速找到文章的所在位置，获取我们想要的信息；
  

---

二、常用的索引

• 几个常用索引：
  1. 主键索引：每张表只有1个，一般设置为表的id字段并让此字段唯一且自增且不允许有空值
  2. 普通索引：（别名:单列索引）最基本的索引，可用在任何字段上，值可为空
  3. 组合索引：(别名:复合索引)，顾名思义就是将多个字段的普通索引放在一起
  4. 唯一索引：顾名思义在本例中每个值都是唯一的且不重复的，唯一索引是允许多个null值出现的，但注意：在sql server中，唯一索引字段不能出现多个null值，否则将会报错
  5. 全文索引：是MyISAM的一个特殊索引类型，它查找的是文本中的关键词，主要用于全文检索

---

三、体验与感受索引

• 本章我们将用过‘test_user’表对比有索引与无索引的差别，并一起创建索引优化本表的查询速度;
• 在体验索引之前需要先给大家介绍一个Mysql自带的一个小工具

EXPLAIN + sql语句

例如

EXPLAIN SELECT
	* 
FROM
	test_users 
WHERE
	tel = '18488477104'

用过EXPLAIN这个工具查询你将得到SQL语句结果如下图

Explain查询结果说明

列名	说明
id	执行的编号，标记select所属的行。如果在语句中没有子查询或关联查询，只有唯一的select,每行都将显示1.否则内层的selec语句一般会正序编号，对应其在原始语句中的位置
select_type	显示本行是简单查询或者是复杂查询，如果查询有任何复杂的子查询，则最外层标记为PRIMARY(DERIVED、UNION、UNION RESUIT)
table	数据的查询访问引用了哪个表
type	数据访问/读取的操作类型(ALL、index、range、ref、eq_ref、const、system、NULL)
possible_keys	显示哪些索引可能有利于高效的数据查询
key	这一列显示mysql实际采用哪个索引来优化对该表的访问。 如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索 引，在查询中使用 force index、ignore index
key_len	该列记录了使用索引的长度，一般用来判断联合索引是否全部生效的作用，该值是根据不同数据类型进行计算的
ref	显示了之前的表在key列记录的索引中查找值所用的列或者常量
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值想乘，可粗略估算整个查询会检查的行数
Extra	额外信息，如using index、filesort等

这里仅介绍几个我们后面要用到的参考指标，其余请关注另一篇文章
点我跳转：Mysql优化之Explain精讲

感受有无索引的差别
现在有2张数据完全一致的表格，test_user的tel字段设置了唯一索引，test_user_copy1的tel字段尚未设置任何索引；

这张测试数据表数据总量有1.67万，可以大致看出有无索引的差距了；

---

四、怎么给数据表字段加索引

1. 直接创建索引
2. 创建索引

CREATE INDEX indexName ON mytable(username(length));

• 创建索引

CREATE INDEX name_index ON test_users(name(20))

解释：创建 INDEX（普通单列）类型索引 索引名叫name_index 给 test_user表里面的name字段，因为name字段是varchar(20)类型 所以lendth = 20;
注意：如果字段类型是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length

• 删除索引

DROP INDEX name_index ON test_users; 

DROP INDEX 要删除的索引名 ON 索引所在的表名; 

解释：删除 索引名为"name_index"的索引 在 test_users表中（删除test_user表中名为name_index的索引）

• 修改表结构的方式创建索引

ALTER TABLE test_users ADD INDEX name_age_index (name,age);

ALTER TABLE 要改动数据结构的表名 ADD 索引类型 索引的名字 (字段名1,字段名2);

解释：修改“test_user”表 添加组合索引 索引名为“name_age_index” 给字段“name”，字段“age”添加；（修改test_user表的表结构并未name,age字段添加名为name_age_index的组合索引）

五、mysql索引用的是什么树？（答案：B+树 or Hash树）

• 测试工具地址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
  要了解B+树需要先知道mysql二叉树、红黑树（二叉平衡树）B树

测试表如下：

id	tel	name
1	1782001365	安正业
2	1706120967	查丹丹
3	1869407800	殷军
4	1399680119	祝春梅
5	1501430617	查楠
6	1761118999	孔晨
7	1390099675	林冬梅
8	1705208569	井洋
9	1337409818	贺依琳

索引工作原理简述：

你可以将索引理解为key=>value的方式保存
例如上表id=9的数据 9=>0x65 及 索引值=>数据在磁盘中的内存地址。
我们通过索引值来去到索引表中找到对应带索引数据在磁盘中的内存地址，然后拿到数据；

二叉树：

在上表仅存在主键id索引的情况下找到id=9的这条数据，可以看出因为要找到id=9这一条数据进行了9次查找这相当于全表便利

红黑树（二叉平衡树）

如果查找id=9的数据使用红黑树仅查找了4次，那么如果数据量很大也就意味着树的层级也会很高，这显然不符合我们的需求；

B树

使用B树就解决了层级的问题，若我们设置B树每个节点最大限度为4则每个节点加满4个索引后才会进行分裂，这样就少了层级从而减少查询次数，但是实际生产过程中我们不仅需要对数据进行查找操作，还有排序，比较等操作，由下图可看出如果进行排序遍历的话依然是对数据进行了多次差找，如果数据量巨大，那么使用B树的效果依然不理想。

B+树

B+树的数据结构与B树有所不同，B+树还会将索引在最底层的叶子节点把数据按顺序进行排列，另外需要注意的是如果我们的表使用的是Innodb存储引擎那么我们用的就是聚簇索引意味着与主键id绑定的数据是直接存储在最底层叶子节点上的，也就是说我们直接根据索引值查询到指定索引后就可以获取我们要的数据了，使用B+树不管是在查找和排序上都做到了相对的均衡

Hsah树（哈希树）

Hash树是数组+链表的结构，mysql根据底层自己指定的算法对索引元素进行hsah值计算后取模（%）然后分配到不同的叶子节点中，当我们查找数据时，也是将索引的key值进行hash计算后取模然后再去找对应的索引value(数据在磁盘文件的地址)所以有些场景使用Hash树要比B+树的速度快，但是Hash树有个缺点，那就是Hash树可以进行“=”、“IN”检索，不支持范围检索，还有可能会出现哈希冲突（例如A的索引值经过Hash计算得出一个值，这个值就被A占有了，B值经过Hash计算也得出了相同的值，此时发现这个值已经被A占用了，这时候就出现了Hash冲突）
因为实际应用场景中我们会用到很多范围查找，所以一般我们用B+树而不是用Hash树，
而且B+树的最底层是存在双向指针，且数据的排好序的数据结构在进行范围检索时，例如查询id>=20 and <= 50的数据，B+树会找到索引值key=20的数据然后直接完后开始查找直到找到索引key=50的元素在停止。所以B+树在这样的应用场景的所速度显然更快。

索引在不同存储引擎下的工作流程（Innodb 与 MyISAM）

• 介绍下mysql8的数据才存储文件变化
• 绿色表示innodb存储引擎的数据存储文件
• 红色表示myisam存储引擎的数据存储文件
  

Innodb

• 聚簇索引（聚集索引）的文件存放在主键索引的叶子节点上
  因为innobd是将索引与数据直接绑定到了叶子最底层的节点上，所以可以简单的理解为直接根据索引就能找到数据；

普通索引（NORMAL）：

普通索引是如何查找数据的？
例如name(名字)字段，我们给name字段加一个普通索引
现有一条数据id=6 name=特别剑。
当我们检索“特别剑”这个名字时，mysql会先去name的索引树中去查找
找到索引key=特别剑得到索引的value=6
此时进行回表，再去主键id索引树中去找索引key=6的value,此时得到的value就是id=6的整行数据了。

联合索引：

联合索引就是将多个单值索引绑定在一起，进行分组排序；
例如下表：

id	class	age	name
1	A	20	李华
2	B	19	王悦
3	A	22	张恒
4	A	20	特别剑
5	B	21	刘明
6	B	23	佟为
7	C	24	章东
8	A	22	王有为
9	C	21	郝冬
10	A	19	钱立
11	C	20	王超

索引树结构如下：

此时我们的mysql查询语句就要满足最左查询原则：（字段从左开始到右边结束）
例如：我将上表中的calss、age、name定义为联合索引

联合索引查询：class、age、name这3个字段添加了联合索引并不是说这3个字段在查询时要时出现，只出现2个也可以，出现1个也可以，但规则必须是按照字段的顺序来。例如我这张表的定义联合索引的顺序就是class→age→name
注意：这里说的是你索引定义的顺序，不是表字段定义的顺序！
注意：这里说的是你索引定义的顺序，不是表字段定义的顺序！
注意：这里说的是你索引定义的顺序，不是表字段定义的顺序！

那么他们的优先级就只有3种分别为：

1. 联合索引查询1

EXPLAIN SELECT * FROM test_user WHERE	test_user.class = 'C' AND test_user.age = 20 AND test_user.`name` = '王超';

2. 联合索引查询2

EXPLAIN SELECT * FROM test_user WHERE test_user.class = 'C' AND test_user.`name` = '王超';

3. 联合索引查询3

EXPLAIN SELECT * FROM test_user WHERE test_user.class = 'C' AND test_user.`name` = '王超';

4. 不按照顺序来（不满足最左前缀原则）将无法使用索引，mysql处理时会直接进行全表查询

EXPLAIN SELECT *  FROM test_user WHERE test_user.age = 20 AND test_user.`name` = '王超';

MyISAM

• 非聚簇索引（非聚集索引）的索引与数据是分开存储的
  因为MyISAM非聚簇索引叶子节点保存的是想要查的那条数据的指针地址，并不是数据本身，要先查到数据指针的地址，在根据数据指针的地址去找你想要的那条数据，也就是说查找myisam_tab表中主键id=9的数据要先根据主键9查询myisam.MYI这个索引文件（你可以把它理解成一张索引表）找的主键id=9这个主键对应的指针地址，在根据这个指针地址去myisam.MYD数据表中去查得你要的那条数据所信息。