Mysql的性能优化

Mysql优化主要分为以下四大方面：

设计：存储引擎，字段类型，范式与逆范式

功能：索引，缓存，分区分表,数据库连接池

架构：主从复制(主库写,从库读)，读写分离，集群,负载均衡

合理SQL：测试，优化查询语句,经验, Explain 查看执行计划,   慢日志

数据库连接池: C3p0 Hikai Druid

缓存:Redis MongDb MemCache HBase

分库分表:数据量非常大,垂直:按业务,水平:按数据

一、存储引擎

存储引擎是一种用来存储MySQL中对象（记录和索引）的一种特定的结构（文件结构），处于MySQL服务器的最底层，直接存储数据。导致上层的操作，依赖于存储引擎的选择

本质：存储引擎就是特定的数据存储格式

可以使用show engines命令来查看当前MySQL支持的存储引擎列表

 Mysql版本>=5.5 默认的存储引擎，MySQL推荐使用的存储引擎。支持事务，行级锁，外键约束,更加注重数据的完整性和安全性。有序存储（按照主键，插入耗时（插入前要排序,在b+tree中找到插入点），查询效率高）,数据，索引集中存储，存储于同一个表空间文件中。

MyISAM存储引擎介绍

MySQL<= 5.5 MySQL默认的存储引擎

数据和索引分别存储于不同的文件中,数据的存储顺序为插入顺序（没有经过排序）

总结:关于Innodb 和myisam的取舍：

Innodb ：数据完整性，并发性处理，擅长更新，删除。

myisam：高速查询及插入。擅长插入和查询。

具体举例：

那么对于微博项目来看，选择哪一个存储引擎呢？

a.微博主要是插入微博和查询微博列表，较为适合MyISAM；

b.微博在更新微博和删除微博，要少的多，较为适合MyISAM；

c.对数据完整性的需求并没有那么强烈，比如用户删除微博，关联的转播和评论并不要求都做相应的行为，较为适合MyISAM；

那么对于记账财务系统，选择哪一款存储引擎呢？

a.财务系统除了读取和插入，经常要进行数据的修改和删除，较为适合InnoDB；

b.在进行财务变更的时候，如果失败需要回滚必须用到事务，较为适合InnoDB；

c.每个用户的财务数据完整性和同步性非常重要，需要外键支持，否则财务将会混乱，较为适合InnoDB。

二、字段类型选择

尽可能小（占用存储空间少）、尽可能定长（占用存储空间固定）、尽可能使用整数

三、范式与逆范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。

第一范式1NF，原子性,确保每列保持原子性,所有字段值都是不可分解的原子值

第二范式2NF，消除部分依赖,主键列与非主键列遵循完全函数依赖关系，确保表中的每列都和主键相关

第三范式3NF，消除传递依赖,非主键列之间没有传递函数依赖关系索引，确保每列都和主键列直接相关,而不是间接相关

逆范式是指打破范式，通过增加冗余或重复的数据来提高数据库的性能

四、索引

普通索引（key）：对关键字没有限制

唯一索引（unique key）：要求记录提供的关键字不能重复

主键索引（primary key）：要求关键字唯一且不为null

全文索引（fulltext key）：不支持中文类型

建表时创建字段索引，不宜太多，一般建在where，order by，join字段 后面

也可以根据业务逻辑建立 复合索引 index(first_name,last_name)

单表3个左右最佳（因为索引影响插入效率）

注意索引失效场景 如：

like语句不要在关键字前模糊查询 即 '%name%' 而是 'name%'

or关键字两边都要有索引，只有一边的话会索引失效

索引的存储结构

btree（多路平衡查找树） b+tree（聚簇结构）

在MySQL中，仅仅只有Innodb的==主键索引为聚簇结构==，其它的索引包括Innodb的非主键索引都是典型的BTree结构。

五、查询缓存query_cache（缓存select语句的查询结果）

使用Redis MongDb MemCache  HBase 做缓存

1.开启查询缓存

windows上是my.ini，linux上是my.cnf

在[mysqld]段中配置query_cache_type：

1：开启，默认缓存所有，需要在SQL语句中增加select sql-no-cache提示来放弃缓存

2.设置缓存大小

set global query_cache_size=64*1024*1024;

3.将查询缓存

select sql_cache * from table;

六、分区分表（了解）

分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

分区，partition，分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件

七、数据库连接池

C3p0 Hikai Druid

八、服务器架构方面

主从复制：

Mysql服务器内部支持复制功能，仅仅需要通过配置完成下面的拓扑结构。一主多从典型结果：主服务器负责写数据。从服务器负责读数据。复制功能mysql会自带。

读写分离:

读写分离是依赖于主从复制，而主从复制又是为读写分离服务的

负载均衡算法是指，客户端读操作时，该服务器会根据取余算法去选择一台从服务器

九、SQL书写优化

1.对于并发性的SQL

少用（不用）多表操作（子查询，联合查询），而是将复杂的SQL拆分多次执行。如果查询很原子（很小），会增加查询缓存的利用率。

2.大量数据的插入

多条 insert或者Load data into table（从文件里载入数据到表里）

建议，先关闭约束及索引，完成数据插入，再重新生成索引及约束。

十、执行计划explain（人为主动分析）

可以通过explain selelct来分析SQL语句执行前的执行计划：

十一、慢查询日志的使用

定位执行较慢的查询语句方案。

show variables like ‘slov_query_log’   查看是否开启,如果状态值为OFF

可以使用set GLOBAL slow_query_log = on来开启

它会在datadir下产生一个xxx-slow.log的文件。

show VARIABLES like 'long_query_time' 查看 设置临界时间