1.为什么要用索引

  • 索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。
  • 索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。
  • 索引优化应该是对查询性能优化最有效的手段
  • 索引能够轻易将查询性能提高好几个数量级
  • 索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查

    2.索引的原理

    1.索引原理

  • 索引的目的在于提高查询效率,与我们查阅图书所用的目录是一个道理:先定位到章,然后定位到该章下的一个小节,然后找到页数。
  • 通过不断地缩小想要获取数据地范围来筛选出最终地结果。
  • 同时把随机地事件变成顺序地事件,有了这种索引机制我们可以总是用同一种查找方式来锁定数据。
  • 数据库不仅面临等值查询、范围查询(<、>、 between、in)、模糊查询(like)、并集查询(or)等等
  • 如果把数据进行等值的分段(如果1000条 1-100、101-200,….)如果查999条,可以直接只要找第十段就可以了,但是分成几段比较好?所以会想到搜索树,平均复杂度为lgN,具有不错的查询性能。
  • 复杂模型是基于每次相同的操作成本来考虑的。
  • 数据库比较复杂,一方面数据是保存到磁盘上,另一方面为了提高性能,每次又可以把部分数据读入内存来计算,访问磁盘的成本大概是访问内存的十万倍,所以简单的查询树难以满足。

2.磁盘ID与预读

  • 当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓存区总,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。
  • 每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,4k或者8k,都去一页数据的时候,实际才发生一次IO

3.索引的数据结构

  • 每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常量级。所以使用B+树(MySQL写成BTREE)
  • 上图为B+树,浅蓝色的块我们称之为一个磁盘块,包含几个数据项(深蓝色)和指针(黄色)
  • 磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,p2表示在17和35之间的磁盘块,P3表示大于35的磁盘快
  • 真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
  • 非叶子节点只存储指引搜索方向的数据项,如17、35并不真实的存在与数据表中。

1.B+树的查找过程

  • 如上图所示如果查找数据项29,
    • 1.那么首先会把磁盘快1加载到内存中,发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针
    • 2.通过磁盘块1和P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO
    • 3.29在26和30之间,锁定磁盘快3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询。
  • 三层的B+树可以表示上百万的数据,如果上百万的时候只需要上次IO,性能将大大提高,如果没有索引,每个数据项都要发生一次IO,那么总共百万次的IO。

2.B+树的性质

1.索引字段要尽量的小

  • IO次数取决于B+树的高度h。
  • 假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则h=log(m+1)N
  • 当数据量N一定的情况下,m越大,h越小,m=磁盘快的大小/数据项的大
  • 如果数据项占的空间越小,数据项的数量越多,树的高度越低。
  • 所以索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。
  • 这也是为什么B+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘快的数据项会大幅度下降,导致树增高,当数据项等于1时将会退化成线性表。

2.索引的最左匹配特性(即从左往右匹配)

  • 当B+树的数据项是复合的数据结构,比如(name,age,sex)的时候,B+树是按照从左到右的顺序来建立搜索树的。
  • 比如当(张三、20、F)这样的数据来检索的时候,B+树会优先比较name来确定下一步的搜索方向,如果name相同再依次比较age和sex,最后得到检索的数据。
  • 当(20,F)这样的没有name的数据来的时候,B+树就不知道下一步该检查哪一个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪查询。当(张三,F)时同理。
  • 非常重要的特性,索引的最左匹配特性

4.MySQL索引管理

1.功能

  • 索引的功能就是加速查找
  • MySQL中的primary key、unique,联合索引也都是索引,这些索引出了加速查找以外,还有约束功能

2.MySQL的索引分类

1.索引分类

  • 普通索引index:加速查找
  • 唯一索引:
    • 主键索引:primary key:加速查找+约束(不为空且唯一)
    • 唯一索引:unique:加速查找+约束(唯一)
  • 联合索引
    • primary key(id,name):联合主键索引
    • unique(id,name):联合唯一索引
    • index(id,name):联合普通索引
  • 全文索引fulltext:用于搜索很长一篇文章的时候,效果最好。
  • 空间索引spatial:几乎不用

2.示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
为某商场做一个会员卡的系统。

这个系统有一个会员表
有下列字段:
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号,作为主键,使用 PRIMARY
会员姓名 如果要建索引的话,那么就是普通的 INDEX
会员身份证号码 如果要建索引的话,那么可以选择 UNIQUE (唯一的,不允许重复)

#除此之外还有全文索引,即FULLTEXT
会员备注信息 , 如果需要建索引的话,可以选择全文搜索。
用于搜索很长一篇文章的时候,效果最好。
用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。
但其实对于全文搜索,我们并不会使用MySQL自带的该索引,而是会选择第三方软件如Sphinx,专门来做全文搜索。

#其他的如空间索引SPATIAL,了解即可,几乎不用

各个索引的应用场景

3.索引的两大类型

1.索引分类

  • hash类型的索引:查询单条速度快,范围查询慢。
  • btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持他)

2.不同的存储引擎支持的索引类型

  • InnoDB 支持事务,支持行级别锁定,支持B-tree、Full-text等索引,不支持Hash索引。
  • MyISAM不支持事务,支持表级别锁定,支持B-tree、Full-text等索引,不支持Hash索引。
  • Memory不支持事务,支持表级别锁定,支持B-tree、Hash等索引,不支持Full-text索引。
  • NDB支持事务,支持行级别锁定,支持Hash索引,不支持B-tree、Full-text等索引。
  • Archive不支持事务,支持表级别锁定,不支持B-tree、Hash、Full-text等索引。

4.创建/删除索引的语法

1.创建表时添加索引

1
2
3
4
5
6
create table 表明 (
字段名1 数据类型 [完整约束条件...],
字段名2 数据类型 [完整约束条件...],
[UNIQUE|FULLTEXT|SPATIAL] index | key
[索引名](字段名[(长度)] [ASC | DESC])
)

2.在已存在的表上创建索引

1
create [UNIQUE | FULLTEXT | SPATIAL] index 索引名 on 表名 (字段名[(长度)] [ASC | DESC])

3.修改已存在的索引

1
alter table 表名 add [UNIQUE | FULLTEXT | SPATIAL] 索引名 (字段名[(长度)] [ASC|DESC]);

3.删除索引

1
drop index 索引名 on 表名

4.善用帮助文档

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
善用帮助文档
help create
help create index
==================
1.创建索引
-在创建表时就创建(需要注意的几点)
create table s1(
id int ,#可以在这加primary key
#id int index #不可以这样加索引,因为index只是索引,没有约束一说,
#不能像主键,还有唯一约束一样,在定义字段的时候加索引
name char(20),
age int,
email varchar(30)
#primary key(id) #也可以在这加
index(id) #可以这样加
);
-在创建表后在创建
create index name on s1(name); #添加普通索引
create unique age on s1(age);添加唯一索引
alter table s1 add primary key(id); #添加住建索引,也就是给id字段增加一个主键约束
create index name on s1(id,name); #添加普通联合索引
2.删除索引
drop index id on s1;
drop index name on s1; #删除普通索引
drop index age on s1; #删除唯一索引,就和普通索引一样,不用在index前加unique来删,直接就可以删了
alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的,那么我们也用alter来删)

5.创建测试数据

1.创建test表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` varchar(255) DEFAULT NULL,
KEY `nameA` (`name`) USING BTREE,
KEY `ageA` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

2.创建存储过程用于插入测试数据

1
2
3
4
5
6
7
8
9
10
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into test values(concat('name',i),i,concat('sex',i));
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

3.查看存储过程

1
show create procedure auto_insert1;

4.调用存储过程

1
call auto_insert1();

6.正确的使用索引

1.覆盖索引

1
2
3
4
5
6
7
select * from s1 where id=123;

// 1.该sql命中了索引,但是未覆盖。
// 2.利用id=123到索引的数据结构中定位该id在硬盘中国呢的位置,或者说在数据表中的位置
// 3.如果select *,除了id字段还需要其他字段,意味着,我们通过索引结构取到id还不够。
// 4.还需要利用id再去找所在行其他字段值,这是需要时间的。
// 5.如果只查询id,则会非常快。

2.联合索引

1
2
3
4
5
6
7
8
// 未加联合索引查询
select * from test where name='张三' and age=12; // 0.3s

// 创建联合索引
create index nameAge on test(name,age); // OK

// 加了联合索引查询
select * from test where name='张三' and age=12; // 0.01s

3.索引合并(Index merge optimization)

  • 需要开启Index merge特性

  • 但是受限于MySQL糟糕的统计信息,可能会导致查询性能极差甚至导致数据库崩溃

  • 如果数据较小时,在对两个数据较小求交集的操作成本也很低,最终整个语句查询高效

  • 当两个字段选择性较差且统计信息不准时,比如整表数据量2000w,按照条件一返回1500w数据,按照条件二返回1000条,此时按照条件二进行索引扫描+聚集索引查找的操作成本极高(可能是整表扫描的百倍消耗),对1000条和1500w条求交集的成本也极高,最终消耗大量的CPU和IO资源且时间超长,但是如果按照条件一进行索引,查询资源较少且性能高

  • 有可能引发死锁

  • 建议使用以下sql优化

    1
    select * from test where name='张三' and age=12 UNION all select * from test where name='张三' and age<>12;
  • explain执行上面sql后会输出下面的内容

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY test NULL ref nameA,ageA nameA 768 const 1 5.00 Using where
2 UNION test NULL ref nameA,ageA nameA 768 const 1 50.00 Using where
  • 使用索引合并(Index merge)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    // 1.给name和age创建联合索引可以命中以下两个sql
    select * from test where name='张三' and age=12;
    select * from test where name='张三';

    // 2.分别给name和age创建索引,可以命中以下四个sql
    select * from test where name='张三' and age=12;

    // 如果开启了Index merge可命中下面这条数据 type为index_merge
    select * from test where name='张三' or age=12;
    select * from test where name='张三';
    select * from test where age=12;

    4.若想利用索引达到预想的提高速度的效果,需遵守以下规则

  • 最左前缀匹配规则,必须按照从左到右的顺序匹配
    1
    2
    3
    4
    5
    // 建立一个(name,age)的联合索引
    select * from test where name='张三'; // 可以命中
    select * from test where name='张三' and age=12; // 可以命中
    select * from test_key_len where name='张三' and sex='sex123';// 可以命中
    select * from test where age=12; // 不可以命中
    • mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配
      • 比如a=1 and b=2 and c>3 and d=4,如果建立了(a,b,c,d)顺序的索引
      • d就会用不到索引,如果建立了(a,b,d,c)则可以用到
  • =和in可以乱序,比如a=1 and b=2 and c= 3建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
  • 尽量选择区分度高的列作为索引,区分度的公式是count(distinct colName)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,使用场景不同,这个值也很难确定,一般需要join的字段要求是0.1以上,即平均一条扫描10条记录。
  • 索引列都不能参与计算,保持列 “干净”,比如from_unixtime(create_time)= ‘2014-05-29’,就不能使用到索引,原因是:B+树中存的都是数据表的字段值,但是进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。应该写成create_time = unix_timestamp(‘2014-05-29’)

1.最左前缀示范

  • 通过以下的代码测试,高下立判,所以一定要遵守最左前缀
  • 查询条件中只要出现最左前缀相关字段就可以加快查询速度
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    // 未加索引查询
    select * from test where age>12 and name='name300' and sex='sex300'; // 1.765s

    // 加索引 未遵循最左前缀
    create index idx on test(age,name,sex);

    // 执行未按照最左前缀索引的sql
    select * from test where age>12 and name='name300' and sex='sex300'; // 1.677s

    // 删掉索引
    drop index idx on test

    // 添加按照最左前缀的索引
    create index idx on test(name,sex,age);

    // 执行按照最左前缀的索引的sql
    select * from test where age>12 and name='name300' and sex='sex300'; // 0.000s

    2.索引无法命中的情况需要注意

  • like ‘%xxx’
    1
    2
    // 如果按照最左匹配可以命中索引,但是type为index,比All好一点
    select * from test where name like '%xxx'
  • 使用函数
    1
    2
    3
    4
    5
    // 无法命中
    select * from test where reverse(name) ='name300';

    // 可以使用下面的方法来命中索引
    select * from test where name =reverse('name300');
  • or
    • 只要查询条件不包含在联合索引中,则不命中索引
    • 理论上只要查询条件在联合索引中则命中索引
    • 实际测试时联合索引的字段要比查询条件多才会命中type为index的索引(表一共有三个字段,给两个字段加了联合索引)
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      index(name,age)
      select * from test where name='name300' or age=12; // 不命中

      index(name,age,sex)
      select * from test where name'name300' or age=12;// 命中

      index(name,age)
      select * from test where name='张三' or sex='sex300';// 不命中

      index(name,age,sex)
      select * from test where name='name300' or sex='sex300' or age=12;// 命中

      index(name,age,sex)
      select * from test where name='name300' or sex='sex300' and age=12;// 命中
  • 类型不一致
    • 当字段类型为字符串时并且没有用引号引起来的话 则会命中type为index的索引
    • 不会命中比index更优的索引
    • 字段类型为int,传入字符串则会正常命中索引
      1
      2
      3
      4
      5
      index(name,age,sex)
      select * from test where name='12';// 不会命中比index更优的索引

      index(age,name,sex)
      select * from test where age='12a'; // 命中
  • 普通索引的 不等于(!= <>) 不会走索引
    • 理论上不等于是不会命中索引的
    • 通过以上生成的假数据经测试都会命中type为range的索引(生成的数据都是按照顺序生成的,所以会命中,这里首次测试是可以命中range,但是重启后又命不中,大家需要自己测试下)
    • 如果是主键会命中索引
    • 如果是整数类型会命中索引(经过测试连续的数字无法命中,大家可以自己测试下)
      1
      2
      3
      4
      5
      index(name)
      select * from test_key_len where name <> '张三'; // 不命中

      index(age)
      select * from test_key_len where age != 300;// 不命中
  • order by
    • 当根据索引排序时候,select查询的字段如果不是索引,则不走索引
    • 如果对主键排序,则还是走索引
      1
      2
      3
      4
      5
      index(name)
      select name from test order by name desc limit 0,100; // 命中type为index索引

      select name from test order by sex desc limit 0,100; // 不命中

  • 联合索引最左前缀
    1
    2
    3
    4
    如果联合索引为:(name,age)
    name and age -- 使用索引
    name -- 使用索引
    age -- 不使用索引

    3.其余优化

  • 避免使用select *,要直接查询自己需要的内容
  • count(1)或count(列)代替count(*) // 新版本mysql区别不大
  • 创建表时尽量用char代替varchar
  • 表的字段顺序 固定长度的字段索引
  • 联合索引代替多个单列索引(经常使用多个条件查询时)
  • 尽量不要使用not in 不等于
  • 尽量使用短索引,如果一个char(255)的列但是前10位或者20位,可以判定唯一,这就不要对整个列创建索引,指定一个前缀长度
  • 使用连接(JOIN)查询来代替子查询(Sub-Queries)
  • 连表查询时注意条件类型需一致
  • 索引散列值(Hash)(重复多)不适合建索引,例如:性别
  • 更新频繁的不适合,耗时且影响性能
  • 联合所以尽量使用and查询,用or提升不了太多

7.慢查询优化的基本步骤

  • 1.先运行看看是否真的慢,注意设置SQL_NO_CACHE
  • 2.where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
  • 3.explain查看执行计划,是否和2预期一致(从锁定记录较少的表开始查询)
  • 4.order by limit形式的sql语句让排序的表优先查
  • 5.了解业务使用场景
  • 6.加索引参照建索引的几大原则
  • 7.观察结果,不符合预期继续从1开始分析

    本文章来源于网络,略有修改,如有侵权行为,请点击这里联系我删除

如发现疑问或者错误点击反馈

备注

2019年9月16日

  • 完善文章

2019年10月17日

  • 完善文章