1.索引优化

为了提高SQL语句的执行效率,在调优中我们一般会对SQL语句进行分析,添加索引. 正确的添加索引有助于我们提高SQL的执行效率.

mysql架构分为4层 :

连接层
服务层
引擎层
存储层

顾名思义,连接层主要用来提供对客户端的连接服务.
服务层则是主要大多核心的服务功能.如SQL接口,并完成缓存的查询,所有跨数据库引擎的功能也在这一层实现,如function(函数),procedure(存储过程)等.服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化如:确定表的查询顺序,是否用到索引,索引的类型等(可使用explain 关键字查看的).最后生成相应的执行操作.
如果是select语句,mysql还会查询内部的缓存.如果设置的缓冲足够大,在读取大数据时,能提高查询性能
引擎层,存储引擎真正负责了数据的存储和提取.不同的存储引擎有不同的功能.如myiasm,innodb
存储层,主要将数据存储在文件系统之上,并完成与存储引擎的交互.

服务层中的Optimizer查询优化器则负责着对SQL查询语句的优化.

首先说一下查询优化器在where查询中的作用:

如果存在一个复合索引,任何最左边的索引前缀能够被优化器使用.
如果列无法满足最左前缀原则,索引将不会生效.
如果一个like语句以通配符开始,索引将失效.
如果一个where 子句中存在范围查询 != ,<>,is null , is not null等,索引将失效

复合索引是最使用,也最常用的索引
复合索引用在查询中: 要确保排序顺序与索引顺序完全相同或相反.例如:
create index idx_xxx on tableName(c1 desc,c2 , c3 desc)
则我们的排序顺序应该为:
order by c1 desc , c2 , c3 desc 或者 order by c1 , c2 desc , c3

索引分析

下面我们将以表的类型对索引进行分析:

单表
1.带头大哥不能断(满足最佳左前缀原则)
2.中间不能出现 range类型的索引
两表
1.永远用小结果集驱动大结果集,尽量减少外循环的次数
2.优先优化嵌套循环的内循环(被驱动表)
3.确保被join表使用的是索引列连接字段
三表
1.同两表情况
2.当无法保证被驱动表字段被索引且内存资源充足的情况下,尽可能增大JoinBuffer的内存(确保查数据完全被存放到内存中);

索引失效

为了确保我们所创建的索引能够正常使用,我们需要了解索引在哪些情况下会失效;
无效的索引不仅无法提高SQL执行的效率,还会加剧我们的内存占用.

1.对于索引,特别是复合索引,我们最好使用到他全部的索引列,索引本身就是问我们的需要而制定
2.遵循最佳左前缀原则,即使不能全部使用,我们也要考虑到经常被使用的列,
拿index(c1 , c2 ,c3)来说,只有匹配c1 c2 c3 | c1 c2 | c1 时才会生效,我们应该把我们有把握使用到的列放到索引最左边.
3.不在索引列上做任何操作(函数,计算,类型转换等),会导致索引失效进而转变为全表扫描.
4.尽量使用覆盖索引(只访问索引的查询,即(select 索引列 from table where 索引列)),减少select *
5.mysql在使用不等于(!=或<>)会导致索引失效
6.is null , is not null 也会导致索引失效
7.like 使用通配符开头,如like '%123',也会使索引失效
8.字符串不加单引号也会导致索引失效(mysql内部帮我们做了类型转换)
9.少用 or , or也会导致索引失效

下面举几个例子来说明:
首先我们创建索引 index(c1,c2,c3)

explain select * from t1 where c1 = 'a1' and c4 = 'a' order by c2,c3

这个索引不会失效,因为where子句的 c1 和 order by 子句的c2 , c3完全匹配复合索引,不同的是 c1 用来查找 ,c2 c3用来排序(同 1)

explain select * from t1 where c1 = 'a1' and c4 = 'a4' order by c3 , c2

这个索引失效,他的顺序为 c1 c3 c2,而我们索引的顺序为 c1 c2 c3,并且c3 c2 出现在order by子句中,会出现using filesort

explain select * from t1 where c1 ='a1' and c2 = 'a2' order by c2 , c3
根据第二个例子的逻辑,我们该SQL 的字段顺序为c1 c2 c2 c3.与我们索引列的字段不一致,所以索引会失效.
但是实际上这行SQL索引不会失效,这是不是有点矛盾.其实是Optimizer查询优化器帮我们做了优化,where 子句中出现的 c2 引用为常量,后面再次出现时被定义为常量,索引查询列变为了 c1 c2 c3.
explain select * from t1 where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c3,c2 经过前面的解惑,我们已经能确定该SQL索引失效了.c1 c2 c3 c2 ,因为 c3 并未在前边where 子句中引用常量(未出现)

关于索引的一般性建议

对于单值索引,尽量选择针对当前query过滤性更好的字段
在选择组合索引时,应当把过滤性最好的字段放在索引列中,位置越靠前越好.
尽可能的使用覆盖索引

2.查询截取分析

SQL调优的流程:
1.查询优化
2.慢查询日志
3.批量数据脚本
4.Show Profile 5.全局查询日志(测试环境)

查询优化

原则:永远小表驱动大表

1.order by关键字优化

order by 子句尽量使用index,避免使用filesort

使用index排序需要满足两种情况:
1.遵循最佳左前缀原则
2.where 子句与 order by 子句组合满足最佳左前缀原则.

group by 与 order by 基本是一致的, group by 实质是先排序后分组 , 遵循最佳左前缀原则的原则.

慢日志查询

首先了解一下,什么是慢日志查询. 慢日志查询是mysql提供的一种日志记录,会把响应时间超过阈值的语句记录到日志中,具体指运行时间超过log_query_time值的SQL,long_query_time 默认值为10s.

默认情况下,mysql是关闭慢查询日志的,需要我们手动去开启,

查看慢日志开启状态
show variables like 'slow_query_log';

开启慢日志记录
set slow_query_log = 1;

查看慢日志记录存放的位置
show variables like 'slow_query_log_file'

show profile

show profile 是mysql提供的能够分析当前会话中语句执行的资源损耗和生命周期.

默认情况下参数处于关闭状态,并保留最近15次的运行结果.

查看当前mysql版本是否支持
show variables like 'profiling'

开启参数 set profiling = 1;

然后执行我们的SQL语句

之后 show Profiles,可以看到我们执行的语句被记录其中,表中有三个字段:

query_id,duration,query

query_id:主键,对应着我们的执行语句
duration:执行总耗时.
query:我们的执行语句

如果我们试图解析这个语句我们可以通过

show profile [参数] for query [主键]

可以看到该SQL的执行顺序: 1 2 3 4 5 6 7.....以及各项耗时,总耗时;
explain过后,我们能通过show profile更精准的找到影响语句执行的出处
参数解读:

全局查询日志

是测试环境中的调优方案,永远不要用在生产环境中.

sql调优的大致流程:
1.慢查询的开启与捕获
2.explain + 慢SQL 分析
3.show profile 查看SQL在mysql服务器里面的执行细节和生命周期情况
4.SQL服务器的参数调优

MySQL 索引优化 以及 查询截取分析