Mysql 的索引总结

本文内容来自知乎专栏,对其中的知识点进行摘录,方便日后查阅。
https://zhuanlan.zhihu.com/p/29118331

一、索引概念和原理

概念: 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。

概念来源:http://feiyan.info/16.html

原理: 提高查询效率,相当于一本书的目录,帮助查询内容。

二、索引的优缺点

优点:

  • 索引大大减小了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机IO变成顺序IO
  • 索引对于InnoDB(对索引支持行级锁)非常重要,因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中,InnoDB可以在服务器端过滤掉行后就释放锁,但在早期的MySQL版本中,InnoDB直到事务提交时才会解锁。对不需要的元组的加锁,会增加锁的开销,降低并发性。 InnoDB仅对需要访问的元组加锁,而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做(即索引达不到过滤的目的),MySQL服务器只能对InnoDB返回的数据进行WHERE操作,此时,已经无法避免对那些元组加锁了。如果查询不能使用索引,MySQL会进行全表扫描,并锁住每一个元组,不管是否真正需要。
  • 关于InnoDB、索引和锁:InnoDB在二级索引上
  • 使用共享锁(读锁),但访问主键索引需要排他锁(写锁)

缺点:

  • 会降低更新表的速度,如insert 、update、delete。因为更新表的时候不仅要保存数据,还要保存索引文件。
  • 建立索引会占用磁盘空间的索引文件,一般问题不大,但如果在一个表上建立了多种组合索引,索引文件会膨胀很快。
  • 如果某个数据列包含了很多重复的内容,为这个列建立索引就没有太大效果(如people表中给gender建立索引就没有什么效果,因为male和female重复的内容很多。)

为最经常查询和排序的数据列建立索引
Mysql里同一个数据表中索引总数最高16个

三、索引的存储

1.索引存储类型

InnoDB使用B+Tree存储索引
B+Tree:每个叶子节点都包含指向下一个叶子节点的指针,从而方便范围遍历。
B-Tree:按照顺序存储,所有叶子在同一个层次,对<,<=,=,>,>=,BETWEEN,IN,以及不以通配符开始的LIKE(“xxx%”)使用索引。

2.索引查询

可以利用B-Tree进行全关键字关键字范围关键字前缀查询,但必须保证最左边前缀(leftmost prefix of the index)。

假设有如下一个表:

CREATE TABLE People (
  last_name varchar(50) not null,
  first_name varchar(50) not null,
  dob date not null,
  gender enum('m', 'f') not null,
  key(last_name, first_name, dob)
);

索引包含last_name、first_name和dob,结构如下:
在这里插入图片描述
根据最左边前缀查询:

  1. 查询必须从索引的最左边的列开始,否则无法使用索引。
  2. 不能跳过某一索引列。
  3. 存储引擎不能使用索引中范围条件右边的列。例如,如果你的查询语句为WHERE last_name=“Smith” AND first_name LIKE ‘J%’ AND dob=‘1976-12-23’,则该查询只会使用索引中的前两列,因为LIKE是范围查询。

查询的形式有6种

  • 匹配全值:对索引的所有列都指定具体的值
  • 匹配最左前缀:可以仅仅使用索引的第一列即根据last name进行查询
  • 匹配列前缀:可以查找last name中以J开始的人
  • 匹配值的范围查询:可以查找last name 在Allen和Barry之间的人。
  • 匹配部分精确而其他部分进行范围匹配:查找last name为Allen,而first name以k开头
  • 仅对索引进行查询:如果查询的列都在索引中,不需要在多一次I/O回读元组了

覆盖索引: 索引的叶子节点中已经包含要查询的数据,那么就没有必要再回表查询了,如果索引包含满足查询的所有数据,就称为覆盖索引。

3.索引排序

用B-Tree索引进行索引排序,保证Order By按索引的最左边前缀进行。

在Mysql中有两种方式生成有序结果集:

  • filesort
  • 使用索引顺序扫描

按索引顺序扫描:
可以利用同一索引同时进行查找和排序操作
order by排序时所有列的方向要相同(都是ASC或都是DESC),其他用法如下图所示:
在这里插入图片描述
filesort:
当Mysql不能使用索引进行排序时,就会利用自己的排序算法在内存(Sort Buffer)对数据进行排序;

如果内存装载不下,会将磁盘伤的数据进行分块,在对各个数据块进行排序,然后将各个块合并成有序的结果集。

对于filesort,Mysql使用的两种排序算法:
两次扫描算法(two passes):
将需要排序的字段和可以定位到相关行数据的指针信息放到内存进行排序,完成后再通过指针取出所需要的columns。

这个算法需要两次访问数据,在第二次的时候会进行大量的I/O操作

一次扫描算法(single pass):
一次性将所需的columns全部取出,在内存中排序后直接输出。

这个算法内存开销较大,有时候并不需要那么多columns。

在Mysql4.1版本后可以通过设置max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种:当取出的所有大字段总大小大于这个值是,Mysql就会用第一种排序算法,反之用第二种。

在进行多表连接操作用到排序时,如果仅仅引用第一个表的列,Mysql对该表进行filesort然后进行连接处理;否则会先连接生成临时表,在进行filesort。

四、聚簇索引和二级索引

一般来说,DBMS都会以聚簇索引的形式来存储实际的数据,它是其它二级索引的基础:

  • 聚簇索引(primary索引):主键索引
  • 非聚簇索引(second索引):二级索引
1.聚簇索引

InnoDB对主键建立聚簇索引;如果没有主键,InnoDB会用一个具有唯一且非空的索引来代替;如果不存在这样的索引,InnoDB会定义一个隐藏的主键,然后对其建立聚簇索引。

使用InnoDB,如果不惜要特殊的聚簇索引,可以用代理主键(surrogate key)即AUTO_INCREMENT的列,这保证记录按顺序插入,提高了用主键进行查询的性能。要避免随机的聚簇主键,如字符串主键,会是插入操作变得随机。

2.聚簇索引和二级索引结构
  • 聚簇索引:节点页只包含了索引列,叶子页包含了行的全部数据。聚簇索引“就是表”,因此可以不需要独立的行存储。

聚簇索引保证关键字的值相近的元组存储的物理位置也相近(所以字符串类型不适合做主键,因为这会导致系统进行大量的移动操作)
在这里插入图片描述

  • 二级索引: 叶子节点保存的不是行的物理位置的指针,而是行的主键值。

所以通过二级索引查找行,存储引擎需要1.找到二级索引的叶子节点获取对应的主键值 2.根据主键值去聚簇索引中找到对应的行。这里使用两次B-Tree。

覆盖索引对于InnoDB很有用,如果二级索引包含查询所需的数据,就不需要在聚集索引中查找了。

3.MyISAM和InnoDB存储结构的区别

创建一个表,col1为主键,对col2创建索引

CREATE TABLE layout_test (
  col1 int NOT NULL,
  col2 int NOT NULL,
  PRIMARY KEY(col1),
  KEY(col2)
);
  • MyISAM

MyISAM按照插入的顺序在磁盘上存储数据在这里插入图片描述
MyISAM建立的索引结构如下:

col1主键索引和col2非主键索引没有大的区别,主键仅仅只是唯一的,非空的索引而已,存储的结构一样。叶子节点按照索引的顺序存储,叶子节点仅仅包含行号,并没有具体的数据
在这里插入图片描述

  • InnoDB

col1为主键索引即聚簇索引:每个叶子节点包含主键的值,事务ID,回滚指针,和余下的所有数据(col2)。
在这里插入图片描述
col2为非主键索引,即二级索引

InnoDB中二级索引的叶子包含主键的值,这样的策略见笑了移动数据或者数据页面分列式维护二级索引的开销,因为InnoDB不需要更新索引的行指针。
在这里插入图片描述

两种索引存储结构的区别
在这里插入图片描述

五、其他索引

1.Hash索引

Mysql只有Memory存储引擎支持hash索引,是其默认索引类型,尽管Memory也可以使用B-Tree索引。

索引结构如下所示,左边是用hash函数对索引求的的hash值,右边是指向这个行数据的指针。
在这里插入图片描述
Hash索引的限制

  • 索引仅包含了hash code和记录指针,所以要读取记录,还是要通过这个指针回读元祖查数据。
  • 不能使用hash索引排序
  • 不支持键的部分匹配
  • 只支持等值不叫,如=,IN( )和<=>
  • 访问hash索引速度非常快,但是如果有很多hash冲突,需要便利所有的行指针
  • 如果哈希冲突很多的话,维护的代价也很高。从表中删除一行,存储引擎需要便利对应哈希值中的每个链表指针。

当InnoDB有自适应哈希索引,它注意到某些索引被使用的很频繁是,会在内存中基于缓冲池中的B+Tree在创建一个哈希索引,进行等值比较。

2.空间(R-Tree)索引

MyISAM支持空间索引,主要用于地理空间数据类型,例如GEOMETRY。

3.全文(full-text)索引

全文索引是MyISAM的一个特殊索引类型,它查找的是文本中的关键词,主要用于全文检索。

InnoDB从5.6开始已经支持全文索引,但还不支持中文和日文等。

六、索引使用

  • 单列索引
  • 组合索引

索引是在存储引擎中实现,而不是在服务器层中实现。所以每种存储引擎的索引都不一定相同。

普通索引

最基本的索引,没有任何限制。普通索引的唯一任务就是加快对数据的访问速度,因此对那些最常出现查询条件或排序条件的数据列创建索引。

创建方式:

  • 创建索引
CREATE INDEX indexName ON mytable(username(length));
  • 修改表结构
ALTER mytable ADD INDEX [indexName] ON (username(length))
  • 创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );
  • 删除索引
DROP INDEX [indexName] ON mytable;

唯一索引

唯一索引的值必须唯一,但可以有空值。如果是组合索引,则列值的组合必须唯一。

创建方式:

  • 创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
  • 修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
  • 创建表的时候直接指定
CREATE TABLE mytable( 
ID INT NOT NULL, 
username VARCHAR(16) NOT NULL, 
UNIQUE [indexName] (username(length)) );

主键索引

是一种特殊的唯一索引,不允许有空值。

一般是在创建表的时候创建主键索引

外键索引与之类似。

组合索引

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。

七、索引使用的技巧

索引设计

  • 索引字段尽量使用数字类型

    因为字符型会降低查询和连接的性能,增加存储开销。因为引擎在处理查询和连接是会逐个比较字符串的每一个字符,而数字类型比较一次就可以了。

  • 尽量不要让字段的默认值为null

    有空值很难进行查询优化。只要列中包含有null,列就不会被包含在索引中,符合索引中只要有一列含有null,这个符合索引就失效了
    所以设计时将字段设为not null,或者设置默认值为0或空字符串.

  • 前缀和索引选择性

    对串进行索引,尽可能指定一个前缀长度。对于Blob,text或很长的varchar则必须用前缀索引,因为Mysql不允许索引这些列的完整长度。

    前缀索引是一种使索引更小、更快的有效办法。但有缺点:Mysql无法使用前缀索引做order by或group by,也无法使用前缀索引做覆盖扫描。

    使用前缀索引可以提高索引效率但是会降低索引的选择性。索引的选择性是指:不重复的索引值和数据表中的所有记录的比值。 选择性高的索引可以让Mysql在查找使过滤掉更多的行。唯一索引的选择性是1,所以性能最好。

    所以前缀的长度要适合,并且保证高的索引选择性。如何选择合适的前缀长度可以通过

select count(*) as cnt,city 
from sakila.city_demo 
group by city 
order by cnt desc 
limit 10;

select count(*) as cnt,left(city,7) as perf 
from sakila.city_demo 
group by city 
order by cnt desc 
limit 10;
  • 使用唯一索引
  • 使用组合索引代替多个列索引
  • 注意重复/冗余的索引,不使用的索引

索引使用:

  • 对大得文本进行索引,使用全文索引而不要使用like “%…%”
  • like语句不要以%或-开头,因为不会使用索引。
  • 不要再列上进行运算
    索引列不能是表达式的一部分,也不是函数的参数
    如一下两个查询无法使用索引:
    (1)表达式:
select actor_id from sakila.actor where actor_id+1=5;

(2)函数参数:

select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10;
  • 尽量不要使用NOT IN、<>、!=操作,引擎将放弃使用索引而进行全表扫描。
    对于not in,可以用not exits或者(外连接+判断为空)来代替
    对于<>,用其他功能的操作符代替

  • or条件
    用or分割开的条件,如果or前的条件列有索引,而后面的列没有索引,那么设计的索引不会被用到。可以用union all代替

  • 任何地方都不要使用 select * from t,不要反悔用不到的字段

  • 如果Mysql估计使用索引比全表扫描更慢,则不使用索引。当索引列有大量数据重复时,查询可能不会去利用索引。

相关推荐
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页