MySQL最佳实践

Proxbj

已于 2023-03-31 09:43:25 修改

阅读量540

点赞数

文章标签： java

于 2023-02-19 22:54:10 首次发布

本文链接：https://blog.csdn.net/Proxbj/article/details/129115391

版权

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

一、MySQL查询执行过程

1.MySQL分层结构

在这里插入图片描述

MySQL8.0没有查询缓存的功能了,如果频繁修改缓存,将会损耗性能
查询流程就按照分层结构就可以清楚,只要了解各个组件的各自功能就行
分析器主要分析语法和词法是否正确
优化器主要优化SQL语句

二、MySQL更新执行过程

更新主要涉及两个重要的日志,redolog(重做日志)和binlog(归档日志)

1.redolog

如果没有日志记录的话,每一条更新语句将会写进磁盘,再从磁盘找到对应的数据进行更新,将会非常损耗性能
InnonDB引擎会先更新内存,再写redolog日志,等系统空闲的时候,再将入磁盘.即使数据库宕机,恢复后也能根据redolog将未写入磁盘的更新数据写入磁盘,保证数据更新这就是InnoDB提供的crash-safe能力
redolog是固定大小的文件,如果日志文件不够写,将会先擦除一部分再写

2.binlog

binlog是server层提供的归档日志,会记录下更新的逻辑,比如将1变为2

3.两种日志区别

redolog是InnoDB提供的,支持crash-safe能力;binlog是server层提供的
redolog是循环写的,日志文件会用完;binlog是追加写的

4.更新过程

示例:给ID=2的C字段加1

查找ID=2的数据,如果内存有直接返回,没有则从磁盘查找,再返回给内存
将ID=2的C字段+1，调用执行引擎接口更新数据,并将数据更新到内存
将记录写入redolog,此时redolog处于prepare状态
告知执行器可以提交事务,写binlog日志
redolog提交事务,更新完成

两阶段提交

为什么会两阶段提交redolog和binlog呢,原因就是为了误删数据后,恢复数据能与历史数据达成一致.我们可以进行反证

假设先提交redolog,当发生了一次crash-safe,恢复后redolog记录的日志可以更新数据,但是binlog没有记录.之后使用binlog恢复删除的数据,会造成该条数据不一致
假设先提交binlog后crash,但是redolog并没有记录该条数据的更新,所以机器恢复后redolog不会更新该条数据,但是使用binlog恢复删除的数据则会更新该条数据,也会造成数据不一致

三、事务隔离

事务特性

原子性
一条事务是原子的，不可分割，要么全部执行，要么都不执行
一致性
事务开始前状态一致，事务执行过程中可能不一致，事务执行后状态一致
隔离性
多个事务之间互相隔离，不依赖不影响
持久性
一旦事务被提交，所做的变更是持久的

事务并发带来的问题

脏读
A事务读取到B事务没有提交的结果，读取到脏数据
不可重复读
前后多次读取，数据内容不一致。举个例子：A事务第一次查询name为"X"，这期间B事务将name改为"Y"，A事务第二次查询name变为了"Y"。A事务中两次读取的数据内容不一致，就不可重复读。针对update操作。
幻读
前后多次读取，数据总量不一致。举个例子：A事务第一次读取数据总量为100条，这期间B事务新增了一条数据，A事务再次读取的数据总量为101条。A事务两次读取的数据总量不一致，称为幻读。针对insert和delete操作。

事务隔离级别

为了形象的解释隔离级别,画个图展示一下.

读未提交
读取没有提交的数据,基本没啥作用.v1和v2和v3都是19
读已提交
可以解决脏读的问题,A事务只能读取其他事务提交后的结果.因为A事务只能读取其他事务提交后的结果,所以v1是18,v2和v3都是19.通过视图保证读已提交,视图在事务的每条sql语句执行时生成.
可重复读
事务在执行的过程中,看到的结果是一样的.v1和v2都在A事务执行过程当中,所以v1和v2都是18,v3是19.通过视图达到可重复读,视图在事务开始的时候创建,整个事务过程都会用到.
串行化
读的时候加读锁,写的时候加写锁.使用synchronize锁保证.v1和v2都是18,v3是19

事务隔离级别实现

以下以可重复读举例

视图是保证事务执行过程中能查询的数据,回滚日志只是为了回滚事务到之前的版本
在每条记录更新的时候都会同时记录一条回滚操作,通过回滚操作可以回滚到上一个版本的值.
因此在数据库中,一条数据可能有多个版本的值,又称作多版本.使用多版本并发控制(MVCC)保证事务隔离级别
当没有事务使用回滚日志时,回滚日志会被删除

不建议使用长事务,原因如下:

长事务占用锁,非常影响性能
长事务可能保存比较老的视图,要想回到视图之前的版本,就需要保存多个版本的回滚日志,将会非常占用资源

四、索引

1.索引数据结构

hash表
通过hash函数把key换算成对应的位置,再把value放在这个位置.需要查询的时候通过hash计算就能找到对应的位置,取出该位置的值了.如果碰到hash碰撞,该下标所在位置会转换成链表.hash索引适合等值查询,如果范围查询就需要遍历全表了.
有序数组
有序数组经过排序后,利用二分法查询速度也比较快,并且非常适合范围查询.如果有新增索引,为了保证有序,新增位置之后的索引全部后移,非常损耗性能.
二叉搜索树
左节点的值小于根节点,右节点的值大于根节点.利用二叉搜索树,查询速度也比较快.但实际上,大多数的索引结构不选用二叉树,理由是索引不止存在内存中,还可能写到磁盘上.索引树的每个节点都是一个页，一页保存多行记录，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说存储空间的基本单位是页。如果二叉树比较高,每次查询都需要访问过多节点，即访问多个页，页存储在数据块中，所以访问数据块过多，而从磁盘随机读取数据块过于耗时。所以更多选用B+树做索引结构,B+树有N叉,保证树尽量矮

2.innodb索引模型

innodb索引结构为B+树,每一个索引就是一棵B+树
innodb索引分为主键索引(又叫聚簇索引),非主键索引(又叫二级索引)

主键索引保存数据整行记录
非主键索引保存主键的值
如果使用非主键索引查询,先要查询到主键的值,再通过主键的值回表查询到该行记录.

3.索引维护

在这里插入图片描述

假设现在插入一条为400的数据,那么400后面的ID索引将会后移,如果数据页满后,还会进行页分裂,当然页的数据太少也会进行页合并.所以为了避免页分裂,尽量插入的索引是有序的,这就是为什么推荐使用自增ID做主键索引的原因.

4.覆盖索引

上面提到,如果是二级索引,可能会进行回表查询,使用覆盖索引将会避免回表查询,提升查询速度.

使用sql语句: select ID from table where k = 3 .这条sql会根据k的索引树查询,但是不需要回表,因为只需要查找ID的值,所以就避免了回表.
有的情况下,建立联合索引避免回表查询,会提升查询速度,但是索引过大也会占用内存和磁盘,需要根据场景选择.比如身份证和姓名,有一个需求需要根据身份证查询姓名,那么就可以将身份证和姓名建立联合索引.

5.最左前缀原则

这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。
利用索引最左前缀匹配索引的最左N个字段的规律,可以尽量少维护索引.比如有根据name和age查询的需求,有根据name查询的需求,那么只需要建立一个联合索引(name,age)就可以了.

6.索引下推

以联合索引(name,age)为例,sql语句: select name,age from table where name like ‘张%’ and age = 10
如果姓张的人有10个,姓张并且年龄为10的只有三个
当查出来姓张的人后,会继续使用age索引查询满足age=10的数据

MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

7.索引失效

模型数空运最快

模：模糊查询的意思。like的模糊查询以%开头，索引失效。比如：
SELECT * FROM user WHERE name LIKE ‘%老猿’;
型：代表数据类型。类型错误，如字段类型为varchar，where条件用number，索引也会失效。比如：
SELECT * FROM user WHERE height= 180;
height为varchar类型导致索引失效。
数：是函数的意思。对索引的字段使用内部函数，索引也会失效。这种情况下应该建立基于函数的索引。比如：
SELECT * FROM user WHERE DATE(create_time) = ‘2020-09-03’;
create_time字段设置索引，那就无法使用函数，否则索引失效。
空：是Null的意思。索引不存储空值，如果不限制索引列是not null，数据库会认为索引列有可能存在空值，所以不会按照索引进行计算。比如：
SELECT * FROM user WHERE address IS NULL不走索引。
SELECT * FROM user WHERE address IS NOT NULL;走索引。
建议大家这设计字段的时候，如果没有必要的要求必须为NULL，那么最好给个默认值空字符串，这可以解决很多后续的麻烦（切记）。
运：是运算的意思。对索引列进行（+，-，*，/，!, !=, <>）等运算，会导致索引失效。比如：
SELECT * FROM user WHERE age - 1 = 20;
最：是最左原则。在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。
快：全表扫描更快的意思。如果数据库预计使用全表扫描要比使用索引快，则不使用索引。