《高性能MySQL》笔记-索引

什么是索引，为什么要使用索引

数据库的索引，就像一本字典的索引一样，可以让你快速定位到你想寻找的地方。
索引可以大大加快查找的速度。想想一下，如果一本字典没有索引，想查一个东西只能从头到尾“遍历”整本字典，十分不方便。
虽然索引可以加快查找速度。但是索引也要占用一部分空间，因此索引不是越多越好。
对于重复数据比较多，选择性高的列，不适合作为索引。（索引的选择性：不重复的索引值和数据表的记录总数的比值，即

$$索引的选择性 = \frac {COUNT(DISTINCT(某个列))} {COUNT(某个列)}$$ ，选择性越高，这个索引就能在查找的时候过滤掉更多的行，从而更快的定位到要找的数据。

索引底层的基本实现

1. B-Tree(B+Tree)
   基于树的数据结构，可以进行值的范围查询，并且可以根据最左前缀来进行匹配。
2. Hash
   基于Hash函数，定位数据特别快，但是由于进行Hash操作之后，数据的有序性被破坏，因此无法进行索引的范围查询，且不支持最左前缀匹配。
3. 空间数据索引
   可用来存储地理位置数据，MyISAM存储引擎支持该类型的索引。
4. 全文索引
   比较类似于搜索引擎做的事情，查找的是文本中的关键字。

前缀索引

索引较长的整个字符列，会使得索引变得占用空间大、查询慢，因此可以只使用该字符列的最开始一段字符来作为索引。
假设数据表中存放了某个公司所有员工的信息，现在要在身份证号这一列上添加一个索引，可能添加一个索引长度为18（身份证号码的长度），但是其实没有必要。分析身份证号码的组成可知，号码由6位地区码、8位生日、3位数字顺序码和1位数字校验码组成。如果公司人数不多，那么使用6位的前缀索引，如果人数多一点，可以使用14位的前缀索引。。。
那么问题来了，怎么确定前缀索引的合适长度，总不能这样跟着感觉来把。
现在问题是：怎么利用前缀索引来尽可能达到整列索引（就是索引整个列）的效果，而又能够占用较小的空间？
我们可以利用索引的选择性来确定合适的前缀索引长度。

假设身份证号码这一列名称为person_id，我们可以首先确定有多少个不同的person_id:

SELECT COUNT(*), person_id FROM employee;

然后查看如果以前6个字符作为索引，效果会如何：

SELECT COUNT(*), LEFT(person_id,6) AS pref FROM employee GROUP BY pref；

然后把6改成其他的值，查看不同的前缀长度会有什么不同的效果。
如果某个前缀长度比较小，并且运行以上SQL语句之后的输出值中，相应第二列（person_id和pref列）的第一列数值比较接近，那么这个长度的前缀索引是比较合适的。（换句话说，通过该长度的前缀索引和对应的整列索引筛选出来的数据数量是差不多的）
一般而言，前缀索引长度越长，选择性就越高，但是索引占用空间会变大。因此选取多长的前缀索引需要自己好好斟酌。

前缀索引可以是索引变得更小、更快，但是MySQL无法使用前缀索引做ORDER BY和GROUP BY操作，也无法使用前缀索引做覆盖扫描。

多列索引

选择合适的索引列顺序