The Commands of Show Information Of Index

序言

sql语句调优万万千,这里调优最简单.

基础表

CREATE TABLE `test_index` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
	`data_date` VARCHAR(50) NOT NULL DEFAULT '' ,
	`data_type` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' ,
	`org_code` VARCHAR(50) NOT NULL DEFAULT '' ,
	`org_name` VARCHAR(255) NOT NULL DEFAULT '',
	`truck_id` VARCHAR(50) NOT NULL DEFAULT '' ,
	`carnum` VARCHAR(10) NOT NULL DEFAULT '' ,
	`driver_id` VARCHAR(50) NOT NULL DEFAULT '',
	`driver_name` VARCHAR(255) NOT NULL DEFAULT '',
	`high_risk_num` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`middle_risk_num` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`safe_event_num` INT(11) UNSIGNED NOT NULL DEFAULT '0' ,
	`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ,
	`miles` FLOAT UNSIGNED NOT NULL DEFAULT '0' ,
	`adas_num` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`dms_num` INT(11) UNSIGNED NOT NULL DEFAULT '0' ,
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `idx_dt_org` (`org_code`, `data_date`, `carnum`, `driver_id`) USING BTREE,
	INDEX `idx_dt_org2` (`data_date`, `carnum`, `driver_id`) USING BTREE,
	INDEX `idx_dt_org3` (`carnum`, `driver_id`) USING BTREE,
	INDEX `idx_dt_org4` (`driver_id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

Show Index

顾名思义就是这哪是指定表的当前索引

语法格式有两种:

1. Show Index From tb_name [FROM db_name] 
2. Show Index From db_name.tb_name

参数说明Table所属表Non_unique是否是唯一索引:0表示是,1标识不是Key_name标识索引名称Seq_in_index

该字段在索引中的位置

比如组合索引名index_name中包含3个字段(a,b,c),则a字段的seq_in_index值为1,

b字段的seq_in_index值为2,

c字段的seq_in_index值为3

Column_name该索引所对应的列Collation

标识该字段以何种顺序存储在索引中

Mysql中:

A表示升序

B表示降序

Null表示无设置

Cardinality

它会估计索引中不重复记录，如果这个相对值很小，可能就要评估索引是否有意义cuiyaonan2000@163.com.另外就是如果没有记录的话肯定是0

Sub_part字段值中被编入索引的数量,如果是整个字段值都被编入则显示NULLPacked表示关键字如何被压缩,若没有压缩则显示NULLNull用于显示索引列中是否包含NULL,若包含NULL则显示YES,若没有则显示NOIndex_type显示索引使用的类型:BTREE,FULLTEXT,HASH,RTREEComment字段注释Index_comment索引注释

Explain

相当于显示查询SQL的执行计划cuiyaonan2000@163.com

格式:Explain 自己的SQL

参数 说明id

1. select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
2. id如果相同，可以认为是一组，从上往下顺序执行；
3. 在所有组中，id值越大，优先级越高，越先执行

select_type

表示查询的类型。用于区别普通查询、联合查询、子查询等的复杂查询。

• SIMPLE 简单的select查询，查询中不包含子查询或者UNION
• PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
• SUBQUERY 在SELECT或WHERE列表中包含了子查询
• DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
• UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
• UNION RESULT 从UNION表获取结果的SELECT

table输出结果集的表 显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称partitions匹配的分区type

查询的方式效率排名如下(一般来说，得保证查询至少达到range级别，最好能达到ref。):

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

1. system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
2. const 通过索引一次就找到了，const用于比较primary key 和 unique key，因为只匹配一行数据，所以很快。如果将主键置于where列表中，mysql就能将该查询转换为一个常量
3. eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键索引和唯一索引 区别于const eq_ref用于联表查询的情况
4. ref 非唯一索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
5. range 只检索给定范围的行，使用一个索引来选择行，一般是在where中出现between、、in等查询，范围扫描好于全表扫描，因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
6. index Full Index Scan，Index与All区别为index类型只遍历索引树。通常比All快，因为索引文件通常比数据文件小。也就是说，虽然all和index都是读全表，但是index是从索引中读取的，而all是从硬盘读取的
7. ALL Full Table Scan,将遍历全表以找到匹配的行

possible_keys表示查询时，可能使用的索引key表示实际使用的索引key_len索引字段的长度ref列与索引的比较rows扫描出的行数(估算的行数越少越好)filtered按表条件过滤的行百分比,体现条件的命中率cuiyaonan2000@163.comextra

最好显示NULL,如果显示如下的内容则需要操作一番.

• Using Index:表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
• Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
• Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
• Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
• Using join buffer：表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
• Impossible where：where子句的值总是false，不能用来获取任何元组
• Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
• No tables used：Query语句中使用from dual 或不含任何from子句