MySQL 高级优化

索引（阿里规约）#

索引数据结构#

B+ 树

Hash#

以 Col2 为例，当插入数据时，会对插入的数据进行一个哈希运算，把运算的哈希散列的值作为地址值。

但是 hash 没法很好的处理范围查找，但是 B+ 树在底层时对所有的数据进行了维护，树的每层都是有序的。

B 树#

• 叶子节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列

B+ 树#

多叉平衡树

• 非叶子节点不存储 data，只存储索引（冗余），可以放更多的索引
• 叶子芥蒂娜包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能

查看 mysql 底层给 B+ 树的一个节点设置的大小数为 16KB

SHOW GLOBAL STATUS LIKE 'Innodb_page_size';Copy

以下图为例，三层，索引拿 bigint 来计算一下，bigint 是占 8 个字节，然后一个节点会有很多索引，一个索引会有一个指针，对应 6 个字节，所以一个索引全部大小为（8+6）就是 14 个字节，一个节点可以放 1170 个索引。

第二层同样可以放 1170 个索引，不过第三层每个索引对应不止有 bigint，还有 data 数据，data 数据可能是索引所在行的磁盘文件的地址，也可能是索引所在行的其它列的字段，放不了 1170 个，如果按一个索引 1kb 算，第三层每个节点放 16 个索引。

所以树的高度为 3 的 B + 树就 总共放 1170 X 1170 X 16 = 21902400 个索引

并且 MySQL 索引底层的 B + 树的根节点是常驻内存（RAM）的。

MySQL 的存储引擎是表级别的。

创建两张表，分别用不同的数据库引擎

CREATE TABLE `test_innodb_lock`(
    `a` int(11) NOT NULL,
    `b` varchar(255) DEFAULT NULL,
    KEY `idx_a` (`a`),
    KEY `idx_b` (`b`)
)ENGINE=INNODB DEFAULT charset=utf8

CREATE TABLE     `test_myisam`(
    `id` int(11) NOT NULL auto_increment,
    `name` varchar(20) DEFAULT NULL,
    PRIMARY KEY(`id`)
)ENGINE=myisam auto_increment=2 default charset=utf8Copy

可以看到实际上是在磁盘文件中 mysql 的 data 目录下具体的目录下存储。

SDI 是 Serialized Dictionary Information 的缩写，是 MySQL8.0 重新设计数据词典后引入的新产物。对于非 InnoDB 引擎，MySQL 提供了另外一中可读的文件格式来描述表的元数据信息，在磁盘上以 $tbname.sdi 的命名存储在数据库目录下。

• MyISAM 索引文件和数据文件是分离的（非聚集）

查找过程为，现判断是否有索引，有的话根据 myi 文件找到索引，然后根据 myd 找到所在行的文件地址

• InnoDB 索引实现（聚集）
  • 表数据文件本身就是按 B+ Tree 组织的一个索引结构文件
  • 聚集索引叶子节点包含了完整的数据记录
  • 为什么 InnoDB 表必须有主键，并且推荐使用整型的自增主键？
  • 为什么非空主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

如果 innodb 表没有主键，会自动根据找一列数据，这一列数据会中的每一个数据都是唯一的，会把那一列数据拿出来对表进行维护。
如果连数据唯一的列都没有，innodb 会在最后增加一列隐藏列，它帮你维护表。都是 B+ 树。mysql 资源是非常紧张的，每个表最好一定要建立主键，并且最好是整型自增，不推荐 UUID 这种字符串比较，因为效率会变慢。为什么要自增，不自增 B + 要自平衡，会导致执行 insert 语句等效率。

B+ 树索引的叶子节点两两之间，是有一个双向指针，放相邻节点的磁盘文件地址，可以在任意节点通过指针快速定位相邻节点的位置。

若要查找索引大于 20 的数据，先从根节点，快速定位到索引为 20 的节点，然后直接把索引 20 之后的所有的数据拿出来。

B 树叶子节点之间没有双向指针。B 树是每个节点都会有数据，B+ 树把所有的数据都放到叶子几点上，非叶子节点是冗余索引，叶子有完整的索引。

由于 B 树上的每个节点都会放置数据，所以层放的节点会更少，存放相同量的数据，B 树的层数会更加高。

联合索引#

创建示例表

CREATE TABLE `employees` (
    `id` int(11) not null auto_increment,
    `name` varchar(24) not null default '' COMMENT '姓名',
    `age` int(11) not null default '0' comment '年龄',
    `position` varchar(20) not null default '' comment '职位',
    `hire_time` timestamp not null default current_timestamp comment '入职时间',
    primary key(`id`),
    key `idx_name_age_position` (`name`,`age`,`position`) using btree
)ENGINE = innodb auto_increment=4 default charset=utf8 comment='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW()),('HanMeimei',23,'dev',NOW()),('Lucy',23,'dev',NOW());
Copy

它是按照建立索引的顺序进行依次比较。

key `idx_name_age_position` (`name`,`age`,`position`) using btree;

EXPLAIN SELECT * FROM employees WHERE NAME = 'Bill' AND age = 31;
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
EXPLAIN SELECT * FROM employees WHERE position = 'manager';Copy

最左前缀原则，所以上面的语句只有第一条会执行。
原因：
在整张表之中查找，如果忽略索引的第一个字段，直接从后面字段查找，字段不是排好序的，无法通过索引查找，还需要全树比对查找。

本作品采用《CC 协议》，转载必须注明作者和本文链接