引擎篇

MyISAM

  • Myisam是Mysql的默认存储引擎,当create创建新表时,未指定新表的存储引擎时,默认使用Myisam。

  • 每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同,扩展名分别是.frm(存储表定义)、.MYD (MYData,存储数据)、.MYI (MYIndex,存储索引)。数据文件和索引文件可以放置在不同的目录,平均分布io,获得更快的速度。

  • 它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一 .

InnoDB

  • InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎,InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

  • 用于事务处理应用程序,具有众多特性,包括ACID 事务支持。

Memory

将所有数据保存在RAM 中,在需要快速查找引用和其他类似数据的环境下,可提供极快的访问。

特点Myisam BDB MemoryInnoDB Archive
存储限制没有没有64TB没有
事务安全支持支持
锁机制表锁页锁表锁行锁行锁
B 树索引支持支持支持支持
哈希索引支持支持
全文索引支持
集群索引支持
数据缓存支持支持
索引缓存支持支持支持
数据可压缩支持支持
空间使用N/A非常低
内存使用中等
批量插入的速度非常高
支持外键支持

Schema与数据类型优化

数据类型优化

  • 一般情况下,应该尽量使用可以正确存储数据的最小数据类型。

  • 简单的数据类型通常需要更少的CPU周期。

  • 尽量避免使用NULL,原因是由于NULL可能需要单独的位(bit)去存储或者NULL使得索引、索引统计、值比较都更为复杂。

范式的优点和缺点

  • 范式化的更新操作会比反范式化的快。

  • 当数据较好范式化的时候,只有很少或者没有重复数据,所以只需要修改更少的数据。

  • 范式化的表通常更小。

  • 很少有冗余数据意味着检索列表数据更少需要distinct或者group by语句。

反范式的优点和缺点

  • 反范式的schema所有数据都在一张表中,可以很好的避免关联,还可以避免随机IO(和存储引擎有关)。

总结:

  • 尽可能避免过度设计,例如会导致复杂查询的schema设计,或者有很多列的表的设计。

  • 使用小而简单的合适数据类型,除非真实数据模型中确切的需要,否则应尽可能的避免NULL值。

  • 尽量使用相同的数据类型存储相似或者相关的值,尤其是在关联条件中要使用的列。

  • 避免使用MySQL已经遗弃的特性,例如浮点数的精度,或者整数的显示宽度。

  • 小心使用ENUM和SET。避免使用BIT。

高性能索引创建

系统从磁盘读取数据到内存是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。

InnoDB存储引擎中有页(page)的概念,页是其磁盘管理的最小单位。

索引基础

B-Tree索引(平衡多路查找树)

b-tree

B-Tree 通常意味着所有的值都是按照顺序存储的,并且每一个叶子页到根的距离相同。B-Tree索引能够加快访问数据的速度,存储引擎不需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放指向子节点的指针,存储引擎根据这些指针向下层查找。指针中定义了子节点页中值的
上限和下限。最终找到该值或者不存在。

叶子节点比较特殊,他们指向的是被索引的数据,而不是其他的节点页。

树的深度和表的大小直接相关。

b-tree

可以使用B-Tree索引的查询类型:

全值匹配:全值匹配指的是和索引的所有列进行匹配,例如前面提到的索引可用于查找姓名为Cuba Allen 1960-01-01。

匹配最左前缀:可用于查找索引的第一列。

匹配列前缀:也可以匹配某一列的值的开头部分。

匹配范围值:遵循最左前缀的前提下,范围查找。

精确匹配某一列并范围匹配另外一列:可用于查找所有姓为Allen,并且名字是字母K开头的人。

只访问索引的查询:B-Tree通常可以支持“只访问索引的查询”,即查询只需要访问索引,而无需访问数据行。

B-Tree使用限制:

必须遵循最左原则。

如果查询中某个列的范围查询,则其右边所有列都无法使用索引优化查找。

B+Tree索引

B+Tree是在B-Tree基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+Tree实现其索引结构。

在B-tree的基础上,为叶子节点增加链表指针,而且所有的关键字都在叶子节点中出现,且数据只存储在叶子节点中。非叶子节点的关键字仅作为叶子节点的索引。

hash索引

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

在MySQL中,只有Memory引擎显式支持哈希索引且支持非唯一哈希索引的。

哈希索引每个槽点编号是有序的。

哈希索引使用限制:

哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行。

哈希索引并不是按照索引值顺序存储的,所以无法用于排序。

哈希索引也不支持部分索引列的匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的。

哈希索引支持等值比较查询,包括=、in()、<=>也不支持任何范围查询,例如WHERE price > 100。

访问哈希索引的数据非常快,除非有很多哈希冲突。

如果哈希冲突很多的话,一些索引维护操作的代价也会很高。

除了Memory引擎外,NDB集群引擎也支持唯一哈希索引,且在NDB集群引擎中作用非常特殊。

InnoDB引擎有一个特殊的功能叫“自适应哈希索引”。当InnoDB引擎注意到某些索引值被使用的非常频繁时,他会在内存中基于B-Tree索引之上再创建一个哈希索引,这样就让B-Tree索引也具有哈希索引的一些优点,比如快速哈希查找。

全文索引

全文索引是一种特殊类型索引,它查找的是文本中的关键词,而不是直接比较索引中的值。适用于MATCH AGINST操作,而不是普通的WHERE条件操作。

索引的优点

  • 索引大大减少了服务器需要扫描的数据量。

  • 索引可以帮助服务器避免排序和临时表。

  • 索引可以将随机IO变为顺序IO。

高性能的索引策略

独立的列

独立的列是指索引列不能是表达式的一部分,也不能是函数的参数。我们应该简化WHERE条件的习惯,始终将索引列单独放在比较符号的一侧。

例如:select actor_id from sakila.actor where actor_id + 1 = 4

前缀索引和索引的选择性

问题: 有时候需要索引很长的字符串,这会让索引变的大且慢。

通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但会降低索引的选择性。

一般情况下对于Blob、text或者很长的varchar()类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。

前缀的基数应该接近于完整列的基数。

多索引列

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。

选择合适的索引列顺序

多列索引的列顺序经典法则:当不需要考虑排序和分组时,将选择性最高的列放到索引最前列。大都情况下是适用的,也需要根据业务去考虑一些特殊情况。

需要考虑哪些运行频率最高的查询来调整索引列的顺序,让这种情况下索引的选择性最高。

聚簇索引

聚簇索引并不是一种单纯的索引类型,而是一种数据存储方式。具体细节依赖其实现方式,但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时,它的数据行实际存放在索引的叶子页中。术语“聚簇”标识数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。

由于是存储引擎自己实现聚簇索引,因此不是每个引擎都支持聚簇索引。

叶子页包含了行的全部数据,但是节点页只包含了索引列。InnoDB将通过主键聚集数据,这也就是说图中的“被索引列”就是主键列。

cluster-index

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

聚簇索引的优点:

  • 可以把相关数据保存在一起。例如实现电子邮箱时,可以根据用户ID来聚集数据,这样只需要从磁盘读取少量的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引,则每封邮件都可能导致一次磁盘IO。

  • 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中,因此从聚簇索引中获取数据通常比在非聚簇索引查找的要快。

  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引缺点:

  • 聚簇索引最大限度的提高了IO密集型应用的性能,但如果数据全部放在内存中,则访问的顺序就没有那么重要了,聚簇索引也就没有什么优势了。

  • 插入速度严重依赖插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据,那么在加载完成后最好使用optimize table命令重新组织一下表。

  • 更新聚簇索引列的代价很高,因为会强制InnoDB将每个被动更新的行移动到新的位置。

  • 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳改行,这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。

  • 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。

  • 二级索引(非聚簇索引)可能比想象的要更大,因为在二级索引的叶子节点包含了引用行的主键列。

  • 二级索引访问需要两次索引查找,而不是一次。二级索引叶子节点保存的不是指向行的物理位置的指针,而是行的主键值。这意味着通过二级索引查找行,存储引擎需要找到二级索引的叶子节点获得对应的主键值,然后根据这个值去聚簇索引中查找对应的行。对于InnoDB,自适应性哈希索引能够减少这样重复的工作。

InnoDB和MyISAM的数据分布对比

聚簇索引和非聚簇索引的数据分布有区别,以及对应的主键索引和二级索引的数据分布也有区别。

例如:

1
2
3
4
5
6
7
create table    layout_test{
col1 int not null,
col2 int not null,
primary key(col1),
key(col2)

}

假设该表主键取值1-10000,按照随机顺序插入并使用OPTIMIZE TABLE命令做了优化。换句话说,数据在磁盘上的存储已经做到最优,但行的顺序是随机的。

列col2的值是从1-100的随机值,所以有很多重复的值。

MyISAM数据(非聚簇)分布:

MyISAM按照数据插入顺序存储在磁盘上。

在行的旁边显示行号,从0开始递增。因为行是定长的,所以MyISAM可以从表的开头跳过所需的字节找到需要的行。

这种分布方式容易创建索引。

数据分布:

数据分布

主键分布:

表的主键

col2列索引分布:

col2列索引分布

InnoDB数据(聚簇)分布:

InnoDB支持聚簇索引,所以使用非常不同的方式存储同样的数据。

主键分布

该图显示了整个表,而不是只有索引。在InnoDB中,聚簇索引就是表,所以不像MyISAM那样需要独立的存储。

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引,InnoDB也会包含完整的主键列和剩下的其他列。

InnoDB二级索引的叶子节点中存储的不是“行指针”,而是主键值,并以此作为指向行的“指针”。利:减少了当出现行移动或者数据页分裂时二级索引的维护工作。弊:使用主键值当做指针会让二级索引占用更多的空间。

索引分布

上图显示了示例表col2的索引,每一个叶子节点都包含了索引列(col2),紧接着是主键值(col1)。

该图展示了B-Tree的叶子节点结构,InnoDB的非叶子节点包含了索引列和一个指向下级节点的指针(下一级节点可以是非叶子节点,也可以是叶子节点)。这对聚簇索引和二级索引都适用。

聚簇索引和非聚簇索引

InnoDB表中按主键顺序插入行

如果正在使用InnoDB表并且并没有什么数据需要聚集,可以定义一个代理键作为主键,最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据按顺序写入,对于根据主键做关联操作的性能也会更好。

最好避免随机(不连续且值分布范围非常大)聚簇索引,特别对IO密集型应用。从性能考虑,使用UUID作为聚簇索引,使得聚簇索引的插入变的完全随机,使得数据没有任何聚集特性。

聚簇索引插入顺序的索引值

如上图所示,因为主键的值是顺序的,所以InnoDB把每一条纪律都存储在上一条纪律后面。当达到页的最大填充因子时,下一条记录就会写入新的页中,一旦数据按照这种顺序的方式加载,主键页就会近似于被顺序的记录填满。

聚簇索引顺序插入缺点:

  • 对于高并发工作负载,在InnoDB中按主键顺序插入可能会造成明显的争用(主键引起间隙锁竞争)。

  • 另一个热点是AUTO_INCREMENT锁机制,如果遇到这个问题,可以重新设计表或者应用,或者修改innodb_autoinc_lock_mode配置。

非聚簇索引插入顺序的索引值

非聚簇索引因为新行的主键值不一定比之前插入的大,所以InnoDB无法简单的总是把新行插入到索引的最后,而是需要为新的行寻找需要的位置—通常是已有数据的中间位置—并且分配空间。还会增加很多额外的工作,并导致数据分布不够优化。

非聚簇索引插入缺点:

  • 写入的目标页可能已经刷到磁盘上并从缓存中移除,或者还没有被加载到缓存中,InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这样会导致大量的随机IO。

  • 因为写入是乱序的,InnoDB不得不频繁的做页分裂操作,以便新的行分配空间。页分裂会导致移动大量数据,一次插入最少需要修改三个页而不是一个页。

  • 由于频繁的页分裂,页会变的稀疏并被不规则的填充,所以最终会有数据碎片。

覆盖索引

通常大家会根据查询的where条件来创建索引,不过这只是优化索引的一个方面。如果一个索引包含所有需要查询字段的值我们就称之为“覆盖索引”。

覆盖索引的优点:

  • 索引条目通常远小于数据行大小,所以如果只需要读取索引,那MySQL就会极大的减少数据访问量。这对缓存的负载非常重要,因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对IO密集型应用也有帮助,因为索引比数据更小,更容易全部放入内存中(MyISAM压缩索引以变得更小)。

  • 因为索引是按照列值顺序存储的,所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多。(MyISAM和Percona XtraDB,甚至可以通过OPTIMIZE命令使得索引完全顺序排列,这样简单的范围查询能使用完全顺序的索引访问)

  • 一些存储引擎如MyISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用。

  • 由于InnoDB的聚簇索引,覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。

不是所有的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引等都不存储索引列的值,所以MySQL只能使用B-Tree做索引覆盖。

当发起一个索引覆盖查询时,在Explain的Extra列可以看到“Using index”的信息(Extra列的Using index和type列的inxex搞混淆,其实两者我完全不同,type列标识查询访问数据方式,也称之为join type)。

索引覆盖查询可能还有很多陷阱可能导致无法实现优化。MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了WHERE条件中的字段,但不是整个查询涉及的字段。如果条件为假,MySQL5.5和更早版本也总是会回表获取数据行,尽管并不需要这一行且最终也会被过滤掉。

例如:

1
2

explain select * from products where actor='scan carrey' and title like '%APOLLO%' --这条语句使用的是Extra:using where

原因:

  • 没有任何索引能够覆盖这个查询。因为查询从表中选择了所有列,而没有任何索引覆盖了所有的列。MySQL存在另一个途径:WHERE条件中的列是有索引可以覆盖的,因此MySQL可以使用该索引找到对应的actor并检查title是否匹配,过滤之后再读取需要的数据行。

  • MySQL不能再索引中执行LIKE操作。这是底层存储引擎API的限制,MySQL5.5和更早版本中只允许在索引中做简单比较操作。MySQL能在索引中做最左前缀匹配的LIKE查询,存储引擎就无法比较匹配。MySQL服务器只能提取数据行的值而不是索引值来做比较。

解决方案:

先将索引扩展至3个(artist,title,prod_id),然后按照如下方式重写查询:

1
2

explain select * from products join(select prob_id from products where actor='sean carrey' and title like '%APOLLO%') as t1 on(t1.prod_id=products.prod_id)

Extra: using where;using index

我们把这种方式叫做延迟关联,因为延迟了对列的访问。在查询的第一阶段MySQL可以使用覆盖索引,在FROM子句的子查询中找到匹配的prod_id,然后根据这些prop_id值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询,但总算比完全无法利用索引覆盖的好。

这样的优化效果取决于WHERE条件匹配返回的行数。举个例子:

假设products有100w行数据,我们使用上面两个SQL在不同的三个场景中测试。

  1. sean carrey 出演了3w部作品,2w部标题中包含Apollo。

  2. sean carrey 出演了3w部作品,40部标题中包含Apollo。

  3. sean carrey 出演了50部作品,10部标题中包含Apollo。

测试结果:








序号原查询优化后
155
2735
324002000

结果分析:

实例1中,查询返回了一个很大的结果集,因此看不到优化结果。大部分时间都花在读取和发送数据上了。

实例2中,经过索引过滤,尤其第二个条件过滤后只返回很少的结果集,优化效果很明显,优化查询的效率主要得益于只需要读取40行完整的数据行。

实例3中,显示了子查询效率反而下降的情况。因为索引过滤符合第一个条件的结果集已经很小,所以子查询带来的成本反而比从表中直接提取完整行更高。

使用索引扫描来做排序

MySQL有两种方式生成有序的结果:通过排序操作。按索引顺序扫描。

如果explain出来的type列的值为index,则说明MySQL使用索引扫描来做排序。

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

MySQL可以使用同一个索引既满足排序,又满足查找行。

只有当索引的列顺序和 order by子句的顺序完全一致,并且所有列的排序方向都一样时,MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表,则只有当order by子句引用的字段全部为第一个表时,才能使用索引做排序。order by子句和查找型查询的限制是一样的:需要满足索引的最左前缀的要求,否则MySQL都需执行排序操作,而无法利用索引排序。

有一种情况下 order by子句可以不满足索引的最左前缀的要求,就是前导列为常量的时候。如果WHERE子句或者JOIN子句中对这些列指定了常量,就可以弥补索引的不足。

例如:表rental在列(rental_date,inventory_id,customer_id)上有名为rental_date的索引。

建表:

1
2
3
4
5
6
7
8
create table rental(
primary key(rental_id),
unique key rental_date(rental_date,inventory_id,customer_id),
key idx_fk_inventory_id(inventory_id),
key idx_fk_customer(customer_id),
key idx_fk_staff_id(staff_id),
...
)

查询排序:

explain select rental_id,staff_id from sakila.rental where rental_date='2005-05-25' order by inventory_id, customer_id

即时order by 子句不满足索引的最左前缀的要求,也可以用于查询排序,这是因为索引的第一列被指定为一个常数。

下面这个查询可以利用索引排序,是因为查询为索引的第一列提供了常量条件,而使用第二列进行排序,将两列组合在一起,就形成了索引的最左前缀,总的来说只要查询满足最左前缀即可排序:

where rental_date='2005-05-05' order by inventory_id desc

下面是一些不能使用索引做排序的查询:

  • 这个查询使用了两种不同的排序方向,但索引列都是正序排序的:

    where rental_date ='2005-05-25' order by inventory_id desc,customer_id asc

  • 这个查询的order by 子句中引用了一个不在索引中的列:

    where rental_date ='2005-05-25' order by inventory_id,staff_id

  • 这个查询的where和order by中的列无法组合成索引的最左前缀:

    where rental_date ='2005-05-25' order by customer_id

  • 这个查询在索引列的第一列上是范围条件,所以MySQL无法使用索引的其余列:

where rental_date > '2005-05-25 order by ...

  • 这个查询在inventory_id上有多个等于条件,对于排序来说,这也是一种范围查询:

where rental_date='2015-05-25' and inventory_id in(1,2) order by customer_id

下面这个例子理论上是可以使用索引进行关联排序的,但由于优化器在优化时将film_actor表当做关联的第二张表,所以实际上无法使用索引:

explain select actor_id,title from sakila.film_actor inner join sakila.film using(film_id) order by actor_id

使用索引排序做排序的一个重要的用法是当查询同时有order by和limit子句的时候。

压缩索引

MyISAM使用前缀压缩来减少索引的大小,从而让更多的索引可以放入到内存中,这在某些情况下能极大的提高性能。默认只压缩字符串,但通过参数设置也可以对整数进行压缩。

MyISAM的压缩方法是:先完全保存索引块的第一个值,然后将其他值和第一个值进行比较得到相同的前缀的字节数和剩余的不同后缀部分,把这部分存储起来即可。

压缩块使用更少的时间,代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值,所以无法使用二分查找而只能从头开始。正序快,倒序慢。

使用pack_keys来控制索引压缩的方式。

冗余和重复的索引

MySQL允许在相同列上创建多个索引,并且需要单独维护重复的索引,并且优化器在优化查询的时候也需要逐个的进行考虑,影响性能。

重复索引是指在相同列上按照相同的顺序创建的相同类型的索引。

错误示例:

1
2
3
4
5
6
7
8

create table test(
id int not null primary key,
a int not null,
b int not null,
unique(id),
index(id)
)engine=InnoDB;

上面其实给id创建了3条索引,MySQL的唯一限制和主键限制都是通过索引实现的。

通常没有理由在同一列上创建多条索引,除非在同一列上创建不同类型的索引来满足查询需求。例如key(id)和fulltext key(id)就是两种类型的索引。

冗余和重复有些不同,如果创建了索引(A,B),再创建(A),则(A)就是冗余的。这种冗余只是对B-Tree来说的。另外,其他不同类型的索引(哈希索引或者全文索引)也不会是B-Tree索引的冗余索引,而无论覆盖的索引列是什么。

冗余索引通常发生在为表添加新索引的时候。

大多数情况下都不需要冗余索引,应该尽量扩展已经有的索引而不是创建新索引。有时候出于性能问题也考虑冗余索引,因为扩展已有的索引会导致其变的太大,从而影响其他使用该索引的查询性能。

未使用的索引

除了冗余索引和重复索引,可能还会有一些服务器永远不用的索引。建议删除。

最简单有效的办法是在Percona Server或者MariaDB中先打开userstates服务器变量,然后让服务器正常运行一段时间,再通过查询Information_schema.index_statistics就能查到每个索引的使用频率。

还可以使用Percona Toolkit中的pt-index-usage,该工具可以读取查询日志,并对日志中的每条查询进行explain操作,然后打印出关于索引和查询的报告。

索引和锁

索引可以让查询锁定更少的行。

优点:

  • InnoDB的行锁效率很高,内存使用也很少,但是锁定行的时候仍然会带来额外的开销。

  • 锁定超过需要的行会增加锁争用并减少并发性。

InnoDB只有在访问行的时候才会对其加锁,而索引能够减少InnoDB访问行数,从而减少锁的数量。只有当InnoDB在存储引擎层能够过滤所有不需要的行时才有效。

例如:

1
2
3
4

set autocommmit = 0;
begin;
select actor_id from sakila.actor where actor_id < 5 and actor_id <> 1 for update;
actor_id
2
3
4

这条查询仅仅会返回2-4行数据,但是实际上获取了1-4行之间的行的排它锁。锁住第一行是因为MySQL为该查询选择的执行计划是索引范围扫描:





idselect_typetabletypekeyExtra
1SIMPLEactorrangePRIMARYUsing where;Using index

问题:如果不能使用索引查找和锁定行的话问题可能会更糟糕,MySQL会做全表扫描并锁住所有的行。

InnoDB在二级索引上使用共享锁,但访问主键索引需要排他锁。这消除了使用覆盖索引的可能性,并且使得select for updatelock in share mode或非锁定查询要慢的多。

索引案例学习

维护索引和表

维护表有3个目的:找到并修复损坏的表,维护准确的索引统计信息,减少碎片。

找到并修复损坏的表

表损坏(corruption)是件很糟糕的事情。对于MyISAM,表损坏通常是系统崩溃导致的。

损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题,严重时甚至还会导致数据库的崩溃。

修复:

  • check table,能找出大多数表和索引错误。有些存储引擎不支持该命令。

  • repair table,和check类似。

  • alter table innodb_tbl engine=innodb,修改表的存储引擎为当前引擎重建表。

  • 离线工具myisamchk

  • 将数据导出在重新导入。

  • 如果损坏的是行数据或者系统区域,以上办法无效。

  • 如果损坏系统区域或者行数据,可以从备份中恢复表,或者尝试从损坏的数据文件中尽可能的恢复数据。

如果InnoDB引擎的表出现损坏,那么一定是发生了严重的错误,需要立刻调查原因。具体细节可以查看MySQL手册。

更新索引统计信息

减少索引和数据的碎片

总结

三个原则:

  • 单行访问时很慢的。最好读取块中能包含尽可能多的所需要的行。使用索引可以创建位置引用以提升效率。

  • 按顺序访问范围数据是很快的,这有两个原因。第一、顺序IO不需要多次磁盘寻道,所以比随机IO快很多。第二、如果服务器能够按需要顺序读取数据,那么就不再需要额外的排序操作,并且group by查询也无需再做排序和将行按组进行聚合计算了。

  • 索引覆盖查询是很快的。

一般来说,我们建议按响应时间来对查询进行分析。

如果一个查询无法从所有可能的索引中获益,则应该看看是否可以创建一个更合适的索引来提升性能。

剖析MySQL查询

对查询进行性能剖析有两种方式,每种方式都有各自的问题。

剖析服务器负载

服务器端可以有效的审计效率低下的查询。

捕获MySQL的查询到日志文件中

慢查询日志是一种轻量而且功能全面的性能剖析工具,是优化服务器查询的利器。可以通过修改针对每个链接的long_query_time的阈值来捕获所以的查询。

通用日志在查询请求到服务器时进行记录,所以不包含响应时间和执行计划等重要信息。日志信息记录到数据库表中。

分析查询日志

工具:pt-query-digest

剖析报告:
查询日志

详细报告:
详细查询日志

剖析单条查询

show profile

show profile命令是在5.1版本之后引入的,默认是禁用的,可以通过set profiling=1动态的修改。这个功能有一定的作用,将来能会被Performance Schema所取代。

下面是对一个视图的剖析结果:

详细查询日志

详细查询日志

详细查询日志

剖析报告给出查询执行每个步骤花费的时间,看结果无法快速确定哪个步骤花费时间最多,因为输出是按照执行顺序排序,而不是花费的时间排序的。如果不使用show profile命令。还可以直接查询information_schema中对应的表,按照格式化输出。

详细查询日志

效果比看show profile输出的结果好很多,可以很直观的看到哪些步骤花费时长较长。

查询性能优化

查询慢的原因

查询最重要的是响应时间,如果要优化查询,实际上要优化其子任务,要么消除其中一些子任务,要么减少子任务的执行次数,要么让子任务运行更快。

MySQL执行查询的时候,查询需要在不同的地方花费时间,如网络、cpu、生成统计信息和执行计划、锁等待等。在每一个消耗大量时间的查询案例中,我们都能看到一些不必要的额外操作、某些操作被额外的执行了很多次、某些操作执行的太慢等。

优化查询的目的就是减少和消除这些操作所花费的时间。

慢查询基础:优化数据访问

查询性能低下最基本的原因是访问的数据太多。某些查询不可避免的需要筛选大量数据,大部分查询都可以通过减少访问的数据量的方式进行优化。对于低效查询可以分为两个步骤分析:

  • 1.确认应用程序是否检索大量超过需要的数据。如访问过多的行或者列。

  • 2.确定MySQL服务器层是否在分析大量超过需要的数据行。

是否向数据库请求了不需要的数据

  • 查询不需要的记录

  • 多表关联返回全部的列

  • 总是取出全部的列

  • 重复查询相同的数据

MySQL是否在扫描额外的记录

在确定查询只返回需要的数据以后,最简单的衡量查询开销的三个指标如下:

  • 响应时间

  • 扫描的行数

  • 返回的行数

响应时间:响应时间只是一个表面上的值。响应时间是服务时间和排队时间。服务时间指数据库处理这个时间花费的时间。排队时间指服务器因为等待某些资源而没有真正执行查询的时间。

快速上限估计:了解查询需要哪些索引以及它的执行计划是什么,然后计算大概需要多少个顺序和随机IO,再用其乘以在具体硬件条件下一次IO的消耗时间。

扫描的行数和返回的行数: 分析查询时,查看扫描的行数一定程度上可以反映出该查询找到需要的数据的效率高不高。

扫描的行数和访问类型:在explain语句中的type列反映了访问类型。访问类型有很多种,从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。从慢到快,扫描的行数从小到大。

如果查询没有找到合适的访问类型,那么解决的最好办法通常是增加一个合适的索引。

例如:

select * from sakila.film_actor where film_id = 1;

这个查询返回10行数据,从explain的结果可以看到,MySQL在索引idx_fk_film_id上使用了ref访问类型来执行查询:

详细查询日志

explain的结果也显示MySQL预估需要访问10行数据。查询优化器认为这种访问类型可以高效的完成查询。如果我们删除索引再来运行:

详细查询日志

正如我们预料的,查询变成全表扫描,MySQL预估会扫描5073条记录来完成查询。

一般MySQL使用如下三种方式应用WHERE条件,从好到坏依次是:

  • 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。

  • 使用索引覆盖扫描(Extra:using index)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的,但无需再回表查询记录。

  • 从数据表中返回数据(Extra:using where),然后过滤不满足条件的记录。这是在服务器层完成的,MySQL需要从数据表中读出来然后再进行过滤。

虽然例子说明了好的索引多么重要,但是也不是说增加了索引就能让扫描的行数等于返回的行数。例如使用聚合函数的查询:

select actor_id,count(*) from sakila.film_actor group by actor_id

这个例子没有什么索引能够让这样的查询减少需要扫描的行数。

通常如果发现查询需要扫描大量的数据但只返回少数的行,那么通常可以尝试使用下面的技巧去优化:

  • 使用覆盖索引扫描,把所有需要的列放到索引中,这样存储引擎无需回表获取对应行就可以返回结果了。

  • 改变库表结构。例如使用单独的汇总表。

  • 重写复杂的查询,让MySQL优化器能够以更优化的方式执行这个查询。

重构查询的方式非锁

一个复杂的查询还是多个简单的查询

切分查询

将大查询分为小查询,每个查询功能完全一样,只完成一小部分,每次返回一小部分查询结果。删除旧数据就是一个很好的例子

分解关联查询

对每一个表进行一次单表查询,然后将结果在应用程序中关联。

分解关联查询的优点:

  • 让缓存效率跟高。

  • 执行单个查询可以减少锁的竞争。

  • 做应用层关联,可以更好的对数据库拆分,更容易做到,高性能可扩展。

  • 查询效率本身效率也可能会有所提升。

  • 可以减少冗余记录的查询。

  • 这样做相当于在应用中实现了哈希关联而不是MySQL的嵌套循环关联。

查询执行的基础

查询执行路径:

查询执行路径

MySQL查询优化器的局限性

查询优化器的提示

优化特定类型的查询

案例学习

MySQL的高级特性

  • 分区表

  • 视图

  • 外键约束

  • MySQL内部存储代码

  • 游标

  • 绑定变量

  • 用户自定义函数

  • 插件

  • 字符集和校对

  • 全文索引

  • 分布式(XA)事务

  • 查询缓存