MySQL进阶(查询优化、写优化)

共饮一杯无
340 阅读10分钟

本文正在参加「技术专题19期 漫谈数据库技术」活动

查询优化

mysql数据库的性能状态监控点非常多，其中很多量都是不能忽视的必须监控的量，且90%以上的内容 可以在连接上mysql后执行show status 或是 show veriables的输出值 获得，需要注意的是以上的命令获得的状态值实际上是累计值，所以如果 要计算时段内的变化 量还需要稍加处理。

show status

常见的状态信息：

--查看MySQL本次启动后的运行时间(单位：秒)
show status like 'uptime';
--查看select语句的执行数
show [global] status like 'com_select';
--查看insert语句的执行数
show [global] status like 'com_insert';
--查看update语句的执行数
show [global] status like 'com_update';
--查看delete语句的执行数
show [global] status like 'com_delete';
--查看连接到MySQL(不管是否连接成功)的连接数
show status like 'connections';
--查看线程缓存内的线程的数量。
show status like 'threads_cached';
--查看当前打开的连接的数量。
show status like 'threads_connected';
--查看当前打开的连接的数量。
show status like 'threads_connected';
--查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。
show status like 'threads_created';
--查看激活的(非睡眠状态)线程数。
show status like 'threads_running';
--查看立即获得的表的锁的次数。
show status like 'table_locks_immediate';
--查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
show status like 'table_locks_waited';
--查看创建时间超过slow_launch_time秒的线程数。
show status like 'slow_launch_threads';
--查看查询时间超过long_query_time秒的查询的个数。
show status like 'slow_queries';

--innodb buffer 命中率:这里innodb buffer 所指的是innodb_buffer_pool，也就是用来缓存innodb类型表和索引的内在空间。类似key buffer，同样可以根据mysql提供的相应的状态信息计算其命中率：
--innodb_buffer_read_hits=(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100%;
-- 命中率过低，说明myisam类型表的读写存在问题。
show status like 'innodb_buffer_pool_read%';

-- key buffer 命中率:key buffer 命中率代表了myisam类型表的索引cache命中率，命中率的大小直接影响myisam类型表的读写性能。key buffer 命中率实际上包括读命中率和写命中率两种，mysql中并没有直接给出这两个命中率的值，但是可以通过如下方式计算：
-- key_buffer_read_hits=(1-key_reads/key_read_requests) * 100%
-- key_buffer_write_hits=(1-key_writes/key-write-requests)*100%
-- 命中率过低，说明innodb类型表的读写存在问题。
-- 获取所需要状态的变量值：
show status like 'key%';



-- binlog cache：若打开binlog日志功能，则需要考虑binlog cache问题。binlog不是一有数据就写到binlog中，而是先写入到binlog cache中，再写入到binlog中。
-- Binlog_cache_disk_use为binlog使用硬盘使用量， Binlog_cache_use  为binlog已使用的量。若 --- Binlog_cache_disk_use大于0，则说明binlog_cache不够用。
show status like 'binlog_cache%';

-- 锁状态：mysql的锁有表锁和行锁，myisam最小锁为表锁，innodb最小锁为行锁，可以通过以下命令获取锁定次数、锁定造成其他线程等待次数，以及锁定等待时间信息。
show status like '%lock%';
-- 如当Table_locks_waited与Table_locks_immediate的比值较大，则说明我们的表锁造成的阻塞比较严重，可能需要调整Query语句，或者更改存储引擎，亦或者需要调整业务逻辑。当然，具体改善方式必须根据实际场景来判断。而Innodb_row_lock_waits较大，则说明Innodb的行锁也比较严重，且影响了其他线程的正常处理。同样需要查找出原因并解决。造成Innodb行锁严重的原因可能是Query语句所利用的索引不够合理(Innodb行锁是基于索引来锁定的)，造成间隙锁过大。也可能是系统本身处理能力有限，则需要从其他方面来考虑解决。

show profile

SQL查询很慢，怎么通过profile信息排查。

  1. 执行SET SESSION profiling = 1;打开profile
  2. 执行查询后，根据show profiles找到要剖分的查询id
  3. 执行show PROFILE FOR QUERY xxx;查看剖分信息
  4. 执行SET SESSION profiling = 0;关闭profile

explain执行计划

一条查询 SQL 语句为什么会执行很慢？执行的时候走了什么索引？多表查询的时候 SQL 大体执行流程是怎么样的？这时需要通过Explain 执行计划来分析SQL语句。 总的来说，通过 Explain 从而知道 MySQL 是如何处理你的 SQL 语句的，分析查询语句或是表结构的性能瓶颈。那么通过 Explain 执行计划可以知道：

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可能被使用
  • 哪些索引实际被使用
  • 表之间的引用
  • 每张表估计有多少行会被执行

Explain 的语法非常简单，只需要在查询语句前面加一个 EXPLAIN 关键字即可。 执行计划执行结果列包含的信息： id，select_type，table，type，possible_keys，key，key_len，ref，rows，Extra

id

id: select查询的序列号，包含一组数字，表示查询中执行select字句或操作的顺序。 id有三种值：

  • id相同，执行顺序由上至下。
  • id不同，如果是子查询，id的序号会递增，id越大优先级越高，先被执行。
  • id相同不同，如1，1，2，id相同可以认为是一组，从上往下顺序执行。在所有组中，id越大，优先级越高，越先被执行。

select_type

查询的类型，主要是用于区别普通查询，联合查询，子查询等的复杂查询。 Select_type的值有：simple, primary, subQuery, derived, union, union result.

  • Simple：简单的select查询，查询中不包含子查询或者union；
  • Primary：主查询，查询中若包含任何子查询，最外层查询则被标记为主查询；
  • subQuery：子查询，在select或者where列表中包含子查询。
  • Derived：临时表，在from列表中包含的子查询被标记为derived(衍生)；
  • Union：第二个select出现在union之后，则被标记为union；
  • Union result：从union表中获取结构的select；

table

显示这一行的数据是关于哪张表的；

type

访问类型排列； 显示查询使用了哪一种类型，从最好到最差依次是： System > const > eq_ref > ref > range > index > ALL 一般来说，得保证查询到range级别，最好能到ref级别。

  • System：表中只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个可以忽略不计。
  • Const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。例如将主键置于where列表中，mysql将该查询转为一个常量。
  • Eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
  • Ref：非唯一性索引扫描，返回匹配某个单独值得所有行。
  • Range：只检索给定范围的行，使用一个索引来选择行。Key列显示使用了哪个索引，一般就是在你的where语句中出现了between, <, >, in等的查询。这种范围扫描比全表扫描，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
  • Index：full index scan全索引扫描，index与all的区别为：index类型只遍历索引树，这通常比all快，因为索引文件通常比数据文件小。也就是说虽然index和all都是读全表，但index是从索引中读的，而all是从硬盘中读的。
  • All：全表扫描，是最慢的类型。

possible_keys

显示可能应用在这张表中的索引，一个或者多个。 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

Key

实际使用的索引。 如果为null，则没有使用索引。查询中若出现了覆盖索引，则该索引仅出现在key列表中。

Key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 在不损失精确性的情况下，长度越短越好。 Key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

Ref

显示索引的具体一列被使用了，也可能是一个const。 哪些列或者常量被用于查找索引列上的值。

Rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。数值越低越好。

Extra

不适合在其他列显示，但十分重要的额外信息。 Extra有如下几个值：

  • Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。Mysql中无法利用索引完成的排序操作称为“文件排序”。
  • Using temporary：使用了临时表保存中间结果。常见于排序order by和分组查询group by。

Using filesort和Using temporary都是不太好的结果，会影响性能。

  • Using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错！
    • 如果同时出现了using where，表明索引被用来执行索引键值的查找；
    • 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
  • Using where：表示使用了where过滤。
  • Using join buffer：使用了连接缓存。
  • impossible where：表示where条件总是false。例如：where name=”zs” and name=”ls”;
  • Select tables optimized away：没有group by子句下，基于索引优化Max/Min操作。
  • Distinct：优化distinct，在找到第一匹配的元组后即停止找到同样值得动作。

写优化

批量写入

需要为数据组准备-一个离线数据库，这个数据库的数据量很大。有哪些措施可以更快的写入这些数据?

  1. 使用ALTER TABLE t_ table_ name DISABLE KEYS;禁用索引，完成工作后用 ALTER TABLE t_ table_ name ENABLE KEYS;启用索引
  2. 使用SET UNIQUE_ CHECKS = 0; 禁用唯一性检查 ,完成工作后用SET UNIQUE CHECKS = 1;恢复唯一

性检查。

  1. 使用SET foreign_ key_ checks = 0;禁用外键检查，完成工作后SET foreign key_ checks = 1;恢复外键检查。
  2. 使用insert values(...).,(...).(...).. .批量插入数据。
  3. 使用LOAD DATA INFILE 'data_ file path' INTO TABLE table name;批量导入数据
  4. 对InnoDB表使用SET autocommit = 0;禁用自动事务，完成工作后用SET autocommit = 1;

快速删除

Joe需要删除数据分析库中orders表的数据，orders按时间分区，因为是分析部门离线使用，不需要考虑并发，下列哪些操作可以更快的删除这些数据?

  1. 使用truncate from orders;
  2. 可以执行ALTER TABLE orders DROP PARTITION partition_ name; 删除指定分区
  3. drop table orders删除后重建。

本文内容到此结束了，

如有收获欢迎点赞👍收藏💖关注✔️，您的鼓励是我最大的动力。

如有错误❌疑问💬欢迎各位大佬指出。

主页：共饮一杯无的博客汇总👨‍💻

保持热爱，奔赴下一场山海。🏃🏃🏃

avatar
文章
阅读
粉丝