高性能的索引策略1

评价一个索引是否适合某个查询的“三星系统”

一星：索引将相关的记录存放在一起
二星：索引中的数据顺序和查找中的排列顺序一直
三星：索引中的列包含了查询中的全部列

1 独立的列

　　“独立的列”：索引列不能是表达式的一部分，也不能是参数的参数，必须将索引列单独放在比较符号的一侧

mysql> select actor_id from sakila.actor where actore_id + 1 = 5;【X】

mysql> select ... where to_days(current_date) - to_days(date_col) <= 10;【X】

2 前缀索引和索引选择性

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

【索引选择性：不重复的索引值（基数）和数据表的记录总数（#T）的比值，范围从1/#T 到 1 之间，索引选择性越高，查询效率越高，因为选择性高的索引可以让mysql过滤掉更多的行】　　

我们需要通过特定的方法确认，当前缀索引长度为N时，这N个字符的选择性已经接近整个字段的选择性，如下面例子所示，当N为7时，再增加前缀长度，选择性的增加已经很小。

select
count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4,
count(distinct left(city,5))/count(*) as sel5,
count(distinct left(city,6))/count(*) as sel6,
count(distinct left(city,7))/count(*) as sel7
from sakila.city;

+--------+--------+--------+--------+--------+

| sel3 | sel4 | sel5 | sel6 | sel7 |

+--------+--------+--------+--------+--------+

| 0.7633 | 0.9383 | 0.9750 | 0.9900 | 0.9933 |

+--------+--------+--------+--------+--------+

增加前缀索引：mysql> alter table sakila.city add key(city(7));

-- 原始ddl，city字段上没有索引
 CREATE TABLE `city` (
  `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint(5) 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`),
  KEY `city` (`city`(7)),
  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8mb4

-- 查询的执行计划
mysql> explain select * from city where city = 'Ahmadnagar';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  600 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 增加city字段索引后的ddl
 CREATE TABLE `city` (
  `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint(5) 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`),
  KEY `city` (`city`(7)),
  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8mb4

-- 查询的执行计划，查询已经可以走上索引了。
mysql> explain select * from city where city = 'Ahmadnagar';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ref  | city          | city | 30      | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

　前缀索引是一种能让索引更小、更快的方法，但是也有缺点：

无法使用前缀索引做order by 和 group by
无法使用前缀索引做覆盖扫描

3 多列索引

首先需要明确，在多个列上建立单独的索引不能大部分情况下不能提高mysql查询性能。

在mysql5.0之后，引入了“索引合并”策略，一定程度上可以使用表上的多个单列索引来定位指定的行，如下例子“

actor_id 和 film_id 是两个独立的列，并且单独有索引。

CREATE TABLE `film_actor_test` (
  `actor_id` smallint(5) unsigned NOT NULL,
  `film_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `actor_id` (`actor_id`),
  KEY `film_id` (`film_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

-- 在做 or 查询时，会做索引合并
mysql> explain select film_id,actor_id from sakila.film_actor_test where actor_id = 1 or film_id = 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor_test
   partitions: NULL
         type: index_merge
possible_keys: actor_id,film_id
          key: actor_id,film_id
      key_len: 2,2
          ref: NULL
         rows: 29
     filtered: 100.00
        Extra: Using union(actor_id,film_id); Using where
1 row in set, 1 warning (0.00 sec)

-- 上面的查询相当于如下两个语句union在一起，每个sql都可走上索引

select film_id,actor_id from sakila.film_actor_test where actor_id = 1
union 
select film_id,actor_id from sakila.film_actor_test where film_id = 1

　索引合并是一种优化策略，但是实际更多说明表上的索引建立得很有问题：

当出现服务器对多个索引做相交操作时（通常为多个and 条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立列的单列索引
当出现服务器对多个索引做联合操作时（通常为多个or 条件），通常需要消耗大量cpu 和内存在算法的缓存、排序和合并操作上。尤其是在有些索引的选择性不高，需要合并扫描返回的大量数据的时候

如果在EXPLAIN中看到索引合并，应该好好检查一下查询和表的结构。

4 选择合适的索引顺序（如下讨论适用于B Tree索引）

在一个多列R-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次按照第二列......索引可以按照升序、降序进行扫描，以满足符合列顺序的order by、group by 和 distinct 等字句的查询需求

如何选择索引列的顺序的经验法则：将选择性最好的列放到索引的最前列。

针对 payment表，通过如下方法来确认哪个字段应该作为索引的最左字段。

mysql> select count(distinct staff_id)/count(*) as selectivity_1, count(distinct customer_id)/count(1) as selectivity_2 from payment;
+---------------+---------------+
| selectivity_1 | selectivity_2 |
+---------------+---------------+
|        0.0001 |        0.0373 |
+---------------+---------------+
1 row in set (0.01 sec)
-- 由上例可知， customer_id 的选择性更高，应该放在索引的最左侧
alter table payment add key(customer_id, staff_id);

除了索引顺序，还得考虑条件命中的行数，如下例：

select count(*),sum(groupId = 1),sum(userId = 111),sum(anonymous = 0) from message \G;

               count(*) : 4142217
        sum(groupId = 1 : 4042217
      sum(userId = 111) : 4002217
     sum(anonymous = 0) : 3842217

由上例可知，几乎所有数据均满足以上的各个条件，也就说索引基本上没有起到作用。

这个案例，只能从应用程序上修改，修改查询条件。

5 聚簇索引

InnoDB的聚集索引不能单独建立，一般默认以主键为聚集索引，即 InnoDB的数据文件要按主键聚集。

聚簇索引不是一种单独的索引类型，而是一种数据存储方式。

InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree 索引和数据行，【索引存储在B Tree的节点页，数据行存储在B Tree的叶子页】

“聚簇”表示数据行和相邻键值（索引值）紧凑的存储在一起。

索引是由存储引擎实现的，并不是所有的存储引擎都支持聚簇索引。

聚簇索引优点：

可以把相关数据保存在一起。如电子邮箱系统按照用户ID作为聚簇索引来聚簇数据，则很容易通过很少的磁盘IO读取某个用不所有的邮件信息
数据访问更快。聚簇索引将索引和数据存储在一个B Tree中，因此在聚簇索引中获取数据比在非聚簇索引中获取数据更快
使用覆盖索引扫描的查询可以直接使用节点页的主键值

聚簇索引缺点：

聚簇索引最大限度提高了IO密集型应用的性能，但是如果数据全部存放在内存中，聚簇索引就没什么优势了
插入速度严重依赖与插入顺序。按照主键的顺序插入是加载数据到innoDB表速度最快的方式
更新聚簇所以你列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
基于聚簇索引的表在插入新行，或者逐渐被更新导致需要移动行的时候，可能面临“页分裂”的问题
......

在InnoDB中按照主键顺序插入行

如果正在使用InnoDB表并且没有什么数据聚集要求，可以定义一个代理键作为主键，这种主键和应用无关

推荐：最简单的方法是使用AUTO_INCREMENT自增列。这样保证行数据按照顺序写入，对于按照主键做关联操作的性能也更好
不推荐：UUID作为聚簇索引则会很糟糕，UUID是随机分布的，聚簇索引的插入也是随机的。UUID作为主键索引插入行花费时间更长，原因：
- 主键字段更长
- 页分裂和碎片导致
  - 写入是乱序的，InnoDB不得不频繁地进行页分裂操作，以为新的行分配空间。页分裂导致大量数据移动，一次插入至少需要修改三个页面而不是一个
  - 由于频繁页分裂，页会变得越来越稀疏并被不规则填充，最终导致数据有碎片

posted @ 2020-01-27 13:53 后山前堂客阅读(162) 评论(0) 收藏举报

刷新页面返回顶部

后山前堂客

高性能的索引策略1

公告