什么是索引
简单来说，索引类似于书的目录，可以加速我们的查找。
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，然后向后遍历，直至不满足条件为止。
索引失效
- select … where A and B 若A或者B有一个不包含索引，则索引失效；
- 索引字段参与运算；比如：from_unixtime(idx) = ‘2021-04-30’; 但是，像 where id * 2 = 10，这种还是走索引的。
- 索引字段发生隐式类型转换；例如： ‘1’ 隐式转换为 1 ；
- LIKE 模糊查询，通配符 % 开头，则索引失效
- 在索引字段上使用 NOT <> != 索引失效；
- 联合索引中，没使用第一列索引，索引失效；
- 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)); // 选择区分度最大的结果作为前缀索引
笔记参考于极客时间《MySQL实战45讲》
