[mysql]索引&性能分析

索引

索引什么？

MySQL官方对索引的定义为：索引（INDEX）是帮助MySQL高效获取数据的数据结果。

可以理解为“排好序的快速查找数据结构”

索引的目的在于提高查询效率，可以类比字典，
如果要查“mysql" 这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql.

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，
这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是-种可能的索引方式示例:

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地
址的指针，这样就可以运用二叉查找在一-定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，
这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

索引本身也很大，不可能在内存中，因此往往以索引文件存储在磁盘中。

我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引
复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

索引的优劣势

优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，
都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查查询

索引的分类

一张表索引不要超过5个。

单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但是允许空值。
复合索引：一个索引包含多个字段。

基本语法

#创建
CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length));
ALTER TABLE tabName ADD [UNIQUE] indexName(columnName(length));
#删除
DROP INDEX [indexName] ON tabName;
#查看
SHOW INDEX FROM tabName \G;

使用AlTER命令

#该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL
ALTER TABLE tabName ADD PRIMARY KEY(column_list);

#该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) 
ALTER TABLE tabName ADD UNIQUE indexName(column_list);

#该语句创建普通索引，索引值可以出现多次 
ALTER TABLE tabName ADD INDEX indexName(column_list);

#该语句指定了索引为FULLTEXT，用于全文检索 
ALTER TABLE tabName ADD FULLTEXT indexName(column_list);

索引的结构

BTree
- 检索原理
  
  「初始化介绍」
  一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示)，
  如磁盘块1包含数据项17和35，包含指针P1、P2、P3,
  P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
  真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99.
  非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
  「查找过程」
  如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次I0，在内存中用二分查找确定29在17和35之间，锁定磁
  盘块1的P2指针，内存时间因为非常短(相比磁盘的I0)可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存
  ，发生第二次I0， 29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO,同时内存中做二分查找找
  到29，结束查询，总计三次I0.
  真实的情况是，3层的b叶树可以表示上百万的数据，如果上百万的数据查找只需要三次I0，性能提高将是巨大的，如果没有索引，每个
  数据项都要发生一-次I0，那么总共需要百万次的I0，显然成本非常非常高。
Hash索引
full-text全文索引
R-Tree索引

哪些情况下建立索引

主键自动建立唯一索引
频繁作为查询条件的字段因该创建索引
查询中与其他表关联的字段，外键关系建立索引。
where条件里经常用的字段
频繁更新的字段不适合做索引
单键/组合键索引的选择问题，who？（在高并发条件下倾向创建组合索引）
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
查询中统计或分组字段（group by）

哪些情况下不建立索引

表记录太少
经常增删改的表
数据重复且分布平均的字段（例如：性别）
假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为 50% ，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

性能分析

MySql Query Optimizer

MysqI中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划(他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)
当客户端向MySQL请求--条Query,命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL
Query Optimizer时， MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算,直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有)，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

MySQL常见瓶颈

CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈:top,free,iostat和vmstat来查看系统的性能状态

Explain

简介

是什么？
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

怎么用？

Explain + SQL

mysql> explain select * from tbl_emp;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tbl_emp | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

能干嘛？

表的读取顺序
数据读取操作的操作流程
哪些索引被实际使用
表与表之间的引用
每张表有多少被优化器查询

各字段解释

mysql> explain select * from tbl_emp;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tbl_emp | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

id

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：

id相同，执行顺序由上至下。
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
id相同不同，同时存在。永远是id大的优先级高，id相等的时候顺序执行

select_type

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

table

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

type:访问类型

从最好到到最差依次如下：
system>const>eq_ref>ref>range>index>ALL

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。
const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转化为一个常量。
eq_ref：唯一性索引扫描，读取本表中和关联表表中的每行组合成的一行，查出来只有一条记录。除了 system 和 const 类型之外, 这是最好的联接类型。
ref：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，然而，它可能会找到多个符合条件的行，所以他应该属于查找的和扫描的混合体。
range：只检索给定范围的行，一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。
index：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的。
ALL：Full Table Scan，没有用到索引，全表扫描。

备注：一般来说至少达到range级别，最好到达ref级别。

possible_keys 与 keys

possible_keys:显示可能应用在这张表中的索引，一个或者多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key：实际使用的索引。如果为NULL，则没有使用索引。
查询中如果使用了覆盖索引，则该索引仅仅出现在key列表中。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精度的情况下，长度越短越好。
key_len显示的值为索引字段的最大可能长度,并非实际使用长度，即key_len是根据表定义计算而，不是通过表内检索出的。

ref

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

rows

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

EXtra

额外的信息(前3点)

Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为"文件内排序"。
Using temporary：使用了临时表保存中间结果，MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。
Using index：表示相应的SELECT操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现Using where，表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

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

注意:如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *
因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

Using where:表明使用了WHERE过滤。
using join buffer:使用了连接缓存。
impossible where：WHERE子句的值总是false，不能用来获取任何元组。
select table optimized away:

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引攀优化COUNT(*)操作，不必等到执行阶段再进行计算，
查询执行计划生成的阶段即完成优化。

distinct

posted @ 2020-09-17 11:45 HankinkK 阅读(336) 评论(0) 收藏举报

刷新页面返回顶部

HankinkK