MYSQL的优化学习，从原理到索引，在到事务和锁机制，最后的主从复制、读写分离和分库分表

最新推荐文章于 2024-08-12 23:11:19 发布

oO小明.同学Oo

最新推荐文章于 2024-08-12 23:11:19 发布

阅读量1.1k

点赞数 24

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_40718364/article/details/136303192

版权

mysql的优化学习

为什么选择Mysql不选择其他的数据库？还有哪些，有什么区别？

Mysql：开源免费版本可用，适用于中小型应用

Oracle：适用于大型企业级应用，复杂的业务场景和大量数据的处理，不过收费

SQL Server ：主要运行在window系统上，其他系统不是很兼用

PostgreSQL：开源免费的关系型数据库，强调可扩展性和标准兼容性，对比MYSQL生态没有它的完善，语法和功能相对会复杂一点

Mysql主要有哪些存储引擎，各有什么区别

MyISAM和InnoDB，InnoDB支持事务、行级锁定和外键

索引的优化

索引的本质（加索引为什么快）

索引是帮助MySQL高效获取数据的排好序的数据结构，加索引的目的就是提高查询效率

索引的优缺点

优点

提高数据检索效率，降低数据库IO成本
降低数据排序的成本，降低CPU的消耗

缺点

索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
因为索引是排好序的数据结构，所以在增删改的时候就会重新排序，降低了效率

索引数据结构

可视化展示

二叉树

左节点比父节点要小，右节点比父节点要大，每个节点最多只能有两棵子树。数据量大，数的高度也变大，增加了磁盘的IO，效率变慢

红黑树

也叫自平衡二叉查找树，跟二叉树一样，数据量大的话，树的高度依旧很大，查询效率会降低，每个节点中只存储一个数据，节点之间不连续，无法利用局部性原理，使得磁盘IO操作频繁，导致查询效率降低

Hash表

A. 事先将索引通过 hash算法后得到的hash值(即磁盘文件指针）存到hash表中。

B. 在进行查询时，将索引通过hash算法，得到hash值，与hash表中的hash值比对。通过磁盘文件指针，只要一次磁盘IO就能找到要的值。

Hash表虽然查找速度快，但不支持范围查询

B-Tree

B-Tree属于多叉树又名平衡多路查找树，所有节点关键字是按递增次序排列，并遵循左小右大原则，每个节点存储了更多的键值（key）和数据（data），数的高度没有其他数据结构的高，减少了对磁盘的IO

B-Tree和B+Tree的区别

B-Tree每个节点key和data在一起；B+树非叶子节点不存储data，只存储key，所有data都存储在叶子节点上
B-Tree每个节点key和data在一起，无法区间查找；B+树叶子节点两两相连可大大增加区间访问性，可使用在范围查询等

B-Tree

B+Tree

为什么使用B+Tree，不选其他的？

对比其他数据结构，B+Tree支持范围查询，有自增ID，节点高度低，更能减少对磁盘的IO，增加读取效率

聚集索引和非聚集索引

两个的区别，在于非聚集索引会多加一次读取磁盘，增加IO，效率会低点

聚集索引（InnoDB）

聚集索引也叫主键索引，叶子节点中的data存储的是该主键对应整行数据，通常B+Tree的高度为3，也就是有三层节点，MySQL会把B+Tree第一层也就是根节点放在内存中，我们根据主键索引查数据，只需要两次磁盘IO（第二层1次，第三次1次）即可

非聚集索引（MyISAM）

非聚集索引也叫辅助索引，叶子节点中的data存储的该索引对应的该行数据的主键值

索引有哪些类型？（怎么加索引）

索引主要有单列索引，组合索引，全文索引

单列索引（主键索引，唯一索引，普通索引）

主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。

唯一索引：索引列中的值必须是唯一的，但是允许为空值

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹是为了查询数据更快一点

组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合

例如：由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面的字段组合（id，name，age）、（id，name）或者（id）。如果要查询的字段不构成索引最左面的前缀，就不会使用索引，比如，age或者（name，age）组合就不会使用索引查询。

全文索引

在大量数据中，通过其中的某个关键字，就能找到该字段所属的记录行。全文索引在开发中很少用，因为其占用很大的物理空间和降低了记录修改性。

注：只有主键索引叶子节点存储的是数据，其他索引存储的都是主键值|（回表），主要目的是为了减少空间，每个索引都是数据的话，会增加空间，大大降低了读取速度，而且在增删改的时候就要同步几个索引表，还有保证数据的一致性，非常的耗时，大大降低了性能

回表

回表，顾名思义就是回到表中，也就是先通过普通索引（我们自己建的索引不管是单列索引还是联合索引，都称为普通索引）扫描出数据所在的行，再通过行主键ID 取出索引中未包含的数据。所以回表的产生也是需要一定条件的，如果一次索引查询就能获得所有的select 记录就不需要回表，如果select 所需获得列中有其他的非索引列，就会发生回表动作。即基于非主键索引的查询需要多扫描一棵索引树。

MyISAM和InnoDB的索引结构对比

MyISAM下索引结构

MyISAM索引文件和数据文件是分开的(非聚集)，存储引擎在磁盘中文件有三个，

一个是 .frm 文件(数据表定义)，

一个是 .MYI(索引)，

一个是 .MYD(实际数据，存储的是一整行的数据，包括索引值)。

　　注：在MyISAM存储引擎中，无论是主键索引还是非主键索引，B+Tree中叶子结点中的data存储的都是该行数据对应的磁盘指针，根据指针再去拿数据。

InnoDB索引结构

InnoDB存储引擎它的表数据文件本身就是按 B+Tree 组织的一个索引文件。不同于MyISAM存储引擎是，数据不分离。

一个frm文件存储数据表定义

一个ibd文件存放的索引和实际数据

什么样的情况下索引失效

like需要符合最左原则，比如like 'a%'走索引，like '%a'不走索引
where中未使用索引字段过滤
使用内置函数操作
数据类型不匹配，比如title是varchat类型，查找的时候使用的是整型title=123123
使用is null可能走索引，看返回数据数量，is not null不走索引

不等于(!= 或者<>)导致索引失效

OR 前后只要存在非索引的列，都会导致索引失效

联合索引需要符合最左原则

IN，between正常是走索引的，不过条件太多可能就不走索引，正常返回数据超过30%就不走索引

这些问题你懂了吗？

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键，不用uuid？

考察点：主键，整型主键，自增

为什么必须要有主键

mysql底层就是用B+Tree维护的，而B+Tree的结构就决定了必须有主键才能构建B+Tree树这个结构，如果没有建立主键，InnoDB会选择这个唯一非空索引作为聚簇索引，如果找不到，会自动生成一个隐藏的主键索引。建议是要自建，由系统生成会消耗mysql资源，效率会比较低

为什么要用整型主键，不用uuid

假如用的uuid字符串作为主键，它会一个字符一个字符的先去比较，效率会比较慢，整型主键可以直接比较，效率会快很多

为什么要自增主键

如果是自增主键的话，因为后面的主键都是大于前面的，在做范围查询的时候能够快速的找到。在添加数据的时候，都是在后面添加，树分裂的机会小，uuid的话就有大概率可能重新分裂排序，性能消耗大

事务

事务的概念

事务是数据库操作的基本单位，它是一组一起执行的数据库操作，这些操作要么全部执行，要么全部不执行，确保数据的完整性和一致性。事务的主要目的是提供一种将多个操作组合在一起的方式，以便它们可以作为一个单一的工作单元来执行

事务的四大特性ACID

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改要么全部执行，要么全部不执行
一致性（Consistency）：事务必须使数据库从一个一致性状态转变到另一个一致性状态。一致性与业务规则有关，比如，转账业务中，无论事务成功与否，参与转账的两个账户的总金额应该保持不变
隔离性（Isolation）：通常，一个事务所做的修改在提交之前，对其他事务是不可见的。即事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务是不可见的。
持久性（Durability）：一旦事务提交，其对数据库中数据的修改就是永久性的。接下来的操作或故障不会对其有任何影响

事务并发事务问题

脏读：一个事务读取到了另一个事务还没有提交的数据
幻读：一个事务里多次读取的数据数量不一致，受到了其他事务的Insert、delete干扰
不可重复度：一个事务里多次读取的数据内容不一致，受到了其他事务的update干扰
更新丢失：发生在两个事务同时更新一条记录的时候

事务的操作

开始事务

使用START TRANSACTION或BEGIN语句开始一个新的事务。
START TRANSACTION;
-- 或者
BEGIN;
执行事务里面的sql操作

在事务中，可以执行SQL语句，如INSERT、UPDATE、DELETE等
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE table_name SET column1 = value1 WHERE column2 = value2;
DELETE FROM table_name WHERE column1 = value1;
提交事务或回滚事务

如果事务中的所有操作都成功，使用COMMIT语句来提交事务
COMMIT;
如果事务中的任何操作失败，使用ROLLBACK语句来回滚事务
ROLLBACK;
查看事务状态

使用SHOW ENGINE INNODB STATUS命令来查看当前InnoDB存储引擎的事务状态，包括正在运行的事务、锁的信息等。
SHOW ENGINE INNODB STATUS;

事务的隔离级别

事务的隔离级别有四种，由低到高分别为Read Uncommitted、Read Committed、Repeatable Read、Serializable。

Read Uncommitted（读未提交）：最低级别，事务还没提交别人就能看到，这样就不能保证读取到的数据是最终的数据。
Read Committed（读已提交）：一个事务只能读取到其他事务已经提交了的数据，解决了脏读问题，但是会出现不可重复读和幻读问题。
Repeatable Read（可重复读）：解决了不可重复读问题，但是会出现幻读问题。
Serializable（串行化）：最高的隔离级别，解决了上面出现的所有问题，但是效率最差，它将事务的执行变成顺序执行。

查看事务隔离等级

select @@transaction_isolation;

更改事务隔离等级

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL 事务隔离级别

锁机制

什么是锁

锁是计算机以协调多个进程间并发访问同一共享资源的一种机制。MySQL中为了保证数据访问的一致性与有效性等功能，实现了锁机制，MySQL中的锁是在服务器层或者存储引擎层实现的。

为什么要用锁

锁是用来解决并发事务的访问问题，例如我们上面事务讲的脏读、不可重复读、幻读的问题。

MySQL锁的分类

按照锁的粒度分为三种：全局锁，表级锁，行级锁

全局锁：锁定数据库所有的表

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
-- 全局锁，整个数据库处于只读状态，其他操作均阻塞
FLUSH TABLES WITH READ LOCK

-- 释放全局锁
UNLOCK TABLES

表级锁：每次操作锁定整张表

表锁

        表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MVISAM、InnoDB、BDB等存储引擎中。共分为表共享锁和表排它锁

        表共享锁（S锁）READ

        若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加X锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

        表排它锁（X锁）WIRTE

        若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
LOCK TABLES 表名 READ|WRITE

UNLOCK TABLES
元数据锁(meta data lock，MDL)

        MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据（如表结构、索引信息等）的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。即当对一张表进行增删改查的时候，加MDL读锁(共享);当对表结构进行变更操作的时候，加MDL写锁(排他)。

意向锁

        为了避免DML在执行时，加的行锁与表锁的冲突，InnoDB自动引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

        意向共享锁（IS）

        一个事务要获取某表中某些行的共享锁时，需要先获取该表的意向共享锁。意向共享锁不互斥，多个事务可以同时获取



意向排它锁（IX）

        一个事务要获取某表中某些行的排他锁时，需要先获取该表的意向排他锁。意向排他锁与意向共享锁互斥，当一个事务持有意向排他锁时，其他事务无法获取该表的意向排他锁或意向共享锁。目的：为了告诉其他事务，此时这条表被一个事务在访问；作用：排除表级别读写锁（全面扫描加锁）

行级锁：每次操作锁定对应的行数据

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

行锁

        锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持

间隙锁

        锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

临键锁

        行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

死锁怎么处理

死锁是指两个或两个以上的进程（线程）在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程（线程）称为死锁进程（线程）

经常出现并发事务操作相同的数据或者事务执行顺序不当

处理死锁的方法有以下几种：

降低事务的隔离级别 。隔离级别越高，锁的粒度就越大，越容易发生死锁。降低隔离级别可以减少死锁的发生，但也可能引起其他问题，需要根据实际情况权衡1。
减少事务并发度 。当存在大量并发事务时，会增加死锁的概率。可以通过调整业务流程或更改代码来减少事务并发度1。
优化SQL语句和索引 。优化SQL语句和索引可以减少对同一数据行的竞争，从而降低死锁的概率。可以通过合理设计索引、使用批量更新或延迟加载等方式来优化SQL语句1。
使用数据库的死锁检测和超时机制 。大多数数据库会提供死锁检测和超时机制，可以使用这些机制来避免或解决死锁问题。当发现死锁时，数据库会自动回滚其中一个事务，释放资源，避免了死锁的进一步扩大。而超时机制则可以在一定时间内主动结束事务，释放资源，避免死锁的长时间持续，从而提高数据库的并发性能。

主从复制

什么是主从复制

主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从数据库上，然后在从数据库上对这些日志进行重新执行，从而使从数据库和主数据库的数据保持一致。

主从复制的原理

主从复制主要有三个线程，主节点 binary log dump 线程、从节点I/O线程、从节点SQL线程

主从复制的原理是：Master提交完事务后，写入Binlog，Slave连接到Master，获取Binlog，Master创建Dump线程，推送Binlog到Slave，Slave启动一个IO线程读取同步过来的Master的Binlog，记录到Relay
Log中继日志中，Slave再开启一个SQL线程读取Relay Log事件并在Slave执行，完成同步，Slave记录自己的Binlog。

为什么要配置主从复制

实现服务器负载均衡。主服务器上只实现数据的更新操作，而不关心数据的查询。查询请求可以转发到多个从服务器中，将数据更新与查询分别放在不同的服务器上进行，可以提高数据的安全性，同时也缩短应用程序的响应时间、提高系统的性能。
通过复制实现数据的异地备份。数据备份是相当重要的一个环节，而MySQL主从配置，可以实现数据的异地备份。
提高数据库系统的可用性。数据库复制功能实现了主服务器与从服务器之间数据的同步，增加了数据库系统的可用性。当主服务器出现问题时，数据库管理员可以马上让从服务器作为主服务器，用来进行数据的更新与查询服务。

配置主从复制

1. 主服务器的配置

在主服务器的my.cnf或my.ini中配置

        [mysqld]
        server-id=1 #服务器 id
        log-bin=mysql-bin #指定了生成二进制日志的名称前缀为"mysql-bin"

        read-only=0 # 是否只读，1代表只读，0代表读写

重启MySQL服务以使配置生效
创建一个专用的复制用户并授予相应的权限

           CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
           GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
           FLUSH PRIVILEGES;

获取主服务器的二进制日志文件和位置

SHOW MASTER STATUS;

2.从服务器的配置

在从服务器的my.cnf或my.ini中配置

        [mysqld]
        server-id=2 #服务器 id

        read-only=1 # 是否只读，1代表只读，0代表读写

重启MySQL服务以使配置生效
配置从服务器指向主服务器
# mysql是8.0.23之前版本
CHANGE MASTER TO 
    MASTER_HOST='主服务器ip', 
    MASTER_USER='主服务器账号', 
    MASTER_PASSWORD='主服务器密码', 
    MASTER_LOG_FILE='主服务器log文件',
    MASTER_LOG_POS='log文件哪个位置',
    MASTER_AUTO_POSITION = 1;

# mysql是8.0.23后版本
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='主服务器ip', 
    SOURCE_USER='主服务器账号', 
    SOURCE_PASSWORD='主服务器密码', 
    SOURCE_LOG_FILE='主服务器log文件',
    SOURCE_LOG_POS='log文件哪个位置',
启动从服务器的复制线程
START SLAVE;    # 8.0.22之前
START REPLICA;  # 8.0.22之后
检查复制状态以确保一切正常
SHOW SLAVE STATUS\G;

# 确保Slave_IO_Running和Slave_SQL_Running的值都是Yes

主从复制引发的问题

1. 延迟和性能问题

可以通过减少从数据库（3-5台），提高服务器配置，优化SQL语句，使用一些缓存技术

2. 网络问题

提升传输大小和允许更长的延迟时长

3. 数据的一致性问题，根据业务场景选择

异步复制：MySQL的默认复制方式，主库更新数据后，会立即将结果返回给客户端，并不关心从库是否已经接收并处理。这种方式可能导致数据不完整的问题。
全同步复制：只有当主库执行完一个事务后，所有的从库都执行完该事务，才会返回给客户端。这种方式会严重影响性能。
半同步复制：介于异步复制和全同步复制之间，主库执行完事务后，会等待至少一个从库接收到并写到relay log中，才返回给客户端。这种方式相对于异步复制，提高了数据的安全性，但会造成一定程度的延迟。

主从复制发现数据不一致处理方案

1. 先SHOW SLAVE STATUS检查服务器复制情况以及是否有错误。

2. SHOW VARIABLES LIKE 'log_error'查看错误日志

3. 比较主从数据

使用工具如pt-table-checksum（来自Percona Toolkit）来比较主从服务器上表的数据一致性。这个工具会检查每个表的数据校验和，并报告不一致的表。

4. 修复数据不一致

如果只是个别表或少量数据不一致，可以手动修复，比如使用pt-table-sync工具来同步数据。
如果数据不一致很严重，考虑使用全备份和恢复的方式来重新建立从服务器。

5. STOP SLAVE停止复制

6. 修复后重新配置复制

读写分离

什么是读写分离

读写分离，基本的原理是让主数据库处理事务性 增、删、改 操作（INSERT、UPDATE、DELETE），而从数据库处理 查（SELECT）操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库中。

为什么要读写分离

主要是为了提高数据库系统的性能和可扩展性。

写操作通常涉及更多的磁盘I/O和锁竞争，会比较消耗资源，而且读操作通常比写操作更频繁，两者分开有助于提高性能。主数据库出问题了，从数据库提升为新的主数据库，从而实现故障转移。这有助于确保系统的高可用性

什么情况下要读写分离

数据库不一定要读写分离，当数据库的访问量很大，且读操作远多于写操作时。或者当需要提高数据库的查询性能，消除读写锁冲突，从而提升数据库的写性能时

主从复制和读写分离

主从和读写分离的关系为：需要先部署主从复制，才能在此基础上进行数据的读写分离

主从复制是主数据库将数据通过二进制的日志文件同步到从数据库。当主数据库有操作时，会记录日志，从数据库通过监听日志，实现和主数据库的数据同步。可以一主多从，即一个主数据库对应多个从数据库。读写分离简单的说是把对数据库读和写的操作分开，对应不同的数据库服务器，这样能有效地减轻数据库压力，也能减轻IO压力。主数据库提供写操作，从数据库提供读操作。

实现方法

读写分离，实际是基于主从复制上，配置好主从即可使用

分库分表

概念

分库分表就是把存于一个库的数据分散到多个库中，把存于一个表的数据分散到多个表中。

分库分表的情况

分表不分库

从单张表拆分成多张表的过程，将数据散落在多张表内。

什么样的情况下分表不分库？

一般当单个表的数据量太大，可以考虑通过分表来提升查询效率

分库不分表

从单个数据库拆分成多个数据库的过程，将数据散落在多个数据库中，多个数据库同时提供服务

什么样的情况下分库不分表？

数据库QPS过高，连接数不足，但是数据量不大

磁盘瓶颈，可能存在单个数据库数量量太大

分表分库

把存于一个数据库的单表数据分散到不同库的多个表中。

什么样的情况下分库分表？

数据库QPS过高，连接数不足，而且单个表数据量大的情况下

怎么分库分表

一般都是在空间上拆分，主要拆分模式是垂直拆分和水平拆分

垂直拆分

主要解决表过多或者是表字段过多问题，一般谈到的垂直拆分主要指的是垂直分库

垂直分库：是将不同的表分离到不同的库中。
垂直分库优点
专库专用，业务层面解耦
能够针对不同业务的数据进行分级管理、维护、监控、扩展
在一定程度上提升了IO、数据库连接数、降低单机硬件资源的瓶颈
垂直分库缺点
事务一致性的问题
多表连接查询困难

垂直分表：修改表结构按照访问的差异将不同的列拆分到不同的表中。
垂直分表拆分原则
将热点字段和不常用的字段区分，放在不同的表中
将text，blob等大字段拆分出来放在附表中
将组合查询的列放在一张表中
垂直分表优点
减少锁竞争，查询不同字段数据互不影响
可实现冷热分离的数据表设计
可以使得行数据变小，一个数据页能存放更多的数据，最大限度利用数据页缓存，减少查询的 I/O 次数
垂直分表缺点
事务一致性的问题
多表连接查询困难
无法解决单表数据量过大

水平拆分

解决表中记录过多，缓解单机单库的性能瓶颈和压力问题。一般谈到的水平拆分主要指的是水平分库

水平分库：将数据切分到不同的数据库上，每个数据库都具有相同的表，只是数据行不一样。
水平分库优点
解决单个库高并发的性能瓶颈
切分的表的结构相同，应用层改造较少，只需要增加路由规则即可
提高了系统的稳定性和负载能力。
水平分库缺点
分片事务的一致性难以解决
数据扩容的难度和维护量极大

水平分表：将一张表水平切分，不同的记录可以分开保存，拆分成几张结构相同的表。
水平分表优点
解决单表数据量大，查询性能下降的问题
可实现多表连接查询
水平分表缺点
引发排序、分页、函数计算等问题
数据扩容的难度和维护量极大。