五、高性能索引

索引是存储引擎用于快速找到记录的一种数据结构。

 

1、索引基础

 

要理解MySQL中索引是如何工作的，最简单的方法就是去看看一本书的“索引”部分，如果想在一本书中找到某个特定

 

 

1.1 索引的类型

 

在MySQL中，索引是在存储引擎层，而不是服务层

 

B-Tree索引

 

MySQL大多数都是使用B-Tree索引 InnoDB使用B+Tree

存储引擎以不同的方式使用B-Tree索引，性能各有不同，各有优劣

eg:MyISAM使用前缀压缩技术使得索引更小，但InnoDB按照原数据格式进行存储

eg:MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB根据主键引用被索引的行

 

可以使用B-Tree索引的查询类型。B-Tree索引适用于全键值、键值范围或者键前缀查找

 

全值匹配 和索引中的所有列进行匹配

匹配最左前缀 只使用索引第一列

匹配列前缀 只匹配某一列的值的开头部分

匹配范围值

精准匹配某一列并范围匹配另外一列

只访问索引的查询

 

一些关于B-Tree索引的限制

 

（1） 如果不是按照索引的最左列开始查找，则无法使用索引

（2） 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询

 

哈希索引

 

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。

MySQL中，只有Memery引擎显示支持哈希索引，这是Memery引擎的默认索引。

 

 

全文索引

 

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。更类似于搜索引擎做的事情，而不是简单的where条件匹配。 match against

在相同的列同时创建全文索引和基于值的B-Tree索引不会冲突。

 

MySql自带的全文索引只能用于数据库引擎为MYISAM的数据表，如果是其他数据引擎，则全文索引不会生效。此外，MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索

 

2、索引的优点

 

索引可以让服务器快速定位到表的指定位置。但是这并不是索引的唯一作用，到目前为主可以看到，根据创建索引的数据结构不同，索引也有一些其他的附加作用

 

最常见的的B-Tree索引，按照顺序存储数据，所以MySQL可以用来order by 和 group by

 

1 索引大大减少了服务器需要扫描的数据量

2 索引可以帮助服务器避免排序和临时表

3 索引可以将随机I/O变成顺序I/O

 

3、高性能索引策略

 

3.1 独立的列

 

独立的列 是指 索引列不能是表达式的一部分，也不能是函数的参数

 

eg : select user_id from user_info where user_id + 1 = 5;

 

3.2 前缀索引和索引选择性

 

有时候索引很长的字符列，会让索引变得大且慢。

可以索引开始的部分字符，大大节约索引空间，提高索引效率，这样也会降低索引的选择性

 

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点 ：MySQL无法使用前缀索引order by group by ，也无法做覆盖扫描

 

3.3 多列索引

 

很多人对多列索引的理解都不够，一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引

 

单个列，单个索引 用where or 就完蛋

 

3.4  择合适的索引列顺序

 

索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列

 

如何选择顺序，经验法则：选择性最高的列放在索引最前列。

 

当不考虑排序和分组时，将选择性最好的列放在前面通常是最好的，这时候索引的作用只是用于优化where条件的查询。

 

select count(distinct topic_id)/count(*) as topic_num , count(distinct post_id)/count(*) as post_num from topic_post_map;

 

3.5 压缩（前缀）索引

 

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存。

 

压缩块使用更少的空间，代价是某些操作可能更慢。 比如 order by desc

 

3.6 冗余和重复索引

 

重复索引是指在相同的列按照相同的顺序创建的相同类型的索引

 

冗余索引 索引（A，B），再创建索引（A）就是冗余索引

再创建索引（B，A）或者 索引（B）都不是冗余索引

 

索引（A，ID）其中ID是主键，对于InnoDB来说主键列已经包含再二级索引中了