高性能MySQL笔记（上） - 版本历史

Admin：/* Persona 的TCPP-MySQL测试工具(2.5.5) */

2019-11-26T01:57:19Z

‎Persona 的TCPP-MySQL测试工具(2.5.5)

Admin：/* 分区表(7.1) */

2018-01-18T03:23:38Z

‎分区表(7.1)

Admin：/* 第九章操作系统和硬件优化 */

2017-12-07T04:06:18Z

‎第九章操作系统和硬件优化

Admin：/* 第八章优化服务器配置 */

2017-12-07T04:03:16Z

‎第八章优化服务器配置

显示更改

Admin：/* 7.11分布式（XA）事务 */

2017-12-07T04:00:46Z

‎7.11分布式（XA）事务

Admin：/* 7.10全文索引 */

2017-12-07T04:00:35Z

‎7.10全文索引

Admin：/* 第七章 MySQL高级特性 */

2017-12-07T04:00:16Z

‎第七章 MySQL高级特性

Admin：/* 7.4在MySQL内部存储代码 */

2017-12-07T03:58:45Z

‎7.4在MySQL内部存储代码

Admin：/* 7.3外键约束 */

2017-12-07T03:58:12Z

‎7.3外键约束

Admin：/* 7.2视图 */

2017-12-07T03:57:47Z

‎7.2视图

@@ 第181行： / 第181行： @@
-查看数据库统计情况（自己总结的语句）
+===数据库行数统计（自己总结的语句）===
 <source lang=sql>
 select TABLE_NAME, TABLE_ROWS,data_length/1024/1024 AS DATA_MB,index_length/1024/1024 as INDEX_MB ,(data_length+index_length)/1024/1024 as TOTAL_MB,TABLE_COLLATION   from information_schema.tables where table_schema='tpcc1000' order by TABLE_ROWS desc;

@@ 第979行： / 第979行： @@
 *如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
 *分区表中无法使用外键约束
 ===查询优化===
 使用EXPLAIN PARTITIONS 可以观察优化器是否使用了分区：

@@ 第1,632行： / 第1,632行： @@
 =第九章操作系统和硬件优化=
-==9.1什么限制了MySQL的性能==
+==什么限制了MySQL的性能==
 最常见的两个瓶颈是CPU和I/O<br />
-==9.2如何选择CPU==
+==如何选择CPU==
 快速CPU还是多CPU，有两个目标：
 ;低延时（快速响应）:要做到这一点就需要高速CPU
@@ 第1,640行： / 第1,640行： @@
 MySQL复制，也能在高速CPU下工作的非常好，而多CPU对复制的帮助不大。而备库的瓶颈通常是I/O子系统而不是CPU（因为在备库上已经被简化成了串行化任务）
-==9.3平衡内存和磁盘资源==
+==平衡内存和磁盘资源==
 ;顺序I/O比随机I/O快:内存随机访问比磁盘随机访问快了2500倍，但顺序读只比磁盘快了10倍左右
 :假设每次读取100字节：
@@ 第1,648行： / 第1,648行： @@
 ;缓存命中率:缓存命中率实际上也会决定使用了多少CPU，所以评估缓存命中率的最好方法是看CPU的使用率。
 :例如，若CPU使用了99%的时间工作，用了1%时间等待I/O，那缓存命中率还是不错的。（REF: 7.12节论述了查询缓存，不要和这里的概念混了）
-==9.4固态存储==
+==固态存储==
 闪存最重要的两个特性：1、提升随机I/O；2、提高并发操作<br />
 '''写放大'''及'''垃圾收集''' 对性能影响较大（尤其是空间快满了的时候）<br />
-===9.4.2闪存技术===
+===闪存技术===
 有两种闪存类型：
 #单层单元（SLC）：每个单元存储一个比特（0或1）。SLC相对更昂贵，但非常快，并且擦写寿命高达100000个写周期（20年左右）缺点是存储密度相对较低
 #多层单元（MLC）：每个单元存储2个比特（3个比特设置也正在进入市场中）。这使得存储密度提高了，但速度和耐擦写性能降低了。一个不错的MLC设备可能被定为10000个写循环周期（2年）
-===9.4.3闪存的基准测试===
+===闪存的基准测试===
 闪存的三阶段模式，我们称为A-B-C模式：
 #A：开始阶段，通常运行比较快
@@ 第1,664行： / 第1,664行： @@
   http://www.ssdperformanceblog.com   http://www.mysqlperformanceblog.com
-===9.4.4固态硬盘===
+===固态硬盘===
 SSD是否配置电容或者电池是我们必须关注的特性
-===9.4.5PCIe设备===
+===PCIe设备===
 没有什么能比得上PCIe设备上获得的性能。缺点是它们太贵了。
-===9.4.6什么时候应该使用闪存===
+===什么时候应该使用闪存===
 #一些事务或插入繁忙的工作负载
 #单线程负载是另一个闪存的潜在应用场景。MySQL复制是单线程工作的典型例子，它可以从低延迟中获得很多收益
@@ 第1,675行： / 第1,675行： @@
 然而缓存也可能不一定是你要的答案。一个很好的例子是，像InnoDB日志文件这样的顺序写的工作负载，闪存不能提供多少性能优势，因为这种情形下，闪存连续写方面不比标准硬盘快多少。这样的工作负载也是高吞吐的，会耗尽缓存的寿命。在标准硬盘上存放日志文件通常是一个更好的主意，用具有电池保护的写缓存的RAID控制器。<br />
 有时答案在内存/磁盘的比例，而不只是磁盘。
-===9.4.7使用Flashcache===
+===使用Flashcache===
 Flashcache创建了一个块设备，并且可以被分区，也可以像其他块设备一样创建文件系统，特点是这个块设置是由闪存和磁盘共同支撑的。闪存设备用作读取和写入的告诉缓存。<br />
 *尽管理论上Flashcache可能更高效，但最终性能并不如低层闪存那么好。
@@ 第1,683行： / 第1,683行： @@
 ;Flashcache的IOPS思想实验:磁盘IPOS: 3,5" 15.000 rpm FC <big>'''150 IOPS'''</big>（参考中列出的最好的一个）参考[http://elf8848.iteye.com/blog/1731301 常见硬盘IOPS参考值] [http://elf8848.iteye.com/blog/1731274 磁盘性能指标--IOPS 理论]
 :假设闪存支持100000IPOS，未命中率为'''10%'''，则按磁盘150IPOS计算，则本Flashcache设备<big>'''仅可以支持 150/10% = 1500IPOS，受制于磁盘！'''</big>
-===9.4.9优化固态存储上的MySQL===
+===优化固态存储上的MySQL===
 关于插入缓冲：[http://www.cnblogs.com/zuoxingyu/p/3761461.html innodb insert buffer 插入缓冲区的理解][http://blog.csdn.net/dbanote/article/details/9077113 InnoDB三大特性之-插入缓冲]
 ;禁用双缓冲:
@@ 第1,691行： / 第1,691行： @@
 :这会把随机I/O集中到缓存设备上，然后把大部分顺序写入的压力尽可能的移出闪存
-==9.6RAID性能优化==
+==RAID性能优化(9.6)==
 ;案例：RAID10 （硬件RAID1，软件RAID0 ReiserFS）获5倍性能提升:请求串行的发送到一个10块盘的RAID10卷中的每个设备，使用ReiserFS文件系统，InnoDB打开了inondb_file_pertable选项。尝试RAID1的基础上用RAID0做成的RAID10的方式，获得了5倍多的吞吐
 ;基准测试:最后，当准备上线新服务器时不要忘了做基准测试！这能让你获得期望的性能。如。若一个硬盘能每秒做200个随机读，一个有8个硬盘驱动器的RAID10卷应该接近每秒1600个随机读。
 ;RAID配置和缓存:除非控制器有电池备份单元（BBU）或其它非易失性存储，否则不应该启用RAID缓存。不带BBU的缓冲写，在断电时，有可能损坏数据库，甚至是事务性文件系统。
-==9.8使用多磁盘卷==
+==使用多磁盘卷(9.8)==
 InnoDB事务日志包含redo（ib_logfile0 ib_logfile1）日志和undo日志（存储于表空间 ibdata中[http://mysql.taobao.org/monthly/2015/04/01/ MySQL · 引擎特性 · InnoDB undo log 漫游]）
 ;分离临时表目录:最好把它们入在内存文件系统，如tmpfs
-==9.9网络配置==
+==网络配置==
 ;skip_name_resolve:<big>'''建议打开'''</big>，默认是关闭的 见8.9
 :损坏或者变慢的的DNS解析对很多应用程序都是个问题。对MySQL尤其严重
@@ 第1,716行： / 第1,716行： @@
 如果需要更大带宽，可以使用网络链路聚合（NICs）（或叫多块网卡并行绑定）
-==9.11选择文件系统==
+==选择文件系统(9.11)==
 ;在/etc/fstab中添加noatime、nodiratime挂载选项可禁用文件的访问时间这样有时可以提高5%~10%的性能:/dev/sda2 /usr/lib/mysql ext3 noatime,nodiratime,data=writeback 0 1
 使用O_DIRECT选项会自动禁用预读。例如，InnoDB有自己的预读策略
-==9.12选择文件调度策略==
+==选择文件调度策略==
 Deadline对数据库环境(ORACLE RAC,MYSQL等)是最好的选择：[http://www.2cto.com/os/201307/228389.html 浅谈linux性能调优之六:IO调度算法的选择]
 ;可按如下命令查询文件调度策略:cat /sys/block/vda/queue/scheduler
@@ 第1,726行： / 第1,726行： @@
 :;noop:适合那么自己在后台实现了高度算法的设备。如RAID和SAN
 :;deadline:则对RAID和直接使用磁盘更好。我们的基准测试表明这两者差别很小（noop），重要的是别使用cfq
-==9.14内存交换区==
+==内存交换区(9.14)==
 用vmstat查看，我们喜欢si和so列的值为0，并且要保证它们低于每秒10个块<br />
 对交换空间利用率要做的监控和报警<br />
@@ 第1,735行： / 第1,735行： @@
 :Direct I/O并不缓存，因此操作系统并不能把MySQL视为增加文件缓存的原因。
 ;另一个选择是使用MySQL的memlock配置项:可以把MySQL锁定在内存。这可以避免交换，但是也可能带来危险：如果没有足够的可锁定内存，MySQL在分配更多的内存时会崩溃。这也可能导致锁定的内存太多而没有足够的内存留给操作系统
-==9.15操作系统状态==
+==操作系统状态(9.15)==
 安装iostat:  yum install sysstat<br />
-==9.15.1如何阅读vmstat的输出==
+===如何阅读vmstat的输出===
 以下是第5秒打印的一个报告：
 [[文件:Vmstat5test.png|600px|缩略图|居中|结果]]
@@ 第1,748行： / 第1,748行： @@
 ;system:这些列显示了很秒中断（in）和上下文切换（cs）的数量
 ;cpu:显示了各类操作的百分比。包括 执行用户代码（非内核）、执行系统代码（内核）、空闲、以及等待I/O
-==9.15.1如何阅读iostat的输出==
+===如何阅读iostat的输出===
 以下是5秒一输出：
 [[文件:Iostat5test.png|600px|缩略图|居中|结果]]

@@ 第1,262行： / 第1,262行： @@
 ;全文索引有时还可以实现一些简单的“边框”搜索 P304:如，希望搜索某个坐标范围时，将坐标按某种方式转换成文本再进行全文索引。假设某条记录的坐标为X=123, Y=456。可以按这样的方式交错存储坐标: XY142536, 然后对此进行全文索引。这时，希望查询某矩形——X[100, 199], Y[400, 499]——范围时，可以在查询直接搜索“+XY14*” ,这比WHERE 的条件过滤要高很多
-==7.11分布式（XA）事务==
+==分布式（XA）事务==
 <big>内部XA事务</big><br />
 innodb_support_xa要打开（默认），才保证二进制日志安全<br />
@@ 第1,268行： / 第1,268行： @@
   show variables like '%sync_binlog%';
 <big>外部XA事务 应尽量避免</big><br />
 ==查询缓存(v)==
 如果表发生变化，那么这个表相关的所有缓存都将失效<br />

@@ 第1,206行： / 第1,206行： @@
 ;LENGTH和CHAR_LENGTH:这两个是用来计算字符器的长度，在多字节字符集中，这两个函数返回的结果不同。例如在用SUBSTRING操作的时候要用CHAR_LENGTH
-==7.10全文索引==
+==全文索引==
 目前只有MyISAM支持全文索引，5.6及之后InnoDB也支持全文索引

@@ 第1,110行： / 第1,110行： @@
 </source>
-==7.6绑定变量==
+==绑定变量(7.6)==
 基于如下原因，MySQL在使用绑定变量时可以更高效的执行大量重复的语句：
 *在服务器端只需要解析一次SQL语句
@@ 第1,118行： / 第1,118行： @@
 *MySQL在存储参数的时候，直接将其放到缓存中，不再需要在内存中多次复制
 绑定变量相对也更安全<br />
-===7.6.2SQL接口的绑定变量===
+===SQL接口的绑定变量===
 <source lang=sql>
 SET @sql := 'SELECT actor_id, first_name, last_name  FROM sakila.actor WHERE first_name = ?';
@@ 第1,167行： / 第1,167行： @@
 </source>
-==7.7用户自定义函数==
+==用户自定义函数==
 [http://www.mysqludf.org 最大的UDF仓库]
-==7.8插件==
+==插件==
 ;存储过程插件:
 ;后台插件:
@@ 第1,177行： / 第1,177行： @@
 ;认证插件:
-==7.9字符集和校对==
+==字符集和校对==
 使用如下语句检查数据库的字符集设置：
   show variables like '%character%';
@@ 第1,268行： / 第1,268行： @@
   show variables like '%sync_binlog%';
 <big>外部XA事务 应尽量避免</big><br />
-==7.12查询缓存==
+==查询缓存(v)==
 如果表发生变化，那么这个表相关的所有缓存都将失效<br />
 '''很多时候我们认为还是应该关闭查询缓存，如果查询缓存作用很大的话，那就配置一个很小的查询缓存（如几十兆）'''。后面我们将解释如果判断在你的系统压力下打开查询缓存是否有好处。
-===7.12.1MySQL如何判断缓存命中===
+===MySQL如何判断缓存命中===
 *MySQL判断缓存是否命中时'''使用原始信息'''，不会解析、“正规化”或参数化，例如空格、注释——任何不同——都会导致不命中
 *当查询中包含一些不确定的函数时，则不会被缓存。例如包含函数NOW()或者CURRENT_DATE()的查询不会被缓存。类似的有 CURRENT_USER或CONNECTION_ID()的查询也不会被缓存。包含任何用记自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，或者任何包含列级别权限的表，都不会被缓存
@@ 第1,283行： / 第1,283行： @@
 另一个指标，“命中和写入”的比值，即 Qcache_hits / Qcache_inserts的比值。'''根据经验来看这个比值大于 3:1时通常查询缓存是有效的，不过这个值最好能达到10:1'''
-===7.12.4如何配置和维护查询缓存===
+===如何配置和维护查询缓存===
 ;query_cache_type:可以设成OFF、ON或DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE才放入查询缓存
 ;query_cache_size:查询缓存使用的总空间，单位是字节。这个值必须为1024的整数倍。
@@ 第1,289行： / 第1,289行： @@
 ;query_cache_limit:MySQL能够缓存的最大结果。如果查询结果大于这个值，则不会被缓存。如果超出，MySQL则增加状态值 Qcache_not_cached。如果事先知道有很多这样的情况发生，那么建议在查询语句中加入SQL_NO_CACHE来避免缓存带来的额外消耗
 ;query_cache_wlock_invalidate:如果某个数据表被其它连接锁住，是否仍然从查询缓存中返回结果。默认为OFF
 =第八章优化服务器配置=
 ==8.1MySQL配置的工作原理==

@@ 第1,023行： / 第1,023行： @@
 在某些场景下，外键会提升一些性能。如果想确保两个表始终有一致的数据，那么使用外键比在应用程序中检查一致性的性能要高很多，此外，外键在相关数据的删除和更新上，也比在应用程序中检查一致性要高很多，不过外键维护操作是逐行进行的。
-==7.4在MySQL内部存储代码==
+==在MySQL内部存储代码==
 缺点：
 *它和基于语句的二进制日志复制合作的并不好<br />
@@ 第1,043行： / 第1,043行： @@
 delimiter ;
 </source>
-===7.4.2触发器===
+===触发器===
 要注意以下几点：
 *对每一个表的每一个事件，最多只能定义一个触发器（换句话说不能在AFTER INSERT上定义两个触发器）
@@ 第1,065行： / 第1,065行： @@
 END;
 </source>
-===7.4.3事件===
+===事件===
 *它类似于Linux的定时任务，可以指定MySQL在某个时候执行一段SQL代码，或者每隔一段时间执行一段SQL代码。通常我们把复杂的SQL封装到一个存储过程中，这样事件在执行的时候只需要做一个简单的CALL调用。<br />
 *事件在一个独立的事件调度线程里初始化，这个线程和处理连接的线程没有任何关系。它不接收任何参数，也没有任何返回值。可能在MySQL的日志中看到命令的执行日志，还可以在INFORMATION_SCHEMA.EVENTS中看到各个事件的状态，如这个事件最后一次被执行的时间。
@@ 第1,095行： / 第1,095行： @@
 *虽然事件是一个单独的线程，但是事件来身是可以并行执行的。MySQL会创建一个新的进程用于事件执行
-===7.4.4在存储程序中保存注释===
+===在存储程序中保存注释===
 技巧：用版本号的注释且用一个非常大的数字，如99999（因为不这样的话，如果用命令行客户端来导入备份的数据，注释就会被干掉）
 <source lang=sql>

@@ 第983行： / 第983行： @@
   EXPLAIN PARTITIONS SELECT * FROM sales \G
-==7.2视图==
+==视图(7.2)==
 创建视图：
 <source lang=sql>create view Oceania as select*from country where continent ='Oceania' with check option;</source>
@@ 第998行： / 第998行： @@
 也可以在创建视图时指定临时表算法：
   CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM sakila.actor;
-===7.2.1可更新视图===
+===可更新视图===
 <source lang=sql>UPDATE Oceania SET Population = Population * 1.1 WHERE Name = 'Australia';</source>
 注意：
@@ 第1,005行： / 第1,005行： @@
 *所有使用临时表算法的视图不能被更新
 *MySQL的视图不支持触发器
-===7.2.2视图对性能的影响===
+===视图对性能的影响===
 视图在某些情况下是可以提升性能的：
 *在重构schema的时候可以使用视图，使得在修改底层表结构的时候，应用代码还可以不报错的运行