注:本文图来自高性能MYSQL 第三版.
1.Mysql服务器架构
Mysql服务器的逻辑架构图如下:
第一层:连接处理、授权认证和安全等;
第二层:mysql大多数核心功能都在这一层,如:查询解析、分析、优化、缓存以及所有的内置函数(如:日期、时间、数学和加密函数),所有跨存储引擎的功能都在这一层实现:存储过程、触发器、视图等。
第三层:包含了存储引擎。存储引擎负责Mysql中数据的存储和提取,每个存储引擎都有自己的优势和劣势,服务器通过API和存储引擎进行通信。 常见的存储引擎有: InnoDB、MyISM、Memory。
- InnoDB引擎:它是Mysql默认的事务型引擎,除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑使用InnoDB引擎。InnoDB将每个表中的数据和索引存放在一个单独的文件当中。优点:1)采用了多版本并发控制(MVCC)来支持高并发、实现了四个标准的隔离级别,默认repeatable read(可重复读),且通过间隙锁(next-key locking)策略防止幻读的出现; 2)InnoDB表基予聚簇索引建立,聚簇索引对主键查询有很高的性能,但它的二级索引包含了主键,如果主键列很大的话,其他的索引都会很大;3)InnoDB的存储格式是平台独立的,可以将数据和索引文件从Intel平台迁移到其他平台上;4)InnoDB内部做了很多的优化,包括从磁盘读取数据时采用的可预测性预读,能够自动在内存中创建hash索引以加速读操作的自适应哈希索引,以及能够加速插入操作的插入缓冲区(Insert buffer)。
- MyISAM引擎:在Mysql 5.1版本之前,它是msyql默认的存储引擎。它将表存储在两个文件当中:数据文件(.MYD)和索引文件(.MYI); 提供了大量的特性:全文索引、压缩、空间函数;但是不支持事务和行级锁,崩溃后无法恢复。它最典型性能的问题是表锁问题,对整张表加锁,读取数据对表加共享锁,写入数据对表加排它锁。
- Memory引擎:特性:1)数据存储在内存中,且这些数据不被修改,不需要IO操作,所以可快速访问数据,但也因此重启后数据会丢失,但Memory表结构还存在。2)支持Hash索引,查找非常快,但由于Memory表是表级锁,并发插入性能较低。3)不支持BLOB或TEXT类型的列,并且每行的长度是固定的,导致部分内存浪费;4)mysql执行查询过程中需要使用的临时表来保存中间结果,内部使用的是临时表就是Memory表。如果中间结果超出了Memory表的限制,或者含有BLOB或TEXT类型字段,则临时表会转化为MyISAM表。
使用场景:
- 用于查找或者映射表,如将邮编和州名映射的表;
- 用于缓存周期性聚合数据的结果;
- 用于保存数据分析中产生的中间数据;
2.索引
含义:索引是帮助MySQL的存储引擎高效地获取数据排好序的数据结构。它存储在文件当中,一般使用B-Tree来存储。在Mysql中索引是在存储引擎层而不是在服务器层实现的。所以,并没有统一的索引标准:不同的存储引擎的索引工作的方式并不一样,也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引,其底层实现也可能不同。
索引结构也可以使用:二叉树、红黑树、HASH、BTREE来存储,但一般只用HASH和BTREE。二叉树存在问题在于:当数据有序的时候,导致查找的性能退化为O(n);红黑树的问题在于每个父节点只能有两个子节点,那么当数据量大的时候,树的深度较大,查找的性能不如BTREE。
2.1 B-Tree索引
存储引擎以不同的方式使用B-Tree索引,性能各异。MyISAM使用前缀压缩技术使得索引更小,但InnoDB按照原数据格式进行存储。
B-Tree结构:
- 度(Degree)-节点的数据存储个数;叶节点具有相同的深度;
- 叶节点指针为空;
- 节点中的数据key从左到右递增排列;
B+Tree结构:(B-Tree变种)
- 非叶子节点不存储data。通常来说,一个节点的大小等于内存中一页大小,如4K,为了在一个节点中存放更多的key,降低树的深度增大度,提高查找性能,所以节点中并没有存储数据。
- 叶子结点不存储指针;
- 顺序访问指针,提高区间访问的性能;
B+Tree索引的性能分析:
- 一般使用磁盘I/O次数评价索引结构的优劣;
- 预读,磁盘一般会顺序向后读取一定长度数据(页的整数倍)放入内存;
- 程序局部性原理:当一个数据被用到时,其附近的数据也通常会马上被使用;
- B+Tree节点的大小被设置为一个页,每次新建节点直接申请一个页的空间,这样就可以保证一个节点物理上也存储在一个页里,就实现了一个节点的载入只需要一次I/O;
- B+Tree的度d一般会超过100,所以h非常小(一般为3-5之间)。
可以使用B-Tree索引查询类型,适用于:全键值、键值范围或键前缀查找,其中键前缀查找只适用于根据最左前缀的查找。它对如下类型的查找有效:
- 全值匹配:指和索引中的所有列匹配;
- 匹配最左前缀;
- 匹配列前缀:可以匹配某一列值的开头部分;
- 匹配范围值;
- 精确匹配某一列并范围匹配另一列;
- 只访问索引的查询(覆盖索引);
B-Tree限制:
- 如果不是按照索引的的最左列开始查找,则无法使用索引;
- 不能跳过索引中的列;
- 如果查询中有某个列的范围查找,则其右边所有的列都无法使用索引优化查找;
这些限制并不是B-Tree本身导致,而是Mysql优化器和存储引擎使用索引的方式导致的,这一部分限制可能在未来版本就不是限制了。
InnoDB索引实现了B+Tree索引:
- 数据文件本身就是索引文件;
- 表数据文件本身就是按照B+Tree组织的一个索引文件;
- 聚簇索引----叶子结点包含了完整的数据记录;
- InnoDB表必须要有主键,并且推荐使用整型的自增型主键;(MyISAM可以没有)如果没有显示指定,则Mysql系统会隐式生成一个可以唯一标识数据记录的,字段长度为6个字节的,类型为长整型的主键;
- 索引如果是字符串,则使用ASCII码作为比较准则;
- InnoDB的辅助索引(二级索引)data域存储的是相应记录主键值而不是地址,便于维护与数据的一致性;
- 为什么不建议使用过长的字段作为主键索引:因为所有的辅助索引都引用了主键索引,过长的主键索引会令辅助索引变得过大;
- 建议使用单调的字段作为主键索引,这是因为,随机的主键索引在插入新纪录时会引起节点的分裂和合并,甚至如果目标也已经刷回磁盘,还得从磁盘上加载到内存中,效率十分低效。
2.2 哈希索引
哈希索引基于哈希表实现。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码。哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。在Mysql中只有Memory引擎显示指定哈希索引,也是Memory引擎默认的索引类型,但Memory同时也支持BTree索引。如果多个列的哈希值相同,那么索引列会以链表的方式存放多个记录指针到同一个哈希条目中。
限制:
- 哈希索引只包含哈希值和指针,不包含数据;
- 哈希索引不是按照索引值排序存储的,无法用于排序;
- 不支持部分索引列匹配查找,因为哈希索引是使用索引列计算哈希值;
- 只支持等值比较,包括:=、IN()、ó;也不支持任何范围查询;
- 当有哈希冲突的时候,要遍历链表中所有的指针行,直到找到符合的记录;
- 哈希冲突很多的话,一些索引的维护操作代价也会很高;如果在某一选择性很低的列上建立哈希索引,那么当删除某一行时,存储引擎需要遍历对应哈希值的链表中的每一行,直到找到符合的记录,才能将其删掉;
虽然哈希索引非常快,但正时因为这些限制,导致哈希索引只适合用于某些特定的场合。
2.3 索引优点
索引可以让服务器快速定位到表的指定位置。根据创建索引的结构的不同,索引也有其他附加功能,如BTREE索引,有排序功能。
常见的Btree索引,数据有序,所以mysql可以用来做order by和group by操作。总结下来索引与如下三个优点:
- 索引大大减少服务器需要扫描的数据量;
- 索引可以帮助服务器避免排序和临时表;
- 索引可以将随机IO变为顺序IO。