《高性能MySQL(第3版)》摘要——索引篇

简介

　　索引是存储引擎用于快速找到记录的一种数据结构。

　　索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。当数据量较少且负载较低时，不恰当的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。

　　索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级，“最优”的索引有时比一个“好的”索引性能要好两个数量级。创建一个真正“最优”的索引经常需要重写查询。

一、索引基础

　　MySQL的索引类似于书籍对应了页码的目录。

　　索引可以包含一个或多个列的值。如果索引包含多个列，name列的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引的大不相同的。

索引的类型

1.B-Tree索引

　　当人们讨论索引的时候如果没有特别指明类型，那么多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。

　　B-Tree对索引列是顺序组织存储的，索引很适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像“找出所有以I到K开头的名字”这样的查找效率是会常高。

假如有如下数据表：

　　CREATE TABLE people(

　　　　last_name　varchar(50)   not null,

　　　　first_naem　varchar(50)   not null,

　　　　dob　　　　date　　　　not null,

　　　　gender 　　 enum('m','f')  not null,

　　　　key(last_name, first_naem, dob)

　　);

对于表中的每一行数据，索引中包含了last_name, first_name和dob列的值。

B-Tree索引适合于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

WHERE条件中查询条件可任意顺序，MySQl的查询优化器会优化到匹配最佳索引。

前面所述的索引对如下类型的查询有效。

全值匹配：

　　select * from people where last_name = 'Allen' and first_name = 'Cuba' and dob = '1960-01-01';

　　全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。

匹配最左前缀：

　　select * from people where last_name = 'Allen';

　　前面提到的索引可用于查找所有姓为Allen的人，即只使用索引的第一列。

匹配列前缀：

　　select * from people where last_name like 'J%';

　　也可以只匹配某一列的值得开头部分。例如前面提到的索引可用于查找所有以J开头的姓的人。这里也只用了索引的第一列。

匹配范围值：

　　select * from people where last_name between 'Allen' and 'Barrymore';

　　例如前面提到的索引可用于查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。

精确匹配某一列并范围匹配另外一列：

　　select * from people where last_name = 'Allen' and first_name like 'K%';

　　前面提到的索引也可用于查找所有姓为Allen，并且名字是字母K开头的人。即第一列last_name全匹配，第二列first_name范围匹配。

只访问索引的查询：

　　select last_name, first_name from people where last_name = 'Allen' and first_name = 'Cube';

　　B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行（覆盖索引）。

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作（按顺序查找）。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY字句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

下面是一些关于B-Trss索引的限制：

如果不是按照索引的最左列开始查找，则无法使用索引。

　　select * from people where first_name = 'Bill';

　　select * from people where dob = 'xxxx-xx-xx';

　　select * from people where last_name like '%X';

　　例如上面例子中的索引无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。

不能跳过索引中的列。

　　select * from people where last_name = 'Smith' and dob = 'xxxx-xx-xx';（只能使用索引的第一列）

　　也就是说，前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定名（first_name），则MySQL只能使用索引的第一列。

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

　　select * from people where last_name='Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23';

　　这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件。如果范围查询列值得数量有限，那么可以通过使用多个等于条件来代替范围条件。

到这里读者应该可以明白，前面提到的索引列的顺序是多么重要：这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

2.哈希索引

3.空间数据索引（R-Tree）

4.全文索引

5.其他索引类别

二、索引的优点

1.索引大大减少了服务器需要扫描的数据量。

2.索引可以帮助服务器避免排序和临时表。

3.索引可以将随机I/O变为顺序I/O。

索引是最好的解决方案吗？

索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录匹配。例如可以使用分区技术。如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到的某些特性。对于TB级别的数据，定位单条记录的意义不大，所以经常会使用块级别元数据技术来代替索引。

三、高性能的索引策略

1.独立的列

　　“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

　　反例：mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

　　　　　mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(data_col) <= 10;

　　我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。

2.前缀索引和索引选择性

　　字符列太长会让索引变得大且慢，通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。

　　但这样会降低索引的选择性。索引的选择性是指，不重复的索引值和数据表的记录总数的比值。

　　索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

　　一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。

　　对于BLOB、TEXT或者更长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

　　诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。

　　创建前缀索引：mysql> ALTER TABLE sakila.city ADD KEY(city(7));

3.多列索引

　　一个常见的错误就是，为每个列创建独立的索引，后者按照错误的顺序创建多列索引。

4.选择合适的索引列顺序

　　对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。但一定要记住别忘了WHERE字句中的排序、分组和范围条件等其他因素，这些因素可能对查询性能造成非常大的影响。

5.聚簇索引

6.覆盖索引

　　如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

7.使用索引扫描来做排序

　　只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，而无法利用索引排序。

　　例如，Sakila示例数据库的表rental在列（retal_date, inventory_id,customer_id）上有名为rental_date的索引。

　　MySQL可以使用rental_date索引为下面的查询做排序，EXPLAIN中不会出现文件排序（firesort）操作；

　　mysql> EXPLAIN SELECT rental_id, staff_id FROM rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id\G;

　　即使ORDER BY子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为索引的第一列被指定为一个常数。

　　mysql> ... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;

　　第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀。

　　mysql> ... WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id;

　　上面这个查询也没问题，ORDER BY使用的两列就是索引的最左前缀。

反例，下面是一些不能使用索引做排序的查询：

• 下面这个查询使用了两张不同的排序方向，但是索引列都是正序排序的：

　　　　... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;

• 下面这个查询的ORDER BY子句中引用了一个不在索引的列：

　　　　... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;

• 下面这个查询的WHERE和ORDER BY中的列无法组合成索引的最左前缀：

　　　　... WHERE rental_date = '2005-05-25' ORDER BY customer_id;

• 下面这个查询在索引列的第一列上是范围条件，所以MySQL无法使用索引的其余列：

　　　　... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;

• 这个查询在inventory_id列上有多个等于条件。对于排序来说，这也是一种范围查询：

　　　　... WHERE rental_date = '2005-05-25' AND inventory_id IN (1, 2) ORDER BY customer_id;

8.压缩（前缀压缩）索引

9.冗余和重复索引

　　重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

　　如果创建了索引（A,B），再创建（A）就是冗余索引，因为这只是前一个索引的前缀索引。因此前一个也可以当做后一个索引来使用。如果再创建（B,A）、（B），则不是冗余索引。

　　冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引（A,B）而不是扩展已有的索引（A），还有一种情况是将一个索引扩展为（A,ID），其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的。

　　尽量扩展已有的索引而不是创建新索引。但有时候处于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。例如，如果再整数列上有一个索引，现在需要额外增加一个很长的VARCHAR列来扩展该索引，那性能可能会急剧下降。

10.未使用的索引

11.索引和锁

四、索引案例学习

　　假设要设计一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色，等等。网站必须支持上面这些特征的各种组合来搜索用户，还必须允许根据用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。如何设计索引满足上面的复杂需求呢？

1.支持多种过滤条件

　　将大部分查询中都会用到的列作为组合索引的前缀，即使查询没有使用到该列，那么可以通过在查询条件中新增IN()列表(多个等值条件查询)查询条件来让MySQL选择该索引。这种做法在该列的选择性不高的时候非常有效，但如果列有太多不同的值，就会让IN()列表太长，这样做就不行了。

2.避免多个范围条件

　　

　假设我们有一个last_online列并希望通过下面的查询显示在过去几周上线过的用户：

　WHERE eye_color  IN ('brown', 'blue', 'hezel')

　　AND   hair_color  IN ('black', 'red', 'blonde', 'brown')

　　AND   sex            IN ('M', 'F')

　　ADN   last_online > DATE_SUB(NOW(), INTERVAL 7 DAY)

　　ADN   age            BETWEEN 18 ADN 25

这个查询有一个问题：他有两个范围条件，last_online列和age列，MySQL可以使用last_online列索引或者age列索引，但无法同时使用它们。

方法一：将age字段转换为一个IN()的列表；

方法二：实现计算好一个active列，由定时任务来维护，每当用户登录时，将对应值设置为1，并且将过去连续七天未曾登录的用户的值设置为0；

3.优化排序

　　使用文件排序对小数据集是很快的，但如果一个查询匹配的结果有上百万行的话会怎么样？例如如果WHERE子句只有sex列，如何排序；对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。例如，可以创建（sex，rating）索引用于下面的查询：

　　mysql> SELECT <cols> FROM profiles WHERE sex = 'M' ORDER BY rating LIMIT 10;

这个查询使用了ORDER BY和LIMIT，如果没有索引的话会很慢。

即使有索引，如果用户界面上需要翻页，而且翻页翻到比较靠后时查询也可能非常慢。

　　mysql> SELECT <cols> FROM profiles WHERE sex = 'M' ORDER BY rating LIMIT 1000000 10;

无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。

　　优化这类索引的另一个比较好的策略是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获取需要的行。这可以减少MySQL扫描那些需要丢弃的行数。下面这个查询显示了如何高效地使用（sex，rating）索引进行排序和分页：

　　mysql> SELECT <cols> FROM profiles INNER JOIN (SELECT <primary key cols> FROM profiles WHERE x.sex = 'M' ORDER BY rating LIMIT 1000000, 10) AS x USING(<primary key cols>);

五、补充：

1.创建索引SQL：

　　CREATE INDEX index_name ON table_name(col[(length)]...);

　　ALTER TABLE table_name ADD INDEX index_name(col[(length)]...);

2.删除索引SQL：

　　DROP INDEX index_name ON table_name;

　　ALTER TABLE table_name DROP INDEX index_name;

3.走不走索引的总结（如有错误，欢迎指正）：

• NOT IN，<>，LIKE '%...' 不走索引；
• =，IN(多个等值条件查询)，LIKE '(非%开头)...'，BETWEEN ... AND ...(范围条件查询)，EXISTS，NOT EXISTS走索引；
• <，<=，>，>=如果字段是整数类型会走索引；字符类型根据实际查询速度来判断；如果全盘扫描速度比索引速度要快则不走索引；

转载于:https://www.cnblogs.com/NguyenVm/p/10040229.html