MySQL数据库优化法则总结

MySQL数据库优化法则总结

一 : 要正确的优化SQL，我们需要快速定位能性的瓶颈点

1 计算机硬件指标

从上图可以看出，计算机系统硬件性能从高到代依次为：
CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘

从图上可以看到基本上每种设备都有两个指标：
延时（响应时间）：表示硬件的突发处理能力；
带宽（吞吐量）：代表硬件持续处理能力。

CPU及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算；

网络：结果数据传输、SQL请求、远程数据库访问（dblink）；

硬盘：数据访问、数据写入、日志记录、大数据量排序、大表连接。

2 表的设计合理化(符合3NF)

	1. 每个列必须有一个唯一的名称
	2. 行和列的次序无关紧要
	3. 每一列都必须有单个数据类型
	4. 不允许包含相同值的两行
	5. 每一列都必须包含一个单值 （一个列不能保存多个数据值）
	6. 列不能包含重复的组
	7. 所有的非键值字段均依赖于所有的键值字段
	8. 不包含传递相关性，不含冗余数据

3 优化SQL语句
（1）迅速的定位执行速度慢的语句

        a 开启慢查询
    	b 设置慢查询时间
    	c 启用慢查询日志
    	d 通过mysqldumoslow工具对慢日志进行分类汇总

（2）分析SQL语句

        a 通过explain分析查询 
    	b 通profiling可以得到更详细的信息

（3）SQL语句优化

        a 创建索引(主键索引/唯一索引/全文索引/普通索引)
    	b 避免Select * (不查询多余的列与行)
    	c Where中少用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE、OR，它们会忽略索引，引起全表扫描
    	d 用Where子句替代having子句,having只会在检索出所有记录之后才对结果集进行过滤
    	e 使用视图（经常被查询的列数据，并且这些数据不被经常的修改，删除）

我们一般在什么字段上建索引？
这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：

a 字段出现在查询条件中，并且查询条件可以使用索引；
b 语句执行频率高，一天会有几千次以上；
c 通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？

4 分表技术(水平分割、垂直分割)、分区技术
为什么要分表和分区？

如果遇到大表的情况下，SQL语句优化已经无法继续优化了，我们可以考虑分表和分区，目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表 的增删改查效率。

5 读写[写: update/delete/add]分离
<1>
大型网站为了缓解大量的并发访问
解决方法:
1 分布式负载均衡
2 采用优秀的代码框架
3 采用优秀的数据缓存技术如：memcached
4 架设服务器群,来分担主数据库的压力
大型网站为了缓解大量的并发访问，除了在网站实现分布式负载均衡，远远不够。如果还是传统的数据结构，或者只是单单靠一台服务器扛，如此多的数据库连接操作，数据库必然会崩溃，数据丢失的话，后果更是不堪设想。这时候，我们会考虑如何减少数据库的联接，一方面采用优秀的代码框架，进行代码的优化，采用优秀的数据缓存技术如：memcached,如果资金丰厚的话，必然会想到架设服务器群，来分担主数据库的压力
因此，一般来说都是通过主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy，是MySQL官方提供的MySQL中间件服务）来提升数据库的并发负载能力 这样的方案来进行部署与实施的

实现方式

第一种：php程序上自己做逻辑判断，写php代码的时候，自己在程序上做逻辑判读写匹配。select，insert、update、delete做正则匹配，根据结果选择写服务器（主服务器）。如果是select操作则选择读服务器（从服务器器） mysql_connect(‘读写的区分’)

第二种：MySQL中间件，基本的原理是让主数据库处理写操作（insert、update、delete），而从数据库处理查询操作（select）。而数据库的一致性则通过主从复制来实现。所以说主从复制是读写分离的基础。

6 存储过程
1）为什么需要存储过程

	a 数据不安全，网络传送SQL代码，容易被未授权者截获

	b 每次提交SQL代码都要经过语法编译后在执行，影响应用程序的运行性能

	c 网络流量大，对于反复执行的SQL代码，在网络上多次传送，影响网络传输量

（2）什么是存储过程

   存储过程是SQL语句和控制语句的预编译集合，保存在数据库中，可有应用程序调用执行，而且允许用户声明变量、逻辑控制语句及其他强大的编程功能。包含逻辑控制语句和数据操作语句，可以接收参数、输出参数、返回单个或多个结果值及返回值

（3）使用存储过程的优点

	a 模块化程序设计，只需创建一次，以后即可调用该存储过程任意次

	b 执行速度快，效率高

	c 减少网络流量

	d 具有良好的安全性

7 mysql服务器硬件升级

（1）磁盘

MySQL每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以，通常认为磁盘I/O是制约MySQL性能的最大因素之一

解决方案： 使用RAID-10 、磁盘阵列设备SAN

（2）CPU 对于MySQL应用，推荐使用S.M.P.架构的多路对称CPU

（3）内存 越大越好

（4）网卡 至少两个网卡，均为1GBE。通常我会将这两个nics绑定在一起以提供冗余
8、定时的去清除不需要的数据,定时进行碎片整理(月/年)
什么是磁盘碎片？

    简单的说,删除数据必然会在数据文件中造成不连续的空白空间,而当插入数据时,这些空白空间则会被利用起来.于是造成了数据的存储位置不连续,以及物理存储顺序与理论上的排序顺序不同,这种是数据碎片.实际上数据碎片分为两种,一种是单行数据碎片,另一种是多行数据碎片.前者的意思就是一行数据,被分成N个片段,存储在N个位置.后者的就是多行数据并未按照逻辑上的顺序排列.

    当有大量的删除和插入操作时,必然会产生很多未使用的空白空间,这些空间就是多出来的额外空间.索引也是文件数据,所以也会产生索引碎片,理由同上,大概就是顺序紊乱的问题.Engine 不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引。这样不但会浪费空间，并且查询速度也更慢。

解决方案：

（1）查看表碎片的方法

select ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE from TABLES where TABLE_SCHEMA=‘test_db’ and TABLE_NAME=‘table_name’ limit 1;

（2）Innodb存储引擎清理碎片方法

ALTER TABLE tablename ENGINE=InnoDB

（3）Myisam存储引擎清理碎片方法

OPTIMIZE TABLE table_name

切记，一定要在夜里执行，表越大，越耗资源时间，不要频繁修复，可以几个月甚至一年修复一次，如果表频繁被更改，可以做个计划任务，按周/月来整理。