文章详情

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

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL优化之三:SQL语句优化

2024-04-02 19:55

关注

一 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语句执行的次数,以下几个统计参数非常重要:

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

下面的参数是针对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;    

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

| Id | User        | Host      | db   | Command | Time  | State                                                                       | Info             |

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

|  1 | system user |           | NULL | Connect | 34400 | Waiting for master to send event                                            | NULL             |

|  2 | system user |           | NULL | Connect |  7738 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |

|  4 | root        | localhost | NULL | Query   |     0 | init                                                                        | 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.

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

| Id | User | Host            | db | Command | Time | State | Info             |

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

| 1  | root | localhost       |    | Sleep   | 265  |       |                  |

| 12 | root | localhost:42210 |    | Query   | 0    | init  | show processlist |

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

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


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

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

mysql> explain select * from emp1;

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

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |

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

|  1 | SIMPLE      | emp1  | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |

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

1 row in set (0.00 sec)

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

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

type=range,索引范围扫描,

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

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

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

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


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;             #执行一个语句

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

| age1 | deptno | ename | birth      |

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

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

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

| Status               | Duration | CPU_user | CPU_system |

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

| 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 索引的存储分类:索引是在存储引擎层中实现的

创建索引方式 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;

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

| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| 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 查看索引的使用情况:

mysql> show status like 'handler_read%';

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

| Variable_name         | Value |

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

| Handler_read_first    | 4     |

| Handler_read_key      | 5     |

| Handler_read_last     | 0     |

| Handler_read_next     | 0     |

| Handler_read_prev     | 0     |

| Handler_read_rnd      | 0     |

| Handler_read_rnd_next | 56    |

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

7 rows in set (0.00 sec)

Handler_read_key:如果索引正在工作,此值应该很高,这个值代表了一个行被索引值读的次数。如果值过低,表明增加索引得到的性能改善不高,因为索引并不常被使用。

Handler_read_rnd_next:值高意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果进行了大量的扫描,它的值会很高,说明索引不正确或查询没有利用到索引。


阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯