Mysql
一、 数据库范式
- 第一范式(原子性)：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；
- 第二范式(完全依赖)：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性，有个主键
- 第三范式(没有传递依赖)：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。
二、 存储过程
一些 SQL 语句的集合，中间加了点逻辑控制语句。
存储过程比单纯 SQL 语句执行要快，因为存储过程是预编译过的。
存储过程在互联网公司应用不多，因为存储过程难以调试和扩展，而且没有移植性，还会消耗数据库资源。
特性
1.有输入输出参数,可以声明变量,有if/else/case/while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能
2.函数的普通特性:模块化,封装,代码复用
3.速度快,只有首次执行需要经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤
-- 创建测试数据表
DROP TABLE IF EXISTS test1;
CREATE TABLE `test1` (
`id` int(11) NOT NULL,
`num1` int(11) NOT NULL DEFAULT '0',
`num2` varchar(11) NOT NULL DEFAULT '',
`type1` int(4) NOT NULL DEFAULT '0',
`type2` int(4) NOT NULL DEFAULT '0',
`str1` varchar(100) NOT NULL DEFAULT '',
`str2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `num1` (`num1`),
KEY `num2` (`num2`),
KEY `type1` (`type1`),
KEY `str1` (`str1`),
KEY `str2` (`str2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 创建存储过程
DROP PROCEDURE IF EXISTS pre_test1;
DELIMITER
CREATE PROCEDURE `pre_test1`()
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
WHILE i < 10000000 DO
SET i = i + 1;
SET @str1 = SUBSTRING(MD5(RAND()),1,20);
-- 每100条数据str2产生一个null值
IF i % 100 = 0 THEN
SET @str2 = NULL;
ELSE
SET @str2 = @str1;
END IF;
INSERT INTO test1 (`id`, `num1`, `num2`,
`type1`, `type2`, `str1`, `str2`)
VALUES (CONCAT('', i), CONCAT('', i),
CONCAT('', i), i%5, i%5, @str1, @str2);
-- 事务优化，每一万条数据提交一次事务
IF i % 10000 = 0 THEN
COMMIT;
END IF;
END WHILE;
END;
// DELIMITER ;
-- 执行存储过程
CALL pre_test1();
三、 drop、delete 与 truncate
- drop(丢弃数据):
drop table 表名，直接将表都删除掉，在删除表的时候使用。 - truncate (重置，清空数据) :
truncate table 表名，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。 - delete（删除数据） :
delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。
drop 语句，此表的结构也会删除
truncate 和 drop 属于 DDL(Data Definition Language 数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发触发器（ trigger）。而 delete 语句是 DML (数据库操作语言)语句，这个操作会放到 rollback segement 中，事务提交之后才生效。
四、 数据库设计
- 需求分析 : 分析用户的需求，包括数据、功能和性能需求。
- 概念结构设计 : 主要采用 E-R 模型进行设计，画 E-R 图。
- 逻辑结构设计 : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换。
- 物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径。
- 数据库实施 : 包括编程、测试和试运行
- 数据库的运行和维护 : 系统的运行与数据库的日常维护。
五、 UTF-8 和 UTF-8mb4
utf8：utf8编码只支持1-3个字节 。 在utf8编码中，中文是占 3 个字节，其他数字、英文、符号占一个字节。但 emoji 符号占 4 个字节，一些较复杂的文字、繁体字也是 4 个字节。utf8mb4： UTF-8 的完整实现。最多支持使用 4 个字节表示字符，因此，可以用来存储 emoji 符号。
乱码的本质： 编码和解码时用了不同或者不兼容的字符集 。
六、 Mysql基础架构
-
连接器：身份验证和权限相关
-
查询缓存：执行查询语句的时候会先查询缓存（mysql8.0之后移除）
-
分析器：sql语句分析器，检查语法正确
-
优化器：按照mysql认为最优的方式执行
IO 成本: 即从磁盘把数据加载到内存的成本
CPU 成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本
- 执行器：执行语句，从存储引擎返回数据。会判断权限，没有会报错
- 插件式存储引擎：负责数据的存储和读取。
每次在执行 SQL 的时候都会将其数据加载到内存中，这块内存就是 InnoDB 中一个非常重要的组件：
缓冲池 Buffer Pool
SQL 执行步骤：
- innodb 存储引擎会在缓冲池中查找 id=1 的这条数据是否存在
- 发现不存在，那么就会去磁盘中加载，并将其存放在缓冲池中
- 该条记录会被加上一个独占锁（修改)
undo log文件：记录数据被修改前的样子
redo log buffer（先放在内存中去完成）
redo 日志文件：记录数据被修改后的样子
执行器调用存储引擎是怎么将一条 SQL 加载到缓冲池和记录哪些日志的，流程如下：
-
准备更新一条 SQL 语句
-
MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（BufferPool）中
-
在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
-
innodb 会在 Buffer Pool 中执行更新操作
-
更新后的数据会记录在 redo log buffer 中
-
MySQL 提交事务的时候，会将 redo log buffer 中的数据写入到 redo 日志文件中 刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置
- 值为 0 表示不刷入磁盘
- 值为 1 表示立即刷入磁盘
- 值为 2 表示先刷到 os cache
-
myslq 重启的时候会将 redo 日志恢复到缓冲池中
bin log 日志文件：记录整个操作过程（mysql级别日志）
| 性质 | redo Log | bin Log |
|---|---|---|
| 文件大小 | redo log 的大小是固定的（配置中也可以设置，一般默认的就足够了） | bin log 可通过配置参数max_bin log_size设置每个bin log文件的大小（但是一般不建议修改）。 |
| 实现方式 | redo log是InnoDB引擎层实现的 | bin log是 MySQL 层实现的，所有引擎都可以使用 bin log日志 |
| 记录方式 | redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。(顺序读写) | bin log 通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上 |
| 使用场景 | redo log用于崩溃恢复(crash-safe) | bin log 适用于主从复制和数据恢复 |
bin log 的刷盘策略
sync_bin log来修改，默认为 0，表示先写入 os cache，提交事务的时候，数据不会直接到磁盘中
建议将sync_bin log设置为 1 表示直接将数据写入到磁盘文件中。
- STATMENT
基于 SQL 语句的复制。每一条会修改数据的 SQL 语句会记录到 bin log 中
- ROW
基于行的复制。不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了
- MIXED
基于 STATMENT 和 ROW 两种模式的混合复制。先使用 STATEMENT 模式保存 bin log ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式
会将 redo log buffer 中的数据写入到redo log 文件中，同时也会将本次修改的数据记录到 bin log文件中，同时会将本次修改的bin log文件名和修改的内容在bin log中的位置记录到redo log中，最后还会在redo log最后写入 commit 标记，这样就表示本次事务被成功的提交了。
MySQL 有一个后台线程，它会在某个时机将我们Buffer Pool中的脏数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了。
存储引擎是基于表的，而不是数据库。
七、MyISAM 和 InnoDB 的区别
1.支持行级锁
2.支持事务
3.支持外键
4.支持数据库异常崩溃后的安全恢复
5.支持 MVCC
6.索引实现不一样。
7.支持真正的在线热备份
B+Tree 作为索引结构。
具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。
与红黑树的比较，B+ Tree 具有更少的查找次数，利用计算机预读特性。
InnoDB 引擎中，其数据文件本身就是索引文件。（聚簇索引）
MyISAM 支持压缩表和空间数据索引
八、MySQL 查询缓存
任何两个查询条件不同，查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL 库中的系统表，其查询结果也不会被缓存。
缓存建立之后，表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。
升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁
对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十 MB 比较合适。此外，还可以通过 sql_cache 和 sql_no_cache 来控制某个查询语句是否需要缓存
select sql_no_cache count(*) from usr;
九、ACID
- 原子性（
Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；undolog保证 - 一致性（
Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的； - 隔离性（
Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；MVCC保证 - 持久性（
Durabilily）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。redolog保证
🌈 只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的
十、 并发事务问题
- 脏读（Dirty read）: A事务对数据进行修改，却未提交到数据库，B事务使用了这个数据。B事务对数据的操作就是不正确的
- 丢失修改（Lost to modify）: A事务和B事务同时对一个数据进行修改，那么有一个修改就会丢失
- 不可重复读（Unrepeatable read）: A事务中多次读同一数据。B事务在中间有修改操作。A事务读取结果不一致
- 幻读（Phantom read）: A事务读取数据，同时B事务插入数据。A事务就会发现多了一些数据
不可重复读和幻读区别：不可重复读的重点是修改，幻读的重点在于新增或者删除
十一、 SQL隔离级别
- READ-UNCOMMITTED(读取未提交) ： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交) ： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读) ： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。（mysql默认级别）
- SERIALIZABLE(可串行化) ： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ-UNCOMMITTED | √ | √ | √ |
| READ-COMMITTED | × | √ | √ |
| REPEATABLE-READ | × | × | √ |
| SERIALIZABLE | × | × | × |
MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。
MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能
十二、锁分类
InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁
当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有记录进行加锁
不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：
- 共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
- 排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。
# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;
意向锁：快速判断是否可以对某个表使用表锁。
- 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
- 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。
意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。
意向锁之间是互相兼容的。
意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。
InnoDB 有哪几类行锁？
MySQL InnoDB 支持三种行锁定方式：
- 记录锁（Record Lock） ：也被称为记录锁，属于单个行记录上的锁。
- 间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。
- 临键锁（Next-key Lock） ：Record Lock+Gap Lock，锁定一个范围，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。
InnoDB 的默认隔离级别 REPEATABLE-READ（可重读）是可以解决幻读问题发生的，主要有下面两种情况：
- 快照读 ：由 MVCC 机制来保证不出现幻读。
- 当前读 ： 使用 Next-Key Lock 进行加锁来保证不出现幻读。
十三、数据类型
1. 数值类型
a. 整型 ----------
类型 字节 范围（有符号位）
tinyint 1字节 -128 ~ 127 无符号位：0 ~ 255
smallint 2字节 -32768 ~ 32767
mediumint 3字节 -8388608 ~ 8388607
int 4字节
bigint 8字节
int(M) M表示总位数
b. 浮点型 ----------
类型 字节 范围
float(单精度) 4字节
double(双精度) 8字节
浮点型既支持符号位 unsigned 属性，也支持显示宽度 zerofill 属性。
不同于整型，前后均会补填0.
定义浮点型时，需指定总位数和小数位数。
float(M, D) double(M, D)
M表示总位数，D表示小数位数。
c. 定点数 ----------
decimal -- 可变长度
decimal(M, D) M也表示总位数，D表示小数位数。
保存一个精确的数值，不会发生数据的改变，不同于浮点数的四舍五入。
将浮点数转换为字符串来保存，每9位数字保存为4个字节。
2. 字符串类型
-- a. char, varchar ----------
char 定长字符串，速度快，但浪费空间
varchar 变长字符串，速度慢，但节省空间
M表示能存储的最大长度，此长度是字符数，非字节数。
不同的编码，所占用的空间不同。
char,最多255个字符，与编码无关。
varchar,最多65535字符，与编码有关。
b. blob, text ----------
blob 二进制字符串（字节字符串）
tinyblob, blob, mediumblob, longblob
text 非二进制字符串（字符字符串）
tinytext, text, mediumtext, longtext
text 在定义时，不需要定义长度，也不会计算总长度。
text 类型在定义时，不可给default值
-- c. binary, varbinary ----------
类似于char和varchar，用于保存二进制字符串，也就是保存字节字符串而非字符字符串。
char, varchar, text 对应 binary, varbinary, blob.
3. 日期时间类型
一般用整型保存时间戳，因为PHP可以很方便的将时间戳进行格式化。
datetime 8字节 日期及时间 1000-01-01 00:00:00 到 9999-12-31 23:59:59
date 3字节 日期 1000-01-01 到 9999-12-31
timestamp 4字节 时间戳 19700101000000 到 2038-01-19 03:14:07
time 3字节 时间 -838:59:59 到 838:59:59
year 1字节 年份 1901 - 2155
4. 枚举和集合
-- 枚举(enum) ----------
enum(val1, val2, val3...)
在已知的值中进行单选。最大数量为65535.
枚举值在保存时，以2个字节的整型(smallint)保存。每个枚举值，按保存的位置顺序，从1开始逐一递增。
表现为字符串类型，存储却是整型。
NULL值的索引是NULL。
空字符串错误值的索引值是0。
-- 集合（set） ----------
set(val1, val2, val3...)
create table tab ( gender set('男', '女', '无') );
insert into tab values ('男, 女');
最多可以有64个不同的成员。以bigint存储，共8个字节。采取位运算的形式。
当创建表时，SET成员值的尾部空格将自动被删除。
十四、索引
磁盘IO和预读
磁盘读取数据靠的是机械运动，花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分。
- 寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；
- 旋转延迟就是我们经常听说的磁盘转速。一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms
- 传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计
访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右（非常长）
计算机硬件延迟的对比图：（参考）
磁盘IO高昂操作，操作系统优化，当一次IO时把相邻的数据也都读取到内存缓冲区内。（一般为4k或8k）读取一页内的数据时候，实际上才发生了一次IO。
普通索引，主键索引，联合索引，全文索引
索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。
hash索引：查询单条快，范围查询慢（支持精确查找，无法用于部分查找和范围查找）
btree索引：b+树，层数越多，数据量指数级增长(我们就用它，因为innodb默认支持它)，适合排序范围等操作，支持多列联合索引的最左匹配
真实的情况是，3层的b+树可以表示上百万的数据。
IO次数取决于b+数的高度h，
建索引的几大原则
- 最左前缀匹配原则
- =和in可以乱序
- 字段不重复（比例越大我们扫描的记录数越少）
- 索引列不能参与计算，保持列“干净”
- 尽量的扩展索引，不要新建索引
聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。
优点：查询速度非常的快
缺点：依赖于有序的数据，更新代价大
非聚集索引即索引结构和数据分开存放的索引。也依赖于有序的数据
非聚集索引的叶子节点是不存放数据的。可能会二次查询
覆盖索引 ：一个索引包含（或者说覆盖）所有需要查询的字段的值
联合索引：多个字段创建索引
十五、日志
MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。
日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类
二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）较重要
redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。
redo log 日志以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。采用的是环形数组形式
binlog 逻辑日志：记录所有涉及更新数据的逻辑操作，并且是顺序写。
数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。
undo log回滚日志：事务的原子性需要在异常发生时，对已经执行的操作进行回滚。恢复机制是通过 回滚日志（undo log） 实现的，回滚日志会先于数据持久化到磁盘上
主从复制与读写分离
主从复制：涉及三个线程
binlog 线程 : 负责将主服务器上的数据更改写入二进制日志中。
I/O 线程 : 负责从主服务器上读取二进制日志，并写入从服务器的中继日志中。
SQL 线程 : 负责读取中继日志并重放其中的 SQL 语句。
读写分离，数据热备，架构性能扩展，高可用
读写分离
- 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
- 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
- 增加冗余，提高可用性。
代理方式来实现
执行计划Explain
使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。
explain 两个变种
- explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。
- explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。
explain执行计划信息
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
|---|
比较重要的字段有id,type,key,rows,extra
1. id
查询中执行select句的序列号，按出现顺序增长
id值越大，优先级越高，id相同则从上往下执行，id为NULL最后执行
2. select_type:
查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询
- SIMPLE：简单的select查询，查询中不包含子查询或者union
- PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary
- SUBQUERY：在select 或 where列表中包含了子查询 （不在 from 子句中）
- DERIVED：在from列表中包含的子查询，mysql或递归执行这些子查询，把结果放在临时表里
- UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
- UNION RESULT：从union表获取结果的select
3. table
这一列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时，table列是
当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。
4. type
访问类型，决定如何查找表中的行，查找数据行记录的大概范围
结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，好的sql查询至少达到range级别，最好能达到ref
- Null ： mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
- system：表只有一行记录（等于系统表），这是const类型的特例
- const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
-
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
-
简单 select 查询，使用普通索引（非唯一索引）
-
关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分
mysql> explain select film_id from film left join film_actor on film.id = film_actor.film_id;
-
- range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
- index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接 对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这 种通常比ALL快一些（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
- ALL：即全表扫描，扫描聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。
5. possible_keys
查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用
possible_keys列有值，而 key 显示 NULL，通常是表中数据少，mysql选择全表扫描
该列为null,则表示没有相关的索引，可以考虑建索引了
6. key
实际使用的索引，如果为NULL，则没有使用索引。
# 强制mysql使用或忽视possible_keys列中的索引
explain select CustName,count(1) c from WorkOrder ignore index(ix_date)
查询中如果使用了覆盖索引，则该索引仅出现在key列表中
7. key_len
表示索引中使用的字节数，越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的。
通过这个值可以算出具体使用了索引中的哪些列。
key_len计算规则如下：
-
字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字 或字母占1个字节，一个汉字占3个字节
- char(n)：如果存汉字长度就是 3n 字节
- varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为 varchar是变长字符串
-
数值类型
-
tinyint：1字节
-
smallint：2字节
-
int：4字节
-
bigint：8字节
-
-
时间类型
-
date：3字节
-
timestamp：4字节
-
datetime：8字节
-
-
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索 引。
8. ref
显示索引key列表查找值所用到的列或常量【const（常量），字段名（例：film.id）】
9. rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，不是结果集里的行数
10.Extra
十分重要的额外信息:
- Using filesort ：
mysql对数据使用一个外部的索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。一般也是要考虑使用索引来优化
索引是先按email排序、再按address排序，所以查询时如果直接按address排序，索引就不能满足要求了，mysql内部必须再实现一次“文件排序”
- Using temporary
使用临时表保存中间结果，常见于order by 和 group by ,首选用索引优化
- Using index
使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高
如果同时出现Using where，表明索引被用来执行索引键值的查找（参考上图）
如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作
覆盖索引（Covering Index）：select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
-
Using where
使用 where 语句来处理结果，并且查询的列未被索引覆盖
-
Using index condition
查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
-
Using join buffer ：
使用了链接缓存
-
Impossible WHERE：
where子句的值总是false，不能用来获取任何元祖
-
select tables optimized away：
在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化 -
distinct：
优化distinct（去重）操作，在找到第一个匹配的元祖后即停止找同样值得动作
慢sql查询处理
1、开启慢查询日志，准确定位到哪个sq语句出现了问题
2、分析sql语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并
不需要的列，对语句进行分析以及重写
3、分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中
索引
4、如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分
表。
MVCC
数据库并发场景有三种，分别为：
读读：不存在任何问题，也不需要并发控制
读写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读
写写：有线程安全问题，可能存在更新丢失问题
MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个
版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，所以MCC可以为数据库解决一下问
题：
1、在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并
发读写的性能
2、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题
实现原理
mvcc的实现原理主要依赖于记录中三个隐藏行字段，undolog,read views来实现的。
隐藏行字段
DB_TRX_ID
6字节，最近修改事务id,记录创建这条记录或者最后一次修改该记录的事务id
DB_ROLL_PTR
7字节，回滚指针，指向这条记录的上一个版本，用于配合undolog,指向上一个旧版本
DB_ROW_ID
6字节，隐藏的主键，如果数据表没有主键，那么innodb会自动生成一个6字节的row_id
示例:
| DB_TRX_ID | DB_ROW_ID | DB_ROLL_PTR |
|---|---|---|
| 1 | 1 | 0x1110 |
undo log
undolog被称之为回滚日志，表示在进行insert,delete,update操作的时候产生的方便回滚的日志
当进行insert操作的时候，产生的undolog只在事务回滚的时候需要，并且在事务提交之后可以被立刻丢弃
当进行update和delete操作的时候，产生的undolog不仅仅在事务回滚的时候需要，在快照读的时候也需
要，所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被purg线程统一清除（当数据发生更新和删除操作的时候都只是设置一下老记录的deleted_bit,并不是真正的将过时的记录删除，因为为了节省磁盘空间，innodb有专门的purge线程来清除deleted_bit为true的记录，如果某个记录的deleted_id为true,
并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定时可以被清除的）
undolog记录链
从上述的一系列图中，大家可以发现，不同事务或者相同事务的对同一记录的修改，会导致该记录的undolog
生成一条记录版本线性表，即链表，undolog的链首就是最新的旧记录，随尾就是最早的旧记录。
read views
Read View是事务进行快照读操作的时候生产的读视图，在该事务执行快照读的那一刻，会生成一个数据系统.
当前的快照，记录并维护系统当前活跃事务的id,事务的id值是递增的，
其实Read View的最大作用是用来做可见性判断的，也就是说当某个事务在执行快照读的时候，对该记录创建一个Read View的视图，把它当作条件去判断当前事务能够看到哪个版本的数据，有可能读取到的是最新的数据，
也有可能读取的是当前行记录的undolog中某个版本的数据
Read View遵循的可见性算法主要是将要被修改的数据的最新记录中的DB_TRX_ID(当前事务id)取出来，与
系统当前其他活跃事务的id去对t比，如果DB_TRX_ID跟Read View的属性做了比较，不符合可见性，那么就通过
DB_ROLL_PTR回滚指针去取出undolog中的DB_TRX_ID做比较，即遍历链表中的DB_TRX_ID,直到找到满足条件
的DB_TRX_ID,这个DB_TRX_ID所在的旧记录就是当前事务能看到的最新老版本数据。
Read View的可见性规则如下所示：
筒先要知道Read View中的三个全局属性：
trx_list:一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID(1,2,3)
up_limit_id:记录trx_list列表中事务ID最小的ID (1)
low_limit_id: Read Views生成时刻系统尚未分配的下一个事务ID(5)
具体的比较规则如下：
1、首先比较DB_TRX_ID<up_limit_id,如果小于，则当前事务能看到DB_TRX_ID所在的记录，如果大于等于进入下一个判断
2、接下来判断DB_TRX_ID>=low_limit_id,如果大于等于则代表DB_TRX_ID所在的记录在Read View生成后才
出现的，那么对于当前事务肯定不可见，如果小于，则进入下一步判断
3、判断DB_TRX_ID是否在活跃事务中，如果在，则代表在Read View生成时刻，这个事务还是活跃状态，还没有commit,修改的数据，当前事务也是看不到，如果不在，则说明这个事务在Read Views生成之前就已经开始commit,那么修改的结果是能够看见的。
从上述表格中,当事务2对某行数据执行了快照读，数据库为该行数据生成一个Read View视图
当事务2在快照读该行记录的是，会拿着该行记录的DB_TRXD去跟up_limit_id,lower_limit_id和活跃事务列表进行比较，判读事务2能看到该行记录的版本是那个。
具体流程如下：先拿该行记录的事务D(4)去跟Read View中的up_limit_id相比较，判断是小于，通过对比发现不小于，所以不符合条件，继续判断4是否大于等于low_limit_id,通过比较发现也不大于，所以不符合条件，判断事务4是处理tXst列表中，发现不再次列表中，那么符合可见性条件，所以事务4修改后提交的最新结果对事务2的快照是可见的，因此，事务2读取到的最新数据记录是事务4所提交的版本，而事务4提交的版本也是全局角度的最新版本。
在RC隔离级别下，是每个快照读都会生成并获取最新的Read View,而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View,之后的快照读获取的都是同一个Read View
分表分库
水平切分
一个表中的记录拆分到多个结构相同的表中
垂直切分
按照列的关系密集程度进行切分
Sharding 策略
- 哈希取模: hash(key) % NUM_DB
- 范围: 可以是 ID 范围也可以是时间范围
- 映射表: 使用单独的一个数据库来存储映射关系
Sharding 存在的问题及解决方案
事务问题: 分布式事务 XA接口
链接：分解成多个单表查询，然后在用户程序中进行 JOIN
ID 唯一性：
- 使用全局唯一 ID: GUID
- 为每个分片指定一个 ID 范围
- 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)
