mysql优化思路

最新推荐文章于 2023-02-07 16:52:28 发布

柔于似水

最新推荐文章于 2023-02-07 16:52:28 发布

阅读量844

点赞数

分类专栏： MySQL 文章标签：慢查询 mysql 优化高并发

本文链接：https://blog.csdn.net/q936889811/article/details/86480903

版权

MySQL 专栏收录该内容

27 篇文章 1 订阅

订阅专栏

文章目录

一、 mysql高并发优化思路

1、索引匹配原则：

全值匹配、前缀匹配、列前缀匹配、匹配范围值、只访问索引的查询、精确匹配某一列并范围匹配某列

2、关于sql的优化思路：

1、使用explain或者desc 查看sql的执行计划
	
	了解输出项的含义，是否走索引或者索引合理。
	
2、当sql要查询的数据超过总记录的30%，sql将不走索引
3、根据where的条件添加合理的组合索引，根据where条件中的字段的使用频率从高到低的顺序从左往右。例如：uion_index(a,b,c) ,a>b>c
4、充分利用覆盖索引，减少回表带来的性能消耗
5、小表驱动大表 join
6、group by、order by可能会造成不能正确的走索引。只有当索引列的顺序和order by 字句的顺序完全一致，并且遵循同样的升序活降序规则，mysql才会使用索引対结果做排序 。  https://blog.csdn.net/z69183787/article/details/54575571
7、虚拟列，可以让表达式使用索引
8、mysql8.X函数索引使用虚拟列演变而来，实现表达式使用索引函数索引使用虚拟列演变而来，实现表达式使用索引
9、强制走索引force index
10、不要问加索引会影响性能，只要利大于弊，大胆加索引
11、临时表   https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html
12、mysql8.x跳跃索引
13、避免使用select * ，只列举出需要的字段
14、in、exists使用
			
	1. in
	select * from A where filed_A in (select filed_B from B);

	执行顺序：先执行B表的子查询，再把子查询的结果集加以缓存并作为A表的过滤条件，执行A表查询时候遍历所有的子查询的结果。

	```
	#include <iostream>
	#include<vector>
	using namespace std;

	int A[] = {A};
	int B[] = {B};

	int ARRA[] = {};
	vector<int> vec;
	int count = 0;
	int main ()
	{
	   for(int i = 0; i<sizeof(A)/sizeof(int); i++){
		
		/*获取A数组的一个，到B数组中遍历匹配*/
		   for(int j = 0; j < sizeof(B)/sizeof(int); j++){
			   if(A[i] == B[j]){
				   vec.push_back(A[i]);
			   }
			   count++;
		   }
	   }
		cout<< "执行次数":<<count<<endl;
	   return 0;
	}
	```
	就是说：B子查询的结果集为N放入缓存，则A查询则循环遍历N次，返回结果集。假设A表的结果集为100，B表结果集越大循环次数越多。
	2. exists
	select * from A where  exists(select 1 from B where filed_A=filed_B);


	执行顺序：先执行A表查询，再去判断B表中是否有满足filed_A和filed_B相等的记录，如果有则返回true，反之，返回false。
	exists不缓存结果集，查询一次A表就需要执行一次B表查询。
	```
	#include <iostream>
	#include<vector>
	using namespace std;

	int A[] = {A};
	int ARRA[] = {};
	vector<int> vec;
	int count = 0;
	int main ()
	{
	   for(int i = 0; i<sizeof(A)/sizeof(int); i++){
		
		  if("slect * from B whereA[i]"){
				vec.push_back(A[i]);
		  }
		  count++;
	   }
	   cout<< "执行次数:"<<count<<endl;
	   return 0;
	}
	```
	因为A表的结果集为N，去和B表匹配时需要执行一次B查询。假设A表结果集为固定值100，B表的不管多或者少，都是不影响循环的次数。
	
	遵循小表驱动大表的原则：in适用于B表大比A表小的场景，exists适用于B表比A表大的场景。

3、关于mysql的优化思路：

1、qps
2、com_select、com_insert、com_update、com_delete、com_commit、com_rollback，判断是读密集型还是写密集型
3、慢查询
4、分析binlog的delete、insert、update的表的多个维度占比，分析热点表
5、genera 分析sql
6、my.cnf 参数调整
7、读写分离，尽量将查询实时性要求不高的读走读库，并结合1、2项查看优化效果
8、拆库，根据业务模块相对独立的拆出来，单独部署数据，分摊数据库压力。
9、大表分表，按时间(年月天)或者哈希等；在分表的时候，一定要控制好表的数量，否则会带来性能问题。
10、业务的热数据能走缓存的一定要走缓存
11、更换性能更强的服务器（核数、内存、ssd++）

4、中间件读写分离：

mmm、orchestrator

5、优化读写分离之后的从库延迟问题

提升从库的执行效率：
1、增大从库参数innodb_buffer_pool_size的值，可以缓存更多数据，减少由于转换导致的IO压力。
2、增大参数innodb_log_file_size、innodb_log_files_in_group的值，减少buffer pool的刷盘IO,提升写入性能。
3、修改参数innodb_flush_method为O_DIRECT,提升写入性能。
4、关掉从库Binlog日志或者关掉参数log_slave_updates.
5、修改参数innodb_flush_log_at_tr_commit为0或2.
6、如果binlog没有关掉（gtid开启，需要binlog日志打开），修改sync_binlog参数为0或者一个很大的值，减少磁盘IO压力。
7、如果binlog_format为row模式并且被修改表没有主键，则需要加上主键
8、如果binlog_format为row模式，则可以在从库总删除一些不必要的索引(同步完成之后再加上)。
9、了解清楚写库上操作内容，适当地在从库中预热一i西安数据，可以减少在复制时等待的时间。
10、如果binlog_format 为statement模式，或者存在DDL复制，则可以将tmpdir参数改到内存中，比如/dev/shm.
11、修改参数master_info_repository、relay_log_info_repository为table，减少直接IO导致的磁盘压力。
12、升级8.x
13、升级硬件

6、根据不同业务需求使用不同的数据库

7、备份：

全备+延迟+binlogserver

二、 mysql性能低解决思路：

一、mysql层面

1、查看慢查询日志(slow log)。
mysql性能低，大多是sql引起的，需要对慢sql进行优化
可以使用 pt-query-digest分析慢查询sql。

分析sql主要从：

利用explain 查看索引及扫描情况

利用set profiling=1,执行时间和等待时间两方面分析的sql的问题

[BEGIN] 
mysql> show variables like 'profi%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT COUNT(*) AS allnum
    -> , SUM(CASE 
    -> WHEN createtime < date_add(CURDATE(), INTERVAL 1 DAY)
    -> AND createtime > CURDATE()
    -> THEN 1
    -> ELSE 0
    -> END) AS todaynum
    -> , SUM(CASE 
    -> WHEN unappropriated = '0'
    -> AND `invalid_count` < 2
    -> THEN 1
    -> ELSE 0
    -> END) AS unappropriatednum
    -> , SUM(CASE 
    -> WHEN unappropriated = '1'
    -> AND currentservicerid = 'ISME9754_T2D_1'
    -> THEN 1
    -> ELSE 0
    -> END) AS currentservicernum
    -> , SUM(CASE 
    -> WHEN `finished` = 1 THEN 1
    -> ELSE 0
    -> END) AS finishednum
    -> , SUM(CASE 
    -> WHEN `invalid_count` >= 2 THEN 1
    -> ELSE 0
    -> END) AS invalidnum
    -> , SUM(CASE 
    -> WHEN `servicerid` = ''ISME9754_T2D_1' THEN 1
    -> ELSE 0
    -> END) AS serviceridnum
    -> FROM crm_user
    -> WHERE `siteid` = '9739'
    -> AND FIND_IN_SET(110268, gid);
+---------+----------+-------------------+--------------------+-------------+------------+---------------+
| allnum  | todaynum | unappropriatednum | currentservicernum | finishednum | invalidnum | serviceridnum |
+---------+----------+-------------------+--------------------+-------------+------------+---------------+
| 1434835 |     2487 |           1434800 |                  0 |           0 |          0 |          6616 |
+---------+----------+-------------------+--------------------+-------------+------------+---------------+
1 row in set (17.19 sec)

mysql> show profiles;
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration    | Query                                                                                                                                                                                                                                                                                                        |
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 17.18347225 | SELECT COUNT(*) AS allnum SUM(CASE WHEN createtime < date_add(CURDATE(), INTERVAL 1 DAY)

+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)

mysql> show profile for query 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000211 |
| checking permissions |  0.000047 |
| Opening tables       |  0.000047 |
| init                 |  0.000098 |
| System lock          |  0.000042 |
| optimizing           |  0.000045 |
| statistics           |  0.000181 |
| preparing            |  0.000070 |
| executing            |  0.000042 |
| Sending data         | 17.182189 |
| end                  |  0.000121 |
| query end            |  0.000045 |
| closing tables       |  0.000046 |
| freeing items        |  0.000072 |
| logging slow query   |  0.000043 |
| Opening tables       |  0.000045 |
| System lock          |  0.000090 |
| cleaning up          |  0.000040 |
+----------------------+-----------+
18 rows in set, 1 warning (0.00 sec)

2、并发、锁、临时表等指标

tps、qps、并发连接数(Threads_connected)、并发活跃线程数(Threads_running)、临时表(tmp_disk_tables)、锁(locks_waited,innodb_row_rock*)等指标。

可以使用innotop工具可以清晰的查看相关情况

抓包分析：
tcpdump -s 65535 -x -nn -q -tttt -i any -c 10000 port 3306 >mysql.tcp.txt
pt-query-digest --type=tcpdump mysql.tcp.txt >mysql.tcp_repot.txt

利用mysqladmin查看
mysqladmin extended-status -r -i 1 | grep -E ‘Questions|Com_select|Com_update’

3、show processlist 查看当前线程状态：

如果出现以下状态可能不出现问题：

copy_tmp_table、creating_sort_index、Sorting_resoult、creating_tmp_table、长时间的sending data等

4、查看innodb_buffer_pool page的使用情况
主要是innodb_pages_free和innodb wait_free两个。

使用 pt-mysql-summary查看相关信息

5、多次执行show engine innodb status ，观察页面的变化情况：

Pages made young 1330334, not young 147073
0.00 youngs/s, 0.00 non-youngs/s

二、linux系统层面
1、使用top命令
内容输出：
link

查看load average表示当前的服务的平均负载，分为 1分钟、5分钟、15分钟。
us%是用户使用cpu占比，如果us%太高，极有可能索引使用不当。
sy%是系统内核使用的cpu占比，如果sy%太高，有可能是mysql的连接数和锁等信息。

wa%是I/O使用cpu的占比，如果wa%太高，有可能mysql使用了硬盘临时表，或者大量刷盘等操作，也有可能硬盘太慢、硬盘故障，可以使用iostat 、pt-diskstats等工具来采集信息。
pt-diskstats 磁盘性能监控利器，功能和iostat类似

# pt-diskstats

  #ts device    rd_s rd_avkb rd_mb_s rd_mrg rd_cnc   rd_rt    wr_s wr_avkb wr_mb_s wr_mrg wr_cnc   wr_rt busy in_prg    io_s  qtime stime
  0.6 vdc        1.6     4.0     0.0     0%    0.0     1.0    48.0     2.9     0.1     0%    0.0     0.8   4%      0    49.6    0.1   0.7
  0.6 vdc1       1.6     4.0     0.0     0%    0.0     1.0    48.0     2.9     0.1     0%    0.0     0.8   4%      0    49.6    0.1   0.7

  1.0 vdc        2.0     4.0     0.0     0%    0.0     1.0    37.0     5.2     0.2     0%    0.6    15.1  37%      2    39.0    5.1   9.4
  1.0 vdc1       2.0     4.0     0.0     0%    0.0     1.0    36.0     5.3     0.2     0%    0.6    15.6  37%      2    38.0    5.2   9.6

  1.0 vda        0.0     0.0     0.0     0%    0.0     0.0     2.0    44.0     0.1    91%    0.0     0.1   0%      0     2.0    0.0   0.1
  1.0 vda1       0.0     0.0     0.0     0%    0.0     0.0     2.0    44.0     0.1    91%    0.0     0.1   0%      0     2.0    0.0   0.1
  1.0 vdc        4.0     7.0     0.0     0%    0.0     1.0    68.0     7.8     0.5     0%    0.3     3.9  16%      0    72.0    1.2   2.2
  1.0 vdc1       4.0     7.0     0.0     0%    0.0     1.0    64.0     8.3     0.5     0%    0.3     4.2  16%      0    68.0    1.3   2.3

参数：

device:设备名称
rd_s:表示每秒读取次数
wr_s：表示每秒写入次数
rd_avkb：平均每次读请求的字节，单位KB/s,也是单次读取数据量。
rd_mb_s：该设备上的读取带宽，单位MB/s。rd_s*rd_avkb/1024=rd_mb_s。
rd_mrg:被合并的读取请求数占总的读取请求的百分比。越高越好
rd_cnc：读取请求并发。
rd_rt:读取请求平均响应时间。
wr_s、wr_avkb、wr_mb_s、wr_mrg、wr_cnc、wr_rt和读取请求类似。
busy：磁盘繁忙度。busy列，它与iostat中的util列相同,指向利用率
in_prg：
io_s：IOPS。rd_s+wr_s=ios_s
qtime:IO请求排队时间
stime：物理设备实际物理读写所耗费的时间,注意，服务时间（分别在pt-diskstats和iostat输出中的stime字段和svctm字段）在Linux上不可靠。如果您阅读了iostat 手册，您会看到它已被弃用。

查看某个指标：

# pt-diskstats --group-by sample --devices-regex sd[a] --columns-regex io_s
  #ts device    io_s
  0.5 {3}        0.0
  1.5 {3}        0.0
  2.5 {3}        0.0
  3.5 {3}        0.0
  4.5 {3}        0.0
  5.5 {3}        0.0
  6.5 {3}        0.0
  7.5 {3}        0.0
  8.5 {3}        0.0
  9.5 {3}        0.0

2、需要关注各个逻辑cpu之前的负载是否均衡(可能是不均衡导致性能问题)，可以使用mpstat命令进行详细观察。
mpstat -P ALL 5

3、建议服务器为mysql专用。

4、是否使用了swap

5、内存使用率

free -m

6、是否发生内存泄漏
内存泄漏观察方法buff/cache和used对比。如果发生了内存泄漏解决方案：
1.重启mysql服务
2.将mysql升级到最新的小版本

7、通过vmstat来观察每秒的进程、内存、swap、I/O、cpu等详细情况。

8、iostat -x I/O使用情况