4.2索引

建立索引的几大原则

1.选择唯一性索引

* 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。**例如：学生表中学号是具有唯一性的字段 **

2.为经常需要排序、分组和联合操作的字段建立索引

* 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3.为常作为查询条件的字段建立索引

* 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4.限制索引的数目

* 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。

5.尽量使用数据量少的索引

* 如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

6.尽量使用前缀来索引

* 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7.尽量的扩展索引，不要新建索引

* 比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

8.最左前缀匹配原则，非常重要的原则

* mysql会一直向右匹配直到遇到范围查询(>、< 、between、like)就停止匹配，比如a="1" and b="2" c>"3" and d = "4" 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

9.=和in可以乱序

* 比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

10.索引列不能参与计算，保持列“干净”

* 比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

btree与hash索引的适用场景和限制

hash索引

• 适用场景：不需要做排序、范围查询的需求。
• 限制：
  • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
  • 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
  • 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列(A, B)上建立哈希索引，如果查询只有数据列A，则无法使用该索引。
  • 哈希索引只支持等值比较查询，包括=、in()、< = >;。不支持任何范围查询，例如where price > 100。
  • 访问哈希索引的数据非常快，除非有很多哈希冲突。
  • 如果哈希冲突很多的话，一些索引维护操作的代价也很高。

btree索引

• 适用场景：用于全键值、键值范围、或者键前缀查找，其中键前缀查找只适合用于根据最左前缀的查找。
• 限制
  • 如果不是按照索引的最左列开始查找的，则无法使用索引(注意，这里不是指的where条件的顺序，即where条件中，不管条件顺序，只要where中出现的列在多列索引中能够从最左开始连贯起来就能使用到多列索引)
  • 不能跳过索引中的列，如：查询条件为姓和出生日期，跳过了名字列，这样，多列索引就只能使用到姓这一列
  • 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询，如：where last_name=xxx and first_name like ‘xxx%’ and dob=’xxx’;这样，first_name列可以使用索引，这列之后的dob列无法使用索引。