高并发解决方案-mysql数据库层的优化

数据表数据类型

数据类型介绍

数值类型

整型

tinyint, smallint, mediumint, int ,bigint（8，16，24，32，64位存储空间）
属性：unsigned
int（3） 指定显示的宽度（显示字符的个数） ，不会限制值的合法范围
属性：zerofill 
12  int （3） zerofill  012 

实数类型

float,double, decimal

decimal可存储比bigint还大的整数；可以用于存储精确的小数
float和double类型支持使用标准的浮点数进行近似计算

字符串类型

varchar，char，text，blob

varchar类型用于存储可变长字符串，它比定长更节省空间
varchar使用一个或两个额外字节记录字符串的长度，列长度小雨255字节，使用1个字节表示，否则用2个
varchar长度，如果存储内容超过指定长度，会被截断。

char是定长的，根据定义的字符串长度分配足够的空间
char会根据需要采用空格进行填充以方便比较
char适合存储很短的字符串，或者所有值都接近同一个长度
char长度，超过设定的长度，会被截断

对于经常变更的数据，char比varchar更好，char不容易产生碎片
对于非常短的列，char比varchar在存储空间上更有效率，只分配真正需要的空间，更长的列会消耗更多的内存。

尽量避免使用blob/text类型，查询会使用临时表，导致严重的性能开销。

枚举

有时可以使用枚举代替常用的字符串类型，把不重复的集合存储为一个预定义的集合，非常紧凑，把列表值压缩到一个或两个字节，内部存储的是整数。
尽量避免使用数字作为enum枚举的常量，易混乱
排序是按照内存存储的整数进行排序
枚举表会使表大小大大减小

日期和时间类型

尽量使用timestamp，比datetime空间效率更高
用整数保存时间戳的方式通常不方便处理
如果需要存储微秒，可以用bigint存储

date, time, year, datetime, timestamp
[
    YYYY-MM-DD (1000-01-01~9999-12-31)
    HH:MM:SS(-838:59:59~838:59:59)
    YYYY(1901~2155)
    YYYY-MM-DD HH:MM:SS (1000-01-01 00:00:00~9999-12-31 23:59:59)
    YYYY-MM-DD HH:MM:SS(1970-01-01 00:00:00~2037)
    
]

列属性

auto_increment, default, not null, zerofill

优化

字段使用什么数据类型更合适
字段使用什么数据类型性能更快
举例：
考虑空间、范围

tinyint smallint bigint 

考虑长度是否固定

char  手机号，md5后的密码（32位）
varchar 姓名

特定，固定的分类可以用enum，效率更快
ip 使用整型来存，可以用php内置函数转换`long2ip ip2long

索引优化

索引对性能的影响

• 大大减少服务器需要扫描的数据量
• 帮助服务器避免排序和临时表
• 将随机I/O变成顺序I/O
• 大大提高查询速度，降低写的速度，占用磁盘（缺点）

索引类型：（实现在存储引擎层）

• 普通索引：最基本的索引，没有任何约束限制
• 唯一索引：与普通索引类似，但是具有唯一性约束
• 主键索引 —特殊的唯一索引，不允许有空值，一个表只能有一个主键索引
• 组合索引：将多个列组合在一起创建索引，可以覆盖多个列
• 外键索引 —InnoDB，保证数据一致性，完整性和实现级联操作
• 全文索引 —MyISAM ,只能对英文进行全文索引

建立合适的索引

索引的创建原则

1.最适合索引的列是出现在WHERE子句中的列，或连接子句中的列而不是出现在SELECT关键字后的列

2.索引列的基数越大，索引的效果越好

3.对字符串进行索引，应该制定个前缀长度，可以节省大量的索引空间

4.根据情况创建复合索引，复合索引可以提高查询效率

5.避免创建过多索引，索引会额外占用磁盘空间，降低写操作效率

6.主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率

• 索引不是越多越好，在合适的字段创建合适的索引
• 复合索引的前缀原则

索引的注意事项

• 复合索引的前缀原则
• like查询，%在前无法利用索引
• column is null 也可以使用索引
• 如果mysql估计使用索引比全表扫描更慢，会放弃使用索引
• 如果or前的条件列有索引但是后面的没有，索引都不会 被用到
• 字符串索引 查询时需要加上‘’，否则会出现隐式转换，无法利用索引

sql语句

优化查询过程中的数据访问

数据访问太多导致查询性能下降
确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
确定mysql服务器是否在分析大量不必要的数据行

sql注意

查询不需要的记录，使用limit解决
多表关联返回需要的指定列，不要返回全部列
返回列不用*，总是取出全部列，select * 会让优化器无法完成索引覆盖扫描的优化
重复查询的数据，可以缓存，下次直接查询缓存

是否在扫描额外的记录

使用explain来进行分析，如果发现查询需要扫描大量的数据但只返回少数的行，可以通过如下技巧去优化:

使用索引覆盖扫描，把所有用的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果

改变数据库和表的结构，修改数据表范式

重写SQL语句，让优化器可以以更优的方式执行查询

优化长难句的查询语句

变复杂为简单

MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多
使用尽可能少的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的

切分查询

MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多
使用尽可能少的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的

分解关联查询

可以将一条关联语句分解成多条SQL来执行
让缓存的效率更高
执行单个查询可以减少锁的竞争
在应用层做关联可以更容易对数据库进行拆分

优化特定类型的查询语句

优化count()

count(*)中的*会忽略所有的列，直接统计所有列数，因此不要使用count(列名)
MyISAM中，没有任何WHERE条件的count(*)非常快
当有WHERE条件，MyISAM的count统计不一定比其他表引擎快

优化关联查询

确定ON或者USING子句的列上有索引
确保GROUP BY和ORDER BY中只有一个表中的列，这样MySQL才有可能使用索引

优化子查询

尽可能使用关联查询来替代

优化group by 和distinct

这两种查询均可使用索引来优化，是最有效的优化方法关联查询中，使用标识列进行分组的效率会更高

如果不需要ORDER BY，进行GROUP BY时使用ORDER BY NULL，MySQL不会再进行文件排序

WITH ROLLUP超级聚合,可以挪到应用程序处理

优化limit和union

• LIMIT偏移量大的时候，查询效率较低
  可以记录上次查询的最大ID，下次查询时直接根据该ID来查询
• UNION ALL 的效率高于 UNION
  使用union all不会合并重复的记录行

存储引擎

InnoDB表引擎

默认事务型引擎，最重要最广泛的存储引擎，性能非常优秀
数据存储在共享表空间，可以通过配置分开
对主键查询的性能，高于其他类型存储引擎
内部做了很多优化，从磁盘读取数据时自动在内存构建hash索引
插入数据时，自动构建插入缓冲区
通过一些工具和机制支持真正的热备份
支持崩溃后的安全恢复
支持行级锁
支持外键

MyISAM表引擎

5.1以前，MyISAM是默认的存储引擎
拥有全文索引，压缩，空间函数
不支持事务和行级锁，不支持崩溃后的安全恢复
表存储在两个文件， .MYD和.MYI
设计简单，某些场景下性能很好

其他表引擎

Archive， Blackhole， CSV, Memory

尽量使用InnoDB存储引擎

数据表结构设计

分区操作

通过特定的操作对数据表进行物理拆分
对用户透明

 partition by

分库分表

水平拆分（行 - 查所有数据用union）
垂直拆分（列 - 查所有数据用join）

数据库架构

数据备份和恢复

全量备份 （mysqldump）

全量备份的代价非常高：
首先，备份文件包含数据库中的所有数据，占用的磁盘空间非常大；其次，每次备份操作都要拷贝大量数据，备份过程中会占用数据库服务器大量的 CPU、磁盘 IO 资源，并且为了保证数据一致性，还有可能会锁表，这些都会导致备份期间，数据库本身的性能严重下降。

增量备份（binlog）

原理：
MySQL 自带了 Binlog，就是一种实时的增量备份。Binlog 里面记录的就是 MySQL 数据的变更的操作日志，开启 Binlog 之后，我们对 MySQL 中的每次更新数据操作，都会被记录到 Binlog 中。Binlog 是可以回放的，回放 Binlog，就相当于把之前对数据库所有数据更新操作按照顺序重新执行了一遍，回放完成之后数据自然就恢复了。

主从复制

异步复制 （默认）

默认情况下，MySQL 采用异步复制的方式，执行事务操作的线程不会等复制 Binlog 的线程。

MySQL 主库在收到客户端提交事务的请求之后，会先写入 Binlog，然后再提交事务，更新存储引擎中的数据，事务提交完成后，给客户端返回操作成功的响应。同时，从库会有一个专门的复制线程，从主库接收 Binlog，然后把 Binlog 写到一个中继日志里面，再给主库返回复制成功的响应。

从库还有另外一个回放 Binlog 的线程，去读中继日志，然后回放 Binlog 更新存储引擎中的数据，这个过程和我们今天讨论的主从复制关系不大，所以我并没有在图中画出来。提交事务和复制这两个流程在不同的线程中执行，互相不会等待，这是异步复制。

半同步复制 （ mysql 5.7 以后支持）

同步复制

同步复制时，主库在提交事务的时候，会等待数据复制到所有从库之后，再给客户端返回响应。

同步复制这种方式在实际项目中，基本上没法用，原因有两个：
一是性能很差，因为要复制到所有节点才返回响应；
二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。

读写分离

主库负责执行应用程序发来的所有数据更新请求，然后异步将数据变更实时同步到所有的从库中去，这样，主库和所有从库中的数据是完全一样的。多个从库共同分担应用的查询请求。

双主热备

负载均衡

• 通过LVS三种模式（ NAT, DR, TUN)实现负载均衡
• MyCat数据库中间件实现负载均衡