索引导图
2019-08-09 14:16:37 59 举报AI智能生成
Mysql索引部分知识
作者其他创作
大纲/内容
没有索引的查找
在一个页中的查找
以主键为搜索条件
1. 在页目录中使用二分法快速定位到对应的槽
2. 遍历该槽对应的分组记录
以非主键为搜索条件
从最小记录开始依次遍历单链表的每条记录
在很多页中查找
步骤
1. 定位到记录所在的页 (【困难】)
2. 从所在的页内中查找相应的记录
缺点
因为不能定位所要查找的记录具体在哪一个页。所以只能从第一个页开始，沿着双向链表找下去。
然后在每一个页中使用【在一个页中的查找】的步骤
然后在每一个页中使用【在一个页中的查找】的步骤
索引方案
索引需要完成的事情
下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。（页分裂操作）
给所有的页建立一个目录项
目录项内容
页的用户记录中的最小主键值
页号
InnoDB中的索引方案
问题
由于一个页只有16KB，当表的记录数量增多时，一个页装不下所有的目录项
当删除一个页时，目录项怎么处理？如果目录项页删掉，那么需要大量移动其他目录项
想根据主键值查找一条用户记录需要经过的3个步骤
1. 确定目录项记录页 （多级目录查找）
2. 通过目录项记录页确定用户记录真实所在的页 （页内二分法找组+组内遍历找记录）
3. 在真实存储用户记录的页中定位到具体的记录 （页内二分法找组 + 组内遍历找记录）
几种索引类型
聚簇索引
特点
数据即索引，索引即数据
1. 使用记录主键值的大小进行记录和页的排序
页内的记录是按照主键的大小顺序排成一个单向链表
各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
存放目录项记录的页分为不同的层次，【在同一个层次中】的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
2. B+树的叶子节点存储的是完整的用户记录
二级索引
特点
1. 使用记录C2列的大小进行记录和页的排序
页内的记录是按照C2列的大小顺序排成一个单向链表
各个存放用户记录的页也是根据页中的记录的C2列的大小顺序排成一个双向链表
存放目录项记录的页分为不同的层次，在同一个层次中的页也是根据页中目录项记录的C2列大小顺序排成一个双向链表
2. B+树的叶子节点存储的并不是完整的用户记录，而是C2列+主键这两个列的值
3. 目录项记录中不再是主键+页号的搭配，而变成了C2列+主键+页号的搭配。（主键是为了确保二级索引目录项的唯一性）
联合索引
可以同时以多个列的大小作为排序规则，即同时为多个列建立索引。
以为c2和c3列建立联合索引为例子
1. 先把各个记录和页按照C2列进行排序
2. 在记录的c2列相同的情况下，采用c3列进行排序。
InnoDB的B+树索引的注意事项
1. 根页面万年不动窝
一个索引==》2个段（叶子，非叶子） ==》 INODE Entry中的链表基节点
2. 内节点中目录项记录的唯一性
非叶子节点
索引列+主键+页号
3. 一个页面最少存储2条记录
MyISAM的索引方案
特点
数据即数据，所以即索引
数据文件
顺序插入
行号 or 偏移量 + 行格式
索引文件
索引列+主键列+行号
创建/维护索引的相关语句
B+树索引的使用
索引的代价
空间上的代价
每建立一个索引都要为它建立一颗B+树，而我们知道一颗B+树对应两个段【叶子段，非叶子段】。
并且每个节点都是一个数据页，一个数据页默认会占用16KB的存储空间。
并且每个节点都是一个数据页，一个数据页默认会占用16KB的存储空间。
时间上的代价
每当对数据进行增删改操作时，都需要维护索引树。
如果建了很多索引，则每个索引对应的B+树都要进行维护。
如果建了很多索引，则每个索引对应的B+树都要进行维护。
B+树索引适用于下边这些情况
怎么看是否可以走索引？
在给定条件下，数据是否有序
1. 全值匹配
2. 匹配左边的列
3. 匹配列前缀
4. 匹配范围值
5. 精确匹配某一列并范围匹配另一列
6. 用于排序
不使用索引的文件排序(filesort)
不能使用索引排序的情况
ASC、DESC混用
WHERE子句中出现非排序使用到的索引列
情景：
聚簇索引： id
联合索引 : (name,birthday,phone_number)
聚簇索引： id
联合索引 : (name,birthday,phone_number)
SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;
这条语句只能全表扫描出所有country="China"的记录，然后再进行排序
这条语句只能全表扫描出所有country="China"的记录，然后再进行排序
SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;
由于name="A"可以走联合索引，并且这种情况下，birthday和phone_number都是有序的。
由于name="A"可以走联合索引，并且这种情况下，birthday和phone_number都是有序的。
排序列包含非同一个索引的列
SELECT * FROM person_info ORDER BY name, country LIMIT 10;
假设这种情况走联合索引，极端情况下所有name都相同，但是country无法排序呀，还是要进行一次文件排序。那还不如直接走文件排序
假设这种情况走联合索引，极端情况下所有name都相同，但是country无法排序呀，还是要进行一次文件排序。那还不如直接走文件排序
排序列使用了复杂的表达式
7. 用于分组
回表的代价
当访问二级索引时，使用顺序I/O，访问聚簇索引使用随机I/O
覆盖索引
查询列表 是 用到的所用列的子集。那就不用回表啦
所以不要使用*号作为查询列表，要把我们需要查询的列标明。不然本来能走索引的，哪天加了一个字段就走不了了
如何挑选索引
只为用于搜索、排序或分组的列创建索引
为列的基数大的列创建索引
列的基数大小，比如性别。那能过滤的记录就很少了。回表代价太高
索引列的类型尽量小
数据类型越小，在查询时进行的比较操作越快
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录。从而减少磁盘IO带来的性能损耗。
也就意味着可以把更多的数据页缓存在内存中。
也就意味着可以把更多的数据页缓存在内存中。
索引字符串值的前缀
字符串值做索引的注意点
B+树索引中的记录需要把该列的完整字符串存储起来，而且字符串越长，在索引中占用的存储空间越大
如果B+树索引中索引列存储的字符串很长，那比较时间也会更多
可以只对字符串的前几个字符串做索引。
虽然不能精确记录的位置，但能定位到范围区间的左边界和右边界。
只要把这个区间的记录都取出来再进行内容比较就可以啦
只要把这个区间的记录都取出来再进行内容比较就可以啦
索引列前缀对排序的影响
SELECT * FROM person_info ORDER BY name LIMIT 10;
因为二级索引不包含完整的name列信息，因此无法对前10个字符相同，后面字符不同的记录进行排序，所以只能使用文件排序了
只有索引列在比较表达式中单独出现才可以适用索引
为了尽可能少的让聚簇索引发生页面分裂和记录位移的情况，建议让主键拥有AUTO_INCREMENT属性
定位并删除表中的重复和冗余索引
尽量使用覆盖索引进行查询，避免回表带来的性能损耗
0 条评论
下一页
