mysql优化-索引类型

索引类型

一. B-tree索引

大的方面看都用的平衡树，但具体的实现上，各引擎稍有不同。比如，NDB引擎使用的是T-tree。Mysiam，innodb中默认使用的B-tree索引。B-tree索引常见误区：1. 在where条件常用列上都加上索引    例：where cat_id = 3 and price > 100;//查询第三个栏目，100块以上的商品    误：cat_id和price都加上索引    错：只能用上cat_id或price索引。因为是独立的索引，同时只能用上1个。2.  在多列上建立索引后，查询哪个列，索引都能发挥作用    误：多列索引发挥作用，需要满足左前缀的要求。    以index(a, b, c)为例

语句 索引是否发挥作用 where a=3 是,只使用了a列 where a=3 and b=5 是，使用了ab列 where a=3 and b=5 and c=4 是，使用了abc列 where b=4 / where c=4 否 where a=3 and c=4 a能发挥索引，c不能 where a=3 and b>10 and c=7 a能用伤，b能用上，c不能用上 where a=3 and b like ‘XXX%’ and c=7 a能用上，b能用上，c不能用上

实验：假设某个表有一个联合索引（c1,c2,c3,c4）A where c1=x and c2=x and c4>x and c3=x B where c1=x and c2=x and c4=x order by c3C where c1=x and c4= x group by c3,c2D where c1=x and c5=x order by c2,c3E where c1=x and c2=x and c5=x order by c2,c3   

create table t4 (    c1 tinyint(1) not null default 0,    c2 tinyint(1) not null default 0,    c3 tinyint(1) not null default 0,    c4 tinyint(1) not null default 0,    c5 tinyint(1) not null default 0,    index c1234(c1,c2,c3,c4));insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);A结果分析:c1=x and c2=x and c4>x and c3=x  <==等价==> c1=x and c2=x and c3=x and c4>x。因此，c1,c2,c3,c4都能用上。mysql> explain select * from t4 where c1=1 and c2=2 and c4>3 and c3=3;+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+|  1 | SIMPLE      | t4    | range | c1234         | c1234 | 4       | NULL |    1 | Using where |+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+1 row in setB结果分析:c1,c2索引用上,在c2用到索引的基础上,c3是排好序的,因此不用额外排序，而c4没发挥作用。假如不用c3排序，用c5排序则需要另外排序。mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c3;+----+-------------+-------+------+---------------+-------+---------+-------------+------+-------------+| id | select_type | table | type | possible_keys | key   | key_len | ref         | rows | Extra       |+----+-------------+-------+------+---------------+-------+---------+-------------+------+-------------+|  1 | SIMPLE      | t4    | ref  | c1234         | c1234 | 2       | const,const |    1 | Using where |+----+-------------+-------+------+---------------+-------+---------+-------------+------+-------------+1 row in setmysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c5;+----+-------------+-------+------+---------------+-------+---------+-------------+------+-----------------------------+| id | select_type | table | type | possible_keys | key   | key_len | ref         | rows | Extra                       |+----+-------------+-------+------+---------------+-------+---------+-------------+------+-----------------------------+|  1 | SIMPLE      | t4    | ref  | c1234         | c1234 | 2       | const,const |    1 | Using where; Using filesort |+----+-------------+-------+------+---------------+-------+---------+-------------+------+-----------------------------+1 row in setC结果分析:只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引。如果排序换成c2,c3则能使用得上c2、c3索引，减少排序步骤。mysql> explain select * from t4 where c1=1 and c4=2 group by c3,c2;+----+-------------+-------+------+---------------+-------+---------+-------+------+----------------------------------------------+| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                                        |+----+-------------+-------+------+---------------+-------+---------+-------+------+----------------------------------------------+|  1 | SIMPLE      | t4    | ref  | c1234         | c1234 | 1       | const |    1 | Using where; Using temporary; Using filesort |+----+-------------+-------+------+---------------+-------+---------+-------+------+----------------------------------------------+1 row in setmysql> explain select * from t4 where c1=1 and c4=2 group by c2,c3;+----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra       |+----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+|  1 | SIMPLE      | t4    | ref  | c1234         | c1234 | 1       | const |    1 | Using where |+----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+1 row in setD结果分析: C1确定的基础上,C2是有序的,C2之下C3是有序的,因此C2,C3发挥的排序的作用。因此,没用到filesort。mysql> explain select * from t4 where c1=1 and c5=2 order by c2,c3; +----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra       |+----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+|  1 | SIMPLE      | t4    | ref  | c1234         | c1234 | 1       | const |    1 | Using where |+----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+1 row in setE结果分析:这一句等价与 select * from t4 where c1=1 and c2=3 and c5=2 order by c3; 因为c2的值既是固定的,参与排序时并不考虑。mysql> explain select * from t4 where c1=1 and c2=3 and c5=2 order by c2,c3;+----+-------------+-------+------+---------------+-------+---------+-------------+------+-------------+| id | select_type | table | type | possible_keys | key   | key_len | ref         | rows | Extra       |+----+-------------+-------+------+---------------+-------+---------+-------------+------+-------------+|  1 | SIMPLE      | t4    | ref  | c1234         | c1234 | 2       | const,const |    1 | Using where |+----+-------------+-------+------+---------------+-------+---------+-------------+------+-------------+1 row in set

二. hash索引

hash的理论查询时间复杂度为O(1)既然hash索引查找如此高效，为什么不都使用hash索引？1. hash函数计算后的结果是随机的2. 无法对范围查询进行优化。每个key都要重新计算hash值，得到的结果分散。3. 无法利用前缀索引。比如在B-tree中，field列的值为"helloworld"，并加索引，查询xx="helloworld"或xx="hello"也可以利用上索引。因为hash("helloworld")和hash("hello")，两者得到的hash值仍未随机。4. 排序无法优化5. 必须回行。就是说，通过索引拿到数据位置，必须回到表中取数据。

三. 聚簇索引

主索引文件上直接存放该行的数据，称为聚簇索引。次索引指向对主键的引用。譬如innodb引擎。

注意：
1. 主索引既存储索引值，又在叶子中存储行的数据。
2. 如果没有主键，则会生成unique key做主键。
3. 如果没有unique，则系统生成一个内部的rowid做主键。
4. 优势：根据主键查询条目比较少时，不用回行。
劣势：如果碰到不规则数据插入时，会造成频繁的页分裂。

四. 非聚簇索引

索引与数据分开存放，譬如myisam引擎。主次索引都指向物理行(磁盘位置)