建网站 企汇网,公司制作网站需要什么知识,重庆可作为推广的网站,手机 网站模板MySQL索引-高性能的索引策略 3 高性能的索引策略3.1 独立的列3.2 前缀索引和索引选择性3.3 多列索引3.4 选择合适的索引列顺序3.5 聚簇索引(Clustered Indexes)3.5.1 InnoDB和MyISAM的数据布局的比较3.5.2 按primary key的顺序插入行(InnoDB) 3.6 覆盖索引(Covering Indexes)3.… MySQL索引-高性能的索引策略 3 高性能的索引策略3.1 独立的列3.2 前缀索引和索引选择性3.3 多列索引3.4 选择合适的索引列顺序3.5 聚簇索引(Clustered Indexes)3.5.1 InnoDB和MyISAM的数据布局的比较3.5.2 按primary key的顺序插入行(InnoDB) 3.6 覆盖索引(Covering Indexes)3.7 利用索引进行排序3.8 索引与加锁 3 高性能的索引策略
3.1 独立的列 独立的列是指索引列不能是表达式的一部分也不是是函数的参数。例如以下两个查询无法使用索引 1表达式 select actor_id from sakila.actor where actor_id15;
2函数参数select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)10;3.2 前缀索引和索引选择性 通常可以索引开始的部分字符这样可以大大节约索引空间从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指不重复的索引值基数和数据表中的记录总数#T的比值范围从1/#T之间。 索引的选择性越高则查询效率越高因为选择性高的索引可以让MYSQL在查找时过滤掉更多的行。 唯一索引的选择性是1这是最好的索引选择性性能也是最好的。 一般情况下某个前缀的选择性也是足够高的足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列必须使用前缀索引因为MYSQL不允许索引这些列的完整长度。 决窍在于要选择足够长的前缀以保证较高的选择性同时又不能太长以便节约空间。前缀应该足够长以使得前缀索引的选择性接近于索引整个列。换句话说前缀的“基数”应该接近于完整列的“基数”。 为了决定前缀的合适长度需要找到最常见的值的列表然后和最常见的前缀列表进行比较。例如以下查询 select count(*) as cnt,city from sakila.city_demo group by city order by cnt desc limit 10;
select count(*) as cnt,left(city,7) as perf from sakila.city_demo group by city order by cnt desc limit 10;直到这个前缀的选择性接近完整列的选择性。 计算合适的前缀长度的另一个方法就是计算完整列的选择性并使前缀的选择性接近于完整列的选择性如下 select count(distinct city)/count(*) from sakila.city_demo;
select count(distinct left(city,7))/count(*) from sakila.city_demo;前缀索引是一种能使索引更小、更快的有效办法但另一方面也有其缺点MYSQL无法使用前缀索引做order by和group by也无法使用前缀索引做覆盖扫描。 3.3 多列索引 一个多列索引与多个列索引MYSQL在解析执行上是不一样的如果在explain中看到有索引合并应该好好检查一下查询的表和结构是不是已经最优。 3.4 选择合适的索引列顺序 对于如何选择索引的顺序有一个经验法则将选择性最高的列放在索引最前列。 当不需要考虑排序和分组时将选择性最高的列放在前面通常是最好的。然后性能不只是依赖于所有索引列的选择性整体基数也和查询条件的具体值有关也就是和值的分布有关。 这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序让这种情况下索引的选择性最高。 使用经验法则要注意不要假设平均情况下的性能也能代表特殊情况下的性能特殊情况可能会摧毁整个应用的性能当使用前缀索引时在某些条件值的基数比正常值高的时候。 3.5 聚簇索引(Clustered Indexes) 聚簇索引保证关键字的值相近的元组存储的物理位置也相同所以字符串类型不宜建立聚簇索引特别是随机字符串会使得系统进行大量的移动操作且一个表只能有一个聚簇索引。 因为由存储引擎实现索引所以并不是所有的引擎都支持聚簇索引。目前只有solidDB和InnoDB支持。 聚簇索引的结构大致如下 叶子页包含了行的全部数据但是节点页只包含了索引列。
二级索引叶子节点保存的不是指行的物理位置的指针而是行的主键值。
这意味着通过二级索引查找行存储引擎需要找到二级索引的叶子节点获取对应的主键值然后根据这个值去聚簇索引中查找到对应的行。
这里做了重复的工作两次BTREE查找而不是一次。注叶子页面包含完整的元组而内节点页面仅包含索引的列(索引的列为整型)。一些DBMS允许用户指定聚簇索引但是MySQL的存储引擎到目前为止都不支持。 InnoDB对主键建立聚簇索引。如果你不指定主键InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引InnoDB会定义一个隐藏的主键然后对其建立聚簇索引。一般来说DBMS都会以聚簇索引的形式来存储实际的数据它是其它二级索引的基础。 3.5.1 InnoDB和MyISAM的数据布局的比较 为了更加理解聚簇索引和非聚簇索引或者primary索引和second索引(MyISAM不支持聚簇索引)来比较一下InnoDB和MyISAM的数据布局对于如下表 CREATE TABLE layout_test (col1 int NOT NULL,col2 int NOT NULL,PRIMARY KEY(col1),KEY(col2)
);假设主键的值位于1—10,000之间且按随机顺序插入然后用OPTIMIZE TABLE进行优化。col2随机赋予1—100之间的值所以会存在许多重复的值。
(1) MyISAM的数据布局 其布局十分简单MyISAM按照插入的顺序在磁盘上存储数据如下 注左边为行号(row number)从0开始。因为元组的大小固定所以MyISAM可以很容易的从表的开始位置找到某一字节的位置。 MyISAM建立的primary key的索引结构大致如下
注MyISAM不支持聚簇索引索引中每一个叶子节点仅仅包含行号(row number)且叶子节点按照col1的顺序存储。 来看看col2的索引结构 实际上在MyISAM中primary key和其它索引没有什么区别。Primary key仅仅只是一个叫做PRIMARY的唯一非空的索引而已叶子节点按照col2的顺序存储。
(2) InnoDB的数据布局 InnoDB按聚簇索引的形式存储数据所以它的数据布局有着很大的不同。它存储表的结构大致如下 注聚簇索引中的每个叶子节点包含primary key的值事务ID和回滚指针(rollback pointer)——用于事务和MVCC和余下的列(如col2)。
相对于MyISAMInnoDB的二级索引与聚簇索引有很大的不同。InnoDB的二级索引的叶子包含primary key的值而不是行指针(row pointers)这样的策略减小了移动数据或者数据页面分裂时维护二级索引的开销因为InnoDB不需要更新索引的行指针。其结构大致如下 聚簇索引和非聚簇索引表的对比 3.5.2 按primary key的顺序插入行(InnoDB) 如果你用InnoDB而且不需要特殊的聚簇索引一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据。最简单的做法就是使用一个AUTO_INCREMENT的列这会保证记录按照顺序插入而且能提高使用primary key进行连接的查询的性能。应该尽量避免随机的聚簇主键例如字符串主键就是一个不好的选择它使得插入操作变得随机。 3.6 覆盖索引(Covering Indexes) 覆盖索引是一种非常强大的工具能大大提高查询性能。设计优秀的索引应该考虑到整个查询而不单单的where条件部分。 索引确实是一种查找数据的高效方式但是MYSQL也可以使用索引来直接获取列的数据这样就不再需要读取数据行。索引的叶子节点中已经包含要查询的数据那么就没有必要再回表查询了如果索引包含满足查询的所有数据就称为覆盖索引。 只需要读取索引而不用读取数据有以下一些优点 (1)索引项通常比记录要小所以MySQL访问更少的数据
(2)索引都按值的大小顺序存储相对于随机访问记录需要更少的I/O
(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
(4)覆盖索引对于InnoDB表尤其有用因为InnoDB使用聚集索引组织数据如果二级索引中包含查询所需的数据就不再需要在聚集索引中查找了。覆盖索引不能是任何索引只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。 对于索引覆盖查询(index-covered query)使用EXPLAIN时可以在Extra一列中看到“Using index”。例如在sakila的inventory表中有一个组合索引(store_id,film_id)对于只需要访问这两列的查询MySQL就可以使用索引如下 mysql EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: inventorytype: index
possible_keys: NULLkey: idx_store_id_film_idkey_len: 3ref: NULLrows: 5007Extra: Using index
1 row in set (0.17 sec)在大多数引擎中只有当查询语句所访问的列是索引的一部分时索引才会覆盖。但是InnoDB不限于此InnoDB的二级索引在叶子节点中存储了primary key的值。因此sakila.actor表使用InnoDB而且对于是last_name上有索引所以索引能覆盖那些访问actor_id的查询如 同时查询actor_id[主键]与last_name[索引字段]
mysql EXPLAIN SELECT actor_id, last_name- FROM sakila.actor WHERE last_name HOPPER\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: actortype: ref
possible_keys: idx_actor_last_namekey: idx_actor_last_namekey_len: 137ref: constrows: 2Extra: Using where; Using index3.7 利用索引进行排序 MySQL中有两种方式生成有序结果集一是使用filesort二是按索引顺序扫描。 如果explain出来的type列的值为“index”则说明MYSQL使用了索引扫描来做排序。利用索引进行排序操作是非常快的因为只需要从一条索引记录移动到紧接着的下一条记录。 但如果索引不能覆盖查询所需的全部列那就不得不每扫描一条索引记录就回表查询一次对应的行这基本上都是随机IO因此按索引顺序读取的速度通常要比顺序地全表扫描慢尤其是在IO密集型的工作负载时。 而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时可以使用索引来排序。如果查询是连接多个表仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort文件排序。 create table actor(
actor_id int unsigned NOT NULL AUTO_INCREMENT,
name varchar(16) NOT NULL DEFAULT ,
password varchar(16) NOT NULL DEFAULT ,
PRIMARY KEY(actor_id),KEY (name)
) ENGINEInnoDB
insert into actor(name,password) values(cat01,1234567);
insert into actor(name,password) values(cat02,1234567);
insert into actor(name,password) values(ddddd,1234567);
insert into actor(name,password) values(aaaaa,1234567);mysql explain select actor_id from actor order by actor_id \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: actortype: index
possible_keys: NULLkey: PRIMARYkey_len: 4ref: NULLrows: 4Extra: Using index
1 row in set (0.00 sec)mysql explain select actor_id from actor order by password \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: actortype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 4Extra: Using filesort
1 row in set (0.00 sec)mysql explain select actor_id from actor order by name \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: actortype: index
possible_keys: NULLkey: namekey_len: 18ref: NULLrows: 4Extra: Using index
1 row in set (0.00 sec)当MySQL不能使用索引进行排序时就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序如果内存装载不下它会将磁盘上的数据进行分块再对各个数据块进行排序然后将各个块合并成有序的结果集实际上就是外排序使用临时表。 对于filesortMySQL有两种排序算法。 (1)两次扫描算法(Two passes) 实现方式是先将需要排序的字段和可以直接定位到相关行数据的指针信息取出然后在设定的内存通过参数sort_buffer_size设定中进行排序完成排序之后再次通过行指针信息取出所需的Columns。 注该算法是4.1之前采用的算法它需要两次访问数据尤其是第二次读取操作会导致大量的随机I/O操作。另一方面内存开销较小。 (2)一次扫描算法(single pass) 该算法一次性将所需的Columns全部取出在内存中排序后直接将结果输出。 注从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数效率较高但是内存开销也较大。如果我们将并不需要的Columns也取出来就会极大地浪费排序过程所需要的内存。 在 MySQL 4.1 之后的版本中可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。 当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时MySQL 就会选择使用第一种排序算法反之则会选择第二种。为了尽可能地提高排序性能我们自然更希望使用第二种排序算法所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。 当对连接操作进行排序时如果ORDER BY仅仅引用第一个表的列MySQL对该表进行filesort操作然后进行连接处理此时EXPLAIN输出“Using filesort”否则MySQL必须将查询的结果集生成一个临时表在连接完成之后进行filesort操作此时EXPLAIN输出“Using temporary;Using filesort”。 3.8 索引与加锁 索引对于InnoDB非常重要因为它可以让查询锁更少的元组。这点十分重要因为MySQL 5.0中InnoDB直到事务提交时才会解锁。 有两个方面的原因首先即使InnoDB行级锁的开销非常高效内存开销也较小但不管怎么样还是存在开销。其次对不需要的元组的加锁会增加锁的开销降低并发性。 InnoDB仅对需要访问的元组加锁而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。 一旦索引不允许InnoDB那样做即达不到过滤的目的MySQL服务器只能对InnoDB返回的数据进行WHERE操作此时已经无法避免对那些元组加锁了InnoDB已经锁住那些元组服务器无法解锁了。 来看个例子
create table actor(
actor_id int unsigned NOT NULL AUTO_INCREMENT,
name varchar(16) NOT NULL DEFAULT ,
password varchar(16) NOT NULL DEFAULT ,
PRIMARY KEY(actor_id),KEY (name)
) ENGINEInnoDB
insert into actor(name,password) values(cat01,1234567);
insert into actor(name,password) values(cat02,1234567);
insert into actor(name,password) values(ddddd,1234567);
insert into actor(name,password) values(aaaaa,1234567);
SET AUTOCOMMIT0;
BEGIN;
SELECT actor_id FROM actor WHERE actor_id 4
AND actor_id 1 FOR UPDATE;该查询仅仅返回2---3的数据实际已经对1---3的数据加上排它锁了。InnoDB锁住元组1是因为MySQL的查询计划仅使用索引进行范围查询而没有进行过滤操作WHERE中第二个条件已经无法使用索引了
mysql EXPLAIN SELECT actor_id FROM test.actor- WHERE actor_id 4 AND actor_id 1 FOR UPDATE \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: actortype: index
possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: NULLrows: 4Extra: Using where; Using index
1 row in set (0.00 sec)mysql表明存储引擎从索引的起始处开始获取所有的行直到actor_id4为假服务器无法告诉InnoDB去掉元组1。 为了证明row 1已经被锁住我们另外建一个连接执行如下操作
SET AUTOCOMMIT0;
BEGIN;
SELECT actor_id FROM actor WHERE actor_id 1 FOR UPDATE;该查询会被挂起直到第一个连接的事务提交释放锁时才会执行这种行为对于基于语句的复制(statement-based replication)是必要的。
如上所示当使用索引时InnoDB会锁住它不需要的元组。更糟糕的是如果查询不能使用索引MySQL会进行全表扫描并锁住每一个元组不管是否真正需要。
