位置：首页-资讯-数据库

MySQL优化之三：SQL语句优化

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

一 SQL语句优化的一般步骤：

1 通过show status命令了解各种SQL语句的执行频率

mysql> show status; #show status：显示服务器状态信息

+-----------------------------------------------+-------------+

| Variable_name | Value |

+-----------------------------------------------+-------------+

| Aborted_clients | 0 |

| Aborted_connects | 0 |

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 8 |

| Binlog_stmt_cache_disk_use | 0 |

| Binlog_stmt_cache_use | 25 |

| Bytes_received | 2919 |

| Bytes_sent | 51750 |

......

mysql> show status like "com%"; #显示当前session中，统计参数的值

+---------------------------+-------+

| 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 | 2 |

| Com_alter_tablespace | 0 |

| Com_alter_user | 0 |

| Com_analyze | 0 |

| Com_begin | 0 |

......

Com_xxx：表示每个xxx语句执行的次数，以下几个统计参数非常重要：

Com_select：执行select的次数，一次查询累计加1
Com_insert：执行insert操作的次数，批量插入只累加1
Com_delete：执行delete操作的次数，
Com_update：执行update操作的次数，

以上参数针对所有存储引擎的表操作。

下面的参数是针对InnoDB存储引擎的，算法也稍有不同：

Innodb_rows_read：select查询返回的行数

Innodb_rows_inserted：执行insert操作插入的行数

Innodb_rows_updated：执行update操作更新的行数

Innodb_rows_deleted：执行delete操作删除的行数

通过以上参数的了解，可以判断出当前数据库是以插入更新为主还是以查询操作为主，以及各种类型SQL大致的执行比例是多少。

此外，以下几个参数可以帮助用户了解数据库的基本情况：

Uptime：数据库服务器的工作时间

Connections：试图连接服务器的次数

Slow_queries：慢查询的次数

2 定位执行效率低的SQL语句

方式1：通过慢查询日志定位

方式2：查看当前正在进行的线程

mysql> show processlist;

+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+

+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+

或

[root@localhost ~]# mysqladmin -uroot -h 127.0.0.1 processlist -proot

Warning: Using a password on the command line interface can be insecure.

+----+------+-----------------+----+---------+------+-------+------------------+

+----+------+-----------------+----+---------+------+-------+------------------+

+----+------+-----------------+----+---------+------+-------+------------------+

备注：show processlist;只列出前100条，如果想全列出请使用show full processlist;

3 通过explain分析低效的SQL语句的执行

通过之前的步骤查询到效率低的SQL语句之后，可以通过explain命令获取MySQL是如何执行select语句的信息。如：

mysql> explain select * from emp1;

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

1 row in set (0.00 sec)

select_type——select类型
table——输出结果的表
type——表示MySQL在表中找到所需行的方式，或者叫访问类型，常见有以下几种：性能由最差到最好。

type=all，即通过全表扫描找到匹配的行。

type=index，索引全扫描，mysql遍历索引才找到匹配的行。

type=range，索引范围扫描，

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

type=eq_ref，类似ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配。

type=const/system，表单中最多有一个匹配行，查询起来非常迅速。如根据主键和唯一索引进行的查询。

type=null，不需要访问表或索引，直接就可以得到结果。

possible_keys——表示查询时可能使用的索引
key——表示实际使用的索引
key_len——使用到索引字段的长度
rows——扫描行的数量
Extra——执行情况的说明和描述

4 通过show profile了解分析SQL执行的过程

mysql> select @@have_profiling; #查看是否支持

+------------------+

| @@have_profiling |

+------------------+

| YES |

+------------------+

mysql> set profiling=1; #开启profiling，默认是关闭

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from emp1; #执行一个语句

+------+--------+-------+------------+

+------+--------+-------+------------+

| 111 | 4 | ccc | 2011-11-30 |

| 666 | 11 | ddd | 2014-12-22 |

| 888 | 22 | eee | 2015-11-30 |

| 333 | 8 | fff | 2011-04-30 |

+------+--------+-------+------------+

4 rows in set (0.02 sec)

mysql> show profiles; #查看当前SQL语句的查询ID

+----------+------------+---------------------------+

| Query_ID | Duration | Query |

+----------+------------+---------------------------+

| 1 | 0.01696625 | select count(*) from emp1 |

| 2 | 0.02623125 | select * from emp1 |

+----------+------------+---------------------------+

mysql> show profile for query 2; #查看执行过程中线程的每个状态和消耗时间

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000111 |

| checking permissions | 0.000019 |

| Opening tables | 0.000046 |

| init | 0.000043 |

| System lock | 0.000031 |

| optimizing | 0.000016 |

| statistics | 0.000039 |

| preparing | 0.000023 |

| executing | 0.000008 |

| Sending data | 0.025442 |

| end | 0.000020 |

| query end | 0.000014 |

| closing tables | 0.000016 |

| freeing items | 0.000326 |

| cleaning up | 0.000079 |

+----------------------+----------+

Sending data表示MySQL线程开始访问数据行并把结果返回给客户端。通常是整个查询中耗时最长的状态

mysql> show profile cpu for query 2; #查看耗费CPU的时间，Sending data主要耗费在CPU上

+----------------------+----------+----------+------------+

+----------------------+----------+----------+------------+

| starting | 0.000111 | 0.000000 | 0.000000 |

| checking permissions | 0.000019 | 0.000000 | 0.000000 |

| Opening tables | 0.000046 | 0.000000 | 0.000000 |

| init | 0.000043 | 0.000000 | 0.000000 |

| System lock | 0.000031 | 0.000000 | 0.000000 |

| optimizing | 0.000016 | 0.000000 | 0.000000 |

| statistics | 0.000039 | 0.000000 | 0.000000 |

| preparing | 0.000023 | 0.000000 | 0.000000 |

| executing | 0.000008 | 0.000000 | 0.000000 |

| Sending data | 0.025442 | 0.000000 | 0.001999 |

| end | 0.000020 | 0.000000 | 0.000000 |

| query end | 0.000014 | 0.000000 | 0.000000 |

| closing tables | 0.000016 | 0.000000 | 0.000000 |

| freeing items | 0.000326 | 0.000000 | 0.000000 |

| cleaning up | 0.000079 | 0.000000 | 0.000000 |

+----------------------+----------+----------+------------+

mysql> show profile all for query 1\G #查看所有明细，了解MySQL在什么资源上耗费了过高的时间

5 通过trace分析优化器如何选择执行计划

6 确定问题之后，采取相应的措施优化

由前面的步骤确认对表进行全表扫描，导致查询效果不理想，那么对表的某个字段建立索引。具体如下：

mysql> create index index_ename on emp1(ename);

Query OK, 0 rows affected (0.25 sec)

Records: 0 Duplicates: 0 Warnings: 0

建立索引后，再看下这条语句的执行状态：

mysql> explain select ename from emp1;

建立索引后，可以发现对表扫描的行数大大减少，提高了对表的访问速度。

二索引问题

索引是数据库优化中最重要也是最常用的手段之一，通过索引可以帮助用户解决大多数SQL性能问题。

1 索引的存储分类：索引是在存储引擎层中实现的

B-Tree索引：最常见的索引，大部分引擎支持B树索引。
HASH索引：只有Memory引擎支持，使用场景简单
Full-text（全文索引）：一种特殊索引类型

创建索引方式 1：

mysql> create index index_age1 on emp1(age1);

Query OK, 0 rows affected (0.15 sec)

Records: 0 Duplicates: 0 Warnings: 0

创建索引方式 2：

mysql> alter table zwj.emp1 add index index_ename (ename);

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

查看索引：

mysql> show index from zwj.emp1;

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| emp1 | 1 | index_ename | 1 | ename | A | 4 | NULL | NULL | YES | BTREE | | |

| emp1 | 1 | index_age1 | 1 | age1 | A | 4 | NULL | NULL | YES | BTREE | | |

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

删除索引：

mysql> drop index index_age1 on zwj.emp1;

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

或

mysql> alter table zwj.emp1 drop index index_ename;

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

另有复合索引：需要咨询开发人员

创建复合索引（将最常用作限制条件的列放在最左边，依次递减）：

mysql> create index name_passwd on abc.student(name,passwd);（需要咨询研发部门）

2 查看索引的使用情况：