MySQLは効率的なインデックス例分析を確立する

この記事では、例を使用して、MySQL で効率的なインデックスを作成する方法について説明します。ご参考までに、詳細は以下の通りです。

理想的なインデックスを作成するにはどうすればよいでしょうか?

• クエリ頻度
• 差別
• インデックスの長さ
• フィールドを上書きする

差別

ユーザーが100万人いると仮定すると、性別は基本的に男性が50万人、女性が50万人なので、差別化は低いです。

ショート丈

インデックスの長さは、インデックス ファイルのサイズ、追加、削除、変更の速度に直接影響し、間接的にクエリ速度に影響します (より多くのメモリを占有します)。

高い識別力、短い長さ

質問: 識別度を高くし、長さを短くするとどうなるでしょうか?

回答：列内の値を左から右に切り取ることでインデックスを作成できます。

（1）カットが短いほど、繰り返しが多くなり、差別化が低くなり、インデックス効果が悪くなります。 （2）カットが長いほど、繰り返しが少なくなり、差別化が高くなり、インデックス効果が高くなりますが、影響が大きくなります。追加と削除が遅くなり、クエリ速度に影響します。

したがって、識別と長さの間でバランスを取る必要があります。従来の方法: 異なる長さにカットし、その識別力をテストします。

13,324 件のレコードを含む英語レベル 4 の語彙テーブルがあるとします。名前フィールドにインデックスを追加するにはどうすればよいでしょうか。

差別をどのように計算しますか?

単語の最初の重複しない数字を抽出します。

dictからcount(distinct left(name,1))を選択します

合計数量:

辞書からcount(*)を選択

区別: 一意の数/合計数、SQL ステートメントは次のようになります。

(select count(distinct left(name,1)) from dict) / (select count(*) from dict) を rate として選択します。

次に、次の手順に従って、他の長さに対応する識別を調べます。このグラフを見ると、長さが 11 の場合、繰り返し率はわずか 1% であることがわかります。11 ビットのインデックスを作成することを検討できます。

テーブル辞書を変更し、インデックス名 name(11) を追加します。

左接頭辞は区別が難しい

左プレフィックスが判別しにくい列のインデックスを作成するためのヒント

URL列など

http://www.baidu.com
http://www.web-bc.cn

列の最初の 11 文字が同じなので区別が困難です。この問題を解決するには、次の 2 つの方法を使用できます。

（１）列の内容を逆順に保存し、インデックスを作成する

moc.udiab.www//:ptth
nc.cb-bew.www//://ptth

このように左接頭辞は優れた識別力を持つ

（２）疑似ハッシュインデックス効果

urlとurl_hashの両方の列を保存する

#テーブルを作成する create table t10 (
id int 主キー、
url char(60) nullでないデフォルト ''
);
#t10値に挿入
(1,'http://www.baidu.com')、
（2、「http://www.sina.com」）、
(3,'http://www.sohu.com.cn')、
(4,'http://www.onlinedown.net')、
（5、「http://www.gov.cn」）；
# テーブル構造を変更し、urlcrc 列を追加します。alter table t10 add urlcrc int unsigned not null;

保存するときは、URLに対応するCRC32コードをデータベースに挿入し、urlcrcフィールドに従ってインデックスを作成します。検索するときは、ビジネスレイヤーで対応するURLをCRC32に変換して検索し、インデックスを使用できます。

crc の結果は 32 ビット int 符号なし数値なので、データが 40 億を超えると重複が発生しますが、それだけの価値はあります。(インデックスの長さは int4 バイトです)

複数列インデックス

複数列インデックスに関する考慮事項 - 列クエリの頻度、列の差別化、実際のビジネスシナリオとの組み合わせ

ecshop を例にとると、goods テーブルの cat_id と brand_id は複数列のインデックスです。差別化の観点から見ると、brand_id の方が差別化が高くなります。ただし、モールの実際のビジネスでは、顧客は一般的に最初に大きなカテゴリ -> 小さなカテゴリ -> ブランドの順に選択し、最終的に 2 つのインデックスを作成することを選択します。

(1) インデックス(cat_id,brand_id)
(2) インデックス(cat_id,shop_price)

（3）インデックス（cat_id、brand_id、shop_price）を追加することもできます。3つの冗長インデックス

しかし、（3）の最初の2つの列は（1）の最初の2つの列と同じなので、（1）を削除して2つのインデックスを作成できます。

index(cat_id,price)およびindex(cat_id,brand_id,shop_price);

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL インデックス操作スキルの概要」、「MySQL 共通関数の概要」、「MySQL ログ操作スキルの概要」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアド プロシージャ スキルの概要」、および「MySQL データベース ロック関連スキルの概要」。

この記事が皆様のMySQLデータベース設計に役立つことを願っています。