Mysql是怎样运行的–下
查询优化
explain
optimizer_trace
查看是否开启
SHOW VARIABLES LIKE 'optimizer_trace';
开启功能
SET optimizer_trace="enabled=on";
查看具体的优化过程
SELECT * FROM information_schema.OPTIMIZER_TRACE;
这个OPTIMIZER_TRACE表有4个列，分别是：
- QUERY：表示我们的查询语句。
- TRACE：表示优化过程的JSON格式文本。
- MISSING_BYTES_BEYOND_MAX_MEM_SIZE：由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数。
- INSUFFICIENT_PRIVILEGES：表示是否没有权限查看优化过程，默认值是0，只有某些特殊情况下才会是1，我们暂时不关心这个字段的值。
完整的使用步骤如下
1. 打开optimizer trace功能 (默认情况下它是关闭的):
SET optimizer_trace="enabled=on";
2. 这里输入你自己的查询语句
SELECT ;
3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程
SELECT * FROM information_schema.OPTIMIZER_TRACE;
4. 可能你还要观察其他语句执行的优化过程，重复上面的第2、3步
5. 当你停止查看语句的优化过程时，把optimizer trace功能关闭
SET optimizer_trace="enabled=off";
化过程大致分为了三个阶段：
prepare阶段
optimize阶段
execute阶段
于单表查询来说，我们主要关注optimize阶段的"rows_estimation"这个过程
对于多表连接查询来说，我们更多需要关注"considered_execution_plans"这个过程
InnoDB的Buffer Pool（缓冲池）
InnoDB的设计者为了存磁盘中的页，在MySQL服务器启动的时候就向操作系统申请了一片连续的内存，他们给这片内存起了个名，叫做Buffer Pool。
默认情况下Buffer Pool只有128M大小。当然如果你嫌弃这个128M太大或者太小，可以在启动服务器的时候配置innodb_buffer_pool_size参数的值，是以字节为单位的
需要注意的是，Buffer Pool也不能太小，最小值为5M(当小于该值时会自动设置成5M)。
Buffer Pool的存储结构
我们就把每个页对应的控制信息占用的一块内存称为一个控制块吧，控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块被存放到 Buffer Pool 的前面，缓存页被存放到 Buffer Pool 后边，所以整个Buffer Pool对应的内存空间看起来就是这样的：
空闲页存储–free链表
把所有空闲的缓存页对应的控制块作为一个节点放到一个链表中，这个链表也可以被称作free链表
为了管理好这个free链表，特意为这个链表定义了一个基节点，里边儿包含着链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。这里需要注意的是，链表的基节点占用的内存空间并不包含在为Buffer Pool申请的一大片连续内存空间之内，而是单独申请的一块内存空间。
有了这个free链表之后事儿就好办了，每当需要从磁盘中加载一个页到Buffer Pool中时，就从free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上（就是该页所在的表空间、页号之类的信息），然后把该缓存页对应的free链表节点从链表中移除，表示该缓存页已经被使用了
脏页（修改后的数据）存储–flush链表
我们修改了Buffer Pool中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页，我们创建一个存储脏页的链表，凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中，因为这个链表节点对应的缓存页都是需要被刷新到磁盘上的，所以也叫flush链表，数据结构与free链表一样
使用Buffer Pool
1、读取时的Hash处理：
以用表空间号 + 页号作为key，缓存页作为value创建一个哈希表，在需要访问某个页的数据时，先从哈希表中根据表空间号 + 页号看看有没有对应的缓存页，如果有，直接使用该缓存页就好，如果没有，那就从free链表中选一个空闲的缓存页，然后把磁盘中对应的页加载到该缓存页的位置。
2、修改数据时脏页处理：
从flush链表中异步同步脏页数据，这种刷新页面的方式被称之为BUF_FLUSH_LIST
从LRU链表的冷数据中刷新一部分页面到磁盘，方式被称之为BUF_FLUSH_LRU
3、查看状态
SHOW ENGINE INNODB STATUS语句来查看关于InnoDB存储引擎运行过程中的一些状态信息，其中就包括Buffer Pool的一些信息
LRU链表的管理
缓存的页占用的内存大小超过了Buffer Pool大小要进行删除不常用的页
简单设计：
只要我们使用到某个缓存页，就把该缓存页调整到LRU链表的头部，这样LRU链表尾部就是最近最少使用的缓存页喽
但是有两种情况会导致缓存命中大大降低：
1、预读功能
读取一个区，另一个区也会预先读取BufferPool
读取一个区的连续13个页，整个区也会读取到BufferPool
2、全表扫描
这两种情况可能并不是常用数据但是会导致很多BufferPool的数据被删掉
设计InnoDB的大佬把这个LRU链表按照一定比例分成两截，分别是：
一部分存储使用频率非常高的缓存页，所以这一部分链表也叫做热数据，或者称young区域。
另一部分存储使用频率不是很高的缓存页，所以这一部分链表也叫做冷数据，或者称old区域。
我们可以通过查看系统变量innodb_old_blocks_pct的值来确定old区域在LRU链表中所占的比例
SHOW VARIABLES LIKE 'innodb_old_blocks_pct';
默认情况下，old区域在LRU链表中所占的比例是37%
针对上面两种情况：
1、当磁盘上的某个页面在初次加载到Buffer Pool中的某个缓存页时，该缓存页对应的控制块会被放到old区域的头部。这样针对预读到Buffer Pool却不进行后续访问的页面就会被逐渐从old区域逐出，而不会影响young区域中被使用比较频繁的缓存页。
2、针对全表扫描时，在进行全表扫描时，虽然首次被加载到Buffer Pool的页被放到了old区域的头部，但是后续会被马上访问到，每次进行访问的时候又会把该页放到young区域的头部
在对某个处在old区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会被从old区域移动到young区域的头部，否则将它移动到young区域的头部。
时间间隔由一下参数决定：
SHOW VARIABLES LIKE 'innodb_old_blocks_time';
更进一步优化LRU链表：只有被访问的缓存页位于young区域的1/4的后边，才会被移动到LRU链表头部，这样就可以降低调整LRU链表的频率，从而提升性能。
还有其他的需要补充
事务
ACID
Atomicity：要不全部成功，要么全部失败. 事务是原子的，要么全部执行成功，要么全部失败。如果事务中的任何一部分失败，系统应该回滚到事务开始前的状态，以保持数据库的一致性。
Isolation：现场A对线程B不可见, 事务的执行应该相互隔离，一个事务的执行不应该影响其他事务的执行。这是通过隔离级别来控制的，如读未提交、读提交、可重复读和串行化。
Consistency：事务执行前后数据库应该保持一致性。这包括应用事务中定义的业务规则和完整性约束。例如，数据库中的主键、外键和其他约束条件在事务执行前后都应该得到满足。
Durability：一旦事务被提交，对数据库的更改应该是永久性的，并且在数据库发生故障或系统崩溃时也应该能够恢复。
事务的状态
活动的（active）
部分提交
失败的
终止的
提交的
事务语法
1、开启事务：BEGIN [WORK];
BEGIN语句代表开启一个事务，后边的单词WORK可有可无。开启事务后，就可以继续写若干条语句，这些语句都属于刚刚开启的这个事务。
或者：START TRANSACTION;
START TRANSACTION语句和BEGIN语句有着相同的功效，都标志着开启一个事务
READ ONLY：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
READ WRITE：标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
WITH CONSISTENT SNAPSHOT：启动一致性读
可以这样写：
START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;//开启一个只读事务和一致性读
START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT //开启一个读写事务和一致性读
2、提交事务:COMMIT [WORK]
3、回滚事务：ROLLBACK [WORK]
4、自动提交
SHOW VARIABLES LIKE 'autocommit';
5、隐式提交
显示的提交：当我们使用START TRANSACTION或者BEGIN语句开启了一个事务，或者把系统变量autocommit的值设置为OFF时，事务就不会进行自动提交。
- 定义或修改数据库对象的数据定义语言（Data definition language，缩写为：DDL）。当我们使用CREATE、ALTER、DROP等语句去修改这些所谓的数据库对象时
- 隐式使用或修改mysql数据库中的表
- 事务控制或关于锁定的语句，使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句
- 加载数据的语句，比如我们使用LOAD DATA语句来批量往数据库中导入数据时
最低0.47元/天 解锁文章
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
