Mysql进阶知识

最新推荐文章于 2024-06-16 05:34:45 发布

小吕学编程

最新推荐文章于 2024-06-16 05:34:45 发布

阅读量95

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_59465624/article/details/131954021

版权

Mysql体系结构

存储引擎

innodb

文件

xxxibd:xxx代表的是表名
innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm、sdi)、数据和索引。

引擎对比

索引

概念

帮助mysql高效获取数据的数据结构（有序）

优缺点

索引结构

B树

B+树

所有数据都会在叶子节点

叶子节点之间存在单链表，可以用来进行范围查找

在mysql数据库中的数据结构

为什么InnoDB存储引擎选择使用B+tree索引结构？

相对于二叉树，层级更少，搜索效率高
对于B树，无论叶子节点和非叶子节点都会保存数据，每一个节点的内存大小又是固定的，如果保存同样大量的数据，只能增加树的高度，导致性能降低
对于Hash索引，B+树支持范围查找

索引分类

索引语法

查看索引

show index from 表名;

删除索引

drop index 索引名称 on 表名

创建索引

name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
为profession、age、status创建联合索引。

-- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
CREATE INDEX idx_user_name on tb_user(nick_name)
-- phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引
CREATE INDEX idx_user_phone on tb_user(phone)
-- 为profession、age、status创建联合索引
CREATE INDEX idx_name_email_sex ON tb_user(nick_name,email,sex);

性能分析

访问次数查询

查询增删查改访问次数

show global status like 'Com___'

慢查询

查看慢查询日志

SHOW VARIABLES LIKE 'slow_query_log';

SHOW VARIABLES LIKE '%slow%';

本地慢查询日志位置：D:\mysql5\mysql-5.7.40-winx64\data\lvyiting-slow.log

打开慢日志开关，并且设置日志查询的时间为2秒

slow_query_log=1
long_query_time=2

profiling

可以查询每一个sql语句耗时情况

是否支持profiling： select @@have_profiling;

查看profiling：SHOW PROFILES;

explain

查看sql的执行计划，可以看sql语句是否使用了索引，索引的使用情况，以及sql的性能

EXPLAIN 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

最左前缀法则

详细看

MySQL-进阶篇.pdf

索引失效

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效（解决方案：使用>=、<=）
不要在索引列上进行运算操作，索引将失效。

explain select * from tb_user where substring(phone,10,2) = '15';

字符串类型字段使用时，不加引号，索引将失效。
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

如果MySQL评估使用索引比全表更慢，则不使用索引。

查询时MySQL会评估，走索引快，还是全表扫描快，如果全表扫描更快，则放弃索引走全表扫描。因此，is null 、is not null是否走索引，得具体情况具体分析，并不是固定的。

SQL提示

通过sql语句强制一条sql语句使用哪一个索引

use index ：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

ignore index ：忽略指定的索引。

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

force index ：强制使用索引。

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

覆盖索引

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

思考题：一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案:

select id,username,password from tb_user where username = 'itcast';

答案: 针对于 username, password建立联合索引, sql为: create index idx_user_name_pass on tb_user(username,password);

这样可以避免上述的SQL语句，在查询的过程中，出现回表查询

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

用法：

create index idx_xxxx on table_name(column(n)) ;

联合索引不需要回表查询的原因：

索引设计原则

针对数据量大并且查询比较频繁的表建立索引
针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
尽量选择区分度高的列（例如身份证）作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。

SQL优化

insert优化

批量插入
数据量大时，采用数据量分段插入，并且手动提交事务

主键顺序插入
超大数据的插入通过的load指令进行插入

Order by优化

Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

优化原则：

A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

B. 尽量使用覆盖索引。

C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。

D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

Group by优化

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

A. 在分组操作时，可以通过索引来提高效率。

B. 分组操作时，索引的使用也是满足最左前缀法则的。

Limit查询优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

优化思路: 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

count优化

数据量太大时可以通过redis进行计数

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)。

视图

语法

创建

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

查询

查看创建视图语句：SHOW CREATE VIEW 视图名称;

查看视图数据：SELECT * FROM 视图名称 ...... ;

删除

DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ..

演示实例

-- 创建视图
CREATE OR REPLACE VIEW user_view_1 as SELECT id,phone,nick_name from tb_user;
-- 查询视图
SHOW CREATE VIEW user_view_1;
-- 查看视图数据
SELECT * FROM user_view_1;
-- 删除
DROP VIEW if EXISTS user_view_1;

作用

安全、简单

案例

1). 为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。

create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user;

select * from tb_user_view;

2). 查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。

create view tb_stu_course_view as select s.name student_name , s.no student_no , c.name course_name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id;

select * from tb_stu_course_view;

锁

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

表级锁

行级锁

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁，如果加锁的不是索引，就会由行级锁升级为表记锁

分类

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

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

临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

幻读解决

事务A 按照一定条件进行数据读取，期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

InnoDB引擎

架构

内存结构

Buffer Pool

InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O 。缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

• free page：空闲page，未被使用。

• clean page：被使用page，数据没有被修改过。

• dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。

Change Buffer

Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page 没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer 中，在未来数据被读取时，再将数据合并恢复到BufferPool中，再将合并后的数据刷新到磁盘中。

Log Buffer

Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。

参数:

innodb_log_buffer_size：缓冲区大小

innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，

取值主要包含以下三个：

1: 日志在每次事务提交时写入并刷新到磁盘，默认值。

0: 每秒将日志写入并刷新到磁盘一次。

2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。

事务

概念

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

特性

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。

隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

undo log

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和 MVCC(多版本并发控制) 。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的 update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

MVCC

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

readView字段：

不同的隔离级别，生成ReadView的时机不同：

READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。

REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

小吕学编程

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql进阶知识

Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page 没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer 中，在未来数据被读取时，再将数据合并恢复到BufferPool中，再将合并后的数据刷新到磁盘中。当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。在RR隔离级别下都支持。
复制链接

扫一扫