Mysql索引原理和使用姿势

索引的简单使用

1. 建立一个表

2. 写入了20几万条数据

3. 没有索引的搜索

SELECT * FROM `order` WHERE CODE = '1001'

耗时：0.376s

4，给code建立索引，同样进行搜索

SELECT * FROM `order` WHERE CODE = '1001'

耗时： 0.020s

5. 对比

加了索引的查询明显提升了一个级别，如果数据量再大，效果会更加明显。
分析sql： mysql索引情况

索引原理

索引的原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。

关于磁盘

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

扇区: 磁盘存储的最小单位，一般为512Byte

磁盘块: 文件系统与磁盘交互的最小单位

mysql中的页: 与磁盘交互的最小单位，mysql内部的数据结构，大小为16kb,一个页中有4磁盘块，mysql每次从磁盘中读取数据默认最小是16kb，要么不读，读了就是16kb，大小可以修改.

索引的底层结构

索引的数据结构有如下几种：

• 二叉树
• 红黑树
  红黑树原理
• Hash表
• B-Tree
  B树，B+树，B*树

B+树

任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

B+树的特点：

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列

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

如图所示，如果要查找数据项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，显然成本非常非常高。

b+树性质

1. 索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2. 索引的最左匹配特性（即从左往右匹配）：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

数据量问题

想要计算MySQL数据存储量，首先就得知道根节点有多大，因为根节点的存储在内存中的，子节点才是存在磁盘中的，那么如何查看根节点的大小呢？

show GLOBAL STATUS like 'Innodb_page_size'

最终查询到的大小是16kb。所以说MySQL设置一个节点最大是16kb。

而一个节点的大小是8个字节，索引指针是6个字节。

公式：16kb / (8b+6b) = 1170

所以，设置一个节点的话，最大可以存储1170个索引。

如果层级数为三层，即高度为3，那么可以存储多少数据呢？

公式：1170 * 1170 * 1170 = 16亿（左右）

高度为三层，就可以存储这么多是数据。

索引管理

索引分类

1. 普通索引index :加速查找
2. 唯一索引
   主键索引：primary key ：加速查找+约束（不为空且唯一）
   唯一索引：unique：加速查找+约束 （唯一）
3. 复合索引
   -primary key(id,name):复合主键索引
   -unique(id,name):复合唯一索引
   -index(id,name):复合普通索引
4. 全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5. 空间索引spatial :了解就好，几乎不用

索引的两大方式 hash与btree

我们可以在创建上述索引的时候，为其指定索引方法

• hash方法的索引：查询单条快，范围查询慢
• btree方法的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

不同的存储引擎支持的索引类型也不一样

• InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
• MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
• Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
• NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
• Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

正确使用索引

聚簇索引

聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。

InnoDB默认创建的主键索引是聚簇索引（Clustered Index），其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。

聚簇索引中的叶子节点则记录了主键值、事务id、用于事务和MVCC的回流指针以及所有的剩余列。

基于上面的图示，如果需要根据商品编码serial_no查询商品，就需要将商品编码serial_no列作为一个索引列。此时创建的索引是一个辅助索引，但叶子节点存储的就不是行指针了，而是主键值，并以此来作为指向行的指针。这样的好处就是当行发生移动或者数据分裂时，不用再维护索引的变更。

非聚簇索引（回表）

非聚集索引，是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。

Innodb 它的辅助索引（Secondary Index， 也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

如果使用主键索引（聚簇索引）查询商品，则会按照B+树的索引找到对应的叶子节点，直接获取到行数据：

1 select * from merchandise where id = 7

如果使用serial_no查询商品，即使用辅助索引进行查询，则会先检索辅助索引中的B+树的serial_no，找到对应的叶子节点，获取主键值，然后再通过聚簇索引中的B+树检索到对应的叶子节点，然后获取整行数据。这个过程叫做回表。

回表就是先通过辅助索引（非聚簇索引）扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

覆盖索引

如下sql：

SELECT * FROM `order` WHERE CODE = '1001'

该sql命中了索引，但未覆盖索引。利用code=1001 到索引的数据结构中定位到该code在硬盘中的位置，或者说再数据表中的位置。但是我们select的字段为*，除了code以外还需要其他字段，这就意味着，我们通过索引结构取到code还不够，还需要利用该code再去找到该code所在行的其他字段值，这是需要时间的，很明显，如果我们只select code，就减去了这份苦恼，如下

select code from `order` where code='1001';

这条就是覆盖索引了，命中索引，且从索引的数据结构直接就取到了code在硬盘的地址，速度很快.

从辅助索引中查询得到记录，而不需要通过聚簇索引查询获得，MySQL中将其称为覆盖索引。使用覆盖索引的好处很明显，不需要查询出包含整行记录的所有信息，因此可以减少大量的I/O操作。

通常在InnoDB中，除了查询部分字段可以使用覆盖索引来优化查询性能之外，统计数量也会用到。例如， SELECT COUNT(*)时，如果不存在辅助索引，此时会通过查询聚簇索引来统计行数，如果此时正好存在一个辅助索引，则会通过查询辅助索引来统计行数，减少I/O操作。

复合索引

两个或更多个列上的索引被称作复合索引。

比如，创建索引列index（code，content），code在前，content在后。

SELECT
	* 
FROM
	`order` 
WHERE
	   CODE = '1001' and content ='订单内容：39ddbea6a235484f8c6ecc5b334ba1fb'

复合索引的失效情况：

复合最左原则

1. index（a,b） —— where b

SELECT
	* 
FROM
	`order` 
WHERE
	    content ='订单内容：39ddbea6a235484f8c6ecc5b334ba1fb'

2. index（a,b） —— where a or b

SELECT
	* 
FROM
	`order` 
WHERE
	   CODE = '1001' or content ='订单内容：39ddbea6a235484f8c6ecc5b334ba1fb'

索引合并

把多个单列索引合并使用

复合索引能做到的事情，我们都可以用索引合并去解决，比如，index（code）和index（content）。

合并索引index（code）和index（content）可以命中的情况：

SELECT * FROM `order` WHERE CODE = '1001'

SELECT * FROM `order` WHERE content ='订单内容：39ddbea6a235484f8c6ecc5b334ba1fb'

SELECT * FROM `order` WHERE CODE = '1001' and content ='订单内容：39ddbea6a235484f8c6ecc5b334ba1fb'

复合索引index（code,content）可以命中的情况：

SELECT * FROM `order` WHERE CODE = '1001'


SELECT * FROM `order` WHERE CODE = '1001' and content ='订单内容：39ddbea6a235484f8c6ecc5b334ba1fb'

下面这种满足复合索引的失效情况1，index（a,b） —— where b 情况，所以不能命中

SELECT * FROM `order` WHERE content ='订单内容：39ddbea6a235484f8c6ecc5b334ba1fb'

所以 ，复合索引可以命中的，通过所以合并都可以满足。

但是像 CODE = '1001' and content ='订单内容：39ddbea6a235484f8c6ecc5b334ba1fb' 这种情况，使用复合索引还是比较合理。如果是单索引查询，还是索引合并合理。

索引失效

表结构

建立索引code

1. or查询

SELECT * FROM `order` WHERE CODE = '1001' and content ='订单内容：39ddbea6a235484f8c6ecc5b334ba1fb'

2. like查询是以’%'开头

SELECT * FROM `order` WHERE CODE like '%1001%'

3. 对查询的列上有运算或者函数的

查询id+2=5的行

SELECT * FROM `order` WHERE id + 2=  5

code后四位为1001

SELECT * FROM `order` where substr(code,-4)='1001'

4，类型进行了转换

code为varchar类型，但条件中赋予数值类型。

SELECT * FROM `order` where code =1001

5. left join 查询，索引列编码不同

增加表product

建立索引code

SELECT
	a.CODE aCode,
	b.CODE bCode 
FROM
	`order` a
	LEFT JOIN product b ON a.CODE = b.CODE

code的编码不同情况下：

code的编码相同情况：

以上情况是覆盖索引情况下，product会走索引。

SELECT
	a.CODE aCode,
	b.name name 
FROM
	`order` a
	LEFT JOIN product b ON a.CODE = b.CODE

如上查询，name字段不是覆盖索引，所以索引失效。

6. 连接查询中，按照优化器顺序的第一张表不会走索引

增加表product

不建立索引code

SELECT
	a.CODE aCode,
	b.name name 
FROM
	`order` a
	l JOIN product b ON a.CODE = b.CODE

如图，没有走索引

将left 改为right

SELECT
	a.CODE aCode,
	b.name name 
FROM
	`order` a
	right JOIN product b ON a.CODE = b.CODE

如图，order表走了索引。

改为使用inner join查询：

SELECT
	a.CODE aCode,
	b.name name 
FROM
	`order` a
	INNER JOIN product b ON a.CODE = b.CODE

如图，a表走了索引

从上面三个连接查询来看只有左外连接a表没有用到索引的，这就是因为由于是左外连接，所以优化器的执行顺序是a表、b表，也就是说首先全表扫描a表，再根据a表的name查询b表的值，所以a表无法用到索引。

一般这种连接查询，A表关联B表，要在将优化器顺序的第二张表上关联的字段上加索引，而第一张表则无需加索引，无用的索引也会影响性能。而第三个例子中内连接就不一样了，如果是内连接优化器本身就会根据索引情况，连接表的大小等去选择执行顺序了，所以上例中的内连接执行顺序是b、a，这样仍然可以用到a的索引。

7. 复合索引失效

不符合最左原则。

1. index（a,b） —— where b

索引为（code，content），这里只用到content，则不走索引

SELECT
	* 
FROM
	`order` 
WHERE
	    content ='订单内容：39ddbea6a235484f8c6ecc5b334ba1fb'

8. order by 索引失效情况

where中content已经使用索引

SELECT * FROM `order` where content ='sf' order by code

select查询中使用*或者不包含索引里的字段

code是索引列

SELECT * FROM `order`  order by code

没有使用到索引

如果是查询code：

SELECT code FROM `order`  order by code

使用到索引

9. not in，<>，！=和is not null操作

code为索引的not in或者<>查询，查询结果为select *。

SELECT * FROM `order` where code not in ('1001')

SELECT * FROM `order` where code <> '1001'

SELECT * FROM `order` where code is not null

SELECT * FROM `order` where code !='1001'

索引失效