快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

mysql的优化前提-explain详解与索引实践

请联系QQ:1793040 索取软件

考虑饮用水: https://www.cnblogs.com/shujiying/p/12546747.html

使用EXPLAIN关键字模拟优化程序执行的SQL语句,并分析查询或结构的性能瓶颈;

在select语句之前添加说明关键字,MySQL将在查询上设置一个标记,执行查询将返回执行计划的信息,而不是执行此SQL;

注意:如果from中包含子查询,则该子查询仍将执行,并且结果将放置在临时表中;

因为我们想学习解释,所以我们必须创建一些表;

复制代码

复制代码

#演员表

如果存在actor,则删除表;

创建表actor(

`id` int(11)NOT NULL,

`name` varchar(45)默认为空,

`update_time` datetime DEFAULT NULL,

主键(`id`)

)引擎= InnoDB默认字符集= utf8;

插入`actor`(`id`,`name`,`update_time`)值(1," a"," 2017-12-2 15:27:18"),(2," b"," 2017-12-22 15:27:18"),(3," c"," 2017-12-22 15:27:18");

#电影表

如果存在"电影",则删除表;

创建表`film`(

`id` int(11)非空AUTO_INCREMENT,

`name` varchar(10)默认为NULL,

主键(`id`),

KEY`idx_name`(`name`)

)引擎= InnoDB默认字符集= utf8;

插入" film"(" id"," name")值(3," film0"),(1," film1"),(2," film2");

#演员与电影的关联表

如果存在film_actor,则删除表;

创建表`film_actor`(

`id` int(11)NOT NULL,

film_id int(11)非空,

`actor_id` int(11)非空,

`remark` varchar(255)默认为NULL,

主键(`id`),

KEY`idx_film_actor_id`(`film_id`,`actor_id`)

)引擎= InnoDB默认字符集= utf8;

插入(film,actor(id),film_id,actor_id)值(1,1,1),(2,1,2),(3,2,1);

复制代码

复制代码

\ (1) 扩展说明:基于explain,将提供一些其他查询优化信息。后跟。显示警告 命令,您可以获得优化的查询语句,以查看优化程序进行了优化。还有一个过滤的列,它是半点值。行*过滤后的行数/100可以估计将在说明中连接到上一个表的行数(该表引用的说明中的ID值小于当前表的ID值)表。

 mysql \>说明id = 1的电影的扩展选择*;

mysql \ gt;显示警告;

\ (2)解释分区:与解释相比,还有一个额外的partitions字段。如果查询基于分区表,则将显示查询将访问的分区。

<表格边框=" 0" cellspacing =" 0" cellpadding =" 0">

<身体>

1

\

上述执行计划的结果:

id列的编号是所选内容的序列号。如果有多个选择,则有多个ID,并且ID的出现顺序按照选择出现的顺序增加。

id列越大,执行优先级越高。如果id相同,则它将自上而下执行。如果id为NULL,它将最后执行。

select_type指示相应的行是简单查询还是复杂查询。简单查询只是简单而复杂的查询,它们是:主查询,子查询,派生查询,联合查询;

(1)简单:简单查询。该查询不包括子查询和联合;

mysql \>解释从id = 2的电影中选择*;

(2) 主要:最外面的选择;—-复杂查询
(3) 子查询:子查询包含在select(不在from子句中)-复杂查询中
(4) 派生的:from子句中包含的子查询。 MySQL将结果存储在一个临时表中,该表也称为派生表(派生表,英语中的派生含义);-复杂查询
使用下面的示例来理解\ 主要,子查询和派生类型:\

 mysql \>设置会话optimizer_switch =" derived_merge =关闭"; #关闭mysql5.7新功能,以优化派生表的合并

mysql \ gt; 解释der(从ID = 1的演员中选择1)从ID = 1的电影中选择*

mysql \ gt;设置会话optimizer_switch =" derived_merge = on"; #恢复默认配置

分析SQL的执行顺序:首先执行电影表的查询->然后执行演员表的查询->最后执行所生成的临时表的查询;

第1行的select_type(id为1)是主要的,指示最外面的查询; id为1,这是最小的,最后执行;

派生第2行的select_type(id为3),这意味着from后面的子查询也称为派生表,并且派生表的别名为der; id为3,这是最大的ID,并首先执行。的;

第3行(id为2)的select_type是子查询,这意味着from之前的子查询; id为2,位于中间,在中间执行;

(5) 联合:联合中的第二个和后续选择均为联合;

 mysql \>说明选择1并全部选择1; 

\此列指示说明行正在访问哪个表。

当from子句中有子查询时,表列位于\ lt中;派生N \\ u>格式,这意味着当前查询取决于id = N查询,因此首先执行id = N查询。

存在联合时,UNION RESULT的表列的值为\ lt; union1,2和1和2代表参与联合的选择行ID。

NULL:MySQL可以在优化阶段分解查询语句,并且在执行阶段不再需要访问表或索引。例如:在索引列中选择最小值,即可找到要完成的索引,执行期间无需访问表

 mysql \>解释影片中的选择min(id); 

此列指示关联类型或访问类型,即MySQL决定如何查找表中的行以及数据行记录的大概范围。

从最佳到最坏的顺序是:系统 \ gt; const \ \ gt; eq_ref \ gt; 参考 \ \ gt; 范围 \ \ gt; 索引 \ \ gt; 全部

一般来说,确保查询达到范围级别,最好是ref

\ (1)const ,\\ ubsp; 系统:mysql可以优化查询的一部分并将其转换为 const >(您可以看到显示警告的结果)。系统=1。

将主键或唯一键的所有列与常量进行比较时,该表最多具有一个匹配行,只需读取一次,速度就相对较快。系统是const的特例。当表中只有一个数据时,它是系统; (使用主键或唯一索引时出现)

 mysql \>设置会话optimizer_switch =" derived_merge =关闭"; #关闭mysql5.7新功能,以优化派生表的合并

mysql \ gt; 解释扩展选择* from(从ID = 1 的电影中选择*)tmp;

mysql \ gt;设置会话optimizer_switch =" derived_merge = on"; #恢复默认配置

(2)eq_ref :主键或唯一键索引的所有部分均已连接并使用,并且最多仅返回一个符合条件的记录。

这可能是const之外最好的联接类型。简单选择查询将不会显示此类型。

(连接查询时,将使用主键或唯一索引的所有字段)

 mysql \>解释select * from film_actor在film_actor上左接合电影。 

\说明: film_id:表的联合索引中的一个字段,但类型为All;

\因为*查询是指查询所有字段,但film_actor表的备注字段未编制索引,因此需要全表扫描;

\ (3)ref :与eq_ref相比,不使用唯一索引,而是使用普通索引或唯一索引的部分前缀。该索引应与某个值进行比较。找到多个匹配的行;

(使用的公共索引的部分前缀或联合唯一索引)

\ \ lt; 1 \\> 简单选择查询,名称是普通索引(非唯一索引)

 mysql \>从名称=" film1"的电影中解释选择*; 

\

\ \ lt; 2 \\> 关联表查询,idx_film_actor_id是film_id和actor_id的联合索引,此处使用film_actor的film_id左前缀。

 mysql \>解释从左上角的电影中选择film_id加入film.id = film_actor.film_id上的film_actor; 

\ (4)范围:范围扫描通常出现在\\ n在()中,之间, \ gt ;, \ ltb, \ gt; =等。正在运行。使用索引来检索给定范围的行。

 mysql \>解释从演员那里选择*,其中id> 1; 

\ (5)索引:扫描全表索引,通常比All更快;

<表格边框=" 0" cellspacing =" 0" cellpadding =" 0">

<身体>

1

\

电影表的所有字段均已索引,使用*进行查询,类型为索引;

(6)ALL :即全表扫描,这意味着mysql需要从头到尾查找所需的行。通常情况下,这需要增加索引来进行优化。

 mysql \>解释演员的选择*; 

此列显示查询。 可能其中索引用于查找。
在进行解释时,可能会出现possible_keys有一个值,并且该键显示为NULL。这是因为表中没有太多数据。 MySQL认为索引对该查询不是很有帮助,因此选择了全表查询。
如果该列为NULL,则没有相关的索引。在这种情况下,您可以检查where子句以查看是否可以创建适当的索引来提高查询性能,然后使用explain来查看效果。

此列显示mysql。 实际\ 其中索引用于优化对表的访问。
如果不使用索引,则该列为NULL。如果要强制mysql使用或忽略possible_keys列中的索引,请使用force index并忽略查询中的索引。

<表格边框=" 0" cellspacing =" 0" cellpadding =" 0">

<身体>

1

\

此列显示索引中mysql使用的字节数,索引中的哪些特定列可以通过此值计算。
例如,film_actor idx_film_actor_id的联合索引由两个int列film_id和actor_id组成,每个int为4个字节。从结果可以推断出查询中的key_len = 4使用第一列:film_id列执行索引查找。

 mysql \>解释select * from film_actor,其中film_id = 2; 

key_len的计算规则如下:

复制代码

复制代码

 字符串

char(n):n个字节的长度

varchar(n):2个字节的存储字符串长度,如果为utf-8,则长度为3n + 2

数值类型

tinyint:1个字节

smallint:2个字节

int:4个字节

bigint:8个字节

时间类型

日期:3个字节

时间戳:4个字节

datetime:8个字节

复制代码

复制代码

注意:如果该字段允许为NULL,则1字节记录是否需要为NULL

索引的最大长度为768个字节。当字符串太长时,mysql将执行类似于左前缀索引的过程,提取字符的前半部分以进行索引。

此列显示表用来在键列记录的索引中查找值的列或常量。常见的是:const(常数),字段名(例如:film.id)。

此列是mysql估计要读取和检查的行数。请注意,这不是结果集中的行数。扫描索引的可能行数

此列显示其他信息。共同的重要价值如下:

使用索引\ 使用索引条件在哪里使用

(1): 使用索引:使用覆盖索引;覆盖索引是只能从索引获得的选定数据列,无需读取数据行,换句话说,查询列应由内置索引覆盖。也就是说,查询结果集中的所有字段都在索引中;

 mysql \>解释从film_actor中选择film_id,其中film_id = 1; 

(2)使用索引条件:索引未完全覆盖查询列,条件是联合索引包围的前导列的范围;

 mysql \>解释select * from film_actor where film_id \\ u> 1; 

(3)使用where :使用where语句处理结果,索引未覆盖查询列;在搜索和使用索引的情况下,您需要返回到表以查询所需的数据

 mysql \>解释select * from actor name =" a"; 

(4)使用临时:mysql需要创建一个临时表来处理查询。在这种情况下,通常有必要优化,首先,我想使用索引进行优化。

\ \ lt; 1 \\> actor.name没有索引,这时将创建一个临时表来区分; (不同的查询可能使用临时表)

 mysql \>解释选择与演员不同的名字; 

\ \ lt; 2 \\> film.name建立idx_name索引。此时,查询是多余的使用索引,没有使用临时表;将索引树加载到内存中,然后返回;

 mysql \>解释选择与电影不同的名字; 

\ (5)使用文件排序:将使用外部排序代替索引排序。当数据较小时,它将从内存中进行排序,否则排序需要在磁盘上进行。在这种情况下,通常有必要考虑使用索引进行优化。

\ \ lt; 1 \\> actor.name未建立索引,它将浏览actor的整个表,保存排序键名称和对应的id,然后排序名称并检索行记录

 mysql \>从演员 名称中选择select *; 

\ \ lt; 2 \\> film.name建立了idx_name索引,这时查询时多余的是使用索引

 mysql \>解释从电影 名称中选择*。 

\ (6)选择经过优化的表:使用一些聚合函数(例如max,min)访问具有索引的字段;它已被MySQL Optimized使用;

 mysql \>解释影片中的选择min(id); 

\创建样本表

复制代码

复制代码

创建表`employees`(

`id` int(11)非空AUTO_INCREMENT,

`name` varchar(24)NOT NULL DEFAULT"" COMMENT" NAME",

`age` int(11)NOT NULL DEFAULT" 0" COMMENT" Age",

`position` varchar(20)NOT NULL DEFAULT"" COMMENT" Job",

`hire_time`时间戳记NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT"加入时间",

主键(`id`),

使用BTREE键`idx_name_age_position`(`name`,`age`,`position`)

)ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8 COMMENT =" Employee Record Form";

插入员工(姓名,年龄,职位,雇用时间)VALUES(" LiLei",22," manager",NOW());

插入员工(姓名,年龄,职位,雇用时间)VALUES(" HanMeimei",23," dev",NOW());

插入员工(姓名,年龄,职位,雇用时间)值("露西",23," dev",NOW());

复制代码

复制代码

解释选择*来自员工,名字=" LiLei"; 

name是联合索引idx_name_age_position的开头字段;

\ key_len为74,名称为varchar(24),则3 * 24 + 2 = 74,因此请使用联合索引中name字段的索引;

解释选择*来自雇员的姓名=" LiLei"且年龄= 22; 

\名称,年龄是联合索引idx_name_age_position的字段;

\ key_len为78,名称为varchar(24),则3 * 24 + 2 = 74; age是int,所以值是4; 74 + 4 = 78,因此在联合索引”步行索引”中使用名称和年龄字段;

解释选择*从员工那里,姓名="李磊" AND年龄= 22 AND职位="经理"; 

\ 名称,年龄,位置是联合索引idx_name_age_position的字段;

\ key_len为140,名称为varchar(24),则3 * 24 + 2 = 74; age是int,所以值是4;位置是varchaer(20),因此值是3 * 20 + 2 = 62; 74 + 4 + 62 = 140,因此在联合索引中使用名称,年龄和位置字段的索引;

\如果建立了联合索引,则必须遵循最左边的前缀规则。这意味着查询从联合索引的最左前列开始,并且不会跳过索引中的列。

(1)使用联合索引的前两个字段进行查询;

解释选择*来自员工,姓名="李磊",年龄= 22;  #name,年龄消失索引 

\ key_len是78,(3 * 24 + 2)+ 4 = 78;姓名和年龄指数消失了;

\ (2)使用联合索引的第一和第三字段来查询

解释选择*从雇员那里,名称=" LiLei",位置=" manager"; #仅名称消失索引 

\ key_len为74,名称的长度仅为74,因此仅对名称进行索引;

分析:联合索引的基础存储是首先比较第一个字段,第一个字段与第二个字段相同,第二个字段与第三个字段相同;比较第一个字段名称,您可以搜索零件,位置是关节索引的第三个字段,但是在存储和搜索时索引不能跳过第二个字段并直接比较第三个字段。 position字段仍列出大量数据进行查询,因此名称已建立索引,位置未建立索引。

(1)不要在索引列上执行函数操作

解释选择*从雇员那里(名称,3)离开=" LiLei"; 

通常,只要您向索引列添加函数操作,底层的MySQL就不会直接使用索引来处理它。

(2)向\中添加一个常规索引。 hire_time :

 ALTER TABLE`employees`

使用BTREE添加索引`idx_hire_time`(`hire_time`);

 EXPLAIN从员工那里选择*,其中date(hire_time)=" 2018-09-30"; #不会进入索引 

\ 对于上述SQL, 转换为日期范围查询,将转到索引:

 EXPLAIN从employ_time \的员工中选择*; =" 2018-09-30  00:00 "和hire_time \ lt; =" 2018-09-30  23:59:59 "; 

\ 恢复原始索引状态

 ALTER TABLE`employees`

DROP INDEX`idx_hire_time`;

联合索引的字段顺序,范围搜索后的列将不转到索引;

解释选择*从员工那里,姓名="李磊" AND年龄= 22 AND职位="经理"; #3个字段索引将

解释选择*来自员工的姓名="李磊"且年龄\\ u> 22 AND position =" manager";

\只取前两个字段的索引。第一个字段名称相等,因此您可以找到特定的数据。第二个字段将缩小到一个范围。第三个字段将用于此范围内的相等查询,还是您必须再次遍历该范围? ,所以只剩下名字和年龄。

从雇员那里解释选择姓名,年龄,姓名=" LiLei" AND年龄= 23 AND职位="经理"; 

从员工那里选择 * 姓名=" LiLei" AND年龄= 23 AND职位="经理"; 

解释选择*来自员工的名字! ="李磊"; 

建议在创建字段时将其设置为非null并设置默认值;

解释选择*来自名称为null的员工

解释选择*来自名称如"%Lei"的员工

解释选择*来自名称如" Lei%"的员工

当进行模糊搜索时,索引将不会在模糊之前被索引,而索引将在模糊之后被索引;

因为在后歧义时期,我们知道此字段前面有几个字符,所以我们只比较索引比较中的前几个字符;

问题:如何解决未使用”%string%”之类的索引的问题?

(a)使用覆盖索引,查询字段必须是覆盖索引字段。

解释选择名称,年龄,职位来自员工的姓名,例如"%Lei%"; 

\ (b)如果您不能使用覆盖索引,则可能需要使用搜索引擎

解释选择*来自名称=" 1000"的员工; #会走索引

解释选择*来自员工的姓名= 1000; #将不会转到索引

\因为name是字符串类型,所以MySql会进行隐式类型转换和类型转换,因此它不会转到索引;

解释选择*从雇员那里,名称=" LiLei"或名称=" HanMeimei"; 

为年龄添加唯一索引:\ ALTER TABLE`employees` add INDEX` idx_age`(`age`)使用BTREE;

说明从年龄*的员工中选择*。 = 1,年龄为\ lt; = 2000; 

从执行计划的结果可以看出,上述范围搜索将不获取索引;

不采用索引的原因:mysql内部优化器将基于多个因素(例如检索比率和表大小)来评估是否整体使用索引。例如,在此示例中,可能是因为单个数据查询太大,并且优化器最终选择不采用索引

优化方法:您可以将大范围分成多个小范围

说明从年龄*的员工中选择*。 = 1,年龄为\ lt; = 1000;

解释选择*从年龄所在的员工那里= 1001,年龄为 = 2000;

\ 恢复原始索引状态:ALTER TABLE`employees` DROP INDEX` idx_age`;

创建联合索引:(a,b,c)

\ 像KK%等同于一个常数,因此取索引;%KK和%KK%等同于范围,因此不取索引;

不采用索引的原因:mysql内部优化器将基于多个因素(例如检索比率和表大小)来评估是否整体使用索引。例如,在此示例中,可能是单个数据查询太大,并且优化器最终选择不采用索引优化方法:可以说将大范围划分为多个小范围。

使用覆盖索引时出现

相关推荐

 
QQ在线咨询
售前咨询热线
QQ1793040