MySQL InnoDB インデックスの導入と最適化

本文:

1. まず、インデックスとは何ですか?

インデックスはディレクトリとして変換され、検索したいデータの場所をすばやく見つけるために使用されます。たとえば、データベースを本に例えますが、本の目次はインデックスです。本の中で興味深い内容を見つけたい場合、通常は本を全部読んで内容を確認することはありません。 、本の目次から内容の章のページ番号を見つけて、最後に直接ページを開きます

データベース内のインデックスを見てみましょう:
全テーブル スキャン VS インデックス スキャン
辞書を例に挙げます, フルテーブルスキャンとは、特定の単語を検索する場合、新華辞典を読んでから見つけたい単語を見つけることを意味します
そして、フルテーブルスキャンに相当するのがインデックス検索です。インデックス検索とは、検索する単語を見つけることを意味します。テーブルのインデックス部分に、探しているデータの特定の場所が必要です。次に、テーブルに移動して、見つけたいすべてのデータを見つけます

例: Student で Dev という名前の学生を見つけます。 table

左側のテーブル全体をスキャンします。最初の行は行 100008 の学生開発者の情報が見つかるまで行ごとにスキャンを開始し、このデータが返される場合があります。テーブル内に同じ名前の学生が存在するため、スキャンは終了しません。通常、データの一部については、すべてを返すかどうかを決定する前に、テーブル全体のデータを検索する必要があります。データ

右インデックススキャン: インデックス検索は、最初の文字が同じである場合は、D で始まる Dev を見つけます。最初の文字に従って 2 文字ずつソートして検索します。 ID が 100008 であることがわかり、テーブルに戻って ID 100008 のデータを見つけます

結論: したがって、インデックス (InnoDB に対応) のインデックス値は主キー ID に対応します

2。 find インデックスに対応する値

InnoDB エンジンは主に
(1)B+tree
(2) 二分探索法に基づいています

B+tree: B+tree にはルート ノード、ブランチ ノード、ページがありますツリー全体のノード、上位層 下位ノードの管理範囲はページノードの固有情報まで格納されます

二分探索法：B+ツリーに格納された各ノードの範囲に従って段階的に比較範囲を狭めて、最後にページ ノード内で必要な位置を見つけます

3. InnoDB テーブルがインデックス テーブルでもあることを紹介しましょう

上で示したように、InnoDB テーブルはクラスター化されたテーブルです。 InnoDB 自体は大規模なインデックス構成テーブルであり、主キーに従ってソートされた大規模なインデックスの B+ ツリー構造でもあります。InnoDB でインデックスを作成するテーブルに追加のフィールドを作成します

クラスター化インデックスとは、InnoDB を意味します。テーブル自体を指し、他のフィールドに従ってソートされたこれらのインデックスをセカンダリ インデックス (セカンダリ クラス) と呼びます

4. データベースにインデックスを作成する方法

MySQL には主に 2 つのタイプのインデックスがあります

1.

rreee

2. ジョイントインデックス

create index idx_name_age on tb_student(name,age);
#索引中先根据name排序，name相同的情况下根据age排序

五、索引维护

首先介绍下什么是索引维护？这是一个关乎性能的重要概念

如果索引所在字段发生了修改、删除、插入等操作，那么索引项就会发生变化，因此如果不能保证索引的有序，那么就不能索引的准确与效率，而索引的排序发生了变化的这个行为，我们称为索引维护

在insert/delete/update操作时，为了维护索引的排序，数据库会自动的完成索引项的维护，索引的排序，这些行为对用户是透明的，感觉不到的

在一个有索引的表中，创建它时，实际上还同时创建了索引排序的表，因此在DML中，插入等操作不再是普通的插入，MySQL将它封装成了一个事务，连着索引项的排序表一起操作

因此，我们应当严格控制表上的索引数量，否则容易影响数据库的性能

总结索引维护如下：

1、索引维护由数据库自动完成
2、插入/修改/删除每一个索引行都变成一个内部封装的事务
3、索引越多，事务越大，代价越高
4、索引越多，对表的插入和索引字段的修改就越慢

因此可以看出索引并非是越多越好，在工作中也要慎用，尤其对于写操作较为频繁的业务

六、如何正确的使用索引？

1、依据where查询条件建立索引

eg:
select a,b from tb_test where c = ?;
idx_c(c)   ->正确

select a,b from tb_test where c = ? and b = ?
idx_cd(c,d)  ->正确

2、根据排序order by ,group by , distinct 字段添加索引

eg:
select * from tb_test order by a;
select a,count(*) from tb_test group by a;
idx_a(a)  ->正确

select * from tb_test order by a,b;
idx_a_b(a,b)  ->正确

select * from tb_test order where c = ? by a;
idx_c_a(c,a)  ->正确

七、到底哪些字段适合创建索引？

1、字段值的重复程度，如图：

身份证号码由于基本上不可能重复，因此选择性非常好，而人的名字重复性较低，选择性也不错， 性别选择性较差，重复度非常高

2、选择性很差的字段通常不适合创建索引，但也有例外

 如：男女比例相仿的表中，性别不适合创建单列索引，如果走索引不如走全表扫描，
 因为走索引的I/O开销更大
    
 但如果男女比例极度不平衡，要查询的又是少数方,如：理工学校、IT公司等可以考虑使用索引

3、联合索引中选择性好的字段应该排在前面

select * from tab_a where gender=? and name=?
idx_name_gender(name,gender)   ->正确

4、联合索引可以为单列、复列查询提供帮助

idx_smp(a,b,c)
where a=?;                ->正确
where a=? and b=?;        ->正确
where a=? and c=?;        ->正确 （注：需要MySQL5.6版本以上；在5.5及以前版本，可以对a字段进行索引扫描，但c字段不行    ）
where a=? and b=? and c=? ->正确

5、合理创建联合索引，避免冗余

(a),(a,b),(a,b,c)      ->不可取
(a,b,c)                ->正确，可以覆盖前两个

八、再来看看如何在长字段上建立索引呢

首先，在较长的字段上建立索引是非常影响性能的，比如文章等超大varchar或者text字段，如果不是非建不可，一般不推荐，另外对InnoDB索引单字段（utf8）只能取前767bytes

那么如何处理长字段索引？

主要根据类型来分别处理：
1、Email类，可以建立前缀索引
mail_addr varchar(2048)
idx_mailadd(mail_addr(39))   -> 正确
解析：由于email邮件类型字段，一般后缀都有较大可能相同，如.com .cn等等，而前缀相同的可能性较低，且邮箱一般长度较短，因此可以建立前缀索引

2、住址类，分拆字段
home_addr varchar(2048)
idx_homeadd(home_addr(30))    ->错误，很可能前半段是相同的省市区街道名

province_add varchar(1024),city_add varchar(1024), district_add `varchar(1024),lolcal_add varchar(1024)    --建立联合索引或者单列索引 ->正确`

九、对核心SQL索引做覆盖扫描

对于最核心的SQL，我们可以考虑使用索引覆盖，什么是索引覆盖呢，下面是个例子

select name from tb_user where userid=?
key idx_uid_name(userid,name)   ->覆盖索引扫描

我们查询用户名这种操作频率非常高，而索引里面又存储了字段的值，因此在我们做查询时，name字段的值直接在索引中返回，而不需要回表;还有一个使用非常广泛的例子：用户登陆，我们可以将username password做覆盖索引，这样大大提高登陆验证的速度

因此覆盖索引覆盖就是将你要查询的字段和条件字段一起建立联合索引，这样的好处是不需要回表获取name字段，IO最小，速度块

十、哪些情况无法使用索引？

1、索引列进行数据运算或者函数运算

eg:
   where id+1=10;    ->错误，无法利用到索引
   where id=(10-1)   ->正确

   where year(id) < 2016      ->错误，无法利用到索引
   where col < &#39;2016-01-01&#39;   ->正确

2、未含复合索引的前缀字段

idx_abc(a,b,c)
where b=? and c=?   ->错误，无法利用到索引
正确的建立索引方式(b,c)

3、前缀通配符"_" "%"等

like '%ttt%'   ->错误，无法利用到索引
like "ttt%"    ->正确

4、where条件使用NOT,<>,!= 通常也无法使用到索引

5、字段类型不匹配

字段类型并不绝对匹配时，可能会导致无法使用索引
a int(11) ,idx_a(a)
where a = '123'   ->错误，可能导致未知的错误,这个跟编码有关系
where a = 123     ->正确

十一、利用索引做排序操作

以 idx_ab(a,b)索引为例
1、能使用上述索引进行排序的操作是：

order by a;
a = 3 order by b;
order by a,b;
order by a desc ,b desc;
a > 5 order by a;

2、不能使用索引帮助排序的查询

order by b; #没有使用到联合索引的第一个字段

a > 5 order by b;  #一旦前缀操作是一个range而非＝操作，那么就无法利用到索引，
这里 a>5无法利用索引，二联合索引的第一个字段未利用，
因此 order by b也无法利用索引查询

a in (1,3) order by b; #in里面的值没有建立索引，因此无法利用索引，a未用因此order by b也无法使用

order by a asc, b desc; #这里order by a esc是利用了索引，但是b desc未利用到，因为b要和a排序方式一致才可利用到索引

十二、如何确定一个查询有没有走索引，走了哪些索引？

MySQL中自带命令行工具 explain 来查看一个sql语句是否了索引

使用方式：

explain select * from tb_test;

关注的项：

1、type : 查询access的方式，表的连接类型
      
      index |  索引  
      full  |  全表扫描 
      ref   |  参照查询，也就是等值查询  
      range |  范围查询
2、key  : 本次查询最终选择使用哪个索引，NULL为未使用索引
3、key_len : 选择的索引使用的前缀长度或者整个长度
4、rows    : 查询逻辑扫描过的记录行数
5、extra   : 额外信息，主要是指fetch data的具体方式

总结：索引的本质还是提升我们查询数据库的速度，减少服务器I/O开销，提供更稳定快捷的服务