都有哪些维度可以进行数据库调优?简言之:
- 索引失效、没有充分利用到索引——索引建立
- 关联查询太多JOIN(设计缺陷或不得已的需求)——SQL优化
- 服务器调优及各个参数设置(缓冲、线程数等)——调整my.cnf。
- 数据过多——分库分表
关于数据库调优的知识点非常分散。不同的DBMS,不同的公司,不同的职位,不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。
虽然SQL查询优化的技术有很多,但是大方向上完全可以分成物理查询优化
和逻辑查询优化
两大块。
- 物理查询优化是通过
索引
和表连接方式
等技术来进行优化,这里重点需要掌握索引的使用。 - 逻辑查询优化就是通过SQL
等价变换
提升查询效率,直白一点就是说,换一种查询写法执行效率可能更高。
索引失效案例(这下面失效都不是绝对,是查询优化器基于对成本的考虑决定的。)
MySQL中提高性能
的一个最有效的方式是对数据表设计合理的索引
。索引提供了高效访问数据的方法,并且加快查询的速度,因此索引对查询的速度有着至关重要的影响。
- 使用索引可以
快速地定位
表中的某条记录,从而提高数据库查询的速度,提高数据库的性能。 - 如果查询时没有使用索引,查询语句就会
扫描表中的所有记录
。在数据量大的情况下,这样查询的速度会很慢。
大多数情况下都(默认)采用B+树
来构建索引。只是空间列类型的索引使用R-树
,并且MEMORY
表还支持hash
索引。
其实,用不用索引,最终都是优化器说了算。优化器是基于什么的优化器?基于cost开销(CostBaseOptimizer)
,它不是基于规则(Rule-BasedOptimizer)
,也不是基于语义
。怎么样开销小就怎么来。另外,SQL语句是否使用索引,跟数据库版本、数据量、数据选择度都有关系。
全值匹配我最爱
系统中经常出现的sql语句如下:
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE -* FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
建立索引之后执行:
mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=45 AND classId=4 AND NAME = 'abcd';
Empty set, 1 warning (0.01 sec)
最佳左前缀规则(最左前缀原则)
在MySQL建立联合索引时会遵守最佳左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。
在student上面创建idx_age_classid_name
的联合索引,注意顺序,age、classid、name
CREATE INDEX idx_age_classid_name ON student (age ,classId,NAME);
举例1:
这下面的SQL就只用到了用了idx_age_classid_name
,但是只用到了name字段(最左前缀原则),先根据age去查,发现第二个条件不是classid,就不会使用了,其实回忆B+树
就行了。
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.name = 'abcd';
结论:MySQL可以为多个字段创建索引,一个索引可以包括16个字段。对于多列索引,过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时,多列(或联合)索引不会被使用。
拓展:Alibaba《Java开发手册》
索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引
如果把*
换成表中的所有字段的话,那么就用上了索引,但是将EXPLAIN的格式换成JSON发现二者的开销是一样的。查询的时候其实相差不大,换成表中的字段的稍微快一点,是索引覆盖,编译器做了某种优化。
EXPLAIN SELECT id , NAME ,age classId
FROM student WHERE NAME ='AkwJNZ';
EXPLAIN SELECT *
FROM student WHERE NAME ='AkwJNZ';
主键插入顺序
对于一个使用InnoDB
存储引擎的表来说,在我们没有显式的创建索引时,表中的数据实际上都是存储在聚簇索引
的叶子节点的。而记录又是存储在数据页中的,数据页和记录又是按照记录主键值从小到大
的顺序进行排序,所以如果我们插入的记录的主键值是依次增大
的话,那我们每插满一个数据页就换到下一个数据页继续插,而如果我们插入的主键值忽大忽小
的话,就比较麻烦了(可能会出现页分裂),假设某个数据页存储的记录已经满了,它存储的主键值在1~100
之间:
如果此时再插入一条主键值为9
的记录,那它插入的位置就如下图:
可这个数据页已经满了,再插进来咋办呢?我们需要把当前页面分裂
成两个页面,把本页中的一些记录
移动到新创建的这个页中。页面分裂和记录移位意味着什么?意味着:性能损耗
!所以如果我们想尽量
避免这样无谓的性能损耗,最好让插入的记录的主键值依次递增,这样就不会发生这样的性能损耗了。
所以我们建议:让主键具有AUTO_INCREMENT
,让存储引擎自己为表生成主键,而不是我们手动插入。
计算、函数、类型转换(自动或手动)导致索引失效
计算导致索引失效
第一个走索引,第二个不走索引,因为用了计算
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
函数导致索引失效
- 这两条SQL那种写法更好
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
- 创建索引
CREATE INDEX idx_name ON student(NAME);
- 结论:第一种好,因为第二种用了函数,查询优化器不知道作用的是哪一个字段,就将name所有的值取出来取到和
'abc'
去比较,使用了函数,索引也就失效了
类型转换导致索引失效
name是varchar类型的,第一个失效,因为第一个不是varchar类型,查询优化器会将它转换成varchar类型,导致索引失效,(其实也相当于使用了函数进行转换)
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123';
范围条件右边的列索引失效
CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
可以看出来key_len是10,也就是说只用到了age和classid的值,而name并没有使用到索引,范围条件右边的列索引失效。(这里的右边是索引的右边)
那么索引idx_age_classid_name还能正常使用吗?
- 不能,范围右边的列不能正常使用。比如:(
>
)(<
)(>=
) - 如果这种查询条件过多,应建立:
也就是在索引中把classid的字段放在索引的最后。
CREATE INDEX idx_age_name_classid ON student(age,NAME,classid);
还是之前那句查询语句,这里和where条件后面的查询的字段的顺序无关,因为查询优化器,会帮我们选择合适的顺序。
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
应用开发中范围查询,例如:金额查询,日期查询往往都是范围查询。应将查询条件放置where语句最后。(创建的联合索引中,务必把范围涉及到的字段写在最后)
不等于(!=或者<>)索引失效
这也很好理解,=
的话就直接直接根据B+树去查找就行了,而!=
(<>
)就需要去查找=
之外的了,就相当于全表扫描了。
先在name字段上创建索引。
CREATE INDEX idx_name ON student(NAME);
然后再查询。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;
发现并没有用上索引。
下面使用的是主键索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.id != 55 ;
is null可以使用索引,is not null无法使用索引
- is null可以触发索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
- is not null 无法使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
结论:最好在设计数据表的时候就将
字段设置为 NOT NULL 约束
,比如你可以将INT类型的字段,默认
值设置为0
。将字符类型的默认值设置
为空字符串
(''
)。
拓展:同理,在查询中使用not like
也无法使用索引,导致全表扫描。
like以通配符%开头索引失效
在使用LIKE关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引就不会起作用。只有“%"不在第一个位置,索引才会起作用。
- 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';
- 未使用上索引(因为你要找的是不确定的,所有无法准确的从B+树中去找,只能全表扫描)
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';
拓展:Alibaba《Java开发手册》
【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
or 前后存在非索引的列,索引失效
在WHERE子句中,如果在OR前的条件列进行了索引,而在OR后的条件列没有进行索引,那么索引会失效。也就是说,OR前后的两个条件中的列都是索引时,查询中才使用索引。
因为OR的含义就是两个只要满足一个即可
,因此只有一个条件列进行了索引是没有意义的,只要有条件列没有进行索引,就会进行全表扫描,因此索引的条件列也会失效。
查询语句使用OR关键字的情况:
- 未创建索引
#未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
- 在age字段上创建索引,发现还是没有用到索引(也就是上面所说到的情况)
CREATE INDEX idx_age ON student(age);
- 在classid上创建索引,这时候用上了索引,因为OR的前后两个字段都加上了索引。
CREATE INDEX idx_cid ON student(classid);
数据库和表的字符集统一使用utf8mb4
统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的字符集
进行比较前需要进行转换
会造成索引失效。
练习及一般性建议
练习:假设:index(a,b,c)
一般性建议:
- 对于单列索引,尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
- 在选择组合索引的时候,尽量选择能够包含当前query中的where子句中更多字段的索引。
- 在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面。
总之,书写SQL语句时,尽量避免造成索引失效的情况。