MySQL索引优化

判断索引选择性好坏

可以使用 show index from table_name方法，看到一个索引的基数。“基数”（cardinality）越大，索引的区分度越好。

 show index from *****;
+---------------------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table               | Non_unique | Key_name    | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

索引优化

（1）如果 force index 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。

（2）可以考虑修改语句，引导 MySQL 使用我们期望的索引。比如，在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。

（3）在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

索引采样

InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的， 索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新 做一次索引统计。 在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。

设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。 由于是采样统计，所以不管 N 是 20 还是 8，这个基数都是很容易不准的。

修正统计信息

统计信息不对，那就修正。analyze table table_name 命令，可以用来重新统计索引信息。

是否使用前缀索引前缀索引优势

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

select count(distinct left(email,4)）as L4, count(distinct left(email,5)）as L5, count(distinct left(email,6)）as L6, count(distinct left(email,7)）as L7, from SUser;

前缀索引劣势

用前缀索引后，可能会导致查询语句读数据的次数变多。

对覆盖索引的影响

如果使用 index1（即 email 整个字符串的索引结构）的话，可以利用覆盖索引，从 index1 查到结果后直接就返回了，不需要回到 ID 索引再去查一次。

而如果使用 index2（即 email(6) 索引结构）的话，就不得不回到 ID 索引再去判断 email 字段的值。 即使你将 index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。