Mysql 优化基础

Mysql数据库优化方式

mysql优化是一个综合性的技术,主要包括:

a.表的设计合理化(符合3范式)

b.添加适当的索引(index)[四种:普通索引,主键索引,唯一索引,全文索引]

c.分表技术(水平分割,垂直分割)

d.读写(update/delete/add)分离

e.存储过程[模块优化,可以提高速度]

f.对mysql配置优化[配置最大并发数my.ini,调整缓存大小]

g.mysql服务器硬件升级

h.定时去清除不需要的数据,定时进行碎片整理(MyISAM)

 

Sql语句本身优化:

问题: 如何在大型项目中如何迅速(定位慢查询)

1)首先了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间,一共执行多少次 select/update/当前连接)

show status

常用的:

show status like 'uptime'

show status like 'com_select'

show status like 'com_insert'

show status like 'com_update'

show status like 'com_delete'

 

show status like 'connection' 连接

show status like 'slow_queries'

show [session/global] status like ... 如果不写[session/global] 默认是session会话,只取出当前窗口的执行,如果想看所有用show global status like...

2)如何去定位慢查询(构建400W-->存储过程) 默认情况下,mysql认为10秒才是慢查询

a)修改mysql的慢查询  

show variables like 'long_query_time' //显示当前慢查询时间 

set long_query_time = 1 //修改慢查询时间

创建大表:

CREATE TABLE dept (

deptno int UNSIGNED NOT NULL DEFAULT 0,

Dname VARCHAR(20)  NOT NULL DEFAULT "",

loc   VARCHAR(13)  NOT NULL DEFAULT ""

)ENGINE=MyISAM DEFAULT CHARSET=utf8

 

CREATE TABLE emp (

empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

ename VARCHAR(20) NOT NULL DEFAULT "",

job VARCHAR(9) NOT NULL DEFAULT "",

mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

hiredate DATE NOT NULL,

sal DECIMAL(7,2) NOT NULL,

comm DECIMAL(7,2) NOT NULL,

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0

)ENGINE=MyISAM DEFAULT CHARSET=utf8

 

CREATE TABLE salgrade(

grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

losal DECIMAL(17,2) NOT NULL,

hisal DECIMAL(17,2) NOT NULL

)ENGINE = MyISAM DEFAULT CHARSET =utf8;

 

insert into salgrade values(1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999)

 

 

delimiter $$

drop function rand_string $$

create function rand_string(n INT)

returns varchar(255)

begin

declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

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(i+rand()*52),1));

set i = i+1;

end while;

return return_str;

end $$

 

  delimiter $$

drop function rand_num $$

create function rand_num()

returns int(5)

begin

declare i int default 0;

set i = floor(10 + rand*500);

return i;

end $$

 

create procedure insert_emp(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 emp values((start+i),rand_string(6),'SALEMAN',0001,curdate(),2000,400,rand_num());

until i = max_num

end repeat;

commit;

end $$

3)这时如果出现一条语句执行时间超过1秒,就会被统计到

4)如果把慢查询sql语句记录到我们的日志中.在默认情况下mysql不会记录慢查询,需要启动mysql时候,指定记录慢查询才可以.

bin\mysqld.exe --safe.mode --slow-query-log (5.5版本)

bin\mysqld.exe -log-slow-queries =d:/abc.log(低版本可以在my.ini指定)

5)测试 可以看到在日志中就记录下我们的mysql慢语句

 

索引创建说明:

1.在mysql中fulltext索引只能针对myISAM生效

2.mysql自己提供的fulltext针对英文生效->sphinx(coreseek)技术处理中文

3.使用方法是match(字段名).against('关键字')

4.全文索引一个叫停止词.

 

索引的缺点:

占用磁盘空间

对dml语句的效率有影响(insert/update/delete)会变慢

维护内部的二叉树

 

在那些列上适合添加索引?

1)在较频繁的作为查询条件的字段上应该创建索引

2)更新非常频繁的字段不适合创建索引

3)不该出现在where条件的字段不适合创建索引

总之:要满足以下条件的字段才适合创建索引:

a)肯定在where条件中经常使用到

b)该字段的内容是有多个并不是唯一的几个值

c)变化不能太频繁

使用注意事项:

1)对于创建的多列索引,只要查询条件使用最左边的列,索引一般都会被使用.

2)对于使用like的查询,插叙如果是'%aa'不会使用到索引 'aaa%'会使用到索引

3)如果条件中有or,即是其中有条件带索引也不会使用.换言之,就是要求使用的所有字段,都必须建立索引,我们建议大家尽量避免使用or.

4)如果有列是字符串类型一定要用单引号引起来,否则不使用索引.

5)如果mysql估计使用全表扫描要比使用索引快,则不使用索引.

 

Sql语句的小技巧:

1.在使用group by 分组查询是,默认分组后,还会排序,可能会降低速度.则可以使用group by ... order by null禁止排序

 

 

mysql 完成数据库定时数据备份:

1)手动备份数据库(表)方法

cmd控制台:

mysqldump -uroot -proot 数据库 > 文件名

比如:把temp数据库备份到d:\temp.bak

mysqldump -uroot -proot temp>d:\temp.bak

如果你希望备份是,数据库的某几张表

mysqldump -uroot -proot temp dept>d:\temp.dept.bak

 

如何恢复备份文件恢复我们的数据:

mysql控制台

source d:\temp.bak

2)使用定时器来自动完成

把备份数据库的指令,写入到bat文件,然后通过任务管理器来定时调用bat文件

mytask.bat内容是:

"D:\Program Files\Mysql\bin\mysqldump" -uroot -proot supplier dept>d:\dept.bak

>>如果你的mysqldump.exe 文件路径中有空格,则一定要使用""包括

把mytask做成一个任务,并定时调用在凌晨2点

 

 

mysql分表:

1.水平分割:

原则是表结构不变的情况下,根据实际的数据进行分库存储和检索

在检索大数据表的时候,我们提供检索检索的原则应该根据实际的业务需求,找到分表的标准,并在检索页面约束用户的检索方式配合分页技术,大数据量的检索的需求在少数.

2.垂直分割:

把某个表的某些字段,这些字段在查询时,并没有太多关系,但是数据量比较大,我们建议大家可以,把这些字段单独的放到另外一张表,从而提高效率

 

mysql表的字段定义是保小不保大,尽量节省空间.

tinyint<smallint<meidumint<int<longint

 

关于网站的图片和视频存放

我们的数据表中,一般只存放图片或者时评的路径,资源是放在文件服务器上(往往配合独立的服务器足够带宽支持)

 

如何优化mysql数据库参数的配置:

 

数据库备份和增量备份配合使用:

方案:每周一做一个全备份,mysqldump,启用增量备份,把过期的时间设为大于等于7.