Mysql索引解析

索引

1.创建索引的SQL

ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
// 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
// 这条语句创建索引的值必须是唯一的。

ALTER TABLE tbl_name ADD INDEX index_name (col_list);
// 添加普通索引，索引值可出现多次。

2.默认方式创建索引innodb

3.聚集索引和非聚集索引

聚集索引：索引和数据存储在一起。在innodb存储引擎中数据和索引都存在ibd文件中。

非聚集索引：索引和数据存储在不同的地方，例如：MyISAM存储引擎将索引存在MYI文件，将数据存在MYD文件中。

1.面试：为什么innodb表要建主键索引，并且推荐主键是整型自增的。

二级索引要回表，找主键，通过主键再去定位数据。整型自增为什么？？B+树遍历时整型自增比大小效率高。

uuid效率低。uuid相比占用空间大。使用自增主键插入元素效率比较高，B+树底层直接往后插，不会因为占用内存大而分裂。

4.最左前缀原则

联合索引要符合最左前缀原则。比如只对a\b\c三个字段建立联合索引，写SQL时where要写a字段的条件。

为什么要这样？生成B+树时，会依次比较各个联合索引，第一个相同再比较第二个。不提供第一个索引条件会导致索引失效。

5.索引失效的情况

• 不满足最左前缀原则：因为联合索引的情况下，数据是按照索引第一列进行排序，第一列相同才会按照第二列排序。
• select * ：把*改成索引字段（覆盖索引）
• order by：select 索引a，非索引字段 from 表 order by xx 。这个时候会索引失效，因为索引字段和非索引字段不在一个索引列中，需要回表。解决，通过子查询查出id然后手动回表。
• sql中有运算、函数：因为对索引列进行了重新计算
• where条件，写错类型 varchar不带引号。int带着引号，会走索引。
• like左边包含了百分号
• 列对比导致索引失效，使用覆盖索引
• 使用or关键字,mysql8中需要or两边都建立索引

6.常问的索引类型

（1）二级索引

（2）覆盖索引

select id from product where product_no = '0002';

当通过二级索引就能找到不需要回表，这就叫做覆盖索引。

（3）前缀索引

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

（4）联合索引

通过将多个字段组合成一个索引，该索引就被称为联合索引。

7.什么时候（不）适合建立索引

• 需要建立：

字段唯一，比如商品编码。

经常用于where查询的字段，可以快速定位并且不需要回表。

经常用于order by和group by的字段。

• 不需要建立：

  字段中有大量的重复字段，这样B+树分叉就很少，比如性别。

表数据太少，不需要建立索引。

经常更新的字段不要建立索引，维护成本大。

where、group by ，order by用不到的字段，起不到定位作用。

8.B+树的生成过程

• mysql底层会对插入的数据进行分组，会把每个组中最小的索引放在页目录当中。页目录和数据组成了页，默认大小是16KB。很多页形成双向链表。

• 为所有的数据页生成一个索引页，索引页中存放每页最小的索引。每个索引有一个指针，指向数据页。

• 根据索引页—>数据页---->数据页目录---->所在分组----->定位数据

9.为什么索引采用B（+）树

为什么不用二叉树：对于自增的数据索引，二叉树会退化成链表。使用索引查找还是需要全表扫描。

为什么不用红黑树：当数据量大的时候，高度不可控

B树：横向存储的元素多，高度低

B+树：所有的数据放在叶子结点，中间的非叶子结点是冗余的索引元素。叶子结点用指针连接。每个结点16KB，bigint8字节，索引6个字节。三层的索引B+树，可以存放三千万条数据（每条数据按照1KB算，索引按bingint类型计算、索引按6字节计算）。磁盘io少。对于mysql高版本，索引会被存放在内存，磁盘io一次。

10.索引优化

• 建立前缀索引，order by无法使用前缀索引
• 覆盖索引，所写SQL需要的字段是包含在二级索引的叶子节点的，不需要回表
• 主键递增，不然插入的时候会发生页分裂
• 索引设置成not null 否则优化器在索引选择时更复杂
• 防止索引失效