mysql优化

一、mysql逻辑架构

• MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。
• MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
• 最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

为了获得更高的查询性能，需要弄清mysql是如何优化和执行查询，实际上很多的查询工作就是遵循一些原则让mysql的优化器能够按照预想的合理方式运行。

mysql请求解析

二、优化层面

1. 表设计层面

• 结构、字段类型是否满足范式，选择那种存储引擎。

常用表引擎

引擎	存储顺序	对锁的支持	并发性	性能
MyISAM	顺序插入	表级	较差	适合大量的查询和插入操作
InnoDB	主键顺序	表级、行级	较好	高效的删除更新，支持事务，外键
memory				速度最快，不支持持久化，不支持text,blob
archive				存档型，仅提供高效插入和读操作，日志型

• InnoDB 数据和索引集中存储，为现在的版本默认引擎，支持事务，外键。
• MyISAM 数据和索引分开存储，插入速度快，但数据被删除后会留下记录空洞。
  可以使用repair table table_name进行修复

范式（维护便捷，数据冗余少）

• 第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。（确保每一列的原子性）
• 第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。（消除部分依赖）
• 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF。（消除传递依赖）

字段类型选择

• 满足功能条件下，尽可能短，占用少的内存空间；
• 尽可能使用定长；char定长，varchar变长，double定长，decimal变长；
• 尽量使用整数（sql提供ip地址、数字互转函数，inet_aton()，inet_ntoa()）

2. 数据库功能层面

• 索引使用是否合理，分区分表，内部缓存

索引

• 尽量在查询字段，排序字段，关联字段建立索引
• like 不能以通配符开头（否则索引无法用到）
• 复合索引，左边的字段才可以使用索引（最左原则）

索引的数据结构

索引分为B-Tree，Hash

• B-Tree索引
• B+Tree索引，B-Tree索引的稍作修改
  其中，又以叶子上的存储不同分为聚簇索引和非聚簇索引
• Hash索引

聚簇索引与非聚簇索引

• 聚簇索引的顺序的顺序就是数据的物理存储顺序。所以一个表最多只能有一个聚簇索引，因为物理存储只能有一个顺序。（在该索引实现方式中B+Tree的叶子节点上的data就是数据本身，key为主键，如果是一般索引的话，data便会指向对应的主索引）
• 非聚簇索引的顺序与物理排列顺序无关。（非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。）
• 在SQL server中，索引是通过二叉树的数据结构来描述的，聚簇索引的叶子节点就是数据节点。而非聚簇索引的叶子节点仍然是索引节点，只不过有一个指针指向数据块。

3. 数据库服务器架构层面

• 读写分离，负载均衡，集群

4. 业务层面

• sql是否合理，符合当前的功能。