mysql优化笔记 整理版_mysql优化笔记

【mysql优化部分】

优化大致思路：

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

b. 添加适当的索引(index)

mysql的索引大致分为四类：

普通索引、主键索引、唯一索引、全文索引

c. 分表技术(水平分割、垂直分割)

d. 读写分离(读 select 写 insert/delete/update)

e. 存储过程(模块化编程，可以提高速度)

f. 对MySQL的配置优化(如 最大并发数 max_connections等)

g. MySQL服务器硬件升级

h. 定时清除不必要的数据 定时进行碎片整理(尤其是myisam存储引擎)

【一、表的设计合理化】

1、表的设计原则

1NF：表的列属性不可分割。(关系型数据库都满足，不必考虑)

2NF：表中的记录唯一。(通过设置主键来实现)

主键一般不含业务逻辑，自增长

3NF：表中不含冗余数据(表的某些字段能被推导出来，

就不应该单独设计字段来存放他们)

有时候会用到反3NF的字段设计表。

例如：

[分类表]：id view

[详细表]：id view name cid

其中[分类表]中的浏览量要通过计算[详细表]中的浏览量得来

那么我们可以，在[分类表]中也加一个view字段，

在更新[详细表]的时候，也增加[分类表]中的view值，那么提取的

时候可以免于计算，提高查询效率。

总的来说，需要计算得出的字段，尽量不要在查询的时候

进行计算，将他们改在更新或插入的时候作为一个字段计算好。

2、字段的设计原则(保短不保长)

能用tinyint 就不要用 int

如果能用char 就不要用varchar

能用varchar 就不要用 text

...

总而言之，就是尽量使用合适的字段类型设计表的字段。

【二、选择合适的存储引擎】

1、选取原则

myisam：表对事务的要求不高，主要以查询和添加修改为主，

考虑使用此引擎(如评价表等)。

innodb：对事务的要求高，保存的都是重要数据，建议使用

此引擎(如订单表、账号表)。

memory：数据变化频繁，不需要入库。同时频繁的查询和修改，

考虑使用此引擎(如用户的登录状态等)。

注：memory数据存在内存中，重启mysql会丢失。

2、myisam与innodb的区别

① 事务安全(innodb)

② 查询和添加速度(myisam)

③ 支持全文索引(myisam)

④ 锁机制(innodb)

⑤ 外键(innodb)

3、大量数据写入

① 对于myisam，关闭索引

alter table table_name disable keys;

插入加载数据

alter table table_name enable keys;

因为创建表的时候会自动创建索引，这样负载会加大

② 对于innodb

将要插入的数据按主键进行排序

set unique_checks=0;#关闭唯一索引(唯一性检查影响效率)

set autocommit=0; #关闭自动提交

【三、建立合适的索引】

四种索引的使用(主键、唯一、全文、普通索引)

1、主键索引

添加索引 alter table art add primary key(id);

删除索引 alter table art drop primary key;

2、唯一索引

表的某一列被指定为unique 关键字是时 即为唯一索引

唯一索引允许为null 和 ''

但是可以允许多个null值存在，不能有多个''(空串)存在

create unique index 索引名 on 表名 (列名1,...)

3、全文索引

在创建表的时候创建

create table art(

id int primary key,

title varchar(20),

body text,

FULL TEXT(title,body)

) engine=myisam charset utf8;

注意：

① 全文索引只支持myisam引擎

② mysql 系统提供的全文索引，只支持英文，不支持中文

如果要支持中文的话，需要下载sphinx插件

③ 全文索引有一个停止词，在一篇文章中，创建全文索引是一个

无穷大的数，所以只会给不常见的词创建全文索引。

④ 使用全文索引必须遵循使用规则 match() against();

select * from art where match(title,body) against('daye');

4、普通索引

create index 索引名 on 表名(列名);

alter table 表名 add index 索引名(列名);

删除索引: alter table 表名 drop index 索引名

5、索引的查询

① 表结构查询

desc 表名;

② 查询单个索引

select index(索引名) from 表名\G

③ 查询表的所有索引

show keys from 表名\G

④ 查看索引的使用情况

show status like 'handler_read%'

handler_read_key      高 说明索引使用率高

handler_read_rnd_next 高 说明查询效率低

6、索引的使用原则

① 创建了多列的索引，只有最左侧的列被使用时，索引才会被使用

② 使用like 关键字进行查询时，开头不能有通配符'％'、'_'等

否则不会使用索引

③ 条件中含or关键字 不会使用索引

7、索引添加原则 与 优缺点

①优点 查询速度快  使用二叉树log2n次查询

②缺点 占用磁盘空间

对dml语句(非查询语句) 频繁操作的表 会导致速度变慢

③添加原则

添加where子句中频繁使用到的字段为索引

唯一性太差的字段不适合单独做索引

【四、表的分割技术】

1、水平分割

即将一个表复制成多张表 结构不变

原则：

① 表结构不变

② 应根据业务的需求，找到分表的标准，并在检索页面

约束用户权限。

[例] 一张qq登录表，几亿条数据 qqlogin

我们根据用户 id%3 的余数决定将用户存入哪一张表

uuid(自动生成用户id)

qqlogin0(存入id求余结果为0的用户)

qqlogin1(存入id求余结果为1的用户)

qqlogin2(存入id求余结果为2的用户)

2、垂直分割

即将一张表中 常用 和不常用的字段分离出来，组成两张不同表

原则：

① 将表中不常用的字段分离出来

② 将表中数据量较大，会影响查询速度的表分离出来

③ 注意分离表与原表的关联关系

【五、读写分离】

1、表的主从复制

insert  into tab1

select col1 col2 ... from tab2;

【六、主从复制】

(略) 详细后面章节进行讲解

【七、定位慢查询sql】

(注意：这里慢查询不一定只指select语句，其它语句执行速度

比较慢的也叫慢查询)

SQL优化一般思路：

1、通过show status 命令了解各种sql执行的效率

2、定位执行效率较低的sql语句

3、通过explain 分析低效率sql语句的执行情况

4、确定问题采取相应的措施

1、通过show status 命令了解各种sql执行的效率

show [session|global] status like '%%';

其中：session为当前的会话窗口统计。默认项

global 则为所有会话窗口统计。

① mysql的运行时间：

show status like 'uptime';

② 一共执行的次数：

select: show status like 'com_select';

update: show status like 'com_update';

insert: show status like 'com_insert';

delete: show status like 'com_delete';

③ 当前连接数

show status like 'connections';

④ 显示慢查询次数

show status like 'slow_queries';

2、定位执行效率较低的sql语句

我们要通过以下几步定位慢查询sql语句：

① 关闭mysql服务

在windows下，打开"服务"，找到mysql，关闭服务

在Linux下，直接找到mysqld 进程，kill掉

② 命令行进入mysql的安装目录 输入

版本5.5及以后

bin\mysqld.exe --safe-mode --slow-query-log

版本5.0及以前

bin\mysqld.exe -log-slow-queries=d:/ab.log

回车

③ 再次进入mysql命令行模式 更改慢查询设定的时间限制为1s

set long_query_time = 1;

④ 此时慢查询日志已开启

记录地址在：my.ini 中的datadir所指的目录中

3、通过explain 分析低效率sql语句的执行情况

mysql> explain select * from emp where empno = 345680\G

*************************** 1. row ***************************

id: 1        #查询序列号

select_type: SIMPLE   #查询类型

PRIMARY/

table: emp      #查询的表名

type: ALL      #扫描方式 ALL(全表扫描，尽量避免)

SYSTEM 表仅有一行

CONST  表匹配到的仅有一行

possible_keys: NULL     #表中可能使用到的索引

key: NULL     #实际使用的索引

key_len: NULL

ref: NULL

rows: 4000000     #该sql语句扫描了多少行，可能得到记录数

Extra: Using where #额外信息 比如排序方式 如filesort等

1 row in set (0.00 sec)

4、确定问题采取相应的措施

优化sql语句

① 优化group by 语句

使用group by子句后 系统会默认进行排序

如果不需要进行排序，则建议加上 order by null

② 使用连接 join 代替子查询

【八、碎片整理】

针对myisam引擎进行碎片整理

//对指定的表进行碎片整理

mysql> optimize table table_name;

【九、备份/还原】

1、PHP定时完成数据库备份

① 手动备份 命令

备份数据库

# mysqldump -uroot -psa 数据库名  > /文件路径

备份表

#mysqldump -uroot -psa 数据库名.表1 数据库名.表2... > 文件路径

② 手动数据 恢复

mysql> source 备份文件路径

2、定时任务

① windows 批处理 (任务管理器)

(1)建立 .bat 批处理文件 (如 my.bat )

找到mysql文件的安装的bin 目录 复制文件路径

在my.bat中加入如下语句

D:\phpservice\mysql\bin\mysqldump -uroot -psa demp > d:demp.bak.sql

(2)将my.bat 文件加入定时任务

控制面板 -> 管理工具 -> 任务计划程序 -> 操作 -> 创建任务 ->

操作   中导入任务

触发器 中新建设置触发时间

常规   中设置任务名称

条件   中设置任务时间

设置   中设置任务的相关条件

(3) 设置好以后，就会按时触发了

② linux  crontab -e 计划任务

这个更简单

直接将上面写入的脚本路径 与 程序路径 更改一下就ok

3、PHP实现定时数据库备份

//设置时区

date_default_timezone_set("PRC");

//设置文件名

$bakfile_path = date('Y-m-d H:i:s', time());

//拼装命令

$commond = "D:\phpservice\mysql\bin\mysqldump -uroot -psa demp > d:{$bakfile_path}.bak.sql";

//执行命令

exec($commond);

?>

4、mysql的增量备份

mysql数据库会以二进制的形式，将mysql对数据库的操作，记录到文件

当用户希望恢复的时候，可以使用该文件进行备份恢复。

增量备份原理

① 记录dml语句(不含查询语句)

② 记录 a. 操作语句本身

b. 操作时间

c. 操作position

如何进行增量备份/与恢复

(1) 配置mysql.ini 启用二进制的备份

在[mysqld] 下增加语句

log-bin = d:\binlog\mylog

(2) 重启mysql服务 (这一步很关键)

windows 下 在服务里找到 mysql 并重启

linux 下 restart mysql 进程

(3) 查看mysql的日志

找到日志文件的位置

使用命令:

# mysqlbinlog 日志文件路径

日志分析

a. end_log_pos 日志文件中操作 所处的位置

b. TIMESTAMP 操作所处的时间点

c. 根据上面两点进行数据库的恢复

(4) 按时间戳/位置恢复

按时间恢复

# mysqlbinlog --stop-datetime="2015-01-14 18:23:43" d:\binlog\mylog000001 | mysql -uroot -psa

按位置恢复

# mysqlbinlog --start-position="112"  d:\binlog\mylog000001 | mysql -uroot -psa

【十、配置优化】

1、端口号更改

如果要设定多个mysql 在同一服务器上使用，需要更改端口号

如果不使用3306，则需要在mysql_connect连接函数使用的时候带上

端口号

2、更改最大连接数(mysql.ini/mysql.conf)

max_connections = 100 (最佳范围100-1000)

3、开启查询缓存

query_cache_size = 15M

4、针对不同的引擎设置不同的缓存大小

myisam ---> key_buffer_size

innodb ---> innodb_additonal_new_pool_size = 64M

innodb_buffer_pool_size = 1G

5、如果服务器内存超过4G，可考虑使用64位操作系统和64位mysql服务器