mysql闲谈

FanZheGOGOGO

已于 2024-04-07 10:40:33 修改

阅读量1.5k

点赞数 16

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-04-02 09:49:07 首次发布

本文链接：https://blog.csdn.net/ss977/article/details/137258603

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

如何定位慢查询

1、测试环境压测时，有的接口非常慢，响应时间超过2秒以上。当时系统部署了运维的监控系统Skywalking，在展示报表中可以看到是哪儿个接口慢，可以看到SQL具体执行时间。
2、如果没有类似的监控系统，在Mysql中也提供了慢日志查询功能，在mysql系统配置文件中开启慢日志的功能，设置超过多少时间记录到一个日志文件中，我记得配置的是2s，只要sql执行时间超过2s就会记录到日志文件中。

explain type

null（没有使用到表）、system（mysql内置表）、const（根据主键查询）、eq_ref（根据主键索引查询或唯一索引查询）、ref（索引查询）、range（走的索引but范围查询）、index（索引树扫描）、all（全盘扫描）

慢SQL如何分析

采用mysql自动执行计划explain来查看执行情况
通过key和key_len检查是否命中索引，如果本身已经添加了索引，可以判断索引是否失效
通过type关键字查看sql是否有进一步的优化空间，是否存在全索引或全盘扫描
通过extra建议判断，是否出现回表，如果出现可以尝试添加索引或修改返回字段

什么是索引

mysql高效访问数据的数据结构（有序）
提高检索数据的效率，降低数据库io成本（你需要全表扫描）
通过索引对数据进行排序，降低数据排序成本，降低CPU消耗

索引底层数据结构

Mysql的InnoDB引擎采用B+树的数据结构来存储索引
· 阶数更多，路径更短
· 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储行数据
· B+便于扫库和区间查询，叶子节点是双向链表（B树非叶子和叶子都会存放数据）

什么是聚簇索引什么是非聚簇索引

聚簇索引（聚集索引）：数据与索引放到一起，B+树叶子节点保存了整行数据，有且只有一个
非聚簇索引（二级索引）：数据与索引分开存储，B+树叶子节点保存对应主键，可以有多个

回表查询

通过二级索引找到对应的主键值，到聚集索引中查找整行数据

覆盖索引

覆盖索引指的是查询使用了索引，返回的列，必须在索引中全部找到。
使用id查询，直接走聚簇索引，一次索引扫描，直接返回数据，性能高
如果返回的列没有创建索引，可能触发回表查询，尽量避免使用select*

Mysql超大分页怎么处理

问题：数据量较大，limit分页查询，需要对数据进行排序，效率低
解决方案：覆盖索引+子查询。先分页查询数据id字段，确定id之后使用子查询过滤，只查询这个id列表中的数据就可以了，查询id的时候，走的覆盖索引，提升效率。

select * 
from tb_sku t, 
	(select id from tb_sku order by id limit 900000000, 10) a
where t.id = a.id;

索引创建原则

~ 数据量较大，且频繁查询的表
~ 常作为查询条件、排序、分组的字段
字段内容区分度高
内容较长，使用前缀索引
~ 尽量联合索引
~ 要控制索引数量
如果索引列不能存储null值，请在创建表时使用not null约束

什么情况下索引会失效

~ 违反最左前缀法则，索引失效
索引abc_index:(a,b,c)，只会在where条件中带有(a)、(a,b)、(a,b,c)的三种类型的查询中使用。其实这里说的有一点歧义，其实当where条件只有(a,c)时也会走，但是只走a字段索引，不会走c字段。
~ 范围查询右边的列，索引失效
~ 字符串类型的数值不加单引号，索引失效（类型转换）
~ 以%开头的like模糊查询，索引失效
~ 不要在索引列上进行运算操作，索引失效
~ 索引包含有 NULL 值的列，索引失效（索引不会包含有 NULL 值的列）

sql优化

表设计优化，数据类型选择
索引优化，索引创建原则
sql语句优化，避免索引失效，避免使用select *
主从复制、读写分离、不让数据的写入影响读操作
分库分表
答：sql优化的话，建表使用索引，sql语句的编写、主从复制、读写分离，数据量比较大的话，可以考虑分库分表。建表的时候参考阿里开发手册《嵩山版》，比如，定义字段需要结合字段含义选择合适类型，如果是数值的话，像tinyint、int、bigint 根据实际情况选择。如果是字符串，char和varchar（可变长度）或者text类型。使用索引的时候，满足创建索引原则。sql语句，select 必须指明字段，不直接使用select * ，注意sql语句，避免索引失效写法，如果是聚合查询，尽量用union all 代替 union（多一层过滤，效率低）表关联尽量使用innerjoin 不要使用 left right ，必须使用的话最好小表驱动大表。

事务

特性

原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）
A向B转账500元，转账成功，A扣500，B加500，原子性操作体现在要么都成功，要么都失败。
转账过程，数据要一致，A扣除500，B必须增加500
在转账过程，隔离性体现在不能受其他事物干扰
事务提交以后，要把数据持久化（落盘）

并发事务带来的问题

脏读：一个事物读到领一个事务没有提交的数据
不可重复读：一个事物前后读取同一条记录，两次读取数据不同
幻读：一个事物按照条件查询数据，没有对应数据行，在插入数据时，又发现这行数据存在。

解决问题：隔离级别默认可重复读

（RU）未提交读啥也没解决
（RC）读已提交脏读
（RR）可重复读脏读、不可重复读
（SB）串行化脏读、不可重复读、幻读

undo log和redo log的区别

undo log：逻辑日志，记录与实际操作语句相反的操作。事务回滚时，通过你操作恢复原来的数据
redo log：记录数据页的物理变化，服务宕机可以用来同步数据
redo log 保证事务的持久性 ，undo log保证事务的 原子性 和 一致性

事务中的隔离性是如何保证的

锁：排他锁，一个事务获取了一个数据行的排他锁，其他事物就不能在获取改行的其他锁
mvcc：多版本并发控制

MVCC

多版本并发控制：一个数据的多个版本，使得读写操作没有冲突。
底层实现分为三个部分：隐藏字段、undo log、readView
隐藏字段：DB_TRX_ID（记录每一次操作的事务id，自增）、DB_ROLL_PTR（回滚指针，指向上一个版本的事务版本记录地址）、DB_ROW_ID（隐藏主键）
undo log：
回滚日志—存储老版本数据
版本链—多个事务并行操作某一行记录，记录不同事物修改数据的版本，通过roll_pointer指针形成一个链表
readView：解决事务查询选择版本的问题，在内部定义了一些匹配规则和当前一些事务id判断该访问哪儿个版本的数据，不让的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是RC隔离级别，每次之快照读生成readView，如果是RR隔离级别，仅在事务中第一次执行快照读时生成readView，后续复用。

主从同步原理