一、Explain关键字查询结果列的信息
type类型为:Index
索引全表扫描,把索引从头到尾扫一遍,包含两种情况:
一种是查询使用了覆盖索引,那么它只需要扫描索引就可以获取数据,这个效率要比全表扫描快,因为索引通常比数据表小,而且还能避免二次查询。在extra显示using index。反之,如果在索引上进行全表扫描,没有using index的提示。
二、Explain 关键字主要是描述可能用到的索引列是什么;解析查询优化器选择的索引
① mysql是如何选择索引的?
1>首先会sql解析,优化join查询,
2>找到所有可能用到的索引列,
3>分别计算全表扫描,可能用到索引列的成本
4>选择成本最低的进行查询
② mysql查询成本公式:成本 = 加载的页数量*1.0(IO成本) + 查询的数据量*0.2(CPU成本);
IO成本:从磁盘加载到内存需要消耗的成本;
CPU成本: 内存中根据条件过滤数据的成本;
select * from user where id > '10101' and id < '20000' and to_date = '1991-10-10';(to_date列有二级索引);
1> 计算全表扫描的成本: 执行sql SHOW TABLE STATUS LIKE table_name;
rows: 展示的是: 数据的行数,对于innoDB来说该值并不准确是一个估算值,对于MyISAM是准确的。
Data_length:占用的存储空间字节数,使用MyISAM存储引擎的表来说,该值就是数据文件的大小,对于使用InnoDB存 储引擎的表来说,该值就相当于聚簇索引占用的存储空间大小,也就是说可以这样计算该值的大小:
Data_length = 聚簇索引页面数*每个页面的大小;所以页面大小 = Data_length /16k/1024;
所以全表扫描成本如下:
I/O成本:1252*1 = 1252。1252指的是聚簇索引占用的页面数,1.0指的是加载一个页面的成本常数。
CPU成本:442070*0.2 = 88414。442070指的是统计数据中表的记录数rows,对于InnoDB存储引擎来说是一个估计值,0.2指的是访问一条记录所需的成本常数 总成本:1252+88414 = 89666。
2> 计算主键索引的扫描成本: 执行sql SHOW TABLE STATUS LIKE table_name;
1、 计算IO成本:使用索引查询时,默认是1(不管是=、in、>、<这些操作都需要从索引中确定一个范围,不论这个范围区间的 索引到底占用了多少页面,查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的)。
2、计算CPU成本:分别算出区间 的最左表一条记录(最左区间),最右边一条记录(最右区间),从最左区间向后查找,如果到最右区间的距离小于10页,则精确计算出记录数,如果大于十页,则估算每页的记录数,然后乘以页数(根据父也的页号计算),估算出总的记录数。
3> 计算二级索引的扫描成本: 执行sql SHOW TABLE STATUS LIKE table_name;
1、因为通过二级索引查询需要回表,所以在计算二级索引需要成本时还要加上回表的成本。回表的成本就相当于 下面这个SQL执行: select * from user where id in (主键值1,主键值2 .........);
2、二级索引成本 = 二级索引成本 + 回表成本。
根据以上获取到的成本选取最小的值作为扫描条件,使用索引查询数据。
③ 关于null值得处理,null在数据库中有三种含义:
1、nulls_equal 代表值相同。innoDB默认使用该含义。
2、nulls_unequal 代表特殊值,所有的null值都是不同的值。
3、nulls_ignored 不具有含义,统计数据时被忽略。
为什么说索引列中不要添加null值,在情况1中,null值过多,造成mysql 统计数据时认为该列重复数据过多,在进行索引统计时,
重复数据过多,导致统计时 CPU成本高,(因为估算的时候会估算每个值大概平均是多少),优化器认为某个列中平均一个值重复次数特别多,所以倾向不使用该列做索引。
④ 关于 in查询的处理。select * from user where name in(1,2,3,4,5,6,7,........);
1、对于in查询如果in查询内的参数个数 <= 200(默认值是200,系统变量 eq_range_index_dive_limit决定,show variables like '%dive%'查看)时,mysql会逐一计算索引字段在表中的数据。
2 、如果in查询内的参数个数 > 200,比如1w,那么mysql统计数据的成本就过高了,所以他会进行估算。
首先 show index from user(表名) 的cardinality列能够显示出,对应的索引列中大概不重复的元素的个数(就是有多少种数据), 使用 SHOW TABLE STATUS LIKE 'user';(表名) 中的rows能够展示出数据表中大概有多少列(这两个统计对于innodb引擎来说都是不准的)。这样 rows / cardinality ,大概就可以计算出每索引记录对应的行数,再 乘以 in中的参数个数来统计回表的成本。in参数个数 * rows / cardinality。
三、join查询1、基于块的嵌套循环链接(驱动表数据加载到 join buffer 匹配被驱动表数据,减少IO成本)
扫描表的过程其实是先把表的数据从磁盘加载到内存,在内存中做匹配,对于join查询,比如一下sql:
select * from t1 join t2 on t1.a=t2.a where t1.b in(1,2);查询过程是先跟据where条件查找出t1表里的数据,然后再把被驱动表的所有数据与驱动表的数据一一匹配,每匹配驱动表的一条数据就需要加载查询所有的被驱动表,这样IO成本就比较高,所以mysql有一个join buffer的概念,join buffer 就是进行连接查询时的一块空间,他会缓存驱动表的 数据查询列 和 查询条件列,加载的被驱动表数据进行多条件匹配,增快匹配速度。这就是为什么查询时尽量不要使用 *的原因。这个join buffffer的大小是可以通过启动参数或者系统变量join_buffffer_size进行配置,默认大小为262144字节(也 就是256KB),最小可以设置为128字节。
2、外连接消除
对于内连接mysql优化器会优化 它的执行顺序,但是left join和right join因为顺序固定则不会优化。
内外连接区别:如果被驱动表中没有 on条件的数据,内连接会舍弃该数据,而外连接则会用null补充值。
3、派生表 select * from (select a,b from t1) as t;(我理解他就是临时表不知道对不对)
① 上面这种查询就会生成派生表 t ,有a,b两个字段。
② 我们可以将派生表的结果集写到一个内部的临时表中,然后就把这个物化表当作普通表一样参与查询。当然,在 对派生表进行物化时,使用了一种称为延迟物化的策略,也就是在查询中真正使用到派生表时才回去尝试物化派 生表,而不是还没开始执行查询就把派生表物化掉。比如:如果派生表查出来的数据为空则不需要物化。