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

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

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

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

普通索引 key

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

主键索引 primary key(字段)

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

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

createtablet_index(idintnotnullauto_increment,titlevarchar(30)notnulldefault'',emailvarchar(30)notnulldefault'',primarykey(id),uniquekeyuni_email(email),keykey_title(title))engine=innodbcharset=utf8;

查看表

desc tablename

mysql>desct_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|||+-------+-------------+------+-----+---------+----------------+3rowsinset(0.01sec)

查看表的创建语句

show create table tbalename/G

mysql>showcreatetablet_index/G;ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'/G'atline1mysql>showcreatetablet_index\G;***************************1.row***************************Table:t_indexCreateTable:CREATETABLE`t_index`(`id`int(11)NOTNULLAUTO_INCREMENT,`title`varchar(30)NOTNULLDEFAULT'',`email`varchar(30)NOTNULLDEFAULT'',PRIMARYKEY(`id`),UNIQUEKEY`uni_email`(`email`),KEY`key_title`(`title`))ENGINE=InnoDBDEFAULTCHARSET=utf81rowinset(0.00sec)ERROR:Noqueryspecified2、删除索引删除主键索引

alter table table_name drop primary key;

注意：

mysql>altertablet_indexdropprimarykey;ERROR1075(42000):Incorrecttabledefinition;therecanbeonlyoneautocolumnanditmustbedefinedasakey

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

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

mysql>altertablet_indexmodifyidintnotnull;QueryOK,0rowsaffected(0.05sec)Records:0Duplicates:0Warnings:0

再来删除主键

mysql>altertablet_indexdropprimarykey;QueryOK,0rowsaffected(0.04sec)Records:0Duplicates:0Warnings:0删除普通和唯一的索引

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

实际操作

mysql>altertablet_indexdropkeyuni_email;QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0

mysql>altertablet_indexdropkeykey_title;QueryOK,0rowsaffected(0.02sec)Records:0Duplicates:0Warnings:03、添加索引

altertablet_indexaddkeykey_title(title);altertablet_indexaddkeyuni_email(email);altertablet_indexaddprimarykey(id);4、有无索引对比

createtablearticle(idintnotnullauto_increment,no_indexint,titlevarchar(30)notnulldefault'',add_timedatetime,primarykey(id));

插入数据

mysql>insertintoarticle(id,title,add_time)values(null,'ddsd1212123d',now());mysql>insertintoarticle(title,add_time)selecttitle,now()fromarticle;QueryOK,10rowsaffected(0.01sec)Records:10Duplicates:0Warnings:0mysql>updatearticlesetno_index=id;

有无索引查询数据对比

mysql>select*fromarticlewhereno_index=1495298;+---------+----------+-----------+---------------------+|id|no_index|title|add_time|+---------+----------+-----------+---------------------+|1495298|1495298|ddsd1123d|2019-05-1523:13:56|+---------+----------+-----------+---------------------+1rowinset(0.28sec)

mysql>select*fromarticlewhereid=1495298;+---------+----------+-----------+---------------------+|id|no_index|title|add_time|+---------+----------+-----------+---------------------+|1495298|1495298|ddsd1123d|2019-05-1523:13:56|+---------+----------+-----------+---------------------+1rowinset(0.01sec)

表结构

mysql>showcreatetablearticle\G;***************************1.row***************************Table:articleCreateTable:CREATETABLE`article`(`id`int(11)NOTNULLAUTO_INCREMENT,`no_index`int(11)DEFAULTNULL,`title`varchar(30)NOTNULLDEFAULT'',`add_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=1572824DEFAULTCHARSET=utf81rowinset(0.00sec)ERROR:Noqueryspecified四、explain分析

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

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

explain|desc select * from tablename \G;

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

mysql>mysql>explainselect*fromarticlewhereno_index=1495298\G;***************************1.row***************************id:1select_type:SIMPLE//单表查询table:article//查询的表名partitions:NULLtype:ALL//索引的类型，从好到坏的情况是：system>const>range>index>Allpossible_keys:NULL//可能使用到的索引key:NULL//实际使用到的索引key_len:NULL//索引的长度ref:NULLrows:1307580//可能进行扫描表的行数filtered:10.00Extra:Usingwhere1rowinset,1warning(0.00sec)ERROR:Noqueryspecified

mysql>explainselect*fromarticlewhereid=1495298\G;***************************1.row***************************id:1select_type:SIMPLEtable:articlepartitions:NULLtype:const//当对主键索引进行等值查询的时候出现constpossible_keys:PRIMARYkey:PRIMARY//实际使用到的所有primary索引key_len:4//索引的长度4=int占4个字节ref:constrows:1//所扫描的行数只有一行filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)ERROR:Noqueryspecified3、explain的type项分析

type项从优到差依次排序:

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

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

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

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

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

实际测试

mysql>usemysql;mysql>explainselect*fromuser\G;***************************1.row***************************id:1select_type:SIMPLEtable:userpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:3filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)

mysql>usetest;mysql>explainselect*fromarticlewhereid=666666\G;***************************1.row***************************id:1select_type:SIMPLEtable:articlepartitions:NULLtype:constpossible_keys:PRIMARYkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL

mysql>explainselect*fromarticlewhereid>666666\G;mysql>explainselect*fromarticlewhereid<666666\G;

mysql>explainselectidfromarticle\G;***************************1.row***************************id:1select_type:SIMPLEtable:articlepartitions:NULLtype:indexpossible_keys:NULLkey:PRIMARYkey_len:4ref:NULLrows:1307580filtered:100.00Extra:Usingindex1rowinset,1warning(0.00sec)ERROR:Noqueryspecified

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

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

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

mysql>altertablearticleaddkeykey_no_index(no_index);QueryOK,0rowsaffected(1.92sec)Records:0Duplicates:0Warnings:0type为ref，应该是关联，但是ref是constmysql>explainselect*fromarticlewhereno_index=666666\G;***************************1.row***************************id:1select_type:SIMPLEtable:articlepartitions:NULLtype:refpossible_keys:key_no_indexkey:key_no_indexkey_len:5ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)速度飞跃mysql>select*fromarticlewhereno_index=666666;+--------+----------+-----------+---------------------+|id|no_index|title|add_time|+--------+----------+-----------+---------------------+|666666|666666|ddsd1123d|2019-05-1523:13:55|+--------+----------+-----------+---------------------+1rowinset(0.00sec)4、使用索引的场景1、 经常出现在where后面的字段,我们需要给他加索引2、order by 语句使用索引的优化

mysql>explainselect*fromarticleorderbyid\G;***************************1.row***************************id:1select_type:SIMPLEtable:articlepartitions:NULLtype:indexpossible_keys:NULLkey:PRIMARYkey_len:4ref:NULLrows:1307580filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)ERROR:Noqueryspecifiedmysql>explainselect*fromarticlewhereid>0orderbyid\G;***************************1.row***************************id:1select_type:SIMPLEtable:articlepartitions:NULLtype:rangepossible_keys:PRIMARYkey:PRIMARYkey_len:4ref:NULLrows:653790filtered:100.00Extra:Usingwhere1rowinset,1warning(0.01sec)ERROR:Noqueryspecified

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

加了where就少了一半

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

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

给title加上铺索引

mysql>altertablearticleaddkeykey_index(title);QueryOK,0rowsaffected(2.16sec)Records:0Duplicates:0Warnings:0mysql>showcreatetablearticle\G;***************************1.row***************************Table:articleCreateTable:CREATETABLE`article`(`id`int(11)NOTNULLAUTO_INCREMENT,`no_index`int(11)DEFAULTNULL,`title`varchar(30)NOTNULLDEFAULT'',`add_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`),KEY`key_no_index`(`no_index`),KEY`key_index`(`title`))ENGINE=InnoDBAUTO_INCREMENT=1507299DEFAULTCHARSET=utf81rowinset(0.00sec)

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

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

mysql>explainselect*fromarticlewheretitlelike'a%'\G;***************************1.row***************************id:1select_type:SIMPLEtable:articlepartitions:NULLtype:range//范围查询possible_keys:key_indexkey:key_indexkey_len:92//ref:NULLrows:1filtered:100.00Extra:Usingindexcondition1rowinset,1warning(0.00sec)mysql>explainselect*fromarticlewheretitlelike'%a%'\G;***************************1.row***************************id:1select_type:SIMPLEtable:articlepartitions:NULLtype:ALL//全表查询possible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:1307580filtered:11.11Extra:Usingwhere1rowinset,1warning(0.00sec)4、limit语句的索引使用优化

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

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

mysql>explainselectsql_no_cache*fromarticlelimit90000,10\G;***************************1.row***************************id:1select_type:SIMPLEtable:articlepartitions:NULLtype:ALL//全表possible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:1307580filtered:100.00Extra:NULL1rowinset,2warnings(0.00sec)ERROR:Noqueryspecifiedmysql>explainselectsql_no_cache*fromarticleorderbyidlimit90000,10\G;***************************1.row***************************id:1select_type:SIMPLEtable:articlepartitions:NULLtype:indexpossible_keys:NULLkey:PRIMARY//使用到了索引key_len:4ref:NULLrows:90010filtered:100.00Extra:NULL1rowinset,2warnings(0.00sec)ERROR:Noqueryspecified

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

索引覆盖+延时关联

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

mysql>selectsql_no_cache*fromarticlelimit1000000,10;+---------+----------+----------------+---------------------+|id|no_index|title|add_time|+---------+----------+----------------+---------------------+|1196579|1196579|ddsd12123123ad|2019-05-1523:13:56||1196580|1196580|ddsd121231ad|2019-05-1523:13:56||1196581|1196581|ddsd1212123d|2019-05-1523:13:56||1196582|1196582|ddsd1123123d|2019-05-1523:13:56||1196583|1196583|ddsd1123d|2019-05-1523:13:56||1196584|1196584|ddsd1123d|2019-05-1523:13:56||1196585|1196585|ddsd1123d|2019-05-1523:13:56||1196586|1196586|ddsd1123d|2019-05-1523:13:56||1196587|1196587|ddsd1123d|2019-05-1523:13:56||1196588|1196588|ddsd1123d|2019-05-1523:13:56|+---------+----------+----------------+---------------------+10rowsinset,1warning(0.21sec)mysql>selectt1.*fromarticleast1innerjoin(selectidaspidfromarticlelimit10000,10)ast2ont1.id=t2.pid;+-------+----------+----------------+---------------------+|id|no_index|title|add_time|+-------+----------+----------------+---------------------+|13058|13058|ddsd12123123ad|2019-05-1523:13:49||13059|13059|ddsd121231ad|2019-05-1523:13:49||13060|13060|ddsd1212123d|2019-05-1523:13:49||13061|13061|ddsd1123123d|2019-05-1523:13:49||13062|13062|ddsd1123d|2019-05-1523:13:49||13063|13063|ddsd1123d|2019-05-1523:13:49||13064|13064|ddsd1123d|2019-05-1523:13:49||13065|13065|ddsd1123d|2019-05-1523:13:49||13066|13066|ddsd1123d|2019-05-1523:13:49||13067|13067|ddsd1123d|2019-05-1523:13:49|+-------+----------+----------------+---------------------+10rowsinset(0.00sec)5、复合(多列)索引的最左原则(面试经常问)

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

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

//给no_index和title创建一个复合索引mysql>altertablearticleaddkeyindex_no_index_title(no_index,title);QueryOK,0rowsaffected(1.18sec)Records:0Duplicates:0Warnings:0//查看创建后的结构mysql>showcreatetablearticle\G;***************************1.row***************************Table:articleCreateTable:CREATETABLE`article`(`id`int(11)NOTNULLAUTO_INCREMENT,`no_index`int(11)DEFAULTNULL,`title`varchar(30)NOTNULLDEFAULT'',`add_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`),KEY`key_no_index`(`no_index`),KEY`key_index`(`title`),KEY`index_no_index_title`(`no_index`,`title`))ENGINE=InnoDBAUTO_INCREMENT=1507299DEFAULTCHARSET=utf81rowinset(0.00sec)//删除no_index和title的索引mysql>altertablearticledropkeykey_index;QueryOK,0rowsaffected(0.05sec)Records:0Duplicates:0Warnings:0mysql>altertablearticledropkeykey_no_index;QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0mysql>showcreatetablearticle\G;***************************1.row***************************Table:articleCreateTable:CREATETABLE`article`(`id`int(11)NOTNULLAUTO_INCREMENT,`no_index`int(11)DEFAULTNULL,`title`varchar(30)NOTNULLDEFAULT'',`add_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`),KEY`index_no_index_title`(`no_index`,`title`))ENGINE=InnoDBAUTO_INCREMENT=1507299DEFAULTCHARSET=utf81rowinset(0.00sec)//复合索引使用情况mysql>explainselect*fromarticlewheretitle='ddsd1123d'andno_index=77777\G;***************************1.row***************************id:1select_type:SIMPLEtable:articlepartitions:NULLtype:refpossible_keys:index_no_index_titlekey:index_no_index_titlekey_len:97ref:const,constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)mysql>explainselect*fromarticlewhereno_index=77777\G;***************************1.row***************************id:1select_type:SIMPLEtable:articlepartitions:NULLtype:refpossible_keys:index_no_index_titlekey:index_no_index_titlekey_len:5ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)五、慢查询日志1、介绍

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

先开启慢日志查询

查看慢日志配置

mysql>showvariableslike'%slow_query%';+---------------------+--------------------------------------------------+|Variable_name|Value|+---------------------+--------------------------------------------------+|slow_query_log|OFF||slow_query_log_file|/usr/local/mysql/data/caredeMacBook-Pro-slow.log|+---------------------+--------------------------------------------------+2rowsinset(0.00sec)

开启慢日志查询

mysql>setglobalslow_query_log=on;QueryOK,0rowsaffected(0.00sec)

再次检查慢日志配置

mysql>showvariableslike'%slow_query%';+---------------------+--------------------------------------------------+|Variable_name|Value|+---------------------+--------------------------------------------------+|slow_query_log|ON||slow_query_log_file|/usr/local/mysql/data/caredeMacBook-Pro-slow.log|+---------------------+--------------------------------------------------+2rowsinset(0.00sec)

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

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

log-slow-queries = 'man.txt'

设置一个界限时间

long-query-time=5

重启

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

开启profile

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

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

根据query_id查找到具体的SQL

实例：

//查看profile设置mysql>showvariableslike'%profil%';+------------------------+-------+|Variable_name|Value|+------------------------+-------+|have_profiling|YES||profiling|OFF|//未开启状态|profiling_history_size|15|+------------------------+-------+3rowsinset(0.00sec)//开启操作mysql>setprofiling=on;QueryOK,0rowsaffected,1warning(0.00sec)//查看是否开启成功mysql>showvariableslike'%profil%';+------------------------+-------+|Variable_name|Value|+------------------------+-------+|have_profiling|YES||profiling|ON|//开启成功|profiling_history_size|15|+------------------------+-------+3rowsinset(0.00sec)

具体查询

mysql>select*fromarticlewhereno_index=666666;+--------+----------+-----------+---------------------+|id|no_index|title|add_time|+--------+----------+-----------+---------------------+|666666|666666|ddsd1123d|2019-05-1523:13:55|+--------+----------+-----------+---------------------+1rowinset(0.02sec)mysql>showprofiles;+----------+------------+---------------------------------------------+|Query_ID|Duration|Query|+----------+------------+---------------------------------------------+|1|0.00150700|showvariableslike'%profil%'||2|0.01481100|select*fromarticlewhereno_index=666666|+----------+------------+---------------------------------------------+2rowsinset,1warning(0.00sec)mysql>showprofileforquery2;+----------------------+----------+|Status|Duration|+----------------------+----------+|starting|0.000291||checkingpermissions|0.000007||Openingtables|0.012663|//打开表|init|0.000050||Systemlock|0.000009||optimizing|0.000053||statistics|0.001566||preparing|0.000015||executing|0.000002||Sendingdata|0.000091|//磁盘上的发送数据|end|0.000004||queryend|0.000007||closingtables|0.000006||freeingitems|0.000037||cleaningup|0.000010|+----------------------+----------+15rowsinset,1warning(0.01sec)

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