mysql 执行过程
连接器
MySQL中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表,MySQL权限表的验证过程为：
先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在，存在则通过验证。
通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推
缓存
MySQL的缓存主要的作用是为了提升查询的效率，缓存以key和value的哈希表形式存储，key是具体的sql语句，value是结果的集合。如果无法命中缓存,就继续走到分析器的的一步,如果命中缓存就直接返回给客户端 。不过需要注意的是在MySQL的8.0版本以后，缓存被官方删除掉了。之所以删除掉,是因为查询缓存的失效非常频繁,如果在一个写多读少的环境中,缓存会频繁的新增和失效。对于某些更新压力大的数据库来说，查询缓存的命中率会非常低,MySQL为了维护缓存可能会出现一定的伸缩性的问题,目前在5.6的版本中已经默认关闭了，比较推荐的一种做法是将缓存放在客户端，性能大概会提升5倍左右
分析器
分析器的主要作用是将客户端发过来的sql语句进行分析，这将包括预处理与解析过程，在这个阶段会解析sql语句的语义，并进行关键词和非关键词进行提取、解析，并组成一个解析树。具体的关键词包括不限定于以下：select/update/delete/or/in/where/group by/having/count/limit等.如果分析到语法错误，会直接给客户端抛出异常:ERROR:You have an error in your SQL syntax.
优化器
够进入到优化器阶段表示sql是符合MySQL的标准语义规则的并且可以执行的，此阶段主要是进行sql语句的优化
执行器
在执行器的阶段,此时会调用存储引擎的API,API会调用存储引擎，主要有一下存储的引擎，不过常用的还是myisam和innodb:
执行到存储引擎，聊一聊数据页
在操作系统中，我们知道为了跟磁盘交互，内存也是分页的，一页大小4KB。同样的在MySQL中为了提高吞吐率，数据也是分页的，不过MySQL的数据页大小是16KB，1mb 分区
一行或者多行数据便是一页
数据库的多行数据就算多列，排列开来就是一个链表。
上一页下一页链表左右节点相连
数据区？
在MySQL的设定中，同一个表空间内的一组连续的数据页为一个extent（区），默认区的大小为1MB，页的大小为16KB。16*64=1024，也就是说一个区里面会有64个连续的数据页。连续的256个数据区为一组数据区。
数据页分裂合并
其实页分裂，你可以理解为链表中间插入一条数据，假设你现在已经有两个数据页了。并且你正在往第二个数据页中写数据。
关于B+Tree，你肯定知道B+Tree中的叶子结点之间是通过双向链表关联起来的。
在InnoDB索引的设定中，要求主键索引是递增的，这样在构建索引树的时候才更加方便。你可以脑补一下。如果按1、2、3…递增的顺序给你这些数。是不是很方便的构建一棵树。然后你可以自由自在的在这棵树上玩二分查找。
那假设你自定义了主键索引，而且你自定义的这个主键索引并不一定是自增的。
这种情况，比如使用雪花算法， uuid做主键就会造成大量io替换页未知，造成页分裂的概念
上图是随机插入的数据
上图是需要整理成顺序id > id -位移1 数据
存储引擎
聊完了，数据的存储过程，执行过程，那么mysql对于存储引擎也是有区分的
常见的对比 常用的有myISAM INNODB 这两种都是B+树结构的
MyISAM 和 innodb 对比
MyISAM存储引擎的特点和应用场景
MyISAM是MySQL 5.1及之前的版本的默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS)等，但MyISAM不「支持事务和行级锁」，对于只读数据，或者表比较小、可以容忍修复操作，依然可以使用它。
特性
MyISAM「不支持行级锁而是对整张表加锁」。读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入。
MyISAM表可以手工或者自动执行检查和修复操作。但是和事务恢复以及崩溃恢复不同，可能导致一些「数据丢失」，而且修复操作是非常慢的。
对于MyISAM表，即使是BLOB和TEXT等长字段，也可以基于其前500个字符创建索引，MyISAM也支持「全文索引」，这是一种基于分词创建的索引，可以支持复杂的查询。
如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成「索引损坏」，需要执行修复操作。
Innodb数据结构
目前Mysql 用的是Innodb存储引擎，Innodb支持事务，外键，表锁，从liunx来看，数据和主键索引是分开放的。
Innodb和myisam使用的都是B+树 聊Innodb 其实主要也就是聊一下B+树
平衡二叉树
平衡二叉树是采用二分法思维把数据按规则组装成一个树形结构的数据，用这个树形结构的数据减少无关数据的检索，大大的提升了数据检索的速度；平衡二叉树的数据结构组装过程有以下规则：
（1）非叶子节点只能允许最多两个子节点存在。
（2）每一个非叶子节点数据分布规则为左边的子节点小当前节点的值，右边的子节点大于当前节点的值(这里值是基于自己的算法规则而定的，比如hash值)；
平衡树的层级结构：因为平衡二叉树查询性能和树的层级（h高度）成反比，h值越小查询越快、为了保证树的结构左右两端数据大致平衡降低二叉树的查询难度一般会采用一种算法机制实现节点数据结构的平衡，实现了这种算法的有比如Treap、红黑树，使用平衡二叉树能保证数据的左右两边的节点层级相差不会大于1.，通过这样避免树形结构由于删除增加变成线性链表影响查询效率，保证数据平衡的情况下查找数据的速度近于二分法查找
总结平衡二叉树特点：
（1）非叶子节点最多拥有两个子节点；
（2）非叶子节值大于左边子节点、小于右边子节点；
（3）树的左右两边的层级数相差不会大于1;
（4）没有值相等重复的节点;
B树(B-tree)
B树和平衡二叉树稍有不同的是B树属于多叉树又名平衡多路查找树（查找路径不只两个），数据库索引技术里大量使用者B树和B+树的数据结构，让我们来看看他有什么特点;
（1）排序方式：所有节点关键字是按递增次序排列，并遵循左小右大原则；
（2）子节点数：非叶节点的子节点数>1，且<=M ，且M>=2，空树除外（注：M阶代表一个树节点最多有多少个查找路径，M=M路,当M=2则是2叉树,M=3则是3叉）；
聊完了这些树 聊一下B+树
B+树
（1）B+跟B树不同B+树的非叶子节点不保存关键字记录的指针，只进行数据索引，这样使得B+树每个非叶子节点所能保存的关键字大大增加；
（2）B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样；
（3）B+树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。
（4）非叶子节点的子节点数=关键字数（来源百度百科）（根据各种资料 这里有两种算法的实现方式，另一种为非叶节点的关键字数=子节点数-1（来源维基百科)，虽然他们数据排列结构不一样，但其原理还是一样的Mysql 的B+树是用第一种方式实现）;
基于树的结构聊一聊mysql存储结构
MYSQL 数据存放方式
下图是 MySQL（5.7.29）在 Windows 系统下安装的数据文件目录，可以看到有如下几类文件。
Data 目录用来存放数据库相关的数据信息，包括数据库信息，表信息等。
MySQL 5.7 及之后的版本开始支持集群模式，installer_config.xml 配置文件主要用于配置单节点或集群模式。
my.ini 文件是 MySQL 服务端和客户端主要的配置文件，包括编码集、默认引擎、最大连接数等设置。MySQL 服务器启动时会默认加载此文件。
Data 目录中存放的文件如下图所示：
对 Data 目录中的文件说明如下：
- mysql、performance_schema、sakila、sys 和 world 是系统数据库，information_schema 数据库比较特殊，这里没有相应的数据库目录。
- test 是用户自定义的数据库，也就是用户自己创建的数据库。
- auto.cnf：MySQL 服务器的选项文件，用于存储 server-uuid 的值。server-uuid 与 server-id 一样，用于标识 MySQL 实例在集群中的唯一性。
- ib_logfile0、ib_logfile1 是支持事务性引擎的 redo 日志文件
- ibdata1 为共享表空间（系统表空间）。如果采用 InnoDB 引擎，默认大小为 10M 。
- ibtmp1 为存储临时对象的空间，比如临时表对象等。
db.opt
用来保存数据库的配置信息，比如该库的默认字符集编码和字符集排序规则。如果你创建数据库时指定了字符集和排序规则，后续创建的表没有指定字符集和排序规则，那么该表将采用 db.opt 文件中指定的属性。
对于 InnoDB 表，如果是独立的表空间，数据库中的表结构以及数据都存储在数据库的路径下（而不是在共享表空间 ibdata1 文件中）。但是数据中的其他对象，包括数据被修改之后，事务提交之间的版本信息，仍然存储在共享表空间的 ibdata1 文件中。
.frm
在 MySQL 中建立任何一张数据表，其对应的数据库目录下都会有该表的 .frm 文件。.frm文件用来保存每个数据表的元数据（meta）和表结构等信息。数据库崩溃时，可以用 .frm 文件恢复表结构。
.MYD和.MYI
.MYD 理解为 My Data，用于存放 MyISAM 表的数据。
.MYI 理解为 My Index，主要存放 MyISAM 表的索引及相关信息。
可以看出Myisam数据和索引是分开的
.ibd
对于 InnoDB 存储引擎的数据表，一个表对应两个文件，一个是 .frm，存储表结构信息；一个是.ibd，存储表中数据。
.ibd和.ibdata
.ibd 和 .ibdata 都是专属于 InnoDB 存储引擎的数据库文件。当采用共享表空间时，所有 InnoDB 表的数据均存放在 .ibdata 中。所以当表越来越多时，这个文件会变得很大。相对应的 .ibd 就是采用独享表空间时 InnoDB 表的数据文件
MyISAM 存储结构
同样是B+树，可以看到数据存放的叶子节点是地址 由于Myisam 存储结构是索引数据是分开的，所以底层缩影上面可以看到，存储的地址值，根据地址值去.MYD找到对应数据
MyISAM 的索引方式也叫做“非聚集索引”,之所以这么称呼是为了与 InnoDB的聚集索引区分。
InnoDB 存储结构
区别是 InnoDB 的数据文件本身就是索引文件。从上文知道,MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址，而上面从结构上面可以看到innoDB是索引和文件放在.IDB 里面的
图不是很好，大概意思可以看到，主键找到对应的值之后，叶子节点上面存放着数据，这种我们叫做聚集索引。
我再找一个好图
大概就算这样
聊一聊索引
mysql 索引分为很多，常见的
- 主键索引
- 全文索引
- -唯一索引
- 普通索引
- 联合索引
- 覆盖索引
普通索引（这个是最基本的索引）
建表时：INDEX IndexName(字段名(length))
唯一索引
要求字段所有的值是唯一的，这一点和主键索引一样，但是允许有空值。
建表时：UNIQUE INDEX IndexName(字段名(length))
建表后：CREATE UNIQUE INDEX IndexName ON TableName(字段名(length))
主键索引
一般在建表的时候自动创建或者手动选择
全文索引
建表时：FULLTEXT INDEX IndexName(字段名(length))
建表后：CREATE FULLTEXT INDEX IndexName ON TableName(字段名(length))
使用： SELECT * FROM TableName
WHERE MATCH(column1， column2) AGAINST(‘xxx′， ‘xx′， ‘x′)
这条命令将把column1和column2字段里有xxx、xx和x的数据记录全部查询出来。
组合索引
建表时：INDEX IndexName(字段名(length)，字段名(length)，…)
建表后：CREATE INDEX IndexName ON TableName(字段名(length)，字段名(length)，…)
或ALTER TABLE TableName ADD INDEX IndexName(字段名(length)，字段名(length)，…)
覆盖索引
所有字段的组合索引，加上之后会覆盖data信息，就不用回标扫描了
什么是索引
项目里面，基本听到添加索引，甚至于建立一个字典表，字典表里面存放的都是索引。索引的作用是mysql最低限度的快速找到对应字段，并且加了索引的字段，可以很大提升查询效率，那么索引是什么呢，是一种数据结构。
索引是个什么样的数据结构呢?
说了索引的作用跟是什么，那么接下来追溯下去，索引既然是数据结构，哪属于什么数据结构，索引的数据结构是跟具体的存储引擎有关的。比较多的索引为hash结构的索引，B+树索引等，我们常用的mysql InnoDB存储引擎使用的便是B+树索引。
Hash索引和B+树所有有什么区别或者说优劣呢?
既然知道索引是一种数据结构，方便查询，那么更具存储引擎不同，索引数据结构不同，那么这两种常见索引又有什么区别呢。hash索引不用说大家也知道的，hash类型的底层肯定是hash表。所以hash索引肯定是通过hash函数通过hash表返回回来键值然后再查询数据出来。至于B+树，既然是树类型的，肯定是多节点的，从树根节点去向上查询，查询子分支树获得键值然后再判断是否要查询数据出来
说了这么多，对比一下，有什么区别
第一，一个是通过hash函数查询hash表 查询数据，一个是通过平衡树查询节点，查询数据。
第二，由于hash函数查询是无序的，所以不可能覆盖查询，而树状节点，天然支持覆盖查询。
第三，hash数据查询中如果键值一对，容易出现hash碰撞，相对不稳定，而平衡树，从根部查询到节点，相对稳定。
第四，平衡树，可以存储聚族索引，而hash不行。
什么是聚簇索引
聚族索引是以索引为主键作为聚族索引，聚族索引比如存储引擎B+树里面存储的聚族索引。那么查询到这个主键索引键值，就不需要回标通过索引查询数据，而是直接拿这个数据，这个查询数据在聚族索引内。避免重复问题
举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询. 如果命中非主键的索引如果全部命中也不需要回表查询。 如果是select * from employee where age < 20，那么必然是回表查询。
建立索引的时候,都有哪些需要考虑的因素呢?
说了这么多索引的问题，总结一下，索引是为了快速找到数据，优化查询速度的，索引是数据结构，什么数据结构，取决于存储引擎，常见的是1B+平衡树1，和1Hash索引1。区别简单了解几点就可以了。InnoDB使用的便是平衡树，什么是`聚族索引1。 那么我们创建这个索引又需要考虑什么呢。
首先既然是为了优化查询的东西，必然是对某个字段查询比较频繁，比如现在有学校表。学校表id必然对应教师，学生，食堂，书馆，等等关联。在查询这个学校的信息比如按是where 学校等于学校表的某个id，那么这个频繁使用查询的必然要在id字段加上索引。我们正常开发是以判断条件比较常用的作为索引。如果需要建立联合索引的话,还需要考虑联合索引中的顺序。
联合索引是什么?为什么需要注意联合索引中的顺序?
在mysql中，MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引.
MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引
当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,以此类推.
简单一点就是犹如三个高能机器，第一个执行给第二个第二个执行给第三个。
因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面.此外可以根据特例的查询或者表结构进行单独的调整.
select name,age,school from t_school_list
那么如果创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?
这个时候就要用到mysql提供的特殊字段explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息.
这个表里面没有加索引，所以可以看到type 查询全部，key无，key长度无，rows查询7行。
添加一个普通索引
查询表示 使用了索引 index_summ
总结可以通过explain 来查看sql执行计划，进行优化。
那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?
这个问题面试官常考这个，其实很简单，
第一，使用不等于这个字段自然不会有这个索引。
第二，使用like模糊查询不会使用索引
第三，使用函数不会使用索引。
第四，使用联合索引，没命中所有索引字段自然不会用这个索引。
稍微想一下便可以记住。
简单说一下索引创建。**
中文是，操作数据库 库名称 增加索引 索引名 索引（）
alter table xxx add index index_index_name (ziduan)
其中xxx表示库名
index_index_name（）表示索引名括号里面是字段名。
聊一聊事务
什么是事务?
这个问题一般面试问的比较多，说到查询多表之间值赋值传递。都有可能用到事务，那么这个事务是什么
事务就是一系列的操作。当初程序员创建库的时候，由于查询修改数据不确定性，不一致性，所以有了事务的定义。
事务就是对数据的一系列的操作。主要为，要么全部成功要么全部失败。
一般我们再使用对库的增删改 都需要用事务模板TransactionTemplate，进行包含，或者对此方法进行@Transaction 加这个注解。
那么事务有什么特性呢
-
原子性
-
一致性
-
可持续化
-
隔离性
原子性决定了作为一个单元运行，要么全部成功要么全部失败。
比方我再对数据进行修改的时候，修改了很多次，调了很多此Service方法，那么要么全部成功，要么全部失败，避免修改出现与实际想法不符。
一致性的意思是 数据永远保持一致，不会出现中间状态。
隔离性，当一个事务再运行的时候，其他事务是看不到的。避免了我对这个数据修改，另一个指令删除。这样可能回导致数据非一致性。
持续化，数据是保存到硬盘上面的，一旦提交是不会改变的。
那么多个事务在进行会怎么样呢?
如果单个增删改其实没什么，主要是避免出现出现多个线程同时增删改，可以用代码测一下，创建多个线程同时对数据增删改。
脏读 可以读到未提交数据
一般如果不加事务模板，会出现，脏读，读到未提交的数据，
这就好像你买东西，10块钱，老板突然准备涨价，写了15块钱，准备提交，你着急买，发现15块钱就扔了15块钱买了这个商品走了，后来老板想了想不涨了，回滚了，东西还是10块，那么你读到的数据就是错的。
不可重复读
只能读到已经提交的
原理和脏读差不多，你买东西10块钱，你10块钱买了，老板相通了，卖15了。你再看就变成15了。这便是不可重复读。会导致两次查询结果不一样。
幻读
A事务读取了一个范围的内容list ,而同时B事务在此期间插入了一条数据.造成"幻觉"，突然多一条数据。数据不准确
既然多个事务有这么多问题，那么我们应该怎么处理这些问题
MySQL的提供四种隔离级别如下:
未提交读(READ UNCOMMITTED)
这就是上面所说的例外情况了,这个隔离级别下,其他事务可以看到本事务没有提交的部分修改.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).
这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用.
已提交读(READ COMMITTED)
其他事务只能读取到本事务已经提交的部分.这个隔离级别有 不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改.
REPEATABLE READ(可重复读)
可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是幻读
当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥
那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.
SERIALIZABLE(可串行化)
这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.
聊一聊事务传播机制（后面代码补充）
基于上面所有，再聊一聊优化
mysql 优化其实最大限度来说，就本人而言，是索引优化，其次再是语句优化，因为一般别人写sql工程方面不会有多复杂，一般加上索引就可以解决百分之80的问题，但是如何查看执行计划，可以通过mybatis的statement 进行拦截校验，也就是重写sql。这个是一种办法还有就是使用执行计划查看命令explain，看下执行计划，查看有没有索引，有没有走索引，先从索引来说
sql优化
-
在业务密集的SQL当中尽量不采用IN操作符，in是在数据达到1w之后才会走索引··索引不要用
-
关键词 %yue%， 由于yue前面用到了“% 模糊查询不走索引
-
二者都能使用尽量使用where （与having比较）where 先过滤（数据就少了）再分组
-
避免使用子查询，如果使用了，同上查看子查询
-
尽量避免使用select *，返回无用的字段会降低查询效率
-
尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描
这几个搞定之后，加索引，基本就搞定一般了；
然后
当语句执行时间较长时，通过日志的方式进行记录，这种方式就是慢查询的日志。
临时开启慢查询日志（如果需要长时间开启，则需要更改mysql配置文件）
在服务器运行命令
set global slow_query_log = on;
set globle log_output = file;
set global slow_query_log = off
对于记录的慢sql进行重点分析一下
然后工程方面
MybatisPlus自定义查询sql 判断时间 配置bean
/**
* SQL执行效率插件 性能分析插件
*/
@Bean
@Profile({"dev","test"})// 设置 dev test 环境开启
public PerformanceInterceptor performanceInterceptor() {
PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
performanceInterceptor.setFormat(true);//格式化语句
//performanceInterceptor.setMaxTime(5);//执行时间超过多少秒会抛出异常
return performanceInterceptor;
}
参考：https://zhuanlan.zhihu.com/p/27700617
扫一扫
692
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
