MySQL進階實戰3，mysql索引詳解，上篇

一、索引

索引是存储引擎用于快速查找记录的一种数据结构。我觉得数据库中最重要的知识点，就是索引。

存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根结点开始进行搜索。根结点的槽中存放了指向子结点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值，要么该记录不存在。

叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。B-Tree对索引列是顺序组织存储的，所有很适合查找范围数据。B-Tree适用于全键值、键值范围或键前缀查找。 因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的order by操作。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。

二、索引的优点

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

三、哈希索引

哈希索引是基于哈希表实现的，只有精确匹配索引所有列的查询才会生效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时哈希表中保存指向每个数据行的指针。

因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。

哈希索引的缺点：

1. 哈希索引无法用于排序；
2. 哈希索引不支持最左前缀原则；
3. 哈希索引只支持等值比较，不支持范围查询，比如where salary > 5000；

InnoDB引擎有一个特殊的功能叫“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这就让B-Tree索引也具有哈希索引的一些优点。这是一个完全自动的、内部的行为，用户无法控制或配置。

四、哈希索引实例

如果一个表需要存储大量的URL，并需要根据URL进行搜索查找，如果使用B-Tree来存储URL，存储的内容就会很大，因为URL本身就很长，正常情况下会有如下查询：

select id,name from url where url = "http://www.baidu.com";

若删除原来URL列上的索引，而新增一个被索引的url-src列，使用SRC32做哈希，就可以使用下面的方式查询：

select id,name from url where url = "http://www.baidu.com" and url_crc = CRC32("http://www.baidu.com");

这样做的话，性能会提高很多，通过url_crc列进行哈希查找。

这样实现的缺点是需要维护哈希值，可以手动维护，也可以使用触发器实现。

五、前缀索引

1、独立的列

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数，必须是等号前单独的列存在。

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

通常可以拿字符串开始的部分字符当索引，这样可以大大节约索引空间，从而提高索引效率。索引的选择性越高则查询效率越高，选择性高的索引可以让MySQL在查找时过滤掉更多的行。

一般情况下，对于blob、text、varchar类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。诀窍在于选择足够长的前缀以保证较高的索引选择性，同时又不能太长，最好使得前缀索引的选择性接近于索引整个列。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。

使用MySQL官方数据库提供的表：

CREATE TABLE city (
  city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  city VARCHAR(50) NOT NULL,
  country_id SMALLINT UNSIGNED NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (city_id),
  KEY idx_fk_country_id (country_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;           

用city列测试一下如何使用前缀索引。

3、通过sql获取city全列的索引选择性

select count(distinct city) / count(*) FROM city;

可以看到，结果为 0.9968，说明这一列的值是有重复的。

4、截取city列，获取合适的索引选择性

现在通过截取city列，来测试一下他们的索引选择性，使前缀的选择性接近于完整列的选择性就可以了。

SELECT 
    COUNT(DISTINCT LEFT(city,3))/COUNT(*) AS pref3,
    COUNT(DISTINCT LEFT(city,5))/COUNT(*) AS pref5,
    COUNT(DISTINCT LEFT(city,7))/COUNT(*) AS pref7,
    COUNT(DISTINCT LEFT(city,9))/COUNT(*) AS pref9,
    COUNT(DISTINCT LEFT(city,11))/COUNT(*) AS pref11,
    COUNT(DISTINCT LEFT(city,13))/COUNT(*) AS pref13,
    COUNT(DISTINCT LEFT(city,14))/COUNT(*) AS pref14,
    COUNT(DISTINCT LEFT(city,15))/COUNT(*) AS pref15 
FROM city;           

我觉得截取5个的时候就可以了，这个时候再增加前缀长度，选择性的提升已经不明显了。但是如果数据分布很不均匀，可能就会有问题，

5、通过sql测试一下

select count(1) as cnt,left(city,5) as pref from city group by pref order by cnt desc limit 5

已经找到了合适的前缀长度，下面创建前缀索引：

alter table city add key city_idx(city(5));

前缀索引是一种能使索引更小、更快的方式，但MySQL中无法使用前缀索引做order by 和group by ，也无法使用前缀索引做覆盖扫描。

6、测试一下是否可以触发索引

explain select city_id,city,last_update from city where city = 'dalian'           

触发了city_idx索引。

六、explain查询结果简介

1、id：SELECT识别符。

这是SELECT的查询序列号。

2、select_type：SELECT类型：

• SIMPLE： 简单SELECT(不使用UNION或子查询)；
• PRIMARY： 最外面的SELECT；
• UNION：UNION中的第二个或后面的SELECT语句；
• DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询；
• UNION RESULT：UNION的结果；
• SUBQUERY：子查询中的第一个SELECT；
• DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询；
• DERIVED：导出表的SELECT(FROM子句的子查询)；

3、table：表名

4、type：联接类型

1. system：表仅有一行(=系统表)。这是const联接类型的一个特例；
2. const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时；
3. eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式；
4. ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列；
5. ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化；
6. index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素；
7. unique_subquery：该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高；
8. index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr；
9. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用rang；
10. index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；
11. all：对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

5、possible_keys

possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

6、key

key列显示MySQL实际决定使用的键(索引)。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

7、key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。

8、ref

ref列显示使用哪个列或常数与key一起从表中选择行。

9、rows

rows列显示MySQL认为它执行查询时必须检查的行数。

10、Extra

该列包含MySQL解决查询的详细信息。

1. Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行；
2. Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的行组合在该表内检查更多的行；
3. range checked for each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行；
4. Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行；
5. Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略；
6. Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时；
7. Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误；
8. Using sort_union(...), Using union(...), Using intersect(...)：这些函数说明如何为index_merge联接类型合并索引扫描；
9. Using index for group-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

通过相乘EXPLAIN输出的rows列的所有值，你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时，也用这个乘积来确定执行哪个多表SELECT语句。