案例:
CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `b` (`b`) ) ENGINE=InnoDB;
然后,我们往表 t 中插入 10 万行记录,取值按整数递增,即:(1,1,1),(2,2,2),(3,3,3) 直到 (100000,100000,100000)。
1)10万条记录是怎么插进去的?
存储过程
2)什么是存储过程?
其实就是java中的函数
3)插入10万条记录的存储过程怎么写?
delimiter ;; //告诉mysql,该段命令是否结束 create procedure idata() begin declare i int; set i=1; while(i<=100000)do insert into t values(i, i, i); set i=i+1; end while; end;; delimiter ; call idata();
4)执行select * from t where a between 10000 and 20000语句,选择是索引是谁?
a
5)执行下面图中的流程,那又是选择谁做索引?
不会再选择a做索引了。
5.1)为什么mysql会选错索引,不选择a做索引了?
MySQL 在判断扫描行数的时候出问题了
5.1.1)扫描行数是怎么判断的?
根据统计信息来估算。
5.1.2)那么这个统计信息是啥呢?
索引的“区分度“:一个索引上不同的值越多,这个索引的区分度就越好。一个索引上不同的值的个数,我们称之为“基数”。
5.1.3)MySQL 是怎样得到索引的基数的呢?
采样统计(选N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数)
5.1.4)为什么要采样统计?
整张表取出来一行行统计,代价太高
5.1.5)索引统计信息是固定的吗?
不是,数据表变,他也跟着变,1/M就变,比如10万条,M就是10,那更新了10000条数据的时候就重新统计索引信息。
5.1.6)索引信息是放在哪里的?
innodb_stats_persistent=on,放在磁盘。N为20,M为10.
innodb_stats_persistent=off,放在内存。N为8,M为16。
索引统计只是一个输入,对于一个具体的语句来说,优化器还要判断,执行这个语句本身要扫描多少行。
5.1.7)优化器预估的,Q1和Q2的扫描行数是多少?
set long_query_time=0; select * from t where a between 10000 and 20000; /*Q1*/ select * from t force index(a) where a between 10000 and 20000;/*Q2*/
Q1 的结果还是符合预期的,rows 的值是 104620。Q2 的 rows 值是 37116,而图 1 中我们用 explain 命令看到的 rows 是只有 10001 行,是这个偏差误导了优化器的判断。
6)我们上面说不再选择a作为索引,优化器为什么放着扫描 37000 行的执行计划不用,却选择了扫描行数是 100000 的执行计划呢?
用索引a我每次都得回表一次。
如果选择扫描 10 万行,是直接在主键索引上扫描的,没有额外的代价
优化器会估算这两个选择的代价,从结果看来,优化器认为直接扫描主键索引更快。当然,从执行时间看来,这个选择并不是最优的。
7)mysql选错索引的根本原因是什么?
没能准确地判断出扫描行数
8)当发现mysql选错索引了我们应该怎么办?
使用analyze table t 命令,可以用来重新统计索引信息
9)假设现在有如下语句,假如你来选择索引,你会怎样选择?
第一直觉当然是选a
select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
先来看一下 a、b 这两个索引的结构图
如果使用索引 a 进行查询,那么就是扫描索引 a 的前 1000 个值,然后取到对应的 id,再到主键索引上去查出每一行,然后根据字段 b 来过滤。显然这样需要扫描 1000 行。
如果使用索引 b 进行查询,那么就是扫描索引 b 的最后 50001 个值,与上面的执行过程相同,也是需要回到主键索引上取值再判断,所以需要扫描 50001 行。
10)实际上上面的语句mysql选择谁做索引?
竟然选择了b做索引。
11)为什么会选择b来做索引?
又选错了
12)为什么又选错了?
扫描行数的估计值依然不准确;
大多数时候优化器都能找到正确的索引,但偶尔你还是会碰到我们上面举例的这两种情况:原本可以执行得很快的 SQL 语句,执行速度却比你预期的慢很多
13)选错索引我们应该怎么办呢?
采用 force index 强行选择一个索引
在数据库内部来解决
14)用 force index有什么缺点?
不优美
如果索引改了名字,这个语句也得改
如果以后迁移到别的数据库的话,这个语法还可能会不兼容。
15)在数据库里面该怎样解决呢?
15.1)改变自己:
优化器放弃了使用索引 a,说明 a 还不够合适。我们可以考虑修改语句,引导 MySQL 使用我们期望的索引。
把“order by b limit 1” 改成 “order by b,a limit 1”
优化器选择使用索引 b,是因为它认为使用索引 b 可以避免排序(b 本身是索引,已经是有序的了,如果选择索引 b 的话,不需要再做排序,只需要遍历),所以即使扫描行数多,也判定为代价更小。
现在 order by b,a 这种写法,要求按照 b,a 排序,就意味着使用这两个索引都需要排序。因此,扫描行数成了影响决策的主要条件,于是此时优化器选了只需要扫描 1000 行的索引 a。
15.2)麻痹优化器
select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;
用 limit 100 让优化器意识到,使用 b 索引代价是很高的。其实是我们根据数据特征诱导了一下优化器,也不具备通用性
15.3)新建一个更合适的索引,或删掉误用的索引。
16)前面我们在构造第一个例子的过程中,通过 session A 的配合,让 session B 删除数据后又重新插入了一遍数据,然后就发现 explain 结果中,rows 字段从 10001 变成 37000 多。而如果没有 session A 的配合,只是单独执行 delete from t 、call idata()、explain 这三句话,会看到 rows 字段其实还是 10000 左右。你可以自己验证一下这个结果。这是什么原因呢?
session A开启了一致性读,目的为了保证session A的可重复读,insert只能 另起炉灶,不能占用delete的空间。所以出现的情况就是delete虽然删除了,但是未释放空间,insert又增加了空间。导致统计信息有误