Einführung und Optimierung des MySQL InnoDB-Index

Text:

1. Lassen Sie uns zunächst darüber sprechen, was ein Index ist.

Der Index wird in ein Verzeichnis übersetzt, das verwendet wird, um schnell den Speicherort der Daten zu finden, die wir finden möchten. Beispiel: Wir vergleichen eine Datenbank mit einem Buch, und der Index ist das Inhaltsverzeichnis im Buch. Wenn wir einen interessanten Inhalt im Buch finden möchten, lesen wir im Allgemeinen nicht das gesamte Buch und bestätigen dann den Inhalt Suchen Sie stattdessen die Seitenzahl des Inhaltskapitels im Inhaltsverzeichnis des Buches und wechseln Sie schließlich direkt zur Seite.

Werfen wir einen Blick auf den Index in der Datenbank:
Vollständiger Tabellenscan VS-Index scannen
Am Beispiel des Wörterbuchs bedeutet ein vollständiger Tabellenscan, dass wir, wenn wir nach einem bestimmten Wort suchen, das Xinhua-Wörterbuch durchlesen und dann das Wort finden, das wir finden möchten
Und das Gegenstück dazu vollständig Der Tabellenscan ist eine Indexsuche. Bei der Indexsuche geht es darum, den spezifischen Speicherort der Daten zu finden, die wir im Indexteil der Tabelle finden möchten, und dann zur Tabelle zu gehen, um alle Daten herauszufinden, die wir finden möchten

Beispiel: Finden Sie einen Studenten namens Dev in einer Studententabelle Student

Vollständiger Tabellenscan auf der linken Seite: Es ist notwendig, Zeile für Zeile von der ersten Zeile bis zu scannen Es werden 100.008 Zeilen mit Informationen zum Schüler-Entwickler gefunden und diese Daten werden dann zurückgegeben. Es können jedoch Schüler mit demselben Namen in der Tabelle vorhanden sein, sodass der Scan normalerweise nicht abgeschlossen ist, um ein Datenelement in einer vollständigen Tabelle zu finden Beim Scannen müssen Sie die gesamte Datentabelle durchsuchen, bevor Sie feststellen können, ob alle Daten zurückgegeben werden sollen

Index-Scan rechts: Bei der Indexsuche wird der Entwickler anhand des ersten Buchstabens gefunden, der mit D beginnt. Wenn die ersten Buchstaben gleich sind, finden Sie sie anhand des zweiten Buchstabens. Analog finden wir, dass die ID 100008 ist, und gehen dann zurück zur Tabelle, um die ID zu finden.

Fazit : Daher entspricht der Indexwert des Index (entsprechend InnoDB) der Primärschlüssel-ID

2. So finden Sie den dem Index entsprechenden Wert

Die InnoDB-Engine basiert hauptsächlich auf
(1)B-Baum
(2)Binäre Suchmethode

B-Baum: Der B-Baum hat den Wurzelknoten, die Zweigknoten und die Seitenknoten des Ganzen Baum, die obere Schicht speichert den Verwaltungsbereich der Knoten der unteren Schicht, bis die spezifischen Informationen des Seitenknotens vorliegen

Binäre Suchmethode: Vergleichen Sie schrittweise entsprechend dem Bereich jedes im B-Baum gespeicherten Knotens Grenzen Sie den Bereich ein und suchen Sie schließlich den gewünschten Seitenknoten

3. Einführung: Die InnoDB-Tabelle ist auch eine Indextabelle

Wie oben gezeigt, Die InnoDB-Tabelle ist eine Cluster-Tabelle, was bedeutet, dass InnoDB selbst eine große indexorganisierte Tabelle ist, die auch eine B-Baumstruktur eines großen Index ist, der nach dem Primärschlüssel sortiert ist. Wir erstellen zusätzliche Felder in der Tabelle, in der wir indizieren möchten InnoDB

Der Clustered-Index bezeichnet die InnoDB-Tabelle selbst. Und wir nennen diese nach anderen Feldern sortierten Indizes sogenannte Sekundärindizes (Sekundärklasse)

So erstellen Sie Indizes in der Datenbank

In MySQL gibt es zwei Haupttypen von Indizes

1. Einspaltiger Index

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