Mysql的一些简单优化
来源:互联网 发布:ubuntu视频下载软件 编辑:程序博客网 时间:2024/05/22 10:37
概述
- 表的设计要合理化,符合三范式(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: NULLmysql> 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)语句效率变差(要维护索引文件)
==那些地方需要使用索引==
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;~~
选择合适的存储引擎
INNODB与MYISAM的区别
### 合适的数据类型,碎片清理1. 整形替代浮点2. 数据库引擎myisam需要定时进行碎片整理 optimize table;### 自动备份数据库定时器### 表的水平分割海量数据需要 分割成多个表可以这样分 用户id %3的值--------------再提供检索功能的时候‘应该根据业务需求要找到分表的标准 并在检索页面约束检索的需求
垂直分割:
例如 考试表中问题和回答太长应该单独一张表
问题表
答案表
配置文件的修改
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
- Mysql的一些简单优化
- Mysql的一些简单优化
- mysql 优化 一些简单实用的原则
- MySQL简单的优化
- Mysql的简单优化
- oracle一些简单的优化
- android的一些简单优化
- 一些 Mysql 的优化经验
- 一些 Mysql 的优化经验
- 一些Mysql的优化经验
- 一些 Mysql 的优化经验
- MYSQL优化的一些思路
- mysql的一些配置优化
- MySQL优化的一些经验
- MySql的一些优化策略
- MySQL优化的一些建议
- 【mysql】MySQL中SQL优化和架构设计的一些简单想法
- MySQL 优化的简单原则
- 27-数组的本质
- 【差分+阶梯Nim游戏】BZOJ1115(POI2009)[石子游戏Kam]题解
- 【Spring】架构模块
- python导入循环问题
- Qt Designer中自定义控件的使用(提升法与插件法)
- Mysql的一些简单优化
- Qt 文件的新建、打开、保存、另存为 (改进版本)
- 如何用jquery获取文本框的值
- MediaCodec
- pyspark-Spark Streaming编程指南
- Android悬浮窗原理解析[源码]
- Spring Cloud构建微服务架构:服务消费(基础)【Dalston版】
- ros笔记:(5)使用 rqt_console 和 roslaunch
- JS &&和||返回值