当Mysql缓慢时，这几招可解燃眉之急

第一步定位问题源：
常见的以查询Mysql性能问题的方法
1.大部分的性能问题都是查询过慢的问题，可以查询慢sql日志。
通过慢查询日志定位那些执行效率较低的SQL语句，用–log–slow-queries[= file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
long_query_time 可以在 my.cnf配置文件里设置
也可以直接设置 set global long_query_time=4

2.使用show processlist命令查看当前MySQL在进行的线程
包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
show processlist; 只列出前100条
show full processlist;列出全部线程情况

3.排查机器内存，磁盘，IO，网络是否资源不够

（1） linux 系统命令
（2）free 命令 显示系统内存使用情况
（3）vmstat 命令 看到整个机器的CPU,内存,IO的使用情况
（4）top 命令 实时显示各个进程的资源占用情况

二、性能优化
1.使用索引的优点
（1）没有合适的索引匹配查询导致查询慢，
（2）使用索引的优点、大大减少了服务器需要扫描的数量，
（3）索引可以帮助服务器避免排序和临时表
（4）索引可以帮助将随机I/O变为顺序I/O
2.索引的类型：
（1）普通索引，普通单列索引
（2）唯一索引，具有唯一约束，可以有多个null
（3）组合索引，多个建组成的索引，使用需要查询符合最左匹配原则。
（4）全文索引，和B-tree索引不会冲突，全文索引适用于MATCH AGAINST 操作，而不是普通的WHERE条件操作。
（5）哈希索引

3.查询语句是否语句的执行计划是否使用了合适的索引。

（1）使用explain关键字查看语句的执行计划
使用方法：explain 【查询语句】

key 列表示用到了那个索引。
rows列表示预计要扫描多少行。
一般来说扫描的行数越少速度越快。

（2）强制使用索引
use index 关键字 建议mysql使用索引，可以指定多个索引让mysql选择。

SELECT * FROM sys_user use index(id, idx_phone) WHERE phone = '123456';

ignore index 关键字 禁止mysql使用指定的索引，防止查询语句使用错误的索引，可指定多个索引。

SELECT * FROM sys_user ignore index(id, idx_email) WHERE phone = '123456';

force index 关键字 强制mysql使用指定索引进行查询。

SELECT * FROM sys_user force index(id, idx_email) WHERE phone = '123456';

4.索引如何挑选

（1）一般常用/高流量的查询条件上建索引。

（2）按照数据的区分度建索引。同样的在where条件中，区分度越高的列上建索引查询得更快。建立组合索引时，多个列将区分度高的列排前面。

（3）按照索引的功能建索引，如果字段有唯一性约束则建唯一索引。如果有多列是常用的筛选条件，那就用组合索引

5.索引失效的情况
（1）使用 or 关键字
（2）复合索引未用左列字段;
（3）like以%开头;
（4）需要类型转换;
（5）where中索引列有运算;
（6）where中索引列使用了函数;
（7）如果mysql觉得全表扫描更快时（数据少）;

6.什么时候不建议用索引：
（1）唯一性差;
（2）频繁更新的字段不用（更新索引消耗）;
（3）where中不用的字段;
（4）索引使用<>时，效果一般;

7.查询优化
优化数据访问
简单衡量查询开销的三个指标：
*响应时间
*扫描行数
*返回行数

（1）确认应用程序是否检索的太多不必要的数据，减少应用程序访问行和访问列。

（2）检查多表join in 时，是否返回了全部的列，只返回需要的列即可。

（3）使用合适索引减少扫描的行数。

（4）使用覆盖索引，避免回表查询。

（5）order by 排序操作和 group by 分组操作尽量使用索引，避免生成临时表和文件排序。

8.DML性能问题*
（1）避免死锁产生。
（2）分析语句在事务中将含有锁竞争的语句放到后面去执行。
（3）能用普通索引的就不用唯一索引，唯一索引用不上 change buffer 的优化机制。

9.数据库参数的优化：
（1）尽量把表字段设置为 not null，避免影响mysql的优化
（2）innodb_buffer_pool_size：调整InnoDB存储引擎的缓存池大小。

三、紧急情况下kill掉异常线程，保证mysql整体可用。

在使用mysql时，可能会发生死锁、应用端没有释放连接、大量的慢查询，占用大量数据库连接，导致mysql连接数不够，无法对外提供服务，处于不可用的状态，这时要kill掉那些异常连接保证数据库整体可用。

KILL命令的语法格式如下：
KILL [CONNECTION | QUERY] thread_id

批量kill 慢查询的方式
select concat(‘kill ‘, id,’;’) from information_schema.processlist where time > 3;

代码007普通

打赏 收藏 海报 链接