MySql进阶

Mysql架构

        通过Mysql完整的架构图，可以清楚地看到Mysql是由连接层，服务层，引擎层，物理文件层构成。

连接层：链接客户端请求，对用户进行认证等

服务层：接收sql，调用存储过程，优化sql,缓存数据

引擎层：负责实际与文件层进行交互操作的，可以有不同的引擎选择。

物理文件层：负责存储数据库表的数据以及各种日志文件

MySQL引擎

        MySQL 存储引擎是数据库的核心组件，负责管理数据的存储、索引和访问方式。不同的存储引擎提供多样化的功能与优化策略，适用于各种应用场景。

概述

存储引擎（Storage Engine） 是 MySQL 中决定数据如何存储、索引及事务处理的底层模块。

每张表可以选择不同的引擎，从而灵活应对不同的性能需求和数据操作特性。 

存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。 

引擎操作

1.查看引擎

SHOW ENGINES;

2.查看表引擎

SHOW TABLE STATUS LIKE '表名'

3.修改引擎

方法一：修改默认引擎

        找到系统安装的Mysql文件夹下的mysql.ini文件，修改配置，之后重启服务。

[mysqld]
default-storage-engine=MyISAM

        如果没有这个文件，那就创建一个mysql.ini文件,与bin目录同级即可(非同级也行，主要是方便找到)，同样是放入上面的代码，但是要指定配置路径.

mysqld --defaults-file="D:\ProgramData\MySQL\MySQL Server 8.0\mysql.ini"

        通过sql命令查看默认引擎

SHOW VARIABLES LIKE 'default_storage_engine';

 方式二：创建表单时指定引擎

CREATE TABLE admins (
    id INT PRIMARY KEY ,          
    NAME VARCHAR(10) ,
    gender VARCHAR(2) ,
    address VARCHAR(50), 
    phone int 
)ENGINE=MYISAM;;

方式三：修改表引擎

ALTER TABLE 表名 ENGINE = INNODB;

引擎分类

        存储引擎主要有：1. MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole, 5. CSV, 6.Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam

特性	MyISAM	InnoDB	Memory	Archive
事务支持	❌ 不支持	✅ 支持 (ACID 兼容)	❌ 不支持	❌ 不支持
锁机制	表级锁	行级锁	表级锁	行级锁
外键约束	❌ 不支持	✅ 支持	❌ 不支持	❌ 不支持
崩溃恢复能力	弱	强 (自动崩溃恢复)	数据丢失	高压缩，恢复依赖备份
存储方式	磁盘（非事务型）	磁盘（事务型）	内存（临时数据）	磁盘（高压缩）
全文索引	✅ 支持	✅ 支持 (MySQL 5.6+)	❌ 不支持	❌ 不支持
压缩能力	✅ 表压缩	✅ 页压缩	❌ 不支持	✅ 极高压缩率
并发性能	读高并发，写性能低	高并发（行级锁）	极高（内存操作）	低（仅支持插入/查询）
数据持久性	✅ 持久化	✅ 持久化	❌ 重启后丢失	✅ 持久化

InnoDB

默认存储引擎,最常用，支持事务，支持行级锁，支持外键约束，支持索引，不存储表的总行数。

MyISAM

MySQL存储引擎的一种，不支持事务，不支持行级锁，进行增删改查时，会对整个表进行加锁，修改效率低，可以存储表的总行数，查询效率高。

索引

概念

索引是帮助MySQL高效快速获取数据的数据结构。数据库系统除了维护数据之外，还维护着索引。索引指向着数据。

没有索引，当数据量较为庞大时，查询效率就会急剧下降。

举个例子：我要在字典中查找某一个字，我从第一页逐页去找就会费时费力，但是通过目录去查找就方便快捷，索引在数据库中就充当目录这一角色。

索引结构

B+Tree索引

• 适用场景：大多数存储引擎（如InnoDB、MyISAM）的默认索引结构。

• 优点：

  • 支持范围查询（>、<、BETWEEN）。

  • 叶子节点形成有序链表，适合排序和范围扫描。

• 特点

  • 排好序的,一个节点可以存储多个数据
  • 非叶子节点不存储数据,只存储索引,可以放更多的索引
  •   数据记录都存放在叶子节点中.
  • 所有叶子节点之间都有一个链指针.

B+树的数据结构：数据结构——B+树

哈希索引

• 适用场景：Memory引擎，适用于等值查询（=）。

• 缺点：

  • 不支持范围查询。

  • 哈希冲突可能影响性能。

索引分类

查看索引

SHOW INDEX FROM 表名;

主键索引

        设定为某个字段为主键后数据库会自动建立索引

-- 设置主键，自动添加主键索引
ALTER TABLE 表名 add PRIMARY KEY 表名(列名);
-- 删除主键，自动删除主键索引
ALTER TABLE info DROP PRIMARY KEY ;

唯一索引

        索引列的值必须是唯一的允许为null,不能重复

-- 创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
-- 删除索引
DROP INDEX 索引名 ON 表名;

单值索引

        一个索引只包含一个列，一个表可以有多个单值索引

-- 创建单值索引
CREATE INDEX 索引名 ON 表名(列名);

组合索引

        一个索引包含多个列，在数据库操作期间，组合索引所需要的开销更小(相较于为多个单列索引)，当表的行数远大于索引列的数目时可以使用组合索引。

-- 创建复合索引
CREATE INDEX 索引名 ON 表名(列 1,列 2...);
-- 删除索引：
DROP INDEX 索引名 ON 表名;

 组合索引最左前缀原则

        列如表中有 a,b,c 3 列,为 a,b 两列创建组合索引,那么在使用时需要满足最左 侧索引原则.在使用组合索引的列作为条件时,必须要出现最左侧列为条件,否则 组合索引不生效.

-- 情形
select * from table where a=' 'and b=' ' -- 索引生效
select * from table where b=' 'and a=' ' -- 索引生效
select * from table where a=' 'and c=' ' -- 索引生效
select * from table where b=' 'and c=' ' -- 索引不生效

 全文索引

需要模糊查询时，一般索引无效，这时候就可以使用全文索引了。

-- 创建全文索引
CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;
-- 查询
SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(‘搜索词')

聚簇索引/非聚簇索引

        聚簇索引：

找到了索引就找到了需要的数据，那么这个索引就是聚簇索引。innodb 中的主键就是聚簇索引(一级索引)。

        索引，表结构，数据都存储在一个文件中

         innodb 中也存在非聚簇索引,就是除主键索引(一级索引)之外的索引,也叫辅助索引.

         例如为 name 列添加索引(二级索引),我们通过 name 找到后并不能找到数据,而是需要再找到主键索引,通过主键索引找到数据,这种索引也称为非聚簇索引.

        非聚簇索引：

索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询。

 

         MySQL中InnoDB引擎的索引和文件是存放在一起的,找到索引就可以找到数 据,是聚簇式设计.而 MyISAM 引擎采用的是非聚簇式设计,索引文件和数据文件不在同一个文件中.  

        回表查询：

下面列举三种查询情形：

1.id是主键，此时id的索引为聚簇索引，id键对应的索引叶子结点上存储了id=1对应的数据

SELECT * FROM student WHERE id = 1

这时就不需要回表查询

2.num是一个唯一索引(非聚簇索引)，但是查询结果包含学生num与name，当命中num索引时，该索引的节点数据存储的是主键id，需要根据主键id再次查询

SELECT num,name FROM admin WHERE num = 1002;

这时就需要回表查询

3.使用num查询num,一般这种情况用作验证数据是否存在

SELECT num FROM admin WHERE num = 1002

 存在就返回条件值，也不需要回表查询。

创建原则

        针对于数据量较大，且查询比较频繁的表建立索引。

        针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

        尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

        尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

        控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

        如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

事务 

概念

        事务就是一次对数据库操作的过程，这个过程包含多条sql，多条sql的执行为一个整体。

        在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 

特性(ACID)

原子性(Atomicity) ：一个事物中有多条sql，这多条sql要么都成功执行，要么都不执行

        如果执行过程中出错，全部sql都会回滚，就行全部没有执行一样。 

持久性(Durability):在事务提交以后，要保证数据的持久化。

        即使系统宕机，数据也不会丢失。

隔离性(Isolation): 在多个事务并发执行时，对多个事务操作要进行隔离，保证数据的完整性

        多个事务同时对数据进行读写时，对其进行隔离，防止多个事务并发执行导致数据不一致。 

一致性(Consistency):最终要保证数据操作的一致性。

        写入的数据必须符合预设规则。

事务设置

        MySQL默认开启自动提交事务，只要指定DML操作语句，机会提交事务。

        下面是事务设置方法：

-- 事务
-- 查询是否自动提交
SHOW GLOBAL VARIABLES LIKE 'autocommit';  -- 全局级
SHOW SESSION VARIABLES LIKE 'autocommit'; -- 对话级
-- 设置自动提交
SET GLOBAL autocommit=0; -- 禁止全局自动提交
SET SESSION autocommit=0; -- 禁止对话自动提交
SET GLOBAL autocommit=1; -- 开启全局自动提交
SET SESSION autocommit=1; -- 开启对话自动提交
-- 使用BEGIN,ROLLBACK<COMMIT提交事务
BEGIN    -- 开启事务
INSERT INTO users(id,NAME,a,b,c)VALUE(2,"李四","a","b","c");
ROLLBACK -- 回滚事务
COMMIT   -- 提交事务

隔离级别

        MySQL 是一个服务器／客户端架构的软件，对于一个服务器而言，可以拥有多个连接，也可以称之为多个会话。不同的会话可以同时发送请求，也就是说服务器可能同时在处理多个事务，这样子就会导致不同的事务可能同时访问到相同的记录。

        理论上，当有一个事务正在处理时，其他的事务应该排队等待，但是这种模式会急剧削减数据库的性能，为此，提出了隔离级别，来提高是数据库并发处理事务的能力。 

 查看隔离级别

SELECT @@session.transaction_isolation,@@transaction_isolation;

         Mysql 数据提供四种不同级别的隔离级别,实际开发中可以根据不同的需要场景，选择不同的隔离级别,除了串行级别以外其他级别都会存在某种问题.

设置隔离级别

读未提交(read uncommitted)：

一个事务可以读取到另一个事物未提交的修改数据，会带来赃读，幻读，不可重复读的问题。

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

读已提交(read committed)：

一个事务只能读取另一个事务已经提交的修改,避免了赃读，但仍存在不可重复读和幻读的问题。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

可重复度(repeatable read)：

同一个事务中多次读取相 同的数据返回的是一样的。其避免了脏读和不可重复读问题，普通查询解决了幻 读问题,如果在查询中添加 for update 语句, 会出现幻读问题.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

串行化(serializable)：

当一个事物对一行进行操作时(即使是读)，如果事务还没有提交，其他事务都不能执行。

事务串行执行,避免了以上所有问题。

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

赃读：B事务读取到A事务未提交的的修改数据，当A事务回滚时，B读取到的数据就是脏数据。

幻读： B事务读取到A事务未提交的的增/删数据，这就是幻读，前后两次读取到的记录不一样多。

不可重复读：在同一个事务中连续两次读取同一个数据，结果不一致。

事务实现原理

InonDB存储引擎提供了两种事务日志redolog(重做日志)和undolog(回滚日志)。其中redolog用于保证事务持久性；undolog则是实现事务原子性和隔离性的基础。

        原子性实现原理：在底层使用undolog(回滚日志)日志，保存一个相反的操作，例如执行一个insert操作，那么undolog日志中就会记录一个相反的delete操作，当事务撤销或回滚时，执行反向操作。
        持久性实现原理：使用redolog(重做日志)保证持久性，执行sql时，先将sql写入到redolog中，然后在执行保存到数据库，万一在这个过程中，数据还没有写入到硬盘中，突然断电，那么下次mysql启动时，会重新执行redolog日志，保证持久性

事务隔离实现原理

        MVCC（多版本并发控制 Multi-Version Concurrent Control）,是MySQL 提高性能的一种方式，配合 Undo log 和版本链,让不同事务的读-写、 写-读操作可以并发执行，从而提升系统性能。

        MVCC 使得数据库读不会对数据加锁，普通的 SELECT 请求不会加锁， 提 高 了 数 据 库 的 并 发 处 理 能 力 。 借 助 MVCC ， 数 据 库 可 以 实 现 READ COMMITTED，REPEATABLE READ 等隔离级别.

        InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现的。一个保 存了行的事务 ID（TRX_ID），一个保存了行的回滚指针（ROLL_PT）。

可重复读：第一次Select,Name="张三"，第二次Select,Name="张三"。

读已提交： 第一次Select,Name="张三"，第二次Select,Name="张小三"。

        对该记录每次更新后，都会将旧值放到一条 undolog 中，就算是该记录 的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pt 属性连接成一 个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。 另外，每个版本中还包含生成该版本时对应的事务 id，这个信息很重要。

读已提交级别:当每个事物每次读取时,会生成一个 readVew(读视图),读取的是最新数据.

可重复读级别: 为了实现可重复读，底层每次实物操作时，都会生成一个版本，对于内行的多个操作，每个版本之间存在关系，形成一个版本链

锁机制

概念

        事务在修改数据之前，需要先获得相应的 锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的， 其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

        多个事务(线程)对同一个表中的同一行数据进行操作,需要通过锁进行保护。InnoDB引擎支持行级锁,可以将锁的粒度到行级.

行锁，间隙锁，表锁 

表锁(粒度最大)

操作时会所在整张表, 效率低 , myisam就只支持表锁，并发度最低。

间隙锁

当条件为一个范围区间时,只对某个区间进行加锁,称为间隙锁,innodb支持间隙锁的.

行锁(粒度最小)

操作时,只会锁住当前操作的行,并发量高, innodb支持行锁

共享锁/排他锁

共享锁(S)

         又称读锁，A事务对记录1进行读操作时,添加了共享锁, 那么其他事物就不能对记录1进行修改,不能加排他锁，但是其他事物可以对记录1进行读操作.

        加锁之后，可读不可写。

select 列名 from 表名 lock in share mode;

排他锁(X)

        又称写锁，A事物对记录1进行修改,删除操作时,会自动添加锁,其他事物就不能记录1加任何锁，直到A事物释放锁才可以.

         加锁之后，其他事务不可加锁，直到锁释放。

select 列名 from 表名 for update ;

SQL优化

         项目初期，业务并发量少时，SQL的执行效率影响不是很明显，当并发量逐渐增大，SQL的运行效率就会很重要，这时优化SQl就是很有必要的了。

SQL优化的方向和思路

1.查询时尽量不使用*，而是具体字段。

        节省资源，减少开销

2.避免在WHERE语句中使用OR连接条件。

        使用OR可能导致索引失效，进行全表扫描

3.使用整型替代字符串类型

        开关使用0/1，性别0/1，字符会降低查询和连接性能。

4.使用varchar替代char

5.对查询进行优化，尽量避免全表扫描

        首先考虑对where/order by/group by后的字段添加索引

6.尽可能避免索引失效

        in和not in 要慎用，能使用between,就不使用in(,,)

        尽量少使用模糊查询

        避免在where语句中使用函数操作

7.提高group by 语句的效率

        先筛选在分组

8.清空表时，优先使用truncate，

        速度优于delete，且使用资源更少，trucate通过释放存储表数据所用的数据页来删除数据

9.表连接不宜过多，索引不宜过多，一般5个以内

10.深度分页问题

-- 优化前
select id,name from account limit 100000,10;
-- 优化后
select id,name FROM account where id > 100000 order by id limit 10;

        区别在于优化前对扫描前100000条数据，优化后会提高这部分性能

11.使用explain 分析SQL执行计划

EXPLAIN

        explain可以获取

表的读取顺序

数据读取操作的操作类型

那些索引可以使用

哪些索引被实际使用

表之间的引用

        在SELECT语句之前添加EXPLAINI关键字，执行查询会返回执行计划的信息，而不是执行SQL.

 EXPLAIN执行后的信息有12列，其中

 id:选择标识符

 select_type:表示查询的类型。

 table:输出结果集的表

 type:表示表的连接类型

 possible_keys:表示查询时，可能使用的索引

 key:表示实际使用的索引

 key_len:索引字段的长度

 rows:扫描出的行数(估算的行数)

参考阅读：

CSDN:学习【Mysql进阶篇】这一篇就够了 

                MySQL进阶（已完结）

                12条 SQL 优化方案（非常实用）

外站：MYSQL（进阶篇）——一篇文章带你深入掌握MYSQL