MySQL中索引+explain的使用示例
发表于：2025-02-02 作者：千家信息网编辑
千家信息网最后更新 2025年02月02日，这篇文章给大家分享的是有关MySQL中索引+explain的使用示例的内容。小编觉得挺实用的，因此分享给大家做个参考。一起跟随小编过来看看吧。一、索引的介绍在mysql中,索引就是数据结构,已经在文件
千家信息网最后更新 2025年02月02日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安全错误
数据库的锁怎样保障安全
中国知识数据库
db2数据库导出导入
服务器的netbios
云端数据库系统
mysql查看数据库语句
爬数据库
过磅系统软件开发公司电话
数据库原理与应用下载
中小学的家庭教育和网络安全
校园网络安全维护制度
迅雷下载显示服务器未连接
松江区专业性软件开发销售方法
master数据库是什么
长垣网络安全宣传片
株洲市网络安全执法回头看
数据库分库分表数据一致性
文明上网守护网络安全黑板报简单
县环保局网络安全应急预案
如何设置默认数据库文件夹里
棉城猫王互联网科技公司最新招聘
通信网络技术举例说明
软件开发的实践报告
焦作服务器机柜厂家直销
未成年网络安全手抄报模板难
网络技术课程论文范围
什么经营范围能开软件开发费
专业硬软件开发设计公司
软件开发做一个管理系统
计算机网络技术课程教学标准
河北唐山软件开发培训学校
