MySQL中索引+explain的使用示例
发表于：2025-11-08 作者：千家信息网编辑
千家信息网最后更新 2025年11月08日，这篇文章给大家分享的是有关MySQL中索引+explain的使用示例的内容。小编觉得挺实用的，因此分享给大家做个参考。一起跟随小编过来看看吧。一、索引的介绍在mysql中,索引就是数据结构,已经在文件
千家信息网最后更新 2025年11月08日MySQL中索引+explain的使用示例
这篇文章给大家分享的是有关MySQL中索引+explain的使用示例的内容。小编觉得挺实用的，因此分享给大家做个参考。一起跟随小编过来看看吧。
一、索引的介绍
在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 1mysql> show create table t_index\G;*************************** 1. row *************************** Table: t_indexCreate 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=utf81 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: 0mysql> 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: articleCreate 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=utf81 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>Allpossible_keys: NULL//可能使用到的索引 key: NULL//实际使用到的索引 key_len: NULL//索引的长度 ref: NULL rows: 1307580//可能进行扫描表的行数 filtered: 10.00 Extra: Using where1 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//当对主键索引进行等值查询的时候出现constpossible_keys: PRIMARY key: PRIMARY//实际使用到的所有primary索引 key_len: 4//索引的长度4 = int占4个字节 ref: const rows: 1//所扫描的行数只有一行 filtered: 100.00 Extra: NULL1 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 filtered: 100.00 Extra: NULL1 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: constpossible_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: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 1307580 filtered: 100.00 Extra: Using index1 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: 0type为ref，应该是关联，但是ref是constmysql> explain select * from article where no_index=666666\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: article partitions: NULL type: refpossible_keys: key_no_index key: key_no_index key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL1 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: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 1307580 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)ERROR: No query specifiedmysql> explain select * from article where id >0 order by id\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: article partitions: NULL type: rangepossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 653790 filtered: 100.00 Extra: Using where1 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: 0mysql> show create table article\G;*************************** 1. row *************************** Table: articleCreate 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=utf81 row in set (0.00 sec)
因为%没有出现在like关键字查询的最左边，所以可以使用到索引查询
只要是like左边出现了%，就是全表查询
mysql> explain select * from article where title like 'a%'\G;*************************** 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 condition1 row in set, 1 warning (0.00 sec)mysql> explain select * from article where title like '%a%'\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: 11.11 Extra: Using where1 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: NULL1 row in set, 2 warnings (0.00 sec)ERROR: No query specifiedmysql> 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: indexpossible_keys: NULL key: PRIMARY//使用到了索引 key_len: 4 ref: NULL rows: 90010 filtered: 100.00 Extra: NULL1 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: articleCreate 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=utf81 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: 0mysql> alter table article drop key key_no_index;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table article\G;*************************** 1. row *************************** Table: articleCreate 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=utf81 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: refpossible_keys: index_no_index_title key: index_no_index_title key_len: 97 ref: const,const rows: 1 filtered: 100.00 Extra: NULL1 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: refpossible_keys: index_no_index_title key: index_no_index_title key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL1 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的使用示例就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到吧！
索引
查询
语句
字段
文件
日志
时间
数据
时候
分析
别名
实际
结构
普通
情况
界限
关联
增长
配置
示例
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
美信网络技术有限公司北京市
网络安全单兵作战是填写题吗
湖北前端软件开发怎么样
至高之战文件服务器
山西网络技术转让厂家批发价
软件开发什么才是项目
数据库和数据处理的概念
网络安全保护与研究论文
希望之村服务器关闭档还有吗
软件开发的部门有哪些问题
敏捷软件开发 培训
国外 硕士 网络安全
互联网科技大佬的老婆学生物
达梦数据库数据存储形式
备份软件开发环境
云赞网络技术有限公司
戴尔服务器cpu电压超出范围
云服务器租用价格
湖南使能互联网科技
云服务器对企业有什么影响
软件开发行业的客户挖掘
游戏服务器cpu哪个好
2核4G服务器够用来做网站吗
好好网络安全海报
大专网络安全技能大赛考什么
微信各种软件开发商拿
网络技术公司需要多少钱
基于多态的软件开发
数据库营销的主要步骤
无法连接服务器是怎么回事lol
