mysql之索引

mysql的索引是存储引擎实现的，而不是服务层实现的，没有统一的标准，不同的引擎支持的索引类型不太一样也不一定支持所有的索引，即使同一种索引不同的引擎实现方式也不一定一样

索引类型

B-Tree索引

最常见的一种索引，不同的引擎以不同的方式使用B-Tree(B+,T-Tree索引)，性能也各有不同，各有优劣。

BTree对索引是按顺序存储的，很适合查找范围数据，索引对多个值排序的依据是定义索引的时候列的顺序可以使用BTree的索引查询类型：全键值，键值范围查询，列前缀匹配。BTree索引的劣势必须最左列匹配，不能跳过中间的列，如果某一个列有范围匹配，则该列右边的列的索引无法生效

哈希索引

只有memory引擎显示支持哈希索引，哈希索引只能进行精确匹配。哈希索引可以用来压缩索引列的长度如：一个url压缩成一个64位的int大大减少索引字段的空间

全文索引

全文索引是用来匹配文本关键字的类似于搜索引擎

空间索引（R-Tree）

用来存储地理位置信息的，不同于其他索引列，从各个维度用来进行索引

其他索引（TokuDB的分形树）

一种新的数据结构，既有BTree的优点，又避免了BTree的缺点

高性能索引

独立索引

索引列不能是表达式的一部分，假设 tablea 的列columa上有独立的索引,则如下查询:

select * from tablea where columna+1=333 ,不能使用该索引，因为 (columna+1) 为表达式，mysql无法自动解析，查询时尽量简化where语句，将索引列单独放置到比较符号的一侧

前缀索引和索引选择性

有时候需要索引很长的字符串，这会使索引非常慢，一种办法是受用哈希对字符串进行瘦身，有时候这种做法还不够，通常可以索引开始的部分字符串而不是全部

多列索引

常见的错误是为每个列创建索引或按错误的顺序创建多列索引，再多个列上创建单独的索引大多数情况下并不能提高mysql的性能j(5.0之后的索引合并策略，一定程度上可以缓解)，向下面的例子，在film_id和actor_id上各有一个单列索引，对于如下查询都不是好的选择： select film_id,actor_id from tableA where actor_id=1 or film_id=2

选择合适的索引顺序

在一个多列BTree索引中，索引列的顺序意味着索引先按照最左列进行排序，其次是第二列，等等。一个经验法则是将选择性最高的列放到索引的最前列,但通常不如避免随机I/O，和排序那么重要。可能还依赖于值得分布，和查询的运行频率来调整索引列

聚簇索引

聚簇索引不是一种索引类型，而是一种数据存储的方式 ，具体的细节依赖其实现方式。 innodb会给主键生成聚簇索引，如果主键不存在，会将第一个非Null 的unique_key当做聚簇索引，如果没有unique_key则会生成一个隐藏的聚簇索引，innodb中聚簇索引是和存放于叶节点当中的。

• 优点
  可以把相关数据存放在一起，例如根据用户Id来聚集数据，这样加载用户数据的时候，只需从磁盘读取少数页就到获取到聚集的某个用户的所有数据，否则可能导致每读取一条数据，发生一次磁盘IO
  数据访问速度更快，聚簇索引将数据和索引存放在一起，通常比非聚簇索引更快
  使用覆盖索引扫描的查询可以直接使用页节点的中主键值
• 缺点
  插入速度严重依赖于插入顺序，按照主键的顺序插入，是加载数据到innodb中最快的方式，如果不是按顺序插入，最好加载完成后执行一次optimize_table命令重新组织下表。
  更新聚簇索引列的代价很高，强制被更新的行移动到新的位置
  在插入新行或主键更新导致需要移动行的时候，面临也分裂（一行数据存储到2个页中）
  导致全表扫描变慢，特别是行比较稀疏的时候，或页分裂导致数据不一致的时候
  innodb中主键递增的插入，可以避免随机IO的发生，但是高并发的情况下却会造成主键的上界热点，因为所有的插入都发生再这里，如何解决？

覆盖索引：

如果一个索引包含所有需要查询的字段，也就没必要再去查询表了，则称之为覆盖索引，覆盖索引是非常有用的工具，可以极大的提高性能，因为索引是存在于内存中（数据不一定，innodb因为聚簇索引的存在，Myisam内存中只存索引）

冗余和重复索引

1. 重复索引

重复索引是指完全相同（列顺序，和索引类型都完全相同）的索引，mysql的主键限制和唯一限制是通过索引实现的，mysql允许创建重复索引，但是通常没有理由这么做。

2. 冗余索引

冗余索引和重复索引有一些不同，索引A,是索引（A,B）的冗余索引（两者索引类型相同）；索引B不是索引（A,B）的索引。大部分时候不需要冗余索引，应该扩展已有的索引而不是，新增加索引，有时候也有例外，例如扩展新的索引导致索引太大了，导致使用该索引的其他查询性能问题，例如

如果在整数列上存在一个索引，现在需要额外增加一个很长的varchar列扩展该索引，那性能可能回急剧下降，特别是查询把这个索引当做覆盖查询或者表是myisam表而且有很多范围查询的时候（myisam前缀压缩）