mysql SQL优化 - 数据库

访问量:1271 创建时间:2021-04-21

项目开发中，性能是重点关注的问题，了解SQL语句的执行过程、数据库如何扫描表、如何使用索引、是否命中索引等信息来帮助优化SQL语句，MySQL提供了explain/desc语句(explain在sql优化中最常用)，来显示这条SQL语句的执行计划，执行计划可以帮助我们查看SQL语句的执行情况，我们可以根据反馈的结果来进行sql优化。

准备数据

create database test;
use test;
create table `test`.`role` (
    id int(10) not null ,
    name varchar(255) character set utf8 collate utf8_general_ci null  default null,
    primary key (id) using  btree
) engine = innodb character set = utf8 collate =utf8_general_ci row_format = DYNAMIC;

create table `test`.`user`(
    id int(11) not null ,
    name varchar(255) character set utf8 collate utf8_general_ci null  default null,
    role_id int(11) null default  null,
    primary key (id) using btree
)engine = InnoDB character set = utf8 collate =utf8_general_ci row_format =DYNAMIC ;

insert into role values (1,'test1');
insert into role values (2,'test2');
insert into role values (4,'贵宾');
insert into role values (3,'管理员');
insert into user values (1,'test1',1);
insert into user values (2,'test2',2);
insert into user values (3,'tomcat',3);
insert into user values (4,'lilei',4);


####desc select* from user;desc与explain相同
mysql> explain select* from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.07 sec)

id字段

id相同，id越靠前的表越先执行。

mysql> explain select * from user u left join role r on u.role_id = r.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
|  1 | SIMPLE      | u     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.u.role_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
#上面可以看出id相同，table分别是u 和r, u表在前先执行。

id不同，id越大的表先执行

mysql> explain select * from user u where u.role_id=(select id from role r where r.id=1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | u     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    2 |    50.00 | Using where |
|  2 | SUBQUERY    | r     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
###从结果看，先执行id 2 table r

id有相同，也有不同：id相同越大的表越先执行，在id相同的表中，id越靠前的表越先执行。

select_type 字段

select_type=simple: 简单的select查询，查询中不包含查询或者union

mysql> explain select* from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

primary : 查询条件中包含子查询时最外层的表(u1)

mysql> explain select * from user u1 where u1.role_id=(select id from user u2 where u2.id=1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | u1    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    2 |    50.00 | Using where |
|  2 | SUBQUERY    | u2    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

UNION(u2) : 使用到union关联时，union关联表；UNION RESULT(): 使用union时，最终的结果集表。

mysql> explain select * from user u1 union select * from user u2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | u1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL            |
|  2 | UNION        | u2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
##将u2的结果和u1的结果合并，产生临时表

dependent union(u3,u4) :在子查询中使用到union的第二个以上的表，DEPENDENT subquery(u2): 在子查询中，使用到union的第一张表

explain select * from user u1 where u1.id in (
    select id from user u2 where u2.id=1
    union
    select id from user u3 where u3.id=2
    union
    select id from user u4 where u4.id=3
    );
+----+--------------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------------+
| id | select_type        | table        | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                          |
+----+--------------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------------+
|  1 | PRIMARY            | u1           | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    2 |   100.00 | Using where                    |
|  2 | DEPENDENT SUBQUERY | u2           | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index                    |
|  3 | DEPENDENT UNION    | u3           | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index                    |
|  4 | DEPENDENT UNION    | NULL         | NULL       | NULL  | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | no matching row in const table |
| NULL | UNION RESULT       | <union2,3,4> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary                |
+----+--------------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------------+
5 rows in set, 1 warning (1.62 sec)
###先执行id432和UNION RESULT合并，然后执行1

subquery(u2)：条件子查询中的表

mysql> explain select * from user u1 where u1.role_id=(select id from user u2 where u2.id=1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | u1    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    2 |    50.00 | Using where |
|  2 | SUBQUERY    | u2    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

subquery(u2,u3): 条件中的子查询的表(包括多重层级)

mysql> explain select * from user u1 where u1.name=(select name from user u2 where u2.name=(select name from user u3 where u3.name='lilei'));
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
|  2 | SUBQUERY    | u2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
|  3 | SUBQUERY    | u3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

dependent subquery(r1):子查询中的条件依赖于外部的查询（r1的条件是u1表中的数据）(不理解)

mysql> explain select * from user u1 where u1.role_id=(select id from role r1 where u1.id=1);
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY            | u1    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    2 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | r1    | NULL       | index | NULL          | PRIMARY | 4       | NULL |    4 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

derived(u1): 衍生表的from子表(该子表必须使用union关联其他表)

mysql> explain select * from (select * from user u1 where u1.role_id=1  union select * from user u2 where u2.name='lilei') temp;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL            |
|  2 | DERIVED      | u1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where     |
|  3 | UNION        | u2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where     |
| NULL | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

table字段

表示该sql语句是作用于那张表的，取值为:表名、表别名、衍生表名等。

mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user u1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

partions字段

涉及到分区的表，准备数据如下：

create table goods_partions(
    id int auto_increment,
    name varchar(12),
    primary key (id)
)

partition by range (id)(
    partition p0 values less than(10000),
    partition p1 values less than maxvalue 
    );
###查看物理存储路径
mysql> show variables like '%dir%';
+-----------------------------------------+--------------------------------+
| Variable_name                           | Value                          |
+-----------------------------------------+--------------------------------+
| basedir                                 | /usr/                          |
| binlog_direct_non_transactional_updates | OFF                            |
| character_sets_dir                      | /usr/share/mysql-8.0/charsets/ |
| datadir                                 | /var/lib/mysql/                |
| innodb_data_home_dir                    |                                |
| innodb_directories                      |                                |
| innodb_doublewrite_dir                  |                                |
| innodb_log_group_home_dir               | ./                             |
| innodb_max_dirty_pages_pct              | 90.000000                      |
| innodb_max_dirty_pages_pct_lwm          | 10.000000                      |
| innodb_redo_log_archive_dirs            |                                |
| innodb_temp_tablespaces_dir             | ./#innodb_temp/                |
| innodb_tmpdir                           |                                |
| innodb_undo_directory                   | ./                             |
| lc_messages_dir                         | /usr/share/mysql-8.0/          |
| plugin_dir                              | /usr/lib64/mysql/plugin/       |
| slave_load_tmpdir                       | /tmp                           |
| tmpdir                                  | /tmp                           |
+-----------------------------------------+--------------------------------+
18 rows in set (0.06 sec)
[root@localhost ~]# ll /var/lib/mysql/test
total 320
-rw-r----- 1 mysql mysql 114688 Apr 21 22:43 goods_partions#p#p0.ibd
-rw-r----- 1 mysql mysql 114688 Apr 21 22:43 goods_partions#p#p1.ibd
-rw-r----- 1 mysql mysql 114688 Apr 21 21:50 role.ibd
-rw-r----- 1 mysql mysql 114688 Apr 21 20:57 user.ibd

###查看查询语句所使用的分区：整个goods_partions使用到了2个分区
mysql> explain select * from goods_partions;
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | goods_partions | p0,p1      | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------+
###查询id<1000记录的（属于p0分区）
mysql> explain select * from goods_partions where id<1000;
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | goods_partions | p0         | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

type字段

反映了一段sql语句性能指标的重要参数，可以通过此参数来判断是否使用到了索引、是否全表扫描、是否范围查询等。

null:代表不访问任何表

mysql> explain select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

system:表中只有一条记录，并且此表为系统表(一般很少出现)

###在mysql8演示是ref,
mysql> use mysql
Database changed
mysql> explain select * from db where host='localhost';
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | db    | NULL       | ref  | PRIMARY       | PRIMARY | 255     | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

const: 通过唯一索引查询到的数据，只查询一次就查询到了

mysql> use test
####分别根据name和id查询，发现只有id的type是const
mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
mysql> explain select * from user where name='tesss';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
###给name字段加上唯一索引（普通索引不行），再次根据name查询，type为const
mysql> create unique index user_name_unique on user(name);
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from user where name='tesss';
+----+-------------+-------+------------+-------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | user_name_unique | user_name_unique | 768     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+------------------+------------------+---------+-------+------+----------+-------+
###测试完删除name字段的唯一索引
mysql> drop index user_name_unique on user;

eq_ref :使用主键关联查询

##代表有其他表引用了r表的主键
mysql> explain select * from user u left join role r on u.role_id=r.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
|  1 | SIMPLE      | u     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |    2 |   100.00 | NULL  |
|  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.u.role_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
###（驱动表多条记录有时候是eq_ref有时候是all）

ref:通过非唯一索引查询到的数据

###创建普通索引
mysql> create index user_name_index on user(name);
Query OK, 0 rows affected (0.12 sec)
mysql> explain select * from user where name='lilei';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | user_name_index | user_name_index | 768     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
mysql> drop index user_name_index on user;

range: 使用索引的范围查询(普通列的范围查询不会是range)

mysql> explain select * from user u where u.id>3;  --使用索引列进行范围查询
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

mysql> explain select * from user u where u.role_id>3;--使用普通列进行范围查询
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
mysql> create index user_role_id_index on user(role_id);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from user u where u.role_id>3;
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | u     | NULL       | range | user_role_id_index | user_role_id_index | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+

index:查询的是索引列，遍历索引树

mysql> explain select id from user;
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | user_role_id_index | 5       | NULL |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+

ALL 效率最低，遍历全表

mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

查询效率从高到低的取值为：

--所有的type字段取值
NULL> system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

--一般情况下type字段取值
system>const>eq_ref>ref>range>index>ALL

字段	含义
possible_keys字段	表示查询时，可能使用的索引
key 字段	表示实际使用的索引，如果为NULL，表示没有使用索引
key_len字段	索引字段的长度，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
ref	引用
rows字段	扫描行的数量
filtered字段
extra字段	执行情况的说明和描述。using filesort说明mysql会对数据使用一个外部索引排序，而不是按照表内的索引顺序进行读取，称为文件排序，效率低；using temporary使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于order by和group by;using index表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错。

show warnings 在explain执行后执行，查看翻译后的sql

索引优化规则

优化方式

分表：横向，表字段相同，数据量太大，纵向：一个表存基本信息，另一个表存详情

分区：优缺点，分区方式管理分区

查看sql执行频率

Mysql 客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。show[session|global]status可以根据需要加上参数"session"或者"global"来显示session级(当前连接)的统计结果和global级a(,自数据库上次启动至今，不写默认session

mysql> show status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_import    | 0     |
| Com_insert    | 4     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 12    |
| Com_signal    | 0     |
| Com_update    | 1     |
| Com_xa_end    | 0     |
+---------------+-------+
#查看整个数据库的操作，以什么操作为主(各种操作的数量)，为数据库优化提供参考
mysql> show global status like 'Com_______';
#查看Innodb 的语句影响行数分布(value是语句操作过得总行数)
mysql> show status like 'Innodb_rows_%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 57    |
| Innodb_rows_read     | 451   |
| Innodb_rows_updated  | 5     |
+----------------------+-------+

mysql> show status like '%Uptime%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| Uptime                    | 624205 |
| Uptime_since_flush_status | 624205 |
+---------------------------+--------+
2 rows in set (0.00 sec)

mysql> show status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like '%Connections%';
+-----------------------------------+---------------------+
| Variable_name                     | Value               |
+-----------------------------------+---------------------+
| Connection_errors_max_connections | 0                   |
| Connections                       | 115                 |
| Max_used_connections              | 9                   |
| Max_used_connections_time         | 2021-04-20 22:19:50 |
| Mysqlx_connections_accepted       | 0                   |
| Mysqlx_connections_closed         | 0                   |
| Mysqlx_connections_rejected       | 0                   |
+-----------------------------------+---------------------+
7 rows in set (0.00 sec)

命令： show status;

show status like '%下面变量%'	作用
Aborted_clients	由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects	尝试已经失败的MySQL服务器的连接的次数。
Connections	试图连接MySQL服务器的次数。
Created_tmp_tables	当执行语句时，已经被创造了的隐含临时表的数量。
Delayed_insert_threads	正在使用的延迟插入处理器线程的数量。
Delayed_writes	用INSERT DELAYED写入的行数。
Delayed_errors	用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands	执行FLUSH命令的次数。
Handler_delete	请求从一张表中删除行的次数。
Handler_read_first	请求读入表中第一行的次数。
Handler_read_key	请求数字基于键读行。
Handler_read_next	请求读入基于一个键的一行的次数。
Handler_read_rnd	请求读入基于一个固定位置的一行的次数。
Handler_update	请求更新表中一行的次数。
Handler_write	请求向表中插入一行的次数。
Key_blocks_used	用于关键字缓存的块的数量。
Key_read_requests	请求从缓存读入一个键值的次数。
Key_reads	从磁盘物理读入一个键值的次数。
Key_write_requests	请求将一个关键字块写入缓存次数。
Key_writes	将一个键值块物理写入磁盘的次数。
Max_used_connections	同时使用的连接的最大数目。
Not_flushed_key_blocks	在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows	在INSERT DELAY队列中等待写入的行的数量。
Open_tables	打开表的数量。
Open_files	打开文件的数量。
Open_streams	打开流的数量(主要用于日志记载）
Opened_tables	已经打开的表的数量。
Questions	发往服务器的查询的数量。
Slow_queries	要花超过long_query_time时间的查询数量。
Threads_connected	当前打开的连接的数量。
Threads_running	不在睡眠的线程数量。
Uptime	服务器工作了多少秒。

定位低效SQL

通过慢查询日志：使用--log-slow-queries=[file_name]选项启动，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
命令： show processlist--慢查询在查询结束时才记录，正在执行的sql不会记录，使用show processlist命令，查看当前mysql在运行的线程，包括线程状态、是否锁表等，可以实时查看SQL的执行情况。如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。 show processlist;只列出前100条，如果想全列出请使用show full processlist;

mysql> show processlist;

show profile分析SQL

MYSQL5.0.37之后增加了show profiles和show profile语句的支持。show profiles能够在做SQL优化时帮助我们了解事件都耗费到哪里去了。

通过having_profiling参数，能够看到当前MySQL是否支持profile:

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+

默认profiling是关闭的，可以通过set语句在session级别开启profiling;

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
####查看当前session所有查询的消耗时间Duration，
mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration   | Query              |
+----------+------------+--------------------+
|        1 | 0.00025150 | select @@profiling |
+----------+------------+--------------------+
#根据id查看sql语句消耗时间在什么地方
mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000132 |
| checking permissions | 0.000013 |
| Opening tables       | 0.000031 |
| init                 | 0.000012 |
| optimizing           | 0.000020 |
| executing            | 0.000012 |
| end                  | 0.000004 |
| query end            | 0.000006 |
| closing tables       | 0.000004 |
| freeing items        | 0.000009 |
| cleaning up          | 0.000009 |
+----------------------+----------+
###加入参数，分析cpu或者磁盘io的情况,了解
mysql> show profile all for query 1\G
mysql> show profile cpu for query 1\G

trace分析优化器执行计划

mysql5.6 提供了对sql的跟踪trace,通过trace文件能够进一步了解为什么优化器选择A计划，而不是选择B计划。

##打开trace,设置格式为json,并设置trace最大能够使用的内存大小，避免解析过程中因为内存过小而不能偶完整展示。
mysql> set optimizer_trace="enabled=on",end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

mysql> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)

示例：

mysql> select * from city where city_id<4;
+---------+-----------+------------+
| city_id | city_name | country_id |
+---------+-----------+------------+
|       1 | 西安      |          1 |
|       2 | NewYork   |          2 |
|       3 | 北京      |          1 |
+---------+-----------+------------+
mysql> select * from information_schema.optimizer_trace\G
###输出内容省略

SQL语句优化

大批量插入数据

#准备数据
create table `tb_user_1` (
    id int(11) not null auto_increment,
    username varchar(45) not null ,
    password varchar(96) not null,
    name varchar(45) not null ,
    birthday datetime default null,
    sex char(1) default null,
    email varchar(45) default null,
    phone varchar(45) default null,
    qq varchar(32) default null,
    status varchar(32) not null comment '用户状态',
    create_time datetime not null ,
    update_time datetime default null,
    primary key (id),
    unique key unique_user_username(username)
) engine = innodb default charset = utf8;
####
load data local infile '/root/data1.log' into table `tb_user_1` fields terminated by ',' lines terminated by '\n';

当使用load命令导入数据的时候，适当地设置可以提高导入效率。Innodb提高导入效率： 1、主键顺序插入，效率高（无序数据load较慢） 2、关闭唯一性校验，在导入数据前 set unique_checks=0 ,关闭唯一性校验，在导入结束后执行 set unique_checks=1 ,恢复校验，可以提高导入效率。 3、手动提交事务，如果mysql使用自动提交事务方式，建议在导入之前执行set autocommit=0,关闭自动提交，导入结束后再执行set autocommit=1,打开自动提交，也可以提高导入效率。

优化insert 语句

#将多条insert合并为1条insert。
insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
insert into tb_test values(1,'tom'),(2,'cat'),(3,'jerry')
#在事务中进行数据插入(如果数据量比较大，可以每1万条提交一次)：
start transaction;
insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
commit;
#数据有序(按主键顺序)插入

优化order by语句

mysql 的排序方式	解释
filesort	通过对返回数据进行排序，所有不是通过索引直接返回排序结果的排序都叫filesort排序(效率低)。
using index	通过有序索引顺序扫描直接返回有序数据，这种情况即为using index,不需要额外排序，操作效率高