sql进阶_sql 进阶

---

一、锁

1.全局锁

全局锁就是对整个数据库所有表加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的备份。

flush tables with read lock ;
mysqldump -uroot –p1234 itcast > itcast.sql
unlock tables ;
1
2
3

2.表锁

1.表锁

表共享读锁（read lock）：其他客户端读写阻塞 ，加锁客户端可读可写
表独占写锁（write lock）：其他客户端读写阻塞， 加锁客户端可读可写

lock tables score read/write；
unlock tables;
1
2

2.元数据锁

meta data lock , 元数据锁，简写MDL：当一客户端的事务中有增删改查操作时，其他客户端也可以增删改查（alter会阻塞），但客户端事务有alter时，其他客户端的事务就所有操作都会阻塞。(ddl和dml/dql互斥)。

mdl存放在performance_schema.metadata_locks中 ;

3.意向锁（我们使用select … lock in share mode后，加表锁共享锁不会阻塞）

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行
数据是否加锁，使用意向锁来减少表锁的检查。
意向共享锁(IS): 由语句select … lock in share mode添加 。 与 表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。
意向锁存放在performance_schema.data_locks;

3.行锁

1.行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在
RC、RR隔离级别下都支持。
2.间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事
务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
3.临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。 在RR隔离级别下支持。
4.Record Lock、Gap Lock、Next-Key Lock分为共享锁（s）和排他锁（x）。我们SELECT … LOCK IN SHARE MODE后加的是s锁，其他事务可以读取被锁住的数据，但不能对s锁锁住的数据执行改写（dml）。同样我们使用dml，加的就是x锁，其他事务就不能读取和改写被锁住的数据了。
行锁保存在performance_schema.data_locks。
间隙锁&临键锁生成条件：
索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。
索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key
lock 退化为间隙锁。
索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

4.意向锁和表锁的关系

使用select lock in share mode 会加行锁（s）和意向锁（is），使用DML时会加行锁（x）和意向锁（ix）。
我们使用select lock in share mode 加表读锁（read lock）不会阻塞。使用dml会read lock和write lock阻塞。

二、innoDB

1.逻辑存储结构

表空间：表文件（xxx.ibd）。如果用户启用了参数 innodb_file_per_table，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。
段：分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段
（Rollback segment）。
区：表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一
个区中一共有64个连续的页。
页：是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。
行，InnoDB 存储引擎数据是按行进行存放的。

2.内存结构

1. Buffer Pool：在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。
   缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：
   • free page：空闲page，未被使用。
   • clean page：被使用page，数据没有被修改过。
   • dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。

2.change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page
没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer
中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。
3.Adaptive Hash Index 自适应hash索引，用于优化对Buffer Pool数据的查询。
4. Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认日志在每次事务提交时写入并刷新到磁盘。

3.磁盘结构（存放一个个idb文件的文件夹）

1.System Tablespace：系统表空间是更改缓冲区的存储区域，同时也存放着系统表。
2.File-Per-Table Tablespaces：innodb_file_per_table开关 ，则每个表的文件表空间包含单个InnoDB表的数据和索引 ，并存储在文件系统上的单个数据文件中。
3. General Tablespaces：需要通过 CREATE TABLESPACE 语法创建通用表空间。

创建表空间
CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name;
创建表时指定表空间
CREATE TABLE xxx ... TABLESPACE ts_name;
1
2
3
4

4.Undo Tablespaces：存储undo log日志
5.Temporary Tablespaces：InnoDB 使用会话临时表空间和全局临时表空间。
6. Doublewrite Buffer Files：双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。
7.重做日志文件： Redo Log：重做日志，是用来实现事务的持久性。

4.线程

1). Master Thread核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收 。
2). IO Thread

Purge Thread：主要用于回收事务已经提交了的undo log。
Page Cleaner Thread：协助 Master Thread 刷新脏页到磁盘的线程

5.事务原理

1.特性：

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

2.redolog

当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。
而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，当多个事务提交，innodb的log thread开始将脏页刷新到磁盘。假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性。
我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。 而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。 这种先写日志的方式，称之为 WAL（Write-Ahead Logging）。

3.undolog

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和
MVCC。
Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment
回滚段中，内部包含1024个undo log segment。
在insert、update、delete的时候产生的便于数据回滚的日志。当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。 而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

4.当前读和快照读

1.当前读：读取的是记录的最新版本。select … lock in share mode会加行共享锁，所以我们就不能修改改行数据了，因而事务再次读取，也是最新版本的数据，因而select … lock in share mode就是当前读。
2.快照读：简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
3.注意：
• Read Committed：每次select，都生成一个快照读。
• Repeatable Read：开启事务后第一个select语句才是快照读的地方。
• Serializable：快照读会退化为当前读。

5.MMVC

1.隐藏字段：表的每一行都会有三个隐藏字段，DB_TRX_ID用于判断改字段是由哪个事务id生成修改的，并将上一版本的字段写入到undolog中，DB_ROLL_PTR指向上一版本的字段。

2.readview
不同的隔离级别，生成ReadView的时机不同：
READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView

我们select某一行，会生成一个readview，记录当前活跃的事务id，当读取的buffer pool中的数据时，会根据该行的事务id判断改行修改的数据是否已经提交，如果提交，读取，如果没有提交，读取undolog中最近提交的改行数据。也就是说，我们读取事务id为min_trx_id-1的数据和创建creator_trx_id数据。
rr和rc的区别在于，rc每次select都会生成一个readview，而rr只在第一次select中生成一个readview。

三、索引

1.索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

2.B+Tree

我们看到，B+Tree 与 B-Tree相比，主要有以下三点区别：
1.所有的数据都会出现在叶子节点。
2.叶子节点形成一个双向链表。
3.非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

3.hash

MySQL中除了支持B+Tree索引，还支持一种索引类型—Hash索引。
如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可
以通过链表来解决。

4.索引术语解析

聚集索引(Clustered Index)将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
二级索引(Secondary Index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键
回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取
数据的方式，就称之为回表查询。
覆盖索引： 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。
单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。

5.sql性能分析

1.SQL执行频率

可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

SHOW GLOBAL STATUS LIKE 'Com_______';
1

2.慢查询日志

通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。

3.profile

-- 查看每一条SQL的耗时基本情况 
show profiles; 
1
2
3

4.explain