in mysql 索引 mysql索引b+

转载

mob64ca1414098d 2024-07-29 19:52:43

文章标签 in mysql 索引数据库数据结构与算法 mysql ci 文章分类 MySQL 数据库

一：索引的类型

二：索引的优点

三：高性能索引策略

四：索引案例

1.1类型介绍

索引有很多类型，可以为不同场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引，即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同

1.2 B+Tree索引

存储引擎以不同的方式使用B+Tree索引，性能也各有不同，各有优劣。

例如MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。

再如MyISAM索引通过数据的物理位置引用被索引行，而InooDB则根据主键引用被索引的行

1.2.1 B+Tree索引数据结构

1.2.2理解数据结构

假如有如下数据表：

CREATE TABLE people(
   last_name         varchar(50)         not null,
   first_name        varchar(50)         not null,
   dob                date                not null,
   gender            eum('m','f')        not null,
   key(last_name, first_name, dob)
)

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

注意：索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。看最后两个条目，两个人的姓和名都一样，则根据他们的出生日期来排列顺序

1.2.3 B+Tree索引对如下类型的查询有效

全值匹配：和索引中的所有列进行匹配，例如前面创建的索引可用于查找姓名为Allen Cuba，出生于1960-01-01的人

匹配最左前缀：前面创建的索引可用于查找所有姓为Allen的人，即只使用索引的第一列

匹配列前缀：前面创建的索引也可以只匹配某一列的值的开头部分，例如可以用于查找所有以J开头的姓的人

匹配范围值：前面创建的索引可用于查找姓在Allen和Barrymore之间的人。（这里只使用了索引的第一列）

精确匹配某一列并范围匹配另外一列：前面创建的索引可用于查找所有姓为Allen，并且名字是字母K开头（比如Kim）的人。即第一列last_name全匹配，第二列first_name范围匹配

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

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

1.2.4 B+Tree索引的限制

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

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

1.2.4.3如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询WHERE last_name='Allen' ANDfirst_name LIKE 'K%' AND dob='1930-07-12'，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件（但是服务器可以把其余列用于其它目的）。如果范围查询列值数量有限，那么可以通过使用多个等于条件来代替范围条件。尽可能将范围查询放到最后,因为范围查询后的列不能使用索引

2.1索引的优点

索引可以让服务器快速的定位到表的指定位置。但是这并不是索引的唯一作用，到目前为止

可以看到，根据创建索引的数据结构不同，索引也有一些其它的附加作用

最常见的B+Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY 和GROUPBY操作。因为数据是有序的，所以B+Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。据此特性，总结下来索引有如下三个优点：

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

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

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

Lahdenmaki和Leach提出，如何评价一个索引是否适合某个查询的“三星系统”：

A.索引将相关的记录放到一起则获得一星；

B.如果索引中的数据顺序和查找中的排序顺序一致则获得两星；

C.如果索引中的列包含了查询中需要的全部列则获得三星；

3.高性能的索引策略

正确的创建索引和使用索引是实现高性能查询的基础。现在讨论一下如何真正的发挥这些索引的优势

3.1独立的索引

通常看到一些查询不当的使用索引，或者使MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数

例1：下面这个查询就无法使用user_id列的索引

mysql> SELECT user_id FROM tb1 WHERE user_id+1 = 5;

很容易看出WHERE中的表达式起始等价于user_id=4，但是MySQL无法自动解析这个方程式，这完全是用户行为。我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧

例2：下面这个查询就无法使用date_col列的索引

mysql> SELECT ... WHERE TO_DAYS(CUURRENT_DATE) - TO_DAYS(date_col) <= 10

解决方法：在程序中获取当前时间，减去10天，加入最后的值存储在last_date_val中

mysql> SELECT ... WHERE date_col <= last_date_val

3.2前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得很慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。

索引选择性是指：不重复的索引值（也称为基数）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高，则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也最好

理解索引选择：每行记录都有一个索引值，如果前缀过少，那么索引值重复的可能性就越大，这样在查询的时候查询到的数据就越多，过滤掉的数据就越少，所以要找一个合理的前缀。

合理前缀的诀窍在于：选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”

3.2.1如何选择合理前缀的长度

mysql> CREATE TABLE tb( 
    -> city VARCHAR(50) NOT NULL 
    -> ); 
Query OK, 0 rows affected (0.26 sec)


mysql> SELECT COUNT(*) AS cnt, city_name FROM city GROUP BY city_name  
       ORDER BY cnt DESC LIMIT 10;

可以看到，上面每个值都出现了5-7次，现在查找到最频繁出现的城市，先从3个前缀字母开始

mysql> SELECT COUNT(*) AS cnt, LEFT(city_name,3) AS pref FROM city  
     GROUP BY pref ORDER BY cnt DESC LIMIT 10;

每个前缀都比原来的城市出现的次数更多，因此唯一前缀比唯一城市要少的多。然后增加前缀的长度，知道这个前缀的选择性接近完整列的选择性。

mysql> SELECT COUNT(*) AS cnt, LEFT(city_name,7) AS pref FROM city 
      GROUP BY pref ORDER BY cnt DESC LIMIT 10;

计算合适前缀的长度的方法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。下面是如何计算完整列的选择性：

mysql> SELECT COUNT(DISTINCT city_name)/COUNT(*) FROM city;

通常来说（尽管也有例外情况），这个例子中如果前缀的选择性能够接近于0.4317，基本上就可用了，可以在一个查询中针对不同前缀长度计算。

显示长度为7的时候，再增加前缀长度，选择性已经没有提升了（或者某些时候提升幅度很小了）

3.2.2创建前缀索引

mysql> ALTER TABLE city ADD [KEY|INDEX] city_name_index(city_name(7));

前缀索引是一种能使索引更小、更快的有效方法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY 和 GROUP BY ，也就无法使用前缀索引做覆盖扫描

3.3多列索引

很多人对多列索引的理解还不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引

CREATE TABLE t(
    c1 INT,
    c2 INT,
    c3 INT,
    KEY(c1),
    KEY(c2),
    KEY(c3)
);

这种索引策略，一般是由于人们听到一些说，“把WHERE条件里面的列都建上索引”导致的。实际上是非常错误的，这样一来最好的情况下也只能是“一星”索引，其性能比起真正最有的索引可能差几个数量级。有时候如果无法设计一个“三星”索引，那么不如忽略掉WHERE子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

更早版本的MySQL只能使用其中某一个单列索引，然后这种情况下没有哪一个独立的单列索引是非常有效的。例如：表film_actor在字段film_id和actor_id上各有一个单列索引，但对于下面这个查询WHERE条件，这两个单列索引都不是好的选择

mysql> SELECT film_id,actor_id FROM film_actor WHERE actor_id=1OR film_id=1;

在老的MySQL版本中，MySQL对这个查询会使用全表扫描。除非改写成如下的两个查询。

mysql> SELECT film_id,actor_id FROM film_actor WHERE actor_id=1  
-> UNION ALL 
-> SELECT film_id,actor_id FROM film_actor WHERE film_id=1 AND actor_id<>1;

3.4选择合适的索引列顺序

3.4.1例子分析

mysql> SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

是创建一个(staff_id, customer_id)索引还是应该颠倒顺序呢？可以做一些查询来确定这个表中值的分布情况，并确定哪个列的选择性更高。

mysql> SELECT SUM(staff_id=2),SUM(customer_id=584) FROM payment;

mysql> SELECT COUNT(DISTINCT(staff_id))/COUNT(*) AS staff_id ,
              COUNT(DISTINCT(customer_id))/COUNT(*) AS customer_id ,
              COUNT(*) 
      FROM payment;