《高性能MySQL》观后感

  今天(是16号,刚过12点),终于把这本书啃完了。去年立的flag,不知不觉就被我放弃了。感觉总结总结,还是要自己来写,而不是把书上的东西,原原本本的抄下来(最主要是懒^_^)。
  去年年底,被迫换工作。正好又赶上18年的裁员大潮,听说跟6月份的P2P平台接连爆雷有关,也不知道是真是假。再加上,前后玩了一个月,虽然没有面试几家公司,但仍感觉处处碰壁,无奈之下,进了一家被我拒绝过的公司。
  看这本书的初衷,也是感觉自己对MySQL的认知,还有所欠缺。特别是在面试过程中,最能体会。当被问到诸如,“事务的隔离级别”、“覆盖索引”、“聚簇索引”时,哑口无言,只能如实的回答不知道。当被问到,MySQL查询优化时,也只能应付式的回答,百度上搜索的结果。
  这本书,从各个角度作为切入点,详细的阐述了如何优化MySQL,让它可以满足你的需求。在不同场景下,分析MyISAM和InnoDB引擎的区别。并且,会一直提醒你,本书的部分内容可能会过时,需要关注最新的信息,而不是完全只信任这本书,还提醒你去官网,或者各种渠道了解MySQL。
  让我印象最深刻的是,我一直纠结于,使用where…in筛选时,in的内容,在应用层用程序代码去排序,是否会影响BTree的扫描策略,从而提高速度?恰巧就在书中,找到了答案(还好我没跳着看)。在书的213页中,关于in的描述,有这么一句话:“MySQL将IN()列表中的数据先进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件”。我当时没做记录,刚刚,回头翻了好几分钟,才找到 (T_T)。
  不过,我还是觉得,这本书有点美中不足。书上的部分内容,讲的不够深入,可能是想让我自己去寻找吧。即使如此,我仍然会安利这本书 ,给身边的朋友看看。

读书笔记《高性能MySQL》第三章 服务器性能剖析

  问10个人关于性能的问题,可能会得到10个不同的回答,比如“每秒查询次数”、“CPU利用率”、“可扩展性”之类的。这其实都没有问题,每个人在不同场景下对性能都有不同的理解。本书的作者将性能定义为:完成某件任务所需的时间度量,换句话说,性能即是响应时间。
  很多人对什么是优化很迷茫,假如你认为性能优化是降低CPU利用率,这样就可以减少对资源的使用。但这是一个陷阱,资源是用来消耗并工作的,所以,有时候如果消耗更多的资源,就能够加快查询的速度,也是可取的。

性能剖析一般有两个步骤:
  1、测量任务所花费的时间;
  2、对结果进行统计和排序,将重要的任务排到前面。

尽管性能剖析输出了排名、总计和平均值,但还是有很多需要的信息是缺失的,如下所示:
  1、值得优化的查询:性能剖析不会自动给出哪些查询值得花时间去优化。一些占总响应时间比重较小的查询,是不值得优化的,根据阿姆达尔定律,对一个占总响应时间不超过5%的查询进行优化,无论如何努力,收益也不会超过5%。如果优化的成本大于收益,就应当停止优化。
  2、异常情况:某些任务,即使没有出现在性能剖析输出的前面,也需要优化。比如,某些任务执行的次数很少,但每次执行都非常慢,严重影响用户体验。因为其执行频率低,所以总的响应时间占比并不突出。
  3、未知的未知:一款好的性能剖析工具会显示可能的“丢失时的间”。丢失的时间指的是,任务的总时间和实际测量到的时间之间的差。例如,如果处理器的CPU时间是10秒,而剖析到的任务总时间是9.7秒,那么就有300毫秒的丢失时间。这可能是有些任务没有测量到,也可能是由于测量的误差和精度问题的缘故。如果工具发现了这类问题,则要引起重视,因为有可能错过了某些重要的事情。即使性能剖析没有发现丢失时间,也需要注意考虑这类问题存在的可能性,这样才不会错过重要的信息。
  4、被掩藏的细节:性能剖析无法显示所有响应时间的分布。只相信平均值是非常危险的,它会隐藏很多信息,而且无法表达全部情况。Peter经常举例说医院所有病人的平均体温没有任何价值。

性能瓶颈可能有很多影响因素:
  1、外部资源,比如调用了外部的web服务或搜索引擎;
  2、应用需要处理大量的数据,比如分析一个超大的XML文件;
  3、在循环中执行昂贵的操作,比如滥用正则表达式;
  4、使用了低效的算法,比如使用暴力搜索算法,来查找列表中的项;

测量工具:New Pelic、xhprof、xdebug、Valgrind、cachegrind、Enterprise Monitor( 它是Oracle提供的MySQL商业服务支持中的一部分 )

剖析MySQL查询:
  1、慢查询日志,可以通过设置long_query_time为0来捕获所有的查询。它是开销最低、精度最高的测量查询时间的工具,但它并不是万能的。例如,当数据库负载已经过高时,即使原本执行速度非常快的查询,也有可能会变的很慢;
  2、抓取TCP网络包,可以先通过tcpdump将网络包数据保存到磁盘,然后使用pt-query-digest的–type=tcpdump选项来解析并分析查询。此方法精度比较高,并且可以捕获所有查询。还可以解析更高级的协议特性,比如可以解析二进制协议,从而创建并执行服务端预解析的语句及压缩协议;
  3、使用show profile,它是在MySQL5.1以后的版本引入的,来源于开源社区的Jeremy Cole的贡献。此命令非常强大,可以分析查询语句具体慢在哪里。例如:是创建临时表慢、还是执行排序慢;
  4、使用show status,这个命令会返回一些计数器。既有服务器级别的全局计数器,也有基于某个连接的会话级别的计数器。它可以分析出,查询语句创建了多少张临时表,是磁盘临时表,还是内存临时表,几条结果用到索引的读操作,几条结果没有用到索引的读操作,也非常强大;
  5、使用explain,它能分析语句的执行计划。可以判断语句是否有用到索引,用了哪些索引,索引的长度,所需扫描的记录数,等等;
  6、使用show processlist,可以通过该命令,来观察是否有大量线程处于不正常的状态,或有其它不正常的特征。例如,查询很少会长时间处于statistics状态;

  MySQL的性能剖析,除了对单条查询语句本身,需要做详细的分析以外,有的时候还需要对整个应用程序,甚至整个MySQL服务或服务器做分析。就像作者说的,没有什么是放之四海而皆准。找到确定的问题点,然后使用正确而有效的方法,就能做到以不变而应万变。

读书笔记《高性能MySQL》第二章 MySQL基准测试

  这一章,将会是最水的一章。并不是说书写的不好,而是我根本看不下去。这一章大篇幅的讲解了,为什么要进行基准测试,基准测试的策略、方法和指标,以及如何通过测试结果,评估系统的性能。最后,还介绍了一些测试工具、测试案例。

测试的指标:
  1、吞吐量:单位时间内事务处理数。
  2、响应时间或者延迟:任务所需的整体时间。
  3、并发性:在任意时间有多少同时发生的并发请求。
  4、可扩展性:给系统增加一倍的工作,在理想情况下就能获得两倍的结果。

基准测试有两种主要的策略:
  1、集成式 ( full-stack ):针对整个系统的整体测试。
  2、单组件式 ( single-component ):单独测试MySQL。

集成式测试工具:
  1、ab:它是一个Apache HTTP服务器基准测试工具,它可以测试HTTP服务器每秒最多可以处理多少请求。这是个非常简单的工具,只能针对单个URL进行尽可能快的压力测试。
  2、http_load:它和ab类似,但比ab更加灵活。可以通过一个输入文件提供多个URL,http_load在这些URL中随机选择进行测试。也可以定制http_load,使其按照时间比率进行测试。
  3、JMeter:它是一个Java应用程序,可以加载其他应用并测试性能。JMeter比ab和http_load都要复杂的多。例如,它可以通过控制预热时间等参数,更加灵活地模拟真实用户的访问。JMeter拥有绘图接口,还可以对测试进行记录,然后离线重演测试结果。

单组件式测试工具:
  1、mysqlslap:可以模拟服务器的负载,并输出计时信息。它包含在MySQL5.1的发行包中,应该在MySQL4.1或者更高的版本中都可以使用。测试时可以执行并发连接数,并指定SQL语句。如果没有指定SQL语句,mysqlslap会自动生成查询schema的SELECT语句。
  2、MySQL Benchmark Suite:它是单线程的,主要用于测试服务器执行查询的速度,结果会显示哪种类型的操作在服务器上执行得更快。
  3、Super Smack:是一款用于MySQL和PostgreSQL的基准测试工具,可以提供压力测试和负载生成。这是一个复杂而强大的工具,可以模拟多用户访问,可以加载测试数据到数据库,并支持使用随机数据填充测试表。
  4、Database Test Suite:是由开源软件开发实验室设计的,发布在SourceForge网站上,这是一款类似某些工业标准测试的测试工具集。
  5、Percona’s TPCC-MySQL Tool:它是由本书作者开发的,一个类似TPC-C的基准测试工具集,其中有部分是专门为MySQL测试开发的,该工具的源代码可以在 https://launchpad.net/perconatools 下载。
  6、sysbench:是一款多线程系统压测工具。它可以根据影响数据库服务器性能的各种因素来评估系统的性能。例如,可以用来测试文件I/O、操作系统调度器、内存分配和传输速度、POSIX线程,以及数据库服务器等。sysbench支持Lua脚本语言,Lua对于各种测试场景的设置可以非常灵活。sysbench是一个全能测试工具,支持MySQL、操作系统和硬件的硬件测试。

  MySQL还有一个内置的函数:BENCHMARK(),可以测试某些特定操作的执行速度,参数可以是需要执行的次数和表达式。该函数可以很方便地测试某些特定操作的性能,比如通过测试可以发现,MD5()函数比SHA1()函数要快:

SET @input := 'hello world';
SELECT BENCHMARK(1000000, MD5(@input));
SELECT BENCHMARK(1000000, SHA1(@input));

读书笔记《高性能MySQL》第一章 MySQL架构与历史

今年3月份,早就买了这本《高性能MySQL》,一直还没有去看。前后一起买的,还有一本《MySQL必知必会》,也早已看完。后来,去看了一些其他书籍。像是什么《Redis实战》、《数据结构C语言版》、《Linux命令行与shell脚本编程大全》。不过,我看书一般都是有选择性的看。看能看懂的,看迫切需要掌握的,毕竟贪多嚼不烂。我看书的习惯是:先看目录,看看这本书大致有什么内容,然后,再看看前言,看看作者想告诉我们些什么。这本书总共分为16章,6个附录。我计划每看完4章,就开始做个笔记。非常推荐大家,去购买这本书来看。

MySQL采用三层架构:

第1层
负责连接处理、授权认证、安全等。
第2层
负责查询解析、分析、优化、缓存、内置函数( 例如,日期、时间、数学和加密函数 )、存储过程、触发器、视图等。
第3层
包含了存储引擎,负责数据的存储与提取,服务器通过API与存储引擎进行通信。除了InnoDB会解析外键定义,其它存储引擎一般不会解析SQL,不同存储引擎之间也不会相互通信。


MSQL5.5加入了企业线程池插件( https://dev.mysql.com/doc/refman/5.5/en/thread-pool.html )

MySQL会解析查询,并创建内部数据结构( 解析树 ),包括重写查询、决定表的读取顺序,以及选择合适的索引等。

对于SELECT语句,在解析查询之前,服务器会先检查查询缓存,如果能够在其中找到对应的查询,服务器就不必再执行查询解析、优化和执行的整个操作,而是直接返回查询缓存中的结果集。

并发控制可以采用共享锁、排他锁来解决问题。

锁粒度包含了表锁、行锁( MyISAM不支持行锁,InnodDB支持行锁 )。

事务的ACID概念:原子性( atomicity )、一致性( consistency )、隔离性( isolation )、持久性( durability )。

事务的隔离级别:未提交读( READ UNCOMMITTED )、提交读( READ COMMITTED )、可重复读( REPEATABLE READ )、可串化( SERIALIZABLE ),InnoDB事务的默认隔离级别是可重复读。

隔离级别 脏读 不可重复读 幻读 加锁读
未提交读 Yes Yes Yes No
提交读 No Yes Yes No
可重复读 No No Yes No
可串行化 No No No Yes

MySQL默认采用自动提交模式。每个查询都会被当作一个事务执行,并自动提交。

InnoDB支持通过特定的语句进行加锁,这些语句不属于SQL规范,经常被滥用,实际上应当尽量避免使用,如下:
SELECT … LOCK IN SHARE MODE ( 共享锁 )
SELECT … FOR UPDATE ( 排他锁 )

InnoDB采用MVCC多版本并发控制,MVCC的实现,是通过保存数据在某个时间点的快照来实现的,并保存系统版本号、行记录的版本号。每开始一个新事务,系统版本号都会自动递增。事务开始时刻的系统版本号,会作为事务的版本号,用来和查询的每行记录版本号进行比较。
SELECT时,只查询行的系统版本号,小于或等于事务的系统版本号。行的删除版本,要么未定义,要么大于当前事务版本号。
INSERT时,为插入的每一行,保存当前系统版本号,作为行版本号。
DELETE时,为删除的每一行,保存当前系统版本号,作为行版本号。
UPDATE时,为插入一行新记录,保存当前系统版本号,作为行版本号。同时保存当前系统版本号,到原来的行,作为行删除标识。
MVCC只在“提交读”、“可重复读”两个隔离级别下工作,其他两个隔离级别都和MVCC不兼容。

在MySQL5.1以及之前的版本中,MyISAM是默认的存储引擎。从MySQL5.5.5开始,新表的默认存储引擎改为InnoDB,并引入了InnoDB Plugin的特性。InnoDB采用MVCC来支持高并发,并且实现了4个标准的隔离级别。其默认级别是可重复读,并通过间隙锁策略,防止幻读的出现。InnoDB表是基于聚簇索引建立的。
MyISAM提供大量特性,包括全文索引、压缩、空间函数( GIS )等,但它不支持事务和行级锁,崩溃后无法安全恢复。

内建的其他存储引擎:
1、Archive引擎,只支持INSERT和SELECT,MySQL5.1之前也不支持索引。
2、CSV引擎,能直接读取csv文件,可以作为一种数据交换的机制。
3、Memory引擎,比MyISAM还要快一个数量级,适合需要快速访问,并且不需要修改的数据。重启后数据会丢失,通常作为临时表。因为所有的数据都保存在内存中,所以不需要进行磁盘I/O。
4、NDB集群引擎,MySQL服务器、NDB集群存储引擎,以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合,被称为MySQL集群。

乐观锁与悲观锁的理解

乐观锁

流程:修改记录前,首先获取记录对应的版本号,然后,在修改语句的where条件上验证版本号。
举例:
— 查询商品
SELECT goods_id, version FROM tb_goods WHERE goods_id={$goods_id};
— 写入订单商品
INSERT INTO tb_order_goods (order_id, goods_id, …) VALUES ({$order_id}, {$goods_id}, …);
— 修改商品库存
UPDATE tb_goods SET inventory_number=inventory_number-1 WHERE goods_id={$goods_id} AND version={$version};
如果修改库存不成功,说明存在冲突,需要采用事务回滚的方式。这里只是举例,实际应用中,我觉得应该是先减库存,成功后,再写订单之类的信息。

悲观锁

流程:修改记录前,先上锁,这样别的事务就无法同时并行。
举例:
— 查询商品
SELECT goods_id FROM tb_goods WHERE goods_id={$goods_id} FOR UPDATE;
— 写入订单商品
INSERT INTO tb_order_goods (order_id, goods_id, …) VALUES ({$order_id}, {$goods_id}, …);
— 修改商品库存
UPDATE tb_goods SET inventory_number=inventory_number-1 WHERE goods_id={$goods_id};

获取/转换/格式化:日期/时间/时间戳

获取

–返回当前日期时间 2018-05-23 01:04:08
select now();
select current_timestamp();
select localtime();
select localtimestamp();

–返回当前日期 2018-05-23
select curdate();
select current_date();

–返回当前时间 01:04:08
select curtime();
select current_time();

–返回当前unix时间戳 1527008648
select unix_timestamp();

–返回昨天 2018-05-22 01:04:08
select date_sub(‘2018-05-23 01:04:08’,interval 1 day);

–返回明天 2018-05-24 01:04:08
select date_sub(‘2018-05-23 01:04:08’,interval -1 day);

–返回30分钟前 2018-05-23 00:34:08
select date_sub(‘2018-05-23 01:04:08’,interval 30 minute);

–返回30分钟后 2018-05-23 01:34:08
select date_sub(‘2018-05-23 01:04:08’,interval -30 minute);

转换

–日期时间转为时间戳 1527008648
select unix_timestamp(‘2018-05-23 01:04:08’);

–时间戳转为日期时间 2018-05-23 01:04:08
select from_unixtime(1527008648);
select from_unixtime(1527008648, ‘%Y-%m-%d %H:%i:%s’);

–时间戳转为日期 2018-05-23
select from_unixtime(1527008648, ‘%Y-%m-%d’);

–时间戳转为时间 01:04:08
select from_unixtime(1527008648, ‘%H:%i:%s’);

格式化

–格式化日期 2018-05-23 01:04:08
select date_format(‘2018/05/23 01/04/08’, ‘%Y-%m-%d %H:%i:%s’);
select str_to_date(’05/23/2018 08:01:04′,’%m/%d/%Y %s:%H:%i’)

共享锁与排他锁

  • 概述

    MySQL锁的机制分别有:共享锁和排他锁。又可分为行锁和表锁,顾名思义作用在行上的锁,或者整个表上的锁(MyISAM只支持表锁)。

  • 使用方法

    共享锁:select * from table_name lock in share mode;
    排他锁:select * from table_name for update;

  • 对比

    共享锁:某个事务上了共享锁,其它事务就只能上共享锁
    排他锁:某个事务上了排他锁,其它事务就不能上任何锁

  • 注意事项

    一般情况下insert/update/delete会自动上排他锁
    如果,某个事务上了锁,其他事务在不上锁的情况下,是可以查询的;
    如果,事务中的第一条语句有共享锁,第二条语句是insert/update/delete的话,事务会变成排他锁

InnoDB和MyISAM的区别

  • 概述

      InnoDB是MySQL的默认事件型引擎,是使用最广泛的存储引擎。它被设计来处理大量的短期事务。除非有特别的原因需要使用其他存储引擎,否则应该优先考虑InnoDB引擎。
      MyISAM在MySQL5.1及之前的版本,是默认存储引擎。它提供了大量的特性:全文索引、压缩、空间函数(GIS)等,但不支持事务和行级锁。

  • 对比

    InnoDB:
    支持事务
    支持外键
    支持行锁
    支持全文索引(5.6加入,之前版本不支持)
    不保存表的行数,扫描表来计算多少行
    DELETE 表时,是一行一行的删除
    InnoDB 把数据和索引存放在表空间里面
    跨平台可直接拷贝使用
    InnoDB中必须包含AUTO_INCREMENT类型字段的索引
    表很难被压缩

    MyISAM:
    不支持事务,回滚将造成不完全回滚,不具有原子性
    不支持外键
    不支持行锁
    支持全文索引
    保存表的行数,不带where时,直接返回行数
    DELETE 表时,先drop表,再重建表
    MyISAM 表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex)引伸
    跨平台很难直接拷贝
    MyISAM中可以使AUTO_INCREMENT类型字段建立联合索引
    表可以被压缩