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

mysql性能优化系列7-索引与执行计划

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

(1)主键索引

基于主键创建索引,不允许重复,不允许空值

(2)唯一索引

索引列值必须唯一,允许空值

(3)综合指数

用普通列构建的索引,不受限制

(4)全文索引

使用大型文本对象的列构建的索引

(5)综合索引

合并多个列以建立索引,并且每个列均不允许具有空值。组合索引遵循最左边前缀的原则

MySQL数据库支持多种索引类型。

(1)哈希索引

仅内存存储引擎支持哈希索引。从索引列的值计算hashCode,然后将值所在的行数据的物理位置存储在hashCode对应的位置。因为使用了哈希算法,所以速度很快,但是它不支持范围搜索和排序。

(2)全文索引

FULLTEXT(全文本)索引只能用于MyISAM和InnoDB,数据量太大而无法生成全文本索引,这非常耗时且占空间。对于文本大对象或大CHAR类型数据,如果使用公共索引来匹配文本(例如LIKE%word%),则响应时间将非常长。此时,您可以使用全文本索引。

(3)BTree索引

对于BTree和B + Tree,本文http://blog.jobbole.com/24006/很好,以下内容引自本文。

为了描述B树,首先将数据记录定义为二进制[key,data],key是记录的键值,对于不同的数据记录,键互不相同; data是除key之外的数据记录的数据。那么B-Tree是一个满足以下条件的数据结构:

 

查看d = 2的B树的示意图。

在此处插入图片描述

由于B树的特性,通过B树中的键检索数据的算法非常直观:首先从根节点执行二进制搜索,如果找到,则返回对应节点的数据,否则递归搜索由相应间隔的指针指向的节点,直到找到该节点或找到空指针为止,前一个搜索成功,而后一个搜索失败。

(4)B +树

B树有许多变体,其中最常见的是B +树。例如,MySQL通常使用B + Tree来实现其索引结构。与B树相比,B树具有以下差异:

 

在此处插入图片描述

B +具有顺序访问指针的树:

在此处插入图片描述

我们通常使用磁盘I/O时间来评估索引结构的优缺点。通过对B树的分析,根据B树的定义,可以知道一次搜索最多需要访问h个节点。数据库系统的设计者巧妙地使用了磁盘预读的原理来将一个节点的大小设置为等于一页,以便每个节点仅可以满载一个I/O。为了实现这一目标,在B树的实际实施中还需要以下技能:

每次创建一个新节点时,都直接申请一个页面空间,以确保一个节点也物理存储在页面中,再加上计算机存储分配是页面对齐的,每个节点仅实现一个I/O。在B树中进行一次搜索最多需要h-1 I/O(根节点驻留内存),并且累进复杂度为O(h)= O(logdN)。在一般实际应用中,输出度d非常大,通常超过100,因此h非常小(通常不超过3)。综上所述,使用B树作为索引结构的效率非常高。

MyISAM索引实施:

MyISAM引擎使用B +树作为索引结构,叶节点的数据字段存储数据记录的地址。

在MyISAM中,主索引和辅助索引(辅助密钥)之间在结构上没有区别,只是主索引要求密钥是唯一的,并且辅助索引的密钥可以重复。

MyISAM中的索引检索算法是首先根据B + Tree搜索算法来搜索索引。如果存在指定的键,则取出数据字段的值,然后以数据字段的值作为地址读取相应的数据记录。 MyISAM的索引方法也称为”非群集”,因为索引文件和数据文件不在同一文件中。

InnoDB索引实现:

第一个主要区别是InnoDB的数据文件本身就是索引文件。由于InnoDB的数据文件本身必须通过主键聚合,因此InnoDB要求表必须具有主键(MyISAM可能没有主键)。如果未明确指定,则MySQL系统将自动选择一列,该列可以唯一地将数据记录标识为主键(如果不存在)。对于此类列,MySQL会自动为InnoDB表生成一个隐式字段,作为主键。首要的关键。此字段的长度为6个字节,并具有长整数类型。与MyISAM索引的第二个区别是InnoDB的辅助索引数据字段存储了相应记录主键的值,而不是地址。换句话说,InnoDB的所有辅助索引都将主键称为数据字段。 br>

聚集索引的实现使按主键进行搜索非常高效,但是辅助索引搜索需要两次检索索引:首先检索辅助索引以获取主键,然后使用主键来检索主记录中的记录。主索引。

(1)查看索引

 

(2)创建索引

 

(3)删除索引

 

SQL语句加EXPLAIN关键字可以查询执行计划。实施计划的结果如下:

在此处插入图片描述

ID列:描述选择查询的序列号,其中包含一组数字,指示在查询中执行选择子句或操作表的顺序。

2.1.1 id相同

执行顺序是从上到下。

(1)执行声明

 

(2)执行结果

在此处插入图片描述

ID列具有相同的值,首先查询t1,然后查询t2,最后查询t3。

2.1.2 id完全不同

如果它是子查询,则id的序列号将增加,id值越大,优先级越高,并且将首先执行的次数越多。

(1)执行声明

 

(2)执行结果

在此处插入图片描述

2.1.3 id不完全相同

id值越高,该值越高。

(1)执行声明

 

(2)执行结果

在此处插入图片描述

Select_type指示查询的类型。类型如下:

  • 简单:简单查询,不包括子查询和联合查询
  • primary:查询包含复杂的子查询,而最外面的查询是primary
  • union:如果第二个SELECT出现在UNION之后,它将被标记为UNION。如果UNION包含在FROM子句的子查询中,则外部SELECT将标记为:DERIVED
  • 子查询:子查询包含在SELECT或WHERE列表中
  • 派生的:FROM列表中包含的子查询的标记
  • UNION RESULT:选择以从UNION表中获取结果

2.2.1简单

(1)执行语句

 

(2)执行结果

在此处插入图片描述

该查询不包含子查询或UNION

2.2.2主要和子查询

主要:如果查询包含任何复杂的子部分,则最外面的查询将标记为

子查询:子查询包含在SELECT或WHERE列表中

(1)执行声明

 

(2)执行结果

在此处插入图片描述

2.2.3派生

FROM列表中包含的子查询被标记为DERIVED(派生),MySQL将递归执行这些子查询,并将结果放入临时表中。

(1)执行语句

 

(2)执行结果

在此处插入图片描述

2.2.4联合结果和联合

UNION:如果第二个SELECT出现在UNION之后,它将被标记为UNION。

UNION RESULT:SELECT
从UNION表中获取结果

(1)执行声明

 

(2)执行结果

在此处插入图片描述

显示此数据行与哪个表有关

type显示访问类型,这是一个更重要的指标。结果值从最佳到最差是:

系统 const \\ u> eq_ref ref 全文 ref_or_null index_merge \ gt;唯一子查询\ gt; index_subquery \\ ugt26范围索引所有。

<最常见的是系统> const \\ u> eq_ref ref 范围索引所有。一般而言,有必要确保查询至少达到范围级别,最好是ref。

2.4.1系统和const

系统:该表只有一行记录(等于系统表),这是const类型的特殊列。

常量:表示通过索引一次找到它

(1)执行声明

 

(2)执行结果

在此处插入图片描述

2.4.2 eq_ref

唯一索引扫描。对于每个索引键,表中只有一个记录与之匹配。常见于主键或唯一索引扫描

(1)执行语句

 

(2)执行结果

在此处插入图片描述

注意表的执行顺序。

2.4.3参考

非唯一索引扫描,返回与单个值匹配的所有行。

(1)执行声明

 

(2)执行结果

在此处插入图片描述

2.4.4范围

仅检索给定范围内的行,使用索引选择行。关键列显示使用哪个索引。通常,where语句
中的<,>,in等之间存在查询。

此范围扫描索引扫描比全表扫描要好,因为它只需要从索引的某个点开始并在另一点结束,而无需扫描整个索引。

(1)执行声明

 

(2)执行结果

在此处插入图片描述

2.4.5索引

当查询的结果均为所有索引列时,尽管全部被扫描,但仅查询索引库,而不会查询其他数据。

(1)执行语句

 

(2)执行结果

在此处插入图片描述

2.4.6全部

遍历整个表以找到匹配的行

(1)执行声明

 

(2)执行结果

在此处插入图片描述

Possible_Keys是可能的键,Key是使用的实际索引。如果为NULL,则不使用索引。如果在查询中使用覆盖索引,则索引和查询的选择字段重叠。

(1)执行声明

 

(2)执行结果

在此处插入图片描述

key_len表示索引使用的字节数。基于此值,可以确定索引使用情况,尤其是在组合索引时,以确定查询是否使用了所有索引字段。 Char和varchar也与字符编码密切相关,latin1占1个字节,gbk占2个字节,utf8占3个字节。

(1)索引字段是char类型,并且字段定义不是Null

 

在此处插入图片描述

名称列为char(10),字符集为utf-8,它占用3个字节,Keylen = 10 *3。

(2)索引字段的类型为char并且字段定义可以为Null

 

在此处插入图片描述

名称列为char(10),字符集为utf-8,占用3个字节,此外还需要存储一个空值,Keylen = 10 * 3 +1(空),结果为31。

(3)索引字段是varchar类型,并且字段定义不是Null

 

在此处插入图片描述

Keylen = varchar(n)可变长度字段+ Null = n *不允许(utf8 = 3,gbk = 2,latin1 = 1)+2。 varchar可变长度字段需要额外的2个字节。

(4)索引字段是varchar类型,并且字段定义可以为Null

 

在此处插入图片描述

Keylen = varchar(n)可变长度字段+ Allow Null = n *(utf8 = 3,gbk = 2,latin1 = 1)+1(NULL)+2。

摘要:

可变长度字段需要额外的2个字节(保存VARCHAR值时仅保存所需的字符数,再加上一个额外的字节来记录长度(如果声明的列长度超过255,则使用两个字节),因此VARCAHR index在长度计算中加2.固定长度字段不需要额外的字节。NULL需要1个字节的额外空间,因此最好不要对索引字段使用NULL,因为NULL使统计信息更加复杂并且需要额外的存储空间。复合索引具有最左边的前缀的特征。如果可以全部使用复合索引,则它是复合索引字段的索引长度的总和。也可以用来确定复合索引是部分使用还是使用完全

(5)整数/浮点数/时间类型

同样,NOT NULL =字段本身的字段长度。 NULL =字段本身的字段长度+1(因为有一个标记为空,所以该标记需要占用1个字节)。 datetime类型在5.6中的字段长度为5个字节,而datetime类型在5.5中的字段长度为8个字节。

显示使用索引的哪一列(如果可能的话)。哪些列或常量用于在索引列上查找值。

(1)执行声明

 

(2)执行结果

在此处插入图片描述

第一项表示使用s1表的name属性的常量。第二个条目指示已使用测试库的s1表的id列。

根据表统计信息和索引选择,粗略估计查找所需记录所需的行数。

其他信息,包括不适合在其他列中显示但非常重要的信息。

  • 与众不同:select部分中使用了distinct关键字
  • 使用文件排序:mysql使用外部索引对数据进行排序,而不是根据表中的索引进行排序和读取。也就是说,mysql无法完成的索引排序操作变为”文件排序”。常见于按和按语句分组。
  • 使用索引:查询时不需要返回表进行查询,可以直接通过索引获取查询数据。即覆盖索引
  • 使用连接缓冲区:使用连接缓冲区
  • 使用相交:指示使用和的每个索引的条件,该信息指示从处理结果获得相交
  • 使用并集:使用或连接使用每个索引的条件时,此信息意味着从处理结果中获取并集
  • 使用临时表:指示使用临时表存储中间结果。临时表可以是内存临时表和磁盘临时表,它们在执行计划中不可见。 mysql对查询结果进行排序时使用临时表
  • 使用where:表示并非存储引擎返回的所有记录都满足查询条件,因此需要根据where进行过滤
  • 不可能where:where子句始终为

以下是一些重要说明。

2.9.1使用文件排序

mysql将使用外部索引对数据进行排序,而不是根据表中的索引顺序进行读取。 MySQL中无法用索引完成的排序操作称为”文件排序”。如果存在”使用文件排序”,则可以在此处对其进行优化。

在这里,为列col1,col2,col3建立了联合索引。

(1)执行声明

 

(2)执行结果

第一条语句的结果:

在此处插入图片描述

第二条语句的结果:

插入p图像描述在这里

2.9.2使用临时

临时表用于保存中间结果,而MySQL在对查询结果进行排序时使用临时表。常见的是按排序顺序和按查询分组。

(1)执行声明

 

(2)执行结果

第一条语句的结果:

在此处插入图片描述

第二条语句的结果:

在此处插入图片描述

2.9.3使用索引

表示在相应的选择操作中使用了覆盖索引,以避免访问表的数据行,并且效率良好。如果同时使用where,则表示该索引用于执行索引键值搜索;。如果”使用where”没有同时出现,则表明该索引用于读取数据而不是执行搜索操作。

注意:

如果要使用覆盖索引,则必须注意仅在选择列表中选择所需的列,而不要选择*,因为如果同时对所有字段建立索引,索引文件将太大,查询性能将降低,并且修改和维护的性能将受到严重影响。

(1)执行语句

 

(2)执行结果

第一条语句的结果:

在此处插入图片描述

第二条语句的结果:

在此处插入图片描述

2.9.4在何处使用联接缓冲区

使用where表示在哪里使用过滤。使用连接缓冲区使用连接缓存。

(1)执行声明

 

(2)执行结果

在此处插入图片描述

(1)尝试匹配所有值

尝试使用完全匹配的条件来查询数据

(2)最佳左前缀规则

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

(3)不要在索引列上做任何事情

不要对索引列执行任何操作(计算,函数,(自动或手动)类型转换),这将导致索引失败并转向全表扫描

(4)将范围条件放在最后

存储引擎无法使用索引
中范围条件右侧的列

(5)尝试使用覆盖指数

(6)这并不意味着它很重要。

当索引不等于(!=或\ lt)时,mysql无法使用索引将导致全表扫描

(7)注意”喜欢”查询

例如以通配符(’%abc…’)开头MySQL索引失败将成为全表扫描操作

(8)字符类型加引号

没有单引号的字符串的索引无效

(9)OR TO UNION效率高

(10)批量插入

提交前关闭自动提交,尝试使用批处理插入语句,可以使用MyISAM存储引擎。如果需要大量导入数据,则可以使用LOAD DATA INFLIE,它比常规insert语句快20倍。

相关推荐

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