提升数据库性能的终极指南：深入MySQL优化技巧

本文链接：https://blog.youkuaiyun.com/qq_40477248/article/details/145517472

#作者：程宏斌

一、解决瓶颈：做索引

查询一些MySQL数据库的性能参数用show status

mysql> SHOW STATUS LIKE 'Connections';    //连接mysql服务器的次数
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 4     |
+---------------+-------+
1 row in set (0.00 sec)

Uptime：mysql服务器的上线时间

mysql> SHOW STATUS LIKE 'uptime';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Uptime        | 14159453 |
+---------------+----------+
1 row in set (0.00 sec)

Com_select:查询操作的次数

mysql> SHOW STATUS LIKE 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 7     |
+---------------+-------+
1 row in set (0.00 sec)

Com_insert：插入操作的次数

mysql> SHOW STATUS LIKE 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 3     |
+---------------+-------+
1 row in set (0.00 sec)

Com_update：更新操作的次数

mysql> SHOW STATUS LIKE 'com_update';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update    | 0     |
+---------------+-------+
1 row in set (0.00 sec)

Com_delete：删除操作的次数

mysql> SHOW STATUS LIKE 'com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 0     |
+---------------+-------+
1 row in set (0.00 sec)

Slow_queries：查询mysql服务器的慢查询次数

mysql> SHOW STATUS LIKE 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

分析查询语句

Explain [extended] select select_option;
mysql> explain select * from fruits\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL   //使用哪个列或常数与索引一起使用来查询记录
         rows: 16  
        Extra:
1 row in set (0.00 sec)

Select_type:表示select语句的类型
其中simple 是简单查询(不包括连接查询和子查询)
Primary 主查询
Union 连接查询
Dependent union 连接查询中的第二个或后面的select语句，取决于外面的查询
Union result：连接查询的结果

Type:表的连接类型

System 仅有系统表一行

Const 数据表中最多只有一行匹配，将在查询开始时被读取，并在余下的查询优化中，作为常量
Eq_ref 用于使用 = 操作符比较带索引的列
ref 对于来自前面的表的任意行的组合，从该表中读取所有匹配的行
ref_or_null 同上，添加可以专门搜索包含null值的行
index_merge 将连接类型表示使用了索引并优化方法

range 只检索给定范围的行

index 与all的连接类型相同，除了只扫描索引树
all 前面的表的任意行的组合，进行完整的表的扫描
possible_keys: NULL //指出mysql使用哪个索引在表中找到行（NULL表示没有创建索引）
Key：表示查询实际使用的索引，没有用索引表示NULL
Key_Len:表示mysql选择的索引字段按字节计算长度
Ref：表示使用哪个列或常数与索引一起来查询记录
Rows：显示mysql在表中进行查询时必须检查的行数。
Extra：mysql在处理查询时的额外详细信息。

describe select 语句和分析查询语句

mysql> DESC select * from fruits\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16
        Extra:
1 row in set (0.00 sec)

索引提高了查询效率

Create index 索引名 on 表名(字段)
mysql> EXPLAIN select * from fruits where f_name='apple';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | fruits | ALL  | NULL          | NULL | NULL    | NULL |   16 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> CREATE INDEX index_name ON fruits(f_name);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> EXPLAIN select * from fruits where f_name='apple';
+----+-------------+--------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | fruits | ref  | index_name    | index_name | 765     | const |    1 | Using where |
+----+-------------+--------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.13 sec)

二、优化查询速度

1.做索引之后，用 like ‘xx%’ %不在第一位查询效率最高

mysql> explain select * from fruits where f_name like '%x';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | fruits | ALL  | NULL          | NULL | NULL    | NULL |   16 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
 
mysql> explain select * from fruits where f_name like 'x%';
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | fruits | range | index_name    | index_name | 765     | NULL |    4 | Using where |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.05 sec)

2.多字段索引，除第一字段查询最快，其余不按索引来，索引不生效

mysql> CREATE  INDEX index_id_price ON fruits(f_id,f_price);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> explain select * from fruits where f_id='12'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)
 
mysql> explain select * from fruits where f_price='5.2'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16
        Extra: Using where
1 row in set (0.00 sec)

3.若创建索引所设置的字段，查询索引组合 or 左右边的值都是属于索引设置字段下的值

mysql> EXPLAIN SELECT * FROM fruits where f_name='apple' or s_id=101\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
         type: ALL
possible_keys: index_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16
        Extra: Using where
1 row in set (0.00 sec)
 
mysql> EXPLAIN SELECT * FROM fruits where f_name='apple' or f_id='12'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
         type: index_merge
possible_keys: PRIMARY,index_name,index_id_price
          key: index_name,PRIMARY
      key_len: 765,30
          ref: NULL
         rows: 2
        Extra: Using union(index_name,PRIMARY); Using where
1 row in set (0.00 sec)

三、优化数据库表结构

将字段很多的表分解成多个表

优化表结构

1 将字段很多的表分解成多个
2 增加中间表
3 合理增加冗余字段
4 优化插入记录的速度
5 避免使用子查询

（1）禁用索引

在插入数据之前禁用索引，会让创建索引不会生效，命令：alter table 表名 disable keys ，注意表的创建表后加引擎 engine=myisam，可以禁用成功

mysql> ALTER TABLE fruits DISABLE KEYS;
Query OK, 0 rows affected, 1 warning (0.05 sec)

（2）禁用唯一性检查

插入记录之前禁用唯一性检查，命令：set unique_checks=0 关闭set unique_checks=1

mysql> set unique_checks=0;
Query OK, 0 rows affected (0.00 sec)

（3）使用批量插入

多条插入命令整合成一条命令

mysql> INSERT INTO fruits values('x1','101','mongo2','5.5');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO fruits values('x2','101','mongo2','5.5');
Query OK, 1 row affected (0.08 sec)
 
mysql> INSERT INTO fruits values('x3','101','mongo2','5.5');
Query OK, 1 row affected (0.01 sec)
 
mysql> INSERT INTO fruits values('x4','101','mongo2','5.5');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into fruits values ('x8','101','mongo2','5.5'), ('x7','101','mongo2','5.5'), ('x6','101','mongo2','5.5'), ('x5','101','mongo2','5.5');
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

（4）使用load data infile批量插入

对于innnodb表来说

1.禁用唯一性检查

mysql> SET unique_checks=0;
Query OK, 0 rows affected (0.00 sec)

2.禁用外键检查

命令: set foreign_key_checks=0，开启=1

mysql> SET foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

3.禁用自动提交

命令: set autocommit=0，开启=1

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

（5）分析表，检查表和优化表

分析表： 分析关键字的分布
检查表： 检查是否存在错误
优化表： 消除删除或更新造成的空间浪费
分析表语句： analyze [local |no_write_to_binlog] table tb1_name[tb2_name]……
Local的关键字不写入二进制日志后跟1个表或多个表
在分析期间只能读，不能进行插入和更新的操作。

mysql> ANALYZE TABLE fruits;
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| test.fruits | analyze | status   | OK       |
+-------------+---------+----------+----------+
1 row in set (0.07 sec)

Table是表名 op执行的操作是什么 msg_type 信息级别（status是正常状态，info是信息，note注意，warning警告，error错误） msg_text 是显示信息
检查表： 检查是否存在错误,关键字统计，检查视图是否有错误
Check table 表名 option ={quick |fast | medium |extended |changed}
Quick 不扫描行，不检查错误连接
Fast 只检查没有被正确关闭的表
Medium 扫描行验证被删除的连接是有效的，也可以计算各行的关键字校验和。
Extended 对每行所有关键字进行全面的关键字查找
Changed 只检查上次检查后被更改的表和没有被正确关闭的表
Option只对myisam 有效对innodb表无效在执行时会给表加上只读锁

mysql> CHECK TABLE fruits ;
+-------------+-------+----------+----------+
| Table       | Op    | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.fruits | check | status   | OK       |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)

优化表：消除删除或更新造成的空间浪费
Optimize [local |no_write_to_binlog] table tb1_name ….
只能优化myisam的表和innodb的表都有效
但是只能优化表中的varchar\text\blob
执行过程中上只读锁

mysql> optimize table fruits\G
*************************** 1. row ***************************
   Table: test.fruits
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: test.fruits
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (0.12 sec)

四、索引缓冲区

可以提高查询速度适合查询的时候使用

在mysql配置文件里写
Vim /etc/my.cnf编辑下

[mysql]
query_cache_size=512M   缓冲区的大小
query_cache_type=1      开启缓冲区
用一个语句刷新缓冲区
在mysql软件里写
flush query cache

刷新缓冲区，清理查询缓冲区当中的碎片

注意：看到
Sql_no_cache 不能建索引缓冲区

(一)连接

连接通常来自Web服务器，下面列出了一些与连接有关的参数，以及该如何设置它们。

1、max_connections

这是Web服务器允许的最大连接数，记住每个连接都要使用会话内存(关于会话内存，文章后面有涉及)。

2、max_packet_allowed

最大数据包大小，通常等于你需要在一个大块中返回的最大数据集的大小，如果你在使用远程mysqldump，那它的值需要更大。

3、aborted_connects

检查系统状态的计数器，确定其没有增长，如果数量增长说明客户端连接时遇到了错误。

4、thread_cache_size

入站连接会在MySQL中创建一个新的线程，因为MySQL中打开和关闭连接都很廉价，速度也快，它就没有象其它数据库，如Oracle那么多持续连接了，但线程预先创建并不会节约时间，这就是为什么要MySQL线程缓存的原因了。
如果在增长请密切注意创建的线程，让你的线程缓存更大，对于2550或100的thread_cache_size，内存占用也不多。

(二)查询缓存

MySQL中的缓存查询包括两个解析查询计划，以及返回的数据集，如果基础表数据或结构有变化，将会使查询缓存中的项目无效。

1、query_cache_min_res_unit

MySQL参数中query_cache_min_res_unit查询缓存中的块是以这个大小进行分配的，使用下面的公式计算查询缓存的平均大小，根据计算结果设置这个变量，MySQL就会更有效地使用查询缓存，缓存更多的查询，减少内存的浪费。

2、query_cache_size

这个参数设置查询缓存的总大小。

3、query_cache_limit

这个参数告诉MySQL丢掉大于这个大小的查询，一般大型查询还是比较少见的，如运行一个批处理执行一个大型报表的统计，因此那些大型结果集不应该填满查询缓存。

qcache hit ratio = qcache_hits / (qcache_hits + com_select)
 
使用
SQL> show status like 'qcache%';
SQL> show status like 'com_%';
找到这些变量。
average query size = (query_cache_size - qcache_free_memory)/qcache_queries_in_cache
使用
SQL> show variables like 'query%';
qcache_* status variables you can get with:
SQL> show status like 'qcache%';
获取query_cache_size的值。

(三)临时表

内存速度是相当快的，因此我们希望所有的排序操作都在内存中进行，我们可以通过调整查询让结果集更小以实现内存排序，

或将变量设置得更大。
tmp_table_size
max_heap_table_size

无论何时在MySQL中创建临时表，它都会使用这两个变量的最小值作为临界值，除了在磁盘上构建临时表外，还会创建许多会话，这些会话会抢占有限制的资源，因此最好是调整查询而不是将这些参数设置得更高，同时，需要注意的是有BLOB或TEXT字段类型的表将直接写入磁盘。深入浅出MySQL双向复制技术

(四)会话内存

MySQL中每个会话都有其自己的内存，这个内存就是分配给SQL查询的内存，因此你想让它变得尽可能大以满足需要。但你不得不平衡同一时间数据库内一致性会话的数量。这里显得有点黑色艺术的是MySQL是按需分配缓存的，因此，你不能只添加它们并乘以会话的数量，这样估算下来比MySQL典型的使用要大得多。最佳做法是启动MySQL，连接所有会话，然后继续关注顶级会话的VIRT列，mysqld行的数目通常保持相对稳定，这就是实际的内存总用量，减去所有的静态MySQL内存区域，就得到了实际的所有会话内存，然后除以会话的数量就得到平均值。

1、read_buffer_size

缓存连续扫描的块，这个缓存是跨存储引擎的，不只是MyISAM表。

2、sort_buffer_size

执行排序缓存区的大小，最好将其设置为1M-2M，然后在会话中设置，为一个特定的查询设置更高的值。

3、join_buffer_size

执行联合查询分配的缓存区大小，将其设置为1M-2M大小，然后在每个会话中再单独按需设置。

4、read_rnd_buffer_size

用于排序和order by操作，最好将其设置为1M，然后在会话中可以将其作为一个会话变量设置为更大的值。

(五)慢速查询日志

慢速查询日志是MySQL很有用的一个特性。

1、log_slow_queries

MySQL参数中log_slow_queries参数在my.cnf文件中设置它，将其设置为on，默认情况下，MySQL会将文件放到数据目录，文件以“主机名-slow.log”的形式命名，但你在设置这个选项的时候也可以为其指定一个名字。

2、long_query_time

默认值是10秒，你可以动态设置它，值从1到将其设置为on，如果数据库启动了，默认情况下，日志将关闭。截至5.1.21和安装了Google补丁的版本，这个选项可以以微秒设置，这是一个了不起的功能，因为一旦你消除了所有查询时间超过1秒的查询，说明调整非常成功，这样可以帮助你在问题变大之前消除问题SQL。