MySQL索引重点问题总结（需要完整脑图的联系我）

MySQL索引

MySQL逻辑架构

连接层

MySQL连接层主要包括连接池，mysql属于C/S架构，每次客户端和服务端连接时，底层都会创建一个TCP连接，连阶层主要负责管理这些连接，验证用户名密码和权限认证

服务层

mysql的服务层主要负责sql语句的解析优化。它有基础服务组件，SQL借口，解析器，优化器，查询缓存（MySQL8.0之后去除了这个鸡肋的设计）等组成。整个语句的执行过程是这样的先查看查询缓存中是否存在当前执行的语句，如果不存在则先由解析器解析，优化器指定执行计划，调用sql接口由存储引擎层执行真正的工作。

解析器

数据库的解析器先对sql语句进行词法分析，分析出哪些是关键字，表名，属性，然后进行语法分析，创建出语法出语法数，进行权限认证。

优化器

优化器的主要作用是制定一个高效的执行计划，对语句的优化分为逻辑上的优化比如说是否可以将一个子查询改写为一个多表连接语句（因为子查询会创建临时表并且不能够使用索引）和物理上的优化比如说使用什么样的索引可以增加查询的速度等等。

查询缓存

查询缓存其实就是mysql中一个kv键值对的存储空间它将对应的sql语句作为key，执行的结果作为value。如果后续执行相同的语句可以通过缓存直接得到结果。
弊端：查询缓存中以sql语句作为key，只要输入的语句有一点点不同即使语句的语义一致也不能够使用上缓存。针对一些函数比如说时间函数now，会改变语句中的时间页使用不到缓存。对数据库的改变会刷新缓存降低命中率
Oracle中的缓存设计，使用到一个共享池的概念，它不仅仅会缓存语句还会缓存对应的执行计划，而且它还利用一种类似函数的机制有利于批量处理数据。

存储引擎层

存储引擎以前有个很土的名字叫做表处理器，这个名字更能够让我们了解其的作用。其实每个存储引擎就是对应着不同应用场景下设计不同数据库表的存储方式，在不同的存储结构上面构建具有不同作用不同性能的表处理器。
例如最经典的INNODB存储引擎在5.0阶段的存储结构就是由.opt文件来存储一些数据库通用的设置，由.frm文件存储表的结构由.ibd文件来存储数据和索引（innodb中有一句很经典的话叫做数据即索引，索引即数据）；在升级到mysql8.0之后mysql的存储结构变成了.ibd文件一个它将数据库配置信息，表结构信息，和索引数据都存储在一个文件中。
再比如MYIAM存储引擎，它底层的表存储结构为使用.frm文件来存储表结构,分别使用.myi,.myd文件存储索引和数据，这也导致它在索引的设计上和innodb有一些细微的差别

查看和设置存储引擎

对于数据库存储引擎的查看和设置

查看mysql所有支持的存储引擎：
show engines
查看存储引擎相关的信息：
show variables like “%storage_engine%”
show variables like “default_storage_engine”

对于表级别存储引擎的设置和修改

可以在表定义的时候在后面跟上

engine = “InnoDB”
可以修改表的结构来修改存储引擎：
alter table table_name engine = “innoDB”

各种存储引擎的介绍

InnoDB

InnoDB是在mysql5.5之后默认的存储引擎，优点在于其支持事务，缺点在于它对比MYISAM上性能会有所偏差

MYSISAM

MYISAM存储引擎主要适合用于处理读比较多的业务场景。
优点在于它的性能会比较好
缺点在于它不支持事务，行级别的锁，外键等，无法保证失败回滚

CSV

CSV存引擎可以支持使用excel打开

Archive

Archive的意思是归档，所以说这是一个专门用于归档的存储引擎，它只有增加和查询操作。并且有很好的压缩机制

memory

memory存储引擎的数据表结构使用.frm文件存储在磁盘上但是其数据存储在内存中，适合使用在短时间内访问的临时数据。默认使用的索引结构是hash索引，并且对表的字段和大小都有严格的限制。

InnoDB和MYISAM的区别

①首先从文件系统的存储结构上来说InnoDB在8.0之前将数据库通用设置存储在.opt文件中，将表机构存储在.fem文件中，将数据和索引存储在.ibd文件中（所以InnoDB中有一句话叫做索引即数据数据即索引，因为它们是一起存放的）,在8.0之后将上述的所有信息都存储在.ibd文件中.而对于MYISAM存储引擎来说它将表结构存储在.frm中，将数据存储在.myi中，将数据存储myd文件中。数据和索引是分开存放的这也导致MYISAM中的索引都是通过B+Tree的结构在索引文件中存放然后通过指针指向数据的所以在MYISAM中不存在聚簇索引这一说。
②由于底层存储的结构不一样导致缓存也不一样，INnoDB缓存聚簇索引和数据，MYIASM只缓存数据
③由于底层结构不一致所以索引的实现上也有所不同
④InnoDB提供对事务的支持，MYIAM则没有
⑤InnoDB提供对行锁的支持，MYIAM没有
⑥InnoDB支持外键MYIAMmy
⑦总结来说不同的存储引擎设置不一样的底层表存储结构为了适应不一样的业务场景，InnoDb侧重事务性，MyIAM侧重性能

物理层

物理层是真正硬件中存储mysql中数据库表和每一行数据的地方，为了不让mysql大部分工作在磁盘中完成，mysql在内存中分配了一块缓存以减少磁盘工作和IO时间大大增加mysql的提高了mysql的整体性能。

数据库缓冲池

数据库缓冲池其实就是在内存中开辟一块空间用于存储页，减少磁盘IO的时间和磁盘的哦工作提升数据库的整体性能。
缓冲的规则：数据库缓冲池的缓冲规则是“位置 * 频率”,其中位置表示的磁盘能够快速检索到的位置，频率指的是数据库页的使用频率。对于使用频率较高的数据它不仅仅会缓存本页的数据还会连带着缓存上下页的数据提高数据库的整体执行效率。

MYSQL索引

为什么要有索引：如果数据库的底层不存在索引，那么我们查询数据就需要全表扫描效率极低
什么是索引：索引是快速检索（基于某一种数据结构组织数据，并且在该种数据结构上执行对应的检索算法）数据的数据结构。基于不同的存储引擎索引有着不同的实现。
索引的特点就是排序和检索

优缺点

优点：
①加快数据检索的速度
②对于数据加上唯一索引能够保证数据的唯一性
③使用索引对数据的排序和分组有很大的性能提升
④使用索引可以加快多表连接
缺点：
①创建索引需要一定的时间
③索引需要一定的存储空间存放
③虽然索引有利于快速查找数据，但是对于数据的修改更新和删除需要付出更多的成本来维护数据和索引

索引底层的数据结构

B+Tree

为什么是B+Tree

索引底层的机构刚刚开始并不是B+Tree，通过一代代的更新迭代的。
一、首先我们想到的检索数据的结构是hash，它的优点在于插入和查询的复杂度都是O（1）级别的。但是这种复杂度是用于检索一条数据的，当检索范围数据时它逐条扫描，扫描一次IO一次效率十分低下，而且还存在着hash碰撞的情况。但是我们在查询时还是可以使用自适应性hash的，它可以让我们快熟定位到数据页。
二、第二个快速检索的数据结构就是树形结构，在树形结构中检索和插入的时间复杂度能够达到logn级别
①首当其冲的数据结构是二叉树，二叉树的缺点是当我们按照顺序插入数据时它就会退化为一个链表，那么我们检索数据无异于全表扫描。
②接着考虑书的平衡性，我们就使用平衡二叉树（AVL数树），但是平衡二叉树的缺点在于，它虽然可以有效控制树的平衡性但是数据库所需要的树是要尽量能够减少磁盘IO的树也就是说树的高度不能够太高所以不能够使用AVL树
③我们考虑使用B-Tree（多路平衡搜索树），这样增加每个节点的儿子节点数量，大大减小了树的高度，有效控制磁盘IO。
④我们使用B+Tree（改进的多路平衡搜索树），它对于B-Tree的改进在于它不在非叶子节点存储数据，二十将所有的数据都放到叶子节点使用一个双向链表的结构连接起来。
好处在于，非叶子节点现在只存储索引不存储真实的数据这样能够让树的分支更多有效降低了树的高度，减少了IO的次数。底层使用双向链表的结构将所有的数据按照大小的顺序排列这样有利于检索范围的数据，对于B树来说如果要检索范围的数据则需要中序遍历。

B+Tree索引的介绍

数据库索引的推演过程

推演的过程：
①首先设想数据库中不存在索引，那么一个表中的数据是散落在一个个的页中的，各个页之间使用双向链表的结构连接起来这个数据记录在页首部的字段中。各个页的内部使用向链表的结构来组织存储，各个记录之间的指针信息存储在字段首部信息中。这样我们要检索一个数据就要从第一页开始逐条搜索，然后通过指针信息跳转到下一个页中，接着逐条扫描，效率极其低下。
②那我们就会想使用一个数据结构给本结构做一个优化处理，于是想到在数据页的上一层建立一层索引页（其实也是数据页区别在于索引页中的记录类型和记录头信息有细微的差别)，这样我们就可以先访问索引页找到对应的页位置大大减小了IO的次数。
③当插入的数据过多底层产生页分裂导致上层索引页不够记录数据，索引页页产生了页分裂，这样我们就需要在索引页上层页再建立一层索引页。保证整个索引的访问结构中树只有一个根节点（这个跟节点页一般时存储在内存当中的，当需要使用索引时就可以减少一次IO操作，这也是为什么数据的访问只需要两次或者三次IO的原因）

推演中三个需要纠正的点

①根节点的位置万年不变，我们上述的推到过程时从下往上的推到，而现实中的B+Tree创建时从上往下的，当一个索引页中的数据不够存放时会产生页分裂这个时候创建一个新的页将本页中的数据copy到新的页中然后将本页的指针指向新创建的索引页中。
②索引中数据记录要保证唯一性，所以创建索引时不单单要保存当前的数据记录，为了保证每个节点的唯一性还需要保存当前表的主键值。
③由于我们要创建的是一个是一个树型结构所以索引页的分支不能少于二

B+Tree索引的文件存储结构

mysql管理数据底层存储的层级分为，表空间，段，区，页，数据行

表空间

表空间是mysql中一个逻辑概念，mysql的表空间中我们需要了解的就是系统表空间和独立表空间两种

####### 系统表空间

系统表空间在mysql5.5之前可以用于存储用户表中的数据和系统表中的数据，但是在5.6之后只能用于存储系统表中的数据。mysql系统数据库主要有如下几个
①information_schame：这个数据库中存储的主要是数据库的一些配置参数信息比如说使用的存储引擎信息，设置的字符集信息等
②performance—_schame：这个数据库中存储的主要是数据库运行的一些状态信息比如说我们的profile信息等。
③sys：为了更加方便查阅数据库中的信息我们将一些经常需要查询大的数据库配置信息和状态信息存储在这个数据库中。

####### 独立表空间

独立的表空间在mysql5.6之后专门用于存储用户自定义的数据库中的信息，我们表中的ibd文件都存储在独立表空间中。
好处：
①有利于数据的迁移
②有利于表空间的回收

段

mysql中的段主要包括，数据段，索引段，系统段，事务段，undo段等等。我们将数据组织成B+Tree之后树中有分为索引页和数据页为了访问对应的数据更加纯粹我们分出了段的概念。

区

为什么要有区：
这个涉及到数据页访问的效率，比如说我们在内存中访问一个数据页大概需要1ms的时间，在磁盘上使用随机IO访问一页数据的时间大概是10ms（大部分的时间浪费在半圈旋转，寻道和排队等待上而真正用于IO的时间只有十分之一），如果在磁盘上使用顺序IO呢，在大批量加载的前提下访问一个数据页的时间只需要0.4ms。至此我们看到了随机IO和顺序IO的效率差别还是很大的所以我们访问数据的时候应该尽可能使用顺序IO来提升数据访问的效率。区就此诞生它将64个页组织成一个连续的存储空间（大小正好为1MB），方便我们在范围这一个区的数据时时顺序IO。而区和区之间再使用指针来连接做到物理上的来连续。
区的分类：
要介绍区的分类首先得引入一个概念叫做碎片区，我们设想很小的数据量情况下我们需要在数据段和索引段中各种划分出一个区也就是2M的存储空间来存储比如说10kb的数据，这太浪费空间了。所以对于着一些占用很小空间的数据我们完全可以让它们共享一个区，而这个区就叫做碎片区。所以MySQL底层在分配空间的时候，碎片区首先时隶属于表空间的当某一个数据占有超过32个区的时候再给它分配一个专门的段。
所以区的分类就出来了：
①空闲区
②带有剩余空间的碎片区
③不带有空闲空间的碎片区
④隶属于某一个段的区

页

页是内存和磁盘交互的基本单位（基本单位的意思是说，不管我们只需要获取修改一个页中的一条或者几条数据，都需要加载完整的页和写入完整的页）一个页的大小默认是64kB。
页的底层存储结构是双向链表结构，页内部则是采用单向链表来存储每一条数据。

####### 页的存储格式

页的存储格式主要分为七个部分我们将这七个部分分为三类来分卸：
一、：文件的头尾
文件头存储的信息包括：文件属于什么类型，页号，页的前后指针，校验和以及日志文件中记录的位置
文件尾存储的信息：就只有校验和和日志文件中记录的位置

其中最重要的两个部分就是校验和和日志文件中存储的位置。
首先我们看看校验和到底是个什么东西，当一个文件很长的时候我们可以使用抽样算法抽取出文件中的主要信息相当于形成一个文件的指纹用于标识一个文件。这个指纹就叫做校验和。那么它的作用就是当一个页被加载到内存中使用和修改之后会修改它的头尾校验和，当刷盘中出现错误时，就会导致首尾的校验和不一致的问题这个时候我们就能够识别这一种错误，采取回滚或者借助redo日志完成刷盘操作。

日志文件的记录位置页是起到这一个作用

二、页头和页目录
页头存储着野种许多零零碎碎的信息，比如页中有多少条记录，有多少个用于页目录访问的槽位以及页中删除链表的信息等。

页目录：
引入页目录的原因是为了快速查找一个页面中的数据，他的设计是这样的为了减少空间的使用它将数据分为一组一组最小记录为一组其他记录按照达到九个分裂出四个的方式分组。我们只要记录每组中最大的记录作为一个槽位来进行二分法查找如果，具体的查找过程由于单链表单向访问的特点所以我们要查找一组中的数据的时候需要查找它的上一个分组中的最大记录顺序往下。
这个还涉及到了行格式中行头部信息的一个用于记录一组中有多少个记录的一个标志位，这个标志位只会存在于一组中最大的记录中。

三、：最大最小记录，页记录，空闲空间
最大最小记录很好理解就是一页中最大的额和最小的它们存储在头部中并且记录的编号为0和1

页记录：这个位置就是真正用于记录我们每一条记录的位置

剩余空间：就是我们这个页还有多多少可用的空间。

数据行

####### 数据行格式

数据行的格式大体上分为四种：
compact，dynamic，compressed，redundant其中最主要需要了解的就是compact其他的格式都和它大同小异。

######## compact

compact格式大体上分为额外记录的信息和行数据，额外记录的信息中包括边长字段长度列表和null值列表记录头信息，行行记录信息分为隐藏列信息和真实数据。

######### 额外信息

########## 变长字段长度列表

变长字段长度列表主要用于记录我们的表记录中边长字段真实使用的长度信息，它的记录方式是从尾到头记录我们变长字段长度

########## null值列表

null值列表的意思是使用一个bit位来记录我们的字段是否为null并且它也是倒序存放d

########## 记录头信息

记录头信息记录以下几个信息：
表中字段的类型，序号，下一条记录的位置，以及三个属性分别为当前的记录是否被删除（被删除的记录会标识为1并且所有已经被删除的记录组成一个链表方便后期使用），该条记录是否是页中一组中的最小值以及一组中数据的个数

######### 真是数据信息

########## 隐藏列数据

隐藏列主要有如下三条：
①本条记录的rou_id：这个值用于唯一标识一条记录，当我们的数据表中不存在主键和唯一字段时就是使用这个字段来作为主键
②事务id
③回滚指针

########## 表记录数据

######## dynamic

######## compressed

######## redundant

基于不同存储引擎的实现

InnoDB 底层索引

索引分类

####### 聚簇索引

聚簇索引其实就是mysql通过主键创建的一个索引（如果表中有主键直接通过表中的主键创建，如果没有主键但是存在唯一约束字段就会通过这个字段创建，如果两者都没则会自动生成一个字段来代表主键）这个索引的底层存储的是表中的整行记录
优点：
①索引及数据，数据即索引，查找的速度很快
②对于排序和范围的数据，可以做到快速查找
③减少IO
缺点：
①对于表记录的修改操作（增，删,改）我们为了维护现在的聚簇索引结构可能会出现页分裂，记录移位，记录换位的情况
②二级索引需要借助主键索引才能访问到其他列中的数据

####### 非聚簇索引（二级索引，辅助索引）

非聚簇索引（也叫二级索引或者辅助索引），就是通过非主键字段创建的索引它的底层存储的是该字段的值以及主键字段的值。InnoDB中有个词叫做回表，如果要查询的字段就在这个索引底层存储表示我们可以通过这个索引来直接的到数据这样获取到的数据就叫做索引覆盖，如果在这个索引中得不到数据就需要根据主键的值在聚簇索引中进行一个回表操作获得值。

####### 联合索引

联合索引（或者叫多列索引），就是通过多个字段共同构建的索引会根据声明索引的顺序先后按照字段排序组织索引。

MYISAM底层索引

MYISAM存储引擎的表文件系统表存储结构时将索引和数据分离的，所以在MYIAM中不存在聚簇索引，非聚簇索引这一说。
它的底层也是通过B+Tree实现的只不过它的叶子节点中存储的是指向数据的指针。

MYISAM和InnoDB索引对比

①首先二者文件存储结构不同，InnoDb将数据和索引存储在一个文件中，MyIAM将数据和索引分别存储
②MYISAM中的索引通过字段组织二叉树，叶子节点存储的是数据的地址，InnoDb存储的是真正的数据，所以MYISAM中的索引其实都是需要回表操作的但是它的回表操作是通过地址直接获取到数据不像InnoDB中通过主键索引去获取数据效率低下
③InnoDB中的索引分为聚簇索引和非聚簇索引使用聚簇索引可以快速查找到数据不需要回表，非聚簇索引借助聚簇索引查找数据。聚簇索引要求InnoDB中必须要有主键，MYIAM是可以没有主键的

索引的增删改查以及MYSQL8.0中索引的新特性

索引的增删改查

索引的创建

索引其实可以在两个时机创建第一个时在建表的时候创建索引，第二个实在建表之后创建索引

建表的时候创建索引

####### 隐式创建

其实对于主键索引（也就是我们所说的聚簇索引）在声明表的主键的时候就已经创建，而对表的唯一约束则时和唯一索引绑定，我们在声明唯一约束的时候就已经创建了一个唯一索引

####### 显式创建索引

在建表的时候显式创建索引其实只需要在建表语句的最后和加约束一样加上创建索引的语句就可以：
eg：
create table table_name(
id int primary key,
name char(10) unique,
index index_name(col1,col2)
);

在建表之后创建索引

在建表之后创建索引的方式有两种：
①使用alter table 语句创建
eg:alter table add index index_name(col1,col2);
②使用create index index_name on table_name(col1,col2);

索引的删除

索引的删除也有两种方式：
①通过alter table语句删除
alter table table_name drop index
index_name;
②通过drop index… on…语句删除
eg：drop index index_name on table_table_name;

如何查看一个表中的索引

eg:show index from table_name

MYSQL8.0中索引的新特性

隐藏索引

隐藏索引其实可以看作时间索引开启或者关闭，用于测试有索引和没有索引时数据库的查询xing’neng

降序索引

在mysql5的版本中其实就有降序索引的功能但是其底层组织索引还是使用升序索引只是在访问时反着访问而已，但是在mysql8.0之后真正的将索引数据降序组织

索引的分类

按照逻辑功能分类

按照逻辑功能分类索引可以分为主键索引，唯一索引，全文索引。普通索引

按照物理组织方式分类

按照物理组织方式对索引分类可以分为聚簇索引和非聚簇索引

按照索引的列数

按照索引的列数分类可以将索引分为单列索引和多列索引

索引创建的原则

什么情况下可以使用索引

①针对列来说
对于重复低的列（一个可参照的数据时%33），具有唯一约束的列，使用distinct的列，列的类型占用字节较小的，对于字符串类型的值需要对字符串的前缀建立索引
②针对语句来说
where语句中经常使用的列（这里的where手的时select，update和delete），group by和order by，对于连表查询的字段建立索引前提是两个连表查询的字段类型要一致
③针对联合索引来说
对于经常一起使用的字段建立索引，需要注意最左前缀pi’pei

什么情况下不可以使用索引

从表的角度来说：
对于数据量较小的表不需要建立索引，对于经常更新的表不需要建立索引
从列的角度来说：
数据上来考虑对于重复度较高(%33),无序的数据不需要建立索引对于where使用不到的字段不建立索引

不要创建过多的索引

①删除冗余的索引
②删除使用不到或者使用少的索引

数据库性能分析

数据库调优的过程

①首先得查看服务器是否存在性能忽高忽低的情况如果存在就需要加上缓存。
②开启慢查询日志，查看慢sql，对失去了语句使用性能分析工具惊醒分析，查看是sql执行的等待时间过长还是执行时间过长。如果是等待时间过长则需要配置服务器参数。如果是执行时间过长则查看是否是是表结构问题，索引的问题还是join语句过多导致的。
③如果性能还是不好则试着数据库分库分表，主从复制读写分离。

性能分析工具的使用

慢查询日志

①慢查询日志的开启和关闭
使用“slow_query_log”参数开启和关闭
②慢查询日志的两个指标一个文件和一个工具
“long_query_time”:表示最大查询时间
“min_examing_rows_limit”：标识查询的条数
“slow_query_log_file”指定慢查询日志文件
使用mysqldumpslowk可以分析慢查询日志
③如何查看慢查询
使用：show status like "Slow_queries"查看慢查询语句

Explain

explain分析工具用于分析一条sql语句的执行计划，一条查询语句再复杂最终也得对每一个表进行访问，所以explain的每一条记录代表着一个表的访问方式，每一个id代表着一条查询语句的执行。它可以使用explain或者describe关键字来查看

使用explain的注意点

在sql5.6之前explain只能对select语句使用explain在5.6的后续版本中就可以使用explain查看delete，insert和update
在5.7版本explain中的patitions和filtered需要使用系统表查看，在后续版本中直接可以在explain查看

四种查看方式

它的查看方式分为comand窗口查看，可视化工具查看或者使用json和Tree的形式查看

各列的信息

由于explain是针对表的访问方法确定的所以有一下分类：
①id和select_type为一类表示查询语句的编号和查询类型
②涉及到表的分别有table，type，patitions为一类标表示表名称和表的访问类型，表分区的具体信息
③possible_keys,key,key_len和ref分为一类表示可能使用的索引，使用的索引，索引的长度和等职比较的具体信息
④rows,filter分为一类用于表示是否为范围一共访问到的记录条数和过滤的百分比
⑤extra单独为一类表示额外的信息

####### 查询的信息

​

######## id

一个id用于表示一条查询语句，id的访问优先级从大到小1，如果优先级相同则从上到下

######## select_type

select_type用于表示本查询在总体查询中是什么样的地位：
simple：简单查询
primary:
union:联合结果
union_result:联合结果产生去重的临时表
dependent_union：使用in和exist会产生相关的联合
subquery：子查询
dependent_subquery：相关子查询

####### 表的信息

######## table：

这条记录中被发访问的表名

######## type：

表的访问方式，主要分为一下几种：
system：表中只有一条记录，并且所使用的存储引擎提供的数据是精确的
const:采用主键索引或者唯一索引匹配一个常量
eq_ref:使用主键索引或者唯一索引联表查询
ref：使用普通索引进行常量查询
unique_subquery:使用唯一索引或者主键索引子查询
range：使用索引匹配方范围数据
index:使用全索引扫描
all:全表扫描

######## patitions

所使用的表是否是分区表，分区表的具体信息

####### 索引的信息

######## possible_keys

这个查询中可能使用到的索引，这个索引数量越多越不好因为查询优化器需要诸葛排除选出最好索引，可以使用的索引太多了导致判断的时间过长

######## key

真正使用到的索引

######## key_len

使用到的索引的长度，这个长度的计算首先根据不同的字符集确定初始长度，再加上两个字节代表边长字段长度列表再叫上一个字节代表null值列表

######## ref

这个用于判断等职匹配的的值的基本信息可以和查询的类型一起判断

####### 访问条数

######## rows

筛选出来访问到的记录数量

######## filtered

过滤率

####### 额外的信息

​

######## extra

这个用于记录整个sql语句中执行的额外信息蕾仕于一句提示，十分重要。
①普通的比如说from中没有东西那么就不提示信息，如果使用到where直接查询没有使用到索引或者回表的就会提示using where，如果条件不成立就会使用impossible where
②关于索引
using index:使用到覆盖索引
using index condition:使用到索引条件吓退
③几种不好的情况：
using buffer:表连接没有使用到索引创建了缓冲
file sort：排序没有使用到索引
using temporary：使用到临时表，使用distinct，gruop by，union可能会使用lin’shi’biao

ProFile

ProFile用于查看sql语句的执行成本
①开启和关闭ProFile
设置profiling开启关闭profile
②查看sql执行成本
show profiles
show profile
show profile for query index

trace

trace用于查看sql执行的全过程包括优化器优化的过程

last_query_cost

用于查看最后一条查询语句的成本

数据库调优策略

数据库的优化策略中有一个三角形，分为四个层次上的优化，优化成本越低起的作用反而越好。这四个层次分别是sql以及索引优化，表结构优化，数据库系统参数配置和硬件优化

查询优化和索引优化

查询优化

关联查询优化

####### 外连接优化

外查询优化语句我们应该在被驱动表上添加索引，这样可以将被驱动被的数据表访问类型变成ref提升整体的连接查询效率

####### 内连接优化

①如果连接的表都没有或者都有索引，那么内连接遵循小表驱动大表的原则。
②如果有一个表的连接字段上有索引那么该表就会被当作是被驱动biao

####### 关联查询的底层原理

关联查询的底层原理是Nested Loop Join（嵌套循环连接）这个优点i像我们学习编程中的循环语句。
NLJ分为如下几种：
①SNLJ（Simple Nested Loop Join）
简单的嵌套循环连接就像编程语言中的嵌套循环一样先从外层循环找一条记录，然后取出连接字段，到内层表当中逐个匹配如果匹配上了就当作是一条记录。
②BNLJ（Bolck Nested Loop Join）
简单的嵌套循环连接每次取出外层的一条记录对比内层记录之后就会释放内存数据等到下一次接着加载内层循环的数据，块嵌套循环连接其实在其基础上做了优化，在内存中多了一个join buffer，使得外层循环中多条记录重用内层循环的数据，不用每次都加载，一定程度上提升了性能
③INLJ（Index Nested Loop Join）
索引嵌套循环优化其实可以看组是内层连接使用到了索引复杂度降低到了logn
④在MySQL8.0.20之后的版本中表连接使用Hash Join的方式进行

子查询优化

子查询其实就是一条查询语句能够做到平常多条查询语句的查询效果，也就是内查询的结果是外查询提供给外查询使用，这样可以有效减少数据库交互的次数。但是内查询在使用上也存在很多缺点：
①内查询会生成临时表
②内查询生成的临时表没办法使用索引

优化建议：尽量少写子查询，要使用子查询的地方尽量使用连接查询来优化

order by,group by和limit优化

o

####### Order by

①order by上其实是可以使用索引的，当排序和过滤的字段一致使用单列索引，当不同时使用多列索引需要注意最左前缀匹配原则
②当没有办法使用到索引时只能使用filesort那么我们就该调节对应的数据库调优参数将filesort的空间增大

####### Group By

①gruop by语句其实也是可以使用索引的，当我们分组和过滤的列一致时使用但开裂索引，当不一致时使用多列索引需要注意最所前缀匹配原则
②当无法使用索引时也需要设置对饮的数据库调优参数
③只有包含分组函数时使用having否则一律使用where

####### Limit

使用到Limit时我们不应该直接使用类似于
select * from employees limit 1000,2
对应的优化策略有两条
①当所查询的列能够直接使用索引时可以使用索引定位
select id from employees limit 10000,1
②当对应的列使用不到索引时我应该直接定位
select * from employees where id > 20000 limit 2

索引优化

索引失效的案例

####### 涉及到表的

①表中的字符集应该和数据库的字符集保持一致不然使用时十分容易出现乱码导致索引失效
②主键插入的时候应该保持递增排序，如果老是从中间插入主键，那么聚簇索引底层不断出现页分裂不断调整数据也和索引页的数据这样性能很差

####### 涉及到计算，类型转换，函数的

①过滤条件中使用计算函数类型转换都会时索引失效
②当使用不等于时索引失效，比如说<>,!=,NOT NUll等等
③使用like “%abc”索引失效
③使用OR连接条件时条件中只要有一个使用不到索引那么其他列都使用不索引

####### 涉及到多列索引

①全职匹配可以直接使用到索引覆盖，不用回表
②最左前缀原则：索引都是逐级排序的，当使用不到前一段索引后一段页使用不到。
③碰到范围列时索引当前列可以使用后面的列失效

覆盖索引

在InnoDB的索引从物理实现上细分为聚簇索引，非聚簇索引两种。聚簇索引是通过主键（如果没有主键就找唯一字段，如果没有唯一字段就找数据行隐藏列中的row_id）排序形成的一个索引，其底层存放的时所有的数据记录，非聚簇索引（页叫做辅助索引或者二级索引）底层存放的时主键值和本列的值，如果时多列索引（也叫联合索引）底层存放的就是声明索引的多列值和主键值。而我们所提及的覆盖索引的意思就是我们使用二级索引并且访问到的字段就是用于组织索引的字段，不需要通过主键再回表查询。

索引下推

索引条件吓退发生在二级索引，多发生在多列索引当中，当使用道二级索引并且需要回表，我们就可以使用到索引底层那些我们不查询的数据来过滤数据达到回表时查询数据尽量少的目的提升语句的性能

给字符串添加索引

再阿里巴巴开发规范里头指出如果给字符串字段添加索引必须要指明索引前缀的长度，也就是说我们不是给整个字符串添加上索引给字符串的前多少个字符加上前缀（至于是多少个前缀我们判断规则如下如果加多了浪费空间如果加少了达不到索引的效果所以我们应该再选取前缀尽量少区分度尽量高的一个维度上选择）

唯一索引和普通索引的区别

①在查找数据上的区别
在查找数据上唯一索引和普通索引的区别就是唯一索引找到一个数据直接返回，普通索引找到一个数据之后还需要进行重复值的判断，不过这点对数据库的性能损耗来说可以忽略不记
②在修改数据上的区别
在数据库修改上最大的区别就在于普通索引可以使用change buffer而唯一索引不可以使用change buffer。change buffer是一块缓存空间如果我们修改的数据页存在数据库缓存当中，直接修改数据页，如果不存在则记录这个修改操作当修改的页被加载到缓存当中是由一个merge线程完成修改的操作，在数据库服务器关闭的时候merge线程也完成未完成的更新操作。
基于以上这个性能差距我们应该在确定了数据唯一性的场景中使用普通索引，当业务需要数据库来保证数据唯一性的情况下使用唯一索引

其他琐碎的优化点

select *

我们应该减少select *的使用，理由有如下两点：
①数据库在执行select 语句是需要将“”转化为各个字段
②使用select * 意味着无论如何操作我们都需要回表除非在数据表的所有字段上建立一个联合索引但是这显然不是很现实

count(*) count(1) count(字段)

①count(*)和count（1）：这两者的性能基本无差别，但是基于不同的存储引擎还是存在差别的，比如收使用MYISAM存储引擎时底层会使用一个字段来记当前表中的记录数，查询时只需要查询这个字段的值就行其时间复杂度时O（1），但是使用InnoDB的时候每一次执行count
操作都会真真切切的去数据库中去找遍历一遍所以时间复杂度时O(n)
②count(具体字段)：在执行这个操作的时候我们必须要去表中寻找一遍了查找到底有多少个记录，查询优化器会选择成本最少的索引去查询

Exist和In

Exist和In适合使用的场景，需要区分表的大小来定，如果内表大于外表应该使用Exist,如果内表小于小标则可以用将内表当作一个集合来处理，使用In

多使用Commit

如果没有开启数据库事务自动提交功能，我们在完成一个事务操作的时候应该及时使用commit释放资源，比如说事务底层需要使用的锁，redo,undo,和回滚段

数据库表结构设计和优化

数据表设计原则

范式

####### 键和属性

各种建：
超键：可以唯一识别一个数据行都可以称之为时超键
候选键：候选键是烈属最少地超键
主键：可以在候选键当中挑选出一个字段座位表的主键
外键：A表中的一个字段不是A的主键但却是B表的主键
属性：
主属性：主键字段
非主属性：非主键字段

####### 数据库表设计的六大范式

范式是什么：数据库库表涉及的原则，范式向下兼容，并且遵从的范式越高数据库比表的冗余度越低

######## 第一范式

第一范式规定数据库表的所有字段都是原子的不可再分的

######## 第二范式

第二范式在第一范式的基础上规定数据库表必须有一个主键用于唯一标识一个数行，并且所有的非主键字段都必须完全依赖于主键

######## 第三范式

第三范式规定所有非主属性字段都必须直接依赖于主键字段而不能传递依赖于主键

######## BC（巴斯歌德范式）

候选键只有一个或者存在多个候选键但是这个多个候选键都是单属性的

######## 第四范式

要明白第四范式首先得提出一个概念叫做多值依赖，多值依赖分为平凡的多值依赖和非平方的多值依赖，平凡的多值依赖指的是一个字段和另一个字段有1对多的关系，非平凡的多值依赖指的是存在字段A，B，CA和B，C都存在一对多的关系。第四范式中不能存在非平凡多值依赖

######## 第五范式（完美范式）

所有的 连接依赖都由候选键发出

####### 反范式化

我们设计数据库表应该以业务优先的原则设计，不能够单单考虑数据的冗余度。所以我们在设计数据库表的时候还要反范式化设计这样增加冗余字段可以增加数据库的性能。
但是冗余字段页存在一定的问题：
一个字段更新了它的冗余字段也要更新，这样难以维护并且还会有一定的性能损耗，还会占用一定的空间。所以设计数据库冗余的时候我们应该遵顼冗余字段对是我们真实需要的并且不经常更新的。

E-R图的使用

####### 三要素

当数据库表设计比较复杂的时候我们可以借助E-R图作为工具设计出表各个实体属性以及关系，观察我们设计中存在的逻辑问题，再将它转化为数据表。E-R图的三要素包括实体，属性和关系

######## 实体

实体的特点就是可以独立存在。尸体可以强实体和若实体，强实体就是不依赖于其他实体单独存在。弱实体就是需要依赖于存在。用正方形标识

######## 属性

一个实体上不可以再分的特性可以抽象为一个属性。用椭圆形标识

######## 关系

关系就是各个实体之间的关系，可以分为一对一的关系，一对多的关系，多对多的关系。菱形来标识

####### PowerDesigner

####### E-R图转化为数据表

首先看转换关系：
一个实体或者一个多对多的关系转换为一张表，属性转化为字段，有一对一和一对多的关系可以转化为i外连接和外键

数据表主键的设计

①设计的原则
主键的式设计原则是唯一并且递增排序
②不推荐使用：
自增主键：
自增主键存在很多问题，比如说可靠性底（在8.0之前存在主键回溯），安全性（可以通过怕从手段轻易的得到判断数据信息），性能差（需要数据库生成主键降低数据库的性能），交互多（数据库生成主角按之后要给客户端客户端确立之后存入数据库），局部唯一性（比如说连锁店各个店都有自己的自增主键但是等到整合所有连锁店的数据时就会存在主键重复的问题）

UUID
UUID其实是一个时间+始终序列+MAC地址的一个组合所以很好的实现了唯一性但是它并没有递增排序这样插入很容易引起底层页分裂性能很差

业务字段
如果直接使用业务字段来构建主键，我们很难保证业务字段是否存在，是否改变后期很难调整

③推荐使用
例如淘宝使用的主键设计：时间 + 去重 + 用户id的一个片段这样很好保证唯一性和自增

变化后的UUID，UUID的时间时倒着存放的我们可以调整它变为时间正序这样很好保证自增

如果使用的不是MYSQL8.0版本的话我们可以使用时间 + 用户ID的形式作为主键局部在申请主键先到主库中查询当前ID分配到哪然后根据这个值生成

数据库参数配置

硬件优化

分库分表

当数据量大的时候一个数据库的性能达到瓶颈我们就想着对数据库分库分表。
分库：
根据不同的业务将不同的数据库表分到不同的数据库中，降低单库的压力
分表：
水平分表：
将表中的记录拆分成不同的范围放到不同表中，存在的问题就是有的时候涉及到两张表的数据很难搞定
垂直分表：
将表字段拆分，这样做的坏处就是查询的字段过多的时候会有很多的表连接所以在拆分时应该讲究点

主从复制，读写分离