MySQL前缀索引

注意： 本文的所有优化仅针对InnoDB存储引擎。

MySQL索引可以加快查询速度，但是索引并不是越多越好，索引虽然可以加快数据的查询速度，但是索引文件本身需要占用存储空间，数据的插入，删除，和修改也需要索引保持同步，据一线有经验的DBA介绍，索引列占表所有列的30%是比较合理的。

1	show index from `table`;

MySQLinformation_schema数据库TABLES表官方手册

1	select concat(round(sum(INDEX_LENGTH/(1024*1024)), 2), 'MB') as 'Total Index Size' from `information_schema`.`TABLES` where `table_schema` = 'order' and `table_name` = 'order_base';

注意： 以下两种情况，不建议建立索引。

表记录数比较少 —— InnoDB辅助索引叶子节点存储的不是记录的物理地址，而是主键值，使用辅助索引查找数据总是需要第二次查找主键索引，在表记录较少的情况下，建立索引反而会降低查询性能，所以直接全表扫描就好了，具体基准需根据业务场景而定。
索引的选择性比较低 —— 索引的选择性指不重复的索引值与表记录数的比值，值域为(0, 1]。索引选择性越高越好，可以通俗的理解为，选择性高的索引在查询时往往可以筛选出比较多的记录，所以访问表的次数较少，索引的有效率比较高。例如存在基本表table，我们要在表table的column列上建立一个索引，我们使用如下SQL语句计算该索引的选择性：

1	select count(distinct `column`) / select count(*) from `table`;

前缀索引是一种与索引选择性相关联的索引优化技术，顾名思义，使用列的前缀代替整个列作为Key，当前缀长度合理时，既可以做到前缀索引的选择性接近全列索引，同时因为索引Key变短而减少索引文件的大小和维护开销。

想象一下存在如下业务场景，一张存放订单信息的基本表order，订单号长度为24位（如：61700123215824），表结构如下：

首先计算一下，全列索引选择性：

1	select round(count(distinct `order_id`) / count(*), 2) from `order`;

结果为1.00，选择前缀长度为5，看一下索引的选择性：

1	select round(count(distinct left(`order_id`, 5)) / count(*), 2) from `order`;

结果为0.00，5个字符长度的前缀，看来不行，我们增加到10，看一下索引的选择性：

1	select round(count(distinct left(`order_id`, 10)) / count(*), 2) from `order`;

结果为0.63，差强人意，我们继续以5位单位递增，看一下索引的选择性：

1	select round(count(distinct left(`order_id`, 15)) / count(*), 2) from `order`;

由于round的四舍五入，结果为1.00，那前缀能不能更少一些，在公司的业务系统内试了一下，前缀长度为11时，索引选择性为0.9999，四舍五入为1。通过夹逼的方法（😆默默的想起高数的夹逼定理），找到了最佳的索引前缀长度为11，接下来我们创建前缀索引：

创建前缀索引

1	alter table `order` add index `idx_order_id`(`order_id`(11));

注意： 前缀索引的缺点