MySQL知识汇总

鹤望兰丶

已于 2024-04-25 16:01:58 修改

阅读量209

点赞数

文章标签： mysql 数据库 memcached

于 2021-08-18 21:30:18 首次发布

本文链接：https://blog.csdn.net/javanmsl/article/details/119788268

版权

MySQL知识汇总

1、存储引擎及区别

MySQL的存储引擎分为InnoDB 和 MyISAM
区别：1、InnoDB存储引擎支持事务，而MyISAM不支持事务；
2、InnoDB为聚焦形索引，MyISAM为非聚焦索引（索引的叶子节点是否存储数据）
3、InnoDB支持外键，而MyISAM不支持外键；
4、InnoDB不保存数据库表中表的具体行数，而MyISAM会保存；

当我们基于 InnoDB 引擎创建一张表的时候，都会创建一个聚集索引，每张表都有唯一的聚集索引：

1 如果这张表定义了主键索引，那么这个主键索引就作为聚集索引。
2 如果这张表没有定义主键索引，那么该表的第一个唯一非空索引作为聚集索引。
3 如果这张表也没有唯一非空索引，那么 InnoDB 内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个 6 个字节的列，该列的值会随着数据的插入自增。（DB_ROW_ID）

默认情况下**，InnoDB 存储引擎使用的页大小是 16KB**。可以通过设置 innodb_page_size 参数来修改页大小。目前，InnoDB 存储引擎支持的页大小包括 4KB、8KB、16KB 和 32KB。

2、索引相关知识

索引是对数据库表中一或多个列的值进行排序的结构，利用索引可快速访问数据库表的特定信息。类似于书籍的目录

1.哈希

缺点：
Hash存储需要全存入内存，比较耗费内存。范围查询不适合

2.二叉树或红黑树

缺点：
树的深度过深导致 IO次数过多，影响读取效率，查询效率不稳定
3.B树

3.B树特点：

1 ，所有数据分布在整颗树中，查询可能在非叶子节点结束，
2，每颗树最多有m个子树，根节点至少有2个子树，分支节点至少有2/m 个子树
3，所有叶子节点都在同一层，并且升序排列
缺点：
非叶子节点存放数据，如果某个节点数据过大也会导致数据页存放的数据少，导致IO次数增加
IO次数不稳定数据层级不固定

4 B+树

特点：
1 相比较于b树，b+树每个节点会存放更多的节点信息，降低了树的高度
2 b+树非叶子节点不存放数据，只存放key
3.叶子结点存放数据，通过双向链表的方式连接，方便做范围查询
4.有两个头指针，一个指向根节点，一个指向最小的叶子结点

默认情况下，InnoDB 存储引擎使用的页大小是 16KB。可以通过设置 innodb_page_size 参数来修改页大小。目前，InnoDB 存储引擎支持的页大小包括 4KB、8KB、16KB 和 32KB
MySQL数据库基的索引类型

唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。为表定义主键将自动创建主键索引。（数据库表某列或列组合，其值唯一标识表中的每一行。该列称为表的主键。）
联合索引：指对表上的多个列做索引。只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。
全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。目前只有char、varchar，text 列上可以创建全文索引。

使用索引查询一定能提高查询的性能吗？为什么？
答：不一定：通常过索引查询数据比全表扫描要快。但，索引也需要资源来维护，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能。
例如：某些枚举字段就没必要添加索引

什么字段适合建立索引:

在最频繁使用的、用以缩小查询范围的字段上建立索引。
在频繁使用的、需要排序的字段上建立索引。

对于那些在查询中很少使用或者参考的列不应该创建索引
对于那些定义为text, image和bit数据类型的列不应该增加索引。（这是因为，这些列的数据量要么相当大，要么取值很少。

2.如何设计索引

可以的话：查询的字段数最好<=3
优先给请求大的sql相关字段加上索引
通过联合索引覆盖尽可能多的索引。
索引数不易过多，一般不要超过5个，索引过多反而会减低性能。

3.索引失效

以%开头的like语句
数据类型出现了隐式转换
用or分隔开的查询条件，or前条件有索引，or后条件无索引
扫描的数据超过%30
字段使用函数

强制使用索引

使用FORCE INDEX
FORCE INDEX是MySQL提供的一个关键字，可以强制MySQL使用指定的索引来查询数据。我们
使用USE INDEX

USE INDEX是另一个MySQL提供的关键字，可以让MySQL只使用指定的索引来查询数据。

使用IGNORE INDEX

IGNORE INDEX是MySQL提供的一个关键字，可以让MySQL忽略指定的索引来查询数据。

3、事务相关知识

事务就是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

关系性数据库需要遵循ACID规则，

原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性： 执行事务前后，数据保持一致；
隔离性： 并发访问数据库时，一个用户的事物不被其他事物所干扰，各并发事务之间数据库是独立的；
持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务的隔离级别：

read uncommitted（读未提交）
read committed（读已提交）
repeatable read（可重复读）：InnoDB的默认隔离级别
serializable（串行）

查看隔离级别：select @@translation_isolation
设置隔离级别： set session 或 global ranslation_isolation level xxx

1、脏读：
A事务读取B事务尚未提交的更改数据，并在这个数据的基础上进行操作，这时候如果事务B回滚，那么A事务读到的数据是不被承认的。

2、不可重复读
不可重复读是指在一个事务内多次读同一数据，在这个事务还没有结束时，另外一个事务也访问并修改该同一数据，那么在第一个事务的两次读数据之间，由于第二个事务的修改，第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读。

3、幻读
A事务读取B事务提交的新增数据,会引发幻读问题
根本原因：快照读和当前读一起使用
例如：A事务先读取数据a=1 有一条，B插入一条a=1，A再读取，只能读到1条a=1，但是影响行数有两条。
解决幻读：手动加锁（A事务加锁查询，B则更新不了）

4、丢失更新
A事务撤销时，把已经提交的B事务的更新数据覆盖了。
指一个事务正在访问修改数据，与此同时另一个事务也在访问修改此数据，两个事务互相不知道对方的存在。假如在是事务A修改数据前事务B已经修改过1次数据，那么事务A最终只能查询到假数据，丢失了更新操作。

2.事务的ACID特性

原子性：通过undolog实现，记录之前的版本和数据，可直接回滚到之前的版本
一致性：通过其他三个来实现
隔离性： mvcc （多版本并发控制）和锁
持久性： redolog （数据：内存->磁盘）

3.mysql日志

mysql server服务器：

Binlog 二进制文件主从复制使用
Relaylog 中继日志主从复制用到
errorlog 错误日志
lowlog 慢日志

innodb存储引擎：

undolog 回滚日志，实现mvcc和原子性
redolog 前滚日志：用来实现数据持久化

4.redolog 随机读写和顺序读写

1.顺序读写效率高
2.redolog将随机读写变成顺序读写，redolog保存了需要写到磁盘中的数据。redolog为了防止正常的随机读写进磁盘失败，保存了一份。

3.redolog二阶段提交

作用：为了解决主从数据一致性，解决binlog和redolog 一致性
先写 redolog 为：prerpare状态 —> 写bindolog —> redolog commit
如果 binlog 中找不到与 prepare 状态的redolog匹配的记录，则删掉此redolog，一致则commit redolog

5.MVCC（多版本并发控制）

作用：实现事务隔离级别，解决并发读写冲突问题，解决并发场景下保证数据一致性
当前读：读取最新版本数据（加锁 select操作，update，insert，delete）
快照读：读取某个版本的数据（普通的select）

MVCC实现原理：
快照读：隐藏字段 + readview + undolog
当前读：行锁+间隙锁

隐藏字段：

DB_TRX_ID :创建或最后一次修改本条记录的事务id
DB_ROLL_PTR ：回滚指针，指向这条记录的上个版本
DB_ROW_ID ：隐藏主键：当表中没有显式的主键时，mysql会默认生成一个6字节的rowid

undolog：回滚日志

在insert、delete、update时产生的方便回滚的日志信息
形式：某条数据不同的版本，通过 DB_ROLL_PTR可以链接起来，形成一个新旧版本的链表
Undolog会通过purge异步线程来已出不再被需要的数据版本，防止undolog无线膨胀（标志位，delete_id）

readview 读视图

概念：是在快照读是产生的事务信息，有以下字段
trx_list 生成readview时刻当前系统活跃的事务列表
up_limit_id 活跃事务中id最小的事务id
low_limit_id 系统尚未分配的下一个事务id

可见性算法

通过可见性算法来判断是否可以读到数据
1.首先比较DBTRX_ID < up_limit_id,如果小于，则当前事务能看到DB_TRX ID所在的记录，如果大于等于进入下一个判断
2.接下来判新DB TRX ID >三low_limit_id.如果大于等于则代表DB_TRX_ID所在的记录在Read View生成后才出现的，那么对于当前事务肯定不可见，如果小手，则进入下一步判断
3.判斯DB TRX 1D足否在活跃事务中，如果在，则代表在Read View生成时刻，这个事务还足活跃状查，还没有
commit，修改的数据，当前事务也是看不到，如果不在，则说明这个事务在Read View生成之前就已经开始commit，那么修改的结果是能够看见的。

在RC隔离级别下，每次进行快服读都会生成新的readview
在RR隔离级别下，只有在第一次进行快照读的时候生成了 reacdview，之后的快照读都会沿用之前的readview
(RR解决不可重复读问题的根本点在于一直沿用的时候第一次生成的readview)

4、drop、delete与truncate的区别

drop直接删掉表。
truncate删除表中数据，再插入时自增长id又从1开始。
delete删除表中数据，可以加where字句。

5、数据库三大范式

第一范式（1NF）
　　（在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。）

所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，第一范式就是无重复的列。强调的是列的原子性，即列不能够再分成其他几列。

第二范式（2NF）
　　满足第二范式（2NF）必须先满足第一范式（1NF）。另外包含两部分内容，一是表必须有主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

第三范式（3NF）
　　满足第三范式（3NF）必须先满足第二范式（2NF）第三范式就是属性不依赖于其它非主属性。非主键列必须直接依赖于主键，不能存在传递依赖。

6、非关系型数据库和关系型数据库区别

非关系型数据库的优势：

性能：NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
可扩展性：同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

关系型数据库的优势：

复杂查询：可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
事务支持：使得对于安全性能很高的数据访问要求得以实现。

7、SQl的执行过程

在这里插入图片描述

8、mysql回表

什么是回表？

简单来说就是数据库根据索引（非主键）找到了指定的记录所在行后，还需要根据主键再次到数据块里获取数据。

如何避免回表
将需要的字段放在索引中去。查询的时候就能避免回表。

mysql主从复制

需要主从复制的原因（多台机器）
1.读写分离
2.数据备份
3.降低IO频率，提高单机的性能

主从复制过程

1.master服务器将数据的改变记录二进制binlog日志（需要开启binlog）
2.slave服务器会在一定时间间隔内对master 的binlog进行探测其，如果发生改变，则开始一个I/OThread请求master的binlog，并写入relay log 中继日志（顺序读写）
3.slave 启动 SQL线程将数据从relay log中重放到从库中（随机读写，因为重新写入磁盘会涉及到寻址）
4.IO线程和SQl线程会睡眠
在这里插入图片描述

主从复制延迟

1.延迟原因：
1 .产生binlog，从库读取binlog，写relaylog 都是顺序读写，速度比较快
而重放SQL是随机读写比较慢，会导致 relaylog堆积，最终导致延迟
2.主库并发高，产生的DDL数量过多，超过一个线程的承受
3.大事务的执行消耗时间长
4.从库机器性能差
5.从库充当了读库，有点一定的查询压力，消耗大量cpu，导致影响速度

2.解决主从复制延迟

MTS：multi-thread-slave（多线程slave）
并行复制
Mysql 5.6 ：只支持按照库的粒度来并行复制，5.7支持行的粒度

并行复制规则：
1.更新同一行数据的多个事务，必须在同一个worker 线程中执行
2.同一个事务不能拆开，必须放在一个worker中执行
在这里插入图片描述

GTID （全局事务id）
服务器唯一标识+递增事务ID
默认会有匿名的GTID

MySql的锁

锁的分类
在这里插入图片描述

锁算法：
RecordLock锁：锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）对索引进行锁定，按照主键或者raw_id来锁
GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）
Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）

mysql优化

**从以下方面入手：
1.sql 执行。监控，慢日志，执行计划等
2.连接，连接数量过多？
3.表设计，字段设置合理性，分库，分表。
4.索引设置合理性
5.查询SQL优化
6.Mysql集群优化，负载，读写分离，
**

单表优化
除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量。

字段
尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
VARCHAR的长度只分配真正需要的空间
使用枚举或整数代替字符串类型
尽量使用TIMESTAMP而非DATETIME，
单表不要有太多字段，建议在20以内
避免使用NULL字段，很难查询优化且占用额外索引空间
用整型来存IP

索引

索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
字符字段只建前缀索引
字符字段最好不要做主键
不用外键，由程序保证约束
尽量不用UNIQUE，由程序保证约束
使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

查询SQL优化
可通过开启慢查询日志查找出较慢的SQL。
不做列运算: SELECT id WHERE age+1=10 ,任何对列的操作都将导致全表扫描.它包括数据库教程函数,计算表达式等等,查询时尽量将操作移至等号右边
SQL语句尽可能简单:一条SQL只能在一个cpu进行运算;大语句拆分成小语句,减少锁时间;一条大SQL可以堵死整个库
不用 SELECT *
少用 JOIN
避免 %xxx式查询
不用函数和触发器,在应用程序实现
OR改写成IN:OR的效率是 n 级别,IN的效率是 log(n)级别。IN的个数建议控制在200以内
使用同类型进行比较:比如’123’和’123’比,123和123比
尽量避免在WHERE 子句中使用!= <>操作,否则将导致引擎放弃索引使用全表扫描
对于连续数值,使用BETWEEN而不用IN
列数据不要拿全表,要使用 LIMIT进行分页,每页数量页不要太大，offest 太大可以考虑只查出主键，再用主键去查数据