本文围绕MySQL索引展开，介绍独立列索引使用原则，阐述前缀索引、多列索引、聚簇索引、覆盖索引等概念及使用方法，还提及索引列顺序选择、排序时索引使用技巧，同时指出冗余、重复和未使用索引的问题及处理建议。
1. 独立的列
⏰ 独立的列索引使用原则
- 在查询中
MySQL列必须是独立的才会使用索引，若查询中的列不是独立的，则 MySQL 就不会使用索引。 独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。- 如下2个范例都不会使用到索引：(●’◡’●)
🍈
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 =5
上述查询无法使用 actor_id 列的索引，MySQL 无法解析
actor_id + 1这个方程式。这个是用户的行为。我们应该养成简化 WHERE 条件的习惯，❗ 始终将索引列单独的放在比较符号的一侧。
🍈
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10
上述查询 date_col 作为了 TO_DAYS 的函数参数，故不会使用索引。
2. 前缀索引和索引选择性
⏰ 前缀索引
- 若索引很长的字符列，这会让索引变得大且慢。解决方式通常可以 索引开始的部分字符 ，这样可以大大的节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。
⏰ 索引选择性
- 索引选择性 是指，不重复的索引值（也称之为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤更多的行。
唯一索引的选择性为 1 ，这是最好的索引选择性，性能也是最好的。 - 对于 BLOB，TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引 ，因为 MySQL 不允许索引这些列的完整长度。一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。
- 为了保证良好的性能，保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以便使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”，应该接近于完整列的“基数”。
⏰ 前缀索引的计算方式1，范例: (●’◡’●)
- 创建示例表：为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较，在
Sakila中并没有合适的例子，所以创建 city 表，如下：
🍈
CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city; # 执行五次
UPDATE sakila.city_demo SET city =(SELECT city FROM sakila.city ORDER BY RAND() LIMIT 1);
- 筛选最常用的10个城市列表：
🍈
SELECT COUNT(*) AS cnt,city FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;
+-----+-----------------+
| cnt | city |
+-----+-----------------+
| 12 | Dongying |
| 11 | Gingoog |
| 11 | Amroha |
| 11 | Ogbomosho |
| 11 | Mandi Bahauddin |
| 11 | Arecibo |
| 11 | Nezahualcyotl |
| 11 | Usak |
| 10 | Kolpino |
| 10 | Nyeri |
+-----+-----------------+
现在我们查找出了最常见的城市列表
- 筛选最频繁出现的城市前缀，先测试3个前缀字母：
🍈
SELECT COUNT(*) AS cnt,LEFT(city,3) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;
+-----+------+
| cnt | pref |
+-----+------+
| 73 | San |
| 30 | Val |
| 29 | Man |
| 29 | Cha |
| 28 | al- |
| 26 | Sou |
| 24 | Bat |
| 22 | Kan |
| 22 | Sal |
| 22 | Tan |
+-----+------+
可见与完全行的城市列表出现频率相差较远。
- 查看前缀长度为7时：
🍈
SELECT COUNT(*) AS cnt,LEFT(city,7) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;
+-----+---------+
| cnt | pref |
+-----+---------+
| 17 | Valle d |
| 13 | Santiag |
| 12 | Dongyin |
| 11 | Gingoog |
| 11 | Amroha |
| 11 | Nezahua |
| 11 | Mandi B |
| 11 | Ogbomos |
| 11 | Arecibo |
| 11 | Usak |
+-----+---------+
⏰ 前缀索引的计算方式2，范例: (●’◡’●)
//TODO
⏰ 创建前缀索引
- 通过上面的计算方式，我们已经找到了合适的前缀长度，使用如下的 sql 创建前缀索引：
🍈
ALTER TABLE sakila.city_demo ADD KEY (city(7));
⏰ 前缀索引的缺点
- 前缀索引是一种能使索引更小，更快的有效办法，但另一方面也有其缺点：MySQL 无法使用前缀索引做
ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
3. 多列索引
⏰ 多列索引误区
- 错误做法：① 在设置索引的时候 “将 WHERE 条件里面的列都建上索引” 这种方式是错误的；② 或者另一个常见错误做法是，为表中每一个列都创建一个独立的索引，③ 或者按照错误的顺序创建多列索引。
- 错误示例：
🍈
CREATE TABLE t ( c1 INT, c2 INT, c3 INT, KEY ( c1 ), KEY ( c2 ), KEY ( c3 ) );
上述范例最好的情况也只能是“一星”索引，其性能比真正最优的索引可能差几个数量级。如果无法设计一个“三星”索引，那么不如忽略掉 WHERE 子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。
⏰ 索引合并
- 旧版本：在MySQL 5.0 之前的版本中 MySQL 只能使用其中某一单列索引，在这种情况下没有哪一个独立的索引是非常有效的。
- 索引合并(index merge)：在 MySQL 5.0 之后的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。此种策略可以一定程度上使用表中多个单列索引来定位指定的行。
- 索引合并特点：MySQL 5.0 之后的版本有三个变种：OR 条件的联合（union），AND 条件的相交（intersection），组合前两种情况的联合以及相交。
- 索引合并范例：(●’◡’●)
🍈
EXPLAIN SELECT film_id,actor_id FROM sakila.film_actor WHERE actor_id=1 OR film_id =1 ;
... +--------------------------------------------------+
... | Extra |
... +--------------------------------------------------+
... | Using union(PRIMARY,idx_fk_film_id); Using where |
... +--------------------------------------------------+
MySQL 使用了此类技术优化复杂查询，我们可以在
Extra列中看到嵌套操作的类型。
⏰ 索引合并缺点和多列索引建议
❗ 索引合并策略是MySQL一种优化的结果，但实际上更多的时候说明了表上的索引建的很糟糕：
- 当出现多个索引做相交操作的时候（通常有多个 AND 条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
- 当需要对多个索引做联合操作的时候（通常有多个 OR 条件），通常需要消耗大量 CPU和内存资源在算法的缓存，排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
- 更重要的是，优化器不会把这些，计算到"查询成本"中。还可能会影响到查询的并发性。通常来说，还不如像在 MySQL 4.1 或者更早的时代，将查询改写为 UNION 的方式。
- 若在 EXPLAIN 中查看到有索引合并，应该检查一下查询和表结构，看是不是已经是最优解了。
4. 选择合适的索引列顺序
⏰ 索引列顺序的重要性
- 创建索引的时候，索引列的顺序至关重要。而正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。
- 在一个多列
B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY，GROUP BY，DISTINCT等子句的查询需求。
⏰ 索引列顺序优化的规则
- ⭕🌏将 选择性最高的列放到索引最前列（此为经验法则）。
使用范围：当不需要考虑排序和分组的时候，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化 WHERE 条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在 WHERE 子句中只使用了索引部分前缀列的查询来说选择性也更高。
- ⭕🌏 查询值分布：索引列排序不只是依赖所有列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。
这里的技巧同选择前缀索引的长度需要考虑的点一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。
⏰ 索引列顺序优化范例
- 查询值分布 范例：(●’◡’●)
🍈
SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584 ;
-
范例任务 ：根据上述查询，需要确认的是创建一个
(staff_id, customer_id)的索引，还是(customer_id, staff_id )的索引。 -
确定查询值分布，特定值确认：我们可以跑一些查询来确定这个表中值的分布情况，并确定哪个列的选择性更高。
🍈 SELECT SUM(staff_id = 2),SUM(customer_id = 584) FROM payment; +-------------------+------------------------+ | SUM(staff_id = 2) | SUM(customer_id = 584) | +-------------------+------------------------+ | 7992 | 30 | +-------------------+------------------------+ 🍈 SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584; +-------------------+ | SUM(staff_id = 2) | +-------------------+ | 17 | +-------------------+根据特定值查询，可以看出我们应该将索引列 customer_id 放到前面，因为 customer_id 列数量更小。
-
确定查询值分布，选择性确认：上述查询非常依赖于选定的具体值，可能出现选择的特定值不够典型，使得优化达不到预期的效果。使用如下查询确定全局基数和选择性：(●’◡’●)
🍈 SELECT COUNT( DISTINCT staff_id ) / COUNT( * ) AS staff_id_selectivity, COUNT( DISTINCT customer_id ) / COUNT( * ) AS customer_id_selectivity, COUNT( * ) FROM payment; +----------------------+-------------------------+------------+ | staff_id_selectivity | customer_id_selectivity | COUNT( * ) | +----------------------+-------------------------+------------+ | 0.0001 | 0.0373 | 16049 | +----------------------+-------------------------+------------+❗ 综上所述：customer_id 的选择性更高，所以答案是将其作为索引列的第一列：
ALTER TABLE payment ADD KEY(customer_id,staff_id);
⏰ 索引列顺序的特殊范例
⭕🌏❓
5. 聚簇索引
⭕🌏聚簇索引：并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但 InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行。
⭕🌏一个表只能有一个聚簇索引。
⭕🌏聚簇索引的选择：InnoDB 的聚簇索引是主键，若没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面的记录。包含相邻键值的页面可能会相距甚远。
6. 覆盖索引
⏰ 定义
- 一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为
覆盖索引。 - 覆盖索引最直接的优点是，索引的叶子节点中已经包含要查询的数据，就不需要再回表查询了。
① 优化查询通常我们会根据查询的 WHERE 条件来创建合适的索引 ② 另一个方面是考虑整个查询，创建覆盖索引，直接从索引中获取所有的数据列。
- MySQL 中只能使用 B-Tree 索引做覆盖索引。因为覆盖索引必须要存储索引列的值，而哈希索引，空间索引和全文索引等都不存储索引列的值。
⏰ 范例
⭕🌏 当发起一个被索引覆盖的查询时，在 EXPLAIN 的 Extra 列可以看到 “Using index”的信息。
- 执行如下查询使用了覆盖索引：(●’◡’●)
🍈
EXPLAIN SELECT store_id,film_id FROM sakila.inventory;
+----+-------------+-----------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | inventory | NULL | index | NULL | idx_store_id_film_id | 3 | NULL | 4581 | 100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
⏰ 优化非覆盖索引
⭕🌏❓
7. 使用索引扫描来做排序
⏰ 排序方式和索引排序
- MySQL 有两种方式可以生成有序结果：① 通过表排序操作；② 或者按照索引顺序扫描；如果 EXPLAIN 出来 type 列的值为
index，则说明 MySQL 使用了索引扫描来做排序（不要和 Extra 列的Using index混淆，它是代表查询使用到了全覆盖索引） - 扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就回表查询一次对应的行。这基本上都是随机 I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在 I/O 密集型的工作负载时。
⏰ 排序时,使用索引
- ⭕🌏 MySQL 可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。
- 只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向（倒序或者正序）都一样时，MySQL 才能够使用索引来对结果做排序。
- 如果查询需要关联多张表，则只有当 ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。
- ORDER BY 子句和查询类型的限制是一样的，需要 满足索引的最左前缀的要求；否则，MySQL 都需要执行排序操作，而无法利用索引排序。
⏰ 排序时,索引的使用技巧
- ⭕🌏 当 前导列为常量 的时候，ORDER BY 子句可以不满足索引的最左前缀要求。这种情况出现在 WHERE 子句或者 JOIN 子句中这些列在执行查询时，指定了常量。
- ⭕🌏 范例表结构，Sakila 示例数据库的表 rental 如下：
CREATE TABLE `rental` ( ... UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`), KEY `idx_fk_inventory_id` (`inventory_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `idx_fk_staff_id` (`staff_id`), ... )上表的联合索引如下 (
rental_date,inventory_id,customer_id) - ❗ 如下 SQL 范例使用了 rental_date 索引为查询做排序，从 EXPLAIN 的 Extra 字段为 Using index condition 可以看出。
🍈
EXPLAIN SELECT rental_id,staff_id FROM sakila.rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | rental | NULL | ref | rental_date | rental_date | 5 | const | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
当索引的第一列被指定为一个常数，即使 ORDER BY 子句不满足索引的最左前缀要求，也可以用于查询排序。
- ❗ 如下 SQL 范例也可以使用 rental_date 索引为查询做排序
🍈
... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC ;
上述查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最前左前缀。
- ❗ 如下 SQL 范例也可以使用 rental_date 索引为查询做排序
🍈
... 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 ;
- 下面这个例子理论上是可以使用索引进行关联排序的，但是由于优化器在优化时将 film_actor 表当作关联的第二张表，所以实际上无法使用索引：
🍈
EXPLAIN SELECT actor_id,title FROM sakila.film_actor INNER JOIN sakila.film USING(film_id) ORDER BY actor_id;
+----+-------------+------------+...+----------------------------------------------+
| id | select_type | table |...| Extra |
+----+-------------+------------+...+----------------------------------------------+
| 1 | SIMPLE | film |...| Using index; Using temporary; Using filesort |
| 1 | SIMPLE | film_actor |...| Using index |
+----+-------------+------------+...+----------------------------------------------+
8. 压缩(前缀压缩)索引
⏰ 压缩(前缀压缩)索引
- ⭕🌏 前缀压缩常用于 MyISAM ，MyISAM 使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，单通过参数设置也可以对整数做压缩。
- ⭕🌏 因为 InnoDB 没有使用索引压缩，所以此节略。
9. 冗余和重复索引
⏰ 重复索引
- ⭕🌏 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
上述创建了一个主键，然后加了一个唯一限制（UNIQUE），然后再加上普通索引（INDEX）以供查询使用。事实上，MySQL 的唯一限制和主键限制都是通过索引实现的，因此上述写法实际上在相同的列上创建了三个重复的索引。通常我们没有理由怎么做，创建一个主键索引就可以了，除非是在同一列上创建不同类型的索引来满足不同的查询需求（例如：创建 KEY(col) 和 FULLTEXT KEY(col) 两种索引，此2个索引类型不同，所以并不算是重复索引）。
⏰ 冗余索引
- ⭕🌏 冗余索引定义：冗余索引和重复索引有一些不同。如果创建了索引（A,B）,再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引。但是如果再创建索引（B,A），则不是冗余索引，索引（B）也不是。另外不同类型的索引（例如哈希索引或者全文索引）也不会是 B-Tree 索引的冗余索引，而无论覆盖索引列是什么。
- ⭕🌏冗余索引使用：大多数情况下都不需要冗余索引，应该尽量拓展已有的索引而不是创建新索引。但是也有时候出于性能方面的考虑需要冗余索引，因为拓展已有的索引会导致其变得太大，从而影响其他使用该索引的查询性能。
10. 未使用的索引
- ⭕🌏 除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引。这样的索引完全是累赘，建议考虑删除。
扫一扫
1104
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
