对于mysql数据库索引的数据结构可以参考这篇文章：
http://blog.codinglabs.org/articles/theory-of-mysql-index.html
1，mysql数据库索引的类型
- B+tree
如果mysql没有特定指明，多半说的就是b+tree 索引，这种索引意味着所有的值都是按照顺序存储的，并且每一个叶子到根的距离是相同的，具体结构如图：
b-tree索引的查询有效的类型如下：
- 全值匹配
- 匹配最左前列：联合索引的时候，只能单独使用最左那一列
- 匹配列前缀：可以匹配某一索引列值的开头部分，但是也只能使用联合索引的第一列
- 匹配范围值
- 精确匹配某一列，但是范围匹配另一列
- 只访问索引查询：也就是覆盖索引，查询的数据为索引列。
下面是一些使用索引的限制:
- 联合索引中，如果不是按照索引的最左列查找，那么无法使用索引
- 不能跳过索引中的列：例如联合索引（A，B，C），如果查询条件为A,C那么其实只是用到了A
- 联合索引查询中如果使用了某个列的范围查询，那其右面的所有列都无法使用索引优化。
- hash索引
基于hash表实现，只有精确匹配索引所有列的查询才有效。memory引擎默认的索引类型。
hash索引的限制
- hash索引只包含hash值和指针行，不存储字段值，所以不能使用索引中的值避免读取行。
- 无法用于排序
- 不支持部分索引列查询，因为存的是hash值，而不是数据
- 只支持等值比较查询，不支持范围查询，b-tree是支持的
- 除非有很多hash冲突，否则查询是非常快的
- 如果hash冲突很多的话，索引维护代价是很高的
innoDB有一种特殊的功能，当他发现有索引值被经常使用的时候，可以在b-tree索引的基础上创建一个hash索引。
- 空间索引（R-TREE）
与B-tree索引相比，这类索引适合所有维度的查询，而不只限于前缀查询
- 全文索引
- 其他第三方索引
2，高性能索引策略
- 独立的列
在写sql的时候，索引列不能使表达式的一部分，例如索引列为A ，那么where A+1=2 或者 to_days(A) <= 10 这两种写法索引 是无效的
- 前缀索引和索引选择性
有时候索引列很长，这就导致维护索引的开销会很大，这个时候不妨取索引列的前面的部分索引索引，而不是整个列的内容。那么如何选择索引列前缀的长度呢？这就需要引入一个概念，选择性=不重复的索引值的数量（基数）/表的记录总数。索引的选择性越高则查询的效率越高，当选择性趋于1的时候，就可以截取了。
- 多列索引
- 选择合适的索引列顺序
通常情况下是将选择性高的索引列放在前面
- 聚簇索引
聚簇索引就是存储此索引的节点上同时也存储了这行除了索引的其他数据。innodb就是这个样子，表的主键都是聚簇索引
- 覆盖索引
如果一个索引包含所有需要查询字段的值，我们就称之为覆盖索引。如果使用了覆盖索引，使用explain分析sql的时候extra显示项为Using index。例如表table有一个多列索引（a，b）,那么select a,b from table 就是用的覆盖索引
- 使用索引扫描来做排序
mysql可以使用同一个索引既做排序又用于查找行，因此设计索引时候尽可能满足这两个条件；只有当索引列顺序和order by字句得到顺序完全一致，并且所有列的排序方向都一样的时候，mysql才能够使用索引对结果进行排序。如果查询关联多张表，只有当order by子句引用的字段为第一个表的时候，才可以使用索引做排序。除了上面的条件外还有一个方法可以使用索引做排序，那就是索引的最左列赋值了常量的时候，order by子句就不需要将所有索引列全部顺序展示了。例如：
表table有多列索引（a,b,c）,那么 select * from table where a='1' order by b,c这种也是可以用为索引列做排序的
- 压缩（前缀压缩）索引
myisam就是使用的这个技术，例如索引块第一个值“perform”，第二个值“performqsfs”，压缩完之后为"7,qsfs"
- 尽量避免创建冗余索引，尽可能扩展（建立联合索引），而不是新增
- 不要创建不使用的索引
- 索引和锁
索引可以让查询锁定更少的行，innoDB的二级索引使用共享锁，主键索引使用排他锁。
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
