Mysql索引原理和使用姿势
标签: Mysql索引原理和使用姿势 博客 51CTO博客
2023-04-03 18:23:45 136浏览
索引的简单使用
- 建立一个表
- 写入了20几万条数据
- 没有索引的搜索
SELECT * FROM `order` WHERE CODE = '1001'
耗时:0.376s
4,给code建立索引,同样进行搜索
SELECT * FROM `order` WHERE CODE = '1001'
耗时: 0.020s
- 对比
加了索引的查询明显提升了一个级别,如果数据量再大,效果会更加明显。
分析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+树性质
- 索引字段要尽量的小:通过上面的分析,我们知道IO次数取决于b+数的高度h,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N一定的情况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。
- 索引的最左匹配特性(即从左往右匹配):当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亿(左右)
高度为三层,就可以存储这么多是数据。
索引管理
索引分类
- 普通索引index :加速查找
- 唯一索引
主键索引:primary key :加速查找+约束(不为空且唯一)
唯一索引:unique:加速查找+约束 (唯一) - 复合索引
-primary key(id,name):复合主键索引
-unique(id,name):复合唯一索引
-index(id,name):复合普通索引 - 全文索引fulltext :用于搜索很长一篇文章的时候,效果最好。
- 空间索引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'
复合索引的失效情况:
复合最左原则
- index(a,b) —— where b
SELECT
*
FROM
`order`
WHERE
content ='订单内容:39ddbea6a235484f8c6ecc5b334ba1fb'
- 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. 复合索引失效
不符合最左原则。
- 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'
索引失效
好博客就要一起分享哦!分享海报
此处可发布评论
评论(0)展开评论
展开评论