mysql之索引

什么是索引？

索引是帮助mysql高效获取数据的数据结构。可以得倒索引的本质：索引是数据结构。
也就是排好序的快速查找数据结构。

B-TREE树

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应的数据，从而快速的检索出符合条件的记录。
结论：
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。

索引本身也很大，不可能全部存储在内存中，因此索引往往是文件的形式存储在磁盘上。
我们平常所说的索引，如果没有特别声明，都是B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中局级索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引。

优势和劣势

优势

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

劣势

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

索引分类：

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

--创建
create [unique] index index name on mutable(column name(length))

alter mutable add [unique] index index name on mutable(column name(length))
-- 删除
drop index index name on mutable
--查看
show index from table_name

mysql索引结构

BTREE结构
Hash索引
full-text全文索引
R-tree索引。

BTREE索引原理

【初始化介绍】
一棵B+Tree树，浅蓝色的块我们称之为一个磁盘快。可以看到每个磁盘快包含几个数据项（深蓝色所示）和指针（黄色所示）
如磁盘块1包含数据项17和35，包含p1、p2、p3
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子结点即3、5、9、10、13、15、28、29、60、75、79、90、99.
非叶子结点只不存储真实的数据，只存储只因搜索方向的数据项，如17、35并不真实存在于数据表中。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常的高。

哪些情况需要创建索引

1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外健关系建立索引
4.频繁更新的字段不适合茶u你更加爱你索引，---因为每次更新不单单是更新了记录，还会更新索引。
5.where条件里用不到的字段不创建索引。
6.单键/组合索引的选择问题，who（在高并发下倾向创建组合索引）
7.查询中排序的字段，排序字段若通过索引去访问将大大提高索引速度。
8.查询中统计或者分组字段。

哪些情况不需要创建索引

1.表记录太少
2.经常增删改的表 ---因为每次更新不单单是更新了记录，还会更新索引。
3.数据重复并且分布平均的表字段。因此应该只为经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

性能分析

MySQL QUERY Optimizer

1.MySql中有专门负责优化Select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，维客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间）

MySQL 常见瓶颈

Explain

执行计划：

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

能干什么？

1.了解表的读取顺序
2.数据读取操作的操作类型
3.哪些索引可以使用
4.哪些索引被十几使用
5.表之间的引用
6.每张表有多少行被优化器查询。

怎么用？

1. Explain+sql语句
   2.执行计划包含的信息
   

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

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类型的特列，平时不会出现，这个也可以忽略不计

const

表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转换为一个常量。

eq_ref

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

ref

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

range

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

index

index与all的区别为index类型只遍历索引树，这通常比all快，因为索引文件通常比数据文件小。

all

将遍历全表以找到匹配的行。

possible_keys

显示可能用在这个表中的索引，一个或者多个。查询涉及到的字段上若存在索引，则该索引将被列出，但是不一定被查询实际使用。

key

实际使用的索引，如果为null，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中

key_len

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

ref

显示索引被那一列失意哦那个，如果可能的话，是一个常数。那些列或者常量被用于查找的索引的列的值。

rows

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

Extra

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

using filesort

说明mysql会对数据是哟ii那个一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为‘文件排序’。

using temporary（危害高）

使用了临时表保存中间结果，mysql在对查询结果排序时，使用了临时表。常见于order by和分组查询group by

using index

表示响应的select操作中使用了覆盖索引，避免访问表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找。如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

覆盖索引

覆盖索引，一般称为索引覆盖

索引优化