概述
- 表的设计要合理化,符合三范式(3FN)
- 添加适当的索引
- 分表技术(水平分割、垂直分割)
- 存储过程(数据库三层结构)
- mysql配置的优化(最大并法术。缓存大小myini)
- mysql硬件升级
- 定时清除不需要的数据。定时警醒碎片整理
优化一: 表的设计
满足三范式:
1. 一范式: 原子性-》表的列不能再分割。
关系型数据库自动那个满足1NF.
2. 二范式:表中的记录是唯一的。就是满足2NF(设置主键)
3. 三范式:表中没有冗余数据(表中具有数据能够根据其他信息推断出来)
【也有例外,比如在相册包裹小相册的时候 ,相册总浏览数可以设计出来减轻数据库压力】
优化二 SQL语句的优化
指令: 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