高性能MySQL【笔记】超详细(一)

高性能MySQL【笔记】超详细（一）一、MySQL架构与历史A.并发控制1.共享锁（sharedlock，读锁）：共享的，相互不阻塞的2.排他锁（exclusivelock，写锁）：排他的，一个写锁会阻塞其他的写锁和读锁B.事务1.事务ACID*原子性（atomicity）一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作*一致性（consistency）数据库总是从一个一致性的状态转换到另外一个一致性的状态*隔离性（isolation）一个事务所做的修改在最终提交以前，对其他事务是不可见的*持久性（durability）一旦事务提交，则其所做的修改就会永久保存到数据库中2.四种隔离级别*READUNCOMMITTED（未提交读），事务中的修改，即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，也被称为脏读（DirtyRead），这个级别会导致很多问题*READCOMMITTED（提交读），大多数数据库系统的默认隔离级别，一个事务开始时，只能“看见”已经提交的事务所做的修改，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的，也叫不可重复读（nonrepeatableread），有可能出现幻读（PhantomRead），指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（PhantomRow）*REPEATABLEREAD（可重复读），通过InnoDB和XtraDB存储引擎，是MySQL的默认事务隔离级别*SERIALIZABLE（可串行化）最高级别，通过强制事务串行执行，避免了幻读问题，会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题3.死锁：指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象4.事务日志：存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘，称为预写式日志（Write-AheadLogging）C.多版本并发控制1.多版本并发控制（MVCC）是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行2.MVCC的实现，是通过保存数据在某个时间点的快照来实现的，有乐观和悲观两种，只在REPEATABLEREAD和READCOMMITTED两个隔离级别下工作D.MySQL的存储引擎1.MySQL的.frm文件保存表的定义，SHOWTABLESTATUS显示表的相关信息2.除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎3.不要轻易相信MyISAM比InnoDB快之类的经验之谈，这个结论并不是绝对的二、MySQL基准测试A.为什么需要基准测试1.基准测试可以观察系统在不同压力下的行为，评估系统的容量，掌握哪些是重要的变化，或者观察系统如何处理不同的数据B.基准测试的策略1.两种主要的策略：*针对整个系统的整体测试（集成式full-stack）*单独测试MySQL（单组件式single-component）2.测试何种指标：*吞吐量，指单位时间内的事务处理数，常用的测试单位是每秒事务数（TPS），或每分钟事务数（TPM）*响应时间或者延迟，用于测试任务所需的整体时间，根据具体的应用，测试的时间单位可能是微秒、毫秒、秒或者分钟。通常使用百分比响应时间（percentileresponsetime）来替代最大响应时间*并发性，需要关注的是正在工作中的并发操作，或者是同时工作中的线程数或者连接数，在测试期间记录MySQL数据库的Threads_running状态值*可扩展性，给系统增加一倍的工作，在理想情况下就能获得两倍的效果（即吞吐量增加一倍），对于容量规范非常有用，可以提供其他测试无法提供的信息，来帮助发现应用的瓶颈C.基准测试方法1.需要避免的一些常见错误：*使用真实数据的子集而不是全集*使用错误的数据分布*使用不真实的分布参数*在多用户场景中，只做单用户测试*在单服务器上测试分布式应用*与真实用户行为不匹配*反复执行同一个查询*没有检查错误*忽略了系统预热（warmup）的过程*使用默认的服务器配置*测试时间太短2.应该建立将参数和结果文档化的规范，每一轮测试都必须进行详细记录3.基准测试应该运行足够长的时间，需要在稳定状态下测试并观察4.在执行基准测试时，需要尽可能多地收集被测试系统的信息5.自动化基准测试可以防止测试人员偶尔遗漏某些步骤，或者误操作，另外也有助于归档整个测试过程，可以选择shell、php、perl等，要尽可能使所有测试过程都自动化，包括装载数据、系统预热、执行测试、记录结果等D.基准测试工具1.集成式测试工具：*ab，测试HTTP服务器每秒最多可以处理多少请求*http_load，和ab类似，但更加灵活*jMeter，可以加载其他应用并测试其性能2.单组件式测试工具*mysqlslap，可以模拟服务器的负载，并输出计时信息*MySQLBenchmarkSuite(sql-bench)，单线程的，主要用于测试服务器执行查询的速度*SuperSmack，提供压力测试和负载生成，是一个复杂而强大的工具，可以模拟多用户访问，可以加载测试数据到数据库，并支持使用随机数据填充测试表*DatabaseTestSuite，类似某些工业标准测试的测试工具集*Percona'sTPCC-MySQWLTool*sysbench，多线程系统压测工具，可以根据影响数据库服务器性能的各种因素来评估系统的性能三、服务器性能剖析A.性能优化简介1.性能，为完成某件任务所需要的时间度量，性能即响应时间，这是非常重要的原则2.如果目标是降低响应时间，就需要理解为什么服务器执行查询需要这么多时间，然后去减少或者消除那些对获得查询结果来说不必要的工作。无法测量就无法有效地优化3.性能剖析（profiling）是测量和分析时间花费在哪里的主要方法，一般有两个步骤：测量任务所花费的时间，对结果进行统计和排序B.对应用程序进行性能剖析1.性能瓶颈可能的影响因素：*外部资源*应用需要处理大量的数据*在循环中执行昂贵的操作*使用了低效的算法2.PHP性能剖析工具：NewRelic、xhprof、IfpC.剖析MySQL查询1.剖析服务器负载*慢查询日志：5.1后long_query_time为0可以捕获所有的查询，查询的响应时间单位可以做到微秒级*生成剖析报告：pt-query-digest2.剖析单条查询：*SHOWPROFILES;*SHOW[GLOBAL]STATUS;，返回一些计数器D.诊断间歇性问题1.尽量不要用试错的方式来解决问题，如果一时无法定位，可能是测量的方式不正确，或者测量的点选择有误，或者使用的工具不合适2.确定单条查询问题还是服务器问题*使用SHOWGLOBALSTATUS*使用SHOWPROCESSLIST*使用查询日志*理解发现的问题：使得gnuplot或R，或其他绘图工具将结果绘制成图形3.捕获诊断数据*诊断触发器：在问题出现时能够捕获数据的基础，有两个常见问题可能导致无法达到预期的结果：误报（falsepositive）或者漏检（falsenegative）,pt-stalk工具*收集数据：尽可能收集所有能收集的数据，但只在需要的时间段内收集，oprofile、strace、tcpdump、GDB堆栈跟踪、pt-collect、pt-stalk*解释结果数据：pt-mysql-summary、pt-summary输出结果打包，pt-sift得到样本汇总信息，pt-pmpE.其他剖析工具1.使用USER_STATISTICS表2.使用strace，可以调查系统调用的情况四、Schema与数据类型优化A.选择优化的数据类型1.数据类型的选择原则：*更小的通常更好*简单就好*尽量避免NULL2.应该尽量只在对小数进行精确计算时才使用DECIMAL，使用int类型通过程序控制单位效果更好3.使用VARCHAR合适的情况：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储4.CHAR适合存储很短的字符串，或者所有值都接近同一个长度；不容易产生碎片，在存储空间上更有效率5.通常应该尽量使用TIMESTAMP，它比DATETIME空间效率更高B.MySQLschema设计中的陷阱1.不好的设计：*太多的列*太多的关联*全能的枚举*变相的枚举*非此发明（NotInventHere）的NULLC.范式和反范式1.范式的优点：*范式化的更新操作通常比反范式化要快*当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据*范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快*很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUPBY语句2.范式化设计的缺点是通常需要关联3.反范式的优点：避免关联，避免了随机I/O，能使用更有效的索引策略D.缓存表和汇总表1.有时提升性能最好的方法是同一张表中保存衍生的冗余数据，有时也需要创建一张完全独立的汇总表或缓存表2.物化视图，MySQL并不原生支持，Flexviews3.如果应用在表中保存计数器，则在更新计数器时可能踫到并发问题，创建一张独立的表存储计数器，可以帮助避免缓存失效*解决独立表并发问题可以建多行，根据id随机更新，然后统计时sum()*按天或小时可以单独建行，旧时间可定时任务合并到统一的一行E.加快ALTERTABLE操作的速度1.两种方式：*一是在一台不提供服务的机器上执行ALTERTABLE操作，然后和提供服务的主库进行切换*二是通过“影子拷贝”，创建一张新表，然后通过重命名和删表操作交换两张表及里面的数据2.快速创建MyISAM索引，先禁用索引，导入数据，然后重新启用索引五、创建高性能的索引A.索引基础1.索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列2.ORM工具能够产生符合逻辑的、合法的查询，除非只是生成非常基本的查询，否则它很难生成适合索引的查询3.在MySQL中，索引是在存储引擎层而不是服务器层实现的，所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引4.B-Tree意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，能够加快访问数据的速度，从索引的根节点开始进行搜索，适用于全键值、键值范围或键前缀查找5.B-Tree索引的限制：*如果不是按照索引的最左列开始查找，则无法使用索引*不能跳过索引中的列*如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找6.哈希索引（hashindex）基于哈希表实现，只有精确匹配索引所有列的查询才有效，只有Memory引擎显式支持哈希索引7.哈希索引的限制：*哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行*哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序*哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的*只支持等值比较查询，不支持任何范围查询*访问哈希索引的数据非常快，除非有很多哈希冲突*如果哈希冲突很多的话，一些索引维护操作的代价也会很高8.空间数据索引（R-Tree），MyISAM表支持空间索引，可以用作地理数据存储，开源数据库系统中对GIS的解决方案做得比较好的是PostgreSQL的PostGIS9.全文索引，适用于MATCHAGAINST操作，