高性能mysql读书笔记

看高性能mysql这本书的一些读好笔记

服务器性能剖析

• 数据库服务器的性能用查询的响应时间来度量,单位是每个查询花费的时间
• 无法测量就无法有效地优化,应该先测量时间到底花在什么地方.
• 在资源还足够的情况下,资源利用率升高了并不代表性能就下降了
• 性能剖析的两种类型:
  • 基于执行时间的分析:基于执行时间的分析研究的是什么任务执行时间最长
  • 基于等待时间的分析:基于等待时间的分析则是判断任务在什么地方被阻塞的时间最长
• 很多时候性能问题是出在应用程序本身,而并不是出在数据库
• 性能剖析用到的相关工具
  • 使用Percona Toolkit,较高版本的mysql都内置这一套工具
    • 可以使用pt-query-digest –type=tcpdump来分析tcpdump的文件
    • pt-query-digest分析慢查询日志(把慢查询日志中的long_query_time配置成0,这样就可以记录所有的查询语句,慢查询日志是开销最低,精度最高的测量查询时间的工具)
  • 使用第三方的数据库监控工具
  • 用explain去分析查询语句
  • show profile (需要执行 SET profiling = 1;)记录所有进行语句的信息,之后可能会被performance schema替代
  • show processlist 查看mysql线程状态
  • show status (show global status)记录数据库的一些统计信息
  • performance schema
  • information schema

Schema 与数据类型优化

选择优化的数据类型

• 尽可能选择简单而且占用空间小的数据类型
• 整形,浮点型,都是固定大小的
• 尽量避免使用NULL
• DECIMAL可以存储更高精度的数据,但是占用空间更大,运算效率也要更低,实际mysql是以字符串的形式来保存该数据类型的
• TIMESTAMP 记录的时间范围要比 DATETIME 小,但是 TIMESTAMP占用空间小,与时区相关
• utf8mb4才是正确的4字节unicode,因为utf8也是变长的,所以不一定varchar和char谁的占用空间更小,utf8编码下的char一个字符占4个字节

  MySQL schema 设计中的陷阱

  太多的列,太多的关联,不适当的使用枚举

  范式和反范式

• 第一范式（1NF）强调的是列的原子性，即列不能够再分成其他几列.
• 第二范式（2NF）首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分.
• 第三范式（3NF）在1NF基础上，任何非主属性不依赖于其它非主属性[在2NF基础上消除传递依赖]。第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）.
• 混用范式化和反范式化

缓存表和汇总表

• 缓存表和汇总表,减少跨表查询,可以提高效率
• flexviews 物化视图

创建高性能的索引

索引基础

B-Tree索引

• mysql只能高效地使用索引的最左列,用以下的方式可以很好的匹配索引
  • 全值匹配(就是匹配索引中的所有列)
  • 匹配最左列
  • 匹配最左列前缀(就是用like关键字去查询)
  • 先精确匹配最左列再作范围匹配
  • 只访问索引列
  • 不能跳过索引中的列(例如一个索引包含3个列,只使用1,3列是不可以使用索引的)
  • 出现列的范围查询后,其右侧的所有列都无法使用索引
  • 因为B-Tree是有序的,所以order by,group by是可以按照以上的条件使用索引的

  • 索引不能作为表达式的一部分,也不能是函数的参数

  • 索引并不一定是最好的工具
  • 对于非常小的表大部分情况下简单的全表扫描更高效
  • 对于中到大型表,索引非常有效
  • 对于特大型表,建立和使用索引的代价将随之增大(这种情况下,可以考虑分区技术)

前缀索引和索引选择性

• 索引选择性是指不重复的索引值和数据表的记录总数的比值,范围在0-1之间,这个数值越高则查询效率越高,唯一索引的选择性是1.(例子: SELECT count(DISTINCT left(waybill_id,10))/count(*) as sel3 from tt_waybill; 这样就可以得到索引选择性的值)
• 当索引很大的时候(例如是个很长的字符串,TEXT或BlOB),可以只取前N位来构建索引,这种索引称为前缀索引,前缀索引只能是单列索引

多列索引

mysql 5.0之后引入了”索引合并”,这个策略有时候是一种优化结果,单实际上更多时候说明了表上的索引建得很糟糕

• 当服务器需要对多个索引做联合操作时(通常有多个OR),通常需要耗费大量CPU和内存资源在算法的缓存,排序和合并操作上.特使是当其中有些索引的选择性不高,需要合并扫描返回大量的数据的时候.
• 优化器不会把这些计算到”查询成本”中,优化器只关心随机页面读取,这会使得查询成本被”低估”,导致该执行计划还不如直接走全表扫描.

聚族索引

• 聚族索引最大限度地提高了I/O密集型应用的性能,但如果数据再内存中就没什么优势了
• 插入速度严重依赖插入顺序.按主键的顺序插入是加载数据到InnoDB表中速度最快的方式.如果不按照主键顺序加载数据,在加载完成后最好使用OPTIMIZE TABLE命令从新组织一下表
• 更新聚族索引列的代价很高
• 基于聚簇索引的表插入新行，或者主键被更新导致需要移动行的时候，可能面临”页分裂（page split)“的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象的要更大，因为在二给索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。
• 顺序主键也有缺点：对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是auto_increment锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，比如应用层面生成单调递增的主键ID，插表不使用auto_increment机制，或者更改innodb_autonc_lock_mode配置。

覆盖索引

• 如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为覆盖索引

  使用索引扫描来做排序

• 使用索引排序的条件如下：
  • 索引的列顺序和Order By子句的顺序完全一致
  • 索引中所有列的方向(升序，降序)和Order By子句完全一致
  • 如果查询需要关联多张表,则只有Order By子句引用的字段全部为第一个表时,才能使用索引做排序
  • 在WHERE子句的索引的第一列被指定一个常量也可以被认为Order By子句匹配的最左前缀列
• 扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录，但如果索引不能覆盖查询所需的全部列，那就不得不扫描一条索引记录就回表查询一次对应的整行，这基本上都是随机IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在IO密集型的工作负载时。

优化数据访问

• 一个常见的错误是常常会误以为Mysql会只返回需要的数据,实际上Mysql却是先返回全部结果集再进行筛选,计算.
• 认真思考你是不是一定要用select *(最好是明确写出所有列) ,取出全部列会让优化器无法完成索引覆盖扫描这类优化,还会对服务器带来额外的I/O,内存和CPU的消耗,但这不是绝对的(例如取出全部列可以简化程序的开发,重复访问数据库等).

是否扫描额外的记录

最简单衡量Mysql查询开销的三个指标分别是:响应时间,扫描行数,返回行数

• 响应时间
  • 响应时间是服务时间和排队时间之和
    • 服务时间是指数据库处理这个时间真正花了多少时间
    • 排队时间是指服务器因为等待资源而没有真正执行查询所花的时间
  • 因为查询语句复杂的执行过程,我们很难做到把响应时间细分到查询过程中的每个部分
• 扫描行数和返回行数
  • 扫描行数越接近返回行数查询效率越高

优化关联查询

• 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。 当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。

• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL 才有可能使用索引来优化这个过程。

• 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化 的地方。因为以前是普通关联的地方可能会变成笛卡儿积，不同类型的关联可能会生成不同的结果

• 优化GROUP BY 和 DISTINCT
  • 当无法使用索引的时候,GROUP BY使用两种策略来完成:使用临时表或者文件排序来做分组
  • 如果需要对关联查询做GROUP BY,并且是按照查找表中的某个列进行分组,那么通常采用查找表的标识列分组的效率会比其他列更高.
  • 如果没有通过ORDER BY子句显式地指定排序,当查询使用GROUP BY子句的时候,结果集会自动按照分组的字段进行排序.如果不关心结果集的顺序,而这种默认排序又导致了需要文件排序,则可以使用ORDER BY NULL,让Mysql不在进行文件排序.也可以在GROUP BY子句中直接使用DESC或ASC使分组的结果按需要的方向排序
• 优化LIMIT分页
  • LIMIT在偏移量非常大的时候是需要查询出所有的数据然后只返回目标数据,例如LIMIT 1000,20 则会查询1020条数据而只返回最后20条
  • OFFSET会导致Mysql扫描大量不需要的然后再抛弃掉

Mysql高级特性

分区表

• 分区的表达式值出现NULL会使分区过滤无效,从而导致每次都会扫描存在NULL的分区,所以最好创建一个分区只存放NULL相关的记录,那样即使每次都扫描,代价也会非常小,MySQL5.5之后直接用COLUMS关键字来分区就不用担心这个问题
• 如果定义的分区列和索引列不匹配,会导致查询无法进行分区过滤(即会扫描每一个分区的索引),要避免这个问题,应该避免建立和分区列不匹配的索引,或查询的时候同时包含了可以过滤分区的条件
• 所有分区都必须使用相同的存储引擎
• 一个很重要的原则:即便在创建分区时可以使用表达式,但在查询时却只能根据列来过滤分区
• 当查询访问分区表的时候,MySQL需要打开并锁住所有底层表.虽然每个操作都会”先打开并锁住所有底层表”,但并不是说分区表在处理过程中是锁住全表的.如果储存引擎能够自己实现行级锁,如InnoDB,则会在分区层释放对应的表锁

视图

• MySQL可以使用两种办法来处理视图,这两种方法分别称为合并算法(MERGE)和临时表算法(TEMPTABLE),如果可能,会尽可能使用合并算法
  • 
• 如果视图中包含GROUP BY,DISTINCT,任何聚合函数,UNION,子查询等,只要无法在原表记录和视图记录中建立一一映射的场景中,MySQL都将使用临时表算法来实现视图.
• EXPLAIN一条针对视图的简单查询如果select_type为”DERIVED”,说明该视图是使用临时表算法实现的.
• 只有在视图上执行查询的时候,才会真正去执行创建视图的SQL.如使用临时表算法创建的视图,只有基于该视图执行查询的时候,视图才会生成一个临时表
• 所有使用临时表算法实现的视图都无法被更新
• MySQL不支持物化视图,MySQL并不会保存视图定义的原始SQL语句(无法通过SHOW CREATE VIEW获取视图定义的原始SQL),但可以通过.frm文件的最后一行获得一些信息,如果有FILE权限,可以通过LOAD_FILE()来读取.frm来获取视图定义的原始SQL
  • 
• 视图还引入一些并非MySQL特有的其他问题。很多开发者以为视图很简单，但实际背后的逻辑可能非常复杂。开发人员如果没有意识到视图背后的复杂性，很可能会以为是在不断地重复查询同一张简单的表，而没有意识到实际上是代价高昂的视图。例如，一条看起来很简单的查询，EXPLAIN出来缺有可能有几百行，因为其中一个或者多个表，实际上是引用了很多其他表的视图。而且视图的性能很难预测，即使是使用合并算法的视图也会有额外的开销
• Mysql对视图的的支持并不够成熟，代码测试不够全面，可能会有一些隐藏的BUG。

外键约束

• 使用外键是有成本的
  • 外键通常要求每次在修改数据时要再另外一张表中多执行一次查找操作
  • 外键维护操作是逐行进行的,所以这样的更新会比批量删除和更新要慢一些
  • 外键约束使得查询需要额外访问一些别的表,这也意味着需要额外的锁,这会导致额外的一些锁等待,甚至会造成死锁,因为没有直接访问这些表,所以这类死锁问题往往难以排查

在MySQL内部储存代码

• 存储过程和函数
  • 大量的小查询,使用存储过程可以减少网络开销,提高执行速度
• 触发器
  • 对于每个表的每个时间最多只能定义一个触发器
  • 触发器始终是针对一条记录的,而不是针对整个SQL语句的.如果变更的数据集非常大的话,效率会很低
  • 一个简单的SQL语句背后,因为触发器,可能包含了很多看不见的工作
  • 触发器的问题很难排查
  • 触发器有可能导致死锁和锁等待

游标

• MySQL的游标是单向,只读的
• 游标可以嵌套
• MySQL的游标中指向的是存储在临时表中的对象而不是实际查到的数据
• 当你打开游标的时候需要执行整个查询,并为这个查询创建临时表
• MySQL不支持客户端的游标

全文索引

### 其他

MySql无法利用多核特性来并行查询,很多其他的关系型数据库能够提供这个特性,但是MySql做不到.

“快速,精确和实现简单”,三者永远只能满足其二,必须舍弃其中一个