MySQL数据库优化

mysql优化主要分为四个大方面：
设计：存储引擎，字段类型，范式与逆范式
功能：索引，缓存，分区分表
架构：主从复制，读写分离，负载均衡
合理SQL：测试，经验

一、存储引擎

在创建表的时候使用sql语句就可以指明了存储引擎是myisam还是innodb，存储引擎是一种用来存储mysql中对象（记录和索引）的一种特定的结构（文件结构），存储引擎就是特定的数据存储格式（方案）。
InNoDB：
mysql>=5.5默认存储引擎是InNoDB，支持事务，行级锁定，外键约束。是事务安全型存储引擎。更加注重数据的完整性和安全性。
总结：InNoDB擅长事务，数据的完整性及高并发处理，不擅长快速插入（插入前要排序，消耗时间）和检索。
MyISAM:
mysql<=5.5时的默认存储引擎。
ISAM:Indexed Sequential Access Method（索引顺序存取方法）的缩写，是一种文件系统。擅长与处理，高速读与写。

关于InNoDB和myisam的取舍：
Innodb:数据完整性，并发性处理，擅长更新，删除。
myisam:高速查询及插入，擅长插入和查询。

具体举例：
①对于微博项目来看，选择哪一个存储引擎呢？
a.微博主要是插入微博和查询微博列表，较为适合myisam
b.微博在更新和删除微博，要少得多，较为适合myisam
c.对数据的完整性需求并没有那么强烈，比如用户删除微博，关联的转播和评论并不要求都做相应的行为，较为适合myisam
②对于记账财务系统，选择哪一个存储引擎呢？
a.记账财务系统除了读取和插入，经常要进行数据的修改和删除，较为适合InNoDB
b.在进行财务变更的时候，如果失败需要回滚必须用到事务，较为适合InNoDB
c.每个用户的财务数据完整性和同步性非常重要，需要外键支持，否则财务将会混乱，较为适合InNoDB

其他存储引擎：
（1）Archive：存档型，仅提供插入和查询操作。非常高效阻塞的插入和查询
（2）Memory：内存型，数据存储于内存中，存储引擎。缓存型存储引擎。
（3）插件式存储引擎：用C和C++开发的存储引擎。

锁的概念：
当客户端操作表（记录）时，为了保证操作单额隔离性（多个客户端操作不能互相影响），通过加锁来处理。
操作方面：
读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是阻塞其他客户端的写操作，不阻塞读操作。（并发读）
写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征是阻塞其他客户端的读，写操作。
锁定粒度（范围）：
行级锁：提升并发性，锁本身开销大
表级锁：不利用并发性，锁本身开销小

二、字段类型选择

字段类型选择尽量满足以下需求：尽可能小（占用存储空间少）、尽可能定长（占用存储空间固定）、尽可能使用整数。
就比如：
（1）使用datetime，当然也可以用int(11)来保存时间戳，并且优点如下：
a.INT占四个字节，DATETIME占8个字节
b.INT存储索引的空间比DATETIME小，查询快，排序效率高
c.在计算机时间差等范围问题，比较方便
（2）char和varchar，char是保存定长的字符串，而varchar是保存变长字符串，char(5)表示必须保存5个字符，而varchar则表示最大保存字符为5.由于char定长，mysql会根据定义的长度进行分配空间，在处理速度上比varchar快的多，所以适合存储例如手机、身份证这种定长的字符，否则就会造成浪费。
总结：短文本定长用char，变长用varchar，长文本用text

三、范式与逆范式

为了建立冗余较小，结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
第一范式1NF：原子性
第二范式2NF：消除部分依赖
第三范式3NF：消除传递依赖

1、范式
（1）第一范式：具有原子性，确保每列保持原子性
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便，这样设计才算满足了数据库的第一范式。
（2）第二范式：主键列与非主键列遵循完全函数依赖关系，确保表中的每列都和主键相关
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
（3）第三范式：非主键列之间没有传递函数依赖关系索引，确保每列都和主键列直接相关，而不是间接相关。
所谓传递函数依赖，指的是如果存在“A&rarr；B&rarr；C”的决定关系，则C传递函数依赖于A，因此，满足第三范式的数据库表应该不存在如下依赖关系：
关键字段&rarr；非关键字段x&rarr；非关键字段y
比如设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系，而不可以在订单表中添加关于客户的其他信息（比如姓名、所属公司等）的字段。
先满足第一范式，再满足第二范式，才能满足第三范式。

2、逆范式
逆范式是指打破范式，通过增加冗余或重复的数据来提高数据库的性能。
比如一个商品表和一个分类表，需要查询一些数据时，可能要进行左连接，sql语句也较长，此时如果数据量较大，就比较消耗性能了，可以考虑在原来数据库表中增加字段，就可以直接查询。

四、索引

1、索引概述
利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。索引的关键字一定是排序的。索引本质上是表字段的有序子集，是提高查询速度最有效的方法。一个没有建立任何索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询效率极低、速度也极慢、如果添加索引，就好比一本添加目录的书，通过目录的指引，迅速翻阅到指定的章节，提升查询性能，节约了查询资源。

2、索引的种类：
从索引的定义方式和用途中来看：主键索引，唯一索引，普通索引，全文索引。
无论任何类型，都是通过建立关键字与位置的对应关系来实现的。索引是通过关键字找对应的记录的地址。
以上类型的差异：对索引关键字的要求不同。
关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）。
普通索引，index：对关键字没有要求
唯一索引，unique index：要求关键字不能重复。同时增加唯一约束
主键索引，primary key：要求关键字不能重复，也不能为NULL。同时增加主键约束。
全文索引，fulltext key：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。
主键索引和唯一索引的区别：
主键索引不能为空值，唯一索引允许为空值
主键索引在一张表内只能创建一个，唯一索引可以创建多个
主键索引肯定是唯一索引，但唯一索引不一定是主键索引。

索引使用归纳：
a、不要过度索引。索引越多，占用空间越大，反而性能变慢
b、只对where子句中频繁使用的建立索引
c、尽可能使用唯一索引，重复值越少，索引效果越强
d、使用短索引，如果char（255）太大，应该给它指定一个前缀长度，大部分情况下前10位或20位值基本是唯一的，那么就不要对整个列进行索引
e、充分利用左前缀，这是针对复合索引，因为where语句如果有and并列，只能识别一个索引（获取记录最少的那个），索引需要使用复合索引，那么应该将where最频繁的放置在左边。
f、索引存在，如果没有满足使用原则，也会导致索引无效

3、索引使用场景
（1）索引检索：检索数据时使用索引
（2）索引排序：如果order by排序需要的字段上存在索引，则可能使用到索引
（3）索引覆盖：索引拥有关键字内容，覆盖了查询所需要的全部数据，此时，就不需要在数据区获取数据，仅仅在索引区即可。覆盖就是直接在索引区获取内容，而不需要在数据区获取。

4、前缀索引
前缀索引是建立索引关键字一种方案。通常会使用字段的整体作为索引关键字。有时，即使使用字段前部分数据，也可以去识别某些记录。就比如一个班级里，我要找王××，假如姓王的只有一个人，就可以建立一个前缀索引，就是王。

5、全文索引
该类索引特殊在是为了解决like‘%keyword%’这类查询的匹配问题。（mysql的全文索引几乎不用，因为它不支持中文，我们应该使用sphinx全文索引）。

6、索引结构-b-tree（B树）介绍
Hash、B-Tree（B树）两种数据结构。指的是mysql存储引擎所采用的数据结构。其中，用户所维护的所有的索引结构B-Tree结构。
B-Tree的结构：每个节点存储多个关键字。关键字也会对应记录地址，该设计时为了解决一次性磁盘IO开销，可以读取到更多的关键字数量。每个关键字之间，存在子节点指针。如果是复合索引：关键字的排序先排左侧字段，在左侧字段相同的情况下，再排序右侧字段。

7、聚集索引（聚簇索引）
B+Tree（B-Tree的变种）
在InnoDB的存储引擎上，主键索引是与数据记录存储在一起的（聚簇在一起的）
带来的问题：InnoDB的其他索引，非主键索引（二级索引）：关键字对应的不再是记录的地址，而是记录的主键。所以，检索需要二次检索。先检索到主键ID，再检索记录。

五、查询缓存query_cache

将select的结果，存取起来供二次使用的缓存区域：

六、分区分表

日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级别乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及到联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的怎删改查效率。
分区，partition，分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

其实每个分区，就是独立的表，都要存储该分区数据的数据，索引等信息。
分区与存储引擎无关，是mysql逻辑层完成的。
1、分区算法
mysql提供4种分区算法：取余:key，hash，条件：List，range。
参与分区的参数字段需要为主键的一部分。
2、分区的管理与选择
（1）取余：key,hash
增加分区数量：add partition partitions N
减少分区数量：coalesce partition N
采用取余算法的分区数量的修改，不会导致已有分区数据的丢失，因为会重新分配数据到新的分区。
（2）条件：list, range
删除条件算法的分区，会导致分区数据丢失。添加分区不会。
（3）选择分区算法
平均分配：就按照主键进行key（primary key）即可（非常常见）
按照某种业务逻辑分区：选择那种最容易被筛选的字段，整数型
3、分表
分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间union关系。（需要手动分表）
分表是分区之前用的，MYSQL5.1后，就开始用分区代替分表了。分表很少用了。
（1）水平分表
创建结构相同的N个表；
（2）垂直分表
一张表中存在多个字段，这些字段可以分为常用字段和非常用字段，为了提高查表速度，我们可以把这两类字段分开来存储。主要目的，减少每条记录的长度。通常我们按照以下原则进行垂直拆分：把不常用的字段单独放在一张表；把text，blog等大字段拆分出来放在附表中；经常组合查询的列放在一张表中；

七、服务器架构介绍

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

读写分离，负载均衡：
PHP不再操作mysql数据库服务器，而是去操作读写分离、负载均衡服务器，只要服务器安装了mysql proxy或Ameoba软件就可以实现读写分离和负载均衡，读写分离是指该服务器会判断客户端的操作时读还是写，从而选择操作mysql主服务器还是从服务器。负载均衡算法是指，客户端读操作时，该服务器会根据取余算法去选择一台从服务器。

八、SQL优化

1、对于并发性的SQL
少用（不用）多表操作（子查询，联合查询），而是将复杂的SQL拆分多次执行。如果查询很原子（很小），会增加查询缓存的利用率。
2、大量数据的插入
多条insert或者Load data into table（从文件里载入数据到表里）建议，先关闭约束及索引，完成数据插入，再重新生成索引及约束。

针对myisam，步骤：
Alter table table_name disable keys;禁用索引约束
大量的插入
Alter table table_name enable keys;启用

针对innodb，步骤：
Drop index ,drop constraint 删除索引及约束，要保留主键
Begin transaction|set autocommit = 0;开启事务，不让他自动提交
[数据本身已经安装主键值排序]
大量的插入
Commit;
Add index,add constraint