mysql索引基本分类与简单优化策略

索引的优点

大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
将随机IO变为顺序IO

BTree索引

存储引擎不同，BTree的具体使用方式实现也不同，MyISAM使用前缀压缩技术存储的很小，InnoDB使用的是B+Tree按照原数据进行存储。MyISAM索引通过数据的物理位置来引用被索引的行。InnoDB则根据主键来引用被索引的行。

BTree对索引列是顺序组织存储的。很适合范围查找数据。索引对于多个值的排序的顺序是按照建表时索引的顺序来排序的

例如，Key(cid,name)

cid	name
1	aa
2	bb
2	cc

就是先按照cid排序，cid相同后再按照name排序。

==可以使用B-Tree索引的查询：==

全值匹配：全值匹配指的是和索引中的所有列都进行匹配。使用cid and name 和 name and cid 是一样的因为mysql优化器会自动优化为 cid and name
最左原则。单独查询某一列只能用cid这一列，会命中，而不能只用name去进行查找。可以用cid这一列进行范围，like查询。
精确查找第一列，范围查询第二列
只访问索引的查询，“覆盖查询”

==不能命中索引的情况：==

如果不是按照第一列进行查找不会去命中索引，例如直接按照name去查找。
如果有3列索引，lastname，firstname,birthday,按照lastname,birthday查找是不会命中索引的。
如果某个列有范围查询，则它右边的都不会命中索引。

哈希索引

哈希索引根据hash表实现，只有精确匹配所有列的查询才能有效。

Hash索引的缺点：

hash索引只包含哈希值和行指针，不存储字段值，不能用索引中的值来避免读取行。
不能用于排序
不支持部分匹配（最左原则），必须全部索引列匹配。
只能用等值查询 = IN() <=>,不支持范围查询 >

InnoDB使用Hash进行快速查询的时候，例如要对列url进行索引，而url字符串过长，不适合索引，这时候可以新建一列url的索引列， crc32(url)之后进行存储，只对这一列加索引，不需要对原url列加索引，查询的时候

select * from db where url = "http://58.com" and url_hash = crc32("http://58.com")

一定要加原url =这个条件，因为可能会产生hash冲突，这样可以解决。

全文索引

TODO

高性能索引策略

独立的列

是指索引列不能是表达式的一部分，eg： select * from db where id+1 =5;
这样是不会命中索引的。

前缀索引和索引的选择性

有时候需要索引很长的字符串，直接进行索引是不可能的，通常可以索引前面几个字符。但是会降低索引选择性。

索引选择性：不重复的索引值/数据记录总数

索引选择性越高，性能越好，唯一索引是1

多列索引

多列索引并不是为每一个where条件都建立一个索引。
例如建立Key（id） Key (name)，在查询语句select * from db where id = 1 or key = "xiaoming";5.0之后的mysql会使他可以命中索引，但是同事扫描两个索引来做合并，这样每个列都建立一个索引性能有时候还不如全表扫描，而且优化器不会将union的操作计入查询时间，问题也不好定位。

合适的索引列顺序

1.将选择性最高的列放在第一个，

聚簇索引

==聚簇索引不是一种单独的索引类型，而是一种存储方式==，具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

==聚簇：表示行和相邻的键值紧凑的存储在一起。无法把数据行存放在两个地方，所以一个表只能有一个聚簇索引==

不是所有的存储引擎都支持聚簇索引，只有Innodb中。使用主键来作为聚簇索引，如果没有主键就使用用一个唯一非空索引代替。

缺点是会让二级索引（非聚簇索引）查找两次，二级索引存储的就是主键值。

聚簇索引的每一个叶子节点包含了主键值，事务ID，用于事务和MVCC的回滚指针以及所有的剩余列。

覆盖索引

使用索引来获取查询的列的数据，如果索引中列的数据已经包含了要返回的值，就不需要再使用主键查一次，也就是回表查询。如果一个索引包含了所有要查询的字段和值就成为覆盖索引

使用索引扫描来做排序

Mysql可以使用同一个索引既满足排序也用于查找行，只有当索引的顺序列顺序和order by子句的顺序完全一致，并且所有列的排序顺序都一样（倒序或者正序）时，才能够使用索引来做排序。

冗余索引和重复索引

如果创建了索引（A，B）再创建索引A就是冗余索引，最左原则可以匹配到。但是如果创建了（A, B）再创建B就不是冗余索引了。

删除从未使用过的索引

执行以下sql查询从未使用过的索引

select distinct
    mysql.innodb_index_stats.table_name,
    mysql.innodb_index_stats.index_name
from
    mysql.innodb_index_stats
where
    concat(mysql.innodb_index_stats.index_name,mysql.innodb_index_stats.table_name) 
    not in (
        select 
            concat(information_schema.statistics.index_name,information_schema.statistics.table_name)
        from
            information_schema.statistics)
        and mysql.innodb_index_stats.index_name <> 'GEN_CLUST_INDEX';

mysql索引基本分类与简单优化策略