image.png

一、先排查业务层面

绝大多情况是由于sql问题导致，因此需要优先从sql排查。
先登录mysql，通过mysql> show processlist;显示哪些线程正在运行。
如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。 show processlist;只列出前100条，如果想全列出请使用show full processlist;
如果是linux 系统安装的mysql，通过 top 指令查看 内存，CPU占用情况高的排行中是否有mysql进程。

具体效果如下：

image2.png image.png
image.png

id        #ID标识，要kill一个语句的时候很有用
user      #当前连接用户
host     #显示这个连接从哪个ip的哪个端口上发出
db       #数据库名
command  #连接状态，一般是休眠（sleep），查询（query），连接（connect）
time     #连接持续时间，单位是秒
state    #显示当前sql语句的状态
info     # 执行的sql语句

writiing to net :当对上面sql分析后，使用了索引，依然很慢，都是查询数据太数据库返回网络回写慢导致，

查看`show VARIABLES like '%max_allowed_packet%'; 只有4M

windows:
修改 my.ini 文件， 增加
max_allowed_packet = 20M

主要是需要观察 state 列，mysql列出的状态主要有以下几种：
Copying to tmp table on disk
　由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
　正在创建临时表以存放部分查询结果。
deleting from main table
　服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables
　服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables
　正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed
　发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
　被其他查询锁住了。
Sending data
　正在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sorting for group
　正在为GROUP BY做排序。
Sorting for order
　正在为ORDER BY做排序。
Opening tables
　这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
　正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
　获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
　修复指令正在排序以创建索引。
Repair with keycache
　修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update
　正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping
　正在等待客户端发送新请求.
System lock
　正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
Upgrading lock
　INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating
　正在搜索匹配的记录，并且修改它们。
User Lock
　正在等待GET_LOCK()。
通过下面语可以缩小范围
mysql-> select * from information_schema.PROCESSLIST where info is not null;
ID|USER|HOST|DB|COMMAND|TIME|STATE

二、 数据库层面

排除了业务层面的问题，现在看看数据库层面的问题
mysql-> show variables like '%pool%';
innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

Variable_name	Value
innodb_additional_mem_pool_size	8388608
innodb_additional_mem_pool_size	8388608
innodb_buffer_pool_dump_at_shutdown	OFF
innodb_buffer_pool_dump_now	OFF
innodb_buffer_pool_filename	ib_buffer_pool
innodb_buffer_pool_instances	8
innodb_buffer_pool_load_abort	OFF
innodb_buffer_pool_load_at_startup	OFF
innodb_buffer_pool_load_now	OFF
innodb_buffer_pool_size	536870912

三、MySQL调优之innodb_buffer_pool_size大小设置

相关查看命令

sql> show global status like 'Innodb_buffer_pool_pages_data';

sql> show global status like 'Innodb_page_size';```


```> show global variables like 'innodb_buffer_pool_size';

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+
1 row in set (0.00 sec)


MariaDB [(none)]> show global status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 6082  |
+-------------------------------+-------+
1 row in set (0.00 sec)


MariaDB [(none)]> show global status like 'Innodb_buffer_pool_pages_total';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_total | 16383 |
+--------------------------------+-------+
1 row in set (0.00 sec)


MariaDB [(none)]> show global status like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)


MariaDB [(none)]> 

官方对这个几个参数的解释：
Innodb_buffer_pool_pages_data
The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and
clean pages.

Innodb_buffer_pool_pages_total
The total size of the InnoDB buffer pool, in pages.

Innodb_page_size
InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be
easily converted to bytes

调优参考计算方法：
val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75%
val < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (102410241024)

设置命令：set global innodb_buffer_pool_size = 2097152; //缓冲池字节大小，单位kb，如果不设置，默认为128M

设置要根据自己的实际情况来设置，如果设置的值不在合理的范围内，并不是设置越大越好，可能设置的数值太大体现不出优化效果，反而造成系统的swap空间被占用，导致操作系统变慢，降低sql查询性能。

修改配置文件的调整方法，修改my.cnf配置：

innodb_buffer_pool_size = 2147483648 #设置2G

innodb_buffer_pool_size = 2G #设置2G

innodb_buffer_pool_size = 500M #设置500M

MySQL5.7及以后版本，改参数时动态的，修改后，无需重启MySQL，但是低版本，静态的，修改后，需要重启MySQL

四、索引的使用

养成搭建索引的习惯，可以很高效的提升查询效率，缓解数据库压力。

-- 创建索引语句
alter table x_test add index(name);

重点提一下多列索引，在我们系统开发中，关联了很多表，查询过滤的条件往往不止一个，单列索引，只能过滤掉一部门，需要使用多列索引进一步提升效率，mysql多列索引

多列索引也叫联合索引又叫复合索引
也就是把多个字段按顺序连起来创建一个索引 ( 最多16列 )
理解这句：按顺序的连起来 从左到右的
左前缀:
对于联合索引的使用上需要注意, where 自己的第一个条件的列名必须是组合索引列的最左边的那个。

为什么要创建多列索引？
相对在每个列上单独建索引 多列索引占用空间更小 检索也就越快

比如表里有3个字段 name，age，from QQ浏览器截图20200206085402.png

-- 创建多列索引
alter table x_test add index(name,age,from);

创建的索引就相当于是 : mashen1991Dalian
当查询时从左到右去比对查询
示例：

// 完全使用索引
WHERE name = 'name' AND age = 'age' AND from = 'from';

// 完全使用索引 ( mysql会自动排序 )
WHERE age = 'age' AND name = 'name' AND from = 'from';

// 使用索引 ( 因为是遵循前缀索引的原则 所以下面两个查询都使用了索引 )
WHERE  name = 'name';
WHERE  name = 'name' AND age = 'age';

// 没有使用索引 ( 因为缺少age字段 无法现实从左到有匹配 )
WHERE  age = 'age';
WHERE  age = 'age' AND from = 'from';

// 部分索引 ( name最左使用了索引 from未使用索引 因为中间断开了age )
WHERE  name = 'name' AND from = 'from';

注意：

QQ浏览器截图20200206085712.png

文件排序 fileSort

CREATE TABLE `t6` (
  `id` int(11) NOT NULL DEFAULT '0',
  `a` char(10) NOT NULL DEFAULT '',
  `b` char(10) NOT NULL DEFAULT '',
  `c` char(10) NOT NULL DEFAULT '',
  `d` char(10) NOT NULL DEFAULT '',
  `e` char(10) NOT NULL DEFAULT '',
  `f` char(10) NOT NULL DEFAULT '',
  `g` char(10) NOT NULL DEFAULT '',
  `h` char(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `indexs` (`a`,`b`,`c`,`d`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into t6 values(1,'a','b','c','d','e','f','g','h');
insert into t6 values(2,'a1','b1','c1','d1','e1','f1','g1','h1');
insert into t6 values(3,'a2','b2','c2','d2','e2','f2','g2','h2');
insert into t6 values(4,'a3','b3','c3','d3','e3','f3','g3','h3');

#走索引indexs 用了a,b,c,d
explain select * from t6 where a ='a' and  b ='b' and  c='c' and  d ='d';

#走索引indexs 用了a,b,c,d
explain select * from t6 where a ='a' and  b ='b' group by   c,d

#走索引indexs 用了a,b,c,d 先d 后 c 用了文件排序//; Using temporary;用了临时表
 Using index condition; Using where; Using temporary; Using filesort
explain select * from t6 where a ='a' and  b ='b' group by   d,c

#走索引indexs 用了a,b,c,d  Using index condition; Using where
explain select * from t6 where a ='a' and  b ='b' and c='c' group by   d,c

#走索引indexs 用了a,b,c,d  Using index condition; Using where
#这里的 c已经是常量'c'了不管 c,d 还是 d,c 都不会走文件排序了
explain select * from t6 where a ='a' and  b ='b' and c='c' group by  c,d

#走索引indexs 用了a,b,c  Using index condition; 没用到Using where 都是索引能直接确定值
explain select * from t6 where a ='a' and  b ='b' and c='c' group by c

#走索引 type = range 用了范围 仅仅用了a索引
select * from t6 where a ='a1' and  b >'b' and c='c1' and d = 'd1' 

InnoDB的索引 与myIsam

innoDB的次索引指向对主键的使用
myIsam的次索引和主索引都指向物理行

QQ浏览器截图20200206134613.png

innodb的id索引就是聚簇索引 好处是 根据主键索引速度非常快

QQ浏览器截图20200206135152.png

对于innodb 没有主键，系统会找Unique key作为聚簇索引

QQ浏览器截图20200206140427.png

总结高性能索引策略：

对于innodb而言 因为节点下有数据文件，因此节点的分裂将会比较慢，对于innodb的主键尽量用整型 而且是递增的整型如果是无规律的数据将会产生叶的分裂，代价比较大。