如何创建高性能的索引

如何创建高性能的索引

EXPLAIN 类型分析

explain 指令可以帮助我们查看查询优化器 处理 执行计划 的一些细节信息

语法: explain + 执行计划

假如我们有这样的两张表(分类表和商品表),我们将结合explain 字段进行说明:

#分类
CREATE TABLE category (
id int primary key auto_increment,
name varchar(50) not null,
c_desc varchar(200),
create_time datetime not null default now(),
key name_index(`name`)
)ENGINE = INNODB;

#商品
CREATE TABLE product (
p_id int PRIMARY KEY auto_increment,
category_id int not null,
name VARCHAR(50) not null
)ENGINE = INNODB;
复制代码

字段说明:

id:选择标识符

通过这个字段,我们可以知道sql语句执行的顺序.

当id相同时,从上到下执行,id不同时,越大的越先执行

select_type:表示查询的类型

SIMPLE : 简单查询

PRIMARY : 子查询时外面的语句会被标记为 PRIMARY,主表

UNION : 使用UNION 连接表查询时处于后面的查询

DEPENDENT UNION : UNION中的第二个或后面的select语句,取决于外面的查询

UNION RESULT : UNION的结果,union语句中第二个查询语句开始以及后面所有select的结果集

SUBQUERY : 子查询中的第一个SELECT , 结果不依赖于外部查询

DEPENDENT SUBQUERY : 子查询中的第一个SELECT , 结果依赖外部查询

DERIVED : 派生表的SELECT, FROM子句的子查询

UNCACHEABLE SUBQUERY : 一个子查询的结果不能被缓存，必须重新评估外链接的第一行

table:输出结果集的表

这一行分析对应的是哪一张数据库表,如果有别名则会显示别名

partitions:匹配的分区 (5.5及之前的版本是没有的,需要使用explain partitions select ……来显示带有partitions 的列)

表示使用的哪个分区,如果没有对表进行显式分区是看不到的

type:使用的索引星级

从优到劣依次为: system > const > eq_ref > ref > range > index > all

索引优化一般需要达到最少range级别

blog.csdn.net/weixin_4434…

possible_keys:表示查询时，可能使用的索引

表示此次查询可以用到的索引,如果没有相关的索引,此列是NULL

key:表示实际使用的索引

如果此次查询没有用到索引,此列是NULL

key_len:索引字段的长度

索引使用到的字节数,该列显示的为索引字段的最大可能长度,并非世纪使用的长度,在定义索引是计算出来的,并非实时计算出来.

长度计算公式:

varchr(10)变长字段且允许NULL = 10 * ( character set：uff8mb4=4, utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchr(10)变长字段且不允许NULL = 10 *( character set： uff8mb4=4,utf8=3,gbk=2,latin1=1)+2(变长字段)

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

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

ref: 引用到上一个表的列

rows:扫描出的行数(估算的行数)

filtered:按表条件过滤的行百分比

Extra:额外的信息说明

这里挑比较常见的几个做下说明:

Using index: 使用到了覆盖索引

Using where:

1.查询的列未被索引覆盖,where子句筛选条件不满足前缀索引(索引失效)

2.查询的列未被索引覆盖,where子句筛选条件非索引列

Using index condition: 范围过滤索引

Using filesort: 使用到了磁盘排序,这是应该避免的

Using temporary : 使用到了临时表,这是应该避免的

索引是什么?

索引是帮助查询快速找到记录的一种高效的查询数据结构.

索引是针对查询优化最有效的手段.索引能够将查询性能提高好几个数量级.

索引是如何工作的?

在Mysql中,首先存储引擎会根据匹配到的索引,在索引上找到对应的值,然后根据匹配上的索引记录找到对应的数据行.

这个过程就像,我们尝试在一本书上找到指定知识点的内容,会先翻到书的“目录”,然后根据指定“目录”找到对应的页码.

CREATE TABLE hero (
id int primary key auto_increment,
name varchar(50) not null,
hero_desc varchar(200),
key name_index(`name`)
)ENGINE = INNODB;

INSERT INTO hero VALUES(1,'张三','法外狂徒...');
INSERT INTO hero VALUES(2,'李四','...');
INSERT INTO hero VALUES(3,'王五','...');
INSERT INTO hero VALUES(4,'赵六','...');
INSERT INTO hero VALUES(5,'冯七','...');
INSERT INTO hero VALUES(6,'莫八','...');
INSERT INTO hero VALUES(7,'莫九','...');
复制代码

假如我们要查找“name”为“张三”的数据.会先根据name_index 索引 查找到name='张三' 的数据行的主键id,然后通过 id = 1查询主键索引 找到指定的数据行返回

select * from hero where name = '张三';
复制代码

索引的类型

索引的类型有很多,我们可以根据不同的场景选择不同的索引.mysql的索引是在存储引擎层使用的.不同的存储引擎实现索引的方式也不相同.每个存储引擎支持的索引也不相同,也不是所有的存储引擎都支持所有的索引类型.

我们常用的存储引擎一般是INNODB , 如果没有特殊说明,使用的索引默认为B-Tree 索引.

B-Tree 索引

B-Tree 使用 B+ 树数据结构来存储数据.大多数的mysql 存储引擎都支持这种索引.存储引擎以不同的方式使用B-Tree索引,性能也各有不同.例如MyISAM 引擎使用前缀压缩技术来使索引更小,而InnoDB 则按照原始数据格式进行存储.MyISAM索引通过数据存储的物理位置来引用索引列,而InnoDB则 根据主键来引用索引列.

B-Tree 通常意味着所有的值都是按顺序存储的,并且每一个叶子页到跟的距离相同.