高性能MySql

原文：https://www.jianshu.com/p/52ffbadf6b12

第四章 schema与数据类型优化

数据类型：
1、varchar，字符串列的最大长度比平均长度大和诺，适合用varchar类型；
2、char,适合存储很短的字符串，或者所有值都接近同一个长度；
3、时间日期类型，尽量使用TIMESTAMP,它比DATETIME空间效率更高；
1、缓存表、汇总表和影子表
2、除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎。
3、mysql可以对整型指定宽度，例如：INT(11),对大多数应用是没有意义的，整型长度只对在mysql一些交互工具中作为显示整型的长度，对于值计算来源INT(1)和INT(11)是相同的。
4、varchar适合场景：字符串列的最大长度比平均长度大很多；char适合场景：存储很短的额字符串，或者所有值都接近同一长度，例如md5加密后的值；
5、大数据量alter table会锁表并且重建整张表，执行时间可达几个小时甚至几天，解决方案：一、现在一台不提供服务的机器上执行alter table操作，然后和提供服务的主库进行切换；二、“影子拷贝”，即创建一张与源表无关的新表，然后通过重命名和删除表的方式交换两张表；
6、 mysql disable keys禁用索引，此操作对单条索引无效，只对多条索引有用；

范式

第一范式：无重复列，表中的每一列都是不可分割的基本数据项
第二范式：属性完全依赖于主键，不能存在仅依赖于关键一部分的属性
第三范式：属性不传递依赖于其它非主属性，非主键列必须直接依赖于主键，而不能传递依赖。
反范式：用空间换时间，将数据冗余在多张表中，查询中无须关联

范式优点：
(1) 范式化的更新操作通常比反范式化要快
(2)当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据
(3)范式化的表通常更小，占用更小的内存，所以处理速度更快
(4)很少有多余的数据，意味着检索列表时更少需要distinct和group by语句时间

范式缺点：
符合范式的schema设计，查询时通常需要关联查询

schema设计简单原则

尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计；
使用小而简单的合适数据类型，除非真是数据模型中有确切的需要，否则应该尽可能地避免使用NULL值
尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列；
注意可变长字符串，其在临时表或者排序时可能悲观的按最大长度分配内存
尽量使用整型标识列
避免使用mysql已经遗弃的特性，例如指定浮点数的精度（可用decimal代替），或者整数的显示宽度
小心使用ENUM和SET，尽量避免使用；避免使用BIT；

第五章创建高性能的索引

1、不同的存储引擎的所在的工作方式并不一样，也不是所有得存储引擎支持所有类型的索引；
2、索引的优点：索引大大减少了服务器需要扫描的数据量；索引可以帮助服务器避免排序和临时表；索引可以将随机IO变为顺序IO;

高性能的索引策略

1、独立的列，索引列不能是表达式的一部分；
2、选择合适的索引顺序，将选择性高的索引放在最前面；
3、聚蔟索引，包含B-Tree索引和数据行；优点：可以将相关数据保存在一起，数据访问更快，使用聚簇索引扫描的查询可以直接使用页节点中的主键值；缺点：数据插入速度依赖于顺序插入，聚蔟索引可能导致全表扫描；
4、覆盖索引，如果一个索引包含所有需查询的字段的值，该索引为覆盖索引。覆盖索引可以极大的提高查询性能。在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。
5、所用索引扫描来做排序，只有当所用的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样，mysql才能使用索引来对结果进行排序；
6、冗余和重复索引，重复索引是指相同的列上按照相同个顺序创建的相同类型的索引；要尽量不使用冗余索引，尽量扩展已有的索引而不是创建新索引；
7、要删除未使用的索引，可提升insert update执行效率；

维护索引和表

1、找到并修复损坏的表；
2、更新索引统计信息，show index from table可查询索引信息；
3、减少索引和数据的碎片，optimize table可有效整理数据，去除数据碎片；例如：delete table后，optimize一下，可有效降低数据占用空间；

哈希索引的限制：

1、哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行；
2、哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序；
3、哈希索引页不支持部门索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的；
4、哈希索引只支持等值比较查询；不支持范围查询；

选择索引的三个原则：

单行访问很慢的。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么久浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引创建位置引用可提升效率。
按顺序访问范围数据是很快的，这有两个原因。第一，顺序IO不需要多次磁盘寻道，所以比随机IO要快很多。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且group by查询也无须再做排序和将行按组进行聚合计算了。
索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回标查找行。这避免了大量的单行访问。

第六章查询性能优化

查询生命周期：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。

慢查询基础：优化数据访问

低效查询分析方法：

确认应用程序是否在检索大量超过需要的数据。通常意味着访问了太多的行，也有可能访问太多的列。
确认mysql服务器层是否在分析大量超过需要的数据行。

低效查询典型案列：

查询不需要的记录
多表关联时返回全部列
总是取出全部列
重复查询相同的数据

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

响应时间
响应时间包括服务时间和排队时间；服务时间：是指数据库处理这个查询真正花了多长时间，排队时间：服务器因为等待某些资源而没有真正执行查询的时间（可能是IO，行锁等等）；
扫描的行数
返回的行数
较短的行的访问速度更快，内存中的行比磁盘中的行访问速度更快；较短的行数，是在内存中查询，当行数较多时则在磁盘中查询；

重构查询方式

一个复杂查询还是多个简单查询
切分查询（大查询分为小查询，例如：大扫描行数查询切分成多个小扫描行数的查询）
分解关联查询，优点：让缓存效率更高；让单个查询减少锁竞争；在应用层做关联，容易对数据库进行拆分，提高系统性能；减少冗余记录的查询；

查询执行的基础

mysql查询过程：

客户端发送一条查询给服务器
服务器先查询缓存，如果命中了缓存，直接返回结果；否则，进入下一步；
服务器进行sql解析、预处理，再由优化器生成对应的执行计划；
mysql根据优化器生成的执行计划，再调用存储引擎API来执行查询；
将查询结果返回给客户端；

SHOW FULL PROCESSLIST可查看当前状态；
sleep:线程正在等待客户端发送新的请求；
Query:线程正在执行查询或者正在将结果发送给客户端；
Locked:该线程正在等待表锁；
Analyzing and statistics:线程正在收集存储引擎的统计信息，并生产查询的执行计划；
Coping to tmp table:线程正在执行查询，并将其结果复制到临时表中；
Sorting result:线程正在对结果集进行排序；
Sending data:线程可能在多种状态之间传送数据，或者正在生成结果集，或者正在向客户端发送数据；

mysql在进行文件排序的时候需要使用的临时存储空间可能比想象的要大得多。

mysql查询优化器的局限性

不需要听取那些关于子查询的“绝对真理”；
应该用测试来严重对子查询的执行计划和响应时间的假设；

mysql不允许对同一张表同事进行查询和更新操作。

优化特性类型的查询

优化count()查询
简单优化，反向count(1)，再减去查询结果；使用近似值代替；使用汇总表，定时汇总数据，总汇总表里查询结果；
优化关联查询
确保on和using列上有索引；确保order by和group by只包含一列；
优化子查询
尽可能使用关联查询代替；
优化group by和distinct
优化limit查询
避免过多的offset；select * from order where id > 10030 order by id desc limit 20;
优化union查询
如果无重复数据，使用union all 代替union；

优化通常都需要三管齐下：不做、少做、快速地做

第十一章：可扩展的MySql

可扩展性：当增加资源以处理负载和增加容量时系统能够获得的投资产出率。

向上扩展：也叫垂直扩展，购买更多性能强悍的硬件；
向外扩展：向外扩展策略划分为三个部分：复制、拆分、以及数据分片
通过多实例扩展：每台服务器上运行过个实例，然后划分服务器的硬件资源，将其分配给每个实例。在一台性能强悍的硬件上可以获得10倍到15倍的合并系数。你需要平衡管理复杂度代价和更有性能的收益。
通过集群扩展：未来典型的集群数据库可能更像是SQL和NoSQL的混合体，有多重存取机制来满足不同的使用需求。MySQL Cluster(NDB Cluster)
向内扩展：处理不断增长的数据和负载最简单的办法是对不再需要的数据进行归档和清理。
做归档和清理时考虑以下几点：

对应用的影响
要归档的行
维护数据一致性
避免数据丢失
解除归档

保持活跃数据独立，即使不真的把老数据转移到别的服务器，也许应用也能受益于活跃数据和飞活跃数据的隔离。可以有一下集中做法：
1、将表划分为几个部分
2、MySQL分区
3、基于时间的数据分区

负载均衡
负载均衡有五个常见的目的：
可扩展性、高效性、可用性、透明性、一致性
一、直接连接
1.1 复制上的读写分离
1.2 修改应用的配置
1.3 修改DNS名
1.4 转移IP地址
二、引入中间件
2.1 负载均衡器
2.2 负载均衡算法
随机、轮询、最少连接数、最快响应、哈希、权重
2.3 在服务器池中增加和删除服务器
三、一主多备的负载均衡

Mysql EXPLAIN执行计划

id列：这一列总是包含一个编号，标识SELECT所属的行。

作者：程序猿小屌丝
链接：https://www.jianshu.com/p/52ffbadf6b12
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。