读书笔记-高性能MySQL中获取的额外干货

最新推荐文章于 2024-07-31 06:19:57 发布

猴子饲养员

最新推荐文章于 2024-07-31 06:19:57 发布

阅读量351

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/youzi_yun/article/details/85808920

版权

数据库专栏收录该内容

7 篇文章 3 订阅

订阅专栏

MySQL逻辑架构

最上层服务: 连接处理,授权认证,安全等
第二层架构：包含了大多数的核心服务功能，包括：查询解析、分析、优化、缓存、内置函数，所有跨存储引擎的功能都在这一层实现，比如：存储过程、触发器、视图等
第三层架构：存储引擎，负责MySQL的数据存储和提取

锁

读写锁

共享锁/读锁
排它锁/写锁：优先级高于读锁，一个写请求可能会被插在读锁队列前面

锁粒度

表锁：alter table 之类语句会使用表锁
行级锁

事务

隔离级别

READ UNCOMMITTED 未提交读，事务中的未提交修改对其他事务可见，容易脏读，一般不用
READ COMMITTED 提交读/不可重复读
REPEATABLE READ 可重复读（MySQL默认事务隔离级别），引起幻读（当某个事务在读取范围内数据时，另一事务在范围内插入了新数据，某事务再次读取时，会产生幻行），MVCC解决了幻读的问题
SERIALIZABLE 可串行化，最高的隔离级别，

多版本并发控制 MVCC

MVCC只存在于可重复读、提交读两个隔离级别下
乐观并发控制、悲观并发控制
MVCC是通过在每行记录后面保存两个列来实现的（行的创建时间系统版本号，行的过期/删除时间系统版本号），每开始一个新的事务，系统版本号就会递增，事务的版本号就是该事务开始时刻的系统版本号。
以InnoDB为例：

SELECT：只查找版本号小于等于当前事务的数据行；行的删除版本要么未定义，要么大于当前事务版本号。

INSERT：为新插入行保存当前系统版本号为行版本号。

DELETE：为删除行保存当前系统版本号为行删除标识。

UPDATE：为新插入行保存当前系统版本号为行版本号，同时保存当前系统版本号为原来行的行删除标识。

还有啥

加快alter table 操作速度

常见的alter方法都是新建空表，从旧表中查出数据插入新表，然后废除旧表
其实并不是所有的alter操作都会引起表重建。
列的默认值存在表的.frm文件中，可以直接修改这个文件，这个语句会直接修改.frm文件而不涉及表数据

# 快速修改列默认值方法：
alter table film alter column end_time set default 5;

修改表默认编码方式

不推荐，只对新的字段生效，老的字段不会做改动
ALTER TABLE tb1 DEFAULT CHARACTER SET=utf8mb4 COLLATE utf8mb4_general_ci; 


不推荐，只单独修改表里面某个字段的字符集类型不推荐： 
ALTER TABLE tb1 change c c varchar(50) CHARACTER SET utf8mb4 not null default 'only for test' ;

推荐：这种写法老的字段也修改成utf8mb4：
ALTER TABLE tb1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

修改注释

修改注释实际只修改.frm文件，不会改动ibd文件的，操作执行的很快。

alter table tb_name modify `column_name` default null comment '编号***'

alter table tb_name comment '用户表'

分页优化

常见的分页写法
select * from students limit 10000,20;  
# 这样会将查出的10000行丢弃掉

优化：
可以取前一页的最大行数的id，然后根据这个id来限制下一页的起点。

例如：上一页最大的id是87654321。sql可以采用如下的写法：
select id,name from students where id> 87654321 limit 20

MYSQL会在索引中储存null值，Oracle不会
MySQL限制了每个关联操作最多只能有61张表，如果希望查询快且并发性好，单个查询最好在12个表以内做关联
缓存表对优化搜索很有效，对于缓存表，可以使用不同的存储引擎，比如MyISAM，会得到更小的索引占用空间，还可以全文搜索。

MySQL查询执行路径

客户端发送一条查询给服务器；
服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；mysql8取消了查询缓存(在查询缓存打开的情况下，每一张表进行了增删改之后都要检查缓存，看看是否需要删除记录，这对相应的操作性能有影响)
分析器（词义分析，确认是查询语句，以及哪个表；语义分析：语法检查）
优化器（确认连表顺序，索引使用 1、根据搜索条件，找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价，找出成本最低的那一个）
执行器（先判断当前登录用户是否有权限访问，然后再执行。如果是全表扫描的话就会调用InnoDB执行引擎调取第一行，记录之后调取“下一行”接口……直至找到；如果是通过索引查找，对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。）
将结果返回给客户端。