mysql高性能索引

索引类型

索引由很多类型,可以为不同场景提供更好的性能,在mysql中索引是存储引擎层而不是服务器层实现的。不同存储引擎的索引工作方式也不一样,也不是所有存储引擎支持所有索引类型,其底层实现也可能不同。

B-Tree索引

使用B-Tree数据结构来存储数据,大多数mysql引擎都支持这种索引,Archive引擎是个例外。
存储引擎以不同的方式使用B-Tree索引,性能也各不同,各有优劣。
例如,MyISAM使用前缀压缩使得索引更小,但InnoDB按照原数据格式进行存储。再如MyISAM索引通过数据的屋里位置引用被索引的行,而InnoDB根据主键引用被索引的行。
B-Tree索引对如下类型的查询有效(假设:key(name,dob)):
1、全值匹配
比如查找姓名为Allen的人,出生于1996年的人
2、匹配最左前缀
比如查找姓名为Allen的人,即只是用索引的第一列
3、匹配列前缀
比如查找所有以J开头的姓的人。这里也只使用了索引的第一列。
4、匹配范围值
比如查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。
5、匹配某一列并范围匹配另一列
比如查找姓名Allen的人,出生1996到2005的人
B-Tree支持“值访问索引的查询”,即查询只需要访问索引,而不需要访问数据行,也叫做“覆盖索引”。
因为索引树中的节点是有序的,所以除了按值查找之外,索引还可以用order by操作。

哈希索引

哈希索引基于哈希表实现,只有精确匹配索引所有的列的查询才有效。
mysql中只有Memory引擎显式的支持哈希索引,也是Memory的默认索引类型,Memory引擎同时也支持B-Tree索引。Memory引擎是支持费唯一哈希索引的,如果多个列的哈希值相同,索引会以链表的方式存放记录指针到同一个哈希条目中。
哈希索引查询的速度非常快,但是哈希索引也有很多限制:
1、哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行。
2、哈希索引数据不是按照索引值顺序存储的,所以也无法用于排序
3、哈希索引不支持部分索引列的匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
4、哈希索引只支持等值比较查询,包括=、IN()、<=>,不支持范围查询。
5、哈希索引的数据非常快,除非有很多哈希冲突,如果冲突很多的时候索引维护操作的代价也高,查找遍历链表代价也大。
因为这些限制,哈希索引只适用于某些特定的场合,而一旦适合则带来的性能提升非常显著。
InnoDB引擎有个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用的非常频繁时,它会在内存中基于B-tree索引之上再创建一个哈希索引,这样就让B-Tree也有哈希索引的一些优点,比如快速的哈希查找,这是个完全自动的内部行为用户无法控制,但可以关闭该功能。
如果存储引擎不支持哈希索引,可以模拟想InnoDB一样创建哈希索引,这样可以享受一些哈希索引的遍历,比如只需要很小的索引就可以为超长的键创建索引,思路很简单:在B-Tree基础上创建一个伪哈希索引。
例如:
select id from url where url=”http://www.mysql.com";
删除原来url列上的所以,新增一个url_rcc列,使用crc32做哈希,使用下面的方式查询:
select id from url where url = “http://www.mysql.com"
and url_rcc = crc32(“http://www.mysql.com");
这样做新能会非常高,因为mysql优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找。这样做需要维护哈希值可以使用触发器或者手动。如果这种方式不要使用sha1()和md5()作为哈希函数,因为这两个函数计算出来的哈希值非常长。

空间数据索引(R-Tree)

MyISAM表支持空间所以呢,可以用作地理数据存储。

全文索引

它是查找文本中的关键词,而不是直接比较索引中的值。

索引的优点

1、大大减少了服务器需要扫描的数据量。
2、可以帮助服务器避免排序和临时表。
3、可以随机I/O变为顺序I/O。

高性能索引策略

独立的列

“独立的列”是指索引列不能使表达式的一部分也不能是函数的参数
例如:select id from aa where id+1=5;
正确写法:select id from aa where id=4;

前缀索引和索引的选择性

有时候需要索引很长的字符列,这会让索引变得大且慢。一个策略是上面提到的通过模拟哈希索引,另一种就是前缀索引。
通常可以索引开始的部分字符,这样可以一打打节约索引空间,提高效率。但这样会降低索引的选择性。索引的选择性是指不重复的索引值和数据表的记录总数的比值,范围。索引的选择性越高则查询效率越高,因为可以过滤掉更多的行。唯一索引的选择性是1,性能是最好的。
创建前缀索引:alter table aa add key(name(7))
mysql前缀索引无法做order by 和group by,也无法使用前缀索引做覆盖扫描。

多列索引

给多个列创建独立的单例索引,多列索引大部分情况下并不能提高mysql的查询性能,mysql5.0之后更是引入一种叫索引合并的策略,一定程度上可以使用表上的多个单列索引来定位指定的行。索引合并策略是一种优化的结果,但实际上更多的说明了表上的索引建的非常糟糕:
1、当出现服务器对多个索引做相交操作(通常有多个and条件),通常以位置需要一个包含所有相关列的多列索引,而不是多个多里的单列索引。
2、当服务器需要对多个索引做联合操作时(通常是多个or条件)。

选择合适的索引列顺序

讲选择性最高的列放到索引最前列。

聚簇索引和非聚簇索引

聚簇索引并不是一个单独的索引类型,而是一种数据存储方式。
B+Tree结构都可以用在MyISAM和InnoDB上。mysql中,不同的存储引擎对索引的实现方式不同。
B+Tree示意图

聚集索引和非聚集索引原理图

聚簇索引

聚簇索引的数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。聚簇索引要比非聚簇索引查询效率高很多。
聚集索引这种主+辅索引的好处是,当发生数据行移动或者页分裂时,辅助索引树不需要更新,因为辅助索引树存储的是主索引的主键关键字,而不是数据具体的物理地址。
InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用”where id = 14”这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。

非聚簇索引

非聚集索引,类似于图书的附录,那个专业术语出现在哪个章节,这些专业术语是有顺序的,但是出现的位置是没有顺序的。每个表只能有一个聚簇索引,因为一个表中的记录只能以一种物理顺序存放。但是,一个表可以有不止一个非聚簇索引。
MyISAM的是非聚簇索引,B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,
这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树

覆盖索引

覆盖索引又可以称为索引覆盖。
解释一: 就是select的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。
解释二: 索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引。
解释三:是非聚集组合索引的一种形式,它包括在查询里的Select、Join和Where子句用到的所有列(即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段,也即,索引包含了查询正在查找的所有数据)
不是所有类型的索引都可以成为覆盖索引,覆盖索引必须要存储索引列的值,而哈希索引、空间索引、全文索引都不存储列的值,所以mysql只能用B-Tree索引做覆盖索引。不同的存储引擎实现覆盖索引的方式也不同,而且也不是所有的存储引擎都支持覆盖索引(memory存储引擎就不支持覆盖索引,不排除未来支持)

覆盖索引的好处

1、索引条目通常远小于数据行大小,减少数据访问和I/O开销。
2、由于InnoDB聚簇索引,覆盖索引对InnoDb表特别有用,InnoDb的二级索引在叶子节点中保存了行的主键值,如果二级主键可以覆盖查询,可以避免对主键索引的二次查询。

使用索引描述来排序

mysql使用两种方式可以生成有序的结果:通过排序操作或者按索引顺序扫描。如果explain出来的type列值为“index”,则说明mysql使用了索引扫描来做排序。
mysql可以使用同一个索引既满足排序有用于查找,如果可能,设计索引时应该尽可能地同时满足这两种任务。
只有当索引的列顺序和order by 子句的顺序完全一致,并且所有列的排序方向(要么都是desc,要么都是asc)都一样时,mysql才能使用索引来对结果做排序。如果查询多张关联表,则只有当order by 子句引用的字段全部为第一个表时,才能使用做排序。
有一种情况下order by子句可以不满足索引的最左前缀的要求,就是前导列为常量的时候。如果where子句或者join子句中这些列制定了常量就可以弥补索引的不足。
表指定索引 key(a,b,c)例子:
where a=1 order by b,c;
where a=1 order by b;
where a>1 order by a,b;
一下是错误的例子:
where a =1 order by b desc,c asc;
where a =1 order by c;
where a>1 order by b,c;
where a =1 and b in(1,2) order by c;

冗余和重复索引

mysql允许在相同的列上创建多个索引。mysql需要单独维护重复的索引,这会影响性能。应该避免创建重复索引,发现以后应该立即移除。
下面的代码创建了三个重复的索引:
create table text(id int not null PRIMARY KEY,
UNIQUE(ID),INDEX(ID)
)ENGIN=InnoDB;
冗余索引和重复索引有些不同,如果创建了索引(A,B),在创建索引(A)就是冗余索引,因为这只是前一个索引的前缀索引。如果在创建(B,A),则不是冗余索引,(B)也不是。冗余索引通常只是对B-tree索引来说的。
大多数情况不需要冗余索引,但也有出于性能考虑需要冗余索引,因为扩展已有的索引会导致变得太大,从而影响其他使用该索引的查询的性能。
表中的索引越多插入速度回越慢,一般来说,增加新索引将会导致insert、update、delete等操作的速度变慢,特别是新增索引达到了内存瓶颈的时候。

未使用的索引

有些服务器永远用不到的索引。建议考虑删除。
怎样检测未使用的索引请google或者百度。

索引和锁

索引可以让查询锁定更少的行。
InnoDB只有在访问行的时候才会对其加锁,而索引能减少InnoDB访问的行数,从而减少锁的数量。

MySQL索引失效的几种情况

索引不存储null值

更准确的说,单列索引不存储null值,复合索引不存储全为null的值。索引不能存储Null,所以对这列采用is null条件时,因为索引上根本
没Null值,不能利用到索引,只能全表扫描。
为什么索引列不能存Null值?
将索引列值进行建树,其中必然涉及到诸多的比较操作。Null值的特殊性就在于参与的运算大多取值为null。
这样的话,null值实际上是不能参与进建索引的过程。也就是说,null值不会像其他取值一样出现在索引树的叶子节点上。

不适合键值较少的列(重复数据较多的列)

如果某个数据列里包含着许多重复的值,就算为它建立了索引也不会有很好的效果。比如说,如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值,就没有必要为它创建一个索引。

前导模糊查询不能利用索引(like ‘%XX’或者like ‘%XX%’)

假如有这样一列code的值为’AAA’,’AAB’,’BAA’,’BAB’ ,如果where code like ‘%AB’条件,由于前面是模糊的,所以不能利用索引的顺序,必须一个个去找,看是否满足条件。这样会导致全索引扫描或者全表扫描。如果是这样的条件where code like ‘A % ‘,就可以查找CODE中A开头的CODE的位置,当碰到B开头的数据时,就可以停止查找了,因为后面的数据一定不满足要求。这样就可以利用索引了。

in 失效的情况

eq_range_index_dive_limit这个参数影响in是否使用索引, 5.6 默认值是10 5.7 默认值是 200

mysql自动优化

如果mysql估计使用全表扫描要比使用索引快,则不使用索引。如果需要使用索引可以强制使用索引。
mysql强制使用索引:force index(索引名或者主键PRI)
例如:
select * from table force index(PRI) limit 2;(强制使用主键)
select * from table force index(ziduan1_index) limit 2;(强制使用索引”ziduan1_index”)
select * from table force index(PRI,ziduan1_index) limit 2;(强制使用索引”PRI和ziduan1_index”)
mysql禁止某个索引:ignore index(索引名或者主键PRI)
例如:
select * from table ignore index(PRI) limit 2;(禁止使用主键)
select * from table ignore index(ziduan1_index) limit 2;(禁止使用索引”ziduan1_index”)
select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引”PRI,ziduan1_index”)

隐式转换

索引字段类型使用
explain extended select uid from user where mo = 123445 limit 0,1
原因:索引隐式转换,mo是varchar类型,这里使用的是数字,索引使用无效(数据类型以及字符集定义不当导致)
在JOIN操作中(需要从多个数据表提取数据时),MYSQL只有在主键和外键的数据类型相同时才能使用索引,否则即使建立了索引也不会使用

索引列不能参与计算

保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

Explain优化查询检测

禁止缓存

在测试sql语句性能时有时需要禁用缓存, 下面是几种不同的实现方式, 供参考使用:

  1. 通过sql的select语句中添加SQL_NO_CACHE修饰来禁用查询缓存
    SELECT SQL_NO_CACHE * FROM TABLE_NAME
  2. 通过set 变量来实现禁用缓存
    SET SESSION query_cache_type=0;
  3. 通过reset指令来重置缓存
    RESET QUERY CACHE
    以上三种方法都可以达到测试mysql性能时清缓存的目的。

    MySQL执行计划调用方式

  4. EXPLAIN SELECT ……
    变体:
  5. EXPLAIN EXTENDED SELECT ……
    将执行计划”反编译”成SELECT语句,运行SHOW WARNINGS
    可得到被MySQL优化器优化后的查询语句
  6. EXPLAIN PARTITIONS SELECT ……
    用于分区表的EXPLAIN生成QEP的信息

    关于explain选项

    下面是一个实例:
    1
    2
    3
    4
    5
    6
    mysql> explain select products_id from products limit 1;
    +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table  | type | possible_keys | key   | key_len | ref | rows | Extra    |
    +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE   | products | index | NULL     | PRIMARY | 4    | NULL | 3113 | Using index |
    +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+

    id

    MySQL Query Optimizer选定的执行计划中查询的序列号。
    表示查询中执行select子句或操作表的顺序,id值越大优先级越高,越先被执行。id相同,执行顺序由上至下

    select_type

    1、SIMPLE:简单的select查询,不使用union及子查询
    2、PRIMARY:最外层的select查询
    3、UNION:UNION中的第二个或随后的select查询,不依赖于外部查询的结果集
    4、DEPENDENT UNION:UNION中的第二个或随后的select查询,依赖于外部查询的结果集
    5、UNION RESULT: UNION查询的结果集SUBQUERY子查询中的第一个select查询,不依赖于外部查询的结果集
    6、DEPENDENT SUBQUERY:子查询中的第一个select查询,依赖于外部查询的结果集DERIVED用于from子句里有子查询的情况。
    MySQL会递归执行这些子查询,把结果放在临时表里。
    7、UNCACHEABLE SUBQUERY:结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估
    8、UNCACHEABLE UNION:UNION中的第二个或随后的select查询,属于不可缓存的子查询

    table

    1、system:表仅有一行(系统表)。这是const连接类型的一个特例。
    2、const:const用于用常数值比较PRIMARY KEY时。当查询的表仅有一行时,使用system。
    3、eq_ref:除const类型外最好的可能实现的连接类型。它用在一个索引的所有部分被连接使用并且索引是UNIQUE或PRIMARY KEY,
    对于每个索引键,表中只有一条记录与之匹配。
    4、ref:连接不能基于关键字选择单个行,可能查找到多个符合条件的行。叫做ref是因为索引要跟某个参考值相比较。
    这个参考值或者是一个常数,或者是来自一个表里的多表查询的结果值。
    5、ref_or_null:如同ref,但是MySQL必须在初次查找的结果里找出null条目,然后进行二次查找。
    6、index_merge:说明索引合并优化被使用了。
    7、unique_subquery:在某些IN查询中使用此种类型,而不是常规的ref:
    value IN (SELECT primary_key FROM single_table WHERE some_expr)
    index_subquery在某些IN查询中使用此种类型,与unique_subquery类似,但是查询的是非唯一性索引:
    value IN (SELECT key_column FROM single_table WHERE some_expr)
    8、range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。
    当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range。
    9、index:全表扫描,只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序,但是开销仍然非常大。
    10、all:最坏的情况,从头到尾全表扫描

    type

    表示MySQL在表中找到所需行的方式,又称“访问类型”,常见类型如下:
    ALL, index, range, ref, eq_ref, const, system, NULL
    从左到右,性能从最差到最好
    a. ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行
    b. index:Full Index Scan,index与ALL区别为index类型只遍历索引树
    c. range:索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时,例如IN()和OR列表,也会显示range(范围扫描),当然性能上面是有差异的。
    d. ref:使用非唯一索引扫描或者唯一索引的前缀扫描,返回匹配某个单独值的记录行
    e. eq_ref:类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
    f. const、system:当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量
    g. NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

    possible_keys

    指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用,如果为空,说明没有可用的索引

    key

    显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
    mysql实际从possible_key选择使用的索引。如果为null,则没有使用索引。很少的情况下,mysql会选择优化不足的索引。这种情况下,可以在select语句中使用use index(indexname)来强制使用一个索引或者用ignore index(indexname)来强制mysql忽略索引

    key_len

    表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)
    在不损失精确性的情况下,长度越短越好

    ref

    表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

    rows

    表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数

    extra

    1、Distinct
    一旦mysql找到了与行相联合匹配的行,就不再搜索了。
    2、Not exists
    mysql 优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了。
    3、Range checked for each
    Record(index map:#)没有找到理想的索引,因此对于从前面表中来的每一个行组合,mysql检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一。
    4、Using filesort
    表示MySQL会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL中无法利用索引完成的排序操作称为“文件排序”。
    5、Using index
    该值表示相应的select操作中使用了覆盖索引(Covering Index),列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候。
    6、Using temporary
    mysql需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上。
    表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询
    这个值表示使用了内部临时(基于内存的)表。一个查询可能用到多个临时表。有很多原因都会导致MySQL在执行查询期间创建临时表。两个常见的原因是在来自不同表的上使用了DISTINCT,或者使用了不同的ORDER BY和GROUP BY列。可以强制指定一个临时表使用基于磁盘的MyISAM存储引擎。这样做的原因主要有两个:

1)内部临时表占用的空间超过min(tmp_table_size,max_heap_table_size)系统变量的限制
2)使用了TEXT/BLOB 列
7、Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题。。许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带where字句的查询都会显示”Using where”。有时”Using where”的出现就是一个暗示:查询可受益与不同的索引。
8、Using join buffer
改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。
9、Impossible where
这个值强调了where语句会导致没有符合条件的行。
10、 Select tables optimized away
这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行.
11、Index merges
当MySQL 决定要在一个给定的表上使用超过一个索引的时候,就会出现以下格式中的一个,详细说明使用的索引以及合并的类型。
Using sort_union(…)
Using union(…)
Using intersect(…)

总结:

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的,并非精确值
• EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划。
参考
(1)《高性能MySQL》
(2)《数据结构与算法分析》

开发者首页 wechat
欢迎您扫一扫上面的微信公众号