6. Mysql的索引（索引优化）

2018-10-07  本文已影响0人  one_zheng

 在Mysql中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。
 索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为Mysql只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。

索引的类型
高性能的索引策略

 例如，下面这个查询无法使用actor_id列的索引：

mysql> SELECT actor_id from sakila.actor where actor_id +1 = 5;

 我们应该简化WHERE条件的习惯，下面是另一个常见的错误：

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

 例如,表film_actor在字段flim_id和actor_id上各有一个单列索。对于下面的这个查询WHERE条件：

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

 在Mysql 5.0 以及更新版本，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合（union）,AND条件的相交（intersection），组合前两种情况的联合及相交。下面的查询就是使用了两个索引扫描的联合，通过EXPLAIN中的Extra列可以看到这点：

image.png

 索引合并策略是一种优化的结果，但实际上说了表上的索引建得很糟糕：

如果在 EXPLAIN 看到有索引合并，应该好好检查一下查询的表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引

 在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。

当不需要考虑排序和分组时，将选择性最高（命中高，数据量小）的列放在前面通常是很友好的。这时候索引的作用只是用于优化WHERE条件的查找

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

是应该创建一个（staff_id,custom_id）索引还是应该颠倒一下索引顺序？可以跑一下查询确认这个表中值的分布情况，并确定哪个列的选择性更高。

mysql> SELECT SUM(staff_id = 2),SUM(customer_id = 584) FROM payment\G
***************************1.row********************************
SUM(staff_id = 2) = 7992
SUM(customer_id = 584) = 30

 根据前面的经验法则，应该将索引customer_id放到前面，因为对应条件值customer_id数量更小。

customer_id的选择性更高，所以答案是将其作为索引列的第一列

mysql>ALTER TABLE payment ADD KEY(customer_id, staff_id);

 Mysql 可以使用同一个索引既满足排序，又用于查找。因此，如果可能，设计索引是应尽可能同时满足这两种任务，这样是最好的。

 只有当索引的顺序和ORDER BY字句的顺序完全一致，并且所有列的排序方法（倒序或者正序）都一样时，Mysql才能够使用索引来对结果做排序。

 如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。

 Mysql允许在相同列上创建多个索引，但Mysql需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

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

  CREATE TABLE test (
    ID INT NOT NULL PRIMARY KEY,
    A  INT NOT NULL,
    B  INT NOT NULL,
    UNIQUE(ID),
    INDEX(ID),
  ) ENGINE = InnoDB;

 上面在相同的列上创建了三个重复的索引。

 冗余索引和重复索引有一些不同。如果创建了索引（A，B），再创建索引（A）就是冗余索引，因为这是前一个索引的前缀索引。因此索引（A，B）也可以当作索引（A）来使用（这种冗余索引甚至是对B-Tree索引来说）。如果再创建索引（B,A），则不再冗余索引，索引（B）也不是，因为B不是索引（A，B）的最左前缀列。

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

应该尽量扩展已有的索引而不是创建新索引

 如果存在服务器永远不用的索引。这样的索引完全是累赘，建议考虑删除。

 定位：设置userstates服务器变量打开，然后让服务器运行一段时间，再通过查询INFORMATION_SCHEMA,INDEX_STATSTICS就能查找每个索引的使用频率。

image.png

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

  WHERE eye_color IN('brown','blue','hazel')
      AND hair_color IN('black','red','blonde','brown')
      AND sex            IN('M','F')
      AND last_oneline > DATE_SUB(NOW(), iINTERVAL 7 DAY)
      AND age            BETWEEN 18 AND 25

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

 如果条件中只有last_oneline而没有age，那么我们可能考虑在索引的后面加上last_oneline列。如果我们能够将其中的一个范围查询转换为一个简单的等值比较，这就可以使用到两个索引。

 InnoDB可以通过先删除，然后再重新创建索引的方式来消除索引的碎片化。

总结
上一篇 下一篇

猜你喜欢

热点阅读