Mysql索引

https://blog.csdn.net/weixin_43935927/article/details/109491334

建立索引，要使用离散度（选择度）更高的字段。

我们先来看一个重要的属性列的 离散度，

count(distinct(column_name)) : count(*) -- 列的全部不同值个数：所有数据行行数

数据行数相同的情况下，分子越大，列的离散度就越高。简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。

当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。

创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引

create table shop(address varchar(120) not null)

alter table shop add key(address(12))  // 截取12个字符作为前缀索引是最优的吗？

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的，截取得少了，重复内容太多，字段的散列度（选择性）会降低。怎么计算不同的长度的选择性呢？

先看一下字段在全部数据中的选择度计算公式：

select count(distinct address) / count(*) from shop

select count(distinct left(address, n)) / count(*) as subn from shop

count(distinct left(address,n)) / count(*) 的结果是会随着 n 的变大而变大。举个例子，现在有两个address（东大街长兴小区，东大街福乐小区），那么 distinct(address，2) <distinct(address，3）

==>所以，截取的长度越长就会越接近字段在全部数据中的选择度

==>所以，我们要权衡索引大小和查询速度。

举个例子，通过不同长度去计算，与全表的选择性对比：

    SELECT  COUNT(DISTINCT(address))/COUNT(*) sub,            -- 字段在全部数据中的选择度

    COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5,  -- 截取前5个字符的选择度

    COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7, 

    COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,

    COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10,  -- 截取前10个字符的选择度

    COUNT(DISTINCT(LEFT(address,11)))/COUNT(*) sub11,

    COUNT(DISTINCT(LEFT(address,12)))/COUNT(*) sub12,

    COUNT(DISTINCT(LEFT(address,13)))/COUNT(*) sub13,

    COUNT(DISTINCT(LEFT(address,15)))/COUNT(*) sub15

FROM shop

+--------+--------+--------+--------+--------+--------+--------+--------+--------+

| sub    | sub5  | sub7  | sub9  | sub10  | sub11  | sub12  | sub13  | sub15  |

+--------+--------+--------+--------+--------+--------+--------+--------+--------+

| 0.9993 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9914 | 0.9943 | 0.9943 | 0.9958 |

+--------+--------+--------+--------+--------+--------+--------+--------+--------+

可以看到在截取 11 个字段时 sub11(0.9993) 就已经很接近字段在全部数据中的选择度 sub(0.9958)了，而且长度也相较后面更短一些， 综合考虑比较合适。

ALTER TABLE shop ADD KEY (address(11))

1.索引的个数不要过多（浪费空间，更新变慢）

2.在用于 where 判断 order 排序和 join 的（on）字段上创建索引

3.区分度低的字段，例如性别，不要建索引（离散度太低，导致扫描行数过多）

4.更新频繁的值，不要作为主键或者索引（页分裂）

5.不建议用无序的值作为索引，例如身份z、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）

6.若在多个字段都要创建索引的情况下，联合索引优于单值索引

7.联合索引把散列性高（区分度高）的值放在前面

问题

我们有一个 SQL，用于找到没有主键 / 唯一键的表，但是在 MySQL 5.7 上运行特别慢，怎么办?

实验

我们搭建一个 MySQL 5.7 的环境，此处省略搭建步骤。

写个简单的脚本，制造一批带主键和不带主键的表：

执行一下脚本：

现在执行以下 SQL 看看效果：

...

执行了 16.80s，感觉是非常慢了。

现在用一下 DBA 三板斧，看看执行计划：

感觉有点惨，由于 information_schema.columns 是元数据表，没有必要的统计信息。

那我们来 show warnings 看看 MySQL 改写后的 SQL：

我们格式化一下 SQL：

可以看到 MySQL 将

select from A where A.x not in (select x from B) //非关联子查询

转换成了

select from A where not exists (select 1 from B where B.x = a.x) //关联子查询

如果我们自己是 MySQL，在执行非关联子查询时，可以使用很简单的策略：

select from A where A.x not in (select x from B where ...) //非关联子查询:1. 扫描 B 表中的所有记录，找到满足条件的记录，存放在临时表 C 中，建好索引2. 扫描 A 表中的记录，与临时表 C 中的记录进行比对，直接在索引里比对，

而关联子查询就需要循环迭代：

select from A where not exists (select 1 from B where B.x = a.x and ...) //关联子查询扫描 A 表的每一条记录 rA：     扫描 B 表，找到其中的第一条满足 rA 条件的记录。

显然，关联子查询的扫描成本会高于非关联子查询。

我们希望 MySQL 能先"缓存"子查询的结果（缓存这一步叫物化，MATERIALIZATION），但MySQL 认为不缓存更快，我们就需要给予 MySQL 一定指导。

...

可以看到执行时间变成了 0.67s。

整理

我们诊断的关键点如下：

\1. 对于 information_schema 中的元数据表，执行计划不能提供有效信息。

\2. 通过查看 MySQL 改写后的 SQL，我们猜测了优化器发生了误判。

\3. 我们增加了 hint，指导 MySQL 正确进行优化判断。

但目前我们的实验仅限于猜测，猜中了万事大吉，猜不中就无法做出好的诊断。

---