MySQL インデックスの最適化がインデックスの構築に適しているのはどのような状況ですか?

結論

1. where の後ろのフィルター フィールドにインデックスを作成し (select/update/delete の後ろの where はすべて適用されます)、インデックスを使用してフィルタリングの効率を向上させます。テーブル全体が必要です

2. をスキャンし、一意の要件を持つフィールドに一意のインデックスを追加して、クエリの効率を向上させます。見つかった場合は、

3. # を直接返すことができます##group by または order by の後にフィールドにインデックスを追加します。インデックスは並べ替えられているため、インデックスの確立は、クエリの前に並べ替えることと同じです (ここでは、結合インデックスの確立におけるフィールドの順序に注意する必要があります。特定のケースのシナリオと組み合わせることができます 7 学習)

4. DISTINCT (重複排除フィールド) の後のフィールドにインデックスを追加します。インデックスが確立されるため、同じデータが隣り合っています。をクリックすると、すぐに削除できます。操作を繰り返します。そうでない場合は、同じデータを見つけて重複排除操作を実行する必要がある場合があります。

5. 複数のテーブルを結合する場合は、接続されたフィールドにインデックスを作成します(小さなテーブルは大きなテーブルを駆動します)

6. 文字列の特定のプレフィックスを取得してインデックスを作成します (文字列全体をインデックスとして使用しないでください。そうしないと、多くのスペースが必要になります)

7. 頻繁に使用される列にインデックスを作成します (結合インデックスを構築できます。最も頻繁に使用されるフィールドは結合インデックスの一番左にある必要があります (左端の原則))

8. 区別性が高い場合 列にインデックスを作成します (すべてのキーが一意であるため、主キーの区別性が最も高くなります)

インデックス作成のシナリオ

シナリオ 1: where フィールドの背後でフィールドにインデックスを確立する

-- 描述：当where中有多个条件需要进行匹配的时候，那么可以创建联合索引，这样所有的条件都可以使用索引，大大提高了检索的效率
select * from student_info where student_id = 1;
-- 当然数据量比较大的时候给where后面的字段添加索引
create index student_id_index on student_info (student_id)

インデックスを追加するまでに、基本的にテーブル全体を走査するため、0.383 秒かかりました

インデックス追加後、0.001 秒かかり、インデックスが使用されました (ただし、インデックスの作成には一定の時間がかかります)

#頻繁にクエリを実行するビジネスでは、where でフィルターされたフィールドにインデックスを作成できます。where でフィルターされたフィールドが複数ある場合は、結合インデックスを作成することもできます。

シナリオ 2: フィールドに一意のインデックスを作成する一意制約のあるフィールド (ターゲットを検索し、検索を続行せずに戻ります)

select * from student_info where id = 1001;
-- 因为学号是唯一的，所以可以在学号这个字段上添加唯一所用
create index id_unique on student_info(id);

一意制約のあるフィールドでは、一意のインデックスを作成できます。ただし、一意のインデックスの確立は挿入操作に一定の影響を与えます (新しく追加したデータが既にテーブルに存在するかどうかを判断する必要がある場合）、一意のインデックスを確立することでクエリの効率が大幅に向上します。 1001 が見つかった場合、データベース内に 1001 に等しい ID を持つ別の学生が存在するかどうかを判断する必要はなく (コピーは 1 つだけあります)、情報を直接返すことができます。インデックスが確立されていない場合は、フル テーブル スキャンが行われます。は必須です

シナリオ 3: インデックスは、group by および order by のフィールドに作成されることがよくあります (インデックス自体が並べ替えられるため、クエリの前に並べ替えることと同じです)

select * from student_info order by name;
-- 这里就可以给name字段进行索引的添加
select * from student_info group by class_id;
-- 这里就可以给class_id字段添加索引

インデックス付けの前に、0.501 秒かかりました。すべてのデータはメモリ内で並べ替えられます。

##インデックス作成後、0.01 秒かかりました

シナリオ4: DISTINCT の後のフィールドにインデックスを追加します (インデックスはすでに同じフィールドを並べ替えており、重複排除の効率が高くなります)

select distinct(student_id) from student_info;
-- 这里就可以根据student_id字段建立索引
create index student_id_index on student_info;

インデックスが確立されている場合、デフォルトは次の値に基づきます。インデックス フィールドが昇順に配置すると、同じ値を持つフィールドが一緒に配置され、重複排除がシンプルかつ効率的になります。

シナリオ 5: 大きなテーブルの結合複数テーブル結合で結合フィールドを確立する インデックス

SELECT s.course_id,NAME,s.student_id,c.course_name
FROM student_info s JOIN course c
ON s.`course_id` = c.`course_id`
WHERE NAME = 'xiaoyuanhao';
-- 根据大表驱动小表的原则需要在student_info表的course_id字段上建立索引

インデックス作成前は 0.697 秒かかりました。インデックス作成なしの場合

インデックス作成後は 0.003 秒かかりました

小さなテーブル大きなテーブルを駆動する:

小さなテーブルを 1 つずつ走査し、大きなテーブルの接続フィールドにインデックスを付けることで、クエリを高速化できます。この場合、毎回、コース テーブルの course_id と学生の接続操作のために、student テーブルの course_id が取り出され、student テーブルで course_id のインデックスが作成されます。

シナリオ 6: 文字列のプレフィックスを使用してインデックスを作成します

create table shop(address varchar(120) not null);
alter table shop add index(address(12));
--这里只是对表中的address的前12个字符建立了索引，而不是整个字符串建立索引

理由プレフィックスインデックス:

#一部の文字列は非常に長いため、文字列全体にインデックスを付けると、インデックスに多くのスペースが必要になります

• 文字列全体を格納する必要があるため、データ項目が大きくなり、インデックスツリーの深さが深くなり、検索速度が低下します。インデックス 2 つの文字列は同じですが、主キーに基づくテーブルの戻り操作の効率は依然として比較的高いです

如何确定前缀索引中前缀的长度呢？（也就是如果前缀的长度太短，那么索引的区分度就很低，从多个字符串截取的前缀数据可能都是一样的，但是如果前缀索引的前缀过长，那么前缀索引的优点就消失了）

引入了区别度的概念，select count(distinct left(索引字段,前缀索引长度) / count(*) from xxx)，该值越接近1，那么区分度就越明显，那么该索引长度就是所求的前缀索引长度

场景七：在频繁使用的列上建立索引或联合索引（频繁使用的字段应该在索引的左侧）

select * from xiaoyuanhao where age = 18;
select * from xiaoyuanhao where age = 19 and sex = 'man';
select * from xiaoyuanhao where age = 10 and sex = 'man' and password = '123456';
-- 在这里实际上就可以建立age,sex,password的联合索引，只需要建立一个索引，这三个查询都是可以使用的
create index age_sex_password_index on xiaoyuanhao(age,sex,password);
select * from student_info group by class_id order by name;
-- 在这里可以建立class_id和name的联合索引，但是一定要注意索引的顺序，一定是要class_id在前，name在后，因为在select语句中执行的顺序是先group by 之后才是 order by 索引如果索引的字段顺序是相反的，那么就无法使用索引
create index class_id_name_index on student(class_id,name);

索引建立需要符合顺序的原因：

索引字段的顺序如果是错误的，那么索引就会失效，因为索引实际上是排好序的，如果索引建立的时候是现根据name排好序之后在根据class_id进行排序，那么在面对需要先根据class_id排序再根据name排序的业务就无法进行使用

补充：

在select * from xxx where age = 19 and sex = ‘man’ and password = '123456’这里索引建立的顺序不一定是(age,sex,password)因为在实际执行的过程中，优化器会优化执行步骤会按照索引的顺序进行查询，但是group by 和 order by的执行顺序是无法改变的，索引必须严格的按照顺序建立索引，否则索引失效

以上がMySQL インデックスの最適化がインデックスの構築に適しているのはどのような状況ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。