如何高效高性能的选择使用 MySQL 索引？

发布时间：2023-09-06 12:56:26 所属栏目：MySql教程来源：

导读：想要实现高性能的查询，正确的使用索引是基础。本小节通过多个实际应用场景，帮助大家理解如何高效地选择和使用索引。

1. 独立的列
独立的列，是指索引列不能是表达式的一部分，也不能是函数的参数。如果 sql 查

想要实现高性能的查询，正确的使用索引是基础。本小节通过多个实际应用场景，帮助大家理解如何高效地选择和使用索引。

1. 独立的列
独立的列，是指索引列不能是表达式的一部分，也不能是函数的参数。如果 sql 查询中的列不是独立的，MysqL 不能使用该索引。

下面两个查询，MysqL 无法使用 id 列和 birth_date 列的索引。开发人员应该养成编写 sql 的好习惯，始终要将索引列单独放在比较符号的左侧。

MysqL> select * from customer where id + = ;
MysqL> select * from customer where to_days(birth_date) - to_days('2020-06-07') <= ;

2. 前缀索引
有时候需要对很长的字符列创建索引，这会使得索引变得很占空间，效率也很低下。碰到这种情况，一般可以索引开始的部分字符，这样可以节省索引产生的空间，但同时也会降低索引的选择性。

那我们就要选择足够长的前缀来保证较高的选择性，但是为了节省空间，前缀又不能太长，只要前缀的基数，接近于完整列的基数即可。

Tips：索引的选择性指，不重复的索引值（也叫基数，cardinality）和数据表的记录总数的比值，索引的选择性越高表示查询效率越高。

完整列的选择性：

MysqL> select count(distinct last_name)/count(*) from customer;
+------------------------------------+
| count(distinct last_name)/count(*) |
+------------------------------------+
| |
+------------------------------------+
不同前缀长度的选择性：

MysqL> select count(distinct left(last_name,))/count(*) left_3, count(distinct left(last_name,))/count(*) left_4, count(distinct left(last_name,))/count(*) left_5, count(distinct left(last_name,))/count(*) left_6 from customer;
+--------+--------+--------+--------+
| left_3 | left_4 | left_5 | left_6 |
+--------+--------+--------+--------+
| | | | |
+--------+--------+--------+--------+
从上面的查询可以看出，当前缀长度为 6 时，前缀的选择性接近于完整列的选择性 0.053，再增加前缀长度，能够提升选择性的幅度也很小了。

创建前缀长度为6的索引：

MysqL> alter table customer add index idx_last_name(last_name());
前缀索引可以使索引更小更快，但同时也有缺点：无法使用前缀索引做 order by 和 group by，也无法使用前缀索引做覆盖扫描。

3. 合适的索引列顺序
在一个多列 B-Tree 索引中，索引列的顺序表示索引首先要按照最左列进行排序，然后是第二列、第三列等。索引可以按照升序或降序进行扫描，以满足精确符合列顺序的 order by、group by 和 distinct 等的查询需求。

索引的列顺序非常重要，在不考虑排序和分组的情况下，通常我们会将选择性最高的列放到索引最前面。

以下查询，是应该创建一个 (last_name,first_name) 的索引，还是应该创建一个(first_name,last_name) 的索引？

MysqL> select * from customer where last_name = 'Allen' and first_name = 'Cuba'
我们首先来计算下这两个列的选择性，看哪个列更高。

MysqL> select count(distinct last_name)/count(*) last_name_selectivity, count(distinct first_name)/count(*) first_name_selectivity from customer;
+-----------------------+------------------------+
| last_name_selectivity | first_name_selectivity |
+-----------------------+------------------------+
| | |
+-----------------------+------------------------+
很明显，列 first_name 的选择性更高，所以选择 first_name 作为索引列的第一列：

MysqL> alter table customer add index idx1_customer(first_name,last_name);

4. 覆盖索引
如果一个索引包含所有需要查询的字段，称之为覆盖索引。由于覆盖索引无须回表，通过扫描索引即可拿到所有的值，它能极大地提高查询效率：索引条目一般比数据行小的多，只通过扫描索引即可满足查询需求，MysqL 可以极大地减少数据的访问量。

表 customer 有一个多列索引 (first_name,last_name)，以下查询只需要访问 first_name 和last_name，这时就可以通过这个索引来实现覆盖索引。

MysqL> explain select last_name, first_name from customer\G
*************************** . row ***************************
id:
select_type: SIMPLE
table: customer
partitions: NULL
type: index
possible_keys: NULL
key: idx1_customer
key_len:
ref: NULL
rows:
filtered:
Extra: Using index
row in set, warning ( sec)
当查询为覆盖索引查询时，在 explain 的 extra 列可以看到 Using index。

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!