MySQLInnoDB索引介绍及优化

转自：https://segmentfault.com/a/1190000007445807?utm\_source=tuicool&utm\_medium=referral

前言：

由于BOOS要求每月写一篇文章,因此，我也申请了自己的博客，我为此感到羞耻。工作了几年，还没写博客，脸微微发红。
以前，我只知道如何从互联网上询问和烤肉。当我看到一篇好文章时，我很欣赏和认可它。当我看到一篇糟糕的文章时，我烤了又叹。我觉得这是不负责任的。
现在我也写一个怀着焦虑之心的博客，我鼓励你！

---

---

正文：

一、指标概念

索引(index)翻译为目录，用于快速定位我们正在寻找的数据的位置。例如，我们将数据库与书籍进行比较，并将索引(index)它是书中的目录。此刻要想在书中找到某个有趣的内容，我们通常不会通读整本书来确认内容在哪里。而是用书的目录来定位内容章节所在的页数，最后直接翻页

让我们看一下数据库中的索引：
全表扫描 VS 索引扫描
以字典为例，全表扫描就是我们搜索某个单词，然后通读新华词典，找到我们想找的单词
而对应的全表扫描就是索引查找，就是在表的索引部分找到我们要查找的数据的具体位置，然后在表中查找出所有想要查找的数据

示例：在学生表上查找名为Dev的学生

左全表扫描：需要从第一行开始逐行扫描，直到找到为止100008行Dev在返回此学生的信息之前，表中可能仍有同名学生，因此扫描未完成。通常，为了在全表扫描中查找数据，在确定是否返回所有数据之前，需要遍历整个表一次

右索引扫描：通过首字母排序找到索引搜索D开头的Dev,如果第一个字母相同，那么我们可以根据第二个字母对其进行排序并找到它，依此类推ID为100008然后回到桌子上找出答案ID为100008的数据

结论：因此，该指数(对应InnoDB)的索引值对应主键ID

二、如何找到指标对应的值

InnoDB发动机主要基于
(1)B+tree
(2)二叉搜索方法

B+tree: B+树有整棵树的根节点、分支节点、页面节点，上层存储下层节点的管理范围，直到页面节点的具体信息

二叉搜索方法：根据B+比较树中存储的每个节点的范围，逐渐缩小范围，最后在页面节点中找到所需的位置

三、简介InnoDB表也是索引表

如上图InnoDB表是簇状表，含义InnoDB它是一个大型索引组织表，也是一个基于主键排序的大型索引B+树结构，我们是InnoDB为要在其中编制索引的表创建其他字段

聚类指数平均值InnoDB表本身，我们将这些按其他字段排序的索引称为二级索引(secondery class)

四、如何在数据库中建立索引

在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索引越多，插入表和修改索引字段的速度越慢

因此，可以看出，索引越多不一定越好，在工作中谨慎使用也很重要，特别是对于写入操作频繁的业务

6、 如何正确使用索引？

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)  ->正确

7、 哪些字段适合创建索引？

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)                ->正确，可以覆盖前两个

8、 我们来看看如何在长字段上建立索引

首先，在较长的字段上建立索引可能会对性能产生重大影响，例如超大的文章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)      --建立联合索引或单列索引 ->正确

9、 直击核心SQL索引覆盖率扫描

对于最核心SQL我们可以考虑使用指数覆盖率。什么是指数覆盖率？这是一个例子

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

查询用户名的频率非常高，索引还存储字段的值。因此，当我们执行查询时，name字段的值直接在索引中返回，无需返回表;还有另一个广泛使用的例子：当用户登录时，我们可以username password做覆盖索引，大大提高了登录验证的速度

因此，覆盖指数覆盖率是通过将要查询的字段与条件字段组合在一起来建立联合索引。这样做的好处是无需回到表上即可获得name字段，IO最小值，速度块

10、 哪些情况不能使用索引？

1数据或函数操作的索引列

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

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

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     ->正确

11、 使用索引进行排序操作

以 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只有排序方法一致，才能利用索引

12、 如何判断查询是否已编入索引，哪些索引已编入索引？

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成本，提供更稳定高效的服务

注意：如果有任何瑕疵，请随时添加;如果有任何错误，请随时更正

• 2016年11月10日发布