MySQL进阶_2-优快云博客

1、事务

1.1 简介

事务是一组操作的集合，这组操作，要么全部执行成功，要么全部执行失败。

1.2 提交与回滚

开启事务：start transaction

提交事务：COMMIT

回滚事务：ROLLBACK

查看事务提交方式：select @@autocommit；

设置事务提交方式：set @@autocommit = 1； 0：手动 1：自动

1.3 四大特性

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

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

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

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

1.4 并发事务问题

1、丢失修改：T1事务对数据库的修改被T2事务覆盖而丢失了，破坏了事务的隔离性。

2、不可重复读：同一事务先后读取同1条记录，但2次读取的数据不同，T2事务干扰了T1事务的隔离性。

3、读脏数据：T1事务读取到T2事务已修改但还未提交的数据，之后T2事务回滚，T1读取的是被丢弃的垃圾值。

4、幻读：T1事务按照条件查询数据时，没有对应的数据行，但在插入数据时，又发现这行数据已存在。

1.5 隔离级别

1、read uncommitted(读未提交)：最低级别，任何情况都无法保证。

2、read committed(读已提交)：可避免读脏数据。

3、repeatable read(可重复读)：可避免读脏数据，不可重复读。

4、serializable(串行化)：最高级别，可避免读脏数据、不可重复读、幻读。

注：事务隔离级别越高，数据越安全，但是性能越低。

查看事务隔离级别：select @@transaction_isolation；

设置事务隔离级别：set [session/global] transaction isolation level session(会话级)：当前客户端窗口

{read uncommitted/read committed/repeatable read/serializable} global(全局)：所有客户端窗口

2、存储引擎

2.1 MySQL体系结构

连接层：接收客户端的连接，完成连接的处理，认证授权，校验用户名及密码，检查是否超过最大连接数。

服务层(SQL接口、解析器、查询优化器、缓存)：跨存储引擎的实现。

引擎层：引擎的增删，控制SQL中数据的存储、提取的方式。 index索引在引擎层实现

存储层：最终数据的存储位置，包含一系列的文件、日志、数据库数据

2.2 存储引擎

2.2.1 InnoDB

简介：

是一种兼顾可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎。

特点：

1、DML操作遵循ACID模型，支持事务。

2、行级锁，提高并发访问性能。

3、支持外键约束，保证数据的完整性和正确性。

文件：

xxx.IBD (可在cmd中用 ibd2sdi 文件名命令打开)

2.2.2 MyISAM

简介：

MySQL早期的默认存储引擎。

特点：

1、不支持事务，不支持外键。

2、支持表锁，不支持行锁。

3、访问速度快.

文件：

xxx.sdi：存储表结构信息→可直接打开，但需在网页用json.cn格式化

xxx.MYD：存储数据

xxx.MYI：存储索引

2.2.3 Memory

简介：

该引擎的表数据存储在内存中，易受影响，只能将表作为临时表或者缓存使用。

特点：

1、内存存放

2、hash索引(默认)

文件：

xxx.sdi：存储表结构信息

2.3 存储引擎的选择

InnoDB：对事务的完整性有较高要求，在并发条件下要求数据的一致性，增删改查较多。

MyISAM：应用以增、查为主，切对事务的完整性、并发性要求不高。

MEMORY：访问速度快，用于临时表及缓存。但对表的大小要求有限制，太大的表无法存储，无法保证数据的安全性。

注：不同的存储引擎，其索引结构不同。

查看系统所支持的引擎种类：show engines；

设置引擎：create table xxx（......） engine = xxx;

3、索引

3.1 索引概述

一种高效获取数据的有序的数据结构，以某种发生指向数据。

（无索引→全表扫描）

优点：

1、提高数据查询的效率，降低数据库的IO成本。

2、降低数据排序的成本，降低CPU能耗。

缺点：

1、索引列占用空间。

2、降低更新表的效率。

3.2 索引结构

3.2.1 常规数据结构

注：二叉树的缺点：数据在顺序插入时，会形成单向链表，查询性能降低。

大量数据存储时，层级越深，速度越慢。

1、红黑树：自平衡二叉树

2、B Tree：多路平衡查找树——中点向上分

度数 = 指针数 = key数 + 1

3、B+ Tree：所有数据都会出现在叶子节点

叶子节点形成单向链表

非叶子节点起索引作用

MySQL中，B+Tree多了指向相邻叶子结点的链表指针，形成顺序的双向链表，提高了区间访问的性能。

3.2.2 MySQL中的索引结构

1、B+ Tree索引：最常见，大部分引擎所支持

2、Hash(Memory特有)：底层为hash表，不支持范围查询，无法完成排序操作，只支持精准(等值）匹配，通常只需一次查询，性能高。

3、R-Tree(空间索引) (MyISAM特有)：主要用于地理空间数据类型。

4、Full-text(全文索引)：一种通过建立倒排索引，快速匹配文档的方式。

3.3 索引分类

3.3.1 普通索引

1、主键索引(PRIMARY)：针对表中主键创建的索引 (默认自动创建，只能有一个)

2、唯一索引(UNIQUE)：避免同一个表中某数据列中的值重复

（创建唯一字段，自动生成唯一索引）

3、常规索引：快速定位特定数据

4、全文索引(FULLTEXT)：查找的是文本中的关键字，而不是比较索引中的值

3.3.2 InnoDB中的索引

1、聚集索引：将数据存储与索引放到一块，索引结构的叶子节点保存了行数据。

（必须有，而且只有一个）

（有主键则选用主键，没有则选第一个唯一索引，再无则自动生成一个rowid作为隐藏的聚集索引）

2、二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键。

（可以存在多个，可根据对应的主键走聚集索引）

注：回表查询：先走二级索引，找到主键值→再走聚集索引，得到行数据。

3.4 索引语法

1、查看表中的索引：show index from 表名（/G）； /G表示将结果转换为行表示

2、创建索引：create [unique / fulltext ] 索引名 on 表名（字段名）；单字段对应单列索引

(默认B+Tree结构) idx_表名__字段名多字段对应联合索引

3、删除索引：drop index 索引名 on 表名；

3.5 SQL性能分析

3.5.1 2个分析语句

1、查看服务器状态信息（客户端连接成功后）：show [session / global] status；

2、查看增删改查的访问频次：show global status like 'Com____';(七个下划线)

3.5.2 慢查询日志

介绍：

记录了所有执行时间超过指定参数（默认10秒）的所有SQL语句的日志。

开启（默认没有开启）：

用vi编辑器在MySQL的配置文件(/etc/my.cnf)中配置：

slow_query_log = 1; —打开慢查询开关

long_query_time = 2; —设置指定时间为2秒

配置完重启MySQL：systemctl restart mysql；

生成：

生成的慢查询日志存放在/var/lib/mysql/localhost_slow.log里面

（符合要求的语句自动写入）

语法：

查询慢查询日志的状态：show varis like 'slow_query_log';

3.5.3 profile

介绍：

了解时间耗费。

语法：

1、了解指令的时间耗费：show profiles；

2、查看系统是否支持profile：select @@have_profiling；

3、查看profile开关：select @@profiling；

4、开启profile：set profiling = 1;

5、查看指定query_id的耗时：show profile for query query_id；（query_id是一个具体的数值，由查看时间耗费语句可知）

6、查看指定query_id的CPU能耗：show profile cpu for query query_id；

3.5.4 explian执行计划

介绍：

获取MySQL如何执行select语句的信息，包括select语句中表的连接顺序。

语法：

查看执行信息：在select语句前加 desc/explain

各字段含义：

id：select查询的序列号，表操作表的顺序

id相同，执行顺序由上至下

id不同，值越大越先执行

select_type：查询的类型

simple：简单表，不使用表连接/子查询

primary：主查询，即外层的查询

union：union中的第二个或者是后面的查询语句

subquery：select / where之后包含了子查询

type：表连接类型

性能由好→差

null→system→const→eq_ref→ref→range→index→all

possible_key：可能应用在这张表上的索引

key：实际应用到的索引

key_len：索引中使用的字节数，为索引字段最大可能长度

非实际使用的长度，越短越好

rows：预估的查询行数

filtered：返回结果的行数占需读取的行数的百分比，越大越好。

extra：额外的信息

3.6 索引使用规则

1、最左前缀法则

联合索引时，查询从索引最左列开始，不跳过索引中的列。

若跳过某一列，索引将部分生效(后面的字段索引失效)，若跳过最左列，则整个索引失效。

“最左”与摆放位置无关，但必须存在（用and连接表示并列时）

2、范围查询

联合索引中，若出现范围查询（如<、>），范围查询右侧的索引无效，范围查询的列有效

可加上 = 号规避

3、失效情况

索引列运算

不要在索引列上进行运算操作（包括函数运算），索引将会失效，改为全表扫描。

字符串不加引号

字符串类型字段使用时，若不加引号，索引将失效，改为全表扫描。

模糊查询

（头部确定）尾部模糊匹配，索引正常不生效

（尾部确定）头部模糊匹配，索引失效

or连接的条件

or连接的条件，各列需均有索引，否则原有索引会失效。

数据发布影响

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

4、SQL提示—位于from的表之后

（优化数据库的一种手段，即加入人为的提示）

1、建议使用：use index（）

2、忽略使用：ignore index（）

3、必须使用：force index（）

5、覆盖索引

查询使用了索引，并且需要返回的列（包括了主键ID），在该索引中已能全部找到。

减少select *

6、前缀索引

只对字符串的一部分前缀建立索引：create index 索引名 on 表名(字段名（n）)→表示取字段的前n位

7、联合索引

结构：按字段优先排序，相同则转入下一字段

3.7 索引设计原则

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

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

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

4、若字符串的字段长度较长，可用前缀索引。

5、控制索引数量，要考虑维护索引结构的代价和增删改的效率。

6、不能存储null值的列，在创建时要用not null约束。

4、SQL优化

4.1 insert优化

批量插入，手动提交事务，主键顺序插入>乱序插入

大批量插入数据时，可用load指令

1、客户端连接服务端时，加上参数--local-infile

mysql --local-infile -u root -p

2、设置全局参数--local-infile为1，开启从本地加载文件导入数据的开关。

set global local_infile = 1；

3、执行load指令

load data local infile '/路径/xxx文件' into table 表名 fields terminated by ',' lines terminated by '\n';

4.2 主键优化

1、乱序插入出现页分裂。

2、删除页记录超过50%，出现页合并。

3、记录降低主键长度（二级索引的叶子节点受影响）。

4、插入数据时，顺序插入，选择用AUTO_INCREMENT自增主键。

4.3 order by优化（创建的索引默认升序）

1、using filesort：通过表索引或全表扫描，读取满足条件的数据行，在排序缓冲区中完成排序。

不通过索引的都是filesort排序。

2、using index：通过有序索引顺序扫描，直接返回有序数据，不需要额外排序，操作效率高。

（覆盖索引也算）

注：若字段全反→反向扫描+using index

一正一反：2中索引都用

order by中不用and，所以需要看索引最左字段的出现位置，其前面的字段不走索引

3、尽量使用覆盖索引，注意多字段排序的字段升降序。

4.4 limit优化

覆盖索引（生成表） + 子查询(连接)

4.5 count优化

效率排行

count(字段) < count(主键) < count(1) < count(*)

取值+判断取值不取值

4.6 update优化

更新条件有索引字段→+行锁

无索引字段→+表锁

4.7 group by优化

多字段分组满足最左前缀法则

5、InnoDB引擎

5.1 逻辑存储结构

表空间( ibd文件 )：一个MySQL实例可以对应多个表空间

用于存储记录、索引等数据

段：数据段——B+树的叶子节点

索引段——B+树的非叶子节点

回滚段

区：表空间的单元结构，每个区的大小为1M

一个区中有64个连续的页(16K)

页：InnoDB存储引擎磁盘管理的最小单元

(为保证页的连续性，每次申请4~5个区)

行：数据按行存放

5.2 架构

5.2.1 内存结构

1、Buffer Pool(缓冲池)

定义：缓存真实数据，增删改查时，先操作缓冲池中的数据(没有再找磁盘)

然后再刷新至磁盘，从而减少磁盘IO，加快处理效率

(以页为单位，底层用链表结构管理)

分类：

free page：空闲，未使用

clean page：被使用，数据未修改

dirty page：被使用，数据已被修改，与磁盘数据不一致

2、Change Buffer(更改缓冲池)

(针对非唯一二级索引页)

定义：在执行DML语句时，若该数据page在Buffer Pool中，不会直接操作磁盘，而会将数据变更存储在更改缓冲区 change buffer中，在未来数据被读取时，再将数据合并到Buffer Pool中，再刷新到磁盘。

作用：在缓冲池中进行数据合并处理，减少磁盘IO

3、Adaptive Hash Index(自适应hash索引)

定义：用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果检测到hash索引可以提升速度，则建立hash索引，称之为hash索引。

（自适应hash索引，无需人工干预，是系统根据情况自动生成）

参数：adaptive_hash_index

4、Log Buffer(日志缓冲区)

定义：用来保存要写入磁盘中的log日志数据（redo log、undo log），默认大小为16MB

日志缓冲区的日志会定期刷新到磁盘中，增加日志缓冲区大小可节省磁盘IO

参数：innodb_log_buffer_size：缓冲区大小

innodb_flush_log_at_trx_commit：日志刷新到磁盘时机

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

1：日志在每次事务提交时写入并刷新到磁盘

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

5.2.2 磁盘结构

1、System Tablespace(系统表空间)

定义：是更改缓冲区的存储区域

参数：innodb_data_file_path

2、File_Per_Table Tablespaces(每张表的独立文件表空间)

定义：包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中。

参数：innodb_file_per_table(默认打开)

3、General Tablespaces(通用表空间)

创建语法：create tablespace 表空间名字 add datafile ‘关联的ibd表空间文件’ engine = 存储引擎名；

在创建表时，可以指定该表空间

语法：create table 表名 tablespace 表空间名字；

4、undo tablespaces(撤销表空间)

定义：MySQL实例在初始化时，会自动创建2个默认的undo表空间(初始大小为16M)

用于存储undo log日志

5、Temporary Tablespaces(临时表空间)

定义：InnoDB使用会话和全局临时表空间，存储用户创建的临时表等数据

6、Double Buffer Files(双写缓冲区)

定义：innodb在数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件，便于系统异常时恢复数据

⇒生成 .dblwr文件

7、redo log(重做日志)

定义：用来实现事务的持久性

事务提交后，会把所有修改信息存到该日志中，用于在刷新脏页到磁盘中，发生错误时进行数据恢复使用

构成：

重做日志缓冲（redo log buffer），存于内存中

重做日志文件（redo log file），存于磁盘中

5.2.3 后台线程

作用：将内存中缓冲池数据在合适时机刷新到磁盘文件中

1、Master Thread(核心后台线程)

定义：负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保存数据的一致性，还包括脏页的刷新、合并缓存的插入。

2、IO Thread

定义：负责IO请求的回调 (innodb用异步IO，即AIO处理IO请求)

分布：

read 4个读操作

write 4个写操作

log 1个日志缓冲区刷新至磁盘

insert buffer 1个写缓冲区刷新至磁盘

查看：show engine innodb status；

3、Purge Thread

定义：用于回收事务已经提交的undo log

4、page cleaner Thread

定义：协助Master Thread刷新脏页到磁盘的线程

减轻Master Thread的工作压力，减少阻塞

5.3 事务原理

1、redo log（重做日志）

定义：记录事务提交时数据页的物理修改，实现事务的持久性

2、undo log（回滚日志） ——逻辑日志（记录于操作相反的指令）

定义：记录数据被修改前信息，实现事务的原子性

作用：提供回滚和多版本并发控制（MVCC）

销毁：事务执行时产生，事务提交时不会立即删除，因为这些日志可能还用于MVCC

存储：采用段的方式进行管理和记录，存放在回滚段中，内部包含1024个段

注：redo + undo ⇒ 一致性

MVCC + 锁 ⇒ 隔离性

5.4 MVCC

5.4.1 基本概念

1、当前读：读取的是记录的最新版本，读取时保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

select ............lock in share mode;

select ............for update、update 、insert、delete

2、快照读：简单的select（不加锁）就是快照读，读取的是记录数据的可见版本，有可能是历史数据，非阻塞锁

分类：read committed：每次select之后，生成一个快照读

repeatable read：开启事务后第一个select语句才是快照读的地方

serializable：快照读会退化为当前读

3、MVCC——多版本并发控制

定义：指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能

具体实现依赖：三个隐式字段，undo log日志，readview

5.4.2 实现原理

1、隐藏字段

DB_TRX_ID：最近修改事务ID

记录插入 / 最后一次修改该记录的事务ID

DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本

用于配合undo log，指向上一个版本

DB_ROW_ID：隐藏主键，若表结构没有指定主键，将会生成该隐藏字段

2、undo log（回滚日志）

在insert、update、delete的时候产生的便于数据回滚的日志。

insert时，产生的undo log日志只在回滚时需要，事务提交后，可被立即删除

update、delete时，产生的undo log日志不仅在回滚时需要，在快照读页需要，不会立即被删除

3、undo log版本链

定义：不同或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表

链表的头部是最新的旧记录，链表的尾部是最早的旧记录

4、read view（读视图）

定义：是快照读SQL执行时，MVCC提取数据的依据

记录并维护系统当前活跃事务(未提交)的ID

4个核心字段：

1、m_ids：当前活跃的事务ID集合

2、min_trx_id：最小活跃事务ID

3、max_trx_id：预分配事务ID，目前最大事务ID+1

4、creator_trx_id：read view创建者的事务ID

trx_id：当前事务的ID

规则：

trx_id = 4 ⇒ 可访问该版本：数据是当前事务更改的

trx_id < 2 ⇒ 可访问该版本：数据已提交

trx_id > 3 ⇒ 不可访问该版本：说明该事务是在read view生成后才开启

2 <= trx_id <= 3 ⇒ 可访问该版本：数据已提交

注：read committed：事务每一次执行快照读时，生成read view

repeatable read：仅在事务中第一次执行快照读时，生成read view，后续复用该read view

6、MySQL管理

6.1 系统数据库

1、mysql：存储MySQL服务器正常运行所需要的各种信息

（时区、主从、用户、权限等）

2、information_schema：提供了访问数据库元数据的各种表和视图

包含数据库、表、字段类型及访问权限等

3、performance_schema：为MySQL服务器运行时的状态提供了一个底层监控功能

主要用于收集服务器性能参数

4、sys：包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图

6.2 常用工具

1、mysql（客户端工具）

语法：mysql [options] [database]

选项：-u ：--user = name 指定用户名

-p ：--password[=name] 指定密码

-h ：--host = name 指定服务器IP / 域名

-P ：--port = port 指定连接端口

-e ：--execute = name 执行SQL语句并退出

2、mysqladmin（执行管理操作的客户端程序）

：用于检查服务器配置和当前状态，创建、删除数据库

帮助文档：mysqladmin -help；

3、mysqlbinlog（日志管理工具）

：检查二进制日志文件

语法：mysqlbinlog [options] log-file1 log-file2........

选项：-d ：--database = name 指定数据库名称

-o ：--offset = # 忽略掉日志中的前n行命令

-r ：--result-file = name 将输出的文本格式日志输出到指定文件

-s ：--short-form 显示简单格式，省略掉一些信息

--start-datetime = date1 --stop-datetime = date2 指定日期间隔内的所有日志

--start-position = post1 --stop-position = post2 指定位置间隔内的所有日志

4、mysqlshow（客户端对象查找工具）

：查看存在哪些库、表、列、索引

语法：mysqlshow [options] [db,name[table.name[col.name]]]

参数：--count 显示库及表的统计信息

-i 显示指定库、表的状态信息

5、mysqldump

：用于备份数据库或在不同数据库之间进行数据迁移

备份内容包含创建表、插入表的SQL语句

语法：mysqldump [options] db_name[tables] > 备份至的文件名

--database / -B db1 [db2 db3 .......]

--all-databases / -A

连接选项：-u ：--user = name 指定用户名

-p ：--password[=name] 指定密码

-h ：--host = name 指定服务器IP / 域名

-P ：--port = port 指定连接端口

输出选项：--add-drop-database ：在每个数据库创建语句前加上drop database语句

table ：表 (默认开启)

-n ：--no-create-db ：不包含数据库的创建语句

-t ：--no-create-info ：表

-d ：--no-data ：不包含数据（不包含插入语句）

-T ：--tab=name ：自动生成2个文件；一个.sql文件，创建表结构语句

一个.txt文件，数据文件

6、mysqlimport(客户端数据导入工具)

：用来导入mysqldump加 -T 参数后导出的txt文本文件

语法：mysqlimport [options] de_name textfile1 [textfile2]

source textfile;(.sql文件)

7、日志

7.1 错误日志

定义：记录了当MySQL启动和停止时，以及服务器在运行过程中发生任何严重错误的相关信息。

（默认开启，存放于/var/log/mysqld.log）

参数：show variables like '%log_error%';

7.2 二进制日志（binlog）

定义：记录了所有的DDL和DML语句，不包含数据查询语句（select、show）

作用：1、灾难时的数据恢复

2、MySQL的主从复制

参数：show variables like '%log_bin%';（日志默认开启）

查看格式：show variables like '%lbinog_format%';

格式：

STATEMENT：基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL语句都会记录在日志文件中。

ROW：基于行的日志记录，记录的是每一行的数据变更，

MIXED：以上2中的混合，默认采用前者，特殊情况下自动切换为后者。

日志查看：

mysqlbinlog [oprions] logfilename；

-v ：将行事件（数据变更）重构为SQL语句

-vv ：将行事件（数据变更）重构为SQL语句，并输出注释信息

日志删除：

reset master ：删除全部binlog日志，日志编号重新开始。

purge master logs to 'binglog.xxx' ：删除binglog.xxx编号前的所有日志。

purge master logs before 'xxxx-mm-dd hh24 :mi :ss' ：删除指定日期之前的所有日志。

查看自动删除参数（配置文件中，设过期时间）：

show variables like '%binlog_expire_logs_seconds%';

7.3 查询日志

定义：记录了客户端的所有操作语句。（默认未开启）

查看：show variables like '%general%';

开启：在/etc/my.cnf中

general_log = 1; 开启

general_log_file = ........log; 设置日志的文件名