索引失效
对我而言这诗也就只能记住半句,“全值匹配我最爱”。。。虽然我记不住诗,我也觉得没必要记住
用不用索引,是优化器说了算,优化器是基于成本开销的,索引失效就是了解优化器的规则
全值匹配我最爱
-- 添加联合索引
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME, age, pos);
-- type=ref key_len=74
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
-- type=ref key_len=78
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
-- type=ref key_len=140
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
这个其实是说,查询优化器会选择最长的那个索引,如下
最佳左前缀法则
最佳左前缀法则主要指联合索引,指的是查询从索引的最左前列开始并且不跳过索引中的列。
idx_staffs_nameAgePos(name, age, pos);
反面例子1:带头大哥不能死
-- type=ALL key_len=NULL EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev'; -- type=ALL key_len=NULL EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';
反面例子2:中间兄弟不能断,不然就接不上了,只用到一个name索引,没有使用pos上的索引
-- type=ref key_len=74 EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND pos = 'dev';
正面例子:索引顺序不重要,但是不推荐打乱顺序,让mysql做隐式的转换和优化
-- type=ref key_len=140 EXPLAIN SELECT * FROM staffs WHERE age = 25 AND NAME = 'July' AND pos = 'dev' ;
计算、函数、类型转换导致索引失效
不在索引列上做任何操作(计算、函数、(自动或者手动)类型转换),会导致索引失效而转向全表扫描
函数导致索引失效
一个一个取出来做函数运算
那怎么写好呢,如下可以是相同的效果。但是使用到了name字段的索引
计算导致索引失效
一个一个取出来做运算,第一条sql会导致索引失效
类型转换导致索引失效
正确写法:直接给字符串
范围条件右边的列索引失效
这个右边,不要看where语句里的左右,要看的是索引里的右边。。
如下,因为classId使用了范围,则name索引失效。这个是看索引里的顺序的,而不是where里怎么写。
下面这么写可以吗?没有意义,优化器本身是可以颠倒顺序的,它看的是索引的顺序
那怎么优化呢?
创建索引的时候,如下这么创建:
is null 可以使用索引,is not null无法使用索引
like以通配符%开头索引失效
like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
-- type=ref key=idx_staffs_nameAgePos key_len=74
EXPLAIN SELECT * FROM staffs WHERE NAME='July';
-- type=ALL key=null key_len=null
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%July%';
-- type=ALL key=null key_len=null
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%July';
-- type=range key=idx_staffs_nameAgePos key_len=74
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE 'July%';
不等于(!=或者<>)索引失效
mysql 在使用不等于(!= 或者<>)的时候有时候无法使用索引会导致全表扫描
-- type=ref key=idx_staffs_nameAgePos key_len=74
EXPLAIN SELECT * FROM staffs WHERE NAME='July';
-- type=ALL possible_key=idx_staffs_nameAgePos key=null
EXPLAIN SELECT * FROM staffs WHERE NAME!='July';
-- type=ALL possible_key=idx_staffs_nameAgePos key=null
EXPLAIN SELECT * FROM staffs WHERE NAME<>'July';
解释下: 3条sql都是能查出来结果的
OR前后存在非索引的列,索引失效
统一使用utf8mb4
数据库和表的字符集统一使用utf8mb4
统一使用utf8mb4(5.5.3版本以上支持)兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。
关联查询优化
查询优化方向
1、物理查询优化是通过索引和表连接方式等技术来进行优化,这里重点需要掌握索引的使用。
2、逻辑查询优化就是通过SQL等价变换提升查询效率,直白一点就是说,换一种查询写法执行效率可能更高。
1、关联查询问题引入
此时没建立索引时。type=all,都是全表扫描,rows=20+20
为了避免全表扫描我们需要针对card字段建立索引,
提出问题:class表和book都有card字段,应该往哪个表上建??
2、在被驱动表上建立索引
先从驱动表取一条数据,然后根据被驱动表找匹配关系,跟嵌套循环是一样的
情况一:book表的card字段建立
SQL_NO_CACHE 每次都要从mysql查询最新的,不从缓存取得
# 添加索引优化 ALTER TABLE `book` ADD INDEX idx_book_card ( `card`); # 查看索引 SHOW INDEX FROM book; # 下面开始explain分析 EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;
此时,在left join情况下,class是驱动表,book表示被驱动表。
我们只在被驱动表上的“连接字段上”建立索引,则被驱动表type会使用ref,驱动表还是all
要注意的问题:
连接字段的俩名字可以不一样,但是类型一定要一样,不然会类型转换导致索引失效
情况二:class表的card字段建立
#删除book表的索引 DROP INDEX idx_book_card ON book; # 添加索引优化 ALTER TABLE `class` ADD INDEX idx_class_card (card); # 查看索引 SHOW INDEX FROM class; # 下面开始explain分析 EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;
此时,在left join情况下,左表class是驱动表,book表示被驱动表。我们在驱动表上创建了索引。则rows=20+20,无效果
结论
A left join B,A驱动表,B是被驱动表,我们在被驱动表上做索引;
A right join B,B驱动表,A是被驱动表,我们在被驱动表上做索引;
3、内连接
对于内连接怎么区分驱动表与被驱动表,有下面3条结论
1.查询优化器是有权利决定哪个是驱动表,哪个是被驱动表
2.如果表的连接条件中,只能有一个字段有索引,则有索引的字段所在的表会作为被驱动表。
3.在2个表的连接条件都存在索引、或者都不存在索引的情况下,小表会作为驱动表,小表驱动大表。
小表驱动大表深层次理解:
1.更准确的说应该是小的结果集驱动大结果集,要把where条件考虑上
2.小表作为驱动表的本质目的就是 减少外层循环的数据数量
3.小的度量单位指的是 表行数*每行大小
4、JOIN语句的原理
从Explain查询的结果来看,上面一定是驱动表,下面一定是被驱动表
对于左外连接来说,前面这个不一定是驱动表。。。
索引嵌套循环连接(我们追求的)
减少了内层表(被驱动表)数据的匹配次数,要求被驱动表上必须有连接字段的索引
索引嵌套循环连接对应示意图如下:
如果被驱动表加索引,效率是非常高的
但如果索引不是主键索引,所以还得进行一次回表查询。相比之下,如果被驱动表的索引是主键索引,效率会更高。
==========
块嵌套循环连接 ,引入join buffer缓冲区
针对join连接的字段没有索引
mysql的改进方案
需要注意的2个点
1.这里缓存的不只是关联表的列,select后面的列也会缓存起来。2.在一个有N个join关联的sql中会分配(N-1)个join buffer。所以查询的时候尽量减少不必要的字段,可以让join buffer中可以存放更多的列。
那么就可以从以下几个方面入手进行优化:
1、为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)
2、增大join buffer size的大小(一次缓存的数据越多,那么内层包的扫表次数就越少)
3、减少驱动表不必要的字段查询(字段越少,join buffer所缓存的数据就越多)
5、使用关联查询的建议
1、保证被驱动表的join字段已经被索引
2、永远是选择小表驱动大表(小表作为驱动表)
3、inner join 时,mysql会自己把小结果集的表选为驱动表。
4、子查询尽量不要放在被驱动表,有可能使用不到索引。
5、能够直接多表关联的尽量直接关联,不用子查询。
一般mysql优化,或者编写高效的sql语句,注意2点
1 符合最佳最前缀原则,
2 永远用小表驱动大表。
子查询优化
1、子查询的问题
子查询可以通过一个SQL语句实现比较复杂的查询。但是,子查询的执行效率不高。原因:
①执行子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表,然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表。这样会消耗过多的CPU和O资源,产生大量的慢查询。
②子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响。
③对于返回结果集比较大的子查询,其对查询性能的影响也就越大。
2、子查询怎么去优化?
解决方案:直接不用子查询
在MySQL中,可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表,其速度比子查询要快,如果查询中使用索引的话,性能就会更好。
3、 子查询优化举例
查询班长的信息
排序分组优化
1、order by 关键字优化
在age和birth建立一个复合索引如下:
MySQL支持二种方式的排序,FileSort和Index
Index效率高.它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
ORDER BY子句,尽量复用已建好索引的方式排序,避免使用FileSort方式排序
index(a,b,c)
只要建过索引就是排过序,尽量复用,不要另起炉灶,以下是好使的2种情况
where a = 'xx' order by b ,corder by a,b,c
#不出现 filesort
EXPLAIN SELECT * FROM tblA ORDER BY age,birth;
#出现 filesort
EXPLAIN SELECT * FROM tblA ORDER BY age ASC,birth DESC;
#出现 filesort
EXPLAIN SELECT * FROM tblA ORDER BY birth;
#不出现 filesort
EXPLAIN SELECT * FROM tblA WHERE age = 20 ORDER BY birth;
#不出现 filesort
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age,birth;
#不出现 filesort
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age;
#出现 filesort
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth;
#出现 filesort
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth,age;
ORDER BY满足两情况,会使用Index方式排序
1、ORDER BY 语句使用索引最左前列
2、使用Where子句与Order BY子句条件列组合满足索引最左前列
2、group by 关键字优化
分组必排序 一句话,先有order by后有group by
1、group by实质是先排序后进行分组,遵照索引建的最佳左前缀
2、where高于having,能写在where限定的条件就不要去having限定了。
3、group by 使用索引的原则几乎跟order by一致 ,唯一区别是groupby 即使没有过滤条件用到索引,也可以直接使用索引
MySQL千万级数据分页的优化
1、limit底层分页原理
limit(0,10)跳过0条,要查10条。
在SELECT 语句中使用LIMIT子句来约束结果集中的行数。LIMIT子句接受一个或两个参数。两个参数的值必须为零或正整数。俩个参数LIMIT子句语法
offest:参数要返回的第一行的偏移量。第一行的偏移量为0,而不是1
count:指定要返回的最大行数
SELECT column1,column2,... FROM TABLE LIMIT OFFSET,COUNT;
不加索引,600万数据量下,老师测试的是21s,主要因为limit后面的偏移量太大导致的。
/*偏移量为4950000,取30*/
SELECT SQL_NO_CACHE
a.empno,
a.ename,
a.job,
a.sal,
b.deptno,
b.dname
FROM
emp a
LEFT JOIN dept b
ON a.deptno = b.deptno
ORDER BY a.id DESC
LIMIT 4950000, 30;
limit底层分页原理
其实是因为limit后面的偏移量太大导致的。比如 limit 4950000,30。
这个等同于数据库要扫描出 4950030条数据,然后再丢弃前面的 49500000条数据返回剩下30条数据给用户,这种取法明显不合理。
生产危险隐患:当偏移量非常大的时候,它会导致MySQL扫描大量不需要的行然后再抛弃掉