MySQL 初

没怎么玩过啥数据库,最近因为项目上的事情必须得了解一些相关的东西。

数据库使用由于有了 SQL 比较容易给人一种错觉,认为所有的数据库具有统一的接口,这个认识是不正确的,因为每个数据库自己的类型都不见得一样,最常见的莫过于对日期/时间的处理,相关的函数命名也不大相同。因此一个项目一般一开开始就会选定一种数据库,后期不再更换,除非中间有额外的一层抽象,避免了直接跟数据库打交道,如 jOOQ (SQL 层面的抽象)或者 hibernate (ORM 上的抽象)等,但即便有这些一般也不推荐中间更换下面的 storage。另外一点,数据库查询似乎就是想办法把需要的东西通过各种 join/subquery 弄在一起,这不假,但是要弄成逻辑正确的 query 是容易的,弄成能高效给出结果的 query 是困难的。

正因为后者的困难,数据库往往需要给出能够帮助用户发现低效 query 的瓶颈在哪里,这个工具常称为 planning explain,我们常在一些自己的 SELECT 前面加上 EXPLAIN 来获得 MySQL 对给定 query 根据一些历史统计信息获得的 planning。往往合理的数据库设计能够简化 query,同时合适的 index 将会把 query 需要的 join 尽量在内存中解决。我们要知道 index 一般分 b-tree 和 hash 两种,对于某些 spatial 数据还有 R-tree。当内存充分的时候,index 在内存中,如此定位信息是较快的,我们一般只有在必须 hit disk 的时候才去进行 IO,这样的 query 才会高效的执行。但是 index 的增多会导致 insert/update/delete 效率的下降,所以不是必要的 index 加之无用。这些工作甚至有些地方会有专人来做,就叫 database optimization。

其实数据库优化很大一部分就是要读懂 planning,MySQL 的 optimization 在其文档里面有专门的一章,关于如何读懂 explain 的结果有专门的一节。我们这里着重看 explain 那块,顺便了解一些其他的优化。

  • 表格的 structure 是否合理
  • 合适的 index 是否已經到位
  • 每个表格是否已經使用了合理的 storage engine(MySQL 本身支持 transactional 的 InnoDB 和 non-transactional 的 MyISAM 等)
  • 每个表格的 row format 是否合适(如压缩是否必要)
  • 应用程序是否使用了合理的 locking
  • cache 的大小是否合理

硬件层次上有的时候也需要优化,如对 seek/write/read 的衡量、CPU 和内存带宽的配置,比较常见的策略就是避免使用磁盘而使用高性能的 SSD 搭建数据库。有了这些我们就该优化使用的 SQL,这一般需要为 join、filtering 等操作设计好 index,index 通常小于数据本身的大小,能够放在内存中,避免某些操作进行 disk IO,为必要的地方设置 index 就能加速查询,同时避免 table scan。

MySQL 的 index(包括 primary、unique、index 和 fulltext,对 in memory 的也有 hash),多数是依赖 B-tree(和 R-tree)的。通常一个 table 会通过 primary key 和 foreign key 这种类型的声明创建对应的 index,这样通过某个条件获得的对象/record 可以继续通过 foreign key 去获得相关的对象/record,对于一些列而言,我们也可以设置对应 index(如果是 blob 需要设置 blob 的前缀长度),fulltext 是全文检索(仅有 MyISAM 的 table 支持)。另外可以为多个列设置 index,这个时候列存在的顺序就非常重要,一般前面的都是不可缺省的列,后面是在 query 中可以省略的 column。与分开的 column index 相比,这个的限制在于,如果 query 中不含有索引中第一列就不会使用这个 index;而分开的 index 需要使用 index merge 来处理,性能应该稍微差一些。index 常用 B-tree 的原因也是因为其支持 range query,可以做 prefix matching,而对 hash 类型的来说,一般只能做 exact match。

通常 explain plan 会输出

  • id 标识通过 union/subquery 连接起来的几个 select 的 id
  • select type 标识 select 的类型,如果就是一个即为 SIMPLE,根据是 union/subquery 等可以是别的值
  • table 表示输出的 table 的名字
  • partition 匹配上的 partition
  • type 表示 join 的类型(后面解释其值)
  • possible keys 备选用的 index
  • key 实际使用的 index key
  • ken len,index key 的大小
  • ref 表示 index 对应的 column
  • rows 估计的能匹配上的行数
  • filtered 被过滤掉的行数的百分比
  • extra 额外的信息(后面解释)

这些信息,对 optimization 有用的无非是 type 和 extra 里面的信息。其中 type 可以取

  • system(const 的特例)表示这个表就一行
  • const 表示最多能匹配上一行,通常这种是非常快的访问,一般对应于 query 开始的地方,用户给了一个常量,也见于 1-to-1 关系的访问(primary、unique 这种 index)
  • eq_ref 表示是一个 join,每个前面的结果只能对应于一个当前表的行(如通过 foreign key 和这个表的 primary key 发生的 join),这通常是 join 最希望看见的结果
  • ref 表示这个 join 可以是 1-to-many 的关系,这个 join 对应的一般不是 primary/unque index。
  • fulltext 使用 fulltext 的 index
  • ref_or_null 与 ref 类似,但是需要处理 NULL
  • index_merge 这时一般是通过几个 index 来处理多列的搜索
  • unique_subquery 通常是在 in 操作下对应 subquery 产生的值
  • index_subquery 与前者类似,但是一般不是 primary/unique 的,但仍然有 index
  • range 表示只有某部分内容需要被访问
  • index 按照 index 遍历全部数据
  • all 遍历全部数据

extra 里面可能因为优化不当有问题的是 using file sort 和 using temporary 的值,常见的一些值有

  • using index 表示使用了 index 不需要做 disk io
  • using where 常于 using index 一同出现表示通过 index 进行了 filter
  • using join buffer,这通常是由于某个 table 在 joining 内层被反复使用做的优化,使用了之后这个内层表格只会被使用一次,外层的结果存放在 join buffer 里面
  • using temporary 表示需要创建临时表
  • using filesort 需要进行外排序

这些 idea 不仅仅在数据库上是成立的,对 PIG、Hive 等基于 map/reduce 框架做的 storage 也是成立的,不过这些的 join 逻辑和数据库依赖 index 却又有些区别。

——————
And by thy sword shalt thou live, and shalt serve thy brother; and it shall come to pass when thou shalt have the dominion, that thou shalt break his yoke from off thy neck.

Advertisements
MySQL 初

一个有关“MySQL 初”的想法

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s