MySQL インデックスは制御下にあります

この記事は、 mysql に関する関連知識を提供します。主に、論理関数によるインデックスの分割、物理実装による分割、フィールドによる個別のインデックス タイプの問題など、mysql インデックスに関連する問題を紹介します。数の割り算など、皆さんのお役に立てれば幸いです。

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

SQL の最適化において、インデックス作成はクエリの効率を向上させる重要な部分です。質的には飛躍的ですが、インデックスは万能ではなく、不合理なインデックス設計はクエリの効率を低下させる可能性さえあります。

インデックス定義 インデックスは、SQL がデータを効率的に取得できるように特別に設計されたデータ構造です。一般的な例として、インデックスは本の目次に似ており、特定の値を検索して検索することで、データ クエリの効率が大幅に向上します。実際、インデックスもテーブルであり、このテーブルは主キーとインデックス フィールドを保存し、エンティティ テーブルのレコードを指します (ポインタと同様)。

インデックスの長所と短所 利点 インデックスを使用すると、サーバーがスキャンする必要があるデータの量が大幅に削減されます インデックスはサーバーがソートや一時テーブルを回避するのに役立ちます インデックスはランダム IO をシーケンシャル IO に変えることができます インデックスは InnoDB (インデックスの行レベルのロックをサポート)、InnoDB にとって非常に重要です。アクセスする必要があるタプルのみにアクセスします。 ロックとインデックス付けにより、InnoDB がアクセスするタプルの数を減らすことができます。クエリでインデックスを使用できない場合、MySQL は実際に必要かどうかに関係なく、テーブル全体のスキャンを実行し、すべてのタプルをロックします。 欠点 インデックスによりクエリ速度は大幅に向上しますが、テーブルの更新速度も低下します。テーブルを更新するとき、MySQL はデータを保存するだけでなく、インデックス ファイルも保存する必要があるためです。したがって、非常に頻繁に更新されるフィールドにインデックスを使用することは一般的に推奨されません。 インデックスを作成すると、ディスク領域が占有されます。 データ列に多くの繰り返しコンテンツが含まれている場合、そのインデックス効果は非常に低くなります。このプロパティはインデックスの選択性と呼ばれます。つまり、インデックスの総数に対する一意のインデックス値の比率です。データテーブル内のレコード。インデックスの選択性が高いほど、クエリ効率も高くなります。たとえば、性別フィールドにインデックスを作成した場合、100 万件のデータに対して男性と女性の可能性は 2 つしかありません。インデックスの選択性は 500,000 分の 1 であり、インデックス作成の効果は非常に低くなります。 非常に小さなテーブルの場合、インデックスは意味がありませんが、大規模なテーブルの場合は、ほとんどの場合、単純な全テーブル スキャンの方が効率的です。 したがって、最も頻繁にクエリされ、最も頻繁に並べ替えられるデータ列のみにインデックスを付ける必要があります。 MySQL の同じデータ テーブル内のインデックスの総数は 16 に制限されています。

インデックスの種類 関数ロジックごとに分けられます 関数ロジックから分かれたインデックスは、主に通常インデックス、一意インデックス、主キーインデックス、全文インデックスに分かれます

通常のインデックス 最も基本的なインデックスであり、制限はありません。通常のインデックス (キーワード KEY または INDEX で定義されたインデックス) の唯一のタスクは、データへのアクセスを高速化することです。したがって、インデックスは、クエリ条件 (WHERE 列 = ...) または並べ替え条件 (ORDER BY 列) で最も頻繁に出現するデータ列に対してのみ作成する必要があります。

通常のインデックスを作成するには 3 つの方法があります。

# 创建索引CREATE INDEX idx_username ON user_tbl(username);# 对于字符串字段，可以手动指定长度，如 user_tbl(username(5))，表示只用前五个字符来做索引，可以进一步加快查询效率，索引长度要小于字段长度# 修改表结构ALTER TABLE user_tbl ADD INDEX idx_username (username)# 创建表的时候直接指定，如CREATE TABLE user_tbl( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX idx_username (username) ); インデックスの削除

DROP INDEX idx_username ON user_tbl; インデックスの表示

SHOW INDEX FROM user_tbl; 一意のインデックス これは、前の通常のインデックスと似ていますが、次の点が異なります。 : 通常のインデックスでは、インデックス付きデータ列に重複した値を含めることができます。一意のインデックス列の値は一意である必要がありますが、NULL 値も許可されます。複合インデックスの場合、列値の組み合わせは一意である必要があります。

一意のインデックスの作成は、通常のインデックスと同様です。

#创建索引 CREATE UNIQUE INDEX idx_username ON user_tbl(username);

# 修改表结构 ALTER TABLE user_tbl ADD UNIQUE idx_username (username)

# 创建表的时候直接指定 CREATE TABLE user_tbl( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE idx_username (username) ); 主キー インデックス これは、特別な一意のインデックスです。 null 値を許可します。テーブルには主キーを 1 つだけ持つことができ、主キーは通常、テーブルの作成時に同時に作成されます。

CREATE TABLE user_tbl( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); これに似ているのが外部キー インデックスです。外部キー フィールドに外部キー制約が定義されている場合、MySQL は最も効率的な方法で管理できるように内部インデックスを定義します。外部キーを使用する制約。

フルテキスト インデックス 前の記事 MySQL の基本構文では、最初に LIKE % を使用するとインデックスが無効になり、その後、あいまい検索 (LIKE '%hello%' など) が必要な場合は、フルテキスト インデックスを使用する必要があります。Innodb はバージョン 5.6 以降でのみフルテキスト インデックスをサポートしていることに注意してください。

全文インデックスの作成と削除:

# 创建的两种方法 CREATE FULLTEXT INDEX idx_name ON tbl_name(field_name); ALTER TABLE tbl_name ADD FULLTEXT INDEX idx_name(field_name);

# 删除的两种方法 DROP INDEX idx_name ON tbl_name; ALTER TABLE tbl_name DROP INDEX idx_name; 完全あいまい一致に全文インデックスを使用する構文は次のとおりです:

SELECT XXX FROM tbl_name WHERE match(field_name) against('xxx'); # 比如对 user_tbl 的 user_name 字段加了全文索引 # 查询结果等效于 SELECT user_name, user_id FROM user_tbl WHERE user_name LIKE '%hello%'; SELECT user_name, user_id FROM user_tbl WHERE match(user_name) against('hello'); Explain を使用して確認すると、次のことができます。全文インデックスが有効であることがわかります。

物理実装による分割 物理実装による分割は、通常、クラスター化インデックスと非クラスター化インデックスに分類できます。

聚集索引（clustered index） 存储内容是按照聚集索引排序的，聚集索引的顺序和行记录的顺序一致,一张表只能有一个聚集索引。聚集索引的叶子节点直接储存聚集索引指向的内容，因此查询的时候只需要进行一次查找。

聚集索引在创建主键时自动生成，如果没有主键，则根据第一个不为空的唯一索引自动生成，如果还没有，则自动生成一个隐式的聚集索引。

需要注意的是，在进行查询操作的时候，聚集索引的效率更高，因为少了一次查找；但是进行修改操作的时候，效率比非聚集索引低，因为直接修改了数据内容，为了标准数据内容的顺序和聚集索引顺序一致，会对数据页重新排序。

非聚集索引（non-clustered index） 非聚集索引虽然索引项是顺序存储的，但是索引项对应的内容是随机存储的，系统会维护单独的索引表来存储索引。

非聚集索引的叶子节点存储的是数据的地址，查询非聚集索引的时候，系统会进行两次查找，先查找索引，再查找索引对应位置的数据。因此非聚集索引也叫二级索引或者辅助索引。

按字段个数划分 按字段个数可以把索引分为单一索引和联合索引。

单一索引 索引字段只有一列时为单一索引，上述所有索引都是单一索引。

联合索引 将多个字段组合在一起创建的索引叫联合索引。如下：

ALTER TABLE user_tbl ADD INDEX idx_name_city_age (username,city,age); 最左匹配原则 建立这样的联合索引，其实是相当于分别建立了下面三组联合索引：

usernname,city,age usernname,city usernname 为什么没有 city，age 这样的联合索引呢？这是因为MySQL联合索引的 最左匹配原则 ，只会按照最左优先的顺序进行索引匹配，也就是说，(x,y,z) 和 (z,y,x) 是不同的索引，即使是使用联合索引中的字段查询，联合索引也有可能失效。

对于 (x,y,z)，只有在以下查询条件联合索引会生效：

WHERE x = 1WHERE x = 1 AND y = 1WHERE x = 1 AND y = 1 AND z = 1 对于其他情况，比如 WHERE y = 1 、 WHERE y = 1 AND z = 1 等，就不会匹配联合索引，索引失效，注意对于 WHERE x = 1 AND z = 1 ，联合索引会对 x 生效，但是对 z 不生效。

可以扩展了解一下，理论上最左匹配原则中索引对 where 中子句的顺序也是敏感的，但是由于MySQL的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以实际上 where 子句顺序不影响索引的效果。

要注意的是，如果联合索引查询过程中有范围查询，就会停止匹配，比如下面的语句中， z 字段不能使用到索引：

WHERE x = 1 AND y > 2 AND z = 3 顺便提一下，可以用 explain 命令来查看在某个查询语句中索引是否生效，具体用法请参考官网文档。

如果分别在 x, y, z 上建立单列索引，让该表有3个单列索引，索引效率也会大不一样，在联合索引生效的情况下，单个索引的效率远远低于联合索引。这是由 MySQL 查询优化器的执行顺序决定的，在执行一条查询 sql 时，针对索引的选择大致有如下步骤：

MySQL 优化器根据搜索条件，找出所有可能使用的索引 计算全表扫描的代价 计算使用不同索引执行查询的代价 对比各种执行方案的代价，找出成本最低的那一个 因此，虽然有多个单列索引，但 MySQL 只能用到其中的那个系统认为似乎是最有效率的，其他的就会失效。

按索引结构划分 不同的 mysql 数据引擎支持不同结构的索引，按结构划分，常用的索引为 B+树索引、Hash 索引、FULLTEXT索引 等，将在下一篇文章 MySQL 索引结构 中介绍。

使用总结 接下来我们来简单总结一下在什么场景下推荐使用索引。

推荐使用 WHERE, GROUP BY, ORDER BY 子句中的字段

多个单列索引在多条件查询是只会有一个最优的索引生效，因此多条件查询中最好创建联合索引。

联合索引的时候必须满足最左匹配原则，并且最好考虑到 sql 语句的执行顺序，比如 WHERE a = 1 GROUP BY b ORDER BY c , 那么联合索引应该设计为 (a,b,c) ，因为在上一篇文章 MySQL 基础语法 中我们介绍过，mysql 查询语句的执行顺序 WHERE > GROUP BY > ORDER BY。

多张表 JOIN 的时候，对表连接字段创建索引。

SELECT にインデックスにないフィールドがある場合、条件を満たす主キー値が最初にインデックスを介してクエリされ、次に SELECT 内のすべてのフィールドがクエリされます。主キー Return to the table フィールドを使用するため、クエリの効率に影響します。したがって、SELECT の内容が非常に少ない場合、テーブルが返されるのを避けるために、SELECT 内のすべてのフィールドをジョイント インデックスに追加できます。これがワイド インデックスの概念です。ただし、インデックス フィールドが多すぎると、インデックスの保存と維持のコストも増加することに注意してください。

#推奨されない、またはインデックスの失敗 #データ量が少ないテーブル

重複データが大量にあるフィールド

頻繁に更新されるフィールド

関数または式の計算がインデックス フィールドに使用されている場合、インデックスは無効です

innodb OR 条件では、すべての条件のインデックスが作成されません。インデックスが無効です。

「大なり小なり条件」 > , インデックスが有効になるかどうかは、ヒット数の割合によって決まります。ヒット数が多ければインデックスは有効になります。ヒット数が小さければ、インデックスは失敗します。 . は条件 != と等しくありません、インデックスが無効です

#LIKE 値は % で始まり、インデックスが無効です

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

MySQL インデックスは制御下にあります

関連記事