MySQL インデックス最適化のナレッジ ポイントを要約して整理する

この記事では、mysql に関する関連知識を提供します。主にインデックス作成に関する関連問題を紹介しており、インデックス最適化の原則に関する関連コンテンツも含まれています。一緒に見ていきましょう。皆さんのお役に立てれば幸いです。

推奨学習: mysql ビデオ チュートリアル

##まえがき : インデックスによって MySQL が大幅に改善されることは誰もが知っていると思います。検索速度は重要ですが、日常業務で実際に SQL を書くときに、この SQL がインデックスをどのように使用して実行効率を向上させるかを真剣に考慮していますか?このブログではインデックス最適化の 20 の原則を詳しく紹介していますが、これらを業務のいつでも適用できれば、作成した SQL はインデックスにヒットし、非常に効率的になると思います。

1. インデックスの分類

インデックスを使用すると、MySQL の検索速度が大幅に向上します。インデックスは本の目次のようなもので、必要なデータをより早く見つけることができるように、MySQL でよく使用されるインデックスを以下に紹介します。

# 1.1. 通常インデックス、主キーインデックス、ユニークインデックス

(1) 通常インデックス

これは最も基本的なインデックスであり、制限はありません。 ＃＃＃＃＃＃＃＃＃使い方？

//方式1
ALTER TABLE table_name ADD INDEX index_name ( column )

例: ALTER TABLE users ADD INDEX index_users(id)

//方式2
CREATE INDEX index_name ON table_name (column_name)

例: CREATE INDEX Index_users ON users (id)

(2) 一意Index

通常のインデックスと似ていますが、インデックス列の値は一意である必要がありますが、NULL 値が許可される点が異なります。複合インデックスの場合、列値の組み合わせは 1 つである必要があります。 ＃＃＃＃＃＃＃＃＃使い方？

//方式1
ALTER TABLE table_name ADD UNIQUE [indexName] (column)

例: ALTER TABLE users ADD UNIQUE Index_users( id )

//方式2
CREATE UNIQUE INDEX index_name ON table_name (column_name)

例: CREATE UNIQUE INDEXindex_users ON users(id)

(3)主キー インデックス

これは、NULL 値を許可しない特別な一意のインデックスです。通常、テーブルの作成時に主キーを指定すると、主キー インデックスが自動的に作成されます。CREATE INDEX を使用して主キー インデックスを作成することはできません。代わりに ALTER TABLE を使用してください。 ＃＃＃＃＃＃＃＃＃使い方？

//方式1
ALTER TABLE table_name ADD PRIMARY KEY ( column )

例: ALTER TABLE ユーザー ADD PRIMARY KEY (id)

方法 2: テーブルの作成時に主キーを指定します

1.2、クラスター化インデックスおよび非クラスター化Index

(1) クラスター化インデックス

クラスター化インデックスとも呼ばれ、すべてのデータはクラスター化インデックス内に存在します。リーフ ノードは、データに直接対応します。中間インデックス ページ インデックス行はデータ ページに直接対応します。 InnoDB ストレージ エンジンの主キーはデフォルトでクラスター化インデックスを作成し、テーブルごとにクラスター化インデックスは 1 つだけ作成できます。レコードのインデックス順序は物理的な順序と同じであり、between および order by 操作により適しています。

InnoDB

クラスター化インデックスのリーフ ノード

には行レコードが格納されます。したがって、InnoDB にはクラスター化インデックスが 1 つだけ必要です:

(1) テーブルで PK が定義されている場合、この場合、PK はクラスター化インデックスになります。

(2) テーブルで PK が定義されていない場合、NULL でない最初の一意の列がクラスター化インデックスになります。

(3) それ以外の場合、InnoDB は非表示の行 ID はクラスター化インデックスとして使用されます

ボイスオーバー: したがって、PK クエリは非常に高速で、行レコードは直接検索されます。

たとえば、テーブルは以前に使用した新華社辞書に似ており、クラスター化インデックスはピンイン ディレクトリに似ており、各単語が格納されているページ番号は、辞書の物理アドレスです。 「Wow」という単語をクエリしたい場合、新華辞典のピンインカタログ内の「Wow」という単語に対応するページ番号をクエリするだけで、対応する「Wow」という単語の位置をクエリすることができます。 、およびピンイン カタログに対応する単語 A ～ Z この順序は、新華辞典に実際に格納されている文字の A ～ Z の順序と同じです。新しい中国語の文字があり、ピンインの先頭の最初の文字が B である場合、いつ挿入される場合は、ピンイン ディレクトリの順序に従って文字 A の後に挿入する必要があります。

(2) 非クラスター化インデックス

非クラスター化インデックスは、非クラスター化インデックスおよび補助インデックスとも呼ばれ、すべてのデータ ディレクトリとインデックス ディレクトリは別々に保存されます。ノードは特定のデータ行全体を保存しません (リーフ ノードはデータ ページを直接ポイントしません) が、この行の主キーの値を保存します。 レコードのインデックス順序は、物理的な順序とは関係ありません。各テーブルには複数の非クラスター化インデックスを含めることができるため、より多くのディスクとメモリが必要となり、複数のインデックスは挿入と更新の速度に影響します。

ナレーション: 非クラスター化インデックスはテーブルに対してクエリを実行する必要があります。まず主キー値を見つけてから、行レコードを見つけます。インデックス ツリーは 2 回スキャンする必要があるため、パフォーマンスが低下します。インデックス ツリーを 1 回スキャンするよりも低いです。

実際、定義上、クラスター化インデックス以外のインデックスは非クラスター化インデックスですが、人々は非クラスター化インデックスを通常のインデックス、一意のインデックス、およびフルテキスト インデックスに細分したいと考えています。非クラスター化インデックスを現実のものにたとえる必要がある場合、非クラスター化インデックスは新華社辞書の部首辞書のようなものであり、その構造順序は実際の格納順序と必ずしも一致しません。

 1.3、联合索引最左匹配原则

联合索引又叫复合索引，对表上的多个字段同时建立的索引(有顺序，ABC，ACB是完全不同的两种联合索引。)以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。

遵循最左前缀原则（必须带着最左边的列做条件才能命中索引），且从出现范围开始索引失效；

当遇到范围查询(>、<、between、like)就会停止匹配。也就是：

#这样a,b可以用到（a,b,c），c不可以
select * from t where a=1 and b>1 and c =1;</p>
<p>这条语句只有 a,b 会用到索引，c 都不能用到索引。</p>
<pre class="brush:php;toolbar:false">create index mix_ind on 表名 (id,name,email);

select * from 表名 where id = 123;  # 命中索引

select * from 表名 where id = 123 and name = 'pamela';  # 命中索引

select * from 表名 where id > 123 and name = 'pamela';  # id命中，name不命中索引，因为出现范围

select * from 表名 where id = 123 and email = 'pamela@123.com';  # 命中索引

select * from 表名 where email = 'pamela@123.com';  # 不命中索引，因为条件中没有id

select * from 表名 where name='pamela' and email = 'pamela@123.com';  # 不命中

A:select * from student where age = 16 and name = '小张'

B:select * from student where name = '小张' and sex = '男'

C:select * from student where name = '小张' and sex = '男' and age = 18

D:select * from student where age > 20 and name = '小张'

E:select * from student where age != 15 and name = '小张'

 A遵从最左匹配原则，age是在最左边，所以A走索引；

 B直接从name开始，没有遵从最左匹配原则，所以不走索引；

 C虽然从name开始，但是有索引最左边的age，mysql内部会自动转成where age = '18' and name = '小张'  and sex = '男' 这种，所以还是遵从最左匹配原则；

 D这个是因为age>20是范围，范围字段会结束索引对范围后面索引字段的使用，所以只有走了age这个索引；

 E这个虽然遵循最左匹配原则，但是不走索引，因为!= 不走索引；

question1:如何给下列sql语句加上联合索引？

select * from test where a = 1 and b = 1 and c = 1;

answer：

咱们一看，直接加索引(a,b,c)就可以了，其实不然，也不能说这个答案不对，只能说这个答案不够完整。因为mysql在执行的时候会经历优化器过程，所以会把sql语句自动优化成符合索引的顺序，所以索引(a,b,c) (a,c,b) 或者是(c,b,a)都是可以的，那我们究竟如何确定索引呢？这个就得根据实际情况来了，比如a字段是表示性别的，只有0，1和2三个值来表示 未知，男，女三种性别，那么把a字段放在联合索引的最后会比较合适，反正哪个字段的内容重复率越高，就把哪个字段往联合索引的后面放。

question2:如何给下列sql语句加上索引？

SELECT * FROM table WHERE a > 1 and b = 2;

answer：

如果咱们建立索引(a,b),那么a>1是可以走到索引的，但是b=2就没法走到索引了。但是如果咱们建立索引(b,a),那么sql语句会被自动优化成 where b=2 and a> 1，这样a和b都能走到索引，所以建立索引(b,a)比较合适

 1.4、索引覆盖和回表

使用聚集索引（主键或第一个唯一索引）就不会回表，非聚集索引就会回表。当select的数据列被所建索引覆盖时不需要回表，可以直接取得数据。

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。覆盖索引在查询过程中不需要回表。只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表速度更快。

覆盖索引其核心就是只从辅助索引要数据。那么, 普通索引(单字段)和联合索引，以及唯一索引都能实现覆盖索引的作用。explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

create index ind_id on 表名(id);  # 对id字段创建了索引

select id from 表名  where id > 100;  # 覆盖索引：在查找一条数据的时候，命中索引，不需要再回表 
select max(id) from 表名  where id > 100;  # 覆盖索引：在查找一条数据的时候，命中索引，不需要再回表 
select count(id) from 表名  where id > 100;  # 覆盖索引：在查找一条数据的时候，命中索引，不需要再回表 
select name from 表名  where id > 100;  # 相对慢

(1) 如何实现索引覆盖？

常见的方法是：将被查询的字段，建立到联合索引里去。

select id,name from user where name='shenjian';

 能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。

Extra：Using index。

(2)哪些场景可以利用索引覆盖来优化SQL？ 

场景1：全表count查询优化

原表为：user(PK id, name, sex)；不能利用索引覆盖

select count(name) from user;

添加索引，就能够利用索引覆盖提效

alter table user add key(name);

场景2：列查询回表优化

这个例子不再赘述，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。

场景3：分页查询

将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

1.5、前缀索引

前缀索引说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快。

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

 1.6、索引合并

深入理解 index merge 是使用索引进行优化的重要基础之一。理解了 index merge 技术，我们才知道应该如何在表上建立索引。

为什么会有index merge？

我们的 where 中可能有多个条件(或者join)涉及到多个字段，它们之间进行 AND 或者 OR，那么此时就有可能会使用到 index merge 技术。index merge 技术如果简单的说，其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。

MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

索引合并是指分别创建的两个索引，在某一次查询中临时合并成一条索引。

# 索引合并
create index ind_id on 表名(id);
create index ind_email on 表名(email);

select * from 表名 where id=100 or email = 'pamela@123.com'  # 索引合并，临时把两个索引ind_id和ind_email合并成一个索引

 1.7、索引下推

(1)索引下推简介

索引条件下推(Index Condition Pushdown),简称ICP。MySQL5.6新添加，用于优化数据的查询。 通过索引下推对于非主键索引进行优化，可有效减少回表次数，从而提高效率。

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

• 当你不使用ICP,通过使用非主键索引（普通索引or二级索引）进行查询，存储引擎通过索引检索数据，然后返回给MySQL服务器，服务器再判断是否符合条件。

• 使用ICP，当存在索引的列做为判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

(2)适用场景

• 当需要整表扫描，e.g.:range,ref,eq_ref....

• 适用InnoDB引擎和MyISAM引擎查询（5.6版本不适用分区查询，5.7版本可以用于分区表查询）。

• InnoDB引擎仅仅适用二级索引。（原因InnoDB聚簇索引将整行数据读到InnoDB缓冲区）。

• 子查询条件不能下推。触发条件不能下推，调用存储过程条件不能下推。

---

二、索引优化规则

查询的条件字段尽量用索引字段

2.0、and/or条件相连

and条件相连，有一列有索引就会命中索引，加快查询速度；or条件相连，所有列都有索引才能命中索引，加快查询速度；

create index mix_ind on 表名 (id);

select * from 表名 where id = 123 and name = 'pamela';  # 有一列有索引，速度快

select * from 表名 where id = 123 or name = 'pamela';  # 不是所有列都有索引，速度慢

2.1、like语句的前导模糊查询不能使用索引

select * from doc where title like '%XX'；   --不能使用索引

select * from doc where title like 'XX%'；   --非前导模糊查询，可以使用索引

因为页面搜索严禁左模糊或者全模糊，如果需要可以使用搜索引擎来解决。

2.2、union、in、or 都能够命中索引，建议使用 in

union能够命中索引，并且MySQL 耗费的 CPU 最少。

select * from doc where status=1

union all

select * from doc where status=2;

in能够命中索引，查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in。

select * from doc where status in (1, 2);

or 新版的 MySQL（MySQL5.0后） 索引合并能够命中索引，查询优化耗费的 CPU 比 in多，不建议频繁用or。

select * from doc where status = 1 or status = 2

补充：有些地方说在where条件中使用or，索引会失效，造成全表扫描，这是个误区：

• 要求where子句使用的所有字段，都必须建立索引；

• 如果数据量太少，mysql制定执行计划时发现全表扫描比索引查找更快，所以会不使用索引；

• 确保mysql版本5.0以上，且查询优化器开启了index_merge_union=on, 也就是变量optimizer_switch里存在index_merge_union且为on。

2.3、负向条件查询不能使用索引

负向条件有：!=、<>、not in、not exists、not like 等。

例如下面SQL语句：

select * from doc where status != 1 and status != 2;

可以优化为 in 查询：

select * from doc where status in （0,3,4）;

2.4、联合索引最左前缀原则

如果在(a,b,c)三个字段上建立联合索引，那么他会自动建立 a| (a,b) | (a,b,c)组索引。联合索引遵循最左前缀原则（必须带着最左边的列做条件才能命中索引），且从出现范围开始索引失效；

create index mix_ind on 表名 (id,name,email);

select * from 表名 where id = 123;  # 命中索引

select * from 表名 where id > 123;  # 不命中索引，因为出现范围

select * from 表名 where id = 123 and name = 'pamela';  # 命中索引

select * from 表名 where id > 123 and name = 'pamela';  # 不命中索引，因为出现范围

select * from 表名 where id = 123 and email = 'pamela@123.com';  # 命中索引

select * from 表名 where email = 'pamela@123.com';  # 不命中索引，因为条件中没有id

select * from 表名 where name='pamela' and email = 'pamela@123.com';  # 不命中索引，因为条件中没有id

登录业务需求，SQL语句如下：

select uid, login_time from user where login_name=? andpasswd=?

可以建立(login_name, passwd)的联合索引。因为业务上几乎没有passwd 的单条件查询需求，而有很多login_name 的单条件查询需求，所以可以建立(login_name, passwd)的联合索引，而不是(passwd, login_name)。

2.5、不能使用索引中范围条件右边的列（范围列可以用到索引），范围列之后列的索引全失效。

范围条件有：<、<=、>、>=、between等。

索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

假如有联合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 则使用不到索引。

select * from employees.titles where emp_no < 10010&#39; and title=&#39;Senior Engineer&#39;and from_date between &#39;1986-01-01&#39; and &#39;1986-12-31&#39;

2.6、不要在索引列上面做任何操作（计算、函数），否则会导致索引失效而转向全表扫描。

例如下面的 SQL 语句，即使 date 上建立了索引，也会全表扫描：

select * from doc where YEAR(create_time) <= &#39;2016&#39;;

可优化为值计算，如下：

select * from doc where create_time <= &#39;2016-01-01&#39;;

比如下面的 SQL 语句：

select * from order where date < = CURDATE()；

可以优化为：

select * from order where date < = &#39;2018-01-2412:00:00&#39;;

2.7、强制类型转换会全表扫描

字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。

如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引。

select * from user where phone=13800001234

可以优化为：

select * from user where phone='13800001234';

2.8、更新十分频繁、数据区分度不高的列不宜建立索引

更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。

“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。

2.9、利用覆盖索引来进行查询操作，避免回表，减少select * 的使用

覆盖索引：查询的列和所建立的索引的列个数相同，字段相同。

被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

例如登录业务需求，SQL语句如下。

select uid, login_time from user where login_name=? and passwd=?

可以建立(login_name, passwd, login_time)的联合索引，由于 login_time 已经建立在索引中了，被查询的 uid 和 login_time 就不用去 row 上获取数据了，从而加速查询。

2.10、索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时，尽量使用not null 约束以及默认值。

2.11、is null, is not null无法使用索引

2.12、如果有order by、group by的场景，请注意利用索引的有序性

order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort 的情况，影响查询性能。

例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。

如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;，索引(a,b)无法排序。

2.13、使用短索引（前缀索引）

对列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果该列在前10个或20个字符内，可以做到既使得前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作，减少索引文件的维护开销。可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度。

但缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引。

不过很多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

2.14、利用延迟关联或者子查询优化超多分页场景

MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

示例如下，先快速定位需要获取的id段，然后再关联:

select a.* from 表1 a,(select id from 表1 where 条件 limit100000,20 ) b where a.id=b.id；

2.15、如果明确知道只有一条结果返回，limit 1 能够提高效率

比如如下 SQL 语句：

select * from user where login_name=?;

可以优化为：

select * from user where login_name=? limit 1

自己明确知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动。

2.16. 3 つ以上のテーブルを結合しないことをお勧めします

結合する必要があるフィールドのデータ型は一貫している必要があります。複数のテーブルをクエリするときは、関連フィールドにはインデックスが必要です。

例: 左結合は左側によって決定されます。左側にデータが存在する必要があるため、右側がキーポイントになります。インデックスを構築するには、右側を構築する必要があります。もちろん、インデックスが左側にある場合は、右結合を使用できます。

2.17. 5

2.18. SQL パフォーマンス最適化の説明を入力してください: 少なくとも範囲レベルに達するようにしてください。 、要件は参照レベルです。const にできる場合は、それが最適です。

consts: 単一テーブル内に一致する行 (主キーまたは一意のインデックス) が 1 つだけあり、データは最適化フェーズ中に読み取られます。

ref: 通常のインデックス (Normal Index) を使用します。

range: インデックスに対して範囲検索を実行します。

type=index の場合、すべてのインデックス物理ファイルがスキャンされるため、非常に時間がかかります。

2.19. ビジネスにおいて独自の特性を持つフィールドは、複数のフィールドの組み合わせであっても、一意のインデックスに組み込む必要があります。固有のインデックスは挿入速度に影響します。この速度の低下は無視できますが、検索速度の向上は明らかです。また、アプリケーション層で非常に徹底した検証制御を行ったとしても、マーフィーの法則に従い、一意のインデックスが存在しない限り、ダーティデータが生成されることは避けられません。

2.20. インデックスを作成するときは、次の誤解を避けてください。

インデックスは多ければ多いほど良いため、クエリが必要だと思われる場合は、インデックスを作成するだけです。インデックスはスペースを消費し、更新や新規追加の速度を大幅に低下させるため、多すぎるよりも少ない方が望ましいです。

一意のインデックスに抵抗し、ビジネスの一意性はアプリケーション層で「最初にチェックしてから挿入する」方法によって解決する必要があると信じています。

システムを理解せずに最適化を開始する、時期尚早な最適化。

3. インデックスを使用する場合と使用しない場合

---

3.1. インデックスを使用する

主キーにより、一意のインデックスが自動的に作成されます。

• WHERE ステートメントまたは ORDER BY ステートメントのクエリ条件として頻繁に使用される列には、インデックスを作成する必要があります。

• クエリ内の他のテーブルに関連付けられたフィールド、外部キー関係にはインデックスが付けられます。

• min()、max() などの集計関数など、集計関数でよく使用される列にはインデックスを付ける必要があります。
#3.2. インデックスを使用しない

## 頻繁に追加、削除、または変更される列にはインデックスを作成しないでください。

• インデックスが作成されていない重複列が多数あります。

• テーブル レコードが少なすぎる場合は、インデックスを作成しないでください。データが少ないため、すべてのデータをクエリする方が、インデックスを走査するよりも時間がかからない可能性があります。最適化効果が得られない可能性があります。

• 推奨学習:

  mysql ビデオ チュートリアル

以上がMySQL インデックス最適化のナレッジ ポイントを要約して整理するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。