MySQL进阶篇

事务

事务简介

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

CREATE TABLE `account`  (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',
  `money` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '余额',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES (1, '小红', 2000);
INSERT INTO `account` VALUES (2, '小明', 2000);

默认MySQL的事务是自动提交的，当执行一条DML语句，MySQL会立即隐式的提交事务。

事务操作

-- 转账流程
-- 查询小红余额
SELECT * FROM account WHERE id = 1;
-- 更新小红余额-1000
UPDATE account SET money = money - 1000 WHERE id = 1;
-- 更新小明余额+1000
UPDATE account SET money = money + 1000 WHERE id = 2;

关闭事务的自动提交

查看/设置事务提交方式

-- 查看事务提交方式
SELECT @@autocommit;
-- 设置事务提交方式(1->默认提交)
SET @@autocommit = 0;

提交事务

1 2	-- 提交事务 commit;

回滚事务

1 2	-- 回滚事务 rollback;

显示开启事务

开启事务

1 2	begin -- 或者start transaction

提交事务

commit;

回滚事务

rollback;

事务四大特征（ACID）

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

一致性（Consistency）：事务执行前后，数据保持一致。
隔离性（Isolation）：并发访问数据库时，一个事务不会被另一个事务所干扰，各并发事务之间的数据库是独立的，这保证了事务在不受外部并发操作影响的独立环境下运行。

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

并发事务问题

脏读（Dirty read）

一个事务读取了另一个事务还没有提交的数据。

当事务1正在访问数据并对数据进行修改，而这种修改还提交时，事务2访问了这个数据并使用了这个数据，导致事务2读到了事务1还没有提交的脏数据。

丢弃修改（Lost to modify）

一个事务对数据的修改保存被另一个事务的修改顶替，导致前一个事务的修改丢失。

事务1读取一个数据，事务2同时读取了数据，事务1修改了这个数据并提交，事务2也修改了这个数据并提交，事务1修改的结果被丢弃。

不可重复读（Unrepeatable read）

一个事务内多次读取同一个数据，由于两次读取的间隔被其他事务修改，两次读取的数据不同。

幻读（Phantom read）

事务获取数据发现没有对应的数据行，在插入时发现该数据已经存在。

不可重复读和幻读的区别

不可重复读面向的是同一条记录，幻读面向的是同一个范围。

事务隔离级别

查看事务隔离级别

1	SELECT @@TRANSACTION_ISOLATION

设置事务隔离级别

1	SET [SESSION\|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED\|READ COMMITTED\|REPEATABLE READ\|SERIALIZABLE]

Read uncommitted

出现脏读

Read committed

避免脏读，出现不可重复读

Repeatable read

避免不可重复度读，出现幻读

SerialIzable

总结

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

总结

存储引擎

MySQL体系结构

连接层

服务层是与客户端连接服务，主要完成一些类似于连接处理、授权认证及相关的安全方案，服务会为安全接入的每个客户端验证他所具有的操作权限。

服务层

服务层完成核心服务功能，如SQL接入、缓存查询、SQL的分析和优化、部分内置函数的执行，所有跨存储引擎的功能在这一层实现，如过程、函数等。

引擎层

存储引擎负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，可以根据自己的需要，来选取合适的存储引擎。

存储层

将数据存储在文件系统之上，并完成与存储引擎的交互。

存储引擎简介

存储引擎是存储数据、建立索引、更新或查询数据等技术的实现方式。

存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

查询表的存储引擎

1 2	# 查询建表语句 --默认存储引擎：INNODB SHOW CREATE TABLE 表名

创建表时指定存储引擎

1
2
3

CREATE TABLE `表名` (
	...
) ENGINE=InnoDB

查看当前数据库支持的存储引擎

1 2	# 查看当前数据库支持的存储引擎 SHOW ENGINES;

存储引擎特点

InnoDB

介绍

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

特点

DML操作遵循ACID模型，支持事务
行级锁，提高并发访问性能
支持外键约束，保证数据的完整性和正确性

文件

xxx.ibd：xxx代表的是表名，InnoDB引擎的每张表都会对应一个表空间文件，存储该表的表结构、数据和索引。参数：innodb_file_per_table

进入MySQL Server的Data文件夹，查看表结构

1	ibd2sdi xxx.ibd

InnoDB的逻辑存储结构

MyISAM

介绍

MyISAM是MySQL早期的默认存储引擎

特点

不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快

文件

xxx.sdi：存储表结构信息

xxx.MYD：存储数据

xxx.MYI：存储索引

Memory

介绍

Memory引擎的表数据是存储在内存中的，由于受到硬件问题或断电问题的影响，只能将这些表作为临时表或者缓存使用。

特点

内存存放
hash索引（默认）

文件

xxx.sdi：存储表结构信息

总结

存储引擎选择

总结

索引

索引概述

介绍

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构即索引，这些数据结构以某种方式引用（指向）数据，以实现高级查找算法。

对比

二叉树索引结构的只是一个示意图，并不是真实的索引结构

索引优缺点

优点

索引提高数据检索的效率，降级数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点

索引列会占用一定磁盘空间
索引大大提高查询效率，同时降低更新表的速度（新增，修改和删除）

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包括：

B+ Tree索引：最常见的索引类型，大部分引擎支持B+树索引
Hash索引：底层数据结构使用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree空间索引：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text全文索引：一种通过建立倒排索引、快速匹配文档的方式，类似于Lucenes、Solr、ES

二叉树

如果MySQL的索引结构采用二叉树的数据结构，理想的情况下，查询效率为log2n；

但是如果主键是顺序插入，则会形成一个单向链表。

二叉树缺点

顺序插入时，形成一个链表，查询性能大大降低

大数据量的情况下，层级较深，检索速度慢

红黑树

红黑树是一颗自平衡二叉树

红黑树缺点

大数据量的情况下，层级较深，检索速度慢

B-Tree

B树是一种多路平衡查找树，B树的每个节点可以有多个分支，即多叉。

树的度数指的是一个节点的子节点个数。

通过一个数据结构可视化网站演示：https://www.cs.usfca.edu/~galles/visualization/BTree.html

顺序插入一组数据，观察树的变化过程

n阶B树特点

每一个节点最多存储n-1个Key，对应n个指针

一旦节点存储的Key数量超过n-1，中间元素就会向上分裂

B树中，非叶子节点和叶子节点都会存放数据

B+Tree

B+Tree是B-Tree的变种

以一颗最大度数为4（4阶）的B+Tree为例，我们可以看到：

绿色虚线框部分是索引部分，起到索引数据的作用，不存储数据

红色虚线框部分是数据存储部分，其叶子节点中存储具体的数据

通过一个数据结构可视化网站演示：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

与B-Tree的区别

所有的数据都会出现在叶子节点

叶子节点形成了一个单向链表

非叶子节点仅仅起到了索引数据的作用，具体数据都在叶子节点中存放

MySQL中对于经典的B+Tree数据类型进行了优化，在原有的基础上，增加一个指向相邻叶子节点的链表指针，形成了有序指针的B+Tree，提高了区间访问的性能，利于数据库的排序操作。

Hash

哈希索引就是采用一定的Hash算法，将键值换算成新的Hash值，映射到哈希表对应的槽位上，然后存储在hash表中。

如果两个（或多个）键值，映射到一个相同的槽位上，就产生Hash冲突（即Hash碰撞），可以通过链表来解决

特点

Hash索引只能用于对等比较（=，in），不支持范围查询（between,>,<,…）

无法利用索引完成排序操作

查询效率高，Hash索引在等值查询上比B+树效率更高

搜索引擎支持

在MySQL中，支持Hash索引的是Memory引擎，而InnoDB中具有自适应Hash功能，hash索引是在存储引擎根据B+Tree索引在指定条件下自动构建的

InnoDB存储引擎选择使用B+Tree索引结构

相对于二叉树，层级更少，搜索效率高

相对于B-Tree，无论是叶子节点还是非叶子节点都会保存数据，这会导致一页中存储的键值减少即指针减少，保保存同样的大量数据，只能增加树的高度，导致性能的降低

相对于Hash索引，B+Tree支持范围匹配及排序操作

索引分类

索引的具体类型

在MySQL数据库中，索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引

分类	含义	特点	关键字
主键索引	针对于表中的主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以存在多个	UNIQUE
常规索引	快速定位特定数据	可以存在多个
全文索引	查找是文本中关键词，而非比较索引中的值	可以存在多个	FULLTEXT

索引的存储形式

在InnoDB存储引擎中，根据索引的存储形式，可以分为聚集索引和二级索引。

分类	含义	特点
聚集索引（Clustered Index）	将数据存储与索引放在一起，索引结构的叶子节点保存行数据	必须有，而且只有一个
二级索引（Secondary Index）	将数据与索引分开存储，索引结构的叶子节点保存行数据对应的主键	可以存在多个

聚集索引选择规则

如果存在主键，主键索引就是聚集索引

如果不存在主键，将使用第一个唯一索引（UNIQUE）作为聚集索引

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

聚集索引和二级索引的结构实例

查询过程：select * from user where name = xxx

由于根据name自动进行查询，所以先根据name字段的二级索引中进行匹配查找，在二级索引中查找到数据对应的主键值

根据主键值到聚集索引中查找主键值对应的记录即具体数据。

回表查询：先到二级索引中查找数据对应的主键值，再到聚集索引中根据主键值获取具体数据的方式

思考

执行效率对比

InnoDB主键索引的B+Tree高度

索引语法

创建索引

1	CREATE [UNIQUE\|FULLTEXT] INDEX index_name ON table_name(index_col_name,...)

index_col_name：索引关联字段（一个：单列索引多个：关联索引或组合索引）

查看索引

1	SHOW INDEX FROM table_name

删除索引

1	DROP INDEX index_name ON table_name

实例

查看索引

创建普通索引

创建唯一索引

创建联合索引

SQL性能分析

SQL执行频率

通过SHOW [SESSION|GLOBAL] status命令可以提供服务器状态信息

1
2
3

-- session：当前会话
-- global：全局数据
SHOW GLOBAL STATUS LIKE 'XXX'

查询执行频次

-- 查看当前数据库INSERT、UPDATE、DELETE、SELECT的全局访问频次
SHOW GLOBAL STATUS LIKE 'Com_______';
-- Com_select：查询频次
-- Com_insert：新增频次
-- Com_update：修改频次
-- Com_delete：删除频次

通过上面的指令，可以查看当前数据库INSERT、UPDATE、DELETE、SELECT的全局访问频次，进而了解当前数据库是以查询为主还是以修改为主，从而为数据库的优化提供参考依据即如果以查询为主，我们可以考虑对数据库的索引进行优化。

慢查询日志

如果当前数据库以查询为主，我们可以借助慢查询日志定位需要优化的查询语句

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认：10秒）的所有SQL语句

慢查询日志默认没有开启，通过如下语句可以查看系统变量 slow_query_log

1	SHOW VARIABLES LIKE 'slow_query_log';

开启慢查询日志

cat >> /etc/mysql/my.cnf <<EOF

# 开启MySQL慢查询日志
slow_query_log=1

# 设置慢日志的时间为2秒
long_query_time=2

重启MySQL服务

1	docker restart mysql

查看慢查询日志状态

1	SHOW VARIABLES LIKE '%slow_query%';

测试

1	SELECT * FROM sys_user;

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

profile详情

通过show_profiles能够在SQL执行的各个过程时间消耗情况

查看MySQL是否支持profile

1	SELECT @@having_profiling;

开启profiling操作

默认profiling是关闭的，通过set语句在session/global级别开启profiling

1	SET profiling = 1;

常用指令

# 查看SQL语句耗时基本情况
SHOW profiles;

# 查看指定query_id的SQL语句各个阶段的耗时情况
SHOW profile FOR query query_id

# 查看指定query_id的SQL语句CPU的使用情况
SHOW profile CPU for query query_id

explain

通过EXPLAIN或者DECS指令可以获取MySQL执行SELECT语句的信息，包括执行过程中表如何连接、连接的顺序以及索引的使用，它可以帮助分析SQL问题，从而更好的使用索引以及优化查询语句。

语法

1 2	# 直接在select语句前加上关键字EXPLAIN/DESC EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

MySQL在执行一条查询之前会分析SQL语句，决定是否使用索引或全表扫描。使用EXPLAIN命令MySQL不会执行查询操作，经过SQL分析器分析后停止执行。

EXPLAIN执行计划中各个字段的含义

字段	含义
id	select 查询的序列号，表示查询中执行select子句或者是操作表的顺序（id不同，值越大先执行；id相同，执行顺序从上往下）
select_type	select的类型，常见的取值有SIMPLE（简单表，即不适用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（联合查询，即第二个或者后面的查询）、SUBQUERY（子查询）
type	连接类型，性能由好到差的连接类型为null > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL（一般来说，得保证查询至少达到range级别,最好能达到ref）
possible_key	显示可能应用在查询语句中的索引
key	实际使用到的索引，如果为null，则没有使用索引，如果为primary，则使用主键
key_len	最长的索引宽度，表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际长度。如果键是NULL，长度就是NULL。在不损失精确性的情况下，长度越短越好
ref	显示哪个字段或常数与key一起被使用
rows	这个数表示mysql必须执行查询的行数，在innodb引擎的表中，是一个估计值
filtered	表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

索引使用

验证索引效率

字段建立索引后，查询效率大大提升，验证了通过索引可以提升数据的查询性能。

最左前缀法则

如果索引关联多列即联合索引，需要遵守最左前缀法则。

最左前缀法则指的是查询需要从索引的最左列开始，并且不跳过索引中的列。如果跳出了索引的每一列，索引将部分失效（即后面的字段索引失效）。

等值匹配查询

在表中有一个联合索引关联了三个字段：brand_id，product_category_id，feight_template_id

对于最左前缀法则而言，查询时最左边的列必须存在，否则索引全部失效。如果中间不能跳过某一列，该列后面的字段索引将失效。

如果查询条件的字段是全部存在但是，索引依旧完全满足最左前缀法则。所以最左前缀法则中指的是最左边的列，是指查询时，联合索引的最左边的字段必须存在，这与查询语句的先后顺序无关。

范围查询

联合索引中，出现范围查询（>或<），范围查询右侧的列索引失效。

当范围查询使用>=或<=时，所有字段都走了索引。

在业务允许的情况下，尽可能的使用类似于>=或<=这类范围查询，而避免使用>或<

索引失效的情况

索引列运算

如果在索引列上进行运算操作，索引将失效

当根据索引字段进行等值匹配查询时，索引生效。

当根据索引字段进行函数运算操作后，索引失效。

索引列查询隐式转换

索引列查询时隐式转换，会导致索引失效。

常见的是字符串类型字段使用时，不加引号，索引将失效。

隐式转换：当两边的操作数类型不一致时，MySQL会发生类型转换以使操作兼容，这些操作是隐式的。

在字符串和数字操作数进行比较时，将其作为浮点数的比较，两边都是浮点数没有使用索引的原因是执行查询时，MySQL会使用CAST函数把每一行主键列的值转换为浮点数，再与条件参数做比较，在InnoDB存储引擎中，在索引列上使用函数会导致索引失效，导致全表扫描。

模糊查询

如果是尾部模糊匹配，索引不会失效

如果是头部模糊匹配，索引会失效

在like模糊查询中，在关键字后面加%，索引可以生效，在关键字前面加%，索引将失效。

or连接条件

查询条件用or分开，如果or前后有任一没有索引，涉及的索引都不会被用到

当or连接的条件，左右两边字段都有索引时，索引才会生效

数据分布影响

如果MySQL分析评估使用索引比全表查询更慢，则不使用索引

因为MySQL在查询时会评估使用索引的效率与全表扫描的效率，如果全表扫描更快，放弃索引，使用全表扫描。因为索引的作用是检索少量数据的，如果通过索引查询返回大量的数据，则不如全表扫描，此时索引失效。

SQL提示

上图中，查询语句中右两个索引可能被用到分别是组合索引和单列索引，MySQL会自动在其中选择一个。

我们可以用过SQL提示的方式指定使用的索引。

SQL提示是优化数据库的一个重要手段，它是在SQL语句中加入一些提示来达到优化操作的目的。

use index

1	EXPLAIN SELECT * FROM 表名 USE INDEX WHERE 查询条件

ignore index

1	EXPLAIN SELECT * FROM 表名 IGNORE INDEX WHERE 查询条件

force index

1	EXPLAIN SELECT * FROM 表名 FORCE INDEX WHERE 查询条件

覆盖索引

覆盖索引：查询使用索引并在该索引中已经全部能够找到需要返回的列

尽量使用覆盖索引，减少select *。

关注Extra的值有所不同，分别是Using index和Using index condition

Extra	含义
Using index	查找使用了索引，但是需要的数据都在索引列中可以找到，不需要回表查询
Using index condition	查找使用了索引，但是需要回表查询数据

联合索引关联了多个字段的信息，叶子节点存储了对应一行数据的id信息，当查询返回的数据在主键id和关联字段之中的时候，则直接走二级索引直接返回数据。如果超过了这个范围，就需要通过主键id去扫描聚集索引，再获取额外的数据即回表查询。依此一直使用select * 查询返回所有字段，很容易造车回表查询（除非根据主键id查询）

表结构以及索引示意图

id是主键即一个聚集索引，name是一个普通索引即一个二级索引（辅助索引）

根据id查询直接通过聚集索引查询，一次索引扫描，直接返回数据，性能高

通过二级索引根据name字段查询，由于查询返回的字段为id，name，分别是索引字段和主键id，这两个字段都可以直接获取到，这就是覆盖索引，不需要回表查询，性能高

当查询的返回字段在二级索引中不包含时，需要进行二次索引扫描，也就是需要回表查询，性能相对较差一点

前缀索引

当索引的字段类型为字符串（varchar,text.longtext等）时，索引变得很大，浪费大量的磁盘IO，影响查询效率。

前缀索引可以将字符串的一部分前缀建立索引，大大节约索引空间，提高索引的效率

语法

1	CREATE INDEX 索引名 ON 表名(字段名(n))

前缀长度

前缀长度可以根据索引的选择性决定，而索引选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引的选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能最好

1
2
3

SELECT COUNT(DISTINCT 表字段)/COUNT(*) FROM 表名;
SELECT COUNT(DISTINCT substring(表字段, 1, n))/COUNT(*) FROM 表名;
SELECT COUNT(distinct substring(brand_story, 1, 18))/COUNT(*) FROM pms_brand;

前缀索引的查询流程

单列索引与联合索引

单列索引：一个索引包含单个列

联合索引：一个索引包含多个列

使用了单列索引，查询会回表查询

创建一个联合索引

指定使用联合索引，无需回表查询

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议使用联合索引，而非单列索引。

通过联合索引的查询的流程示意图（无需回表查询）

索引设计原则

针对于数据量较大而且查询比较频繁的表建立索引
针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
如果时字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
尽量使用联合索引，减少单列索引，查询时联合索引一些时候可以覆盖索引，节省存储空间，避免回表
控制索引的数量，索引并不是越多越好，索引越多，维护索引结构的代价越大，会影响增删改查的效率
如果索引列不能存储NULL值，在创建表时需要对该列使用NOT NULL进行约束。优化器判断每列是否包含NULL值，可以更好地确定哪个索引最有效地用于查询

小结

SQL优化

插入数据

大量输入插入

如果我们需要往数据库一次性插入多条记录，可以有三个方面的优化

批量插入

1	INSERT INTO 表名 VALUES (字段值,...),(字段值,...),(字段值,...);

手动提交事务

START TRANSACTION;
INSERT INTO 表名 VALUES (字段值,...),(字段值,...),(字段值,...);
INSERT INTO 表名 VALUES (字段值,...),(字段值,...),(字段值,...);
INSERT INTO 表名 VALUES (字段值,...),(字段值,...),(字段值,...);
COMMIT;

主键顺序插入

1	主键顺序插入：1 2 3 4 5 7 8 9 15 21 88 89

大批量数据插入

如果一次性需要插入大批量数据，使用insert语句插入性能比较低，可以使用MySQL提供的load指令进行插入。

# 客户端连接服务端，加上参数 --local-infile
mysql --local-infile -uroot -p
# 设置全局参数local_infile=1,开启从本地加载文件导入数据的开关
SET global local_infile = 1;
# 查看本地加载文件导入数据开关是否开启
SELECT @@local_infile;

执行如下指令，将数据脚本文件中的数据加载到表结构中

1	load data local infile '/文件名.log' into table 表名 fields terminated by ',' lines terminated by '\n';

主键顺序插入性能插入高于乱序插入

主键优化

数据组织方式

在InnoDB存储引擎中，表数据是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（Index Organized table IOT）。

行数据都是存储在聚集索引的叶子节点上。

页（Page）是InnoDB磁盘管理的最小单元，行数据是记录在逻辑结构Page页中，每一个页的大小是固定的，默认16k。这意味着一个页中所存储的行是有限的，如果插入数据行row在该页存储不下，将会存储到下一页中，页与页之间会通过指针连接。

页分裂

每页中包含了2-N行数据（如果一行数据过大会出现行溢出）。

主键顺序插入

主键乱序插入

页合并

删除一行记录时，记录实际上并没有被物理删除，只是被标记（flaged）为删除状态并且它的空间允许被其他记录声明使用。

当页中删除记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会寻找最靠近页（前或后）看是否可以将两个页合并以优化空间使用。

MERGE_THRESHOLD：合并页的阈值可以自己设置，在创建表或者在创建索引时指定。

索引设计原则

在满足业务需求的情况下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键，防止过多的页分裂现象
尽量不要使用uuid或者其他自然做主键
业务操作时，避免对主键的修改

order by优化

MySQL排序的两种方式

Using filesort：通过表单索引和全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作。所有不是通过索引直接返回排序结构的排序结果的排序都是FileSort排序。
Using index：通过有序索引顺序扫描直接返回有序数据，不需额外排序，操作效率高。

Using index性能高，Using filesort性能低，在优化排序操作时，尽量要优化为Using index。

由于排序字段没有建立索引，排序出现了Using firesort，性能较低。

建立索引

倒叙排序

Extra中出现Backward index scan，代表反向索引。

MySQL中我们创建的索引默认索引的叶子节点是从小到大排序的，此时查询时从大到小，因此扫描时反向扫描，会出现Backward index scan。

在MySQL8版本中，支持降序索引，可以创建降序索引。

一个升序和一个降序

创建索引时，未指定顺序，默认按照升序排序

升序/降序联合索引结构示意图

值得注意的是，联合索引排序时，只支持覆盖索引，如果不是覆盖索引，需要回表查询数据，在排序缓冲区中对数据进行排序

总结

根据排序字段建立合适的索引，多字段排序时，要遵循最左前缀法则
尽量使用覆盖索引
多字段排序，升序和降序混合要注意联合索引在创建时的规则（asc/desc）
如果大数据排序不可避免的出现filesort，可以适当增加排序缓冲区大小sort_buffer_size（默认256kb-如果超出缓冲区将到磁盘文件进行排序）

group by优化

没有索引的情况下，出现 Using temporary

创建索引的情况下，出现Using index

不符合最左前缀法则，出现了Using temporary

总结

在分组操作时，可以通过索引提高效率
分组操作时，索引的使用满足最左前缀法则

limit优化

在数据量较大时，使用limit进行分页操作，偏移量越大，分页查询的效率越低。

因为在执行limit分页查询时，MySQL需要排序页号前面的数据，返回limit指定区间的记录，丢弃前面全部的记录，使得查询排序的代价较大。

优化

在分页查询时，通过覆盖索引后子查询的方式进行优化

1	SELECT * FROM pms_product a, (SELECT id FROM pms_product order by id limit 1100000, 2) b WHERE a.id = b.id;

count优化

MyISAM引擎把一个表的总行数存储在磁盘上，因此执行count(*)时直接返回这个数，效率比较高，但这并不支持条件搜索。

InnoDB引擎执行count(*)时需将数据一行一行读出来再累积计数。

InnoDB优化

将表的总行数存储在内存数据库中

count用法

count()是一个聚合函数，对于返回结果集一行一行的判断，如果count()函数的参数不是null，累计值加1，否则不加，最后返回累计值。

count用法	含义
count(主键)	InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加。
count(字段)	没有not null约束，InnoDB引擎会遍历整张表把每一行的字段都取出来，返回给服务层，服务层判断是否围为null，不为null，计数累加。有not null约束，InnoDB引擎会遍历整张表把每一行的字段值都取出来，给服务层直接按行进行累加。
count(1)	InnoDB引擎遍历整张表，不取值，服务层对于返回的每一行，直接按行进行累加。
count(*)	InnoDB引擎并不会把全部字段取出来，而是做了专门的优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键id) < count(1) ~ count()，尽量使用count()

update优化

当开启多个事务执行根据id修改一条数据时，事务提交后，行锁释放。

当我们开启多个事务，执行没有走索引的修改语句，行锁升级了表锁，导致update语句的性能大大降低。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。一旦行锁升级为表锁，update语句的性能将大大降低。

尽量根据主键/索引字段进行数据更新。

总结

锁

概述

定义

锁是计算机协调多个线程或线程并发访问某一个资源的机制。除了传统计算资源（cpu、ram、I/O）的争用之外，数据同样是一种多个用户共享的资源。锁是保证数据并发访问的一致性和有效性的一种方式，但锁冲突也是影响数据库并发访问性能的一个重要因素。

全局锁

全局锁是对整个数据库实例加锁，加锁之后整个实例处于只读状态，只能允许读，不允许做任何写操作，后续的DML和DDL语句以及未提交的更新操作事务等都将处于被阻塞的状态。

应用场景

全库的逻辑备份：对于所有的表进行锁定，获取一致性视图，保证数据的完整性。

加锁

1	flush tables with read lock;

备份

1	mysqldump -h 远程服务器ip -uroot -p 表名 > 下载目录/文件名.sql

释放锁

1	unlock tables;

特点

全局锁问题

主库上备份加上全局锁，锁期间不能执行写入或者更新等操作，业务停摆
从库上备份加上全局锁，锁期间从库不能执行主库同步的二进制日志（binlog），导致主从延迟

不加锁的数据备份

在InnoDB引擎中，可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份

1	mysqldump --single-transaction -h 远程服务器ip -uroot -p 表名 > 下载目录/文件名.sql

表级锁

定义

每次操作锁住整张表。锁的粒度大，发生锁冲突的概率最高导致并发度最低。

分类

表锁

表锁分类

表共享读锁（read lock）
表独占写锁（write lock）

语法

# 加锁
lock tables 表名[...] read/write
# 释放锁
unlock tables/客户端断开连接

表共享读锁

不会阻塞读操作，但会阻塞其他客户端的写

表共享写锁

客户端对于表加表共享写锁，就可以读也可以写，但是其他客户端不可以读也不可以写

总结

读锁不会阻塞其他客户端的读，但会阻塞其他客户端以及本客户端的写。写锁既会阻塞其他客户端的读，也会阻塞其他客户端的写

元数据锁(meta data lock, MDL)

Meta Data Lock元数据锁-MDL锁，基于表的元数据加锁。

所有存储引擎的表都会存在一个.frm文件，该文件只要存储表的结构（DDL语句），而DML锁就是基于.frm文件中的元数据加锁。

在MySQL5.5版本引入了MDL锁，MDL加锁过程是系统自动控制，无需手动获取锁

作用

更改表结构时使用，在更改表结构时自动加上DML锁，加锁后，整张表不允许其他事务任何操作。

更改表结构的操作有向一张表创建/删除一个索引、修改一个字段的名称/数据类型、增加/删除一个表字段等情况

查看元数据锁

1	SELECT object_type, object_schema, object_name, lock_type, lock_duration FROM `performance_schema`.metadata_locks;

意向锁

在执行DML添加表锁时，为了避免行锁和表锁的冲突，表锁需要检查每行数据是否加锁，InnoDB引入意向锁，使用意向锁可以减少表锁的检查

由是否有意向锁和意向锁的类型判断表锁是否可以添加成功

意向锁的类型

意向共享锁（IS）：由语句select...lock in share mode添加，与表锁共享锁（read）兼容，与表锁排他锁（write）互斥
意向排他锁（IX）：由语句insert,update,delete,select...for update添加，与表锁共享锁（read）和表锁排他锁（write）都互斥，意向锁之间不会互斥

查看意向锁以及行锁的加锁情况

1	SELECT object_schema, Object_name, index_name, lock_type, lock_mode, lock_data FROM `performance_schema`.data_locks;

意向共享锁实例

意向互斥锁实例

行级锁

行级锁，每次操作锁住对应的行数据。锁的粒度小，发生锁冲突的概率最小，并发度最高，应用在InnoDB存储引擎中。

InnoDB的数据是基于索引来组织的，行锁通过对索引上的索引项加锁来实现，而不是对记录加的锁

行锁的类型

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

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

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

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

行锁的分类

共享锁（S）：多个事务对于同一数据可以共享一把锁访问数据，但是，阻止其他事务获得相同数据的排它锁
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获取相同数据的共享锁和排他锁

行锁

针对唯一索引进行检索时，对已存在的记录等值匹配时，会自动优化为行锁
InnoDB的行锁时针对于索引加的锁,，不通过索引条件检索数据时，那么InnoDB将对表中的所有记录加锁，此时锁就会升级为表锁

InnoDB行锁是通过给索引上索引项加锁来实现的。所以，只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁。

间隙锁/临键锁

针对唯一索引上的等值查询，给不存在的记录加锁时，会优化为间隙锁

针对普通索引上的等值查询，向右遍历到最后一个值不满足查询条件时，next-key-lock退化为间隙锁

针对唯一索引上的范围查询，会访问到不满足条件的第一个值为止

间隙锁的唯一目的时防止其他事务插入间隙造成幻读，间隙锁是非互斥锁的，间隙锁可以共存的，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

总结

概述

在并发访问时，解决数据访问的一致性和有效性问题。

锁按照粒度划分为全局锁、表级锁、行级锁

全局锁

对于整个数据库实例加锁，加锁后整个实例处于只读状态，性能较差，数据逻辑备份时使用。

表级锁

操作锁住整个表，锁的粒度大，发生锁冲突的概念高，常见有表锁、元数据锁（避免DML和DDL的冲突），意向锁（避免添加表锁时，全表扫描检查行锁）

行级锁

操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低，分为行锁、临界锁、间隙锁

InnoDB引擎

逻辑存储结构

表空间（Tablespace）-> 段（Segment）->区（Extent）->页（Page）->行（Row）

表空间（idb文件）：一个MySQL实例可以对应多个表空间，用于存储记录、索引等数据
段：分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment）。由于InnoDB是索引组织表，数据段是B+树的叶子节点，索引段时B+树的非叶子节点。段用来管理多个区（Extent）
区：表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16k，即一个区一共有64个连续的页
页：是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区
行：InnoDB存储引擎数据是按行进行存放的

行中的字段Tri_id是隐藏列，每次对某条记录进行改动时，都会将对应的事务id赋值在Trx_id

行中的字段Roll_pointer相当于一个指针，指向该记录修改前的数据。每次对某条记录进行改动时，都会将修改前的版本写在undo日志中，Roll_pointer就指向修改前的undo日志

架构

MySQL5.5版本开始，默认使用InnoDB存储引擎

InnoDB架构中分为内存结构和磁盘结构。

内存结构

内存结构由缓存池（Buffer Pool），更改缓存区（Change Buffer）、日志缓冲区（Log Buffer）以及自适应哈希索引（Adaptive Hash Index）。

Buffer Pool
缓冲池是主内存中的一个区域，可以缓存磁盘上经常操作的数据，在执行增删改查的操作时，先操作缓冲池中的数据（若缓冲池中没有数据，则从磁盘加载并缓存），然后在以一定频率刷新磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以页（Page）为单位，底层采用链表数据结构管理Page，根据状态，将Page分为三个类型：
- free page：空闲page，未被使用
- clean page：被使用page，数据没有被修改过
- dirty page：脏页，被使用page，数据被修改过，数据与磁盘的数据发生了不一致

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

Change Buffer的作用：与聚集索引不用，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引，插入和删除的效率比较慢。同样，新增和删除还有可能会影响索引树中不相邻的二级索引页。另外，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。

Adaptive Hash index
自适应哈希索引，用于优化对Buffer Pool数据的查询，InnoDB存储引擎会监控对表上各种索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。自定义哈希索引无需人工干预，是系统根据情况自动完成的。

1 2	# 查看自定义哈希索引的开启状态 show variables like '%hash_index%';

Log Buffer

日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log、undo log），默认大小为16MB，日志缓冲区的日志会定定期刷新到磁盘中。当一共事务需要更新、插入或删除多条数据时，日志缓冲区开源节省磁盘I/O。

# 日志缓冲区大小
show variables like '%log_buffer_size%'
# 日志刷新到磁盘时机
show variables like '%flush_log%';
#0:每秒将日志写入并刷新磁盘一次
#1:日志在每次事务提交时写入并刷新到磁盘
#2:日志在每次事务提交后写入,并每秒刷新到磁盘一次

磁盘结构

System Tablespace
系统表空间，更改缓冲区的存储区域。如果表在系统表空间而不是每个表文件或者通用表空间中创建的，它也可能包含索引和数据。在MySQL5.X版本中包含InnoDB数据字典、undolog等文件。
1
show variables like '%data_file_path%'

File-Per-Table Tablespaces
独立表空间，每个表的文件表空间包含单个表InnoDB表的数据和索引以及存储在文件系统上的单个数据文件中。
1
2
# 查看独立表空间是否开启
show variables like '%file_per_table%';

General Tablespaces

通用表空间，需要通过CREATE TABLESPACE语法创建通用表空间，在创建表时，以指定该表空间。

# 创建通用表空间
CREATE TABLESPACE XXX ADD DATAFILE 'file_name.idb' ENGINE = innodb; 
# 指定表空间
CREATE TABLE xxx TABLESPACE XXX;

Undo Tablespaces
撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16MB），用于存储undo log日志。
Temporary Tablespaces
InnoDB使用会话临时表空间和全局临时表空间，存储用户创建的临时表等数据。

Doublewirte Buffer Files
双写缓冲区，innoDB引擎将数据从内存结构中Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。
Redo Log
重做日志，用来实现事务的持久化。该日志文件由两个部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log），前者是内存中，后者在磁盘中，当事务提交之后会把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用。

后台线程

后台线程的作用就是将InnoDB存储引擎缓冲池的数据在合适的时机刷新到磁盘当中。

Master Thread
核心后台线程，负责调度其他线程，将缓冲池中的数据异步刷新到磁盘中来保持数据的一致性以及脏页，合并插入缓存，undo页的回收。
IO Thread
InnoDB存储引擎中大量使用异步AIO来处理I/O请求，极大提高数据库的性能。而IO Thread负责这些I/O请求的回调。
- Read thread：负责读操作
- Write thread：负责写操作
- Log thread：负责将日志缓冲区刷新到磁盘
- Insert buffer thread：负责将写缓存区内容刷新到磁盘
1
show engine innodb status;
Purge Thread
主要用于回收事务已经提交的undo log，在事务提交之后，回收不用的undo log
Page Cleaner Thread
协助Master Thread刷新脏页到磁盘的线程以减轻Master Thread的工作压力，减少阻塞

事务原理

redo log

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

该日志文件由两个部门组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。

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

当客户端发起事务操作，当缓冲区没有需要修改的数据，会通过后台线程将数据从磁盘中读取出来并写入到缓冲区中，事务执行直接操作缓冲区中的数据，此时存放缓冲区的数据变成了脏页，将会以一定的频率调度后台线程将数据刷新到磁盘中，当刷新脏页数据到磁盘的过程中发生错误时，会导致事务的持久性不能被保证。而添加redo log后，将每次缓冲区数据的操作写到redo log buffer（重做日志缓冲）以记录数据页的变化，同时将redo log buffer（重做日志缓冲）以追加的方式同步到redo log file（重做日志文件）中，当刷新脏页到磁盘发生错误时，进行数据恢复。

undo log

回滚日志，用于记录数据被修改前的信息，作用包含两个：回滚和MVCC(即多版本并发控制)

undo log和redo log记录物理日志不一样，undo log是逻辑日志，这意味着当delete一条记录时，undo log中会记录一条对应的insert记录，当update一条记录时，它记录一条对应相反的update记录。当执行rollback操作时，可以从undo log中的逻辑记录读取到响应的内容并进行回滚。

undo log销毁：undo log在事务执行时产生，当事务提交时，undo log日志不会被立即删除，因为这些日志可能还用于MVCC
undo log存储：undo log采用Segment段的方式进行管理和记录，存放于rollback segment回滚段中，rollback内部包含1024个undo log segment

MVCC

基本概念

MVCC

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制，是一种并发控制的方法，实现对数据库的并发访问和事务内存。

通过维护数据的多个版本，更好的方式处理读写冲突，使得不加锁，也可以非阻塞并发读。比如快照读就是MySQL实现MVCC提供一共非阻塞读的功能。

MVCC的具体实现需要依赖于数据库记录中的三个隐式字段、undo log日志以及readView。

当前读

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

使用当前读的操作有：select…lock in share mode（共享锁），select…for update、update、insert、delete（排他锁）

由上图可见：当事务2执行修改操作并提交事务后，事务1读取的还是之前的数据，这是由于事务的默认隔离级别是可重复读，当使用 SELECT...LOCK IN SHARE MODE，SELECT...FOR UPDATE

快照读

快照读：读取记录数据的可见版本，即不加锁的非阻塞读。快照读可能读到的不一定是数据的最新版本，有可能是之前的历史版本。

上面演示的事务1读取的还是历史版本的数据就是基于快照读

简单的select在不同的事务管理级别下的类型

Read Committed：每次select都生成一个快照读

Repeatable Read：开启事务后，第一个select是快照读的地方

Serializable：快照读会退化为当前读，每次读取操作都会加锁

MVCC

MVCC 全称 Multi-Version Concurrency Controller 多版本并发控制，是指维护一个数据的多个版本，使得读写操作没有冲突，快照读为 MySQL 实现 MVCC 提供一个非阻塞读功能。

MVCC具体实现，依赖于数据库记录中的三个隐式字段、undo log日志，readView

MVCC就是为了实现读-写冲突不加锁，这个读的指的是快照读，而非当前读。当前读实际上是一种加上悲观锁的操作。

实现原理

记录中的隐藏字段

DB_TRX_ID（事务ID-6bytes）：最近修改事务ID，记录创建这条记录/最后一次修改改记录的事务ID
DB_ROLL_PTR（回滚指针-7bytes）：指向这条记录的上一个版本，用于配合undo log指向上一个版本
DB_ROW_ID（隐式主键-6bytes）：隐含的自增ID，如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

Ibd2sdi

查看 ibd 文件的一些数据字典信息

1	ibd2sdi ums_member.ibd

Undo log

回滚日志，为了回滚而记录的日志，insert、update、delete 产生便于数据回滚的日志（查询操作并不会修改任何用户记录，执行查询操作时，不需要记录相应的undo log）

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

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

Insert undo log：插入一条记录时，要将这条记录的主键值记录下来，回滚时只需要将这个主键值对应的记录删除
Update undo log：修改一条记录时，至少要把修改这条记录前的旧值记录下来，回滚时再把这条记录更新为旧值就好
Delete undo log：删除一条记录时，至少要把这条记录中的内容记录下来，回滚时再把由这些内容组成的记录插入表中即可

undo版本链

在并发访问的情况下，不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧纪录

Read View（读视图）

Read View 就是事务进行快照读操作时生产的读视图，是快照读执行时MVCC提取数据的依据

在事务执行的快照读时，会生成数据库系统当前的一个快照，记录并维护系统当前活跃的事务（未提交的）id

当每个事务开启时，会被分配一个ID，这个ID是递增的，所以最新的事务，ID值越大

字段	含义
m_ids	当前活跃事务的ID集合
min_trx_id	最小活跃事务ID
max_trx_id	预分配事务ID，当前最大事务ID+1（因为事务是依次递增）
creator_trx_id	Read View 创建者的事务ID

版本链数据访问规则

trx_id：代表当前事务ID

trx_id = creator_trx_id：可以访问该版本-说明数据是由当前事务更改的
trx_id < min_trx_id：可以访问该版本-说明数据已经提交
trx_id > max_trx_id：不可以访问该版本-说明事务在ReadView生成后才开启
min_trx_id <= trx_id <= max_trx_id：如果trx_id不在m_ids中是可以访问该版本的，说明数据已经提交

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

READ COMMITTED：事务中每一次执行快照读时生成Read View
REPEATABLE READ：在事务中第一次执行快照读时生成Read View，后续复用该Read View

RC

RC（Read Committed-可重复读）隔离级别下，在事务中每一次执行快照读时生成的ReadView

RR

RR（Repeatable Read）隔离级别下，仅在事务中第一次执行快照读生成的 ReadVIew，后续复用该 ReadView

原理分析

总结

逻辑存储结构：表空间、段、区、页、行
架构：内存结构、磁盘结构
事务原理：
- 原子性-undo log
- 持久性-redo log
- 一致性-undo log 和 redo log
- 隔离性-锁 + MVCC
MVCC：记录隐藏字段、undo log 版本链、readView

MySQL管理

系统数据库

MySQL数据库安装完成后，自带四个数据库：mysql、information_schema，performance_schema，sys。

数据库	含义
mysql	存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等）
information_schema	提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型以及访问权限等
performation_schema	为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务性能参数
sys	包含了一系列方便DBA和开发人员利用performation_schema性能数据库进行性能调优和诊断的视图

常用工具

mysql

该mysql是指客户端工具，不是指mysql服务

语法：

1	mysql [options] [database]

选项：

-u, --user = name # 指定用户名
-p, --password[=name] # 指定密码
-h, --host=name # 指定服务器ip或域名
-P, --port=port # 指定连接端口
-e, --execute=name # 执行SQL语句并退出

1	mysql -u root -p 123456 demo -e"select * from sys_file"

mysqladmin

mysqladmin是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

语法：

1	mysqladmin [OPTIONS] command command....

选项：

# 查看mysql版本
mysqladmin -uroot -p123456 version
# 查看mysql变量
mysqladmin -uroot -p123456 variables
# 创建mysql数据库
mysqladmin -uroot -p123456 create demo
# 删除mysql数据库
mysqladmin -uroot -p123456 drop demo

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，使用mysqlbinlog日志管理工具检查这些文本的文本格式。

语法：

1	mysqlbinlog [options] log-files1 log-files2...

选项：

-d, --database=name # 指定数据库名称，列出指定的数据库相关操作
-o, --offset=n # 忽略日志中的前n行命令
-r, --result-file=name # 将输出的文本格式日志输出到指定文件
-s, --short-form # 显示简单格式，省略一些信息
--start-datatime=date1 --stop-datetime=date1 # 指定日期间隔内的所有日志
--start-position=pos1 --stop-position=pos2 # 指定位置间隔内的所有日志

mysqlshow

mysqlshow客户端对象查找工具，用来很快的查找存在哪些数据库、数据库中的表、表中的列或者索引。

语法：

1	mysqlshow [options] [db_name[table_name[col_name]]]

选项：

1 2	-- count # 显示数据库及表的统计信息（数据库、表均可以不执行） -i # 显示指定数据库或者指定表的状态信息

mysqldump

mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表以及插入数据的SQL语句。

语法：

1
2
3

mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A

连接选项：

-u, --user=name # 指定用户名
-p, --password[=name] # 指定密码
-h, --host=name # 指定服务器ip或域名
-P, --port=# #指定连接端口

输出选项：

--add-drop-database # 在每个数据库创建语句前加上drop database语句
--add-drop-table # 在每个表创建语句加上drop table语句，默认开启：不开启(--skip-add-drop-table)
-n, --no-create-db # 不包含数据库的创建语句
-t, --no-create-info # 不包含数据表的创建语句
-d, --no-data # 不包含数据
-T, --tab=name # 自动生成两个文件：一个sql文件；创建表结构的语句：一个.txt文件，数据文件

1	mysqldump -uroot -p demo > demo.sql

mysqlimport/source

mysqlimport/source是客户端数据导入工具，mysqlimport用来导入mysqldump加-T参数后导出的文本文件，source用来导入sql文件。

语法：

1 2	mysqlimport [options] db_name textfile1 [testfile2...] source /xxx/xxx.sql

MySQL进阶篇

事务

事务简介

事务操作

关闭事务的自动提交

查看/设置事务提交方式

提交事务

回滚事务

显示开启事务

开启事务

提交事务

回滚事务

事务四大特征（ACID）

并发事务问题

脏读（Dirty read）

丢弃修改（Lost to modify）

不可重复读（Unrepeatable read）

幻读（Phantom read）

不可重复读和幻读的区别

事务隔离级别

查看事务隔离级别

设置事务隔离级别

Read uncommitted

Read committed

Repeatable read

SerialIzable

总结

总结

存储引擎

MySQL体系结构

连接层

服务层

引擎层

存储层

存储引擎简介

查询表的存储引擎

创建表时指定存储引擎

查看当前数据库支持的存储引擎

存储引擎特点

InnoDB

介绍

特点

文件

MyISAM

介绍

特点

文件

Memory

介绍

特点

文件

总结

存储引擎选择

总结

索引

索引概述

介绍

对比

索引优缺点

优点

缺点

索引结构

二叉树

二叉树缺点

红黑树

红黑树缺点

B-Tree

n阶B树特点

B+Tree

Hash

特点

搜索引擎支持

InnoDB存储引擎选择使用B+Tree索引结构

索引分类

索引的具体类型

索引的存储形式

聚集索引选择规则

聚集索引和二级索引的结构实例

思考

执行效率对比