Mysql进阶

最新推荐文章于 2025-03-25 21:58:09 发布

刘zy_9527

最新推荐文章于 2025-03-25 21:58:09 发布

阅读量604

点赞数 29

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/qq_45576281/article/details/144692546

版权

Mysql进阶

一. Mysql服务器内部架构

1.1 连接层

负责客户端的链接，验证账号密码等授权认证

1.2 服务层

对sql进行解析，优化，调用函数，如果是查询操作，还要看有没有缓存等操作

1.3 引擎层

是真正负责数据存储和提取的地方， mysql中提供各种引擎进行数据处理

1.4 物理文件存储层

物理存储表数据，以及各种日志文件的地方

二. Mysql引擎

2.1 概述

mysql中的引擎就是实际对数据操作的一种实施者，不同的引擎所使用的技术不同

引擎种类可以使用：SHOW ENGINES查询

主要的两种引擎是innodb和myisam

2.2 innodb

是一种综合能力比较强的引擎，支持事务，行级锁，外键约束，全文索引，支持数据缓存等功能。支持主键自增，不存储表的总行数(统计表的总行数，innodb中默认不存储，需要自己查询)。适合增删改较多，且数据重要的场景

2.3 myisam

不支持事务，只支持表锁，增删改操作时会锁定整个表。效率低，适合查询较多的情况。

支持全文索引，存储表的总行数。

三. 索引

3.1 什么是索引？

数据库索引是为了实现高效数据查询的一种排好序的数据结构，索引类似于书的目录，通过目录可以快速的定位到想要找到的数据。因为一张表中的数据会有很多，如果直接去表中检索数据会效率低(逐行查找)。所以需要为表中的数据建立索引(一般主键默认会创建索引)，这样就会提高查询效率。

3.2 索引优势

通过索引可以快速定位到数据，降低IO次数，提高效率。

排序列添加索引，也可以提高排序的效率(因为索引是有序的)

3.3 索引劣势

索引保存也是需要占用空间的。

增删改数据时，数据发生变化，索引也需要随之变动，也是需要开销的。

3.4 索引创建原则

3.4.1 哪些场景适合创建索引：

主键自动创建索引
查询条件列
多使用组合索引(多个列用一个索引)，减少单值索引
建议排序和分组使用到的列
对数据量大的表

3.4.2 哪些场景不适合：

表记录太少(类型表，菜单表，友情链接，系统信息表)
查询条件中用不到的列
经常增删改的表：提高了查询速度，但是同时会降低更新表的速度。因为mysql不仅要保存数据还要保存一下索引文件
重复率高的列(性别：男，女)

3.5 索引分类

3.5.1 主键索引

创建表时，设置哪个列为primary key就是主键列，主键列默认会自动创建索引。

create table test1(
	id int primary key		-- 创建表时直接设置主键
)
create table test2(
	id int
)
ALTER TABLE test2 add primary key test2(id);	-- 后来修改表结构，设置主键
ALTER TABLE test2 drop primary key;		-- 删除主键

3.5.2 唯一索引

如果索引列的值必须唯一，则使用唯一索引，比如身份证号，学号，但同时出现时也得考虑身份证号和学号的优先度，只能选取一个唯一索引，允许为null。数据库系统会自动确保某一列的数据不重复

create table test1(
	id int primary key
	account varchar(20) unique	-- 设置唯一约束，会添加唯一索引
)
create table test2(
	account varchar(20)
)
CREATE unique index index_unique_account on test2(account);

3.5.3 单值索引

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

create index index_test1_name on test1(name)
drop index index_test1_name on test1	-- 删除索引

3.5.4 组合(复合)索引

一个索引中包含多个列，节省了索引开支

create index index_test1_name_age on test1(name,age)

在使用组合索引时，需要注意一个问题：满足组合索引最左前缀原则

在使用组合索引时，条件中必须要用到最左侧的列，负责索引失效

eg：a，b，c3个列，a和b创建组合索引

使用时 where a=1 and b=2 索引生效

where a=1 and b=2 索引生效

where c=1 and b=2 索引不生效

explain select * from test3 where b=2 and c=3	-- 查看sql执行计划发现key为null说明没用到索引

3.5.5 前缀索引

有些列长度比较大，需要只给前面指定的长度区间添加索引即可。

create index 索引名 on 表名(列名(长度))

3.5.6 全文索引

模糊查询时，即使列有索引，也会导致索引失效

可以为列添加全文索引

create fulltext index index_test2_title on test2(title) with parser ngram;
explain select * from test2 where match(title) against('法国大使馆')

使用ngram是解析方式，用match函数匹配

3.5.7 查看索引

show index from 表名

3.6 索引数据结构

由于二叉树，平衡二叉树一个节点只能存储一个元素，再加上mysql使用自增主键，导致不适合二叉树，平衡二叉树。

mysql底层使用的是b+树

一个节点中可以存储多个索引数据

表数据都存储在叶子节点，非叶子节点不存储表数据，只存索引，这样一个节点就可以存储更多的索引

叶子节点之间还有指针指向，所以非常适合范围查询(一个区间)

3.7 聚簇索引和非聚簇索引

区分方式：找到了索引就找到了你要找的数据这种为聚簇索引

3.7.1 聚簇索引

innodb引擎中主键索引就是聚簇索引，主键和数据在一个树上。

3.7.2 非聚簇索引

myisam引擎中，由于索引和数据分别在两个不同的文件中存储，找到了索引，还需要重新查找一次，才能找到数据，这种称为非聚簇索引。

innodb引擎中，像普通的索引也称为二级索引，它们也是非聚簇索引

例如姓名，通过名字查找人的所有信息时，在姓名索引树中找到后，还需要再主键索引树中再次进行查找，最终在主键索引树中找到数据，这种称为非聚簇索引。

3.8 回表查询

回表查询指的是查询时的次数。

例如学生id，学号，姓名三个信息，设置id是主键，学号添加唯一索引

select * from student where id =1 通过id(主键)查询学生所有的信息只需要一次查询即可
select * from student where 学号=2 通过学号查询学生的所有信息，由于学号是普通索引，先通过学号在学号索引树上找学号，然后通过id回表二次查询主键索引树。一共查询了两次，称为回表查询。
select 学号 from student where 学号=1 通过学号查询学号自己(判断学号是否存在)，由于使用学号只查询学号本身，并不查询其他数据。这种情况下，我们可以再学号索引树上直接找到学号数据，这种情况不需要回表查询，也可以称为是聚簇索引。

3.9 索引下推

mysql5.6版本引入的一项查询优化技术，将条件筛选过程下推到索引树上。

以前没有索引下推，先找具体的数据，然后再对数据进行条件过滤，查询的数据范围比较大。现在使用索引下推，直接在索引树上进行条件筛选，筛选出符合条件的记录，然后只将满足条件的记录进行回表查询。

减少了回表查询的次数，使用的是非主键索引。

查看使用索引下推：给查询语句前面加explain，Extra中显示using index condition:using就表示使用了索引下推。

四. 事务

4.1 什么是数据库事务？

首先数据库事务是数据库对执行操作的一种管理机制。保证在同一个事务中，一次执行的多条sql语句使一个不可分割的单元，多条sql要么都执行，要么都不执行。

4.2 数据库事务特征：

原子性：一次执行的多条sql，使一个不可分割的单元，多条sql要么都执行，要么都不执行，这是事务最基本的特点。
持久性：保证事务提交后，数据在数据库是持久保存的，即使操作时，出现宕机。
隔离性：mysql是运行多个并发事务同时对数据进行读和写操作的，这时可以采用不同的隔离级别进行控制。

隔离级别：1. 读未提交 2. 读已提交 3. 可重复读 4. 串行化一次只允许一个事务操作
```
-- 查看隔离级别
SELECT @@session.transaction_isolation,@@transaction_isolation;
```
session：当前会话，也就是当前连接立即生效。

global：全局，不包含当前连接，之后新获取的连接都会生效。
一致性：数据库事务终极目标，在我们对数据库多次操作过程中，最终要保证数据和我们预期的结果是一致的。eg：转账，多次对同一个账号的金额进行操作，最终结果不能出现错误。

-- mysql默认是开启自动事务提交的
SHOW GLOBAL VARIABLES LIKE 'autocommit';	-- 查看autocommit格式
SHOW SESSION VARIABLES LIKE 'autocommit';

-- 禁止自动提交
SET GLOBAL/ SESSION autocommit=0; 
-- 开启自动提交
SET GLOBAL/ SESSION autocommit=1;

begin -- 开启一个数据库事务
insert into employees(id,name,salary)value(5,'李四',5000);
commit; -- 提交事务
rollback; -- 回滚事务

4.3 隔离级别：

4.3.1 读未提交

一个事务可以读到另一个事务还未提交的数据，并发最高的，也是最不安全的

问题：会出现脏读，不可重复读，幻读问题。

脏读：A事务修改了数据，还没提交，这时被B事务读到了，但是A事务有可能出错回滚，这种情况下，B事务读到的数据是垃圾数据

-- 设置事务为读 未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

4.3.2 读已提交

一个事务只能读到另一个事务已提交的数据

能够解决脏读问题，但是没有解决不可重复读和幻读问题

不可重复读问题：在同一个事务中，读取同一个id的数据两次，两次读到的数据不一致。

-- 设置事务为读 已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

4.3.3 可重复读

同一个事务读取多次相同数据，多次读取返回的结果是一致的。

可重复读解决了不可重复读问题。

-- 设置事务为可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

普通的查询解决了幻读问题，如果在查询语句后面添加了for update(给查询加锁升级到和update同级别)，就会出现幻读问题。

幻读问题：同一个事务中，多次读取数据，读到行数不同。

mysql中默认隔离级别是可重复读

4.3.4 串行化

相当于加锁了，解决以上所有的问题，当一个事务操作时，其他事务必须等待，即使执行的是查询操作。

-- 设置事务为串行化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

4.4 事务实现原理

4.4.1 持久性原理

使用redo log日志文件(重做日志)保证已提交事务的数据持久保持。

当事务提交后，先用redo log日志文件进行存储，因此在此过程中，有可能宕机

如果此时当即，确保操作数据存储记录下来(日志文件中)，这样再服务恢复时，可以继续将日志文件中的数据，写入到物理硬盘上。

4.4.2 原子性实现

使用undo log日志文件，当我们执行一个insert语句时，再undo log日志文件中记录一个delete语句。如果执行delete语句，在日志文件中记录insert语句。

也就是记录一个操作的反向操作。

当事务回滚时，执行undo log日志中的反向操作

4.4.3 隔离性实现

提到了MVCC机制(多版本并发控制)，每次事务对数据操作时，都会记录一个历史记录(记录事务id，还会记录上一次操作事务id)。

还提到了一个readView(读视图)：是快照读sql执行时，MVCC提取数据的一句，记录并维护系统当前活跃的事务(未提交事务)id

当隔离级别为读已提交时：在一个事务中，每次读时，都会从历史版本记录中，获取一个最新的快照，这样就会导致每次读到的数据是最新的数据，也就会出现不可重复读问题。

当隔离级别为可重复读时：在第一次读时，会获取一个快照，之后再次读取时，还是从第一次生成的快照中读数据，所以，两次读到数据是一样的，解决了不可重复读。

4.4.4 一致性实现

以上三个都满足即可实现一致性。

五. 锁

mysql中读写不互斥(前提是没有使用串行化隔离级别)

但是写写操作是要互斥才行，mysql中使用锁机制来实现写写互斥。

按照锁的粒度分为：

5.1 全局锁

锁定整个数据库，只允许读操作。一般在备份数据库时使用

-- 添加全局锁
FLUSH TABLES WITH READ LOCK
-- 释放全局锁 
UNLOCK TABLES
-- 备份数据库语句
mysqldump--single-transaction-uroot-proot 库名> E:/文件名.sql

5.2 表级锁

给整个表加锁，myisam引擎只支持表锁，innodb默认支持行锁

-- 加锁
lock tables 表名 read/write
-- 释放锁
unlock tables

5.3 行级锁

加锁的粒度以行为单位，行级锁又可分为：

5.3.1 行锁(Record Lcok)

只锁定操作的那一行数据，使用的主键作为条件

5.3.1.1 共享锁

主要是为查询语句添加的，查询语句如果添加了共享锁，那么其他事务可以读，但是其他事务不能写(指同一条记录)

-- 为查询语句添加共享锁
select * from employees where id = 1 lock in share mode

也是读锁，允许多个事务读，但不允许读写同时进行

5.3.1.2 排他锁

就是互斥锁，当一个事务操作时，其他事务就不能进行加共享锁和排他锁操作。insert,update,delete操作时，自动会添加排他锁。

查询操作如果需要添加排他锁，可以在查询语句后面添加for update语句

select * from employees where id=1 for update

也是写锁，只允许一个事务读写操作

5.3.2 间隙锁(Gap Lock)

SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

锁定的是一个范围 id>1 and id<10

5.3.3 临建锁(Next-Key Lock)

eg：表数据 age有24 35 39岁

SELECT * FROM table WHERE age = 24 FOR UPDATE;
UPDATE table SET name = Vladimir WHERE age = 24;

这两条语句都会造成当前事务A获取临建锁(24,35]，那么事务B就不可以对这个区间的age对应的行进行select或update

是行锁和间隙锁的结合，左开右闭

六. SQL优化的一些方法

6.1 查询sql尽量不要使用select *，而是具体字段

节省资源减少开销

6.2 尽量使用数值替代字符串类型

eg：

主键(id)：primary key优先使用int

性别(gender)：0代表女，1代表男

数据库没有布尔类型，mysql推荐使用tinyint

因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于int而言只需要比较一次就够了。

6.3 使用varchar代替char

varchar变长字段按数据内容实际长度存储，可以节省存储空间。

而char按声明大小存储，不足的会补空格。

6.4 对查询进行优化

应尽量避免全表扫描，首先应该考虑where 及 order by group by涉及的列上建立索引

6.5 应尽量避免索引失效

6.5.1

应尽量避免在where子句中使用or来连接条件，否则会导致引擎放弃使用索引而进行全表扫描比如：

select id from t where num=10 or num=20;

6.5.2

in 和 not in也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3);

能用between就不要用in：

select id from t where num between 1 and 3;

6.5.3

模糊查询也将导致全表扫描：

select id from t where name like '%abc%'

6.5.4

应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描：

selectid from t where substring(name,1,3)='abc'

6.6 提高group by语句的效率

应该先过滤，后分组

错误❌：

SELECT user_id, SUM(amount) FROM orders
GROUP BY user_id
HAVING city = '北京';

正确✔：

SELECT user_id, SUM(amount) FROM orders
WHERE city = '北京'
GROUP BY user_id;

6.7 清空表时优先使用truncate

truncate table 比 delete 速度快，且使用的系统和事务日志资源少. delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table 通过释放存储表数据所用的数据页来删除数据

6.8 表连接不宜太多，索引不宜太多，一般5个以内

联的表个数越多，编译的时间和开销也就越大每次关联内存中都生成一个临时表应该把连接表拆开成较小的几个执行，可读性更高

6.9 深度分页问题

错误❌：

select id,name from account limit 100000,10;

正确✔：

select id,name FROM account where id > 100000 order by id limit 10;

6.10 使用explain分析SQL执行计划

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL 是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

作用：

表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用

概要描述：

id:选择标识符

select_type:表示查询的类型

table:输出结果集的表

type:表示表的连接类型

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

key:表示实际使用的索引

key_len:索引字段的长度

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

extra:执行查询时的一些额外信息，这些信息有助于理解查询的执行计划和优化数据库性能。

possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引。如果为NULL，则没有使用索引,或者索引失效

Mysql进阶

Mysql进阶

一. Mysql服务器内部架构

1.1 连接层

1.2 服务层

1.3 引擎层

1.4 物理文件存储层

二. Mysql引擎

2.1 概述

2.2 innodb

2.3 myisam

三. 索引

3.1 什么是索引？

3.2 索引优势

3.3 索引劣势

3.4 索引创建原则

3.4.1 哪些场景适合创建索引：

3.4.2 哪些场景不适合：

3.5 索引分类

3.5.1 主键索引

3.5.2 唯一索引

3.5.3 单值索引

3.5.4 组合(复合)索引

3.5.5 前缀索引

3.5.6 全文索引

3.5.7 查看索引

3.6 索引数据结构

3.7 聚簇索引和非聚簇索引

3.7.1 聚簇索引

3.7.2 非聚簇索引

3.8 回表查询

3.9 索引下推

四. 事务

4.1 什么是数据库事务？

4.2 数据库事务特征：

4.3 隔离级别：

4.3.1 读 未提交

4.3.2 读 已提交

4.3.3 可重复读

4.3.4 串行化

4.4 事务实现原理

4.4.1 持久性原理

4.4.2 原子性实现

4.4.3 隔离性实现

4.4.4 一致性实现

五. 锁

5.1 全局锁

5.2 表级锁

5.3 行级锁

5.3.1 行锁(Record Lcok)

5.3.1.1 共享锁

5.3.1.2 排他锁

5.3.2 间隙锁(Gap Lock)

5.3.3 临建锁(Next-Key Lock)

六. SQL优化的一些方法

6.1 查询sql尽量不要使用select *，而是具体字段

6.2 尽量使用数值替代字符串类型

6.3 使用varchar代替char

6.4 对查询进行优化

6.5 应尽量避免索引失效

6.5.1

6.5.2

6.5.3

6.5.4

6.6 提高group by语句的效率

6.7 清空表时优先使用truncate

6.8 表连接不宜太多，索引不宜太多，一般5个以内

6.9 深度分页问题

6.10 使用explain分析SQL执行计划

作用：

概要描述：

possible_keys

key

4.3.1 读未提交

4.3.2 读已提交