Mysql索引学习笔记

1、分类

   MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列,索引项可以为null(唯一索引、组合索引等),但是只要列中有null值就不会被包含在索引中。

    (1)普通索引:create index index_name on table(column);

    或者创建表时指定,create table(..., index index_name column);

    (2)唯一索引:类似普通索引,索引列的值必须唯一(可以为空,这点和主键索引不同)

    create unique index index_name on table(column);或者创建表时指定unique index_name column

    (3)主键索引:特殊的唯一索引,不允许为空,只能有一个,一般是在建表时指定primary key(column)

    (4)组合索引:在多个字段上创建索引,遵循最左前缀原则。alter table t add index index_name(a,b,c);

  最左前缀原则https://mp.weixin.qq.com/s/RemJcqPIvLArmfWIhoaZ1g

    (5)全文索引:主要用来查找文本中的关键字,不是直接与索引中的值相比较,像是一个搜索引擎,配合match against使用,现在只有char,varchar,text上可以创建全文索引。在数据量较大时,先将数据放在一张没有全文索引的表里,然后再利用create index创建全文索引,比先生成全文索引再插入数据快很多。

从另外的角度还可以分为

1、聚集索引。

  表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引,因为真实数据的物理顺序只能有一种。

2、非聚集索引。

  表数据存储顺序与索引顺序无关。对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。

2、使用

2.1、何时使用索引

 

    MySQL每次查询只使用一个索引。与其说是“数据库查询只能用到一个索引”,倒不如说,和全表扫描比起来,去分析两个索引B+树更加耗费时间。所以where A=a and B=b这种查询使用(A,B)的组合索引最佳,B+树根据(A,B)来排序。

 

    (1)主键,unique字段;

 

    (2)和其他表做连接的字段需要加索引;

 

    (3)在where里使用>,≥,=,<,≤,is null和between等字段;

 

    (4)使用不以通配符开始的like,where A like 'China%';

 

    (5)聚集函数MIN(),MAX()中的字段;

 

    (6)order by和group by字段;

2.2、何时不使用索引

 

 (1)表记录太少;

 

    (2)数据重复且分布平均的字段(只有很少数据值的列);

 

    (3)经常插入、删除、修改的表要减少索引;

 

    (4)text,image等类型不应该建立索引,这些列的数据量大(假如text前10个字符唯一,也可以对text前10个字符建立索引);

 

    (5)MySQL能估计出全表扫描比使用索引更快时,不使用索引;

2.3、索引何时失效

    (1)组合索引未使用最左前缀,例如组合索引(A,B),where B=b不会使用索引;

    (2)like未使用最左前缀,where A like '%China';

    (3)搜索一个索引而在另一个索引上做order by,where A=a order by B,只使用A上的索引,因为查询只使用一个索引 ;

    (4)or会使索引失效。如果查询字段相同,也可以使用索引。例如where A=a1 or A=a2(生效),where A=a or B=b(失效)

  注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引

    (5)如果列类型是字符串,要使用引号。例如where A='China',否则索引失效(会进行类型转换);

    (6)在索引列上的操作,函数(upper()等)、or、!=(<>)、not in等;

others

1) 没有查询条件,或者查询条件没有建立索引 
2) 在查询条件上没有使用引导列 
3) 查询的数量是大表的大部分,应该是30%以上。 
4) 索引本身失效
5) 查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等) 错误的例子:select * from test where id-1=9; 正确的例子:select * from test where id=10; 
6) 对小表查询 
7) 提示不使用索引
8) 统计数据不真实 
9) CBO计算走索引花费过大的情况。其实也包含了上面的情况,这里指的是表占有的block要比索引小。 
10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子:select * from test where tu_mdn=13333333333; 正确的例子:select * from test where tu_mdn='13333333333'; 
12) 1,<> 2,单独的>,<,(有时会用到,有时不会) 
13,like "%_" 百分号在前. 
4,表没分析. 
15,单独引用复合索引里非第一位置的索引列. 
16,字符型字段为数字时在where条件里不添加引号. 
17,对索引列进行运算.需要建立函数索引. 
18,not in ,not exist. 
19,当变量采用的是times变量,而表的字段采用的是date变量时.或相反情况。 
20,B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走 
21,联合索引 is not null 只要在建立的索引列(不分先后)都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null(但必须在所有列 都满足is null的时候),或者=一个值; 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况(包括is null =一个值),以上两种情况索引都会走。其他情况不会走

3、其他

3.1、explain语句

 

3.2、\g、\G

\g 的作用是分号和在sql语句中写’;’是等效的 
\G 的作用是将查到的结构旋转90度变成纵向 

4、例子

create table test(
id1 int ,
id2 int,
id3 int,
id4 int,
key index_id12(id1,id2)
);

用到索引
explain select * from test where id1 < 10;
用到索引
explain select * from test where id1 < 10 and id2 > 1; 
用到索引
explain select * from test where id2 > 1 and id1 < 2; 
未用到索引,组合索引要满足最左原则
explain select * from test where id2 > 1;
未用到索引
explain select * from test order by id1 desc ;
用到索引
explain select id1 from test order by id1 desc ;
explain select id1,id2 from test order by id1 desc ;
未用到索引
explain select id1,id2,id3 from test order by id1 desc ;

 

5、常见面试问题

以下全部是基于MySQL的InnoDB引擎

5.1、什么是最左前缀原则

例如对于下面这个表

如果我们按照 name 字段来建立索引的话,采用B+树的结构,大概的索引结构如下

如果我们要进行模糊查找,查找name 以“张"开头的所有人的ID,即 sql 语句为

select ID from table where name like '张%'

  由于在B+树结构的索引中,索引项是按照索引定义里面出现的字段顺序排序的,索引在查找的时候,可以快速定位到 ID 为 100的张一,然后直接向右遍历所有开头的人,直到条件不满足为止。

也就是说,我们找到第一个满足条件的人之后,直接向右遍历就可以了,由于索引是有序的,所有满足条件的人都会聚集在一起。

而这种定位到最左边,然后向右遍历寻找,就是我们所说的最左前缀原则

5.2、为什么用 B+ 树做索引而不用哈希表做索引

1、哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置,这样的话,如果我们要进行模糊查找的话,显然哈希表这种结构是不支持的,只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。

2、如果我们要进行范围查找,例如查找ID为100 ~ 400的人,哈希表同样不支持,只能遍历全表。

3、索引字段通过哈希映射成哈希码,如果很多字段都刚好映射到相同值的哈希码的话,那么形成的索引结构将会是一条很长的链表,这样的话,查找的时间就会大大增加。

5.3、主键索引和非主键索引有什么区别

例如对于下面这个表(其实就是上面的表中增加了一个k字段),且ID是主键。

主键索引和非主键索引的示意图如下:

其中R代表一整行的值。

  从图中不难看出,主键索引和非主键索引的区别是:非主键索引的叶子节点存放的是主键的值,而主键索引的叶子节点存放的是整行数据,其中非主键索引也被称为二级索引,而主键索引也被称为聚簇索引

  根据这两种结构我们来进行下查询,看看他们在查询上有什么区别。

1、如果查询语句是 select * from table where ID = 100,即主键查询的方式,则只需要搜索 ID 这棵 B+树。

2、如果查询语句是 select * from table where k = 1,即非主键的查询方式,则先搜索k索引树,得到ID=100,再到ID索引树搜索一次,这个过程也被称为回表。

5.4、为什么建议使用主键自增的索引

对于这棵主键索引的树

如果我们插入 ID = 650 的一行数据,那么直接在最右边插入就可以了

但是如果插入的是 ID = 350 的一行数据,由于 B+ 树是有序的,那么需要将下面的叶子节点进行移动,腾出位置来插入 ID = 350 的数据,这样就会比较消耗时间,如果刚好 R4 所在的数据页已经满了,需要进行页分裂操作,这样会更加糟糕。

但是,如果我们的主键是自增的,每次插入的 ID 都会比前面的大,那么我们每次只需要在后面插入就行, 不需要移动位置、分裂等操作,这样可以提高性能。也就是为什么建议使用主键自增的索引。

5.5、一条SQL语句执行得很慢的原因有哪些

一个 SQL 执行的很慢,我们要分两种情况讨论:

5.5.1、大多数情况下很正常,偶尔很慢,则有如下原因

(1)、数据库在刷新脏页,例如 redo log 写满了需要同步到磁盘。

  当我们要往数据库插入一条数据、或者要更新一条数据的时候,我们知道数据库会在内存中把对应字段的数据更新了,但是更新之后,这些更新的字段并不会马上同步持久化到磁盘中去,而是把这些更新的记录写入到 redo log 日记中去,等到空闲的时候,在通过 redo log 里的日记把最新的数据同步到磁盘中去。

  不过,redo log 里的容量是有限的,如果数据库一直很忙,更新又很频繁,这个时候 redo log 很快就会被写满了,这个时候就没办法等到空闲的时候再把数据同步到磁盘的,只能暂停其他操作,全身心来把数据同步到磁盘中去的,而这个时候,就会导致我们平时正常的SQL语句突然执行的很慢,所以说,数据库在在同步数据到磁盘的时候,就有可能导致我们的SQL语句执行的很慢了。

(2)、执行的时候,遇到锁,如表锁、行锁。

  这个就比较容易想到了,我们要执行的这条语句,刚好这条语句涉及到的,别人在用,并且加锁了,我们拿不到锁,只能慢慢等待别人释放锁了。或者,表没有加锁,但要使用到的某个一行被加锁了,这个时候,我也没办法啊。

  如果要判断是否真的在等待锁,我们可以用 show processlist这个命令来查看当前的状态哦,这里我要提醒一下,有些命令最好记录一下。

5.5.2、这条 SQL 语句一直执行的很慢,那么sql本身的问题了,一般有如下原因。

我们先来假设我们有一个表,表里有下面两个字段,分别是主键 id,和两个普通字段 c 和 d。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

  

(1)、没有用上索引:例如该字段没有索引;由于对字段进行运算、函数操作导致无法用索引。

没有用上索引,c 字段上没有索引,那么抱歉,只能走全表扫描了

select * from t where 100 <c and c < 100000;

字段有索引,但却没有用索引  

select * from t where c - 1 = 1000;

正确的查询应该如下

select * from t where c = 1000 + 1;

函数操作导致没有用上索引

select * from t where fun(c,2) = 1000;

    

(2)、数据库选错了索引。

  我们知道,主键索引和非主键索引是有区别的,主键索引存放的值是整行字段的数据,而非主键索引上存放的值不是整行字段的数据,而且存放主键字段的值。面试小知识:MySQL索引相关    里面有说到主键索引和非主键索引的区别.

  也就是说,我们如果走 c 这个字段的索引的话,最后会查询到对应主键的值,然后,再根据主键的值走主键索引,查询到整行数据返回。

  就算你在 c 字段上有索引,系统也并不一定会走 c 这个字段上的索引,而是有可能会直接扫描扫描全表,找出所有符合 100 < c and c < 100000 的数据。

为什么会这样呢?

  其实是这样的,系统在执行这条语句的时候,会进行预测:究竟是走 c 索引扫描的行数少,还是直接扫描全表扫描的行数少呢?显然,扫描行数越少当然越好了,因为扫描行数越少,意味着I/O操作的次数越少。

  如果是扫描全表的话,那么扫描的次数就是这个表的总行数了,假设为 n;而如果走索引 c 的话,我们通过索引 c 找到主键之后,还得再通过主键索引来找我们整行的数据,也就是说,需要走两次索引。而且,我们也不知道符合 100 c < and c < 10000 这个条件的数据有多少行,万一这个表是全部数据都符合呢?这个时候意味着,走 c 索引不仅扫描的行数是 n,同时还得每行数据走两次索引。

  所以呢,系统是有可能走全表扫描而不走索引的。那系统是怎么判断呢?

  判断来源于系统的预测,也就是说,如果要走 c 字段索引的话,系统会预测走 c 字段索引大概需要扫描多少行。如果预测到要扫描的行数很多,它可能就不走索引而直接扫描全表了。

  那么问题来了,系统是怎么预测判断的呢?

  系统是通过索引的区分度来判断的,一个索引上不同的值越多,意味着出现相同数值的索引越少,意味着索引的区分度越高。我们也把区分度称之为基数,即区分度越高,基数越大。所以呢,基数越大,意味着符合 100 < c and c < 10000 这个条件的行数越少。

所以呢,一个索引的基数越大,意味着走索引查询越有优势。

  那么问题来了,怎么知道这个索引的基数呢?

  系统当然是不会遍历全部来获得一个索引的基数的,代价太大了,索引系统是通过遍历部分数据,也就是通过采样的方式,来预测索引的基数的。

  扯了这么多,重点的来了,居然是采样,那就有可能出现失误的情况,也就是说,c 这个索引的基数实际上是很大的,但是采样的时候,却很不幸,把这个索引的基数预测成很小。例如你采样的那一部分数据刚好基数很小,然后就误以为索引的基数很小。然后就呵呵,系统就不走 c 索引了,直接走全部扫描了

所以呢,说了这么多,得出结论:由于统计的失误,导致系统没有走索引,而是走了全表扫描,而这,也是导致我们 SQL 语句执行的很慢的原因。

这里我声明一下,系统判断是否走索引,扫描行数的预测其实只是原因之一,这条查询语句是否需要使用使用临时表、是否需要排序等也是会影响系统的选择的。

  不过呢,我们有时候也可以通过强制走索引的方式来查询,例如

select * from t force index(a) where c < 100 and c < 100000;

  我们也可以通过

show index from t;

  来查询索引的基数和实际是否符合,如果和实际很不符合的话,我们可以重新来统计索引的基数,可以用这条命令

analyze table t;

  来重新统计分析。

既然会预测错索引的基数,这也意味着,当我们的查询语句有多个索引的时候,系统有可能也会选错索引哦,这也可能是 SQL 执行的很慢的一个原因。

 

5.5.3、总结

一个 SQL 执行的很慢,我们要分两种情况讨论:

1、大多数情况下很正常,偶尔很慢,则有如下原因

(1)、数据库在刷新脏页,例如 redo log 写满了需要同步到磁盘。

(2)、执行的时候,遇到锁,如表锁、行锁。

2、这条 SQL 语句一直执行的很慢,则有如下原因。

(1)、没有用上索引:例如该字段没有索引;由于对字段进行运算、函数操作导致无法用索引。

(2)、数据库选错了索引。

 6、出处文章

https://blog.csdn.net/guoxingege/article/details/51034387

https://blog.csdn.net/weixin_39420024/article/details/80040549

MySQL索引相关:

https://mp.weixin.qq.com/s/RemJcqPIvLArmfWIhoaZ1g

一条SQL语句执行得很慢的原因有哪些:

https://mp.weixin.qq.com/s/pTywDcdg8AVZ8qvR0KZFFQ

posted @ 2019-05-06 13:28  木子木泗  阅读(836)  评论(0编辑  收藏  举报