高性能mysql--高性能的索引策略（中）

寻找合适的索引序列顺序

这个索引顺序的问题是适用于B-tree索引中的

当不需要进行排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化where条件的查找。需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

比如说语句select * from payment where staff_id = 2 and customer_id = 584

索引是应该创建为（staff_id，customer_id）还是颠倒顺序。这个就要确定哪个列的选择性最高。

如何判定选择性更高

判断staff_id=2和customer_id=584的条数有多少个，然后把数量更小的列建议放在索引的前面。当然这种方式只是正对2和customer_id得出来很片面得值，没有一点的公平性，可能会让服务器整体水平变得更糟。

当然也可以利用pt-query-disgest工具来进行分析。如果不利用工具，那么就考虑全局基数和选择性。而不是具体的某个值。可以利用下面的语句来判定全局的基数。

Select count(distinct staff_id)/count(*) as staff_id_selectivity,count(distinct customer)/count(*) as customer_id_selectivity,count(*) from payment

查看各列的数值，如果某一列的数值高可以作为索引的前列

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引的时候，它的数据行实际上存储在索引的叶子页中，术语聚簇表示数据行和相邻的键值紧凑的存储在一起。一个表只能有一个聚簇索引，因为无法把数据行存放在多个不同的地方。

叶子页包含了行的全部数据，节点页只包含了索引列。InnoDB只聚集同一个页面的记录，包含相邻键值的页面可能会相距甚远。

Mysql不能选择哪个索引作为聚簇索引，InnoDB是通过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDb会隐式定义一个主键作为聚集索引。

聚集的数据有一些重要的优点

可以把相关的数据保存在一起。例如通过用户ID来聚集数据这样只需要从磁盘中读取少量的数据页就能获得某个用户的全部邮件。如果没有使用，可能每一封信都要导致一次I/O
数据访问更快
使用覆盖索引扫描的查询可以直接使用叶节点中的主键值

聚簇索引的缺点

聚簇数据最大限度地提高了I/O密集型应用地性能，但是数据全放在内存中，则访问地顺序就没有那么重要了，聚簇索引就没有什么优势。
插入速度严重依赖于插入顺序。如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表。
更新聚簇索引列的代价很高，因为会被强制移动到新的位置
当在聚簇索引列的表中插入数据，可能会插入到某个已满的列中，那么存储引擎会将该页分成两个页从而来容纳该行，从而导致表占用了更多的磁盘空间。
聚簇索可能导致全表扫描变慢，尤其是行比较稀疏
二级索引需要两次索引查询。原因是因为二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。存储引擎首先根据二级索引的叶子节点获得对应的主键值，然后再根据这个值去聚簇索引中查找对应的行。

InnoDB和MyISAM的数据分布对比

myISAM的数据分布

myISAM按照数据插入的顺序存储在磁盘上。在行的旁边显示了行号，是定常的，更有利于定位。MyISAM的主键分布情况是以（主键，行号）为结构存储的，并且按照主键的顺序进行分布的。

InnoDB的数据分布

因为InnoDB支持聚簇索引，所以使用完全不同的方式存储同样的数据

聚簇索引的每个叶子节点都包含了主键值，事务ID，用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。

还有一点和MyISAM的不同是，InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，myISAM和innoDB的具体主键索引和二级索引形式如下

高性能mysql--高性能的索引策略（中）

在InnoDB表中按主键顺序插入行

如果正在使用的InnoDB表并且没有什么数据需要聚集，那么就定义一个代理键作为主键，最简单的是使用自增列。这样可以保证数据行是是按照顺序写入的，对于根据主键的关联操作的性能也会更好。

最好要避免随机的(不连续且值的分布范围非常大)聚簇索引，特别是对I/O密集型的应用。例如使用uuid作为聚簇索引，完全没有任何聚集的特性。

缺点如下：

注：当然，对于高并发工作负载，在InnoDB中主键的上界通常访问度最高，间隙锁的竞争是最激烈的。另一个热点可能是AUTO_INCREMENT锁机制。如果遇到这个问题，则可以考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置