MySQL の複数列インデックスの最適化サンプル コードを共有する

クローラーによって取得されるデータの量が増加しているため、データベースとクエリ ステートメントは過去 2 日間で継続的に最適化されてきました。テーブル構造の 1 つは次のとおりです:

CREATE TABLE `newspaper_article` (
  `id` varchar(50) NOT NULL COMMENT '编号',
  `title` varchar(190) NOT NULL COMMENT '标题',
  `author` varchar(255) DEFAULT NULL COMMENT '作者',
  `date` date NULL DEFAULT NULL COMMENT '发表时间',
  `content` longtext COMMENT '正文',
  `status` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_status_date` (`status`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

ビジネス ニーズに従って、idx_status_dateが追加される > 以下の SQL を実行するときにインデックスに特に時間がかかります: idx_status_date 索引，在执行下面这个 SQL 时特别耗时：

SELECT id, title, status, date FROM article WHERE status > -2 AND date = '2016-01-07';

根据观察，每天新增的数据大概在2500条以内，本以为这里指定了具体某天的日期 '2016-01-07' ，实际需要扫描的数据量应该在2500条以内才对，但实际并非如此：

实际共扫描了185589条数据，远远高于预估的2500条，且实际执行时间都将近3秒钟：

这是为什么呢？

解决方案

将 idx_status_date (status, date) 改为 idx_status (status) 后，查看 MySQL 执行计划：

可以看到将多列索引改为单列索引后，执行计划要扫描的数据总量没有任何变化。结合多列索引遵循最左前缀原则，推测上面的查询语句只使用了 idx_status_date 最左边的 status 的索引。

翻了下《高性能MySQL》找到了下面这段话，证实了我的想法：

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询 WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23' ，这个查询只能使用索引的前两列，因为这里 LIKE 是一个范围条件（但是服务器可以把其余列用于其他目的）。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

因此，这里解决思路有两种：

• 可以通过使用多个等于条件来代替范围条件

• 修改 idx_status_date (status, date) 为索引 idx_date_status (date, status) ，并新建一个 idx_status 索引，即可达到同样的效果。

优化后的执行计划：

实际执行结果：

总结

当人们谈论索引的时候，如果没有特别指明类型，那么多半说的是 B-Tree 索引，它使用 B-Tree 数据结构来存储数据。我们使用术语“B-Tree”，是因为 MySQL 在 CREATE TABLE

CREATE TABLE People (
  last_name  varchar(50)    not null,
  first_name varchar(50)    not null,
  dob        date           not null,
  gender     enum('m', 'f') not null,
  key(last_name, first_name, dob)
);

観測によると、毎日追加される新しいデータの数は約 2500 以下であると考えられました。特定の日付が指定されていると思いました。ここでは '2016-01-07' ですが、スキャンする必要がある実際のデータ量は 2500 以内である必要がありますが、そうではありません:

実際にスキャンされたデータは合計 185,589 個で、これは推定 2500 個で、実際の実行時間はほぼ 3 秒でした:

• 

  
  

  これはなぜですか？

解決策

• idx_status_date (status, date) を idx_status (status) に変更した後、MySQL 実行プランを表示します:

  
  

• 複数の列がインデックス化されていることがわかります単一列インデックスに変更した後、実行プランによってスキャンされるデータの総量に変更はありません。複数列インデックスが左端の接頭辞の原則に従っていることと組み合わせると、上記のクエリ ステートメントは idx_status_date の左端の status のインデックスのみを使用すると推測されます。

  
  「高性能 MySQL」をめくって、次の段落を見つけました。これは私の考えを裏付けるものです:

• クエリ内の特定の列に範囲クエリがある場合、その右側のすべての列はインデックスを使用して検索できません最適化。たとえば、クエリ WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23' があります。このクエリはインデックスの最初の 2 列のみを使用できます。ここでは LIKE が範囲条件であるためです (ただし、サーバーは残りの列を他の目的に使用できます)。範囲クエリ列の値の数が制限されている場合は、複数の等価条件を使用して範囲条件を置き換えることができます。

  
  

  したがって、ここには 2 つの解決策があります:

• 
  複数の等しい条件を使用して範囲条件を置き換えることができます

• idx_status_date (status, date) をインデックス として変更しますidx_date_status (date, status) を使用し、新しい idx_status インデックスを作成して、同じ効果を実現します。
  

最適化された実行計画:

• 実際の実行結果:

  🎜🎜🎜

  概要

  🎜人々がインデックスについて話すとき、タイプを指定しない場合は、おそらく B-Tree インデックスについて話しているでしょう。 B-Tree データ構造を使用してデータを保存します。 「B ツリー」という用語を使用するのは、MySQL も CREATE TABLE やその他のステートメントでこのキーワードを使用するためです。ただし、基礎となるストレージ エンジンは異なるストレージ構造を使用することもあります。 InnoDB は B+Tree を使用します。 🎜次のデータ テーブルがあるとします。 🎜rrreee🎜 B ツリー インデックスは、次の種類のクエリに対して有効です🎜🎜🎜🎜 完全な値の一致🎜 完全な値の一致とは、インデックス内のすべての列との一致を指します。上の表のを使用できます。 1960-01-01 生まれの Cuba Allen という名前の人を検索します。 🎜🎜🎜🎜左端の接頭辞と一致🎜 上の表のインデックスは、Allen という姓を持つすべての人々を検索するために使用できます。つまり、インデックスの最初の列のみが使用されます。 🎜🎜🎜🎜列の接頭辞と一致する🎜 列の値の先頭のみと一致します。たとえば、上の表のインデックスを使用すると、姓が J で始まるすべての人々を検索できます。ここではインデックスの最初の列のみが使用されます。 🎜🎜🎜🎜一致範囲の値🎜 たとえば、上記の表のインデックスを使用して、Allen と Barrymore の間の姓を持つ人々を検索できます。ここではインデックスの最初の列のみが使用されます。 🎜🎜🎜🎜特定の列に完全一致し、別の列に範囲一致する🎜 上記の表のインデックスは、姓が Allen で、名前が文字 K で始まるすべての人々 (Kim、Karl など) を検索するために使用することもできます。 。）。つまり、最初の列 last_name は完全に一致し、2 番目の列 first_name は範囲と一致します。 🎜🎜🎜🎜インデックスのみにアクセスするクエリ🎜 Bツリーは通常、「インデックスのみにアクセスするクエリ」をサポートできます。つまり、クエリはデータ行にアクセスせずにインデックスにアクセスするだけで済みます。 🎜🎜🎜🎜B-Treeインデックスのいくつかの制限🎜🎜🎜🎜インデックスの左端の列に従って検索を開始しないと、インデックスを使用できません。たとえば、上の表のインデックスは、どちらの列も左端のデータ列ではないため、Bill という名前の人を検索するのに使用することはできません。また、特定の誕生日を持つ人を検索するのにも使用できません。同様に、姓が特定の文字で終わる人を見つける方法はありません。 🎜

• インデックス内の列をスキップすることはできません。つまり、上の表のインデックスを使用して、姓が Smith で特定の日に生まれた人を検索することはできません。名前 (first_name) を指定しない場合、MySQL はインデックスの最初の列のみを使用できます。

• クエリ内の特定の列に範囲クエリがある場合、その右側にあるすべての列はインデックスの最適化を使用して検索できません。たとえば、WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23' ，这个查询只能使用索引的前两列，因为这里 LIKE が範囲条件であるクエリがあります (ただし、サーバーは残りの列を他の目的に使用できます)。範囲クエリ列の値の数が制限されている場合は、複数の等価条件を使用して範囲条件を置き換えることができます。

以上がMySQL の複数列インデックスの最適化サンプル コードを共有するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。