MySQL数据库优化

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

一、常用查询

1.1 查询链接MySQL服务器的次数

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 616   |
+---------------+-------+

1.2 查询MySQL服务器运行时间

mysql> show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 3534  |
+---------------+-------+
#单位为秒

1.3 查询操作的次数

mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 2     |
+---------------+-------+

1.4 查询插入操作的次数

mysql> show status like 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 2     |
+---------------+-------+
#即使插入的操作是错误的，依然会增加次数

1.5 更新操作的次数

mysql> show status like 'com_update';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update    | 1     |
+---------------+-------+

1.6 删除操作的次数

mysql> show status like 'com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 0     |
+---------------+-------+

1.7 查询慢查询次数

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 10     |
+---------------+-------+

二、对SQL语句进行分析

2.1 使用explain关键字进行分析

mysql> explain select * from stu_info\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE       #表示select语句的类型 其中simple 是简单查询(不包括连接查询和子查询) Primary 主查询 Union 连接查询
        table: stu_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL      #使用哪个列或常数与索引一起使用来查询记录
         rows: 3
     filtered: 100.00
        Extra: NULL

2.2 使用索引提高查询效率

mysql> explain select * from stu_info where 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
mysql> create index index_01 on stu_info(id);
#创建索引
mysql> explain select * from stu_info where 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

使用索引注意事项：

创建索引之后，用 like ‘xx%’ %不在第一位查询效率最高；

若使用多字段索引，除了第一字段查询最快，其余不会按索引来，索引不生效；

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

关于索引的注意事项可以参考MySQL索引

三、profiling分析查询

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

3.1 查询profiling是否开启

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |     #OFF表示未开启
| profiling_history_size | 15    |
+------------------------+-------+
mysql> select  @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
#0表示没有开启

3.2 开启profiling

mysql> set profiling=1;    #开启
mysql> select  @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+

3.3 执行要测试的SQL语句

mysql> select * from stu_info;
+------+------+
| id   | name |
+------+------+
|    1 | lv   |
|    2 | lv1  |
|    3 | l1   |
+------+------+

3.4 查看SQL语句对应的ID，对其进行分析

mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00024900 | select  @@profiling               |
|        2 | 0.00126350 | show variables like '%profiling%' |
|        3 | 0.00033450 | select * from stu_info            |
+----------+------------+-----------------------------------+
mysql> show profile for query 3;    #查询sql语句的详细分析
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000085 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000035 |
| init                 | 0.000015 |
| System lock          | 0.000006 |
| optimizing           | 0.000003 |
| statistics           | 0.000028 |
| preparing            | 0.000010 |
| executing            | 0.000002 |
| Sending data         | 0.000108 |
| end                  | 0.000006 |
| query end            | 0.000005 |
| closing tables       | 0.000005 |
| freeing items        | 0.000008 |
| cleaning up          | 0.000009 |
+----------------------+----------+
#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;

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

4.1 分析表

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

mysql> analyze table stu_info;
+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| sbtest.stu_info | analyze | status   | OK       |
+-----------------+---------+----------+----------+

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

4.2 检查表

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

mysql> check table stu_info;
+-----------------+-------+----------+----------+
| Table           | Op    | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| sbtest.stu_info | check | status   | OK       |
+-----------------+-------+----------+----------+

4.3 优化表

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

mysql> optimize table stu_info\G
*************************** 1. row ***************************
   Table: sbtest.stu_info
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: sbtest.stu_info
      Op: optimize
Msg_type: status
Msg_text: OK

posted @ 2020-12-27 14:50 吕振江阅读(117) 评论(0) 编辑收藏举报

刷新页面返回顶部

吕振江的博客