1. 字符集和比较规则
MySQL有4个级别的字符集和比较规则，分别是：
- 服务器级别
- 数据库级别
- 表级别
- 列级别
2. InnoDB记录存储结构
InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
行格式即一行数据如何在磁盘或者内存中存储，InnoDB存储引擎的行格式有四种：
- Compact
- Redundant
- Dynamic MySQL5.7默认
- Compressed 带有压缩功能
Compact行格式
image
- 变长字段长度列表：变长字段的实际长度列表
- NULL值列表：可为NULL的列是否为NULL列表
- 记录头信息：固定的5个字节长度
-
记录的真实数据：即每一行的真实数据，此外MySQL会为每个记录默认的添加一些列（也称为隐藏列）：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。InnoDB表对主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。所以我们从上表中可以看出：InnoDB存储引擎会为每条记录都添加 transaction_id 和 roll_pointer 这两个列，但是 row_id 是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）。：
image
VARCHAR(M)最多能存储的数据
VARCHAR(M)类型的列最多可以占用65535个字节，这个65535个字节除了列本身的数据之外，还包括一些其他的数据（storage overhead），比如说我们为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：
- 真实数据
- 真实数据占用字节的长度
- NULL值标识，如果该列有NOT NULL属性则可以没有这部分存储空间
对于单字节字符集：
- 如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为真实数据的长度可能占用2个字节，NULL值标识需要占用1个字节。
- 如果VARCHAR类型的列有NOT NULL属性，那最多只能存储65533个字节的数据，因为真实数据的长度可能占用2个字节，不需要NULL值标识。
对于其他字符集：
能够存储的最大长度取决于该字符集表示一个字符最多需要的字节数。在列的值允许为NULL的情况下，gbk字符集表示一个字符最多需要2个字节，那在该字符集下，M的最大取值就是32766（也就是：65532/2），也就是说最多能存储32766个字符；utf8字符集表示一个字符最多需要3个字节，那在该字符集下，M的最大取值就是21844，就是说最多能存储21844（也就是：65532/3）个字符。
上述所言在列的值允许为NULL的情况下，gbk字符集下M的最大取值就是32766，utf8字符集下M的最大取值就是21844，这都是在表中只有一个字段的情况下说的，一定要记住一个行中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节！
行溢出
我们的记录都会被分配到某个页中存储。而一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65532个字节，这样就可能造成一个页存放不了一条记录的尴尬情况。
在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址。
Dynamic和Compressed行格式
我现在使用的MySQL版本是5.7，它的默认行格式就是Dynamic，这俩行格式和Compact行格式挺像，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。Compressed行格式和Dynamic不同的一点是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。
3. InnoDB数据页结构
数据页根据类型不同可以分为很多种，下面说的数据页是指索引页(FIL_PAGE_INDEX)，数据页的一种。
image
一个数据页包含两个伪记录（虚拟记录）：最小记录、最大记录，这两个记录存放在数据页的Infimum + Supremum部分。
数据页中的记录通过记录头中的next_record形成一个升序(通过比较主键的大小)的链表，然后这些记录会被分成多个组，每个组4-8条记录。组内最大记录的偏移量对应一个槽位，这些槽位按升序排序便组成了页目录(Page Directory)。
当在数据页中查找一条记录时，首先在页目录中使用二分查找快速定位记录应该所在当组，然后在组内通过遍历链表即可。
当插入一条记录时，利用页目录进行二分查找就可以快速查找到记录应在的组，如果组内记录数量已经达到8个，则分裂成两个组，并新增一个槽位。
image
因为数据页中的记录是按照升序排序并且存放无间隔，所以在做插入的时候性能比较低，所以主键最好要趋势递增。
数据页的File Header部分存储了上一个页的页号(FIL_PAGE_PREV)和下一个页的页号(FIL_PAGE_NEXT)，所以多个数据页组成了一个双向链表。
4. B+树
表里的数据按照某一列的值升序排序分散在由多个首尾相连的数据页组成的双向链表中，如下图所示：
image
如果只是这样的一种结构存储数据的话，当查找数据的时候虽然页内可以使用二分查找算法进行查找，但是页之间还是需要遍历，其总体的查找效率并没有提升。解决思路是再添加一个数据页对页进行排序，页中只存储升序的列值(主键)和页编码，如下所示：
image
新添加的页和其他页不同之处有：
- 里面存储的行记录不再是完整的用户数据，而是存储了某一数据页中最小的行记录的某一列的值（主键）以及这个数据页的编号。我们称这样的行记录为目录项，一个目录项对应一个数据页。
- 目录项头部分的record_type字段值为1，而普通行记录的值为0，record_type有四个取值：
- 0：普通的用户记录
- 1：目录项记录
- 2：最小记录
- 3：最大记录
存储目录项的数据页和存储普通记录的数据页大小都为16KB，如果数据页很多，那么目录项也会很多，一个数据页就有可能存储不了这么多的目录项，此时就可以再添加一个存储目录项的数据页，如下图所示：
image
数据越来越多，就会添加更多更高层的用于存储目录项的数据页，就像下面一样：
image
最终会像下面这样：
image
上头是树根，下头是树叶！其实这是一种组织数据的形式，或者说是一种数据结构，它的名称是B+树，这也是MySQL索引的存储结构。
不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到B+树这个数据结构中了，所以我们也称这些数据页为节点。从图中可以看出来，我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。
联合索引
一个表只能有一个主键，但是主键可以由多个列组成，MySQL会为主键生成索引，这个索引成为一级索引。除了主键索引外，还可以在表的其他列创建索引，这些索引成为二级索引。
一个索引可以有多个列，这样的索引称为联合索引，那么它和单列索引有什么不同呢？
- 多列索引的目录项中存储列多个列值，它们按照升序排列在数据页中，如果前一个列值相同，则下一个列值页按照升序排序。
- 其叶子节点不是完整的用户记录，而是多个列值+主键。
所以当根据二级索引查找数据时其查到的并不是完整的用户记录，而是主键，然后再根据主键查询完整的用户记录，这个过程成为回表。
聚簇索引
叶子节点存储完整的用户记录的索引成为聚簇索引。
MyISAM中的索引
我们知道InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储。
MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录！
这一点和InnoDB是完全不相同的，在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引！
6. 正确使用索引的姿势
联合索引从左原则，where语句中左边列没有或者不确定(范围查找)时后面字段均不会查询索引。
索引列选择：
- 只为用于搜索、排序或分组的列创建索引
- 考虑列的基数：即区分度大的列有限索引
- 索引列的类型尽量小
- 索引字符串值的前缀：即只索引一个字符串的前几个字符
查询优化：
- 只查询索引列
例如：有A、B、C联合索引，如果场景允许，查询的时候只查询A、B、C三列：select A、B、C from tbl where A = 'a'，这样可以减少回表。 - 让索引列在比较表达式中单独出现，例如使用WHERE my_col < 4/2代替WHERE my_col * 2 < 4
7. 访问
const
通过主键(primary key)或者唯一二级索引(unique key)列来定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的。
这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效。
其有两个特点：
- 最多只能匹配1条记录。
- 只需查找一次聚簇索引或者一次二级索引加一次回表即可定位数据。
ref
由于普通二级索引并不限制索引列值的唯一性，所以在使用等值查找的时候可能找到多条对应的记录，此时需要回表多次，这种访问称为ref访问。
有两种特殊的ref访问：
-
二级索引列值为NULL的情况
不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法。 - 联合索引，最左列等值查找
其特点为：一次二级索引，多次聚簇索引
ref_or_null
二级索引等值查找或is null查找，例如：
SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;
这种需要两次二级索引，多次回表(聚簇索引)称之为ref_or_null访问。
range
二级索引范围查找或者in查找，这种查找需要多次二级索引，多次回表，称之为range访问。
index
看下边这个查询：
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';
其中key_part1, key_part2, key_part3组成了一个联合索引，这种查找因为取值为索引列，所以会全扫描二级索引，这种称之为
index访问。
all
全表扫描。
8. 表连接
连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。
image
例如：
select * from t1, t2;
这种最简单的组合也称为笛卡尔乘积。
驱动表：第一个过滤的表。与之对应的是被驱动表。驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数
有如下表数据：
mysql> select * from student;
+----------+-----------+--------------+
| number | name | major |
+----------+-----------+--------------+
| 20180101 | 张三 | 软件工程 |
| 20180102 | 李四 | 软件工程 |
| 20180103 | 王五 | 软件工程 |
| 20180104 | 赵六 | 土木工程 |
| 20180105 | 马达 | 软件工程 |
| 20180106 | 王美丽 | 美术 |
+----------+-----------+--------------+
6 rows in set (0.00 sec)
mysql> select * from score;
+----------+-----------------------+-------+
| number | subject | score |
+----------+-----------------------+-------+
| 20180101 | C语言设计 | 84 |
| 20180101 | 数据结构与算法 | 73 |
| 20180101 | 计算机组成原理 | 75 |
| 20180102 | C语言设计 | 80 |
| 20180102 | 数据结构与算法 | 89 |
| 20180102 | 计算机组成原理 | 75 |
| 20180103 | C语言设计 | 80 |
| 20180103 | 数据结构与算法 | 45 |
| 20180103 | 计算机组成原理 | 67 |
| 20180104 | C语言设计 | 79 |
| 20180104 | 数据结构与算法 | 90 |
| 20180104 | 计算机组成原理 | 30 |
+----------+-----------------------+-------+
12 rows in set (0.00 sec)
内连接
驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集。
例如：
select st.number, st.name, sc.subject, sc.score from student st, score sc where st.number = sc.number;
student表中马达和王美丽就没在结果集中。
外连接
驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。
-
左外连接
选取左侧的表为驱动表。 -
右外连接
选取右侧的表为驱动表。
之所以需要区分内连接和外连接，是因为连接的过程中有三种情况：匹配上
、匹配不上、不存在。也就是说内外连接主要是用于解决一个表的记录在另外一个表中没有匹配记录时是否在结果集中显示的问题。
ON子句中的过滤条件
对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。
需要注意的是，这个ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，所以如果把ON子句放到内连接中，MySQL会把它和WHERE子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。
例如，因为是内连接，所以下边两行是等价的：
select st.number, st.name, sc.subject, sc.score from student st inner join score sc where st.number = sc.number;
select st.number, st.name, sc.subject, sc.score from student st inner join score sc on st.number = sc.number;
但是下边两行，第一行会报语法错误，第二行则是正常的：
select st.number, st.name, sc.subject, sc.score from student st left join score sc where st.number = sc.number;
select st.number, st.name, sc.subject, sc.score from student st left join score sc on st.number = sc.number;
也就是说，在使用外连接(left join和right join)时，必须使用on进行连接，而不能使用where。
另外外连接时对于被驱动表，on语句中的过滤条件会在连接之前进行过滤，而where语句中的过滤条件则在连接之后进行过滤，例如如下两条sql，前者结果集中会包含student的记录在score表的中不存在的记录，而后者就没有：
select st.number, st.name, sc.subject, sc.score from student st left join score sc on st.number = sc.number and sc.score >= 60;
select st.number, st.name, sc.subject, sc.score from student st left join score sc on st.number = sc.number where sc.score >= 60;
内连接就没有这种区别，例如如下两条sql是等价的：
select st.number, st.name, sc.subject, sc.score from student st inner join score sc on st.number = sc.number and sc.score >= 60;
select st.number, st.name, sc.subject, sc.score from student st inner join score sc on st.number = sc.number where sc.score >= 60;
9. Explain
一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。
explain命令结果包含的含义解释：
image
其中核心信息有：
- table：表名。
- type：索引类型，InnoDB引擎常见的有：const、ref、ref_or_null、range、index、all等。
- possible_keys：可能使用到的索引。
- key：实际使用到的索引。
网友评论