MySQL之优化

SQL优化
应用优化
MySQL Server优化
- - - 1.MySQL服务端参数的查看
    - 2.重要参数的优化设置

MySQL优化主要从三个方面考虑：SQL优化、应用优化、MySQL Server优化。本文案例如不特别声明，则使用的版本为MySQL8.0.34

SQL优化

SQL优化是数据库性能优化最基础也是最重要的一个问题，而索引是数据库优化中最常用也是最重要的手段之一。

1.优化SQL的一般步骤

通过show status like "Com_%"了解各种SQL语句的执行频率，Com_XXX表示每个XXX语句的执行次数。以下是几个重要的参数，通过如下参数可以了解当前应用是以插入更新为主还是以查询为主。

Com_select：执行select操作的次数，每一次查询累加1
Com_update
Com_insert 
Com_delete

定位执行效率较低的SQL语句：
1. 通过慢查询日志定位那些执行效率较低的SQL语句。
使用explain分析低效SQL的执行计划
确定问题后采取相应的优化措施：例如是否需要建立索引

2.索引问题

对相关列使用索引是提高select查询性能的最佳途径，而使用索引需要查询条件中使用索引关键字
如果是多列索引，那么查询条件只有使用了多列关键字最左边的前缀时，才可以使用索引，否则不能使用索引。

CREATE TABLE t_stu (   uid int unsigned NOT NULL AUTO_INCREMENT,   name varchar(16) DEFAULT NULL,   age tinyint unsigned DEFAULT NULL,  sex enum('male','female') DEFAULT 'male', PRIMARY KEY (uid)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# 创建联合索引，包含name、age两个字段
create index idx_name_age on t_stu(name,age);

insert into t_stu(name,age) values('张三',23),('李四',22),('刘丽丽',28),('王妍',32),('王五',16),('刘升',27);

mysql> select * from t_stu;
+-----+-----------+------+--------+
| uid | name      | age  | sex    |
+-----+-----------+------+--------+
|   1 | 张三      |   23 | male   |
|   2 | 李四      |   22 | male   |
|   3 | 刘丽丽    |   28 | female |
|   4 | 王妍      |   32 | male   |
|   5 | 王五      |   16 | male   |
|   6 | 刘升      |   27 | male   |
|   7 | 小凯      |   59 | male   |
+-----+-----------+------+--------+

# 使用了联合索引
mysql> explain select * from t_stu where name = '王五';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_stu | NULL       | ref  | idx_name_age  | idx_name_age | 67      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# 使用不到索引，如果需要使用索引则考虑单独建立一个单例索引age
mysql> explain select * from t_stu where age = 27;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_stu | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

对于使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能被使用

# 使用聚合索引
mysql> explain select * from t_stu where name like '王%';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_stu | NULL       | range | idx_name_age  | idx_name_age | 67      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

# 使用不到索引
mysql> explain select * from t_stu where name like '%丽';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_stu | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果MySQL估计使用索引比全表扫描慢，则不使用索引。

# 不使用索引
mysql> explain select * from t_stu where age > 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_stu | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

使用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及到的索引都不会被使用到

# 不使用索引
mysql> explain select * from t_stu where name = '王五' or sex = 'female';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_stu | NULL       | ALL  | idx_name_age  | NULL | NULL    | NULL |    7 |    57.14 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果列类型是字符串类型，则避免发生类型转换，否则不能使用索引

# 不能使用索引
mysql> explain select * from t_stu where name = 23;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_stu | NULL       | ALL  | idx_name_age  | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

# 使用联合索引
mysql> explain select * from t_stu where name = '23';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_stu | NULL       | ref  | idx_name_age  | idx_name_age | 67      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

3.常用SQL的优化

insert语句的优化：

同时从同一客户插入很多行，则尽量使用多个值表的insert语句

# 使用1而不是2

# 1
insert into t_stu(name,age) values('张三',23),('李四',22),('刘丽丽',28)...

# 2
insert into t_stu(name,age) values('张三',23);
insert into t_stu(name,age) values('李四',22);
insert into t_stu(name,age) values('刘丽丽',28);
...

优化group by语句：默认情况下，MySQL对所有group by 后面的字段进行排序。如果使用分组查询，但避免排序结果的损耗，则可以指定order by null
优化嵌套查询：使用连接查询替代嵌套查询，之所以连接查询更有效率一些是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作

应用优化

在应用访问数据库的那一层上添加连接池。连接池就是预先创建好连接对象，当应用请求MySQL server时不用再进行TCP连接和释放连接了，直接从连接池中获取连接。
增加cache层：比如说使用redis或者memcache，将经常使用的数据缓存起来，这样应用查询数据时先查询cache层是否有数据。
能用一条SQL解决问题就不用两条
使用负载均衡：利用某种均衡算法，将固定的负载数量分布到不同的服务器上，来减轻单台服务器的负载。
1. 利用MySQL的主从复制：主服务器用来执行更新操作，多台服务器用来执行查询操作
2. 使用分布式数据库系统：例如mycat
表的字段尽量不使用自增长变量，在高并发情况下该字段的增长可能对效率有比较大的影响，推荐通过应用来实现字段的自增长
充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确插入值，这会减少MySQL需要做的语法分析从而提高插入速度。

MySQL Server优化

对于MySQL server优化，主要指的是修改MySQL服务器启动时加载的配置文件的配置项

1.MySQL服务端参数的查看

查看MySQL服务器的静态配置信息：在MySQL client中执行show variables like '%xx%';
查看MySQL服务器动态运行状态信息：show status like '%xxx%'';
MySQL服务器的参数很多，可以使用如下命令查看每个参数的解释以及参数的默认值
1. 查看所有参数：mysqld --verbose --help
2. 查看指定参数的值：mysqld --verbose --help | grep 参数名称 -A 2，-A 2表示显示包含指定字符串的行和此行后面的2行。

2.重要参数的优化设置

查询缓存：MySQL8中不支持查询缓存，这里不过多讲解
innodb_buffer_pool_size：这个参数定义了InnoDB存储引擎的表数据和索引数据的最大内存缓冲区大小，这个值设置的越高，访问表中数据需要的磁盘IO就越少。

root@hecs-205584:~# mysqld --verbose --help | grep 'innodb-buffer-pool-size=' -A 3;
  --innodb-buffer-pool-size=# 
                      The size of the memory buffer InnoDB uses to cache data
                      and indexes of its tables.
  --innodb-change-buffer-max-size=#

innodb-flush-log-at-trx-commit：这个参数用来控制缓冲区的数据写入到日志文件以及日志文件数据刷新到磁盘操作时机。其值有三种：
1. 0：日志缓冲每秒一次写入到日志文件，并且对日志文件做磁盘刷新的操作，但是在一个事务提交时不做任何操作
2. 1：在每个事务提交时，日志缓冲写入到日志文件，并且对日志文件做向磁盘刷新的操作
3. 2：在每个事务提交时，日志缓冲写入到日志文件，但是不对日志文件做向磁盘刷新的操作，对日志文件每秒向磁盘做一次刷新操作。

root@hecs-205584:~# mysqld --verbose --help | grep 'innodb-flush-log-at-trx-commit' -A 3;
  --innodb-flush-log-at-trx-commit[=#] 
Set to 0 (write and flush once per second), 1 (write and
flush at each commit), or 2 (write at commit, flush once
per second).
--

# 默认值为1
innodb-flush-log-at-trx-commit  1

innodb-lock-wait-timeout：MySQL可以自动检测行锁导致的死锁并进行相应的处理。这个参数的值表示等待指定的时间后回滚。

--innodb-lock-wait-timeout=# 
Timeout in seconds an InnoDB transaction may wait for a
lock before being rolled back. Values above 100000000
disable the timeout.

# 默认值50秒
innodb-lock-wait-timeout    50

innodb-log-buffer-size：将日志写入到log-buffer，再从log-buffer写入到磁盘。

--innodb-log-buffer-size=# 
The size of the buffer which InnoDB uses to write log to
the log files on disk.

# 默认值大概16M
innodb-log-buffer-size  16777216

max-connections：设置客户端的最大连接数量

--max-connections=# The number of simultaneous clients allowed

max-connections  151

wait-time：设置客户端的连接超时时间

--wait-timeout=#    The number of seconds the server waits for activity on a
connection before closing it

# 默认值2
wait-timeout    28800

thread-cache-size：线程池的初始线程数量

--thread-cache-size=# 
How many threads we should keep in a cache for reuse
thread-cache-size   9

参考：<<深入浅出MySQL>>

posted on 2023-10-04 02:32 scrutiny-span 阅读(5) 评论(0) 编辑收藏举报

刷新页面返回顶部

导航