MySQL数据库优化（1）

Myth丶恋晨 2023-06-27 06:15 32阅读 0赞

## 前言 ##

不管对于哪种服务，对于其优化，无非是从两个方面着手，第一个是对于硬件方面的优化，第二个是对系统以及服务本身的优化。

### 1、查询连接MySQL服务器的次数 ###

### 2、查询MySQL服务器的运行时间 ###

### 3、查询操作的次数 ###

### 4、插入操作的次数 ###

### 5、更新操作的次数 ###

### 6、删除操作的次数 ###

### 7、查询MySQL服务器的慢查询次数 ###

## 二、对SQL语句进行分析 ##

### 1、使用explain关键字进行分析 ###

mysql> explain select * from stu_info\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE 
            table: stu_info         #表名
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL     #使用哪个列或常数与索引一起使用来查询记录
             rows: 3
         filtered: 100.00
            Extra: NULL
    1 row in set, 1 warning (0.00 sec)

> 上面的select\_type解释如下：
> 
>  *  Select\_type:表示select语句的类型 其中simple 是简单查询(不包括连接查询和子查询) Primary 主查询 Union 连接查询；

### 2、利用索引来提高查询效率 ###

mysql> explain select * from stu_info where s_id=3\G     #没有索引时的查询结果分析如下
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: stu_info
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 3        #需要查询三行才能查到（这个表数据总共也就三行）
         filtered: 33.33
            Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    
    mysql> create index index_01 on stu_info(s_id);     #创建索引
    mysql> explain select * from stu_info where s_id=3\G   #再次进行查询
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: stu_info
       partitions: NULL
             type: ref
    possible_keys: index_01   #使用的是哪个索引名称
              key: index_01
          key_len: 5
              ref: const
             rows: 1     #创建索引后，查询1行就查到可。
         filtered: 100.00
            Extra: NULL
    1 row in set, 1 warning (0.00 sec)

> 使用索引注意事项如下：
> 
>  *  做索引了之后，用 like ‘xx%’ %不在第一位查询效率最高；
>  *  若使用多字段索引，除了第一字段查询最快，其余不会按索引来，索引不生效；
>  *  若创建索引所设置的字段，查询索引组合 or 左右边的值都是属于索引设置字段下的值。

关于使用索引的其他注意事项，可以参考博文：[MySQL索引类型详解][MySQL]；

## 三、profiling分析查询 ##

通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。 profiling默认是关闭的。可以通过以下语句查看：

### 1、查看profiling是否开启 ###

### 2、开启profiling ###

mysql> set profiling=1;          #开启
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> select @@profiling;    #qu
    +-------------+
    | @@profiling |
    +-------------+
    |           1 |
    +-------------+
    1 row in set, 1 warning (0.00 sec)

### 3、执行要测试的SQL语句 ###

mysql> select * from bank;
    +-------+-------+
    | name  | money |
    +-------+-------+
    | lu    |  1000 |
    | qi    |  1000 |
    | zhang |  2000 |
    +-------+-------+
    3 rows in set (0.00 sec)

### 4、查看SQL语句对应的ID，对其进行分析 ###

mysql> show profiles;
    +----------+------------+--------------------+
    | Query_ID | Duration   | Query              |
    +----------+------------+--------------------+
    |        1 | 0.00012925 | select @@profiling |
    |        2 | 0.00401325 | SELECT DATABASE()  |
    |        3 | 0.01405400 | show databases     |
    |        4 | 0.00034675 | show tables        |
    |        5 | 0.00011475 | show tabels        |
    |        6 | 0.00029225 | show tables        |
    |        7 | 0.00041200 | select * from bank |
    |        8 | 0.00020225 | select * from bank |
    +----------+------------+--------------------+
    8 rows in set, 1 warning (0.00 sec)
    mysql> show profile for query 7;     #查询sql语句的详细分析
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000161 |
    | checking permissions | 0.000010 |
    | Opening tables       | 0.000016 |
    | init                 | 0.000047 |
    | System lock          | 0.000013 |
    | optimizing           | 0.000004 |
    | statistics           | 0.000013 |
    | preparing            | 0.000009 |
    | executing            | 0.000004 |
    | Sending data         | 0.000050 |
    | end                  | 0.000004 |
    | query end            | 0.000008 |
    | closing tables       | 0.000007 |
    | freeing items        | 0.000012 |
    | logging slow query   | 0.000041 |
    | cleaning up          | 0.000013 |
    +----------------------+----------+
    16 rows in set, 1 warning (0.00 sec)

在上面命令的返回结果中，status是profile里的状态，duration是status状态下的耗时，因此我们关注的就是哪个状态最耗时，这些状态中哪些可以优化，当然也可以查看更多的信息，比如：CPU等。语法如下：

mysql> show profile block io for query 7\G
    mysql> show profile all for query 7\G

除了上面的block io和all以外，还可以换成cpu（显示用户cpu时间、系统cpu时间）、ipc（显示发送和接收相关开销信息）、page faults（显示页面错误相关开销信息）、swaps（显示交换次数相关开销的信息）。

**注意：测试完成之后，记得要关闭调试功能，以免影响数据库的正常使用。**

## 四、对数据库表结构进行优化 ##

> 对数据库表结构的优化大概可以从以下几个方面着手：
> 
>  *  将字段很多的表分解成多个表，尽量避免表字段过多；
>  *  增加中间表，合理增加冗余字段；
>  *  优化插入记录的速度；
>     
>      *  在插入数据之前禁用索引，会让创建索引不生效，命令： ALTER TABLE table\_name DISABLE KEYS;
>      *  根据实际情况来定，在插入记录之前禁用唯一性检查，命令：set unique\_checks=0;
>      *  多条插入数据的命令最好整合为一条；
>      *  使用load data infle批量插入数据。
>  *  对于innodb引擎的表来说，以下几点可以进行优化：
>     
>      *  禁用唯一性检查：set unique\_checks=0;
>      *  禁用外键检查：set foreign\_key\_checks=0;
>      *  禁用自动提交：set autocommit=0;

### 分析表，检查表和优化表 ###

所谓分析表，就是分析关键字的分布，检查表就是检查是否存在错误，优化表就是删除或更新造成的空间浪费。

#### 1、分析表 ####

分析表可以一次分析一个或多个表，在分析期间只能读，不能进行插入和更新操作。分析表的语法如下：

对于上述返回的结果解释：Table是表名 ，op执行的操作是什么， msg\_type 信息级别（status是正常状态，info是信息，note注意，warning警告，error错误）， msg\_text 是显示信息。

#### 2、检查表 ####

检查是否存在错误,关键字统计，检查视图是否有错误 Check table 表名 option =\{quick |fast | medium|extended |changed\} Quick 不扫描行，不检查错误连接 Fast 只检查没有被正确关闭的表 Medium 扫描行验证被删除的连接是有效的，也可以计算各行的关键字校验和。 Extended 对每行所有关键字进行全面的关键字查找，Changed 只检查上次检查后被更改的表和没有被正确关闭的表，Option只对myisam 有效 对innodb表无效，在执行时会给表加上只读锁。

#### 3、优化表 ####

消除删除或更新造成的空间浪费，命令语法格式为：Optimize \[local |no\_write\_to\_binlog\] table tb1\_name ….， 优化myisam的表和innodb的表都有效， 但是只能优化表中的varchar\\text\\blob数字类型， 执行过程中上只读锁。

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

———————— 本文至此结束，感谢阅读 ————————

[MySQL]: https://blog.51cto.com/14154700/2460196