MySQL-优化

2021-07-06  本文已影响0人  Zeppelin421

SQL优化

查看SQL执行频率

show status 命令可以查看服务器状态信息

show [session|global] status

定位低效率SQL

可以通过两种方式定位执行效率较低的SQL语句：

慢查询日志

通过慢查询日志定位那些执行效率较低的SQL语句，用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。

show processlist

慢查询日志在查询结束之后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题。可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化

explain分析执行计划

explain或者desc命令获取MySQL如何执行select语句的信息，包括select语句执行过程中表如何连接和连接的顺序等。

id
id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序

select_type

type

extra

show profile分析SQL

MySQL从5.0.37版本开始增加对show profile和show profiles语句的支持。
通过have_profiling参数能够看到当前mysql是否支持profile

mysql> select @@have_profiling;

默认profiling是关闭的，可以通过set语句在session级别开启profiling

mysql> select @@profiling;
mysql> set profiling = 1;

查看SQL语句耗时：

mysql> show profiles;
mysql> show profile for query query_id -- 查看到该SQL执行过程中每个线程的状态和消耗的时间


TIP：
Sending Data状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回给客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

Trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A计划而不是B计划

开启trace

mysql> set optimizer_trace="enabled=on", end_markers_in_json=on;
mysql> set optimizer_trace_max_mem_size=1000000;

检查information_schema.optimizer_trace

mysql> select * from information_schema.optimizer_trace\G;

索引失效情况

优化方法

1、大批量导入数据

使用load命令导入数据的时候，适当的设置可以提高导入的效率

2、Insert
insert into tb_test values(1, "tom");
insert into tb_test values(2, "cat");
insert into tb_test values(3, "jerry");

改为

insert into tb_test values(1, "tom"), (2, "cat"), (3, "jerry");

start transaction;
insert into tb_test values(1, "tom");
insert into tb_test values(2, "cat");
insert into tb_test values(3, "jerry");
commit;
3、order by
create index idx_emp_age_salary on emp(age, salary);

两种排序

select * from emp order by age desc;
select * from emp order by age asc;
select * from emp order by age, salary;
select id, age, salary from emp order by age desc, salary asc;
select id, age, salary from emp order by salary, age;

select id, age, salary from emp order by age, salary;
select id, age, salary from emp order by age desc, salary desc;

tips: 尽量减少额外的排序，通过索引直接返回有序数据。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者降序。否则肯定需要额外的排序。

Filesort的优化

mysql通过比较系统变量 max_length_for_sort_data的大小和Query语句取出的字段总大小来判断使用哪种排序算法，如果max_length_for_sort_data更大，采用第二种算法，否则采用第一种算法

可以适当提高sort_buffer_size 和 max_length_for_sort_data系统变量，来增大排序区的大小

mysql> show variables like 'max_length_for_sort_data';
mysql> show variables like 'sort_buffer_size';
4、group by

group by实际上也会进行排序操作，而且与order by相比，group by只是多了排序之后的分组操作。

如果查询包含group by但是用户想要避免排序结果的消耗，可以执行order by null禁止排序

select age, count(*) from emp group by age;

优化后

select age, count(*) from emp group by age order by null;
5、优化嵌套查询

使用多表联查代替子查询

select * from t_user where id in (select user_id from user_role);

优化后

select * from t_user u, user_role ur where u.id = ur.user_id;
6、or

使用UNION替换or

select * from t_user where id = 1 or age = 10;

优化后

select * from t_user where id = 1
union
select * from t_user where age = 10;
7、分页

一般分页查询时通过创建覆盖索引能够比较好的提高性能。一个常见又非常头痛的问题是 limit 2000000, 10，此时需要MySQL排序前2000010记录，仅返回2000000 - 2000010的记录，查询代价非常大

select * from t_user u, (select id from t_user order by id limit 2000000, 10) a where u.id = a.id;

select * from t_user where id > 2000000 order by id limit 10;

SQL提示

SQL提示是优化数据库的一个重要手段，简单说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

USE INDEX

在查询语句中表名的后面添加use index来提供希望MySQL去参考的索引列表，这样就可以让MySQL不再考虑其他可用的索引。

select * from emp use index(name) where name = 'zs';
IGNORE INDEX

如果只是单纯的想忽略一个或多个索引，可以使用ignore index作为hint

select * from emp ignore index(name) where name = 'zs';
FORCE INDEX

强制MySQL使用一个特定的索引，可在查询中使用force index作为hint

select * from emp force index(name) where name = 'zs';

TIPS: use index 和 force index 的区别在于 use index 只是让MySQL去参考，最终不一定会采用；而 force index 是一定会走索引。

数据库优化

查询缓存优化

开启MySQL的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。

操作流程
查询缓存配置

mysql>show variables like 'have_query_cache';

mysql>show variables like 'query_cache_type';

mysql>show variables like 'query_cache_size';

mysql>show status like 'Qcache%';

各变量含义
开启查询缓存

query_cache_type的取值：

在 /usr/my.cnf配置中增加

#开启mysql的查询缓存
query_cache_type=1

查询缓存SELECT选项

可以在select语句中指定两个与查询缓存相关的选项

select SQL_CACHE id, name from emp;
select SQL_NO_CACHE id, name from emp;

查询缓存失效情况

内存优化

优化原则
MyISAM内存优化

myisam存储引擎使用 key_buffer 缓存索引块，加速myisam索引的读写速度。对于myisam表的数据块，mysql没有特别的缓存机制，完全依赖于操作系统的IO缓存。

key_buffer_size
key_buffer_size 决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率。
*对于一般MyISAM数据库，建议至少将1/4可用内存空间分配给 key_buffer_size
在 /usr/my.cnf 中做如下配置:

key_buffer_size=512M

read_buffer_size
如果需要经常顺序扫描myisam表，可以通过增大read_buffer_size的值来改善性能。

read_rnd_buffer_size
对于需要做排序的mysiam表的查询，如带有order by子句的sql，可以适当增加 read_rnd_buffer_size 的值来改善性能。

TIPS: read_buffer_size 和 read_rnd_buffer_size 都是每个session独占的，默认值设置太大会造成内存浪费。

InnoDB内存优化

innodb用一块内存区域做IO缓存池，该缓存池不仅用来缓存innodb索引块，而且也用来缓存数据块。

innodb_buffer_pool_size
变量决定了innodb存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size的值越大越好，缓存命中率越高，访问innodb表需要的磁盘I/O就越少，性能也就越高。

innodb_buffer_pool_size=512M

innodb_log_buffer_size
变量决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加 innodb_log_buffer_size 的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。

innodb_log_buffer_size=10M

并发参数

max_connections
max_connections控制允许连接到MySQL数据库的最大数量，默认值是151.如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到最大值而失败，这时可以考虑增大 max_connections 的值。
MySQL最大可支持的连接数取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷，CPU的处理速度，期望的相应时间等。在linux平台下，性能好的服务器，支持500~1000个连接不是难事，需要根据服务器性能进行评估设定。

back_log
back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySQL的连接数达到 max_connections 时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即 back_log，如果等待连接的数量超过 back_log，将不被授予连接资源，将会报错。5.6.6版本之前默认值为50，之后的版本默认为50 + ( max_connections / 5 )，但不超过900。
如果需要数据库在较短时间内处理大量连接请求，可以考虑适当增大back_log的值

table_open_cache
table_open_cache参数用来控制所有SQL语句执行线程可打开表缓存的数量，而在执行SQL语句时，每一个SQL执行线程至少要打开1个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定：max_connections * N

thread_cache_size
为了加快连接数据库的速度，MySQL会缓存一定数量的客户服务线程以备重用，通过参数 thread_cache_size 可控制MySQL缓存客户服务线程的数量。

innodb_lock_wait_timeout
用来设置innodb事务等待行锁的时间，默认值是50ms，可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起；对于后台运行的批处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作。

上一篇下一篇

猜你喜欢

热点阅读