mysql索引

为了提高查询速度，mysql提供了索引功能。

数据结构

mysql索引使用B＋树数据结构，是多路平衡搜索树，由二叉树、平衡二叉树等演变而来，这里不再专门讨论B＋树。

mysql中B＋树节点大小默认16KB，大于操作系统中页的大小，是为了让树更加扁平，减少磁盘IO的次数，同时大小是操作系统中页大小的2的整数次幂倍。
mysql中每一个索引都对应了一棵B＋树。这里专门提醒。对于innode存储引擎，聚集索引的行数据和索引都存在叶子节点中。与之相反的是，myisam里面没有真正的数据，只有索引和数据指针，指向磁盘存储数据的位置。

索引分类

索引分为主键索引、唯一索引、普通索引、组合索引以及全文索引。

主键索引

非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的B+树包含表数据信息。

PRIMARY KEY(key);

唯一索引

不可以出现相同的值，可以有NULL值。

UNIQUE(key);

普通索引

允许出现相同的索引内容，也允许为空值。

INDEX(key);
-- 另一种写法
KEY(key[,...]);

组合索引

对表上的多个列进行索引。

INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

对于组合索引而言，由多个字段决定B＋树的顺序。
这里单独提一下，如果B＋树上的key值相同，不需要再加一个小值，放在右子树上即可。当然也可以放在左子树，这样的意思就是key相同的情况下，后插入的节点小一些。

全文索引

将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术，关键词 FULLTEXT，在短字符串中用 LIKE %，在全文索引中用 match 和 against。

主键选择

innodb 中表是索引组织表，每张表有且仅有一个主键：
1、如果显示设置 PRIMARY KEY ，则该设置的key为该表的主键；
2、如果没有显示设置，则从非空唯一索引中选择；
(1)只有一个非空唯一索引，则选择该索引为主键；
(2)有多个非空唯一索引，则选择声明的第一个为主键；
(3)没有非空唯一索引，则自动生成一个6字节的 _rowid 作为主键。_rowid的大小选择6字节，是由表的最大容量决定的。

约束

为了实现数据的完整性，对于innodb，提供了以下几种约束，primary key，unique key，foreign key， default, not null。
这里提示一下外键索引。
外键用来关联两个表，来保证参照完整性。MyISAM存储引擎本身并不支持外键，只起到注释作用，而innodb完整支持外键。给个例子。

create table parent 
(
    id int not null, primary key(id)
) engine=innodb;

create table child 
(
    id int, parent_id int, foreign key(parent_id) references parent(id) ON DELETE CASCADE ON UPDATE CASCADE
) engine=innodb;
-- 被引用的表为父表，引用的表称为子表
-- 外键定义时，可以设置行为 ON DELETE 和 ON UPDATE，行为发生时的操作可选择：
-- CASCADE 子表做同样的行为
-- SET NULL 更新子表相应字段为 NULL
-- NO ACTION 父类做相应行为报错
-- RESTRICT 同 NO ACTION
INSERT INTO parent VALUES (1);
INSERT INTO parent VALUES (2);
INSERT INTO child VALUES (10, 1);
INSERT INTO child VALUES (20, 2);
DELETE FROM parent WHERE id = 1;

外键具备事务性，如删除动作与子表中的删除是在一个事务中的。
一般不建议使用外键约束，尤其是对外键不熟悉的情况下，避免产生一些意外。
提示一下约束于索引的区别：创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束是逻辑上的概念；索引是一个数据结构，既包含逻辑的概念也包含物理的存储方式。

索引实现

innodb和myisam存储引擎的实现有所不同，这里先讲一个共同点，它们都有.frm文件，记录表结构，与存储引擎无关。

myisam

除.frm文件外，myisam索引还有.MYD文件和.MYI文件。.MYD文件记录数据，是堆表结构，不是B＋树。.MYI文件是索引，是B＋树，指向.MYD中数据。所以myisam读取性能较高。

innodb

除.frm文件外，innodb的索引还有一个.ibd文件，数据和索引都存储在文件中。除此之外，还有一个全局数据，存储在ibdata文件中，是共享表空间，存储一些共享数据。
再讲一下innodb的存储结构。innodb由段、区、页组成，段分为数据段、索引段、回滚段等；区大小为 1 MB（一个区由64个连续页构成），页的默认值为16k，页为逻辑页，磁盘物理页大小一般为 4K 或者 8K；为了保证区中的页的连续，存储引擎一般一次从磁盘中申请 4~5 个区。

聚集索引和辅助索引

聚集索引就是主键按照主键构造的 B+ 树，叶子节点中存放数据页，数据也是索引的一部分。辅助索引的叶子节点不包含行记录的全部数据；辅助索引的叶子节点中，除了用来排序的 key 还 包含一个bookmark，该书签存储了聚集索引的 key。
使用辅助索引的话，就要先根据辅助索引找到聚集索引，再根据聚集索引找到相应数据，也就是说，有两次查找的过程。辅助索引需要显式创建，所以自行根据业务场景创建。

优化

最左匹配原则

对于组合索引，从左到右依次匹配，遇到 > < between like 就停止匹配。
优化器会自动进行优化，会根据之前创建组合索引的顺序对查询语句的匹配顺序进行优化。如果查询的key个数少于组合索引key的个数，那么索引会进行最左匹配，匹配成功则索引仍有效，失败则失效。以上child表为例

INDEX(id, parent_id);

此时查询语句为

select * from child where parent_id = 2 and id = 1;

会自动进行调整。
优化器会优先使用组合索引，因为组合索引的数据会小一些（只存储了key），每个节点存储的行记录会多些，磁盘IO次数较少，而聚集索引还存储了数据（事务相关），每个节点存储记录较少。

覆盖索引

辅助索引中已经包含了所有需要查找的数据，就不用聚集索引，原因也是组合索引较小。
这里再提示一下，就算不满足最左匹配原则，依然是能使用索引的地方就使用索引，而且是能用辅助索引就用辅助索引。

索引失效

1、不包含索引

select ... where A and B;

若 A 和 B 中有一个不包含索引，则索引失效。
2、索引字段参与运算则索引失效，例如

from_unixtime(idx) = '2021-04-30';

3、索引字段发生隐式转换，则索引失效。例如 1隐式转换为 ‘1’，但是字符串转换成数值可以。

4、LIKE 模糊查询，通配符 % 开头，则索引失效。例如

select * from user where name like '%ark';

注意如果不是通配符开头，不会失效。
5、在索引字段上使用 NOT <> != 索引失效。如果判断 id <> 0 ，可以修改为 idx > 0 or idx < 0，这样索引不会失效。
6、组合索引中，没使用第一列索引，索引失效，原因与4相同，但是有一点特殊。如果符合覆盖索引，不会失效。

索引原则

1、查询频次较⾼且数据量⼤的表建⽴索引。索引选择使⽤频次较⾼，过滤效果好的列或者组合。过滤性好，即是重复数据少，count(distinct idx)/count(*)越接近1越好。
2、使⽤短索引。节点包含的信息多，较少磁盘io操作。
3、对于很长的动态字符串，考虑使用前缀索引。
有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的
部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让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));
-- 注意：前缀索引不能做 order by 和 group by

这一点在名字匹配上应用较多。
4、对于组合索引，考虑最左侧匹配原则和覆盖索引。
5、尽量扩展索引，在现有索引的基础上，添加复合索引；
6、不要 select * ； 尽量只列出需要的列字段；
7、索引列，列尽量设置为非空。索引列设置为空可能会造成业务逻辑上的bug。

慢查询日志

一般是DBA或是运维使用，把慢查询给到相应的开发人员进行优化，开发人员一般没有直接查询的权限。类似的还有SHOW PROFILE和SHOW PROCESSLIST等。开发人员一般接触的是EXPLAIN语句和优化器选择过程。