[大数据]mysql索引优化，explain输出字段介绍

查看mysql的连接数

有时候mysql数据库卡是因为连接数过多导致的。所以查看链接数然后关闭一些不必要的连接也是调优的一种手段。
查看连接数：

show processlist;

主动断开链接
id是查看连接数时的id（如下例子）

kill id

例子

索引

聚簇索引：就是主键索引
非聚簇索引：非主键的索引（普通索引、唯一索引）
索引都是B+树的结构。

聚簇索引和非聚簇索引（二级索引）的区别

聚簇索引包含了表中所用字段的信息。
非聚簇索引只是包含了索引字段和主键信息
所以用非聚簇索引的查询中包含了非索引字段信息时，就要用id去聚簇索引再查一次（回表操作）

普通索引和唯一索引的区别

唯一索引比普通索引多了唯一的约束。

创建索引语法

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],…) [ASC | DESC]
例子
给表【student】的字段【name】添加普通索引【index】索引的名称是【name_index】

ALTER TABLE student ADD index name_index(name) 

删除索引语法

ALTER TABLE table_name DROP INDEX index_name;
例子
删除表【student】名称为【name_index】的索引

ALTER TABLE student DROP INDEX name_index;

explain

我们建立索引后想查看mysql是否用上了？用上了哪个个索引？explain就可以清楚的展示出来。

explain语法

explain <sql语句>
例子

explain输出字段介绍

前言-表结构

s1表

CREATE TABLE `s1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int(11) DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`),
  KEY `idx_key2` (`key2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=110002 DEFAULT CHARSET=utf8;

s2表

CREATE TABLE `s2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int(11) DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB AUTO_INCREMENT=110002 DEFAULT CHARSET=utf8;

id

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

table

不论我们的查询语句有多复杂，里边儿 包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

select_type

SIMPLE
普通查询（包含连接查询）

explain select * from  s1 join s2 on s1.key1=s2.key1;

PRIMARY
主表（最外层的表）

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 where s2.key1='a') OR key3 = 'a';

图中可以看出最外层的表是【s1 】
SUBQUERY
非依赖子查询

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 where s2.key1='a') OR key3 = 'a';

DEPENDENT SUBQUERY
依赖子查询，子查询条件上依赖主表

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 where s2.key1=s1.key1) OR key3 = 'a';

MATERIALIZED
MATERIALIZED物化子查询结果集

 explain select * from s1 where key1 in (select key1 from s1 where key2=10003);

图中id为2的是MATERIALIZED，然后注意看table中有就是被物化出来的表，然后进行连接查询。

DERIVED
查询派生出来的表，如下sql

EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS
derived_s1 where c > 1;

type

type有如下值： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ，
index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。
system
在MyISAM中表字段只有一个的情况下查询会出现，速度最快。
const
根据表中主键去查询，或者根据唯一索引去查询唯一索引的字段（唯一覆盖索引）。
1主键查询例子：

explain  select * from s1 where id=100001;

2唯一覆盖索引
说明：key2 是唯一索引
注意：这里查询字段只能是key2，如果包含了唯一主键之外的字段要重新走聚簇索引进行回表查询

explain select key2 from s2 where key2=2710006;

eq_ref
用主键作为关联查询的条件

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

ref
查询条件用上了非聚簇索引

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

index_merge
两个索引合并使用

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

unique_subquery
unique _subquery 是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery

EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 where s1.key2 =s2.key2) OR key3 = 'a';

index_subquery
子查询用了普通索引

EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';

range
用上索引范围查找

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

index
在组合索引中有要查询的字段，但是where条件中没有出现组合索引的第一个索引字段。
例如：有组合索引：idx_key_part (key_part1,key_part2,key_part3)
查询了【key_part2】 但是where没有出现【key_part1】

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

ALL
全表扫描
小结：
结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL

possible_keys和key

possible_keys可能用上的索引
key是最后用上的索引

key_len

key_len是用上索引的大小，组合索引可以看这个字段判断出组合索引生效了多少个

EXPLAIN select * from s1 where key_part1 ='aa' and  key_part2='aa' and  key_part3 = 'aa';

key_len的计算方法：

varchar(10)变长字段且允许NULL = 10 * ( character set：
utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

例子中组合索引idx_key_part (key_part1,key_part2,key_part3)
三个字段都是 varchar(100)，一个key_len就是303，图上看到key_len是909所以是三个都用上了。

ref

是指查询的参数类型

rows

预估符合行数，就是经过条件过滤出来的数据。注意是预估值

filtered

索引的命中率

Extra

执行SQL的一些日志说明

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖