mysql学习总结（高性能MySQL笔记）

1.mysql基本情况

mysql架构

    处理与存储分离

    三层结构：连接等基本功能层，mysql核心处理层（查询缓存，sql解析，优化器），存储引擎层

    共享锁与排他锁，锁粒度与锁开销，表锁与行级锁，服务器层与存储引擎层加锁。

    数据库事务，数据库四种类型的事务：读未提交，读已提交，可重复读，序列化，这四种类型的分类分别从事务是否提交，对修改是否透明，对插入删除是否透明进行区分的。死锁，事务日志，mysql的锁主要是在存储引擎层实现，事务全部在存储引擎层实现

    多版本控制mvcc机制（http://blog.youkuaiyun.com/chen77716/article/details/6742128）

    存储引擎：innodb 设计目标：处理大量短事务，性能好，崩溃能够自动恢复，使用mvcc来支持高并发（http://blog.youkuaiyun.com/chen77716/article/details/6742128），实现了四个标准的隔离级别，innodb表与聚簇索引，聚簇索引的原理及优缺点；myisam引擎的锁与并发，索引等特性。csv引擎，memory引擎，archive引擎，面向列的第三方引擎，

5.index设计

索引的基本功能：一种帮助快速找到记录的数据结构

索引种类：B-tree索引，hash索引，全文索引等。

索引的优点：能够大大减少服务器需要扫描的数据量（一个是不需全表扫描，一个是可以从索引中直接获取到一部分数据，无需访问表），能够帮助服务器避免一些排序和临时表（对于排序索引有效），能够将随机IO变为顺序IO（对于排序索引有效）。

三星索引：索引将相关的记录放在一起  一星，索引中的数据顺序和查找的排列顺序一致  一星，索引中的列包含了查找中的全部列  一星

索引的缺点：索引针对小型表和超大型表效果不太好，小型表本来全表扫描的代价很小，超大型表的索引维护成本很高。

高效索引：

       where 子句中需要索引的列独立放在条件符号一侧

       前缀索引与选择性    一般用于像text等长字符串的列中

       多列索引与索引合并

       索引列顺序

       聚簇索引

       覆盖索引

       索引扫描与排序

       压缩索引，冗余和重复索引，未使用的索引，索引和锁。

4.schema设计

schema的一些基本原则：1.更小的通常更好，节省空间，2.简单类型一般更好，节省CPU时间，3.尽量避免null，避免对null单独处理

首先，为列选择合适的大类型，然后再选择具体的小类型。

数字类型：整数和实数

整数有tinyint(8), smallint(16), mediumint(24), int(32), bigint(64)，在mysql内部计算时，一般都转为bigint再进行计算

实数有float(32)，double(64)，decimal(65个数字，支持精确计算）

字符串类型有varchar（变长，不截断后面空格，有1-2字节保存长度），char（定长，截断后面空格，保存时自动补空格），这些是在硬盘上的结果，但将数据读到内存后，varchar的 变长可能会变成定长。

blob与text，前者使用二进制形式存储，后者使用字符串存储，只会排序前面一小部分数据，可以通过max_sort_length参数设置。

有时可以将一些字符串转成enum类型，enum存储格式是整数类型，并在表文件中保存了整数到enum的映射查询表，使得存储更紧凑

日期和时间类型：year，date，datetime，timestamp等,

datetime使用8字节整数存储，存储范围1001年-9999年，timestamp1970年-2038年，使用4字节整数存储

位数据类型：bit和set，set与enum的异同

mysql schema设计中的一些常见问题

范式与反范式的优缺点与权衡

合适地使用缓存表与汇总表

快速地修改schema的一些技巧

2.基准测量

3.性能剖析

性能优化的原则：1.性能即响应时间，2.无法测量就无法有效地优化

6.查询优化

查询优化指的是缩短对数据库请求的响应时间。

查询任务可以细分为一系列子任务：比如从应用通过网络将查询命令发送到mysql服务器，服务器解析查询命令，生成执行计划，存储引擎执行查询，服务器返回数据等步骤，对应地，查询优化可以在上面的每一个子任务中进行。

衡量查询开销的最简单的三个指标：

响应时间

查询的行数

返回的行数

其中响应时间由排队时间和服务时间两部分组成。

优化数据访问：

是否检索了大量超过需要的数据：有的sql语句从数据库中请求多过多行数据回来后，却只需要使用少数行数据（比如前10行）丢掉了绝大部分数据；有的sql语句读取了太多列的数据，而实际上应用只需要其中的几列数据；有时候应用可以缓存数据，根本可以不用去数据库中重复读取。

mysql服务器层是否在分析大量超过需要的数据行：如果扫描的行数大大超过了返回的数据行，那么可以从下面的几方面去优化：1，是否能够通过建立合适索引，达到索引覆盖扫描，2，改变库表结构，如使用单独汇总表，3，重写这个复杂查询，让mysql能够以更优化的方式执行这个查询。

重构查询方式：

首先，如果有些复杂的sql语句很耗时，也许可以重构为一组简单查询，然后在应用程序中进行关联处理，从而降低数据库服务器的压力：可行性，有利的一面是一般来说网络带宽和网卡不是访问数据的瓶颈，mysql的连接机制也是的建立连接和断开连接的开销很轻量级，不利的地方是一般来说，mysql内部每秒能够扫描内存中上百万行数据，相对来说，mysql响应数据给应用程序的速度就慢很多。

其次，将一些耗时耗资源很高的sql切分成一组sql分时段执行，能够降低对服务器的影响，提高可用性。

再次，对一些关联查询可以进行分解，分解成针对单表的一组查询，然后在应用端进行关联。这样做有这么一些优点：1.使得应用端的缓存效率更高，缓存复用几率更大，2.降低对数据库锁的竞争，3.更容易对数据库进行拆分，提高数据库层的可扩展性。

mysql内部查询过程分析

mysql客户端/服务器通信协议

查询缓存

优化器处理

      语法解析与预处理，执行计划优化方式（静态优化和动态优化），数据和索引统计信息使用，关联查询的原理，执行计划的生成方式，关联查询的优化方式，排序优化的方式。

执行引擎

结果返回

mysql查询优化器的一些局限性

针对查询优化器的一些提示（hint）

一些特定类型的优化