Mysql索引

什么是索引

简单来说，索引类似于书的目录，可以加速我们的查找。

Mysql索引分类

按物理存储分为：聚簇索引和非聚簇索引（也叫二级索引或辅助索引）
按照字段特性分为：主键索引、唯一索引、普通索引、全文索引
按照字段个数分为：单列索引和联合索引（也叫复合索引或组合索引）

Mysql索引实现 页

页是InnoDB磁盘管理的最小单位，默认16k，可通过 innodb_page_size 参数来修改，B+树的一个节点就是一个页。

B+树

特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息，叶子结点之间通过指针连接，方便范围查询。在Mysql中，每个索引对应一颗B+树。

B+树树高问题

innodb 的一个节点大小为16 kb，
假设：
key为10 bytes，指针大小为 6 bytes，假设一行记录大小为1kb；
非叶子节点可以存储：16kb/16b=1024个索引，叶子结点存储16条记录。
结论：
树高为2时：最多存储 102416 = 16384 条记录
树高为3时：最多存储 1024102416 = 16777216 条记录（千万级）
树高为4时：最多存储 10241024102416 = 17179869184 条记录（百亿级）

聚簇索引和非聚簇索引

聚簇索引也叫主键索引（非空、唯一），叶子结点存储的是具体的数据。在数据页内部，可以认为是二分查找定位id位置。

假设现在有张表 t ，id为主键，并在 k 字段上创建索引（二级索引）。

mysql> create table t （
	id int primary key,
	k int not null default 0,
	index k(k)
）engine=InnoDB;

mysql> select * from t where id > 18 and id < 40;

查找流程如下：

非聚簇索引也叫二级索引或辅助索引，和聚簇索引的区别在于，非聚簇索引的叶子结点存储的是主键的值。

select * from t where k = 33;  // * 表示查找整行记录

查找流程如下图：

因为是查找整行记录，找到k=33对应的id的值，再根据id的值去id索引树上查找整行记录，该 *** 作称为回表。

假如我们的查询语句改为：

mysql> select id from t where k = 33;

此时，因为 k 索引的叶子节点已经包含了我们的查询结果，也就是id的值，所以这条查询语句不需要执行回表的 *** 作，这个优化就是覆盖索引。

覆盖索引

从辅助索引中就能找到数据，而不需通过聚集索引查找；

联合索引

对表上的多个列进行索引，比如： 在user 表中，我们在name 和 age字段，创建name_age 的联合索引。

建表语句如下

mysql> create table `user` (
	`id` int(11),
	`name` varchar(64) NOT NULL,
	`age` int(11) NOT NULL,
	`ismale` tinyint(1) DEFAULT NULL,
	PRIMARY KEY(`id`),
	KEY `name_age` (`name`, `age`)
)ENGINE=InnoDB;

联合索引是按照索引定义里面出现的顺序排序的。先按照 name 的顺序排序，在 name 相同时才会以 age 字段排序，更多字段的联合索引类似。

最左匹配原则

对于联合索引，从左到右依次匹配，遇到 > < between like 就停止匹配；

selcet * from user where name like `张%`;

此时，可以用上name_age的联合索引，找到第一个符合条件的记录是ID-3，然后向后遍历，直至不满足条件为止。

索引失效

1. select … where A and B 若A或者B有一个不包含索引，则索引失效；
2. 索引字段参与运算；比如：from_unixtime(idx) = ‘2021-04-30’; 但是，像 where id * 2 = 10，这种还是走索引的。
3. 索引字段发生隐式类型转换；例如： ‘1’ 隐式转换为 1 ；
4. LIKE 模糊查询，通配符 % 开头，则索引失效
5. 在索引字段上使用 NOT <> != 索引失效；
6. 联合索引中，没使用第一列索引，索引失效；
7. in + or 索引失效；单独的in 是不会失效的；not in 肯定失效的；

创建索引的原则

• 查询频次高且数据量大的表建立索引，索引选择使用频次较高且区分度好的字段或者组合，像性别这种只有两种结果的字段不适合做索引。
• 尽量使用短索引，比如：smallint，tinyint；节约空间，节点包含的信息更多，节约磁盘IO
• 对于组合索引，考虑最左前缀原则和覆盖索引
• 尽量扩展索引，在现有的索引基础上，添加复合索引；最多6个索引
• 不要使用select *，尽量值写需要的列字段，方便覆盖索引
• 索引列尽量设置为非空
• 对于很长的字符串，考虑使用前缀索引

解释下：对于很长的字符串，考虑使用前缀索引
有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的区分度，索引的区分度是指不重复的索引值和数据表记录总数的比值。索引的区分度越高则查询效率越高，因为区分度更高的索引可以让mysql在查找的时候过滤掉更多的行。对于 BLOB , TEXT , VARCHAR 类型的列，必要时使用前缀索引，因为mysql 不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的区分度。

select count(distinct left(name,3))/count(*) as sel3,
count(distinct left(name,4))/count(*) as sel4,
count(distinct left(name,5))/count(*) as sel5,
count(distinct left(name,6))/count(*) as sel6, from user;

alter table user add key(name(4));  // 选择区分度最大的结果作为前缀索引