《MySQL是怎样运行的》——读书笔记

《MySQL是怎样运行的》小孩子4919

MySQL B+树

1、数据页

数据页之间双向链接
数据页内record单向链表
数据页内record分为多个组,每个组的最大记录组成数据槽
数据槽采用数组方式在页内存储

2、索引

索引记录为页的最小主键key+数据页号pageNo
多个索引记录组成记录页面,成为目录页,对目录页继续创建索引记录
record_type区分record类型(数据or目录项)
数据越多,索引树越深,一般不会超过4层(一般4层可以存储上百亿数据),查询性能会降低.
B+树一个节点有多个子节点,可以有效的所有树的深度.
一个B+树索引的根节点在数据字典中存储.

3、聚簇索引

聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引
聚簇索引默认根据主键ID创建,没有主键则默认创建一个.
B+树的叶子节点存储的是完整的用户记录.

4、二级索引

页内的记录是按照非主键列的大小组织索引.
B+树的叶子节点存储的并不是完整的用户记录，而只是索引字段列+主键这两个列的值.
二级索引查询完整数据需要回表到主键索引进行查询.
联合索引是多个列建立索引,大小按照联合索引字段从左到右依次排序.

5、索引的代价

空间上的代价:每建立一个索引都要为它建立一棵B+树
时间上的代价:每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引

6、B+树索引适用的条件

组合索引要最左匹配,where语句的字段条件顺序并不会影响是否使用到索引,MySQL有一个查询优化器会分析这些搜索多个条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件，后使用哪个搜索条件.
匹配列前缀可以走索引,如SELECT * FROM person_info WHERE name LIKE ‘As%’;
只给出后缀或者中间的某个字符串不能走索引,如SELECT * FROM person_info WHERE name LIKE ‘%As%’;
匹配范围值可以走索引,如SELECT * FROM person_info WHERE name > ‘Asa’ AND name < ‘Barlow’;
精确匹配某一列并范围匹配另外一列,如SELECT * FROM person_info WHERE name = ‘Ashburn’ AND birthday > ‘1980-01-01’ AND birthday < ‘2000-12-31’ AND phone_number > ‘15100000000’;

7、排序

ORDER BY子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤,如SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;
颠倒顺序就不能使用索引,前缀可以匹配的部分可以走索引.
当联合索引左边列的值为常量，也可以使用后边的列进行排序.
使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序.
排序列包含非同一个索引的列,这种情况不能使用索引进行排序
排序列使用了复杂的表达式,无法使用索引进行排序.

8、回表

需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引
为了彻底告别回表操作带来的性能损耗，建议最好在查询列表里只包含索引列

9、如何挑选索引

只为用于搜索、排序或分组的列创建索引
考虑列的基数,最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好
索引列的类型尽量小
索引字符串值的前缀

10、经验

让索引列在比较表达式中单独出现
主键插入顺序
冗余和重复索引