MySQL はインデックスを使用してクエリ最適化を実装します_MySQL

インデックスの目的はクエリの効率を向上させることであり、これは辞書に例えることができます。「mysql」という単語を検索したい場合は、必ず m の文字を見つけてから、y の文字を下から順に見つける必要があります。 、残りの SQL を見つけます。索引がないと、必要な内容を見つけるためにすべての単語に目を通さなければならない場合があります。

1.インデックスの利点

インデックスのない 3 つのテーブル t1、t2、t3 があり、各テーブルにはそれぞれデータ列 i1、i2、i3 が含まれており、各テーブルには 1 から 1000 までのシリアル番号を持つ 1000 のデータ行が含まれているとします。特定の値に一致するデータ行の組み合わせを検索するクエリは次のようになります:

リーリー

このクエリの結果は 1000 行になるはずで、各データ行には 3 つの等しい値が含まれます。インデックスを使用せずにこのクエリを処理した場合、すべてのテーブルをスキャンすることなく、どの行にどの値が含まれているかを知る方法はありません。したがって、WHERE 条件に一致するレコードを見つけるには、すべての組み合わせを試す必要があります。可能な組み合わせの数は 1000 x 1000 x 1000 (10 億!) で、これは一致するレコードの数の 100 万倍です。これでは多くの作業が無駄になります。この例は、インデックスを使用しない場合、テーブル内のレコードが増加するにつれて、これらのテーブル間の結合の処理にかかる時間が速くなり、パフォーマンスが低下することを示しています。インデックスを使用するとクエリを次のように処理できるため、これらのデータ テーブルにインデックスを付けることで速度を大幅に向上させることができます。

1．テーブル t1 の最初の行を選択し、そのデータ行の値を表示します。

２．テーブル t2 のインデックスを使用して、t1 の値に一致するデータ行を直接見つけます。同様に、テーブル t3 のインデックスを使用して、テーブル t2 の値に一致するデータ行を直接見つけます。

３．テーブル t1 の次の行を処理し、前のプロセスを繰り返します。この操作は、t1 内のすべてのデータ行がチェックされるまで実行されます。

この場合でも、テーブル t1 でフル スキャンを実行しますが、t2 と t3 でインデックス ルックアップを実行して、これらのテーブルから直接行を取得できます。この方法で上記のクエリを実行すると、理論的には 100 万倍高速になります。もちろん、この例は結論を導くために人為的に設定されたものです。ただし、これによって解決される問題は現実のものであり、インデックスのないテーブルにインデックスを追加すると、多くの場合、パフォーマンスが大幅に向上します。
-

2. インデックスのコスト

まず、インデックスにより検索は高速化されますが、挿入と削除、およびインデックス付きデータ列の値の更新は遅くなります。つまり、インデックスにより、書き込みを伴うほとんどの操作が遅くなります。この現象が発生する理由は、レコードを書き込むときに、データ行を書き込む必要があるだけでなく、すべてのインデックスも変更する必要があるためです。データ テーブルのインデックスが増えるほど、より多くの変更を行う必要があり、平均パフォーマンスの低下が大きくなります。この記事の「データを効率的に読み込む」セクションでは、これらの現象を詳しく調べ、対処方法を説明します。

第二に、インデックスはディスク領域を消費し、複数のインデックスはそれに応じてより多くのディスク領域を消費します。これにより、データ テーブルのサイズ制限に早く到達する可能性があります:

・MyISAMテーブルの場合、頻繁にインデックスを作成すると、インデックスファイルがデータファイルよりも早く上限に達する可能性があります。

・データとインデックス値を同じファイルに一緒に保存する BDB テーブルの場合、インデックスを追加すると、そのようなテーブルはファイルの最大制限に早く到達します。

・InnoDB の共有テーブルスペースに割り当てられたすべてのテーブルは、同じ共通スペース プールを使用するために競合するため、インデックスを追加するとテーブルスペース内のストレージがより早く使い果たされます。ただし、MyISAM テーブルや BDB テーブルで使用されるファイルとは異なり、InnoDB 共有テーブルスペースは複数のファイルを使用するように構成できるため、オペレーティング システムのファイル サイズによって制限されません。追加のディスク領域が利用できる限り、新しいコンポーネントを追加してテーブル領域を拡張できます。

別のテーブルスペースを使用する InnoDB テーブルは、そのデータとインデックス値が単一のファイルに保存されるため、BDB テーブルと同じ制約を受けます。

これらの要素が実際に意味することは、クエリの実行を高速化するために特別なインデックスが必要ない場合は、インデックスを作成しないでください。

3. インデックスを選択します

インデックス付けの構文はすでに知っていると仮定しますが、その構文ではデータテーブルにどのようにインデックスを付ける必要があるのか​​がわかりません。そのためには、データ テーブルがどのように使用されるかを考慮する必要があります。このセクションでは、インデックス作成の候補となるデータ列を特定する方法と、インデックスを最適に構築する方法について説明します。

検索、並べ替え、グループ化に使用されるインデックス データ列は、出力表示だけを目的としたものではありません。つまり、インデックス付けに最適なデータ列は、WHERE 句、join 句、ORDER BY、または GROUP BY 句にあるデータ列です。出力データ列リストに SELECT キーワードの後に​​のみ表示されるデータ列は、適切な候補列ではありません:

リーリー

もちろん、表示されるデータ列とWHERE句で使用するデータ列が同じであっても構いません。ここで重要なのは、出力リスト内のデータ列は本質的にインデックス付けの候補としては適切ではないということです。

　　Join子句或WHERE子句中类似col1 = col2形式的表达式中的数据列都是特别好的索引备选列。前面显示的查询中的col_b和col_c就是这样的例子。如果MySQL能够利用联结列来优化查询，它一定会通过减少整表扫描来大幅度减少潜在的表－行组合。

　　考虑数据列的基数（cardinality）。基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是4。索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数据列用于记录性别（只有”M”和”F”两种值），那么索引的用处就不大。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是”30%”。现在查询优化器更加复杂，把其它一些因素也考虑进去了，因此这个百分比并不是MySQL决定选择使用扫描还是索引的唯一因素。

　　索引较短的值。尽可能地使用较小的数据类型。例如，如果MEDIUMINT足够保存你需要存储的值，就不要使用BIGINT数据列。如果你的值不会长于25个字符，就不要使用CHAR(100)。较小的值通过几个方面改善了索引的处理速度：

　　· 较短的值可以更快地进行比较，因此索引的查找速度更快了。

　　· 较小的值导致较小的索引，需要更少的磁盘I/O。

　　· 使用较短的键值的时候，键缓存中的索引块（block）可以保存更多的键值。MySQL可以在内存中一次保持更多的键，在不需要从磁盘读取额外的索引块的情况下，提高键值定位的可能性。

　　对于InnoDB和BDB等使用聚簇索引（clustered index）的存储引擎来说，保持主键（primary key）短小的优势更突出。聚簇索引中数据行和主键值存储在一起（聚簇在一起）。其它的索引都是次级索引；它们存储主键值和次级索引值。次级索引屈从主键值，它们被用于定位数据行。这暗示主键值都被复制到每个次级索引中，因此如果主键值很长，每个次级索引就需要更多的额外空间。

　　索引字符串值的前缀（prefixe）。如果你需要索引一个字符串数据列，那么最好在任何适当的情况下都应该指定前缀长度。例如，如果有CHAR(200)数据列，如果前面10个或20个字符都不同，就不要索引整个数据列。索引前面10个或20个字符会节省大量的空间，并且可能使你的查询速度更快。通过索引较短的值，你可以获得那些与比较速度和磁盘I/O节省相关的好处。当然你也需要利用常识。仅仅索引某个数据列的第一个字符串可能用处不大，因为如果这样操作，那么在索引中不会有太多的唯一值。

　　你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀。

　　使用最左（leftmost）前缀。建立多列复合索引的时候，你实际上建立了MySQL可以使用的多个索引。复合索引可以作为多个索引使用，因为索引中最左边的列集合都可以用于匹配数据行。这种列集合被称为”最左前缀”（它与索引某个列的前缀不同，那种索引把某个列的前面几个字符作为索引值）。

　　假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列，因此它们也会自动地按照state/city和state次序排列。这意味着，即使你在查询中只指定了state值，或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：

state, city, zip
state, city
state

　　MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照city或zip来搜索，就不会使用到这个索引。如果你搜索给定的state和具体的ZIP代码（索引的1和3列），该索引也是不能用于这种组合值的，尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围。

　　不要过多地索引。不要认为”索引越多，性能越高”，不要对每个数据列都进行索引。我们在前面提到过，每个额外的索引都会花费更多的磁盘空间，并降低写操作的性能。当你修改表的内容的时候，索引就必须被更新，甚至可能重新整理。如果你的索引很少使用或永不使用，你就没有必要减小表的修改操作的速度。此外，为检索操作生成执行计划的时候，MySQL会考虑索引。建立额外的索引会给查询优化器增加更多的工作量。如果索引太多，有可能（未必）出现MySQL选择最优索引失败的情况。维护自己必须的索引可以帮助查询优化器来避免这类错误。

　　如果你考虑给已经索引过的表添加索引，那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。如果是这样的，不用增加索引，因为已经有了（例如，如果你在state、city和zip上建立了索引，那么没有必要再增加state的索引）。

　　让索引类型与你所执行的比较的类型相匹配。在你建立索引的时候，大多数存储引擎会选择它们将使用的索引实现。例如，InnoDB通常使用B树索引。MySQL也使用B树索引，它只在三维数据类型上使用R树索引。但是，MEMORY存储引擎支持散列索引和B树索引，并允许你选择使用哪种索引。为了选择索引类型，需要考虑在索引数据列上将执行的比较操作类型：

　　· 对于散列（hash）索引，会在每个数据列值上应用散列函数。生成的结果散列值存储在索引中，并用于执行查询。散列函数实现的算法类似于为不同的输入值生成不同的散列值。使用散列值的好处是散列值比原始值的比较效率更高。散列索引用于执行=或操作等精确匹配的时候速度非常快。但是对于查询一个值的范围效果就非常差了：

id < 30
weight BETWEEN 100 AND 150

　　· B树索引可以用于高效率地执行精确的或者基于范围（使用操作=、>、、!=和BETWEEN）的比较。B树索引也可以用于LIKE模式匹配，前提是该模式以文字串而不是通配符开头。

　　如果你使用的MEMORY数据表只进行精确值查询，散列索引是很好的选择。这是MEMORY表使用的默认的索引类型，因此你不需要特意指定。如果你希望在MEMORY表上执行基于范围的比较，应该使用B树索引。为了指定这种索引类型，需要给索引定义添加USING BTREE。例如：

CREATE TABLE lookup
(
id INT NOT NULL,
name CHAR(20),
PRIMARY KEY USING BTREE (id)
) ENGINE = MEMORY;

　　如果你希望执行的语句的类型允许，单个MEMORY表可以同时拥有散列索引和B树索引，即使在同一个数据列上。

　　有些类型的比较不能使用索引。如果你只是通过把值传递到函数（例如STRCMP()）中来执行比较操作，那么对它进行索引就没有价值。服务器必须计算出每个数据行的函数值，它会排除数据列上索引的使用。

　　使用慢查询（slow-query）日志来识别执行情况较差的查询。这个日志可以帮助你找出从索引中受益的查询。你可以直接查看日志（它是文本文件），或者使用mysqldumpslow工具来统计它的内容。如果某个给定的查询多次出现在”慢查询”日志中，这就是一个线索，某个查询可能没有优化编写。你可以重新编写它，使它运行得更快。你要记住，在评估”慢查询”日志的时候，”慢”是根据实际时间测定的，在负载较大的服务器上”慢查询”日志中出现的查询会多一些。

*4.建索引的几大原则*

4.1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

4.2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

4.3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4.4. インデックス列は計算に参加できません。たとえば、from_unixtime(create_time) = '2014-05-29' の場合、そのインデックスは非常に単純です。 b+ ツリーはデータ テーブルのフィールドですが、取得するときに比較するすべての要素に関数を適用する必要があるため、明らかにコストがかかりすぎます。したがって、ステートメントは create_time = unix_timestamp('2014-05-29');

のように記述する必要があります。

4.5. インデックスを可能な限り拡張し、新しいインデックスを作成しないでください。たとえば、テーブル内に a のインデックスがすでに存在し、(a, b) のインデックスを追加したい場合は、元のインデックスを変更するだけで済みます。

上記は、クエリの最適化を実現するためのインデックスの使用についてエディターが紹介したものです。ご質問があれば、メッセージを残してください。エディターがすぐに返信します。ウェブサイトをご利用いただき、誠にありがとうございます。