从根上理解mysql-读书笔记
查看变量
查看默认存储引擎: SHOW VARIABLES LIKE 'default_storage_engine';
查看最大连接数: SHOW VARIABLES LIKE 'max_connections';
设置默认会话存储引擎: SET SESSION default_storage_engine = MyISAM;
设置全局会话引擎: SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';
字符集
utf8mb3：阉割过的utf8字符集，只使用 1 ～ 3 个字节表示字符
utf8mb4：正宗的utf8字符集，使用 1 ～ 4 个字节表示字符
查看所有的字符集: SHOW COLLATION LIKE 'utf8_%';
Innodb存储结构
将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位， InnoDB 中页的大小一般为 16 KB
行格式有4种: Compact、Redundant、Dynamic和Compressed
CREATE TABLE record_format_demo (
-> c1 VARCHAR(10),
-> c2 VARCHAR(10) NOT NULL,
-> c3 CHAR(10),
-> c4 VARCHAR(10)
-> ) CHARSET=ascii ROW_FORMAT=COMPACT;
InnoDB数据页结构
如果指定了主键,在具体的行格式中 InnoDB 就没必要为我们去创建那个所谓的 row_id 隐藏列了
记录按照主键从小到大的顺序形成了一个单链表
插入数据:INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');
示意图:
如果再插入一条:
INSERT INTO index_demo VALUES(4, 4, 'a');
新分配的数据页编号可能并不是连续的，也就是说我们使用的这些页在存储空间里可能并不挨着
不符
合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以在插入主键值为4的记录的时候需要伴随着一次记录移动，也就是把主键值
为5的记录移动到 页 28中，然后再把主键值为4的记录插入到 页 10中
为页做一个目录
根据主键值快速查找某条记录的功能:
- 先从目录项中根据二分法快速确定出主键值为20的记录在 目录项 3中（因为 12 < 20 < 209），它对应的页是 页 9。
- 再根据前边说的在页中查找记录的方式去 页 9中定位具体的记录
复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为 目录项记录
0：普通的用户记录
1：目录项记录
2：最小记录
3：最大记录
聚簇索引: 叶子节点存储完整的记录,例如主键索引
非聚簇索引: 叶子节点只存储索引列信息+主键, 所以,非聚簇索引如果查询索引外的字段一般需要回表操作
B+树的形成
每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个 根节点 页面。最开始表中没有数据的时候，
每个B+树索引对应的 根节点 中既没有用户记录，也没有目录项记录。
随后向表中插入用户记录时，先把用户记录存储到这个根节点 中。
当 根节点 中的可用空间用完时继续插入记录，此时会将 根节点 中的所有记录复制到一个新分配的页，比如 页 a中，然后对这个新页进行 页分裂 的操作，得到另一个新页，比
如 页 b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到 页 a或者 页 b中，而 根节点 便升级为存储目录项记录
的页。
一个 B+ 树索引的根节点自诞生之日起，便不会再移动
可以使用索引的情况
1.全值匹配
2.匹配左边的列,或者多个左边的列
3.匹配列前缀: WHERE name LIKE 'As%';
4.匹配范围值
5.精确匹配某一列并范围匹配另外一列
如果asc desc混用则不能使用索引
查看数据目录
SHOW VARIABLES LIKE 'datadir';
区: 每64个页就是一个区
组: 每256个区就是一个组
区是为了减少磁盘io,一个区就是连续的64个页
一个索引会生成 2 个段，一个叶子节点段，一个非叶子节点段
mysql批量插入测试数据
https://www.jianshu.com/p/c49e631f2ee2
索引类别:
const： 常数级别,代价可以忽略不计,这种const访问方法只能在主键列或者唯一二级索引和常数列对比才有效
ref: 通过一个二级索引,可能找到多条记录,然后回表进行筛选
ref_or_null:包含null条件时SELECT * FROM single_table WHERE key2='36' OR key2 IS NULL
range: SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);
index: 列都在组合索引中 SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part3 = 'abc';
连接
SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';
驱动表只访问一次,而被驱动表要访问很多次
如果有三张表连接,则第二张表的结果会一个个连接第三张表进行查询
当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从
内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从
磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载
被驱动表的代价了。所以设计MySQL的大叔提出了一个join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的
记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成
的，所以这样可以显著减少被驱动表的I/O代价。
查看innnodb的统计数据
SHOW TABLES FROM mysql LIKE 'innodb%';
innodb_table_stats存储了关于表的统计数据，每一条记录对应着一个表的统计数据。
innodb_index_stats存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。
- 查看成本常数: SELECT * FROM mysql.server_cost;
explain详解
EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，该条记录的table 列代表着该表的表名
eg: EXPLAIN SELECT * FROM s1 INNER JOIN s2;
id
每个表都会对应一条记录，但是这些记录的 id 值都是相同的
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.m1=t2.m2
如果是子查询:
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
则id会有变化,并不是一样的
optimizer trace
SHOW VARIABLES LIKE 'optimizer_trace';
开:SET optimizer_trace="enabled=on";
Buffer Pool
SHOW VARIABLES LIKE 'innodb_old_blocks_pct';
old区域在LRU 链表 中所占的比例是37%
redo日志通用结构
系统表空间
默认情况下,会在数据目录下建一个12mb的数据文件,名称为ibdata1,它是自扩展文件
在mysql5.6之后,新建表时不会存在系统表空间,而是新建一个表建立一个独立的表空间
将innodb_file_per_table设置为0 可以将新建表建立在系统表空间
innodb引擎有两个文件
test.frm
test.ibd
myisam有3个文件,多出的一个文件用来存储索引
test.frm
test.MYD
test.MYI
如果是视图表则只有一个frm文件,用来描述表结构
连接查询成本占大头的其实是驱动表扇出数 x 单次访问被驱动表的成本
优化方法:
- 尽量减少驱动表的扇出
- 对被驱动表的访问成本尽量低
这一点对于我们实际书写连接查询语句时十分有用，我们需要尽量在被驱动表的连接列上建立索引，
这样就可以使用ref访问方法来降低访问被驱动表的成本了。
如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。
