Mysql的一些简单优化

概述

1. 表的设计要合理化，符合三范式（3FN）
2. 添加适当的索引
3. 分表技术（水平分割、垂直分割）
4. 存储过程（数据库三层结构）
5. mysql配置的优化（最大并法术。缓存大小myini）
6. mysql硬件升级
7. 定时清除不需要的数据。定时警醒碎片整理

优化一： 表的设计

满足三范式：
1. 一范式： 原子性-》表的列不能再分割。
关系型数据库自动那个满足1NF.
2. 二范式：表中的记录是唯一的。就是满足2NF(设置主键)
3. 三范式：表中没有冗余数据（表中具有数据能够根据其他信息推断出来）
【也有例外，比如在相册包裹小相册的时候 ，相册总浏览数可以设计出来减轻数据库压力】

优化二　ＳＱＬ语句的优化

指令： show status

常用：
    show status like 'uptime';//启动多长时间
    show status like 'com_select';//多少次查询
    show status like 'com_insert';//插入操作
    show status like 'com_delete'；
    show status like 'com_update';


    show[session|global] status like 'com_+';[默认是session]

    show status like 'connections';//连接次数

定位慢查询。

    show status like 'slow_queries';//显示慢查询

    show variables like 'long_query_time';//显示慢查询的时间是多少
    set long_query_time = 1;//设置慢查询时间为1秒

    【默认情况下不会记录慢查询日志】
    需要指定
    bin\mysqld.exe--safe-mode --slow-query-log
    [5.5后可以在my.ini配置文件中指定]
    bin\mysqld.exe-log -slow-queries = d:/abc.log
    [5.0低版本在my.ini指定]

    重启mysql，文件放在与data文件同级的地方


    在日志里查找慢查询记录

优化三： 适当的索引

*可以使用explain语句分析*（不真正执行sql，只得到信息）

mysql> explain select * from hehe\G;
*************************** 1. row ***************************
           id: 1              -查询序列号
  select_type: SIMPLE         -查询类型
        table: hehe           -查询的表
         type: ALL            -扫面的方式
possible_keys: NULL           -可能用到的索引
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: NULL
mysql> explain select uid  from hehe where uid = 11 \G;
*************************** 1. row ***************************
           id: 1                     -查询序列号
  select_type: SIMPLE                  -查询类型
        table: hehe                    -查询的表
         type: const                  -扫面的方式
possible_keys: PRIMARY                -可能用到的索引
          key: PRIMARY                -实际用到的索引
      key_len: 4                      -索引长度
          ref: const
         rows: 1                      -扫描的行数
        Extra: Using index            -额外信息比如排序方式
1 row in set (0.00 sec)

---------------
.frm -结构
.NYD -数据
.MYI -索引
------------------------------------------------------------------

注意： 执行过一一语句后 ，会缓存
       下次查询同样的sql语句会非常快

添加索引
“`
四种（主键、唯一、全文、普通）

|主键：

create table a(
id int unsigned primary key
);

如果已经建表：
alter table a add primary key(id);

|查询
desc table_name 缺点：索引名字不显示
show index from table_name\G; [good]
show keys from table_name\G;[good]

|普通索引
create index index_name on table(column);
create index index_h on hehe(id);

|全文索引
create table hehe(
FullText(column,…)
) engine = myisam characterset = utf8;

查询的时候必须：（否则不使用索引）
select * from hehe where match(xxx,xxx) against(‘文字’)；

Notes：
-必须是myisam引擎
-针对英文有效
-使用方法：math（字段名） aginst（’关键字’）
-全文索引（停止词 常用的词。字符不会建立索引）

select match(字段) aginst （‘关键词’）；//显示命中概率

|唯一索引
create table hehe(
id int,
name char(10) unique
);
字段可以为null，可以有多个，但是空串不能为多个‘’

---

|添加索引 alter table heeh add primary key(id);
|添加索引 alter unique index index_name on table_name(column);
|删除索引 alter table hehe drop index index_name;
|删除主键 alter table hehe drop primary key;

---

索引的使用注意点

代价
1. 磁盘占用
2. 对dml（update delete insert）语句效率变差（要维护索引文件）

引擎	允许的索引类型
myisam	BTREE
innodb	BTREE
MEMORY\HEAP	HASH,BTREE

==那些地方需要使用索引==
1. 比较频繁作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合建立索引
3. 更新非常频繁的字段不适合做索引
4. 不会出现在where 子句字段不该创建索引

---

2

使用索引时的注意点（engine 是myisam的时候）

1表中有复合索引的时候

alter table hehe add index my_index (name,text);//建立复合索引
只要查询条件使用了最左边name ，一般就会使用索引

2模糊查询的时候

select* from hehe where id like '%h%';
当左边有%时候 不会使用索引（全文索引解决）

3.如果查询条件有or（其中一个条件没有索引，都不使用索引）

4.如果是字符串类型一定要使用‘’ ，否则不使用索引

5.如果mysql全表扫描比使用索引快，则不使用索引

---

索引的使用情况

show status like 'Handler_read%'
值越大。说明索引的使用率越高
handler_read_key 越高越好
handler_read_next 越高效率越低

常用优化

当一张表导入另一张表（大量插入数据时），最好先禁用索引

对于myISAM:

alter table name disable keys;
loading data/insert语句
alter table_name enable keys;

对于innodb：

1.将要导入的数据按照主键排序
2.set unique_checks=0,关闭唯一性校验
3.set autocommit=0,关闭自动提交

### 优化group by 语句

会对重复的信息进行排序 ，降低性能
select *from table_name group by column order by null;

### 尽量使用join代替子查询，因为子查询会先在内存中创建临时表

select * from dept left join emp om dept.id = emp.id;

   ~~select *from dept,emp wherer dept.id= emp.id;~~ 

选择合适的存储引擎

MYISAM	INNODB	MEMORY
表对于事务的要求不高，同时是以查询与添加为主的，比如bbs中的发帖表和恢复表	对事务要求高，保存的数据都是重要数据，比如订单表，帐号表	比如数据变化频繁。不需要入库，同时又频繁的查询与修改，我们考虑使用memory，速度极快。（内存中的）

INNODB与MYISAM的区别

NAME	INNODB	MYISAM
事务安全	安全	不安全
查询与添加的速度	慢	快
支持全文索引	不支持	支持
锁机制	行读	表读
外键	支持	不支持

### 合适的数据类型,碎片清理
1. 整形替代浮点
2. 数据库引擎myisam需要定时进行碎片整理 optimize table;


### 自动备份数据库
定时器

### 表的水平分割
海量数据需要 分割成多个表
可以这样分 用户id %3的值
--------------
再提供检索功能的时候
‘应该根据业务需求要找到分表的标准 
并在检索页面约束检索的需求

垂直分割：

例如 考试表中问题和回答太长应该单独一张表

id	status	question_id	answer(text)	greade
1	1	20	20	90

问题表

id	question
20	xxxxxxxxx

答案表

id	answer
20	dguiwgfiulewgfuigsdauft89weqyhfi0

配置文件的修改

INNOdb
INNODBAddtional_sql_poll_size = 64M
INNODB_BUFFER_POOL_Size=1G

MYISAM
调整KEY_BUFfer_size( show variables like ‘key_BUFFEr_size’; )

读写分离

压力过大使用 多个数据库

主

slave1 slave2 slave3..

MYSQL的增量备份

以二进制的形式，自动用户对mysql数据库的操作记录到文件。当用户希望恢复的时候可以使用备份文件进行恢复

=>增量备份会记录dml语句穿件表的语句 但是不会记录select语句
-》记录操作语句本身、操作时间、postion

``` 
步骤： 
 配置my.ini文件或者myconf启用二进制备份
 #指定备份文件放在哪里？
 log-bin=d:/binlog/mylog

 重启mysql

 工具mysqlbinlog ->查看二进制文件
 根据时间戳和position来恢复

 drop tables trmp;
 commit;

恢复：
时间： mysqlbinlog --stop-datetime = '2013-01-04 hh:mm:ss' D;//xsdjhda |mysql -uroot -pxxx
位置： mysqlbinlog --stop-position = '110' D://dsadiu.log|mysql -uroot -pxxx


注意：只记录当天的记录
linux 中有个指令 reset master

-expire_logs_date..过期时间

---

全备份 ，与增量备配合使用

方案：每周一做一个全备份，mysqldump启动增量备份，把过期的时间设置为大于等于7