专项学习MySQL高级（2）

2021.3.11 天气：晴朗

Mysql

一、索引

1.Index是帮助MySQL高效获取数据的数据结构。
可以理解为排序后的快速查找数据结构

2.我们平时所说的索引，如果没有特别说明，一般是指B树（多路搜索树，并不一定是二叉的）结构组织的索引，其中聚集索引，次要复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引，还有哈希索引等

3.一般索引本身也比较大，不可能全都储存在内存中，往往以索引文件的形式储存在磁盘上

索引的优势：

1.类似图书馆的书目索引，提高数据检索的效率，降低数据库IO成本

2.通过索引列队数据进行排序，降低数据排序成本，降低了CPU的消耗

索引的劣势：

1.实际上索引也是一张表，只是该表只保存了索引字段与主字段，并指向实体表的记录，所以索引需要占用空间

2.索引提高了查询速度，也会降低更新速度，因为在更新表的时候还需要更新索引文件

3.索引是提供效率的一个因素，但是需要花时间研究建立最优的索引方式

索引的分类：
1.单值索引
2.唯一索引
3.复合索引
4.基本语法

索引的结构
1.BTree索引
2.Hash索引
3.full-text全文索引
4.R-Tree索引

什么情况建索引

1.主键自动建立的唯一索引
2.频繁作为查询条件的字段应该创建索引
3.外键关系建立索引
4.频繁更新的字段不适合建立索引
5.where条件里用不到的字段不创建索引
6.单键/组合索引：高并发更倾向组合索引
7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8.查询中统计或者分组字段

二、性能分析工具：Explain

Explain可以模拟优化器执行SQL查询语句，从而分析查询语句或者表结构的性能瓶颈
【Explain + SQL语句】：显示优化信息
【Explain + SQL语句\G】：竖表显示

因为子查询需要先查出结果，才能作为主查询的条件，所以子查询的优先级更高更先被执行。如果是平行查询的话SQL有自己的解析方法，id相同的时候，由上往下执行。

查询类型：

1.SIMPLE：简单的select查询
2.PRIMARY：查询中包含的复杂子查询，最外层为主查询
3.SUBQUERY：在SELECT或者WHERE列表中包含了子查询
4.DERIVED:在FROM列表中包含的子查询被标记为DERIVED（衍生），会递归执行这些子查询，把结果放在临时表里
5.UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若包含在FROM子句的子查询中，外层SELECT被标记为DERIVED；
6.UNION RESULT：从UNION表获取结果的SELECT

类型：

POSSIBLE_KEYS：

可能用的的索引，但是不一定用到

KEYS：

实际上用到的索引，如果查询中是覆盖索引，则不会出现在possible_keys，直接出现在keys

key_len：

表示索引中使用的字节数，理论上在不损失精度的情况下，key_len越少越好

ref：

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

rows：

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

个人分析：
建立索引前
1.因为and条件，先查出t2,col1 = ‘ac’;这个条件，所以没有用到主键索引，type是ALL
2.然后查出t1.id = t2.id,所以t1的type是eq_ref，这里用到主键索引
建立索引后
1.先查出t2,col1 = ‘ac’;这个条件，用到建立的索引，减少了读取的行数
2.然后查出t1.id = t2.id,所以t1的type是eq_ref，这里用到主键索引

extra：

包含不适合在其他列显示但是十分重要的额外信息（类似重要备注）

12是不行的，3是高效的，其他的是正常