不断的学习,我们才能不断的前进
一个好的程序员是那种过单行线马路都要往两边看的人

1. Mysql索引

索引是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,特别是当表中的数据量越来越大的时候。
索引是在存储引擎层实现的而不是服务器层。

1.1 索引基础

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。

索引可以包含一个或多个列的值,如果索引包含多个列,那么列的顺序也十分重要,因为Mysql只能高效地使用索引的最左前缀列

1.1.1 索引的类型

索引可以有很多种类型,为不同的场景使用不同的类型,在Mysql中索引是在存储引擎而不是在服务器层实现的。

B+Tree索引

B+树的非叶节点不存储数据,只存储索引和指针。B+树的每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历。B+树索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之从索引的根节点开始进行搜索。

B+树每次新建节点时,直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,就实现了一个node只需一次I/O。

一个结点就是一个页的数据。

一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,I/O存取的消耗要高几个数量级,所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。

B+Tree索引的高度

哈希索引

哈希索引是基于哈希表的实现,只有精确匹配索引所有列的查询才有效,对于每一行数据,存储引擎都会对所有列的索引计算一个哈希码。哈希索引会将所有的哈希码存储在索引中,同时哈希表保存指向每个数据行的指针。

如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中。

哈希索引的查找是很快的,因为只存储对应的哈希值。但是哈希索引不能存储字段值、也无法用于排序、也不支持部分索引列匹配查找、哈希冲突的话,索引维护代价很高。

InnoDb引擎有自适应哈希索引,会在内存中给予B-Tree索引之上再创建一个哈希索引,这样就让B-tree也具有哈希索引的优点。查找的时候使用B-Tree查找,但是它使用哈希值而不是键本身来进行索引查找。

全文索引

全文索引是一种特殊类型的索引,它查找的是文本中的关键字,而不是直接比较索引中的值。

InnoDB存储引擎

InnoDB也使用B+Tree作为索引结构,InnoDB的数据文件本身就是索引文件,在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。叶节点包含了完整的数据记录,这种索引叫做聚集索引,这种索引也叫聚簇索引,按主键聚集。InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节。

InnoDB的二级索引,叶结点值存储相应记录主键的值而不是地址。

MyISAM索引文件和数据文件是分离的, 叶节点用一个指针指向文件的存储地址。

索引对如下类型的查询有效:全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另外一列、只访问索引的查询

但是对于索引有以下限制:如果不是按照最左列开始查找,则无法使用索引;不能跳过索引中的列;如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找。

1.1.2 索引原则

  • 索引不是越多越好
  • 不要对数据量经常变动的数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在经常查询的字段上

1.1.3 索引的优点

索引可以让服务器快速定位到表的指定位置,但这并不是索引的唯一作用。

索引的优点:

  • 索引大大减少了服务器需要扫描的数据量
  • 索引可以帮助服务器排序和临时表
  • 索引将随机I/O变成顺序I/O

参考文档

1.2 高性能的索引策略

1.2.1 独立的列

独立的列是指索引列不能是表达式的一部分,也不能是函数的参数。

1.2.2 前缀索引和索引选择性

当索引很长的列时,会让索引变得很慢,通常可以索引开始的部分字符,可以大大节约索引空间。但是也会降低索引的选择性,索引选择性是指,不重复的索引值(称为基数)和数据表的记录总数的比值。索引的选择性越高则查询效率越高。因为选择性高的索引,可以在查找时过滤更多的行。唯一索引的选择性是1。

计算合适的前缀长度的一个办法是计算完整列的选择性,并使前缀的选择性接近于完整列的选择性。

前缀索引无法做覆盖扫描,也无法进行排序。

后缀索引:把字符串反转后存储

1.2.3 多列索引

在多个列上创建独立的单列索引是不能提高MySql的查询性能,Mysql有一种索引合并的策略,一定程度上可以使用多个单独的索引来定位指定的行。

当出现服务器对多个索引做相交操作时(多个AND条件),通常需要一个包含所有相关列的多列索引。

当服务器需要对讴歌索引做联合操作时(多个OR条件),通常需要消耗大量CPU和内存资源在算法的缓存、排序和合并操作上。

1.2.4 选择合适的索引列顺序

索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列,所以多列索引的列顺序至关重要。

1.2.5 聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDb的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表中有聚簇索引时,它的数据行实际上存在索引的叶子页中,因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引

如果没有定义主键,InnoDB会选择一个唯一的非空索引代替,如果没有这样的索引,InnoDb会隐式定义一个主键作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。

优点:

  • 可以把相关数据保存在一起
  • 数据访问更快
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点:

  • 聚簇数据最大限度的提高了I/O密集型应用的性能,但如果数据全部都放在内存中,那么访问顺序就不重要了,聚簇索引就没有什么优势了。
  • 插入速度严重依赖于插入顺序。
  • 更新聚簇索引列的代码很高,因为会强制InnoDB将每个被更新的行移动到新的位置。
  • 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能会面临页分裂的问题。当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行。
  • 聚簇索引可能导致全表扫描变慢,尤其是行笔记稀疏,或者由于页分裂导致数据存储不连续的时候。
  • 二级索引可能比想象大,因为二级索引的叶子节点包含了引用行的主键列。
  • 二级索引访问需要两次索引查找,而不是一次。(因为二级索引叶子节点保存的不是指向行的物理地址,而是行的主键值。先根据二级索引查找行对应的主键值,然后去聚簇索引中根据这个值查找查找对应的行。)

二级索引的叶子节点存储的是主键值,可以减少当出现行移动或者数据页分裂时二级索引的维护工作。

避免使用随机的(不连续且分布范围非常大)聚簇索引,特别是对于I/O密集型的应用。使用UUID来作为聚簇索引会很糟糕,因为它使得聚簇索引的插入变得完全随机。插入新的一行的主键值不一定比之前插入的值大,所以InnoDB无法简单的总是把新行插入到索引的最后,而是需要为新的行寻找合适的位置。通常是已有数据的中间位置并分配空间。但是这会导致数据分布不够优化,而且会有以下缺点:

  • 由于频繁的页分裂,页会变得稀疏并被不规则填充,所以最终数据会有碎片。
  • 因为写入是乱序的,InnoDB不得不频繁地做页分裂操作,页分裂会导致移动大量数据。
  • 写入的目标页可能已经刷到磁盘上并从缓存中移除,InnoDb在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O。

当达到页的最大填充因子时(默认是页大小的15/16,留出部分用于以后修改),下一条记录就会写入新的页中。

使用InnoDB时应该尽可能地按主键顺序插入数据,并且尽可能使用单调增加的聚簇键的值来插入新行。

1.2.6 覆盖索引

通过索引列来直接获取列的数据,这样就不再需要读取数据行。如果一个索引包含所有需要查询的字段的值,我们就称之为覆盖索引。覆盖索引必须要存储索引列的值。

覆盖索引的优点:

  • 索引的数目远小于数据行大小,极大的减小数据访问量。
  • 因为索引是按照列值顺序存储的,所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。
  • InnoDB的二级索引在叶子节点中保存了行的主键值,所以如果二级索引能够覆盖查询,则可以避免对主键索引的二次查询。

Mysql只能使用B+Tree索引做覆盖索引。

延迟关联操作:

# 无法使用覆盖索引的sql语句。
select * from products where actor='SEAN CARREY' and title like '%APOLLO%';
# 延迟关联操作的优化后的语句。
select * from products Join(
	select prod_id from products where actor='SEAN CARREY' and title like '%APOLLO%'
) as t1 on (t1.prod_id=products.prod_id)

覆盖索引包含了(artist, title,prod_id)三列,所以在查询的第一阶段Mysql可以使用覆盖索引,然后根据覆盖索引获取的prod_id来匹配需要的所有列的值。

1.2.7 使用索引扫描来排序

Mysql可以通过两种方式来生成有序的结果:排序操作 或者 按索引顺序扫描。

扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列,那就不得不每扫描一条索引记录就回表查询一次对应的行,这基本上都是随机I/O。因此按索引顺序读取数据的速度通常比顺序地全表扫描慢,尤其是在I/O密集型的工作负载。

Mysql可以使用同一个索引即满足排序,又用于查找行。只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向都一样时,才能够使用索引来对结果做排序。如果查询需要关联多张表,则只有当ORDER BY子句引用的字段全部为第一个表时,才能使用索引做排序

在前导列为常量的时候ORDER BY子句可以不满足索引的最左前缀的要求。

1.2.8 冗余和重复索引

Mysql允许在相同列上创建多个索引。重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。冗余索引和重复索引有一些不同,如果创建了索引(A,B),再创建索引(A)就是冗余索引,因为这是前一个索引的前缀索引。将一个索引(A),扩展为(A,ID),其中ID是主键,对于InnoDB来说主键列已经包含在二级索引中列,所以这也是冗余的

索引可以让查询锁定更少的行。InnoDB只有在访问行的时候才会对其加锁,而索引能够减少InnoDB访问的行数,从而减少锁的数量。

InnoDB在二级索引上使用共享锁,但访问主键索引需要排他锁。

减少索引和数据的碎片

B+Tree索引可能会碎片化,这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。B+Tree需要随机磁盘访问才能定位到叶子页,所以随机访问是不可避免的,然而如果叶子页在物理分布上是顺序且紧密的,那么查询的性能就会更好。

数据碎片的三种类型:

  • 行碎片:这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录,行碎片也会导致性能下降。
  • 行间碎片:指的是逻辑上顺序的页,或者行在磁盘上不是顺序存储的。
  • 剩余空间碎片:指数据页中有大量的空余空间,这会导致服务器读取大量不需要的数据,从而造成浪费。

Mysql的锁?行级锁、表级锁、页级锁?以及锁的对象?

行级锁
Mysql的行级锁是基于索引实现的,加锁的对象是索引而非具体的数据。
当加锁操作使用聚簇索引时,Innodb会锁住聚簇索引,而使用非聚簇索引锁时,会先锁住非聚簇索引,然后在锁的非聚簇索引对应的聚簇索引。

行级锁加锁条件必须有对应的聚簇索引,否则会退化为表级锁。

Reference

B+Tree索引的高度


目录