目录
一、答疑
Q：如果插入的数据是在主键树叶子结点的中间，后面的所有页如果都是满的状态，是不是会造成后面的每一页都会去进行页分裂操作，直到最后一个页申请新页移过去最后一个值
A：不会不会，只会分裂它要写入的那个页面。每个页面之间是用指针串的，改指针就好了，不需要后面的全部挪动
Q：插入数据如果是在某个数据满了页的首尾，为了减少数据移动和页分裂，会先去前后两个页看看是否满了，如果没满会先将数据放到前后两个页上，不知道是不是有这种情况
A：对，为了增加空间利用率
Q：为什么需要索引重建？
A：索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。
二、普通索引和唯一索引
1.查询过程
例子：select id from T where k=5
这个查询语句在K索引树的查找过程，先从B+树的树根开始，按层搜索到叶子节点，即数据页，再在数据页记录内部通过二分法来定位记录
普通索引：查找到满足条件的第一个记录（5，500）后，需要继续查找下一个记录，直到碰到第一个不满足k=5的记录
唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止检索
由于innodb的数据是按照数据页为单位来读写的，当找到k=5的记录的时候，他所在的数据页基本上都在内存里了，所以对于普通索引来说，要多做的那一次查找和判断下一条记录，就只需要一次指针寻址和一次计算。
所以此时，普通索引和唯一索引差别不大
2.更新过程
例子：插入一个新纪录（4，400）
①这个操作要更新的数据页已经在内存中
对于唯一索引来说，找到数据页中是否有4的记录，如果没有直接插入，如果有则冲突，执行结束
对于普通索引来说，找到3和5之间的位置，插入这个值，执行结束
====>>>效率差不多
②这个操作要更新的数据页没有在内存中
对于唯一索引来说，从磁盘中找到该记录所在的数据页，加载到内存中，若没有冲突，则直接插入这个值，执行结束
对于普通索引来说，直接写入change buffer中，执行结束
====>>>将数据从磁盘读到内存，涉及到随机IO访问，是数据库中成本最高的操作之一，change buffer因为减少了磁盘随机访问，所以能够提高性能
注：
1.change buffer
change buffer基本使用:
①当需要更新一个数据页时，若当前数据页在内存中，则直接更新；
若这个数据页还没有在内存中，在不影响数据一致性的情况下，innodb会把这些更新操作缓存在change buffer，这样就不需要从磁盘读取数据页到内存中。
在下次查询需要访问这个数据页的时候，将数据页读入内存，将会执行change buffer中与这个数据页相关的操作。
②将change buffer中的操作应用到内存的数据页中并写入到redo log，得到最新结果的过程称为merge。
除了查询这个数据页会触发merge以外，系统后台还会有定时任务来触发merge，在数据库正常关闭的时候也会触发merge
change buffer的适用场景：
①对于唯一索引来说，所有的更新操作都会判断这个操作是否违反唯一性约束。比如，要插入（4，400）这个记录（4为主键），就要先判断现在表中是否存在k=4的记录，而这必须要将数据从磁盘读到内存中，
这样才能进行判断k=4是否存在，如果已经都读入到内存中，那直接更新内存会更快，就没必要写入change buffer了。
因此，唯一索引的更新就不能使用change buffer，只有普通索引才能够使用
②因为merge的时候是真正进行数据更新的时刻，而change buffer的作用就是将记录变更的动作缓存下来，所以在一个数据页做merge之前，change buffer记录该数据页上的变更越多越好
因此，对于写多读少的业务来说，写完数据页马上读到该数据页的可能性比较小，此时使用change buffer最好。
反之，如果写完以后马上会查到该数据页，会立即触发merge过程，如果频次较高，则change buffer的维护成本增大，所以对这这种业务模式来说，change buffer反而起到了反作用
change buffer的大小：
change buffer用的是buffer pool中的内存，不能无限增大，change buffer的大小，可以通过innodb_change_buffer_max_size来动态设置。
这个参数设置为50时，表示最多只能占用buffer pool的50%空间
2.change buffer 和 redo log 区别
change buffer主要是节省随机读磁盘的IO消耗（唯一索引，需要随机读磁盘到内存中，再更新，非唯一索引只需要写到change buffer）
redo log主要是节省随机写磁盘的IO消耗，转成顺序写
change buffer 的操作也会记录到redo log 中，进行持久化磁盘
三、优化器的索引选择
1.影响优化器选择索引的主要因素
①扫描的行数
MySQL在真正开始执行语句之前，并不能精确的知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录的行数。
这个统计信息就是索引的区分度。一个索引上不同的值越多，这个索引的区分度越高。而一个索引上不同值的个数，称之为基数，也就是说，基数越大，索引的区分度越好
基数的获取：
innodb默认选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到这个索引的基数。当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计
索引统计的配置：innodb_status_persistent
设置为on时，表示统计信息会持久化存储。这时，默认N为20，M为10
设置为off时，表示统计信息会存储在内存中。这时，默认N为8，M为16
但是由于是估算，所以会出现统计信息不正确的情况，可以使用 analyze table t 命令，重新统计索引信息
知道了一个辅助索引上不同值的个数，即基数，又如何知道一个辅助索引值下面平均对应多少条记录？
②回表
使用辅助索引，每次从辅助索引拿到一个主键值，都要去主键索引上查出整行的数据，这个代价优化器也会算进去。
导致不走辅助索引，直接使用主键索引全表扫描
③是否排序
例子：mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
a,b均有索引，order by b 有可能导致优化器选择索引b进行查询，因为根据索引b查询的话，不需要再做排序，只需要遍历
④临时表
2.选择最合适的索引
优化器选择索引不能保证每次都是最优的，有可能选择别的索引，所花费时间更少！！！！
如果出现原本可以执行很快的SQL语句，由于索引选择错误却执行的比较慢，可以使用以下方法处理：
①使用force index强行选择一个索引
select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
②修改SQL语句，引到MySQL使用我们期望的索引（具体情况具体修改）
③新建一个更合适的索引，或者删除误用的索引
根据实际需要，删除优化器选择的错误索引
④使用 analyze table t 命令，重新统计索引信息
如果explain结果预估的rows值和实际情况差距比较大，可以采用这个方法来处理
四、字符串索引的设置
1.整个字符串建立索引和前缀索引
例子：邮箱建立索引
email：1234@qq.com、12345@qq.com
前者：alter table User add index index1(email);
后者：alter table User add index index2(email(4));
两种方式的对比，where email = "1234@qq.com"
①前者索引文件占用的磁盘空间比后者大，前者索引文件叶子节点存储全文，后者索引文件叶子节点存储前4个字符
②前者在索引记录查询到符合条件的，继续往下查，有不符合的直接结束
后者在索引记录查询到符合条件的，需要去主键索引中查询是否条件，无论符合，都需要继续往下查，直到索引记录不符合条件
因此，前者与后者相比，查询次数一般较少，但是占用空间较大。如果想使用后者，必须定义好长度，既减少额外的查询成本，又节省空间
2.选择多长的前缀建立索引
①查询这个列上不同值的数量
select count(distinct email) from User;
②依次选取不同长度的前缀值的数量
select count(distinct left(email,4)) as L4,
count(distinct left(email,5)) as L5,
count(distinct left(email,6)) as L6
from User;
总之，根据不同前缀占的比重来选择合适的前缀索引
3.前缀索引对覆盖索引的影响
select id,email from User where email='1234@qq.com';
如果email字段使用整个字符串建立的索引，则不需要回表，直接查询完成
如果email字段使用前缀索引，则需要回表，不能使用覆盖索引的优化
4.前缀区分度不大的处理方式
①倒序存储
如果字符串前缀区分度不大，最后几位区分度大，该字段可以使用倒序存储
使用之前，需要验证倒序完的前缀是否有足够的区分度（count(distinct)）
②使用hash字段
表中新增一个整数字段，并在该字段上增加索引
相同点：
①都不支持范围查找，只能支持等值查询
不同点：
①占用空间
前者不需要额外的存储空间，后者需要新增一个整数字段
后者索引只需要4个字节，如果前者的索引前缀长度大于4字节，那么其实两者占用的空间差不多
②CPU消耗
前者每次读和写需要调用reverse函数，后者需要需要额外调用crc32()函数，相比起来，前者消耗小
③查询效率
前者本质还是前缀索引的方式，还是会增加扫描行数
后者查询更加稳定，虽然crc32()会有冲突概率，但是非常小
五、order by工作流程
例子：city字段为普通索引
select city,name,age from t where city='杭州' order by name limit 1000 ;
1.全字段排序
MySQL会给每个线程分配一块内存用于排序，称为sort buffer（server层）
①初始化sort buffer，确定放入name、city、name这三个字段
②根据索引city字段找到第一个满足city=‘杭州’条件的主键id，即ID_X
③根据上一步找到的主键id去主键索引中找到整行数据，取出city、name、age三个字段的值，存入到sort buffer
④重复2-3步骤，直到找不到满足city=’杭州‘条件
⑤对sort buffer中的数据根据name进行排序
⑥取出前1000行返回客户端
按照name排序这件事情，可能在sort buffer中进行，也可能需要使用外部排序，这取决于排序所需要的内存和参数sort_buffer_size
即所需要的内存大于sort_buffer_size，则使用磁盘临时文件辅助排序，反之，使用sort_buffer进行排序
外部排序一般使用归并排序算法，比如，将需要排序的数据分成12份，每一份单独在sort_buffer排序后存在这些临时文件中，然后把这12个有序文件合并成一个大的有序文件（归并排序）
2.rowid排序（即主键）
如果要查询的字段很多，sort_buffer中需要存放的字段太多，这样内存中同时存放的行数很少，要分成很多的临时文件，导致排序的性能下降
set max_length_for_sort_data=16（sort_buffer中单条记录最大字节数）
此时，如果单行的长度超过了上面这个配置，MySQL会采取另外一种策略。
①初始化sort_buffer，确定放入name和id（需要使用name进行排序、使用id最后再查询）
②从索引city字段中找到第一个满足第一个city='杭州'条件的主键id，即ID_X
③根据找到的主键id，去主键索引中找到整行数据，取出name、id两个字段，存入到sort_buffer中
④重复2-3步骤，知道找不到满足city='杭州'条件
⑤对sort buffer中的数据根据name进行排序
⑥排序完成后，取出前1000行，并按照id去主键索引中查询出city、name、age三个字段，返回客户端
总的来说，如果排序内存太小，才会使用rowid排序或者是使用外部文件；如果内存足够大，则会优先选择全字段排序，不需要去原表取数据。
3.如何避免排序操作
①建立city和name的联合索引，这样就不需要额外的排序操作，直接查询出前1000条记录就可以了
②建立city、name、age的联合索引，不仅避免了额外的排序操作，还避免了回表查询age的操作
六、索引失效
1.对索引做函数操作
对索引做函数操作，可能会破坏索引值的有序性，因此优化器就会决定放弃走树搜索功能。但是，并不意味着优化器放弃使用索引，可能走遍历主键索引，也可能遍历普通索引
例子：select count(*) from t where month(create_time)=7;（create_time索引图如下）
但是，对于select count(*) from t where id +1 = 10000这个SQL语句，加一操作并不会改变有序性，但是MySQL优化器还是不能用id索引来快速定位到9999这一行，
所以，需要写成where id = 10000 - 1才可以。
2.隐式类型转换
字符串和数字作比较的时候，会将字符串转换成数字
例子：select * from tradelog where traceid = 110;（traceid字段类型为varchar，普通索引）
===>select * from tradelog where CAST(traceid AS signed int) = 110;
本质上还是对索引做函数操作，因此优化器放弃走树搜索功能
例子：select * from tradelog where id = "110";（id字段类型为bight，主键索引）
===>select * from tradelog where id = CAST("110" AS signed int);
没有对索引字段做函数操作，因此优化器选择走树搜索功能
3.隐式字符编码转换
字符集utf8mb4是utf8的超集，当这两个类型的字符串做比较时，会先将utf8字符串转换成utf8mb4字符集，再进行比较
例子：trade_detail的traceid为utf8编码，trade_log的traceid为utf8mb4编码，两个表的traceid和id均为索引
①mysql> select detail.* from trade_log log, trade_detail detail where detail.tradeid=log.traceid and log.id=2;
此时log表可以根据索引查询到id为2的数据，此时拿到了log表的traceid（utf8mb4编码），
由于 utf8 = utf8mb4，导致左边的detail.traceid需要进行函数转换编码，从而detail不走索引
===>where CONVERT(detail.tradeid USING utf8mb4) = log.traceid
②where detail.tradeid=log.traceid and detail.id=2;
此时走detail的id索引和log的traceid索引
295
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
