mysql索引结构、explain工具

1：什么是索引？

排好序的数据结构

2：为什么mysql索引结构不是二叉树、红黑树、B-tree？

1：如果是二叉树：
如图:当字段为有顺序递增时，二叉树子节点大于父节点原则，二叉树会无限单边递增。这样高度不可控。查询效率不高。

2：红黑树
红黑数高度 2的n次方等于数据量，如果数据量为500万，那么高度大，查询次数多，性能不高，而且插入时，节点还自旋。

3：B-tree
1：节点数据索引从左到右依次增加。所有索引不重复。
因为每个大节点mysql默认存储空间为16kb,而每个索引下存了具体的数据。如果表的字段多。那么一个大节点存的数据也不是很多，那么必定会增加高度，那么查询次数好是会增多，查询速度还是不可控

4：B+ tree
1：非叶子节点不存数据，值存索引（冗余），可以放更多索引
2：叶子节点包含所有字段数据
3：叶子节点间有双向指针连接，提高区间访问性能
当高度为3时，假设data为1kb，指针6b，索引8b
数据量=16kb/8b+6b =1170
117011703 =2千万条
而且 根节点 是存在内存中的，所以根据索引查询一个数值要2次

3:mysql支持两种索引数结构hash和B+tree，为什么不用hash呢？

对于等值查询，hash只要查询1次就可以了，可是当范围查询、排序、模糊查询hash结构就不能做索引查询了

4：mysql两种存储引擎

4.1 myIsam存储引擎（非聚集索引）

索引文件和数据文件分开，叶子节点存储数据的地址，根据地址去数据文件查找数据

4.2 innodb索引实现

1：主键索引（聚集索引）
2：叶子节点存储表所有数据
3：表文件就是B+tree 组织的一个索引结构文件

5 :为什么innodb必须要有主键，并且推荐使用自增整型的主键？

1：因为innodb数据文件是B+tree结构组织的索引文件，而且该文件非叶子节点存储都是索引。
2：叶子节点索引大小从左到右依次递增，如果前面存了许多数据，现在插入一条索引较小的数据时，导致节点会分裂，需要调整平衡，主键自增会减少这种节点分裂，调整平衡的次数。
3：如果用uuid做主键索引，当查询走索引时，uuid要转化为整型再做比较，而且uuid消耗存储空间

6：为什么非主键索引叶子节点存储的事索引值？

为了保证数据一致性，如果非主键索引存数据的话，当修改一个值时，多个文件多要修改，不好保证数据一致性，也浪费存储空间。

7：联合索引存储结构

a,b,c三个字段联合索引

最左原则：三种走索引情况： a ab abc

8： explain查询sql是否走索引

注意：1：如果from中包含子查询，会执行子查询，并将结果放在内存中
2：join连接，explain执行会有两行记录

字段	含义
id	id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的,id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
select_type	1:simple：简单查询 2:primary：复杂查询中最外层的 select 3:subquery：包含在 select 中的子查询（不在 from 子句中）4:derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表
table	这一列表示 explain 的一行正在访问哪个表,当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的select 行id
type	这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL一般来说，得保证查询达到range级别，最好达到ref
possible_keys	这一列显示查询可能使用哪些索引来查找
key	这一列显示mysql实际采用哪个索引来优化对该表的访问
key_len	显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。(1):字符串:char(n)：n字节长度,varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n+ 2 ( 2):数值类型:tinyint：1字节,smallint：2字节,int：4字节,bigint：8字节(3:):时间类型: date：3字节timestamp：4字节datetime：8字节…索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
ref	这一列显示了在key列记录的索引中，表查找值所用到的列或常量,常见的有：const（常量），字段名（例：film.id）
rows	这一列是mysql估计要读取并检测的行数，
extra	这一列展示的是额外信息。(1）Using index：使用覆盖索引 (2）Using where：使用 where 语句来处理结果，查询的列未被索引覆盖 (3）Using index condition：查询的列不完全被索引覆盖，(4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。(5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。（）6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段

explain select (select 1 from actor where id =1 ) from (select * from film where id =1) der；

综上：1：当type为ALL时，走全表扫描，则需要优化
:2：当extra为using temporary，mysql创建临时表处理查询，需要用索引优化
:3：当extra为using filesore ，当数据小的时候是内存排序，数据大的时候是磁盘排序，需优化为索引排序

优化sql总结：

1. 最左前缀法则
2. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
3. 存储引擎不能使用索引中范围条件右边的列
4. 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句
5. mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
6. is null,is not null 也无法使用索引
7. like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作
8. 字符串不加单引号索引失效
9. 少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引