资讯专栏INFORMATION COLUMN

上云采购季！| 爆款云服务器低至4.6元/月，首单享0.9折

免费试用
首页/文章专栏/MySQL高级（索引优化+慢查询定位）

MySQL高级（索引优化+慢查询定位）

wmui / 1432人阅读

摘要：串行最高的隔离级别，完全服从的隔离级别。但是这将严重影响程序的性能。列显示使用了哪个索引。具体指运行时间超过值的，则会被记录到慢查询日志中。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

一、先谈谈事务 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;获取

云数据库MySQL mysql 查询慢 mysql清空慢查询 mysql查询表数据慢 索引分析和优化

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/48938.html

相关文章

  • SQL-SQL优化-索引

    摘要：在库存系统中，最重要的就是要防止超卖。系列创建高性能的索引索引是存储引擎用于快速找到记录的一种数据结构。对查询性能优化最有效的手段。性能优化梳理前言本文主要针对的是关系型数据数据库。用户可以通过特殊的关键字提示优化器，影响的决策过程。 图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一，一般关系型数据库中包含的常用 Join：内联接、外联接和交叉联接等。如果我们...

    zacklee 评论0 收藏0

  • SQL-SQL优化-索引

    摘要：在库存系统中，最重要的就是要防止超卖。系列创建高性能的索引索引是存储引擎用于快速找到记录的一种数据结构。对查询性能优化最有效的手段。性能优化梳理前言本文主要针对的是关系型数据数据库。用户可以通过特殊的关键字提示优化器，影响的决策过程。 图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一，一般关系型数据库中包含的常用 Join：内联接、外联接和交叉联接等。如果我们...

    kk_miles 评论0 收藏0

  • PHP面试MySQL数据库的面试题

    摘要：自己整理了一篇什么是数据库三级封锁协议的文章，关注公众号琉忆编程库，回复锁，我发给你。以下内容部分来自程序员面试笔试宝典和程序员面试笔试真题解析如需转载请注明出处。 你好，是我琉忆，PHP程序员面试笔试系列图书的作者。 本周（2019.3.4至3.8）的一三五更新的文章如下： 周一：PHP面试MySQL数据库的基础知识周三：PHP面试MySQL数据库的索引周五：PHP面试MySQL数...

    awokezhou 评论0 收藏0

  • PHP面试MySQL数据库的面试题

    摘要：自己整理了一篇什么是数据库三级封锁协议的文章，关注公众号琉忆编程库，回复锁，我发给你。以下内容部分来自程序员面试笔试宝典和程序员面试笔试真题解析如需转载请注明出处。 你好，是我琉忆，PHP程序员面试笔试系列图书的作者。 本周（2019.3.4至3.8）的一三五更新的文章如下： 周一：PHP面试MySQL数据库的基础知识周三：PHP面试MySQL数据库的索引周五：PHP面试MySQL数...

    djfml 评论0 收藏0

  • mysql优化

    摘要：显示处于不可中断的休眠的进程数量。在等待显示被交换到磁盘的数据块的数量。服务器硬件优化物理状态灯自带管理设备远程控制卡设备，开关机硬件监控。 数据库层面问题解决思路 一般应急调优的思路：针对突然的业务办理卡顿，无法进行正常的业务处理！需要立马解决的场景！ 1、show processlist 2、explain select id ,name from stu where name=...

    yvonne 评论0 收藏0

发表评论

登陆后可评论

0条评论

wmui

男|高级讲师
我要私信

TA的文章

阅读更多

云社区相关服务

提问 提问 学习 学习 认证 认证 产品 产品 技术服务 技术服务 售前咨询 售前咨询
最新活动
阅读需要支付1元查看
<