Mysql高级

Mysql高级

Mysql服务器内部架构

连接层

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

服务层

对sql进行解析,优化,调用函数,如果是查询操作,会查询内部的缓存

引擎层

是真正负责数据存储和提取的地方,mysql中提供多种引擎进行数据处理,可以根据需要进行选择

物理文件存储层

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

MySql引擎

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

引擎种类: SHOW ENGINES

查看表引擎 :SHOWTABLE STATUS LIKE ‘表名’

修改引擎

方式1:将mysql.ini 中default-storage-engine=InnoDB，重启服务.

方式2:建表时指定 CREATETABLE 表名(…)ENGINE=MYISAM;

方式3:建表后修改 ALTERTABLE 表名 ENGINE=INNODB;

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

主要讲innodb和myisam

innodb

是一个综合能力比较强的引擎,支持事务,行级锁,外键约束,全文索引,支持数据缓存等功能.支持主键自增,不存储表的总行数(统计表的,innodb中默认不存储,需要自己查询)

适合增删改较多,且数据重要的场景

MyIsAm

不支持事务,只支持表锁,增删改操作时会锁定整个表,效率低下,适合查询较多的情况,支持全文索引,存储表的总行数

索引

数据库索引是为了实现高效数据查询的一种排好顺序的数据结构

索引是帮助MySQL高效获取数据的数据结构。 排好序的快速查找的数据结构. 数据库在存储数据本身之外，还维护着一个满足特定查找算法的数据结构， 这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高 级查找算法，这种数据结构就是索引。

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址. 为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分 别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉 查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录

为什么要有索引呢？

假设有一张表，表中有100万条数据，这100万条数据在硬盘上是存储在 数据页上的，一页数据大小为16K，存储100万条数据需要很多数据页，假设 其中有一条数据是id=‘7900’，如果要查询这条数据，其SQL是 SELECT * FROM 表名称 WHEREid=7900。mysql需要扫描全表来查找id=7900的记 录。全表扫描就是从“数据页1”开始，向后逐页查询。对于少量的数据，查询 的速度会很快，但是，当随着数据量的增加，性能会急剧下降。100万条数据逐 页查询的时间是无法被用户接受的

索引原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先 定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查 字典，查火车车次，飞机航班等. 本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果， 同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总 是用同一种查找方式来锁定数据。 索引类似于书的目录，在一本书前面加上目录，查找内容时不必逐页翻阅就 能够快速地找到所需的内容。借助索引，执行查询时不必扫描整个表就能够快速 地找到所需要的数据。

索引优势

提高数据检索的效率,降低数据库的IO成本;

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

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

索引劣势

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

增删改数据时,数据发生变化,索引也随之需要变动,需要开销大

索引创建原则

哪些情况需要建立索引?

主键自动建立唯一索引

作为查询条件的字段应该创建索引(where 后面的语句)

尽量使用联合索引，减少单列索引 针对于数据量较大，且查询比较频繁的表建立索引。

查询中排序的字段，分组中的字段,若通过索引去访问将大大提高排序速度

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

哪些情况不适合创建索引?

表记录太少

经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进 行INSERT、UPDATE和DELETE,因为更新表时，MySQL不仅要保存数据， 还要保存一下索引文件

Where条件里用不到的字段不创建索引

数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数 据列建立索引，某个数据列包含许多重复的内容，建立索引没有太大实际效果。

索引分类

主键索引

创建表时,设置列为primary key为主键列,主键列会默认生成索引

ALTER TABLE 表名 add PRIMARY KEY 表名(列名);
删除建主键索引：
ALTER TABLE 表名 drop PRIMARY KEY 

唯一索引

设置某个列数据唯一性,会创建唯一索引,索引列的值必须唯一，允许为null

生成索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名)
删除索引 
DROP INDEX 索引名 ON 表名

单值索引

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

创建单值索引
CREATE INDEX 索引名 ON 表名(列名)
删除索引：
DROP INDEX 索引名 ON 表名(列名)

组合索引(复合索引)

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

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

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

在使用组合索引时,条件中必须要用到最左侧的列,否则索引失效

例如:a,b,c三个列 a和b创建组合索引,使用时

select *from 表 where a =’ ’ and b=’ ’ 索引生效

select *from 表 where a =’ ’ and c=’ ’ 索引生效

select *from 表 where b =’ ’ and c=’ ’ 索引失效

前缀索引

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

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

全文索引

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

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名)WITH PARSER ngram;

SELECT 结果 FROM 表名 WHEREMATCH(列名)AGAINST ('搜索词')

索引的数据结构

B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树 （B-Tree），B+树即由这些树逐步优化而来。使其更适合实现外存储索引结构， InnoDB 存储引擎就是用B+Tree实现其索引结构。

排好序的,一个节点可以存储多个数据.

非叶子节点不存储数据,只存储索引,可以放更多的索引.

数据记录都存放在叶子节点中.

所有叶子节点之间都有一个链指针

Mysql 索引使用的是B+树，因为索引是用来加快查询的，而B+树通过对 数据进行排序所以是可以提高查询速度的，然后通过一个节点中可以存储多个元 素，从而可以使得B+树的高度不会太高.并且叶子节点之间有指针，可以很好的 支持全表扫描，范围查找等SQL语句。

由于二叉树,平衡二叉树一个节点只能存储一个元素,mysql使用自增主键,导致不适合二叉树,平衡二叉树,mysql底层使用的是B+树,一个节点可以存储多个索引数据,表数据都存储在叶子节点,非叶子节点不存储表数据,只存储索引,这样一个节点就可以存储更多的索引,叶子节点之间还有指针指向,所以非常适合范围查询

聚簇索引和非聚簇索引

区分方式:找打了索引就找到了数据,这种设计就称为聚簇索引

聚簇索引

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

非聚簇索引

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

innodb引擎中 普通的索引也称为二级索引,他们也是非聚簇索引. 例如通过姓名查找人的信息时,在姓名索引树找到后,还需要在主键索引树中在此进行查找,最终在主键索引树中找到数据,这种称为非聚簇索引.

回表查询

指的是查询时的次数

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

select * from student where id= 1 通过id(主键)查询学生所有的信息,这时只需查询一次即可

select * from student where 学号=12 通过学号查询学生的所有信息,由于学号是普通索引,先通过学号,在学号索引树上查找学号,然后通过id去回表二次查询主键索引树,查询两次,称为回表查询

select 学号 from student where 学号=12 通过学号查询学号(判断是否存在),由于使用学号只查询学号本身,并不查询其他数据,这种情况下,我们可以在学号索引树上直接找到学号数据,这种情况不需要回表查询,也可称为聚簇索引

索引下推

将条件筛选的过程下推到索引树上,若没有索引下推,一般先找到具体的数据,再对数据进行条件过滤,查询的数据范围就比较大,使用索引下推,直接在索引树上进行条件筛选,筛选出符合条件的记录,然后只将满足条件的记录进行回表查询,减少了回表查询的次数.适用的是非主键索引

如何查看索引下推生效

我们可以使用EXPLAIN语句来查看索引下推是否在查询中生效。

在EXPLAIN 输出的Extra 列中，如果出现Usingindex condition，这意味着 MySQL 在该查询中使用了索引下推优化

事务

什么是数据库事务?

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

事务特性

**原子性:**一次执行的多条sql,是一个不可分割的单元,多条sql要么都执行,要么都不执行

持久性:保证事务提交后,数据在数据库是持久保存的,即使操作时,出现宕机

隔离性:mysql是运行多个并发事务同时对数据进行读和写操作的,这时可以采用不同的隔离级别进行控制.

​ 隔离级别: 读-未提交 读-已提交 可重复读 串行化:一次只允许一个事务操作

一致性:数据库事务终极目标,在对数据库多次操作的过程中,最终保证数据和预期结果一致.

​ 转账案例:多次对同一个账号的金额进行操作,最终结果不能出现错误

事务隔离性中的隔离级别

提供四种隔离级别

**1.读 未提交(read uncommitted)😗*一个事务可以读到另一个事务还未提交的数据.并发最高,安全性最低

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

​ 问题:会出现脏读(A事务事务修改了数据,还没提交,这时被B事务读到,但是A事务有可能出错回滚,这种情况下B事务读到的数据就是垃圾数据),不可重复读,幻读问题

**2.读 已提交(read committed)😗*一个事务只能读到另一个事务已提交的数据.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

​ 问题:读 已提交 能够解决脏读问题,但是没有解决不可重复读(在同一个事务中读取同一个id的数据两次,两次读到的数据不一致)和幻读问题

**3.可重复读(repeatableread MySQL默认隔离级别)😗*同一个事务读取多次相同数据,多次读取返回的结果是一致的,解决了不可重复读问题

​ 问题:但是会出现**幻读( 同一个事物中多次读取读到数据行数不同)**问题

 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

普通查询结局了幻读问题,如果在查询语句后面添加了 for update 就会出现幻读问题

4.串行化(serializable)：相当于加锁,事务串行执行,避免了以上所有问题。当一个事物操作时,其他事物必须等待,即使执行的是查询操作

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

事务实现原理

MySQL的日志有很多种，如二进制日志、错误日志、查询日志、慢查询日 志等，此外InnoDB存储引擎还提供了两种事务日志：redolog(重做日志)和 undolog(回滚日志)。其中redolog用于保证事务持久性；undolog则是事务 原子性和隔离性实现的基础。

**1.持久性实现:**使用到redo log日志文件(重做日志) 保证已提交事务的数据持久保持,当事务提交后,先用redo log日志文件进行存储,因为在此过程中,有可能宕机.如果此时宕机,要确保操作数据存储记录下来(日志文件中),在服务恢复时,可以继续将日志文件中的数据写入到物理硬盘上.

**2.原子性实现:**使用到undo log日志文件,当我们执行一个insert语句时,在undo log日志文件中记录一个delete语句,执行delete语句,在日志中记录insert语句,记录了一个操作的反向操作,这样确保当事务回滚时,执行undo log日志中的反向操作.

**3.隔离性实现:**提到了MVCC机制(多版本并发控制),每次事务对数据操作时,都会记录一个历史记录(记录事务id和上一次操作事务的id)

还提到了一个read view(读视图),当隔离级别为读 已提交时:在一个事务中,每次读时都会从历史版本记录中获取一个最新的快照,这样就会导致每一次读到的数据是最新的,也就会出现不可重复读问题.

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

**4.一致性实现:**以上三个都满足实现,即可实现一致性

锁机制

mysql中读写不互斥(前提是没有使用串行化隔离级别) 写写操作是要互斥才行,mysql中使用锁机制来实现写写互斥.按照锁的粒度可以分为:

全局锁:锁定整个数据库,只允许读操作,一般在备份数据库时使用

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

**表级锁:**给整表加锁

myisam引擎只支持表锁 ,innodb默认支持行锁

特点:开销小，加锁快；锁定粒度大，发出锁冲突的概率最高，并发度最低。 表锁，分为两类： 1.表共享读锁 2.表独占写锁

语法： 1.加锁：lock tables 表名…read/write。 2.释放锁：unlock tables/客户端断开连接。

共享读锁,当客户端1添加共享读锁,客户端1和客户端2只能读,不能写

共享写锁,当客户端1添加共享写锁,客户端1能读,能写,客户端2不能读,不能写

**行级锁:**加锁的粒度以行为单位

行级锁可以分为: 1.行锁(只锁定操作的那一行数据 使用的主键作为条件)

2.间隙锁(锁定一个范围 id>2 and id<6)

3.临键锁(行锁和间隙锁组合，同时锁住数据，并锁 住数据前面的间隙)

行锁分为:

1.共享锁(S):允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。主要为查询语句添加,为查询语句如果添加了共享锁,其他事务可以读,但是不可以写(指定的是同一条记录)

2.排它锁(X):也称为互斥锁,允许获取排他锁的事务更新数据，阻止其他事务获得相同数 据集的共享锁和排他锁。当一个事物操作时,其他事务就不能进行加共享锁和排它锁

update,delete,insert 都会自动给涉及到的行数据加上排他锁， select 语句默认不会加任何锁类型.

如果需要为查询语句添加共享锁,可在查询语句后面添加 lock in share mode 如果需要为查询语句添加排他锁,可在查询语句后面添加 for update

Sql 优化

为什么要对SQL进行优化

项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运 行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率 有多大，故很少针对SQL进行专门的优化，而随着时间的积累，业务数据量的 增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化 就很有必要。

SQL优化的一些方法

1.查询SQL尽量不要使用select*，而是具体字段 节省资源、减少开销。

2 尽量使用数值替代字符串类型 正例 主键（id）：primary key优先使用数值类型int 性别（gender）：0代表女，1代表男；数据库没有布尔类型，mysql 推荐使用tinyint 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符； 而对于数字型而言只需要比较一次就够了； 字符会降低查询和连接的性能，并会增加存储开销；

3.使用varchar代替char varchar 变长字段按数据内容实际长度存储，可以节省存储空间； char 按声明大小存储，不足补空格； 其次对于查询来说，在一个相对较小的字段内搜索，效率更高

4.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 orderby group by涉及的列上建立索引

5.应尽量避免索引失效

​ 5.1 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引 而进行全表扫描，如:selectid fromt where num=10 or num=20

​ 5.2 in 和 not in 也要慎用，否则会导致全表扫描，如：selectidfromtwherenum in(1,2,3),对于连续的数值，能用 between 就不要用 in ,select id from t where numbetween 1 and 3

​ 5.3 模糊查询也将导致全表扫描 select id from t where name like ‘%abc%’

​ 5.4 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进 行全表扫描。如:selectid from t where substring(name,1,3)=‘abc’

6.提高group by语句的效率

反例:先分组，再过滤 正例:先过滤，后分组

7.清空表时优先使用truncate

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

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

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

9.深度分页问题

反例 select id,name from account limit 100000,10;

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

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

    执行计划

EXPLAIN

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

EXPLAIN 作用

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

EXPLAIN 使用

在select 语句之前增加explain关键字，执行查询会返回执行计划的信息， 而不是执行SQL。

EXPLAIN SELECT * FROM USER WHERE id = 1

id:选择标识符

select_type:表示查询的类型。

table:输出结果集的表

type:表示表的连接类型

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

key:表示实际使用的索引

key_len:索引字段的长度

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

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

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

key 实际使用的索引。如果为NULL，则没有使用索引,或者索引失效
之前增加explain关键字，执行查询会返回执行计划的信息， 而不是执行SQL。

EXPLAIN SELECT * FROM USER WHERE id = 1

[外链图片转存中…(img-QNd9Y1Ru-1734707237181)]

id:选择标识符

select_type:表示查询的类型。

table:输出结果集的表

type:表示表的连接类型

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

key:表示实际使用的索引

key_len:索引字段的长度

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

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

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

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