Skip to content

Latest commit

 

History

History
216 lines (111 loc) · 15.4 KB

MySQL-Index.md

File metadata and controls

216 lines (111 loc) · 15.4 KB

MySQL索引

是什么

  • MYSQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构,所以说索引的本质是:数据结构

  • 索引的目的在于提高查询效率,可以类比字典、 火车站的车次表、图书的目录等 。

  • 可以简单的理解为“排好序的快速查找数据结构”,数据本身之外,数据库还维护者一个满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。下图是一种可能的索引方式示例。

    1.png

    左边的数据表,一共有两列七条记录,最左边的是数据记录的物理地址

    为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值,和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到对应的数据,从而快速检索出符合条件的记录。

  • 索引本身也很大,不可能全部存储在内存中,一般以索引文件的形式存储在磁盘上

  • 平常说的索引,没有特别指明的话,就是B+树(多路搜索树,不一定是二叉树)结构组织的索引。其中聚集索引,次要索引,覆盖索引,符合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。此外还有哈希索引等。

优势

  • 提高数据检索效率,降低数据库IO成本

  • 降低数据排序的成本,降低CPU的消耗

劣势

  • 索引也是一张表,保存了主键和索引字段,并指向实体表的记录,所以也需要占用内存
  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。 因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段, 都会调整因为更新所带来的键值变化后的索引信息

mysql索引分类

  • 单值索引:一个索引只包含单个列,一个表可以有多个单列索引
  • 唯一索引:索引列的值必须唯一,但允许有空值
  • 复合索引:一个索引包含多个列

基本语法:

  • 创建:

    • 创建索引:CREATE [UNIQUE] INDEX indexName ON mytable(username(length));

      ?> Tip 如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。

    • 修改表结构(添加索引):ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)

  • 删除: DROP INDEX [indexName] ON mytable;

  • 查看: SHOW INDEX FROM table_name\G --可以通过添加 \G 来格式化输出信息。

  • 使用ALERT命令

    • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。

    • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。

    • ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。

    • **ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):**该语句指定了索引为 FULLTEXT ,用于全文索引。

哪些情况需要创建索引

  1. 主键自动建立唯一索引

  2. 频繁作为查询条件的字段

  3. 查询中与其他表关联的字段,外键关系建立索引

  4. 单键/组合索引的选择问题,who?高并发下倾向创建组合索引

  5. 查询中排序的字段,排序字段通过索引访问大幅提高排序速度

  6. 查询中统计或分组字段

哪些情况不要创建索引

  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且分布均匀的表字段,只应该为最经常查询和最经常排序的数据列建立索引(如果某个数据类包含太多的重复数据,建立索引没有太大意义)
  4. 频繁更新的字段不适合创建索引(会加重IO负担)
  5. where条件里用不到的字段不创建索引

MySQL索引结构

介绍索引之前,先了解下磁盘IO与预读

磁盘读取数据靠的是机械运动,每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分,寻道时间指的是磁臂移动到指定磁道所需要的时间,主流磁盘一般在5ms以下;旋转延迟就是我们经常听说的磁盘转速,比如一个磁盘7200转,表示每分钟能转7200次,也就是说1秒钟能转120次,旋转延迟就是1/120/2 = 4.17ms;传输时间指的是从磁盘读出或将数据写入磁盘的时间,一般在零点几毫秒,相对于前两个时间可以忽略不计。那么访问一次磁盘的时间,即一次磁盘IO的时间约等于5+4.17 = 9ms左右,听起来还挺不错的,但要知道一台500 -MIPS的机器每秒可以执行5亿条指令,因为指令依靠的是电的性质,换句话说执行一次IO的时间可以执行40万条指令,数据库动辄十万百万乃至千万级数据,每次9毫秒的时间,显然是个灾难。下图是计算机硬件延迟的对比图,供大家参考:

various-system-software-hardware-latencies

考虑到磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,一般为4k或8k,也就是我们读取一页内的数据时候,实际上才发生了一次IO,这个理论对于索引的数据结构设计非常有帮助。

?> 首先要明白索引(index)是在存储引擎(storage engine)层面实现的,而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型,它们的实现和行为也可能有所差别。

BTree索引

  • MyISAM存储引擎,使用B+Tree的数据结构,它相对与BTree结构,所有的数据都存放在叶子节点上,且把叶子节点通过指针连接到一起,形成了一条数据链表,以加快相邻数据的检索效率

  • MyISAM引擎索引结构的叶子节点的数据域,存放的并不是实际的数据记录,而是数据记录的地址。索引文件与数据文件分离,这样的索引称为***"非聚簇索引"***。MyISAM的主索引与辅助索引区别并不大,只是主键索引不能有重复的关键字。

  • InnoDB引擎索引结构的叶子节点的数据域,存放的就是实际的数据记录(对于主索引,此处会存放表中所有的数据记录;对于辅助索引此处会引用主键,检索的时候通过主键到主键索引中找到对应数据行),或者说,InnoDB的数据文件本身就是主键索引文件,这样的索引被称为***“聚簇索引”***,一个表只能有一个聚簇索引

  • 检索原理

    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并不真实存在于数据表中。

【查找过程】 如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。

真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

b+树性质

  1. 通过上面的分析,我们知道IO次数取决于b+数的高度h,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(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)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

Hash索引

  • 主要就是通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法),将数据库字段数据转换成定长的Hash值,与这条数据的行指针一并存入Hash表的对应位置;如果发生Hash碰撞(两个不同关键字的Hash值相同),则在对应Hash键下以链表形式存储。

    检索算法:在检索查询时,就再次对待查关键字再次执行相同的Hash算法,得到Hash值,到对应Hash表对应位置取出数据即可,如果发生Hash碰撞,则需要在取值时进行筛选。目前使用Hash索引的数据库并不多,主要有Memory等。

    MySQL目前有Memory引擎和NDB引擎支持Hash索引。

  • Hash索引的弊端

    一般来说,索引的检索效率非常高,可以一次定位,不像B-Tree索引需要进行从根节点到叶节点的多次IO操作。有利必有弊,Hash算法在索引的应用也有很多弊端。

    1. Hash索引仅仅能满足等值的查询,范围查询不保证结果正确。因为数据在经过Hash算法后,其大小关系就可能发生变化。
    2. Hash索引不能被排序。同样是因为数据经过Hash算法后,大小关系就可能发生变化,排序是没有意义的。
    3. Hash索引不能避免表数据的扫描。因为发生Hash碰撞时,仅仅比较Hash值是不够的,需要比较实际的值以判定是否符合要求。
    4. Hash索引在发生大量Hash值相同的情况时性能不一定比B-Tree索引高。因为碰撞情况会导致多次的表数据的扫描,造成整体性能的低下,可以通过采用合适的Hash算法一定程度解决这个问题。
    5. Hash索引不能使用部分索引键查询。因为当使用组合索引情况时,是把多个数据库列数据合并后再计算Hash值,所以对单独列数据计算Hash值是没有意义的。

full-text全文索引

  • 全文索引也是MyISAM的一种特殊索引类型,主要用于全文索引,InnoDB从MYSQL5.6版本提供对全文索引的支持。

  • 它用于替代效率较低的LIKE模糊匹配操作,而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。

  • 同样使用B-Tree存放索引数据,但使用的是特定的算法,将字段数据分割后再进行索引(一般每4个字节一次分割),索引文件存储的是分割前的索引字符串集合,与分割后的索引信息,对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

R-Tree空间索引

空间索引是MyISAM的一种特殊索引类型,主要用于地理空间数据类型

MySQL索引分类

数据结构角度

  • B+树索引
  • Hash索引
  • Full-Text全文索引
  • R-Tree索引

从物理存储角度

  • 聚集索引(clustered index)

  • 非聚集索引(non-clustered index),也叫辅助索引(secondary index)

    聚集索引和非聚集索引都是B+树结构

从逻辑角度

  • 主键索引:主键索引是一种特殊的唯一索引,不允许有空值
  • 普通索引或者单列索引
  • 多列索引(复合索引):复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合
  • 唯一索引或者非唯一索引
  • 空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。 MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建

MySQL高效索引

覆盖索引(Covering Index),或者叫索引覆盖, 也就是平时所说的不需要回表操作

  • 就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

  • 索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据,当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含(覆盖)满足查询结果的数据就叫做覆盖索引。

  • 判断标准

    使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

美团技术-MySQL索引原理及慢查询优化