数据库进阶-存储引擎 索引

一.存储引擎

1.MySQL体系结构

！索引在引擎层 不同的索引会有不同的引擎层。

2.存储引擎简介

默认InnoDB

3.存储引擎特点

（1）InnoDB**

（）

innoDB每张表都会对应一个表空间文件，存储该表的表结构 数据和索引

其中page是磁盘操作的最小单位，一个extent和page的大小是固定的（一个区可以有64个页）

（2）MyISAM

本地磁盘C中的.sdi文件可以直接打开去Json

（3）Memory

速度快 但只能临时表！数据不在磁盘上 在内存中

4.存储引擎选择

二.索引

1.索引概述

2.索引结构

（1）BTree索引

从下往上分裂

 插入1200后 元素超4个 需要分裂 0345变为中间元素 中间元素向上分裂

插入1234 与0345比较 大 放右边 右边也可以放下

......

（2）B+tree

1. B+ Tree中所有元素都会出现在叶子节点

2.绿色方框起到索引作用 叶子节点（红色方框）起到存储作用所有叶节点包含所有关键字，叶节点将关键字按大小排列，并且相邻叶节点按大小顺序链连接起来，形成单向链表。（B+ 树中是叶子节点中的数据是单向链表，叶子节点之间是双向链表）

插入890后

......

插入2345后

B+树搜索效率稳定 无论任何数都要到底层叶子节点找到

（3）Hash

58dda是一行的哈希值

3.索引分类

二级索引（辅助索引/非聚集索引）

聚集索引之所以必须有是因为它是用来存储数据的，而二级索引更多的使用来更快更高效的查询数据

聚集索引下面挂的是一整行的数据，二级索引挂的是id

聚集索引（1）。二级索引需要回表查询

4.索引语法

1.

2.

3.

4.

5.删除email索引

5.SQL性能分析

（1）SQL执行频率

若发现查询占比很大 我们就需要对此进行优化

（2）慢查询日志

默认关闭

（3）show profiles

（4）explain

字段含义

多表查询

id相同，从上到下

查询选修MySQL的学生

id不同 从大到小（c表先执行）

null一般在不用查表时出现，

当根据主键或唯一索引访问时 会出现const，

使用非唯一索引时 会出现ref，

使用非唯一索引, 但是范围匹配，会出现range

6.索引使用

（1）验证索引效率

针对字段建立索引（构建B+树）

再次执行相同的SQL语句

（2）最左前缀法则&范围查询

（1）最左前缀法则

蓝色区域为联合索引，最左前缀法则要求第一个字段必须存在，并且中间不能跳过某列

此时联合索引的第一个字段都在查询中 所以key中都显示用联合索引

此时联合索引的第一个字段不在查询中 不满足最左前缀法则 所以索引失效 key为null

此时索引部分（status）失效 因为pro-age-sta中跳过了age

调换了查询顺序后 索引并没有失效 并且三个字段的索引都用上了

（2）范围查询

此时由于age是范围查询 所以status的索引失效 并未用到

而将>改为>= 这三个字段就会都用到了

（3）索引失效情况

（1）索引列运算

（2）字符串不加引号

例一

例二

（3）模糊查询

后面模糊 还走索引

前面模糊 不走索引

（4）or连接的条件

（age 字段没有索引）

（5）数据分布影响

例一

例二

但修改整张表的profession为null时

故查的少数走索引,查的多数不走索引

对于选择性越高的列，加上索引更合适

（4）SQL提示

在创建单列索引后 默认选择了联合索引

建议索引（use index） 有时MYSQL不接受麻烦建议 

此时可能用到的索引就忽略了单列索引（ignore index）

强制必须走这个索引（force index）

（5）覆盖索引&回表查询

==

注意最后一列的区别 其中 前三个的性能高 最后一个性能低

覆盖索引

回表查询

=======================================================================

id是主键为聚集索引，建立username和password的复合索引

（6）前缀索引

实际情况中实际选择

然后验证拿到的row的email是否与所求相同（此时对比的只有前缀） 不相同就继续看下一行 重复

（7）单列&联合索引

此时进行的是phone的索引来查询 所以后续必然出现回表查询

增加了phone和name的联合索引后 也用的是phone的索引

指定索引（use index）后发现（Extra）不再需要回表查询

覆盖索引 不需要回表查询 但是要考虑联合索引的顺序（最左前缀法则）

7.索引设计原则