MySQL高级讲座篇之：排查磁盘IO高负载：从索引、存储引擎到I/O调度器的多维分析。

各位观众老爷们，大家好！我是你们的老朋友，BUG终结者，今天咱们来聊聊MySQL里让人头疼的磁盘IO高负载问题。这玩意儿就像高血压，平时不声不响，一发作起来，服务器直接瘫痪，搞得运维小哥们欲哭无泪。

咱们今天就从索引、存储引擎到I/O调度器，来个全方位立体式的诊断，看看这磁盘IO到底闹的是哪出。

第一部分：索引这玩意儿，用好了是神器，用不好是灾难！

索引，说白了就是为了加快查询速度的东西。你想象一下，如果没有索引，MySQL就像在字典里找一个字，只能一页一页翻，累死个人。有了索引，就像有了目录，直接定位到页码，效率那叫一个嗖嗖的。

但是，索引也不是越多越好，这玩意儿就像药，吃多了会中毒。

• 索引过多带来的问题：

  • 占用磁盘空间： 索引也是要占地方的，表里数据越多，索引占的地方也就越大。
  • 降低写入速度： 每次写入数据，MySQL都要更新索引，索引越多，更新就越慢。
  • 选择索引时的开销： MySQL优化器会选择最优索引，索引越多，选择的时间就越长。

• 如何判断索引是否有效？

  • EXPLAIN大法： 这是MySQL自带的利器，可以告诉你MySQL是怎么执行你的SQL语句的。

    EXPLAIN SELECT * FROM users WHERE age > 20 AND city = 'Beijing';

    重点关注以下几个字段：

    • type： 查询类型，ALL表示全表扫描，最慢；index表示索引扫描，比全表扫描好一点；range表示范围扫描，还不错；ref表示使用非唯一索引扫描；eq_ref表示使用唯一索引扫描；const/system表示常量查找，最快。
    • possible_keys： 可能用到的索引。
    • key： 实际用到的索引。
    • key_len： 索引的长度，越长表示使用的索引字段越多。
    • rows： 扫描的行数，越少越好。
    • Extra： 额外信息，如果出现Using filesort或Using temporary，说明需要优化。

  • SHOW INDEX： 查看表上的索引信息。

    SHOW INDEX FROM users;

    关注Cardinality字段，表示索引的基数，也就是索引中不同值的数量。基数越高，索引的选择性越好。如果Cardinality很低，说明索引的区分度不高，可能需要删除。

• 优化索引的几个小技巧：

  • 联合索引的顺序： 将选择性最高的字段放在前面。

    比如，INDEX(city, age)和INDEX(age, city)，如果city的区分度比age高，那么INDEX(city, age)更好。

  • 覆盖索引： 如果查询只需要用到索引中的字段，那么就不需要回表查询，可以大大提高查询效率。

    比如，SELECT age, city FROM users WHERE age > 20 AND city = 'Beijing';，如果有一个INDEX(age, city)的索引，那么就可以使用覆盖索引。

  • 前缀索引： 对于字符串类型的字段，可以只索引字符串的前缀，减少索引的大小。

    比如，INDEX(email(10))，只索引email字段的前10个字符。

  • 删除无用索引： 定期检查并删除长时间未使用的索引。

    可以通过查询information_schema.TABLE_IO_STATS_GLOBAL_INDEX表来查看索引的使用情况。

    SELECT INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
    FROM information_schema.TABLE_IO_STATS_GLOBAL_INDEX
    WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table'
    ORDER BY COUNT_FETCH DESC;

第二部分：存储引擎的选择，直接影响IO性能！

MySQL支持多种存储引擎，比如InnoDB、MyISAM、Memory等。不同的存储引擎，IO性能差异很大。

• InnoDB：

  • 特点： 支持事务、行级锁、MVCC，数据存储在共享表空间或独立表空间中。
  • IO特点： 数据和索引都存储在磁盘上，读写操作都需要进行磁盘IO。InnoDB使用缓冲池来缓存数据和索引，可以减少磁盘IO。
  • 适用场景： 需要事务支持、数据一致性要求高的场景。

• MyISAM：

  • 特点： 不支持事务、表级锁，数据存储在单独的文件中。
  • IO特点： 数据和索引都存储在磁盘上，读写操作都需要进行磁盘IO。MyISAM没有缓冲池，IO性能比InnoDB差。
  • 适用场景： 查询为主、并发量低的场景。

• Memory：

  • 特点： 数据存储在内存中，速度非常快。
  • IO特点： 所有数据都在内存中，没有磁盘IO。
  • 适用场景： 临时表、缓存等场景。

• 如何选择存储引擎？

  特性	InnoDB	MyISAM	Memory
  事务支持	支持	不支持	不支持
  行级锁	支持	不支持	不支持
  外键	支持	不支持	不支持
  空间占用	大	小	极小
  数据恢复	支持	不支持	不支持
  适用场景	高并发、事务型	查询为主、只读型	缓存、临时表

• InnoDB优化：

  • 调整innodb_buffer_pool_size： 这是InnoDB缓冲池的大小，越大越好，建议设置为服务器内存的50%-80%。

    SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

    SET GLOBAL innodb_buffer_pool_size = 8589934592; -- 8GB

  • 调整innodb_log_file_size和innodb_log_files_in_group： 这是InnoDB事务日志的大小和数量，适当增大可以减少磁盘IO。

    SHOW VARIABLES LIKE 'innodb_log_file_size';
    SHOW VARIABLES LIKE 'innodb_log_files_in_group';

    SET GLOBAL innodb_log_file_size = 536870912; -- 512MB
    SET GLOBAL innodb_log_files_in_group = 2;

  • 使用独立表空间： 将每个表的数据和索引存储在单独的文件中，可以提高IO性能。

    SET GLOBAL innodb_file_per_table = ON;

  • 优化fsync策略： InnoDB使用fsync将数据刷新到磁盘，可以调整innodb_flush_method参数来优化fsync策略。

    • O_DIRECT: 绕过操作系统的page cache，直接写入磁盘，适合SSD。
    • fdatasync: 只刷新数据，不刷新元数据，性能较好。
    • normal: 使用操作系统的默认策略。

    SHOW VARIABLES LIKE 'innodb_flush_method';

    SET GLOBAL innodb_flush_method = O_DIRECT;

第三部分：I/O调度器，默默无闻的幕后英雄！

I/O调度器是操作系统内核的一部分，负责管理磁盘IO请求的顺序和优先级。不同的I/O调度器，IO性能差异也很大。

• 常见的I/O调度器：

  • CFQ (Completely Fair Queuing)： 为每个进程分配一个IO队列，保证公平性。适合多用户、多进程的场景。
  • Deadline： 尝试在截止时间内完成IO请求，适合对延迟敏感的应用。
  • NOOP (No Operation)： 最简单的调度器，只进行简单的合并和排序，适合SSD。

• 如何查看当前的I/O调度器？

  cat /sys/block/sda/queue/scheduler

  其中sda是磁盘的设备名，你需要根据实际情况修改。

• 如何选择I/O调度器？

  • HDD： CFQ或Deadline。
  • SSD： NOOP或Deadline。

• 如何修改I/O调度器？

  echo noop > /sys/block/sda/queue/scheduler

  这种方式是临时的，重启后会失效。要永久修改，需要在GRUB配置文件中添加elevator=noop参数。

  vi /etc/default/grub

  找到GRUB_CMDLINE_LINUX一行，添加elevator=noop。

  GRUB_CMDLINE_LINUX="... elevator=noop"

  然后更新GRUB配置：

  update-grub

  重启服务器。

第四部分：一些其他的优化技巧

• SQL优化： 避免使用SELECT *，只查询需要的字段。避免使用OR，可以使用UNION或IN代替。避免在WHERE子句中使用函数。
• 分库分表： 将数据分散到多个数据库或表中，减少单个数据库或表的压力。
• 读写分离： 将读操作和写操作分离到不同的服务器上，提高并发能力。
• 使用缓存： 使用Redis、Memcached等缓存系统，缓存热点数据，减少数据库的访问。
• 硬件升级： 更换更快的CPU、更大的内存、更快的磁盘。

第五部分：案例分析

假设你的MySQL服务器磁盘IO很高，CPU也很高，但是查询速度很慢。

1. 首先，使用top命令查看哪个进程占用了大量的CPU和IO。

   top

   如果发现mysqld进程占用了大量的CPU和IO，那么说明MySQL服务器遇到了性能瓶颈。

2. 使用iostat命令查看磁盘IO的详细信息。

   iostat -x 1

   重点关注以下几个字段：

   • %util：磁盘利用率，越高说明磁盘越繁忙。
   • await：平均IO等待时间，越高说明磁盘IO越慢。
   • r/s：每秒读取的扇区数。
   • w/s：每秒写入的扇区数。

3. 使用SHOW PROCESSLIST命令查看当前MySQL的连接和执行的SQL语句。

   SHOW PROCESSLIST;

   找到执行时间长的SQL语句，使用EXPLAIN命令分析SQL语句的执行计划，看看是否可以使用索引优化。

4. 检查InnoDB缓冲池的命中率。

   SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

   计算命中率：1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)。如果命中率很低，说明InnoDB缓冲池太小，需要增加innodb_buffer_pool_size。

5. 检查慢查询日志。

   MySQL会将执行时间超过long_query_time的SQL语句记录到慢查询日志中。分析慢查询日志，可以找到需要优化的SQL语句。

   SHOW VARIABLES LIKE 'slow_query_log';
   SHOW VARIABLES LIKE 'long_query_time';

   可以使用mysqldumpslow命令分析慢查询日志。

   mysqldumpslow -s t -t 10 /path/to/slow_query_log

总结

磁盘IO高负载是一个复杂的问题，需要从多个方面进行分析和优化。希望今天的讲座能够帮助大家更好地理解MySQL的IO性能，并解决实际问题。

记住，没有银弹！根据你的实际情况，选择合适的优化方案才是王道！

今天的讲座就到这里，谢谢大家！希望大家以后少遇到BUG，多拿奖金！ 下次有机会再见！