索引的基本知识
1. 索引介绍
- 索引的出现就是为了提高数据检索效率，就跟书的目录一样。
- 索引不但在内存中，还写在硬盘中。
- 索引是存储引擎实现的。
2. 索引常见模型
- 搜索树: 每个节点左儿子小于父节点，父节点小于右节点. select/update 复杂度O(log(N))
- 哈希表: key-value存储数据. 哈希冲突的解决办法: 链表. 使用场景: 只有等值查询的场景.
- 有序数组: 按顺序存储。查询用二分法就可以快速查询，时间复杂度是: O(log(N))
- 查询效率高，更新效率低
- 适用场景: 静态存储引擎
3. 索引类型
- 主键索引(聚族索引): 叶子节点值存储的是数据行内容
- 非主键索引(二级索引): 叶子节点存储的是主键值
- 主键索引和普通索引的查询有什么区别
- select * from table where Id = 2;
- select * from table where T = “年后”
- 普通索引查询时，首先会命中非主键索引，然后找到叶子节点主键值2，然后找主键索引。相当于造成一次回表，所以尽量使用主键索引.
- hash索引:
- 主要就是通过Hash 算法（常见的hash算法有 直接定址法，平方取中法，折叠法，除数取余法，随机数法），
- 将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入hash表的对应位置；
- 如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应的Hash键下以链表形式存储。
- 目前, mysql只有memory引擎和NDB引擎支持
- full-text索引(全文索引):
- 同样适用B-tree 存放索引数据，但是使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），
- 索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。
- 用途： 用来代替效率很低的like模糊操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段
- 引擎支持: MYIASM, innodb在5.6开始支持
- R-tree空间索引: MyISAM的一种特殊索引类型，主要是地理空间数据类型。
- …
4. 索引的维护
B+树为了维护索引的有序性，需要做索引的维护。
页分裂、页合并:
页分裂: 一个数据页满了(有比例的，比如90%算满了)，按照B+tree算法，新增加一个数据页, 叫页分裂，会导致性能下降。
空间利用率降低大概50%.
页合并: 当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程.
自增主键的使用场景 == 为啥要用自增主键?
- 存储: 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间就越小。
- 性能: 业务字段往往不是有序的，(UUID) 每次插入就可能造成页分裂 + 数据移动，页分成两个，降低了空间利用率。
- B+树插入页分裂，删除数据页合并，二者都是比较重的IO消耗，最好采用自增主键，每次插入到最后一个数据页，如果页满了，就新建一个数据页。
业务字段用作自增主键
- 只能是一个索引
- 该所以必须是唯一索引，这是典型的KV场景，where ke = N
- 由于没有其他索引，所以不用考虑叶子节点大小的问题，故将该值设为主键索引。
额外case
一个数据页默认16KB, B+树是有N叉树，那怎样调整N?
- key值调整
- N叉树中非叶子节点存放的是索引信息，索引包含key 和 point指针。Point指针固定为6个字节，假设一个key为10个字节，一个数据页16KB，那么此时一个数据页就能存放1024个索引，此时N = 1024。
- 改变页的大小
- 页越大，一个页存放的索引就越多，N就越大。
数据页调整后，如果数据页太小，层数就会越高，扫描数据页次数增加。
数据页越大，数据页加载到内存时间和单个数据页查询时间就会提高，需要达到平衡。
重建索引
optimize table alter table engine = innodb;
8bit(位)=1Byte(字节)
1024Byte(字节)=1KB
索引的特性
1. 回表
当二级索引去找叶子节点，然后根据叶子节点中主键id去找主键索引，此时找到数据行，称为回表。
回到主键索引树搜索的过程，叫做回表。
2. 如何避免回表?
覆盖索引: 如果我们把被选择的字段加入到where 字段的索引中，无需去主键索引中找数据了。直接通过二级索引就能拿到数据。
目的: 减少搜索树的次数，提升查询性能。
2.1 最左前缀原则
联合索引的最左N个字段，也可是字符串索引的最左M个字符。
B+树就是采用索引的最左前缀原则，来定位记录的.
联合索引: 其实就是多个字段组成的组合索引.
覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，
不用回表操作，直接返回结果，减少IO磁盘读写读取数据.
小case:
请问已经有了一个name_age联合索引，还需要一个name索引吗?
alter table table_name add index idx_name_age(name, age);
('李四', 22), ('王五',33),('张三', 10),('张三', 10),('张三',20),('六六',40)
不需要？为哈?
因为索引中的索引项是根据索引定义中的字段顺序排序的。
此时
select * from user where name = '张三'; 此时就会命中idex_name_age
select * from user where name like '张%'; 也会, 但是'%三'会使命中索引失效
2.2 建立联合索引的原则
- 如果通过调整顺序，可以少维护一个索引，那么这个顺序就可以优先考虑. 如果有name_age, age此时我看第二个.
- 空间问题, 如果name字段是比age大的，新建name_age, age两个索引. 因为age查询时，无法命中name_age。
- 所以，还是那句话，索引的索引项是根据索引定义中的字段顺序排序的。
- 可以通过索引选择性来看name和age的顺序
- select count(distinct name)/count(name), count(distinct age)/count(age) from table\G.
3. 索引下推
联合索引idx_name_age 假如一个select * from table where name like ‘hello%’ and age >10检索.
- MySQL5.6版本之前，会对匹配的数据进行回表查询. 也是只会命中name开头hello的数据，然后回表.
- 5.6版本之后，也是只会命中name开头hello的数据C，并且会先过滤C中的age<10(不满足条件的)数据，然后在进行查询，
减少回表率，提升检索速度.
索引存储
0. 为啥删除了表一半的数据，表文件大小没变化?
delete 命令其实只是把记录的位置，或者数据页标记为"可复用"，但磁盘文件的大小是不会变的，也可以认为是一种逻辑删除，所以物理空间
没有实际释放，只是标记为可复用，表文件的大小当然是不会变的。
1. 表数据信息
首先介绍一个参数: innodb_file_per_table:
off: 表存放在系统共享空间或和数据字典在一起. drop表空间也不会回收.
on: 存放在.ibd文件中方便操作. drop直接删除空间.
2. 表结构信息
首先，表结构定义占有的存储空间比较小，在MySQL8.0之前，表结构的定义信息存放在.frm为后缀的文件中，
在MySQL8.0之后，则允许把表结构的定义信息存放在系统数据表中。
系统数据表: 主要用于存储MySQL的系统数据，比如: 数据字典、undo log(默认)等文件.
3. 如何才能删除表数据后，表文件大小就变小?
重建表，消除表因为进行大量的增删改操作而产生的空洞，使用如下命令:
3.1 alter table t engine=innodb(recreate)
5.5和之前，server层，自动temp_table, A -> B -> A如果在临时表插入数据时，A表写入数据， 此时会造成数据丢失, 也就是说这个DDL不是online的。
5.6 之后，innodb层, temp_file, online_DDL
流程:
- 新建一个临时文件，扫描表A主键的所有数据页。
- 用数据页中表A的记录生成B+树，存储到临时文件中
- 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中.
- 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件.
- 用临时文件替换表A的数据文件.
alter 首先会获取MDL写锁，然后退化为MDL读锁，因为需要实现online, 读锁可以不影响其它DML操作。
为啥不写锁后直接解锁，因为可能存在其它线程去DDL修改表结构。
MDL:DML的锁.
3.2 optimize table t (== recreate + analyze(统计索引的情况))
3.3 truntace table t (==drop + create)
在重建表过程中，online == replace (其实就等于原地操作)
但是8.0之前
full index / 空间索引时，inplace 会阻塞DML操作，所以此时不为online
online 可以为inreplace
inplace 不一定为online
4. 空洞是啥？咋产生的？
空洞就是那些被标记可复用但是还没被使用的存储空间。
使用delete命令删除数据会产生空洞，标记可复用。
插入新的数据可能引起页分裂，也可能产生空洞
修改操作，有时是一种先删后插的动作也可能产生空洞
数据页的复用和记录的复用是不同的。
记录是范围性的，数据页哪块需要去哪块。
5. 唯一索引和普通索引选择?
建议普通，因为唯一索引不适用于change buffer(存在于buffer pool中)
首先看一下，change buffer是啥是innodb中buffer pool中的一个存储机制。
当有一个更新操作的时候，如果数据页在内存中就直接更新，而数据页没有在内存中，就把更新操作记录在change buffer中，
下次等有查询这个数据页的时候，从磁盘获取数据页载入内存，执行change buffer中更新操作，然后返回给客户端。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。
merge时机:
- 除了访问数据时触发merge，
- 系统后台线程定制会执行merge。
- 数据库正常关闭(shutdown)的过程中，也会执行merge操作.
我们再看为啥不建议使用唯一索引用change buffer
假如一个更新操作了来了。
- 如果数据页在内存中
- 唯一索引: 找位置，没有冲突，更新
- 普通索引: 直接更新
- 如果数据页不在内存中
- 唯一索引: 将数据页读入内存，没有冲突时，更新
- 普通索引: 将更新操作记录在change buffer 中.
很明显，唯一索引增加了一次读磁盘操作，将数据页从磁盘载入内存是一个IO过程(成本高)。
change buffer很大程度上减少了随机读磁盘的访问次数，提升了性能。
有一个小疑问：系统掉电重启，change buffer会丢失吗?
不会，change buffer 又存在于system table data ibdata(系统表空间里)
机器重启后，重新加载入innodb buffer pool内.
和redo log区别:
redo log侧重于节省写磁盘的IO消耗(转成顺序写)
change buffer 侧重于节省读磁盘的IO消耗，适用于写多读少场景。如账单，日志类系统。
索引其他
1、如何给字符串建立索引?
- 直接创建完整索引，这样可能比较占用空间
- 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。
alter table user add index index1(name(6)) 6个字节
select count(distinct left(name, 3)), count(distinct left(name, 4)) from user;
尽量提高区分度，这样重复率减少，回表次数减少。
- 倒叙索引，再创建前缀索引，有于绕过字符串本身前缀的区分度不够的问题.
- 创建hash字段索引只要保证唯一性即可，查询性能稳定，有额外的存储和计算消耗，和第三种一样，都不支持范围扫描。 常见crc32/64, 但不能返回查找，倒叙也一样。
2、执行order by 过程
前提: mysql会给每个线程分配一块内存空间, 用于排序sort_buffer.
create table kk (
id, name, city, age
)
explain select city, name, age from kk where city = '杭州' order by name asc limit 100;
// using index condition, using filesort.
执行过程:
- 初始化sort_buffer, 去确定能放下name, city, age 三个字段.
- 从索引city 中找到第一个命中杭州的主键ID_X
- 拿着ID_X 去找主键索引，找到一条记录, city, name , age 放入sort_buffer中.
- 从city索引中继续3，直到不满足条件为止.
- 对sort_buffer中的数据按照name字段做快速排序.
- 按照结果返回100行给客户端.
在哪排序? 内存中还是外部文件排序?
- 需要排序数据大小 < sort_buffer_size, 内存中.
- 否则, 不得不利用磁盘临时文件辅助排序.
如果Mysql认为排序的单行长度太大怎么办？
- set max_length_for_sort_data = 16; //字节
- 参数: 如果单行字节(city + name + age) 长度大于这个值，就用rowId + name(需要排序的字段)进行排序，最后再回表查询结果返回客户端.
如果 alter table kk add index idx_city_name(city, name)
此时name 天然有序, 此时就不会排序, 直接返回结果集.
explain 就没有using filesort, 只有using index condition
如果alter table kk add index idx_city_name_age(city, name, age)
此时命中覆盖索引, 性能上会快很多, 不用回表拿数据
explain extra中 只有using index, using where
附录:
hash算法
https://www.cnblogs.com/lyfstorm/p/11044468.html
开发地址法
元素取模，填入数组，发生冲入，直接后移找空位，并记录。
再hash法
多次hash
链地址法
建立公共溢出区
分为基本表和溢出表，凡是和基本表发生冲突的元素，一律入溢出表
为啥采用B+树而不是B树
首先，来看两者的区别
- 存储: B树非叶子节存储指针,键值,数据，而B+树非叶子节点存储指针和键值，叶子节点存储具体的数据，B+树的叶子节点是双向链表，而且头结点和尾节点都是循环指向的，方便搜索.
- IO性能: 相同数据量下，一个B树非叶子节点存储的指针相对B+树更少，这样B树的层级就会越高，扫描相同数据，IO更频繁。
- 磁盘加载: 当数据量比较大的时候，就不能把整个索引全部加载到内存中，只能逐个加载每个磁盘页(对应索引树的节点)
B树存储图:
为啥不采用hash方式？
首先，hash索引是key-value存储的，实际上是没有任何顺序关系的。
- 对于区间查找时无法通过索引搜索，只能全表扫描。它只适用于等值查找。B+树是多路平衡查找树，它的索引是天然有序的，所以可以范围查询。
- hash索引不支持多列联合索引的最左匹配规则，如果有大量重复键值的情况下，hash效率会很低，因为存在hash碰撞。
怎样查看sort_buffer在哪排序
/* 打开optimizer_trance, 只对本线程有效 */
set optimizer_trance='enabled=on'
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name, age from t where city='杭州' order by name limit 100;
/* 查看optimizer_trace输出 */
select * from `information_schema`.`OPTIMIZER_TRACE`\G
/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算Innodb_rows_read差值 */
select @b-@a;
这个方法是通过查看OPTIMIZER_TRACE 的结果来确认的, 你可以从number_of_tmp_files中看到是否使用了临时文件.
number_of_tmp_files: 表示排序过程中使用的临时文件数, 咦, 为啥是12个?
因为内存放不下, 使用了外部排序 ---- 归并思维.
<font color='red'>Mysql将排序的数据分为12份, 每一份单独排序后存在这些临时文件中。最后把这12份有序文件再合并成一个有序的大文件.</font>
如果number_of_tmp_files == 0, 证明了sort_buffer_size 大于需要排序的数据量, 在内存中排序.
否则就需要放在临时文件中排序。sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大。
"filesort_summary": {
"rows": 2,
"examined_rows": 2, // 真是扫描行数, 注意是执行器server, 而不是引擎层面, 因为rowId时, 引擎排序完之后会回表查.
"number_of_tmp_files": 0,
"sort_buffer_size": 584,
"sort_mode": "<sort_key, rowid>"
}
为啥是rowId 因为,select 后面字段大小 > max_length_for_sort_data == 1024
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
