mysql捕获分析和优化sql

最新推荐文章于 2021-01-21 14:01:15 发布

吴世豪芳芳

最新推荐文章于 2021-01-21 14:01:15 发布

阅读量76

点赞数

本文链接：https://blog.csdn.net/qq_44906603/article/details/89785220

版权

author：skate
time：2012/05/24

mysql的sql优化

mysql捕获、分析和优化sql

1. 捕获sql优化相关信息的方法

A.slow query log
默认是不开启的，需要调整相应的参数才可以生效
主要用变量
5.0版本
log_slow_queries
long_query_time

5.1版本
slow_query_log
slow_query_log_file
long_query_log
log_output //控制输出格式的“file||table||both”

参考文档：http://blog.csdn.net/wyzxg/article/details/7269020

B.general query log
默认是不启用的，需要调整相应的参数才可以生效
5.0版本
log

5.1版本
general_log
general_log_file
log_output //控制输出格式的“file||table||both”

这种日志一般不用在生产环境，因为日志量很大，影响性能。在测试和开发环境可以使用，可以帮助我们找出问题

C.binary log
默认也是不启用，需要调整相应的参数才可以生效。是主从复制的基础
log_bin
expire_logs_days

参考：http://blog.csdn.net/wyzxg/article/details/7412777

D.processlist
可以查看当前连接到mysql库的进程/线程情况，查看是否有锁
1). mysql> show [full] processlist;
2). $ mysqladmin processlist
3). mysql> select * from information_schema.processlist; //版本5.1

E.engine status
可以查看存储引擎（innodb）详细的运行信息
mysql> show engine innodb status;

G.status variables
这是mysql内部累计的计数器，可以了解数据库运行状况,如连接数，吞吐量等
1) $ mysqladmin extended-status
2) mysql> show global status;

常用命令：显示间隔1秒变换值
$ mysqladmin -u -p -r -s -i 1 extended-status | grep -v "| 0"

分析优化sql所需要的信息

A.explain

mysql>

mysql> explain select * from aa1,aa where aa1.a1=aa.a1\G;
*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: aa
         type: ALL
possible_keys: idx_aa
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra:
*************************** 2. row ***************************
           id: 1
select_type: SIMPLE
        table: aa1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
        Extra: Using where
2 rows in set (0.00 sec)

ERROR:
No query specified

mysql>

B. mk-visual-explain
mk-visual-explain是maatket的一个功能，可以格式explain的输出，格式化成树形，查看起来更方便

安装方法
wget http://maatkit.googlecode.com/files/maatkit-6652.tar.gz
tar zxvf maatkit-6652.tar.gz -C /usr/local/
cd /usr/local/maatkit-6652
perl Makefile.PL
make install

eg：
[root@detaildb maatkit-6652]# echo "explain select * from aa1,aa where aa1.a1=aa.a1" |mysql -uroot -p test| mk-visual-explain
Enter password:
JOIN
+- Filter with WHERE
| +- Table scan
|     rows           11
|     +- Table
|        table          aa1
+- Table scan
   rows           1
   +- Table
      table          aa
      possible_keys idx_aa
[root@detaildb maatkit-6652]#

C.show create table
可以查看表的定义、属性和索引

语法：mysql> SHOW CREATE TABLE [schema.]tablename

eg:
mysql> show create table aa1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aa1 | CREATE TABLE `aa1` (
`a1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`a2` timestamp NOT NULL default '0000-00-00 00:00:00',
`a3` time default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

查看库里的所有表
mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| aa             |
| aa1            |
| aa2            |
| test           |
| test1          |
+----------------+
5 rows in set (0.00 sec)

D. show table status
查看表的当前基本信息

语法：SHOW TABLE STATUS [IN schema] LIKE 'tablename'

eg：

mysql> show table status like 'aa'\G;
*************************** 1. row ***************************
           Name: aa
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
Auto_increment: NULL
    Create_time: 2012-05-21 14:21:37
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment: InnoDB free: 175104 kB
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

E. show indexes

可以查看指定表的索引信息

语法：SHOW INDEX[ES] [FROM|IN] [schema.]tablename

mysql> show indexes from aa;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| aa    |          1 | idx_aa   |            1 | a1          | A         |           1 |        6 | NULL   | YES | BTREE      |         |
| aa    |          1 | idx_aa   |            2 | a2          | A         |           1 |     NULL | NULL   | YES | BTREE      |         |
| aa    |          1 | idx_aa   |            3 | a3          | A         |           1 |       20 | NULL   | YES | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

mysql>

F. table/index size

通过information_schema收集的信息可以查看表和索引的大小

mysql> SELECT table_schema,
    -> table_name,
    -> engine,
    -> row_format,
    -> table_rows,
    -> avg_row_length,
    -> (data_length+index_length)/1024/1024 as total_mb,
    -> (data_length)/1024/1024 as data_mb,
    -> (index_length)/1024/1024 as index_mb,
    -> CURDATE() AS today
    -> FROM information_schema.tables
    -> WHERE table_schema=DATABASE()
    -> ORDER BY 7 DESC;
+--------------+------------+--------+------------+------------+----------------+------------+------------+------------+------------+
| table_schema | table_name | engine | row_format | table_rows | avg_row_length | total_mb   | data_mb    | index_mb   | today      |
+--------------+------------+--------+------------+------------+----------------+------------+------------+------------+------------+
| test         | test       | InnoDB | Compact    |          4 |           4096 | 0.03125000 | 0.01562500 | 0.01562500 | 2012-05-23 |
| test         | test1      | InnoDB | Compact    |          0 |              0 | 0.03125000 | 0.01562500 | 0.01562500 | 2012-05-23 |
| test         | aa         | InnoDB | Compact    |          1 |          16384 | 0.03125000 | 0.01562500 | 0.01562500 | 2012-05-23 |
| test         | aa1        | InnoDB | Compact    |         11 |           1489 | 0.01562500 | 0.01562500 | 0.00000000 | 2012-05-23 |
| test         | aa2        | InnoDB | Compact    |          1 |          16384 | 0.01562500 | 0.01562500 | 0.00000000 | 2012-05-23 |
+--------------+------------+--------+------------+------------+----------------+------------+------------+------------+------------+
5 rows in set (0.00 sec)

mysql>

G. show session status

查看当前会话的状态变量

mysql>

H. show profile

查看sql在执行每一步所需要消耗的资源。

mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000041 |
| Waiting for query cache lock   | 0.000005 |
| checking query cache for query | 0.000022 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000010 |
| init                           | 0.000013 |
| optimizing                     | 0.000005 |
| executing                      | 0.000010 |
| end                            | 0.000005 |
| query end                      | 0.000004 |
| closing tables                 | 0.000003 |
| freeing items                  | 0.000014 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000004 |
+--------------------------------+----------+
14 rows in set (0.00 sec)

mysql>

总结：分析sql中使用的哪些表，表有哪些索引；表和索引的大小；sql的执行计划与资源消耗；相关状态变量等。为
我们优化sql做好优化依据。

3.优化sql

通过slow_log等方式可以捕获慢查询sql，然后就是减少其对io和cpu的使用（不合理的索引、不必要的数据访问和排序）

当我们面对具体的sql时，首先查看其执行计划
A.看其是否使用索引
B.查看其查询的记录数
C.确定索引的代价是否过高
D.是否可以使用复合索引
E.是否有“using temporary”
F.是否有“using filesort”

创建高效索引
mysql的innodb有自己特殊的聚集索引（数据是按聚集索引的顺序存储的并和索引存储在一起），索引访问效率较高，次
要索引是通过先找到pk聚集索引，然后才能找到数据。

单列索引
单列索引比较简单，就是对单个列创建的索引，主键索引最好选择int类型，提高性能。

复合索引
复合索引是多列组合而成的索引，过滤效果越好的越放在前面，尽量通过索引完成过滤，回表只是取出额外的字段，当然如果
不需要回表，只扫描索引就可以获取所需数据最好了

eg：
创建表
mysql> show create table tab1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab1 | CREATE TABLE `tab1` (
`col1` bigint(20) NOT NULL auto_increment,
`col2` bigint(20) NOT NULL,
`col3` bigint(20) NOT NULL,
PRIMARY KEY (`col1`),
KEY `col2` (`col2`),
KEY `col3` (`col3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tab1 where col2='12121212' and col3 in(121212,121212);
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | tab1 | ref | col2,col3 | col2 | 8 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> alter table tab1 drop index col3 ,add index(col3,col2);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

从这里可以看出，还是用的单列索引col2，没有使用我们创建复合索引col3

mysql> alter table tab1 drop index col2 ,add index(col2,col3);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from tab1 where col2='12121212' and col3 in(121212,121212);
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | tab1 | ref | col3,col2 | col2 | 8 | const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

把列col2的单列索引变为col2和col3的复合索引，这时有两个复合索引，sql使用的col2索引，索引mysql是对"="的选择性更大

mysql> alter table tab1 drop index col2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from tab1 where col2='12121212' and col3 in(121212,121212);
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | tab1 | index | col3 | col3 | 16 | NULL | 1 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql>

把复合索引col2删除了，这是使用了col3索引，但键长从8变为16

前缀索引

为了减少索引的大小，提高索引的效率，对于字符型的字段强烈建议用前缀索引

mysql> show create table tab2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab2 | CREATE TABLE `tab2` (
`col1` int(11) NOT NULL auto_increment,
`col2` varchar(30) default NULL,
`col3` varchar(100) NOT NULL,
PRIMARY KEY (`col1`),
KEY `idx_tab2_1` (`col3`(6))
) ENGINE=InnoDB AUTO_INCREMENT=393217 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

查看当前表和索引的大小

创建非前缀索引
mysql> create index idx_tab2_1 on tab2(col3);
Query OK, 393216 rows affected (6.20 sec)
Records: 393216 Duplicates: 0 Warnings: 0

mysql> alter table tab2 drop index idx_tab2_1;
Query OK, 393216 rows affected (3.51 sec)
Records: 393216 Duplicates: 0 Warnings: 0

创建前缀索引
mysql> create index idx_tab2_1 on tab2(col3(10));
Query OK, 393216 rows affected (4.84 sec)
Records: 393216 Duplicates: 0 Warnings: 0

mysql>

从上面的测试可以看到索引从“41.81250000M”减少到“8.51562500”

sql写法注意事项
1)禁止3表以上的join
2)避免"select *"和排序功能共同使用
3)用jion替换子查询
4)用union all替换union
5)避免数据类型的转换，同数据类型比较
6)避免排序(通过索引或减少排序记录数)
7)对数据尽早过滤(复合索引过滤性更好的字段放的更靠前；尽量加少最后join结果集的数量)
8)把大sql拆分为多小sql
9)如果只是分组，用”group by a1 order by null“替换“group by a1”去除排序
10)禁止索引null列
11)字符例是否是前缀索引

------end-----