乐趣区

MySQL高级(索引优化+慢查询定位)

一、先谈谈事务
1. ACID 特性
1.1 原子性:事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;1.2 一致性:执行事务前后,数据库从一个一致性状态转换到另一个一致性状态。1.3 隔离性:并发访问数据库时,一个用户的事物不被其他事务所干扰,各并发事务之间数据库是独立的;1.4 持久性:一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库 发生故障也不应该对其有任何影响。
2. 事务隔离级别
2.1 READ_UNCOMMITTED(未提交读): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读 2.2 READ_COMMITTED(提交读): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生 2.3 REPEATABLE_READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。2.4 SERIALIZABLE(串行): 最高的隔离级别,完全服从 ACID 的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。
Mysql 默认采用的 REPEATABLE_READ 隔离级别
二、了解索引
1. 什么是索引
索引是一种帮助 MySQL 高效获取数据的数据结构
2. 优势和劣势
优势:

提高数据检索的效率,降低数据库的 IO 成本
降低数据排序的成本,降低了 CPU 的消耗

劣势:

索引列要占用空间
降低了更新表的速度(INSERT、UPDATE)
建立优秀索引的时间成本

3. 哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段,外键关系建立索引
where 条件里用不到的字段不创建索引
单键 / 组合索引的选择问题,在高并发倾向创建组合索引
查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
查询中统计或分组字段

4. 哪些情况下不需要创建索引

表记录太少
频繁更新的字段不适合创建索引,因为每次更新不单单是更新了记录还会更新索引,加重了 IO 负担
数据重复且分布平均的表字段(若某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果,即过滤性不好的字段)

三、EXPLAIN【重点】
使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句,从而知道 MYSQL 是如何处理你的 SQL 语句的,分析你的查询语句或是表结构的性能瓶颈
EXPLAIN 查询出来的字段
a、id
①id 相同,执行顺序由上至下
②id 不同,id 值越大优先级越高,越先被执行
b、select_type
查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询

c、table
显示这行的数据是关于哪张表的。

d、type(最好到最差的顺序)

system:表只有一行记录(等于系统表),这是 const 的特例,平时不会出现,这个可以忽略
const:表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据,所以很快。如将主键置于 where 列表中,MySQL 就能将该查询转换为一个常量
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常用于主键或唯一索引扫描
ref:非唯一性索引扫描,返回匹配某个单独值的所有行,可能会找到多个符合符合条件的行
range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引。一般是你的 where 语句中出现 between、<、>、in 等的查询,这种范围扫描索引扫描比全表扫描要好
index:index 与 all 的区别为 index 类型只遍历索引树,也就是说,虽然 all 和 index 都是读全表,但 index 是从索引中读取的,而 all 是从硬盘中读取的
all:遍历全表

备注:一般来说,得保证查询至少达到 range 级别,最好能达到 ref
e、possible_keys
可能应用到这张表的索引

f、key
实际使用的索引

g、key_len
索引使用的字节数,在不损失精确性的情况下,长度越短越好

h、ref
显示索引的哪一列被使用了

i、rows
大致估算出找到所需记录所需要读取的行数

j、Extra

Using filesort:说明 mysql 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”
Using temporary:使用了临时表保存中间结果,MySQL 在对查询结果排序时使用临时表,常见于排序 order by 和分组查询 group by
USING index:表示相应的 select 操作中使用了覆盖索引,避免访问了表的数据行

什么情况下索引会失效

全值匹配
最佳左前缀法则
不在索引列上做任何操作(计算、函数、(手动或自动)类型转换),会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引
MySQL 在使用不等于(!= 或 <>) 的时候无法使用索引会导致全表扫描
is null,is not null 也无法使用索引
like 以通配符开头(“%abc..‘)MySQL 索引失效会变成全表扫描的操作
字符串不加单引号索引失效(自动类型转换)
or 左边有索引、右边没索引也会失效

order by 关键字优化

尽量使用 index 方式排序,避免使用 filesort 方式。
order by 满足两种情况会使用 index 排序:①、order by 语句使用索引最左前列,②、使用 where 子句与 order by 子句条件列组合满足索引最左前列
双路排序:MySQL4.1 之前,两次扫描磁盘
单路排序:从磁盘读取查询需要的所有列,按照 order by 列在 buffer 对它们进行排序,然后扫描排序后的列进行输出,效率更高一点,但是它会使用更多的空间,因为它把每一行都保存在内存中了

优化策略: 增大 sort_buffer_size 参数的设置、增大 max_length_for_sort_data 参数的设置
group by 关键字优化
实质是先排序后进行分组,遵照索引键的最佳左前缀,当无法使用索引列时,增大 sort_buffer_size+max_length_for_sort_data 参数的设置
三、慢查询【重点】
1. 慢查询日志是什么
MySQL 的慢查询日志是 MySQL 提供的一种日志记录,它用来记录在 MySQL 中响应时间超过阀值的语句,具体指运行时间超过 long_query_time 值的 SQL,则会被记录到慢查询日志中。

具体指运行时间超过 long_query_time 值的 SQL,则会被记录到慢查询日志中。long_query_time 的默认值为 10,意思是运行 10 秒以上的语句。

由他来查看哪些 SQL 超出了我们的最大忍耐时间值,比如一条 sql 执行超过 5 秒钟,我们就算慢 SQL,希望能收集超过 5 秒的 sql,结合之前 explain 进行全面分析。

2. 怎么用
默认情况下,MySQL 数据库没有开启慢查询日志,需要我们手动来设置这个参数。(当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。)
— 查看开启情况
SHOW VARIABLES LIKE ‘%slow_query_log%’;

— 开启(只对当前数据库生效,如果要永久生效,就必须修改配置文件 my.cnf)
set global slow_query_log=1;
3. Show Profile【重点】
3.1. 是什么
mysql 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于 SQL 的调优的测量,相比 explain,show profile 展示的数据更加详尽。

3.2. 怎么用
— 查看是否开启
show variables like ‘profiling’;

— 开启功能,默认是关闭,使用前需要开启
set profiling=1;

— 查看结果
show profiles;

— 诊断 SQL
show profile cpu,block io for query n;
— 还可以通过 SELECT * FROM information_schema.profiling WHERE query_id = n ORDER BY seq; 获取

退出移动版