MySQL中索引+explain的使用示例

共计 17586 个字符，预计需要花费 44 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章给大家分享的是有关 MySQL 中索引 +explain 的使用示例的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考。一起跟随丸趣 TV 小编过来看看吧。

一、索引的介绍

在 mysql 中, 索引就是数据结构, 已经在文件中按照索引进行排序好的结构.

使用索引可以加快我们的查询速度, 但是对我们的数据增删改效率会降低.

因为一个网站大部分都是查询, 我们主要优化 select 语句.

二、MySQL 中索引的分类

普通索引 key

唯一索引 unique key unique key 别名 别名可忽略 别名可忽略

主键索引 primary key(字段)

全文索引 myisam 引擎支持(只对英文进行索引,mysql 版本 5.6 也支持),sphinx(中文搜索)

混合索引 多个字段组成的索引. 如 key key_index(title,email)

三、索引的基本操作 1、给表添加索引

create table t_index(
 id int not null auto_increment,
 title varchar(30) not null default  ,
 email varchar(30) not null default  ,
 primary key(id),
 unique key uni_email(email) ,
 key key_title(title)
)engine=innodb charset=utf8;

查看表

desc tablename

mysql  desc t_index;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(30) | NO | MUL | | |
| email | varchar(30) | NO | UNI | | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

查看表的创建语句

show create table tbalename/G

mysql  show create table t_index/G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near  /G  at line 1
mysql  show create table t_index\G;
*************************** 1. row ***************************
 Table: t_index
Create Table: CREATE TABLE `t_index` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(30) NOT NULL DEFAULT  ,
 `email` varchar(30) NOT NULL DEFAULT  ,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uni_email` (`email`),
 KEY `key_title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR: 
No query specified

2、删除索引删除主键索引

alter table table_name drop primary key;

注意：

mysql  alter table t_index drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

主键不一定是自增长，但是自增长一定是主键。

删除逐渐之前先要把主键索引的自增长去掉。

mysql  alter table t_index modify id int not null;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

再来删除主键

mysql  alter table t_index drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

删除普通和唯一的索引

alter table table_name drop key‘索引的别名’

实际操作

mysql  alter table t_index drop key uni_email;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql  alter table t_index drop key key_title;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

3、添加索引

alter table t_index add key key_title(title);
alter table t_index add key uni_email(email);
alter table t_index add primary key(id);

4、有无索引对比

create table article(
id int not null auto_increment,
no_index int,
title varchar(30) not null default  ,
add_time datetime,
primary key(id)
);

插入数据

mysql  insert into article(id,title,add_time) values(null, ddsd1212123d ,now());
mysql  insert into article(title,add_time) select title,now() from article;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql  update article set no_index=id;

有无索引查询数据对比

mysql  select * from article where no_index=1495298;
+---------+----------+-----------+---------------------+
| id | no_index | title | add_time |
+---------+----------+-----------+---------------------+
| 1495298 | 1495298 | ddsd1123d | 2019-05-15 23:13:56 |
+---------+----------+-----------+---------------------+
1 row in set (0.28 sec)

mysql  select * from article where id=1495298;
+---------+----------+-----------+---------------------+
| id | no_index | title | add_time |
+---------+----------+-----------+---------------------+
| 1495298 | 1495298 | ddsd1123d | 2019-05-15 23:13:56 |
+---------+----------+-----------+---------------------+
1 row in set (0.01 sec)

表结构

mysql  show create table article\G;
*************************** 1. row ***************************
 Table: article
Create Table: CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `no_index` int(11) DEFAULT NULL,
 `title` varchar(30) NOT NULL DEFAULT  ,
 `add_time` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1572824 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR: 
No query specified

四、explain 分析

使用 explain 可以对 sql 语句进行分析到底有没有使用到索引查询, 从而更好的优化它.

我们只需要在 select 语句前面加上一句 explain 或者 desc.

1、语法

explain|desc select * from tablename \G;

2、分析

用刚才的两个有无索引对比看看

mysql  mysql  explain select * from article where no_index=1495298\G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE// 单表查询
 table: article// 查询的表名
 partitions: NULL
 type: ALL// 索引的类型，从好到坏的情况是：system const range index All
possible_keys: NULL// 可能使用到的索引
 key: NULL// 实际使用到的索引
 key_len: NULL// 索引的长度
 ref: NULL
 rows: 1307580// 可能进行扫描表的行数
 filtered: 10.00
 Extra: Using where
1 row in set, 1 warning (0.00 sec)
ERROR: 
No query specified

mysql  explain select * from article where id=1495298\G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: article
 partitions: NULL
 type: const// 当对主键索引进行等值查询的时候出现 const
possible_keys: PRIMARY
 key: PRIMARY// 实际使用到的所有 primary 索引
 key_len: 4// 索引的长度 4  = int 占 4 个字节
 ref: const
 rows: 1// 所扫描的行数只有一行
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR: 
No query specified

3、explain 的 type 项分析

type 项从优到差依次排序:

system: 一般系统表只有一行记录的时候才会出现

const: 当对主键值进行等值查询的时候会出现, 如 where id=666666

range: 当对索引的值进行范围查询的时候会出现, 如 where id 100000

index: 当我们查询的字段恰好是我们索引文件中的值, 就会出现

All: 最差的一种情况, 需要避免.

实际测试

mysql  use mysql;
mysql  explain select * from user\G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: user
 partitions: NULL
 type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 3
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql  use test;
mysql  explain select * from article where id=666666\G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: article
 partitions: NULL
 type: const
possible_keys: PRIMARY
 key: PRIMARY
 key_len: 4
 ref: const
 rows: 1
 filtered: 100.00
 Extra: NULL

mysql  explain select * from article where id 666666\G;
mysql  explain select * from article where id 666666\G;

mysql  explain select id from article \G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: article
 partitions: NULL
 type: index
possible_keys: NULL
 key: PRIMARY
 key_len: 4
 ref: NULL
 rows: 1307580
 filtered: 100.00
 Extra: Using index
1 row in set, 1 warning (0.00 sec)
ERROR: 
No query specified

如果查询的字段在索引文件存在，那么就会直接从索引文件中进行查询，我们把这种查询称之为索引覆盖查询。

出现 all，我们需要避免，因为进行全面扫描。

对于出现 all 的，可以给该字段增加普通索引查询

mysql  alter table article add key key_no_index(no_index);
Query OK, 0 rows affected (1.92 sec)
Records: 0 Duplicates: 0 Warnings: 0
type 为 ref，应该是关联，但是 ref 是 const
mysql  explain select * from article where no_index=666666\G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: article
 partitions: NULL
 type: ref
possible_keys: key_no_index
 key: key_no_index
 key_len: 5
 ref: const
 rows: 1
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql  select * from article where no_index=666666;
+--------+----------+-----------+---------------------+
| id | no_index | title | add_time |
+--------+----------+-----------+---------------------+
| 666666 | 666666 | ddsd1123d | 2019-05-15 23:13:55 |
+--------+----------+-----------+---------------------+
1 row in set (0.00 sec)

4、使用索引的场景 1、经常出现在 where 后面的字段, 我们需要给他加索引 2、order by 语句使用索引的优化

mysql  explain select * from article order by id\G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: article
 partitions: NULL
 type: index
possible_keys: NULL
 key: PRIMARY
 key_len: 4
 ref: NULL
 rows: 1307580
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR: 
No query specified
mysql  explain select * from article where id  0 order by id\G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: article
 partitions: NULL
 type: range
possible_keys: PRIMARY
 key: PRIMARY
 key_len: 4
 ref: NULL
 rows: 653790
 filtered: 100.00
 Extra: Using where
1 row in set, 1 warning (0.01 sec)
ERROR: 
No query specified

可以看出，即使是使用了索引但是几乎还是全表扫描。

加了 where 就少了一半

3、针对 like 的模糊查询索引的优化

where title like‘%keyword%’==== 全表扫描

where title like‘keyword%’=== 会使用到索引查询

给 title 加上铺索引

mysql  alter table article add key key_index(title);
Query OK, 0 rows affected (2.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql  show create table article\G;
*************************** 1. row ***************************
 Table: article
Create Table: CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `no_index` int(11) DEFAULT NULL,
 `title` varchar(30) NOT NULL DEFAULT  ,
 `add_time` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `key_no_index` (`no_index`),
 KEY `key_index` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

因为 % 没有出现在 like 关键字查询的最左边，所以可以使用到索引查询

只要是 like 左边出现了 %，就是全表查询

mysql  explain select * from article where title like  a% 
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: article
 partitions: NULL
 type: range// 范围查询
possible_keys: key_index
 key: key_index
 key_len: 92//
 ref: NULL
 rows: 1
 filtered: 100.00
 Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
mysql  explain select * from article where title like  %a% 
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: article
 partitions: NULL
 type: ALL// 全表查询
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 1307580
 filtered: 11.11
 Extra: Using where
1 row in set, 1 warning (0.00 sec)

4、limit 语句的索引使用优化

针对于 limit 语句的优化，我们可以在它前面加 order by 索引字段

如果 order by 的字段是索引，会先去索引文件中查找指定行数的数据

mysql  explain select sql_no_cache * from article limit 90000,10 \G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: article
 partitions: NULL
 type: ALL// 全表
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 1307580
 filtered: 100.00
 Extra: NULL
1 row in set, 2 warnings (0.00 sec)
ERROR: 
No query specified
mysql  explain select sql_no_cache * from article order by id limit 90000,10 \G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: article
 partitions: NULL
 type: index
possible_keys: NULL
 key: PRIMARY// 使用到了索引
 key_len: 4
 ref: NULL
 rows: 90010
 filtered: 100.00
 Extra: NULL
1 row in set, 2 warnings (0.00 sec)
ERROR: 
No query specified

另外一种针对于 limit 的优化方法:

索引覆盖 + 延时关联

原理: 主要利用索引覆盖查询, 把覆盖索引查询返回的 id 作为与我们要查询记录的 id 进行相关联,

mysql  select sql_no_cache * from article limit 1000000,10;
+---------+----------+----------------+---------------------+
| id | no_index | title | add_time |
+---------+----------+----------------+---------------------+
| 1196579 | 1196579 | ddsd12123123ad | 2019-05-15 23:13:56 |
| 1196580 | 1196580 | ddsd121231ad | 2019-05-15 23:13:56 |
| 1196581 | 1196581 | ddsd1212123d | 2019-05-15 23:13:56 |
| 1196582 | 1196582 | ddsd1123123d | 2019-05-15 23:13:56 |
| 1196583 | 1196583 | ddsd1123d | 2019-05-15 23:13:56 |
| 1196584 | 1196584 | ddsd1123d | 2019-05-15 23:13:56 |
| 1196585 | 1196585 | ddsd1123d | 2019-05-15 23:13:56 |
| 1196586 | 1196586 | ddsd1123d | 2019-05-15 23:13:56 |
| 1196587 | 1196587 | ddsd1123d | 2019-05-15 23:13:56 |
| 1196588 | 1196588 | ddsd1123d | 2019-05-15 23:13:56 |
+---------+----------+----------------+---------------------+
10 rows in set, 1 warning (0.21 sec)
mysql  select t1.* from article as t1 inner join (select id as pid from article limit 10000,10) as t2 on t1.id=t2.pid;
+-------+----------+----------------+---------------------+
| id | no_index | title | add_time |
+-------+----------+----------------+---------------------+
| 13058 | 13058 | ddsd12123123ad | 2019-05-15 23:13:49 |
| 13059 | 13059 | ddsd121231ad | 2019-05-15 23:13:49 |
| 13060 | 13060 | ddsd1212123d | 2019-05-15 23:13:49 |
| 13061 | 13061 | ddsd1123123d | 2019-05-15 23:13:49 |
| 13062 | 13062 | ddsd1123d | 2019-05-15 23:13:49 |
| 13063 | 13063 | ddsd1123d | 2019-05-15 23:13:49 |
| 13064 | 13064 | ddsd1123d | 2019-05-15 23:13:49 |
| 13065 | 13065 | ddsd1123d | 2019-05-15 23:13:49 |
| 13066 | 13066 | ddsd1123d | 2019-05-15 23:13:49 |
| 13067 | 13067 | ddsd1123d | 2019-05-15 23:13:49 |
+-------+----------+----------------+---------------------+
10 rows in set (0.00 sec)

5、复合 (多列) 索引的最左原则(面试经常问)

只要查询的时候出现复合索引的最左边的字段才会使用到索引查询

把 article 表的 no_index 和 title 建立复合索引:

// 给 no_index 和 title 创建一个复合索引
mysql  alter table article add key index_no_index_title(no_index,title);
Query OK, 0 rows affected (1.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
// 查看创建后的结构
mysql  show create table article\G;
*************************** 1. row ***************************
 Table: article
Create Table: CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `no_index` int(11) DEFAULT NULL,
 `title` varchar(30) NOT NULL DEFAULT  ,
 `add_time` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `key_no_index` (`no_index`),
 KEY `key_index` (`title`),
 KEY `index_no_index_title` (`no_index`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
// 删除 no_index 和 title 的索引
mysql  alter table article drop key key_index;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql  alter table article drop key key_no_index;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql  show create table article\G;
*************************** 1. row ***************************
 Table: article
Create Table: CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `no_index` int(11) DEFAULT NULL,
 `title` varchar(30) NOT NULL DEFAULT  ,
 `add_time` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `index_no_index_title` (`no_index`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
// 复合索引使用情况
mysql  explain select * from article where title= ddsd1123d  and no_index=77777\G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: article
 partitions: NULL
 type: ref
possible_keys: index_no_index_title
 key: index_no_index_title
 key_len: 97
 ref: const,const
 rows: 1
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql  explain select * from article where no_index=77777\G; 
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: article
 partitions: NULL
 type: ref
possible_keys: index_no_index_title
 key: index_no_index_title
 key_len: 5
 ref: const
 rows: 1
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warning (0.00 sec)

五、慢查询日志 1、介绍

我们可以定义 (程序员) 一个 sql 语句执行的最大执行时间, 如果发现某条 sql 语句的执行时间超过我们所规定的时间界限, 那么这条 sql 就会被记录下来.

2、慢查询具体操作

先开启慢日志查询

查看慢日志配置

mysql  show variables like  %slow_query% 
+---------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/caredeMacBook-Pro-slow.log |
+---------------------+--------------------------------------------------+
2 rows in set (0.00 sec)

开启慢日志查询

mysql  set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)

再次检查慢日志配置

mysql  show variables like  %slow_query% 
+---------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/caredeMacBook-Pro-slow.log |
+---------------------+--------------------------------------------------+
2 rows in set (0.00 sec)

去 mysql 配置文件 my.ini 中指定 sql 语句的界限时间和慢日志文件的路径

慢日志的名称，默认保存在 mysql 目录下面的 data 目录下面

log-slow-queries = man.txt

设置一个界限时间

long-query-time=5

重启

六、profile 工具 1、介绍

通过 profile 工具分析一条 sql 语句的时间消耗在哪里

2、具体操作

开启 profile

执行一条 SQL，(开启之后执行的所有 SQL 语句都会被记录下来

，以查看某条 sql 语句的具体执行时间耗费哪里)

根据 query_id 查找到具体的 SQL

实例：

// 查看 profile 设置
mysql  show variables like  %profil% 
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |// 未开启状态
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
// 开启操作
mysql  set profiling = on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
// 查看是否开启成功
mysql  show variables like  %profil% 
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |// 开启成功
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)

具体查询

mysql  select * from article where no_index=666666;
+--------+----------+-----------+---------------------+
| id | no_index | title | add_time |
+--------+----------+-----------+---------------------+
| 666666 | 666666 | ddsd1123d | 2019-05-15 23:13:55 |
+--------+----------+-----------+---------------------+
1 row in set (0.02 sec)
mysql  show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------+
| 1 | 0.00150700 | show variables like  %profil%  |
| 2 | 0.01481100 | select * from article where no_index=666666 |
+----------+------------+---------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql  show profile for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000291 |
| checking permissions | 0.000007 |
| Opening tables | 0.012663 |// 打开表
| init | 0.000050 |
| System lock | 0.000009 |
| optimizing | 0.000053 |
| statistics | 0.001566 |
| preparing | 0.000015 |
| executing | 0.000002 |
| Sending data | 0.000091 |// 磁盘上的发送数据
| end | 0.000004 |
| query end | 0.000007 |
| closing tables | 0.000006 |
| freeing items | 0.000037 |
| cleaning up | 0.000010 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)

感谢各位的阅读！关于 MySQL 中索引 +explain 的使用示例就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到吧！

向 AI 问一下细节

丸趣 TV 网 – 提供最优质的资源集合！