MySQL 技术内幕 读书笔记

Mysql 体系结构和存储引擎

数据库实例：是一个程序，位于用户和操作系统之间

数据库：文件，保存数据的文件

Mysql 既是数据库又是数据库实例，是单进程多线程架构（和Oracle在Windows上一样）。

Mysql实例启动后会在默认的位置找配置文件，可以通过mysql --help查看，会看到类似的字串

Default options are read from the following files in the given order:
C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\Program Files\mysql\my.ini C:\Program Files\mysql\my.cnf

如果有同一个参数会以读到的最后一个配置文件中的参数为准。

Mysql支持的存储引擎很多，可以使用show engines查看。

默认使用的InnoDB引擎。

InnoDB支持 事务、全文检索，数据量到100万或1000万以上也不会很慢，只要操作得当。

索引与算法

一般的数据库查找是一行一行的比，索引相当于书的目录，这样会快的多，实际上索引找的过程是二分查找，因为传统意义上的索引用B+树实现的。

索引分为：

• B+树索引
• 全文索引
• 哈希索引（InnoDB存储引擎）

B+树索引是我们传统意义上的索引，B+树的本质是平衡搜索树（平衡树左右子树高度差不超过2），注意不是二叉树，B+树不能找到键值对具体的行，只能找到具体的页，把页放到内存，数据库到内存里找要的数据。

聚集索引

聚集索引只能有一个（至于为什么。。。），一般是主键。数据结构如图：

叶子节点保存的是数据页，页通过双向链表连接。注意，聚集索引存储不是物理上连续的，而是逻辑上连续的，其中有两点：1、页通过双向链表链接，页通过主键的顺序排序；2、每个页的记录也是通过双向链表维护，物理存储上可以同样不按照主键存储。

推荐文章：mysql 创建聚集索引

辅助索引（Secondary Index）

辅助索引又叫非聚集索引，就是我们一般通过KEY或INDEX创建的索引，数据结构如下。

辅助索引键是索引值，值保存的主键聚集索引。所以查询过程是通过索引值找到主键索引，再去聚集索引找到数据页。如果B+树是3层的，那么通过辅助索引查看的话会发生6次IO。

联合索引

联合索引指建立一个索引有多个索引列，聚集索引可以看成只有一个字段的联合索引。当然了，也可以单独创建一个字段的索引。如图，数据结构

从图中可以看到对于联合索引（a,b）是根据a排序的。

所以查询时以 a 为条件就会走索引，以b就不会。

这就有了所谓的最左前缀原则，即字段左边连续，不能断，（a,b,c）可以a，可以ab，可以abc。

当有三列联合索引（a,b,c）时，abc查询条件按顺序就会走索引，同时不能跳，比如下面的会走索引：

select * from t where a=1

select * from t where a=1 and b=1 and c=1

而这个不会走索引

select * from t where a=1 and c=1

覆盖索引

desc 和 explain 功能一样，都能查看命令执行情况。

查询时辅助节点找到主键得到数据页的过程叫做回表。

覆盖索引就是不做回表的过程，效率得到提高。

原理是要查找的值就保存在辅助节点里，而不是数据页里。

如果执行的查询用到的覆盖索引，那么命令执行情况里面的 extra 字段里会有 using index字段，表示使用了覆盖索引。extra 还有其他常见的字段如 filesort表示使用了外部排序，using where表示使用了条件查询，using index表示用到覆盖索引。

例如：

create table t
(
	id int not null,
    age int not null,
    name char(10),
    key idx_age_name (age,name),
    primary key(id)
);

insert into t values (1,1,"xm"),(2,2,'xg'),(3,3,'xh');

-- 没用到覆盖索引
select * from t where age=1;

-- 用到覆盖索引
select age,name from t where age=1;

其核心就是只从辅助索引要数据。那么, 普通索引(单字段)和联合索引,以及唯一索引都能实现覆盖索引的作用。

索引管理

修改索引

ALTER TABLE tbl_name
| ADD (INDEX|KEY) [index_name]
[index_type] (index_col_name,...) [index_option] ...

ALTER TABLE tbl_name
DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name

创建索引

CREATE [UNIQUE] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)

-- 删除索引
DROP INDEX index_name ON tbl_name

我们可以设置对整个列的数据索引，也可以对一列的开头部分数据索引，比如：

-- 对b列前100个数据索引
alter table t add key idx_b (b(100));

查看表的索引信息 show index from tbl_name

• key_name 删除索引的时候用到
• seq_in_index 表示联合索引中的第几个
• collation 列以什么方式存储在索引中，InnoDB默认A，表示排序，如果用Heap引擎就会是NULL
• cardinality 比较关键，表示索引中唯一值的数目的估计值，这个数应该尽可能的接近1，如果非常小，我们可以考虑是否可以删除此索引
• index_type 索引的类型，InnoDB只支持B+数索引，所以这里默认BTREE

Cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引，这个值不是实时更新的，可以通过命令analyze table tbl_name更新改值。

什么情况下添加索引

一般的经验是访问表中很少一部分时使用B+树索引才有意义，对于性别、地区、类型可取的范围非常小，称为低选择性。在高选择性字段加索引是最合适的，比如在nickname字段上，姓名字段重名的很少。

总而言之：每次取很少一部分值的字段加索引比较合适。即高选择性，较少行。这也是为什么说Cardinality的值要尽可能接近1了。

MRR（Multi-Range Read）优化

这个优化为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对范围查询可以代来很大的提升。好处如下：

• 使数据访问变得较为顺序，在查询辅助索引时，首先得到查询结果，按照主键排序，并按照主键排序的顺序进行书签查询。
• 减少缓冲池中页被替换的次数
• 批量处理对键值的查询操作

InnoDB和MyISAM都支持MRR优化，在范围查询和JOIN操作时，其工作方式如下：

• 将查询到的辅助索引键值存到缓存，此时缓存中的数据根据辅助索引键值排序的
• 将缓存中的键值根据RowID（主键）排序
• 根据RowID的排序顺序来访问实际的数据文件

当查询用到MRR优化时，extra 字段会有 using MRR字串。

总是开启MRR优化：

set @@optimizer_switch='mrr=on,mrr_cost_based=off';

ICP（Index Condition Pushdown）优化

和MRR优化一样，是一种根据索引进行查询的优化条件。

在进行索引查询时，先通过索引查询出记录，然后通过where条件过滤。

但使用了ICP后，会在索引查记录的同时判断是否进行where条件的过滤，优化器选择ICP后会在执行计划的Extra下看到 using index condition的提示。ICP优化好像是默认开启的。

书中说MRR和ICP都用时对范围查找效率有很大提升。

全文检索

全文检索针对文本查询，InnoDB引擎默认解析器针对英文，默认的分隔符是空格，如果要针对中文要用到 ngram解析器。

详情见官方文档：https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-ngram.html

ngram推荐中文博客：https://blog.csdn.net/zhang_referee/article/details/83239684

有一种全文检索技术（效率更高），进行模糊查询时：

select * from t where match(column_name) against ('str' in natural language mode);

不适用全文检索：

select * from t where column_name like '%str%';

事务

事务由一条或一组SQL语句组成，要么都执行，要么都不执行，在执行某一条语句时如果出错，会执行回滚操作，撤销前面做的一切操作，保证原子性。

InnoDB中的事务完全符合ACID的特性，即：

• 原子性 atomicity
• 一致性 consistency
• 隔离性 isolation
• 持久性 durability

事务分为：

• 扁平事务

  所有操作都处于同一层次，由BEGIN WORK开始，由COMMIT WORK或 ROLLBACK WORK 结束，其间的操作是原子的，要么都执行，要么都回滚。最简单的一种事务，使用最频繁。

• 带保存点的扁平事务

  假如你1499抢到了一瓶茅台，准备3000卖掉，但抢到后只能2000卖掉，你可能就想留着不卖了，在这个场景下如果都回滚的话，会把抢到的茅台退货，这样代价就大了，所以提供这种带保存点的事务，只回滚到抢到茅台后的地方。

• 链事务

  带保存点的事务能回滚到任意正确的保存点，但链事务只能回滚到最近的一个保存点。COMMIT后释放拥有的所有锁，但带保存点的不影响拥有的锁。

• 嵌套事务

  顾名思义，事务里面套事务。

• 分布式事务

InnoDB不支持嵌套事务。

redo

基本概念：重做日志用

来实现事务的持久性，即事务ACID中的D，由两部分组成，一是内存中的重做日志缓冲（redo log buffer），其是易失性的；二是重做日志文件（redo log file），其是持久的。

当事务执行COMMIT时，必须将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成，这里的日志指重做日志，在InnoDB中由 redo log 和 undo log组成。redo log 保证事务的持久性，undo log帮助事务回滚及MVCC的功能。

每次日志写入重做日志文件后都会执行一次fsync操作（为了确保重做日志写入磁盘），这个操作的效率取决于磁盘的性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。

不过这个操作不是强制的，参数innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略，默认值为1，表示事务提交时必须调用一次fsync操作。当设为0时表示事务提交时不进行写入重写日志操作，设为2时表示重做日志写入重写日志文件，但仅写入文件系统的缓存中，不进行fsync操作，在2下，如果操作系统宕机将会损失一段时间的事务日志。

通过set global innodb_flush_log_at_trx_commit=0;命令设置为1可以提高事务的性能，0和2都是不写入磁盘，但是这样就丧失了事务ACID的特性。

二进制日志

二进制日志用来进行POINT-IN-TIME的恢复和主从复制（环境的）建立。

和redo日志的区别：

二进制日志不只针对InnoDB引擎，其他存储引擎都会产生二进制日志，是一种逻辑日志，保存的是对应的SQL语句，只在事务提交完成后写入。

redo针对InnoDB，是物理格式日志，记录的是对于每个页的修改，在事务进行中不断的写入，写入时是并发的。

purge

delete 和 update操作可能并不直接删除原有的数据。

purge用于最终完成delete和update操作，这样设计是因为InnoDB支持MVCC，所以记录不能在提交时马上处理，这时其他事务可能在引用这行，等没其他事务引用时才真正执行delete操作。

事务控制语句

• start transaction | begin：显式开启一个事务，begin在存储过程中识别为begin…end，在存储过程中只能用start transaction
• commit：提交事务，使得对数据库的修改成为永久性的
• rollback：回滚事务，撤销所有未提交的修改
• savepoint identifier：创建保存点，一个事务可以有多个保存点
• release savepoint identifier：删除一个事务的保存点，当没有这个保存点时会抛异常
• rollback to identifer：与savepoint搭配用，回滚到identifier保存点。
• set transaction：设置事务的隔离级别，隔离级别有 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE，Innodb默认REPEATABLE READ。

在命令行下事务都是自动提交，显式的开启事务不会自动提交，或者SET AUTOCOMMIT=0关闭自动提交。

commit和commit work语句基本一样，可以通过参数completion_type控制，默认0，表示没有任何操作（两者等价）；为1时 commit work 等同 commit and chain，表示自动马上开启一个相同隔离级别的事务；为2时 commit work 等同 commit and release，表示事务提交后自动断开和服务器的连接。

Tips：大部分DDL语句都会有一个隐式的自动提交（在事务中），truncate语句属于DDL语句，虽然功能和delete整张表一样，但是对truncate回滚会失败。

存储过程

create procedure p_load(count int UNSIGNED)
begin
DECLARE s int UNSIGNED DEFAULT 1;
DECLARE c char(80) DEFAULT repeat('a',80);
while s<=count do
insert into t1 select NULL,c;
set s = s+1;
end while;
commit; -- 注意这里
end;

undo

事务回滚时用到undo日志，undo存放在数据库内部的一个特殊段中，这个段叫undo 段，这个段位于共享表空间。

undo是逻辑日志，因此这是将数据库恢复到原来的样子，所有修改都被逻辑地取消了。

因为如果有很多并发事务对一个表访问，可能这个事务处理这两行，另一个事务处理另外几行，如果物理上恢复到之前肯定是会出错的。

例如：如果用户执行了一个 INSERT 10W的请求，那会得到额外的段空间，当回滚时段空间不会收回，而是会执行相反的操作，即 DELETE 10W的命令。每个INSERT，InnoDB会完成一个DELETE，每个DELETE会完成一个INSERT，每个UPDATE会完成一个相反的UPDATE回去。

除了回滚操作，undo还完成MVCC，当用户读取一行记录时，此记录被锁住，当前请求可以通过undo读取到之前的行版本，以此实现非锁定读取。

最后一个重要的点：undo log 会产生redo log，这是因为undo log也需要持久化保护。

清空表：truncate table table_name;

不好的事务习惯

查看是否是自动提交：select @@autocommit';

1 是自动提交，0则不是。可以通过set autocommit=0关闭自动提交。

在事务下（默认参数completion_type=0），mysql会自动执行set autocommit=0，并在commit或rollback后执行set autocommit=1。

把insert语句写进事务里，这样就只会写入一次重做日志，而不是不加事务。

-- 不加事务的操作
create procedure load1(count int unsigned)
BEGIN
DECLARE s int unsigned default 1;
DECLARE c char(80) default repeat('a',80);
while s<=count do
insert into t1 select NULL,c;
set s = s + 1;
commit; -- 这里加不加commit都行，因为insert后会自动提交
END WHILE;
end;

-- 这样做性能会很差，因为会写同样数量的重做日志文件。

-- 好的操作
create procedure load1(count int unsigned)
BEGIN
DECLARE s int unsigned default 1;
DECLARE c char(80) default repeat('a',80);
start transaction;
while s<=count do
insert into t1 select NULL,c;
set s = s + 1;
END WHILE;
commit;
end;

-- 这样做性能比较好，因为不管count是多少只会写一个重做日志文件。

使用自动回滚

创建一个存储过程，当发生错误时自动回滚，正确执行返回1，错误执行返回-1（即回滚了）

create procedure sp_auto_rollback_demo()
begin
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK;SELECT -1;END;
START TRANSACTION
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 2;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 3;
COMMIT;
SELECT 1;
END;

但是当还想知道发生错误的类型时就无从下手了。

在这里原书作者推荐对事务的BEGIN、COMMIT、ROLLBACK都交给程序段完成。

表

主键

mysql在创建表时如果没有显示的指定主键（primary key），那么会自动找第一个（unique not null）的键，如果还没有，会自动创建一个6字节的指针作为主键。

可以通过查询字段后面加上_rowid查看主键，不过查看单个列为主键的情况。

约束

对列的约束有四种：

• primary key 标识主键
• unique key 唯一键(不包括非空)
• foreigh key 外键
• default 默认
• not null 非空

# 创表时加约束
create table u (
	id INT PRIMARY KEY,
    name VARCHAR(20),
    passwd VARCHAR(20),
    UNIQUE KEY(name),
    NOT NULL(passwd)
);

# 创表后加约束
ALTER TABLE u ADD UNIQUE KEY uk_passwd (passwd);

# 添加外键，前提是要创建一个子表
create table child (
	id INT PRIMARY KEY,
    u_id INT,
    FOREIGN KEY(u_id) REFERENCES u(id)
);

约束和索引的区别

约束是一个逻辑上的概念来保证数据的完整性；

索引是一个数据结构，既有逻辑的概念，在数据库中还代表着物理存储的方式。

对错误数据的约束

如果列的约束是非空，你插入数据NULL不会报错，而是警告，mysql会自动转换成0，也就是说mysql本身不对数据做约束。可以通过SET sql_mode='STRICT_TRANS_TABLES'设置严格约束来避免这一情况。（不过好像MySQL8.0默认就是严格模式）

对离散数值的约束

MySQL数据库不支持传统的CHECK约束。

所以离散数值约束可以通过ENUM或SET满足这样的需求。

create table a (
	id INT,
    sex ENUM('male', 'female')
);

对连续数值的约束

这种需求可以通过触发器实现。

触发器的作用是在执行INSERT、DELETE、UPDATE命令之前或之后自动调用的SQL命令或存储过程。

也就是说最多可以为一个表创建6个触发器，分别在上面三个命令的之前和之后。

命令格式：

create
[DEFINER = {user | current_user}]
trigger trigger name before|after insert|delete|update
on tb1_name for each row trigger_stmt

外键约束

一般来说，被引用的表叫父表，引用的表叫子表。外键定义时的ON DELETE和ON UPDATE表示在对父表进行DELETE和UPDATE时对子表所作的动作：

• CASCADE

  级联操作，表示父表做的删除和更新操作同步到子表

• SET NULL

  父表发生删除和更新操作时把子表对应数据更新为NULL

• NO ACTION

  父表发生删除和更新操作时抛出错误，不允许此操作发生

• RESTRICT

  父表发生删除和更新操作时抛出错误，不允许此操作发生

如果定义外键时没有指定ON DELETE或ON UPDATE时默认的就是RESTRICT

在导入数据时，外键检查是及时操作，很慢，可以通过设置忽视外键检查。

mysql > SET foreign_key_checks = 0;
mysql > load data ......
mysql > SET foreign_key_checks = 1;

视图

视图主要用途之一被用做一个抽象装置，同时也起到一个安全层的作用。

创建视图：

create
view view_name | [{column_list}]
as select_statement
[with {cascaded | local} check option]

上面的with check option针对更新视图（即对视图里面限制范围内更新）

物化视图

Oracle支持物化视图，在SQL server中叫索引视图， 该视图不是基于基表的虚表，而是根据基表实际存在的实表，即物化视图的数据存储在非易失的存储设备上。物化视图可以用于预先计算并保存多表的链接（JOIN）或聚集（GROUP BY）等耗时较多的SQL操作结果。这样在执行复杂查询时可以避免耗时的操作。好处是对于一些复杂的统计类查询能快速得到结果。

不过在MySQL中不支持物化视图，但可以通过一些机制来实现物化视图的功能。

例如要创建一个ON DEMAND的物化视图还是很简单的，用户只需定时把数据导入到一张表，例如有如下的订单表，记录了用户采购电脑设备的信息。

create table Orders(
	order_id int unsigned not null auto_increment,
    product_name varchar(30) not null,
    price decimal(8,2) not null,
    amount smallint not null,
    primary key (order_id)
)ENGINE=Innodb;

insert into Orders VALUES
(NULL, 'CPU', 135.5, 1),
(NULL, 'Memory', 48.2, 3),
(NULL, 'CPU', 125.6, 3),
(NULL, 'CPU', 105.3, 4);

# 创建物化视图的基表
create table Orders_MV(
	product_name varchar(30) not null,
    price_sum DECIMAL(8,2) not null,
    amount_sum INT not null,
    price_avg float not null,
    orders_cnt int not null,
    unique index (product_name)
);

insert into Orders_MV
select product_name, SUM(price), SUM(amount), AVG(price), COUNT(*)
from Orders group by product_name;

不过如果基表有更新的话就需要重新对物化视图进行一次插入操作，如果想要在基表插入的时候更新物化视图就可以通过触发器实现这个目的。首先对Orders表创建一个触发器：

create trigger tgr_Orders_insert
after insert on Orders
for each row
begin
	set @old_price_sum = 0;
	set @old_amount_sum = 0;	
	set @old_price_avg = 0;	
	set @old_orders_cnt = 0;	
	
	select IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(orders_cnt, 0)
	from Orders_MV where product_name = NEW.product_name into
	@old_price_sum, @old_amount_sum, @old_price_avg, @old_orders_cnt;
	
	set @new_price_sum = @old_price_sum + NEW.price;
	set @new_amount_sum = @old_amount_sum + NEW.amount;	
	set @new_orders_cnt = @old_orders_cnt + 1;	
	set @new_price_avg = @new_price_sum / @new_orders_cnt;
	
	replace into Orders_MV
	values(NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt);
end

我们进行插入数据，观察物化视图的变化：

insert into orders VALUES (NULL, 'SSD', 299, 3);
insert into orders VALUES (NULL, 'Memory', 47.9, 5);
select * from orders_mv;
# mv这里表示物理视图

分区表

MySQL 报错

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决：

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));