mysql优化介绍

ゞ浴缸里的玫瑰 2023-06-22 13:40 42阅读 0赞

当面对一个sql性能问题，我们应该从何处入手使得尽快定位问题sql，我们从基础的命令开始。

### **数据库优化** ###

**1.查看各种命令的执行频率**

mysql> show status like 'Com_%' \g
    +---------------------------+-------+
    | Variable_name             | Value |
    +---------------------------+-------+
    | Com_admin_commands        | 0     |
    | Com_assign_to_keycache    | 0     |
    | Com_alter_db              | 0     |
    | Com_alter_db_upgrade      | 0     |
    | Com_alter_event           | 0     |
    | Com_alter_function        | 0     |
    | Com_alter_procedure       | 0     |
    | Com_alter_server          | 0     |
    | Com_alter_table           | 0     |
    | Com_alter_tablespace      | 0     |
    | Com_alter_user            | 0     |
    | Com_analyze               | 0     |
    | Com_begin                 | 0     |
    | Com_binlog                | 0     |
    | Com_call_procedure        | 0     |
    | Com_change_db             | 0     |
    | Com_change_master         | 0     |
    | Com_check                 | 0     |
    | Com_checksum              | 0     |
    | Com_commit                | 0     |

Com\_xxx表示每个xxx语句执行的次数，创建的的参数如下

Com\_select :执行select操作的次数，一次查询累加一次。

Com\_insert: 执行insert操作的次数，批量插入累加一次

Com\_delete:执行delete操作的次数

Com\_update:执行update操作的次数

下面的参数只针对InnoDB存储引擎，累加的算法也略有不通。

Innodb\_rows\_read:select查询返回的行数。

Innodb\_rows\_inserted:insert插入的的行数。

innodb\_rows\_deleted:delete删除的行数。

innodb\_rows\_updated:update更新的行数，不管是提交还是回滚都会进行累加。

上面命令可以查出你的数据库到底是以查询为主，还是更新操作为主。

对于事物型的应用，通过Com\_commit和Com\_rollback可以了解提交和回滚的情况，对于回滚此处过多，就有可能是编程出现了问题。

下面参数是了解数据库的基本情况

Connections:试图连接Mysql服务器的次数。

Uptime:服务器工作时间。

Slow\_queries:慢查询的次数。

**2.定位执行效率低的sql语句**

通过慢查询日志定位那些sql执行效率低，通过--log\_slow-queries\[=file-names\]选项启动，mysqld会记录一个超过long\_query\_time秒的日志文件。

慢查询日志是在应用执行完之后记录的，索引应用在出现sql效率的问题上并能定位问题，我们可以使用 show processlist命令查看当前mysql在进行的线程，包括线程的状态，是够锁表，可以实时的查看sql的执行情况，同时对一些锁表操作进行优化。

**3通过explain分析低效率的sql执行计划**

通过上面步骤查看到低效率的sql之后，我们就可以使用explain或desc命令获取mysql如何执行select语句的信息,包括如何连接和链接的顺序。

我们先看几个参数的说明

select\_type:表示select类型。常见的取值有SIMPLE(简单表，既不是用表连接或子查询)PRIMARY(朱查询，即外层查询)，UNION( UNION中的第二哥或者后面的查询语句)SUBQUERY(子查询的第一个select)

table:输出结果的表。

type:访问类型，在表中找到所需行的方式。

![format_png][]

从左到右，性能依次最好。

*  type=ALL,全表扫描

mysql> explain select  * from film where rating>9\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: film
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 1000
            Extra: Using where
    1 row in set (0.00 sec)

*  type=index.索引全扫描，mysql遍历整个多音来查询匹配的行

mysql> explain select title from film\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: film
             type: index
    possible_keys: NULL
              key: idx_title
          key_len: 767
              ref: NULL
             rows: 1000
            Extra: Using index
    1 row in set (0.00 sec)

*  type=range.索引范围查询，常用语>,<,<=,>=,between

mysql> explain select * from payment where customer_id>=300 and customer_id<350\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: payment
             type: range
    possible_keys: idx_fk_customer_id
              key: idx_fk_customer_id
          key_len: 2
              ref: NULL
             rows: 1325
            Extra: Using index condition
    1 row in set (0.00 sec)

*  type=ref,使用非唯一索引或唯一索引前缀扫描，返回某个单独之的记录行

mysql> explain select *  from payment where customer_id=350\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: payment
             type: ref
    possible_keys: idx_fk_customer_id
              key: idx_fk_customer_id
          key_len: 2
              ref: const
             rows: 23
            Extra: NULL
    1 row in set (0.00 sec)

*  type=eq\_ref,类似ref,区别是使用的是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单是多表链接中使用primary\_key或unique index作为关联条件、

mysql> explain select * from film a, film_text b where a.film_id =b.film_id\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: b
             type: ALL
    possible_keys: PRIMARY
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 1000
            Extra: NULL
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: a
             type: eq_ref
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 2
              ref: sakila.b.film_id
             rows: 1
            Extra: Using where
    2 rows in set (0.00 sec)

*  type=const/system，单表中最多匹配一行数据，查询非常迅速，所以这个匹配行中的 其他列的值可以被优化器在当前查询中当做常量来处理。例如根据主键或者唯一索引进行查询。

mysql> explain select  *   from (select  * from customer where email='AARON.SELBY@SAKILACUSTOMER.ORG')a\G
    *************************** 1. row ***************************
               id: 1
      select_type: PRIMARY
            table: <derived2>
             type: system
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 1
            Extra: NULL
    *************************** 2. row ***************************
               id: 2
      select_type: DERIVED
            table: customer
             type: const
    possible_keys: uk_email
              key: uk_email
          key_len: 153
              ref: const
             rows: 1
            Extra: NULL
    2 rows in set (0.00 sec)

*  type=null,不用访问表或索引，直接就返回结果。

mysql> explain select   1 from dual where 1\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: No tables used
    1 row in set (0.00 sec)

类型type还有其他值，如ref\_or\_null(与ref类似，区别是对查询条件包含null值查询)，index\_merge(索引合并优化)，unique\_subquery(in的后面是一个查询主键字段的子查询)，index\_subquery(与unique\_subquery类似，区别是在于in的后面是查询非唯一索引字段的子查询)等，

*  possible\_key:表示查询可能使用的索引
 *  key：实际使用的索引
 *  key\_len：使用索引的的字段长度
 *  rows:扫描行的数目
 *  extra:执行情况的说明和 描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息.

mysql4.1引入了explain extended 命令，通过explain extended和show warnings,w我们能够看到sql真正执行之前优化器做了哪些优化，在mysql 5.1开始支持分区的功能，同时可以使用explain partitions 查看数据结果多需要访问的分区名称。

**4.show profile分析sql**

首先查看是否支持show profile，如果支持，开启profiling,如下是否支持使用

select @@have\_profiling.

mysql> select @@have_profiling;  //是否支持show profiling
    +------------------+
    | @@have_profiling |
    +------------------+
    | YES              |
    +------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> select @@profiling;  //默认关闭
    +-------------+
    | @@profiling |
    +-------------+
    |           0 |
    +-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> set profiling=1;  //开启profiling
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> select @@profiling;
    +-------------+
    | @@profiling |
    +-------------+
    |           1 |
    +-------------+
    1 row in set, 1 warning (0.01 sec)

通过profile，我们可以更清楚的了解sql执行的过程，比如，我使用count(\*).我们知道myisam表使用count(\*)是不需要小号太多资源的，相对于Innodb就没有这种元数据缓存，count(\*)执行的较慢，如下

mysql> select count(*) from payment;
    +----------+
    | count(*) |
    +----------+
    |    16049 |
    +----------+
    1 row in set (0.01 sec)

使用show profiles查看query\_id

mysql> show profiles;
    +----------+------------+------------------------------+
    | Query_ID | Duration   | Query                        |
    +----------+------------+------------------------------+
    |        1 | 0.00350500 | select @@profiling           |
    |        2 | 0.00863050 | select count(*) from payment |
    +----------+------------+------------------------------+
    2 rows in set, 1 warning (0.00 sec)

在使用show profiling for query 查看执行过程的每个线程状态和消耗的时间

mysql> show profile for query 2;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000506 |
    | checking permissions | 0.000272 |
    | Opening tables       | 0.000467 |
    | init                 | 0.000039 |
    | System lock          | 0.000229 |
    | optimizing           | 0.000231 |
    | statistics           | 0.000040 |
    | preparing            | 0.000222 |
    | executing            | 0.000083 |
    | Sending data         | 0.006353 |
    | end                  | 0.000024 |
    | query end            | 0.000013 |
    | closing tables       | 0.000022 |
    | freeing items        | 0.000090 |
    | cleaning up          | 0.000041 |
    +----------------------+----------+
    15 rows in set, 1 warning (0.00 sec)

我们发现消耗的时间在 sending data(表示mysql线程开始访问数据，到返回到客户端，而不是仅仅返回结果给客户端，sending data 状态往往需要做大量的磁盘读取操作，所以往往是访问消耗最长的状态)，

我们也可以查看mysql在上什么资源(all,cpu，block io,context switch,page faults)上消耗过高的时间，例如，查看CPU

mysql> show profile cpu for query 2;
    +----------------------+----------+----------+------------+
    | Status               | Duration | CPU_user | CPU_system |
    +----------------------+----------+----------+------------+
    | starting             | 0.000506 | 0.000000 |   0.000000 |
    | checking permissions | 0.000272 | 0.000000 |   0.000000 |
    | Opening tables       | 0.000467 | 0.000000 |   0.000000 |
    | init                 | 0.000039 | 0.000000 |   0.000000 |
    | System lock          | 0.000229 | 0.000000 |   0.000000 |
    | optimizing           | 0.000231 | 0.000000 |   0.000000 |
    | statistics           | 0.000040 | 0.000000 |   0.000000 |
    | preparing            | 0.000222 | 0.000000 |   0.000000 |
    | executing            | 0.000083 | 0.000000 |   0.000000 |
    | Sending data         | 0.006353 | 0.010000 |   0.000000 |
    | end                  | 0.000024 | 0.000000 |   0.000000 |
    | query end            | 0.000013 | 0.000000 |   0.000000 |
    | closing tables       | 0.000022 | 0.000000 |   0.000000 |
    | freeing items        | 0.000090 | 0.000000 |   0.000000 |
    | cleaning up          | 0.000041 | 0.000000 |   0.000000 |
    +----------------------+----------+----------+------------+
    15 rows in set, 1 warning (0.01 sec)

使用myisam存储引擎，效率就比较好，完全不需要访问数据

mysql> create table payment_myisam like payment;
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> alter table payment_myisam engine=myisam;
    Query OK, 0 rows affected (0.05 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> insert into payment_myisam select  *  from payment;
    Query OK, 16049 rows affected (0.09 sec)
    Records: 16049  Duplicates: 0  Warnings: 0
    
    mysql> select count(*) from payment_myisam;
    +----------+
    | count(*) |
    +----------+
    |    16049 |
    +----------+
    1 row in set (0.00 sec)

使用show profiles  查看queryid ,查看queryid消耗在哪个状态以及时间

mysql> show profiles
        -> ;
    +----------+------------+----------------------------------------------------+
    | Query_ID | Duration   | Query                                              |
    +----------+------------+----------------------------------------------------+
    |        1 | 0.00350500 | select @@profiling                                 |
    |        2 | 0.00863050 | select count(*) from payment                       |
    |        3 | 0.00044600 | set @query_id :=2                                  |
    |        4 | 0.00038475 | select state ,sum(duration) as total_r,            |
    |        5 | 0.05704400 | create table payment_myisam like payment           |
    |        6 | 0.05384750 | alter table payment_myisam engine=myisam           |
    |        7 | 0.08750150 | insert into payment_myisam select  *  from payment |
    |        8 | 0.00061250 | select count(*) from payment_myisam                |
    +----------+------------+----------------------------------------------------+
    8 rows in set, 1 warning (0.00 sec)
    
    mysql> show profile for query 8;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000115 |
    | checking permissions | 0.000035 |
    | Opening tables       | 0.000064 |
    | init                 | 0.000054 |
    | System lock          | 0.000053 |
    | optimizing           | 0.000023 |
    | executing            | 0.000022 |
    | end                  | 0.000023 |
    | query end            | 0.000022 |
    | closing tables       | 0.000037 |
    | freeing items        | 0.000048 |
    | cleaning up          | 0.000117 |
    +----------------------+----------+
    12 rows in set, 1 warning (0.00 sec)

发现在myisam存储引擎中，在executing之后就直接结束查询了，完全不需要访问数据。

经过以上步骤基本就可确定问题出现在哪里，此时可以采用响应的措施，进行提高执行的效率。最常见的措施就是添加索引。

### 常用的sql优化 ###

### **大量插入数据** ###

MyiSam存储引擎的表，进行大量导入数据，可以使用，但是对于Innodb存储引擎这种方式并不能提高导入效率。我们可以使用下面几条规则：

1.因为Innodb存储引擎的表示按照主键顺序保存的，我们按照主键顺序排序，可以提高导入顺序

2.在导入数据前执行set unique\_checks=0,关闭唯一索引校验，在导入后执行set unique\_checks=1,恢复唯一性校验，可以恢复导入的效率

如果应用使用自动提交的方式，建议在导入数据之前执行 set autocommit=0,关闭自动提交，结束之后再执行set autocommit=1.打开自动提交**，**

**优化insert 语句**

1.  如果从同一个客户端插入很多行，使用多个值表的insert语句，这种将大大的减少客户端和数据库之间的连接诶，关闭消耗，inset into test values (1,2) ,(3,4),(5,6)
2.  如果从不同的客户端插入很多行，可以通过insert delayed 语句得到更高的速度，delayed的含义是让insert语句马上执行，其实数据都被放到了内存的队列中，并没有进行真正写入磁盘，low\_priority刚好相反，在所有用户对表读写完成之后进行插入，
3.  将索引文件和数据文件放在不同的磁盘存放
4.  如果进行批量插入的时候，可以设置bulk\_insert\_buffer\_size变量值的方法来提高速度，但是这个只对MyISAM表使用
5.  当从一个文件装载一个表的时候，可以使用load data infile.

**优化order by **

优化ordder by 先要了解mysql的排序方式

第一种就是通过有序索引扫描直接返回有序数据，

mysql> explain select customer_id from customer order by store_id\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: customer
             type: index
    possible_keys: NULL
              key: idx_fk_store_id
          key_len: 1
              ref: NULL
             rows: 599
            Extra: Using index
    1 row in set (0.00 sec)

第二种通过返回的数据进行排序，也就是Filesort,不通过索引直接返回排序结果排序Filesort,

mysql> explain select  *  from customer order by store_id\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: customer
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 599
            Extra: Using filesort
    1 row in set (0.00 sec)

Filesort是通过响应的排序算法，将取得的数据在sort\_buffer\_size系统变量设置的内存排序去中进行排序，如果内存装载不下，他讲在磁盘上进行数据进行分块，在对每个数据块进行排序，然后将各个块进行合并成有序的集合，sort\_buffer\_size是对每一个线程独有的，所以在mysql的同一时刻，会存在多个sort\_buffer\_size.

所以我们优化order by  的策略就是尽量的不进行额外的排序，通过索引直接返回有序的数据，where 条件和order by 使用相同的索引，且order  by  的顺序和索引的顺序相同，order by 是降序或升序，否则就会进行额外的排序，这样就会出现Filesort,

总结一下使用sql会使用索引

select * from table order by key_part1 ,key_part2
    select * from table where key_part1=1 order by key_part1 desc, key_part2 desc
    select * from table order by key_part1 desc ,key_part2 desc

以下不会用到索引

select * from table order by key1 desc , key2 asc --混用desc asc
    select * from table where key2=constant order by key1  --where 条件关键字和order by 关键字不同
    select * from table order by  key1 ,key2 --order by 使用不同的关键字

**Filesort优化**

某些情况下还是无法避免Filesort ，所以要加快Filesort操作。Mysql有两种排序算法，

1.  两次扫扫描算法，
    
    首先是取出需要的字段和行指针信息，之后再sort\_buffer中就进行排序，如果sort buffer 不够就使用临时表存储中进行排序，完成排序后在使用行指针回表查询数据，需要两次访问数据，第一次排序字段和获取行指针信心，第二次，需要那行信息回表查询记录，缺点是需要多次IO操作，优点内存消耗少
2.  一次扫描算法
    
    一次性取出满足条件的左右字段，在sor buffer中进行排序，直接返回结果，缺点内存消耗大，优点，排序效率高，

mysql是使用系统变量max\_length\_for\_sort\_data的大小和query语句的字段总大小判断使用哪一种算法，如果max\_length\_for\_sort\_data更大，使用第二种优化之后算法，否则使用第一种算法。

适当的时候可以增加max\_length\_for\_sort\_data，能够让mysql选择更优化的算法，也可以适当的加大sort\_buffer\_size排序区，尽可能的在内存中进行排序，而不是通过临时表进行排序，max\_length\_for\_data和sort\_buffer\_size都不能设置过大，否则会带来其他问题。也查询的的时候，尽量select 具体的字段，不要使用select \*.

**优化group by**

默认情况下，mysql对group by col1,col2 字段进行排序，这与order by col1 col2类似，如果显式的堆一个包含相同列的order by 子句，实际上没有什么影响，如果查询group by 但是用户想要避免不必要的排序，则可以指定order by null.

**优化分页查询**

一般查询是，通过创建覆盖索引能够比较好的提高性能，一个常见的问题就是limit 1000,20 查询出1020行，但是返回的是1000到1020条数据，其他数据都进行抛弃了

1.使用主键回表查询原表的记录,下面我们发现直接查询是进行全表查询，而使用主键关联回表查询可以提高查询效率

mysql> explain select  film_id, description from film order by title limit 50,5 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: film
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 1000
            Extra: Using filesort
    1 row in set (0.00 sec)
    
    mysql> explain select a.film_id,a.description from film a inner join (select film_id from film order by title limit 50,5) b on a.film_id=b.film_id \G
    *************************** 1. row ***************************
               id: 1
      select_type: PRIMARY
            table: <derived2>
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 55
            Extra: NULL
    *************************** 2. row ***************************
               id: 1
      select_type: PRIMARY
            table: a
             type: eq_ref
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 2
              ref: b.film_id
             rows: 1
            Extra: NULL
    *************************** 3. row ***************************
               id: 2
      select_type: DERIVED
            table: film
             type: index
    possible_keys: NULL
              key: idx_title
          key_len: 767
              ref: NULL
             rows: 1000
            Extra: Using index
    3 rows in set (0.00 sec)

2.记录上一次的某个位置，用记录上一页的最后一行的字段，在使用limit n ,

mysql> explain select  *  from payment where rental_id<15640 order by rental_id desc limit 10\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: payment
             type: range
    possible_keys: fk_payment_rental
              key: fk_payment_rental
          key_len: 5
              ref: NULL
             rows: 8043
            Extra: Using index condition
    1 row in set (0.00 sec)

使用排序rental\_id 记录上一页的最后位置，在根据这个位置过滤且使用limit n，可以有效提高查询的效率，但是在rental\_id有大量重复的情况下，这种优化会丢失数据。

**使用sql 提示**

SQL提示也是优化数据库的一种重要手段，就是认为的加入一些提示达到优化的目的。

1.  use index:在查询表明的后面，添加use index 来提供希望mysql参考索引列表，就可以让mysql不在考虑其他可用的索引
2.  ignore index:如果用户想忽略一个或多个索引，可以使用ignore index .
3.  force index :可以使用mysql强制只用某个特定的索引。

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9Uek5ubUxDWE9YUE9GSVI1VGgzd2czZHVXNWliYmd2QlNyUnFKQmliUTFpY3QxcURrYUpOakVITFFRMGZzRlhqa3lhR296Y1l6bE9pYXFpYXlRQ29KSmtpYW9PQS82NDA?x-oss-process=image/format,png