mysql索引与执行计划

zishuo
208 阅读14分钟

慢查询

慢查询配置

  • show_query_log 启动停止慢查询日志

  • show_query_log_file 指定慢查询日志文件及路径（默认和数据文件放一起）

  • long_query_time 记录慢查询查询记录sql的执行时间阈值

  • log_queries_not_useing_indexes 是否记录为使用索引的sql

  • log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

配置了慢查询之后会记录相应的sql，其中包括

  • 查询语句
  • 数据修改语句
  • 已经回滚的sql

慢查询分析

一条慢查询的日志如下：

image-20200624113610768

解析之后如下

第一行：用户名 、用户的IP信息、线程ID号

第二行：执行花费的时间【单位：毫秒】

第三行：执行获得锁的时间

第四行：获得的结果行数

第五行：扫描的数据行数

第六行：这SQL执行的具体时间

第七行：具体的SQL语句

慢查询分析

1.1.1. Mysqldumpslow

常用的慢查询日志分析工具，汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出。

语法：

mysqldumpslow -s r -t 10 slow-mysql.log

-s order (c,t,l,r,at,al,ar)

​ c:总次数

​ t:总时间

​ l:锁的时间

​ r:总数据行

​ at,al,ar :t,l,r平均数 【例如：at = 总时间/总次数】

-t top 指定取前面几天作为结果输出

mysqldumpslow.pl -s t -t 10 D:\DESKTOP-2EKGEE5-slow.log

索引

索引的原理

mysql的索引是由存储引擎来实现的，由于存在不同的存储引擎，所有存在不同的索引，比如hash索引，B Tree索引，B+ Tree索引，全文索引等等。innodb使用的就是B+索引

由于mysql5.7开始默认支持全文索引，，从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。

BTree

BTree是一种多路查找平衡树，结构如下

截屏2020-06-28 上午9.33.33

设树的度为2d（d>1）,高度为h，那么BTree具有如下的特质

  • 每个叶子结点的高度一样，都为h

  • 每个非叶子结点由n-1个key和n个指针组成，key和指针相互隔离，结点的两端一定是key

  • 叶子结点的指针为bull

  • 非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的其它列的数据；

在BTree中，对索引列是顺序存储的，所以很适合查找范围数据和ORDER BY操作。

B+Tree
截屏2020-06-28 上午9.48.16

B+Tree是BTree的一种变形，主要的不同点在于

  • B+Tree的非叶子结点不存储数据，只存储间值
  • B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址
  • B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

B+Tree对比BTree的优点：

一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构。

磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找。

已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

带顺序索引的B+Tree

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针

截屏2020-06-28 上午9.50.38

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，不用从头再查询一次，极大提到了区间查询效率。

聚簇索引和非聚簇索引

image-20200628095805744

mysql中最主要的两个引擎就是innodb和myisam，分别实现了聚簇索引和非聚簇索引，

主索引与辅助索引

在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

为什么需要用到辅助索引？有时候我们的查询并不会使用到主键列，所以需要在其它列建立索引，即辅助索引。

非聚簇索引

非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。

非聚簇索引的数据表和索引表是分开存储的。非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。

聚簇索引

聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。

聚簇索引的数据和主键索引存储在一起。

聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂（BTree插入时的一个操作），严重影响性能。

在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率

索引的分类

**普通索引：**即一个索引只包含单个列，一个表可以有多个单列索引

**唯一索引：**索引列的值必须唯一，但允许有空值

**复合索引：**即一个索引包含多个列

基础语法

  • 查看索引

    SHOW INDEX FROM table_name\G

  • 创建索引

    CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));

    ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))

  • 删除索引

    DROP INDEX [indexName] ON mytable;

执行计划

什么事执行计划

使用EXPLAIN关键字模拟优化器只是sql语句，从而知道mysql是如何执行你的sql的。分析你的查询语句和表结构的性能瓶颈

图片 1

执行计划的作用

  • 表的读区顺序
  • 数据读取和操作的类型
  • 那些索引可以使用
  • 那些索引实际被使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

执行计划详解

ID列

id：描述select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序，根据id的数值结果分为一下三类

  • id相同，至上而下执行

  • id不同，如果是子查询，id会递增，id越大越先执行

  • id相同又不同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

Select_type列

select_type查询的类型，用于区别普通查询，联合查询，子查询，复杂查询等

类型如下

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

  • simple

  • PRIMARY与SUBQUERY

  • DERIVED

  • UNION RESULT 与UNION

table列

显示这一行的数据是关于哪张表的

Type列

type显示的是访问类型，是一个较为重要的指标，结果从好到坏依次是

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

主要的就是system>const>eq_ref>ref>range>index>ALL这几个级别，

一般来说得保证查询至少到range级别

  • system和const

    • system表里只有一行数据（等于系统表），这个是const类型的特例，平时不会出现，这个也可以忽略不计
    • const：表示通过索引一次就找到了，const用于比较primary key和unique索引，因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量

    图片 1

  • eq_ref

    唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于组件或者唯一性索引扫描

    图片 1

  • ref

    非唯一性索引扫描，返回匹配某个单独值所在行，本质上也是一种索引访问，他返回所有匹配某个单独值的行 ，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

    图片 1

  • range

    只检索给定范围的行，使用一个索引来选择行，key列显示使用哪个索引，一般就在你where语句中出现了 < > between in等查询的时候，这种范围扫描比全表扫描要好，因为它只需要开始于索引的某一点，结束于另一点，不需要全表扫描。

  • index

    当查询结果全为索引列的时候，虽然也是全表扫描，但是只扫描的索引库，而没有全查数据

  • all

    ull Table Scan，将遍历全表以找到匹配的行

    图片 1

possible_keys与key

​ possible_keys:可能使用的key

​ Key:实际使用的索引。如果为NULL，则没有使用索引

​ 查询中若使用了覆盖索引，则该索引和查询的select字段重叠

key_len

​ Key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

​ key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

  • key_len表示索引使用的字节数，
  • 根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。
  • char和varchar跟字符编码也有密切的联系
  • latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。（不同字符编码占用的存储空间不同）

字符类型

image-20200628111139053

Keylen=varchar(n)变长字段+允许Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)+2即允许变化的需要+2允许为空需要+1

数值类型

image-20200628111610297

日期和时间

image-20200628111658510

datetime类型在5.6中字段长度是5个字节，

datetime类型在5.5中字段长度是8个字节

  • 字符类型

    变长字段需要额外的2个字节（VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)，所以VARCAHR索引长度计算时候要加2），固定长度字段不需要额外的字节。

    而NULL都需要1个字节的额外空间,所以索引字段最好不要为NULL，因为NULL让统计更加复杂并且需要额外的存储空间。

    复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

  • 整数/浮点数/时间类型的索引长度

    NOT NULL=字段本身的字段长度

    NULL=字段本身的字段长度+1(因为需要有是否为空的标记，这个标记需要占用1个字节)

    datetime类型在5.6中字段长度是5个字节，datetime类型在5.5中字段长度是8个字节

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

图片 1

由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 'ac'

其中 【shared.t2.col1】 为 【数据库.表.列】

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

图片 1

extra

包含不适合在其他列中显示但十分重要的额外信息

值 描述
Using filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”
Using temporary 使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
USING index 是否用了覆盖索引
Using where 表明使用了where过滤
Using join buffer 使用了连接缓存：
Impossible where where子句的值总是false，不能用来获取任何元组
  • Using filesort 当发现有Using filesort 后，实际上就是发现了可以优化的地方
image-20200628112240579

​ 上图其实是一种索引失效的情况，可以看出查询中用到了个联合索引，索引分别为col1,col2,col3

image-20200628112340700

​ 当我排序新增了个col2，发现using filesort 就没有了。

  • Using temporary

    image-20200628112442567
image-20200628112455685

​ 尤其发现在执行计划里面有using filesort而且还有Using temporary的时候，特别需要注意

  • Using index

    如果同时出现using where，表明索引被用来执行索引键值的查找

    image-20200628112608859

    如果没有同时出现using where**，表明索引用来读取数据而非执行查找动作**

    image-20200628112626534

    覆盖索引

    覆盖索引（Covering Index）,一说为索引覆盖。

    理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

    理解方式二:索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引

    注意：

    如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，

    因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

    所以，千万不能为了查询而在所有列上都建立索引，会严重影响修改维护的性能。

  • Using where 与 using join buffer

  • Using where 表明使用了where过滤

  • using join buffer 使用了连接缓存

image-20200628112822846

  • impossible where

    where子句的值总是false，不能用来获取任何元组

    image-20200628112855663

avatar
文章
阅读
粉丝