首页 > 代码库 > mysql优化

mysql优化

目前看网站的开发瓶颈

1. 带宽-云计算,云终端(视屏网站)

2. 数据库-网站大并发,海量存储(sina,sohu )

 

话题 :怎样做,我们的数据库会更快!

 

数据库分类:

关系型数据库: mysqloraclesql serverdb2informix

非关系型数据面向集合,面向对象

nosql数据库: mongodb PHP项目

 

 

数据库优化应当考虑方面:

1. 数据库的设计()->符号3NF(有时候反3NF)

2. select语句 ->重点(如何定位慢查询)

3. 数据库参数设置.

4. 配套硬件的设置(CPU、内存)

 

 

数据库的设计

 

 

1.我们的数据表要满足1NF, 含义是

表中的每条记录是原子性约束 2. 只要数据库是关系型数据库,就满足1NF

2.2NF 二范式

表中的记录,不能够出现完全一样的记录,就满足了二范式,一般说是通过设置主键来完成.

3. 3NF, 三范式

表中的某个字段的信息,可以推导得出,不要出现冗余数据.

比如下面就是不满足3NF

  

 但是,规定是死的,技术是需要灵活使用.

sql语句的优化-针对Mysql

1. 通过 show status 可以查看当前Mysql的一些情况.

比如看看mysql一共执行了多少次 增,删,改,查

show session  status like com_select【这个指令用于查看,当前会话,执行了多少次select

show session  status like com_update; //delete /insert

 

如果你希望查看的是数据库从启动到目前执行的情况则,命令应该这样写:

show global   status like com_update; //delete /inser

 

Connections:试图连接MySQL服务器的次数

show status like Connections;
Uptime:服务器工作的时间(单位秒)
Slow_queries:慢查询的次数 (默认是慢查询时间10s) [重要的知识点如何找到慢查询]


2. 在对mysqlselect 语句进行优化时,我们使用的工具是explain(具体说)

如何定位慢查询

 

1.任务:做一张1800000 记录的大表

使用的是自写的脚本.(介绍Mysql的存储过程和自定义函数)

<?php

$con=mysql_connect();

if(!$con){

exit;

}

//msql_select_db(temp,$con);

$sql=select  temp.rand_string();

 

?>

 查询的慢日志,默认是放在 my.ini文件 :

datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

 

2. 如果在工作中,需要去记录慢查询,则我们启动mysql的方式是这样

dos>bin\mysqld.exe  --slow-query-log

 

 

3. 用一个指令可以查看当前慢查询的时间 ,默认10s

show variables like slow_query_time;

如果 测试,我们可以把这个默认的慢查询时间10s->1s

set long_query_time=1;

 

4. 当你的查询时间查过了1s ,则在

datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

就有日志文件itcast-211b4ff3-slow.log

就记录了.

 

Explain 的使用

基本用法是

explain sql语句;

 

作用是:通过explain 可以看到Mysql数据库将怎样执行你的sql语句


extra表示额外信息 (比如 where , filesort ,temp表)

rows: 修改一下说法(图上有点错误): 估算出结果集行数

 

 

 我们使用子查询时,会创建临时表临时表的效率相对比较低我们建议使用 left join 来完成完成查询.

 

举例

//查询办公地点在北京的 所有雇员

explain select * from emp where emp.deptno=(select * from dept where loc=bj);

 

select * from 1 left join 2 on 连接条件 where 更多条件

 

 

更详细的说明

 

Explain select * from emp where ename=zrlcHd
会产生如下信息:
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型
possible_keys:表示查询时,可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描的行数
Extra:执行情况的描述和说明

 

 

如何给表提速

 

MyISAM数据表,有三个文件, *.frm , *.myd , *.myi 

创建索引:

基本语法:

create index 索引名 on 表名(字段);

 

实际案例:

create index myind1 on emp(ename);

哪些字段不要建立索引

1. 唯一性差的字段不要建立索引 [sex ]

2. 变化频繁的字段不要建立索引 [用户在线状态] [update]

3. where条件中,很少出现的不要建立索引.

4. where条件中经常出现,同时不满足上面3个条件就应当建立索引.

 

 

索引的分类

1. 主键索引(只有这个字段,被设置成主键,则自动成为主键索引在一张中只能有一个主键索引) primary key 是可以多个列构造.

2. 唯一索引(UNIQUE)

 

 create table bbb( id int primary key auto_increment, name varchar(20) unique);

insert into aaa values(1,aa);

insert into aaa values(1,bb); (x)

insert into aaa values(null,cc);

insert into aaa values(2,aa);(x)

insert into aaa values(3,null);

insert into aaa values(4,null);

 

 主键和UNIQUE的比较

① 主键不能重复,也不能为NULL

② UNIQUE不能重复,但是可以为NULL

 

3. 普通索引

4. 全文索引(FULLTEXT). ->MySql  -sphinx (中文分词)/ coreseek

create fulltext index on news (content)

select * from news where content like 大暴雨%

 

创建索引

1. 主键索引创建

创建表的时候,就指定主键

create table aaa100(id int primary key ,name varchar(20) );

如果是复合主键

create table aaa100(id int ,name varchar(20), primary key (id,name) );

 

如果表已经创建好,然后通过修改的方法来添加主键索引.

 

ALTER TABLE emp ADD PRIMARY KEY (empno);

 

2. 唯一索引的创建

create unique index 索引名 on 表名(字段名);

 

3. 普通索引

create  index 索引名 on 表名(字段名);

 

4. 全文索引

create fulltext index 索引名 on 表名(字段名);

 

查询索引

三种方法:

show index form 表名

show keys from 表名

desc 表名

 

修改索引

alter 。。

删除索引,重新创建

 

删除索引

drop index 索引名 on 表名 

alter table 表名 drop index索引名;

删除主键索引

alter table 表名 drop primary key;

 

如何正确使用索引,和注意事项

 

1. 如果你创建的索引是复合索引:

alter table dept add index myind (dname,loc);

 

 那么当select 语句用到左边的列时,才用到该索引.

 

2. 在使用like语句查询时,当 like %aa 就不能使用到索引了

 

3. select 语句,尽量不要使用or 语句,因为这个关键字,会让索引失效.(比如 where 条件中有三个 字端有两个是索引字段,但是有一个不是索引,导致一个索引都不要.)

 

4. 一会说意思建议 在mysql不管这个字段是数值,还是字符串,建议大家 使用’’引用一下.

 

5. 如果mysql发现扫描全部表,比通过索引查询还快,就会不使用索引

 

 

 

小技巧

 

关于group by的优化

如果只希望分组,但不希望排序可以使用 order by null 来禁用排序

explain select *from dept group by loc order by null\G

 

 

有些情况下,可以使用连接来替代子查询。(在实际开发中,使用left join 来替代案例到时在想),因为使用joinMySQL不需要在内存中创建临时表。(讲解)

 

 

MyISAMINNODB的选择

如果我们查询某张表频繁,同时对该表不需要太多的事务处理,则考试使用MyISAM

如果对某表操作是,对事务要求高,则使用INNODB

事务(1. commit 2. rollback, 3 savepoint)

 

MyISAM>INNODB

 

一个项目中,可以更加实际情况来决定,表使用什么存储引擎.

 

关于字段类型选择

 

对应数值精度要求高的 建议使用 deciaml ,不要使用float

 

对应存储引擎是MyISAM类型的,我们要定时对它优化. *.MYD

optimize table 表名 [优化]

 

日期类型要根据实际需要选择能够满足应用的最小存储的早期类型

 

这里说明一下datetime   timestamp区别:

timestamp 是会跟着你的update变化的.

我建议大家 关于时间字段,可以使用 datetime , 也可以使用int

 

提出一个: 如果取出 一个小时内的新贴.

 

time()-3600*30

 

mysql函数-DEDE [日期函数]

 

补充: datetime 这样就可以使用Mysql数据库提供的日期函数.

 

 

create table bbs(id int primary key, poster varchar(32), content varchar(1024),posttime datetime);

 

//请查询1个小时内,发的最新帖子

 

最重要的函数是 DATE_ADD(时间,间隔时间); | DATE_SBU

select * from bbs where DATE_ADD(now(),interval  -1 hour) < posttime;  

 表的水平划分

所谓表的水平分割,指的是:把一张大表,根据某个标准(实际来定),分割成小表,各个表的结构都是一样的! (关键点是,找到分割表的标准

 表的垂直分割