MySQL InnoDB 인덱스 도입 및 최적화

텍스트:

1. 먼저 인덱스가 무엇인지 알아보겠습니다.

색인은 디렉토리로 변환되어 찾고자 하는 데이터의 위치를 ​​빠르게 찾는 데 사용됩니다. 예를 들어 데이터베이스를 책과 비교하고 색인은 책의 목차입니다. 책에서 흥미로운 내용을 찾으려면 일반적으로 책 전체를 읽은 다음 내용을 확인하지 않습니다. , 대신 책의 목차를 통해 목차 장의 페이지 번호를 찾아 마지막으로 해당 페이지로 바로 넘어갑니다

데이터베이스의 색인을 살펴보겠습니다.
Full table scan VS index 스캔
사전을 예로 들면, 전체 테이블 스캔이란 특정 단어를 찾고 있는 경우 신화사전을 통해 읽은 다음 찾고 싶은 단어를 찾는 것을 의미합니다
그리고 전체 테이블에 대한 대응물 테이블 스캔은 인덱스 검색입니다. 인덱스 검색은 테이블의 인덱스 부분에서 찾고자 하는 데이터의 특정 위치를 찾은 후, 해당 테이블로 가서 찾고 싶은 데이터를 모두 찾아내는 것입니다

예: 학생 테이블에서 Dev라는 학생을 찾습니다. Student

왼쪽 전체 테이블 스캔: 첫 번째 행부터 시작하여 한 행씩 스캔해야 합니다. 학생 Dev에 대한 100008개 행의 정보를 찾은 다음 이 데이터를 반환합니다. 그러나 테이블에 동일한 이름을 가진 학생이 있을 수 있으므로 일반적으로 전체 테이블에서 데이터 조각을 찾는 것은 완료되지 않습니다. 스캔, 모든 데이터를 반환할지 여부를 결정하기 전에 데이터의 전체 테이블을 순회해야 합니다

오른쪽의 인덱스 스캔: 인덱스 검색은 첫 글자를 기준으로 D로 시작하는 Dev를 찾는 것입니다. 첫 번째 문자가 동일하면 두 번째 문자를 기준으로 유추하여 ID가 ​​100008임을 찾은 다음 테이블로 돌아가서 ID를 찾습니다.

결론 : 따라서 InnoDB에 해당하는 인덱스의 인덱스 값은 기본키 ID에 해당합니다

2. 인덱스에 해당하는 값을 찾는 방법

InnoDB 엔진은 주로 다음을 기반으로 합니다.
(1)B+트리
(2) 이진 검색 방법

B+트리: B+트리에는 루트 노드, 분기 노드 및 For 페이지가 있습니다. 상위 레이어는 페이지 노드의 특정 정보가 나올 때까지 하위 레이어 노드의 관리 범위를 저장합니다.

이진 검색 방법: B+ 트리에 저장된 각 노드의 범위에 따라 비교하여 점차 좁아집니다. 범위를 지정하고 마지막으로 원하는 위치에 페이지 노드를 찾습니다

3. 소개 InnoDB 테이블은 인덱스 테이블이기도 합니다

위와 같이, InnoDB 테이블은 클러스터링된 테이블, 즉 InnoDB를 의미합니다. 대규모 인덱스로 구성된 테이블이기도 하며, 기본 키에 따라 정렬된 대규모 인덱스의 B+ 트리 구조이기도 합니다. 인덱스하려는 테이블에 추가 필드를 생성합니다. InnoDB에서

클러스터형 인덱스는 InnoDB 테이블 자체를 의미하며, 이러한 인덱스를 다른 필드에 따라 정렬된 인덱스라고 부릅니다. 보조 인덱스(보조 클래스)

4. 데이터베이스에 인덱스를 만드는 방법

MySQL 인덱스에는 크게 두 가지 유형의 인덱스가 생성됩니다

1. 단일 열 인덱스

create index idx_name on tb_student(name);
              索引名        表名   字段名

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开销，提供更稳定快捷的服务