MySQL数据库的优化

mtain 2016年04月04日 120次浏览

MySQL数据库的优化
1.数据库设计
数据表符合3NF 3范式,有时也需要逆范式
2.sql语句优化
索引等
3.数据库参数配置
缓存、内存大小设置
4.恰当的硬件和操作系统
读写分离

====================数据库设计====================
3NF
1NF :就是具有原子性,不可分割.(只要使用的是关系性数据库,就自动符合)
2NF: 在满足1NF 的基础上,我们考虑是否满足2NF: 只要表的记录满足唯一性,也是说,你的同一张表,不可能出现完全相同的记录, 一般说我们在 表中设计一个主键即可.
3NF: 在满足2NF 的基础上,我们考虑是否满足3NF:即我们的字段信息可以通过关联的关系,派生即可.(通常我们通过外键来处理)
逆范式:依照具体问题,冗余一定的数据

====================sql语句优化====================
sql语句分类:
ddl (数据定义语言) [create alter drop]
dml(数据操作语言)[insert delete upate ]
select
dtl(数据事务语句) [commit rollback savepoint]
dcl(数据控制语句) [grant  revoke]

show status命令
该命令可以显示你的mysql数据库的当前状态.我们主要关心的是 “com”开头的指令
show status like ‘Com%’  <=>
show session status like ’Com%’;  //显示当前控制台的情况
show global status like ’Com%’; //显示数据库从启动到查询的次数
如:
show session status like ’Com_select’;

显示连接数据库次数
show status like  ’Connections’;

显示MySQL启动时间-单位为秒
show status like ’uptime’;

显示查看慢查询的情况
show status like ’long_query_time’;

慢查询(在默认情况下是10秒以上的查询)
显示慢查询设置
show variables like ’long_query_time’;

MySQL启动慢查询日志记录的MySQL启动方式 xx>binmysqld.exe ?slow-query-log
设置慢查询时间
set long_query_time=1;

可在my.ini文件配置慢查询日志路径和时间

#--------------------------------------------存储过程示例开始--------------------------------------------#
CREATE DATABASE  `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

#修改命令结束符
delimiter $$

#自定义生成随机字符的存储过程
create function rand_string(n INT)
returns varchar(255)
begin
 declare chars_str varchar(100) default
   ’abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ’;
 declare return_str varchar(255) default ’’;
 declare i int default 0;
 while i < n do
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end $$
#将命令结束符修改回;
delimiter ;
#测试自定义函数
select rand_string(6);

delimiter $$
drop procedure insert_dept $$

#批量插入数据的存储过程 调用随机字符函数
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
 set autocommit = 0;  
 repeat
 set i = i + 1;
 insert into dept values ((start+i) ,rand_string(10),rand_string(8));
  until i = max_num
 end repeat;
   commit;
 end $$

delimiter ;
#调用存储过程向数据表中插入数据
call insert_dept(100,10);
#--------------------------------------------存储过程示例结束--------------------------------------------#

MySQL性能分析工具explain的使用
mysql> explain select * from emp where ename=’jDpQjv’G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1800000
        Extra: Using where
1 row in set (0.00 sec)

注意:语句后面不需要加;

create index myind on 表名 (列1,列2); alter table emp drop primary keyExplain select * from emp where ename=“zrlcHd”
会产生如下信息:
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型
possible_keys:表示查询时,可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描的行数
Extra:执行情况的描述和说明

在什么列上添加索引比较合适

1 在经常查询的列上加索引.
2 列的数据,内容就只有少数几个值,不太适合加索引(如性别).
3 内容频繁变化,不合适加索引
4 不会出现在where语句中的字段不适合加索引

索引的种类
1 主键索引 (把某列设为主键,则就是主键索引)
2 唯一索引(unique) (即该列具有唯一性,同时又是索引)
3 index (普通索引)
4 全文索引(FULLTEXT)
select * from article where content like ‘%李连杰%’;
hello, i am a boy
你好,我是一个男孩  =>中文 sphinx

5 复合索引(多列和在一起)
create index myind on 表名 (列1,列2);

如何创建索引
如果创建unique / 普通/fulltext 索引
1. create [unique|FULLTEXT] index 索引名 on 表名 (列名...)
2. alter table 表名 add index 索引名 (列名...)
//如果要添加主键索引
alter table 表名 add primary key (列...)

删除索引
1. drop index 索引名 on 表名
2. alter table 表名 drop index index_name;
3. alter table 表名 drop primary key

显示索引
    show index(es) from 表名
    show keys from 表名
    desc 表名

如何查询某表的索引
show indexes from 表名

使用索引的注意事项
查询要使用索引最重要的条件是查询条件中需要使用索引。
下列几种情况下有可能使用到索引:
1,对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。
2,对于使用like的查询,查询如果是  ‘%aaa’ 不会使用到索引
    ‘aaa%’ 会使用到索引。
下列的表将不使用索引:
1,如果条件中有or,即使其中有条件带索引也不会使用。
2,对于多列索引,不是使用的第一部分,则不会使用索引。
3,like查询是以%开头
4,如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。
5,如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

检测索引使用情况
show status like ’handler_read%’;
结论: Handler_read_key 越大越好
Handler_read_rnd_next 越小越好

显示建库建表
show create database test;
show create table dept;

MyISAM 和 Innodb区别是什么
1. MyISAM 不支持外键, Innodb支持
2. MyISAM 不支持事务,不支持外键.
3. 对数据信息的存储处理方式不同.(如果存储引擎是MyISAM的,则创建一张表,对于三个文件..,如果是Innodb则只有一张文件 *.frm,数据存放到ibdata1)

对于 MyISAM 数据库,需要定时清理
optimize table 表名

常见的sql优化手法
1. 使用order by null  禁用排序
比如 select * from dept group by ename order by null

2. 在精度要求高的应用中,建议使用定点数(decimal)来存储数值,以保证结果的准确性
1000000.32 万
create table sal(t1 float(10,2));
create table sal2(t1 decimal(10,2));

对表进行水平划分
例如:将id编号的第一位作为数据表表的编号,然后将数据插入不同的数据表中。

对表进行垂直划分
根据实际情况合理设计表

其它:
选择字段的一般原则是保小不保大,能用占用字节小的字段就不用大字段。
数据库只存储路径。图片和文件存放在文件系统,甚至单独放在一台服务器(图床).

====================MySQL参数配置====================
1.最重要的参数就是内存,我们主要用的innodb引擎,所以下面两个参数调的很大
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G
2.对于myisam,需要调整key_buffer_size
    当然调整参数还是要看状态,用show status语句可以看到当前状态,以决定改调整哪些参数

====================MySQL读写分离====================
将读写放在不同的服务器上,然后使用MySQL镜像技术将各个服务器上的数据同步。