MySQL查询优化和数据库监控

如何确定查询处理的各个阶段所消耗的时间

使用profile

mysql> set profiling=1; -- 启用profile，这是一个session级别的配置
mysql> select count(*) from film; -- 执行任意SQL
mysql> show profiles; -- 查看profile，可以看到query id，duration和具体的sql
mysql> show profile for query 5; -- 查看某个query id的各个阶段的消耗时间
mysql> show profile cpu for query 5; -- 查看CPU信息
-- 以上的查询中我们可以看到都有一个警告信息
mysql> show warnings; -- 查看警告信息,发现警告信息是show profile已经被废弃了，官方建议使用Performance Schema

使用performance_schema

和profile不同的是，该配置是全局的。要使用这个配置需要在performance_schema数据库下执行下面的2条update语句：

1 2	UPDATE `setup_instruments` SET enabled='yes',timed='yes' WHERE name LIKE 'stage%'; UPDATE `setup_consumers` SET enabled='yes' WHERE name LIKE 'events%';

执行完上面的2句执行任意的SQL，查看这些SQL在各个阶段的执行时间可以采用如下的查询：

SELECT a.THREAD_ID,SQL_TEXT,c.EVENT_NAME,(c.TIMER_END - c.TIMER_START)/1000000000 AS 'DURATION(ms)'
FROM events_statements_history_long a
JOIN threads b ON a.THREAD_ID = b.THREAD_ID
JOIN events_stages_history_long c ON c.THREAD_ID = b.THREAD_ID AND c.EVENT_ID BETWEEN a.EVENT_ID AND a.END_EVENT_ID
ORDER BY a.THREAD_ID,c.EVENT_ID;

mysql proformance_schema

如上图，我们可以看到和profile类似的结果。

特定SQL的查询优化

关于大表的更新

大表的数据最好分批处理，例如1000W条数据中删除或者更新100W行记录，我们一次最好只删除或者更新5000行记录，并且为了减少对主从复制的压力，我们可以在每次修改后暂停若干秒，大表的更新和删除可以采用如下的存储过程：
对大表结构的修改是一个更为严重的问题——修改表结构将会锁表，且无法解决主从复制数据库延迟的问题。一个比较好的方法是建立一个新表，将老表中的数据导入到新表中，并且在老表中建立一系列的触发器（把老表中数据的修改同步到新表中），当老表和新表的数据同步后对老表加一个排它锁，然后重新命名新表为老表的名字，最后删除重命名的老表。这种操作只会在重命名的时候加一个短暂的锁，通常对应用不会有影响，并且可以有效降低主从延迟。我们可以使用pt-online-schema-shange工具帮助我们完成上述复杂的操作。

如何优化`NOT IN`和`<>`的查询

NOT IN通常可以转化为LEFT JOIN可以避免对子查询表的多次查询。

1
2
3

SELECT customer_id,first_name,last_name,email FROM customer WHERE customer_id NOT IN (SELECT customer_id FROM payment); -- 查询没有支付的用户记录
-- 我们可以使用LEFT JOIN对以上的查询进行优化,可以避免对payment表进行多次关联查询
SELECT a.customer_id,a.first_name,a.last_name,a.email FROM customer a LEFT JOIN payment b ON a.customer_id = b.customer_id WHERE b.customer_id IS NULL;

分库分表

如果需要分担数据库的读负载，我们可以使用主从复制的方式为数据库增加若干从服务器，通过读写分离的方式就可以将数据库的读负载分担到各个从服务器。但是随着业务的增长可能单一的主服务器就承担不了写的负载了。分库分表主要有以下的几种方式：

MySQL分库分表1
MySQL分库分表2

注意上图中的一个数据库节点并不是一台数据库物理机器，而是多台机器组成的集群，集群中的数据是一致的。

如果上面的2种方式还是不能满足要求就可以放终极大招了：表的水平拆分（又称为数据库分片），通常说的分库分表就是这种方式：

对一个库中的相关表进行水平拆分到不同实例的数据库中。对数据库分片并不容易，并且对数据库分片后还会变得难以维护，不到万不得已不要轻易使用！

MySQL分库分表3

数据库监控

对数据库服务的可用性进行监控。这里需要注意一点：数据库进程或者端口存在并不意味着数据库是可用的。我们必须通过网络连接到数据库并且确定数据库对外是可以提供服务的。
对DB性能进行监控，最常见的2个指标是QPS和TPS。
主从复制进行监控。主从复制状态的监控以及主从复制延迟的监控，定期确定主从复制的数据是否一致。
对服务器资源进行监控。

DB可用性监控

1 2	$ mysqladmin -umonitor_user -p -h ping $ telnet ip db_port

但是最好的方式是通过程序通过网络建立数据库连接。除了监控DB服务可用，还需要确认DB是否可读写。

检查DB的read_only是否为off。
建立一张专门用于监控的表，通过程序定期对表进行读写操作。
连接到DB后进行select @@version进行查询。

最后需要监控DB的连接数，连接数目过大将不会对外提供服务。有很多原因可能导致数据库的连接过大，例如：出现阻塞或者缓存失效。

首先获得最大连接的数量：show variables like 'max_connections';
获取当前DB连接数量：show global status like 'Threads_connected';
当Threads_connected / max_connections > .8的时候可以发出报警。

DB性能监控

如何计算QPS和TPS

需要注意的是这里的Query并不特制SELECT，它包含DB处理的所有请求。TPS为每秒执行的事务数量，指的是INSERT,UPDATE,DELETE。DBMS的性能会随着并发处理请求的数量的增加而下降。通常情况下DB的并发量是固定的，并且远小于同一时间连接到DB的线程的数量。

监控InnoDB的阻塞可以周期性执行以下的SQL：

SELECT b.trx_mysql_thread_id AS '被阻塞线程',
	b.trx_query AS '被阻塞SQL',
	c.trx_mysql_thread_id '阻塞线程',
	c.trx_query AS '阻塞SQL',
	(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started)) AS '阻塞时间'
	FROM information_schema.innodb_lock_waits a
	JOIN information_schema.innodb_trx b ON a.requesting_trx_id = b.trx_id
	JOIN information_schema.innodb_trx c ON a.blocking_trx_id = c.trx_id
	WHERE (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started)) > 60; -- 查询阻塞时间超过60s

主从复制的监控

使用pt-table-checksum工具可以检查主从复制数据一致性