ChatGPT3.5使用体验( 四 ) _索引

下面这里来讨论下中聚簇索引的实现。
中必有要求有聚簇索引的存在，默认会在主键上建立聚簇索引，如果没有主键字段，表中第一个非空唯一索引将会被建立聚簇索引，在前面两者都没有的情况下，将自动生成一个隐式的自增 id 列，并在此列上建立聚簇索引。
聚簇索引的优点
数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
缺点
1、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于表，一般都会定义一个自增的 ID 列为主键；
2、更新聚簇索引的代价很高，因为会强制将每个更新的行移动到新的位置；
3、二级索引访问可能需要经过两次查找。
二级索引中保存的不是指向行的物理位置的指针，而是行的主键值，这就意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子结点获取对应的主键值，然后根据这个值去聚簇索引中找到对应的行。所以有两次的查找过程，这种叫做回表操作，在中，自适应哈希索引能够减少这样重复的工作。
非聚簇索引（non- index）
非聚簇索引也叫二级索引或者辅助索引，辅助索引叶子节点存储的不是具体的行数据，而是行的主键值，所以使用辅助索引会面临二次查找的问题，也就是回表。存储引擎需要找到二级索引的叶子结点获取对应的主键值，然后根据这个值去聚簇索引中找到对应的行。所以有两次的查找过程，这种就叫做回表查询，在中，自适应哈希索引能够减少这样重复的工作。
联合索引
联合索引指对表上多个列进行索引，联合索引的创建方法和单列索引的创建方法一样，不同的是联合索引有多个索引列。
联合索引中有一个很重要的原则就是最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
create table t(id int auto_increment primary key,a int,b int,key index_a_b (a,b))ENGINE=INNODB;
比如上面的建立了 a 和 b 的联合索引，来看下下面几种查询：
1、 * FROM t WHERE a=1 AND b=3这样 a 和 b 的查询会命中联合索引；
2、 * FROM t WHERE a=1这样a=3的查询会命中联合索引；
3、 * FROM t WHERE b=3 AND a=1这样 a 和 b 的查询也会命中联合索引；
4、 * FROM t WHERE b=3这样b=3是不会命中联合索引，因为 b 位于联和索引第二个位置，不满足最左匹配原则；
来看下联合索引中的最左匹配原则的实现
可以看到联合索引中 (a,b)a 是有顺序的，所以索引 a 列是可以使用这个联合索引的，索引 b 列只是相对索引 a 列是有序的，本身是无序，所以单索引 b 列是不能使用这个联合索引的。
同时因为联合索引在第一个键值固定的情况下，第二个键值进行了排序，所以适合下面的查询
SELECT * FROM t WHERE a=6 ORDER BY b desc
联合索引中索引字段的先后顺序该如何选择，栗如 (a,b)这个联合索引，创建的时候，应该将索引列 a 放前面还是索引 b 列放前面呢？
有一个原则就是：将选择性最高的列放到最前面。
选择性最高值得是数据的重复值最少，因为区分度高的列能够很容易过滤掉很多的数据。组合索引中第一次能够过滤掉很多的数据，后面的索引查询的数据范围就小了很多了。
栗如，一个性别字段，值只有两种男或者女，这种区分度就很低了，搜索的话也只能够过滤掉一半的数据，数据量大的时候，这种效果是不明显的。
这里有一个区分度的公式count( col)/count(*)表示字段不重复的比例，比例越大扫描的记录书越少，唯一键的区分都是1，例如性别等的字段在数据量很大的情况下接近于0 。这个值多少是个标准呢？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是 0.1 以上，即平均 1 条扫描 10 条记录。