mysql学习手册

mysql学习手册

本文大部分内容来自小林coding https://xiaolincoding.com/

索引

数据结构

索引的目的是为了减少磁盘IO次数，降低查询所消耗的时间

磁盘读写的最小单位是 扇区，扇区的大小只有 512 B 大小，操作系统一次会读写多个扇区，所以操作系统的最小读写单位是块，Linux 中的块大小为 4 KB,也就是一次磁盘IO 操作会直接读写8个扇区

二分查找

每次查找都将范围减半，时间复杂度为 O(logn)

二分查找树

二分查找树利用了二分查找，将每一次二分查找的中间节点作为根节点连起来成为二分查找树

二分查找树的特点为节点的左子树所有节点都小于这个节点，右子树的所有节点都大于这个节点

特殊情况时，当插入的数据每次都是最大的那个元素，那么二分查找树会变成一个只会向右延申的树，没有左子树只有右子树

平衡二叉树（AVL）树

每个节点的左子树和右子树的高度不能超过1

红黑树也是一种自平衡二叉树

B树

B树的每一个节点最大包含M 个子节点，M 称为 B 树的阶，B 树是一个多叉树

假如M = 3，那么每个节点最多有（M-1）个数据和最多M个子节点，当超过这两个要求时就会分裂节点

每个节点都保存了数据和索引，当想要查询某个节点的数据时，途径的节点数据都会被加载到内存中，显然这些内存是无用的，这样不仅增加磁盘IO ,还占用内存资源

B+树

B+树和B树的区别，有以下几点

• 叶子节点才会存放实际数据（索引和记录），非叶子节点只会存放索引
• 所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表
• 非叶子节点的索引存在叶子节点中，并且是在叶子节点中所有索引的最小值或最大值。在链表的首位
• 非叶子节点中有多少个子节点，就有多少索引
• 叶子节点采用双链表连接，适合范围的顺序查找

由于B+树只有叶子节点存放数据，非叶子节点存放索引，所以B+树相比于B树可以存放更多的索引，减少磁盘IO次数

索引类型

聚簇索引（主键索引）

主键索引的 B+ 树的叶子节点存放完整的数据

一张表只有一个主键索引，索引的值不能为空

CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

二级索引

二级索引的B+树的叶子节点存放的是主键值，而不是实际数据。

当查询时使用了二级索引，如果查询到的数据能在二级索引中查到，那么就不需要回表,这个过程是覆盖索引。如果查询到的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，在检索主键索引，就能查询到数据，这个过程就是回表

唯一索引

索引列的值必须唯一，允许有空值，一张表可以有多个唯一索引

CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

普通索引

和唯一索引的区别是不限制值必须唯一，也不要求字段是否为主键。

CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

前缀索引

前缀索引是指对字符串类型的前几个字段建立的索引，而不是整个字段的索引。

前缀索引建立在字段类型为 char 、 varchar 、binary 、 varbinary 的列上

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
); 

单列索引

建立在单列上的索引，如主键索引

联合索引

511498.png&pos_id=img-pgcVqZyz-1707292440222)

CREATE INDEX index_product_no_name ON product(product_no, name);

建立在多列上的索引

当联合索引查询时，先按照 product_no 字段比较，当 product_no 相同时，在按照 name 字段比较

使用联合索引时，存在最左匹配原则，如果不遵循最左匹配原则则会导致索引失效。

联合索引的最左匹配原则，在遇到范围查询(> 或者 < )的时候，就会停止匹配，也就是范围查询的字段也可以用到联合索引，但是在范围查询字段后的字段无法用到联合索引。

对于>= 、<= 、 between 、 like 前缀匹配的范围查询，并不会停止匹配

索引下推

索引下推的目的就是减少回表次数、减少磁盘IO

在执行 select * from table where a > 1 and b = 2 语句的时候，

只有 a 字段能用到索引，b字段不能使用索引

• 在mysql 5.6 之前，只能回表，不进行筛选
• 在 mysql 5.6 版本引入了索引下推优化（index condition pushdown），可以在联合索引遍历过程中，对联合索引中包含的字段先作判断，直接过滤掉不满足条件的记录，减少回表次数

当你的查询语句的执行计划里，出现了 Extra 为 Using index condition，那么说明使用了索引下推的优化。