济南PHP培训
济南市达内教育培训学校

17156168575

热门课程

大型网站提升访问速度之MySQL优化

  • 时间:2016-09-22
  • 发布:济南php培训
  • 来源:济南php培训

1.表的设计合理化(符合3NF)

1)什么样的表才是符合3NF(范式)

表的范式首先是符合1NF、才能满足2NF、进一步满足3NF

1NF:即表的列具有原子性,不可再分解,只要数据库是关系型数据库,就自动的满足1NF

2NF:表中的记录是唯一的,就满足2NF,通常设计一个主键(不包含业务逻辑,一般自增长)来实现

3NF:即表中不要有冗余数据,就是表的信息如果能够被推导出来,就不应该单独的设置一个字段来存放(反3NF:在1对N的情况下为了提高效率可能会在1这一方设计一些字段来提高速度——适度冗余)

2)Sql语句优化

如何从一个大项目中迅速定位执行速度比较慢的语句(定位慢查询)

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

show status

常用指令:

show status like “uptime

show status like “com_select”(其他以此类推:com_insert/com_update/com_delete)

show [session|global] status like “com_select”

show status like “connections”

//显示慢查询

show status like “slow_queries”

②如何去定位慢查询

默认情况下10秒钟才是一个慢查询(mysql)

修改mysql的慢查询时间:

show variables like ‘long_query_time’

set long_query_time = 1

构建大表:

使用Mysql自定义函数即存储过程(也可以用php去构造)

如何把慢查询的sql记录到我们的日志里:

默认情况下我们的mysql不会记录慢查询,需要在启动mysql的时候,指定记录慢查询才可以

mysqld --safe-mode --slow-query-log [mysql5.5及以后版本可以在my.ini指定]

mysqld --log-slow-queries=/tmp/test.log [mysql5.0以后可以在my.ini指定]

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

1)添加索引

① 主键索引添加

当一张表把某个列设为主键时则该列就是主键索引

如果创建表时没有添加主键索引,也可以在创建表后添加主键索引:

alter table 表名 add primary key (列名);

② 普通索引

一般来说普通索引的创建是先创建表然后再创建普通索引

create index 索引名 on 表 (列)

③ 创建全文索引(FULLTEXT)

全文索引主要是针对文件、文件的索引,比如文章。全文索引只对MyISAM引擎有效。

如何使用全文索引:

select * from articles where body like ‘%mysql%’; [不会用到全文索引]

select * from articles where match(title,body) against(‘database’); [正确]

注意:fulltext只针对英文生效,中文采用sphinx(coreseek)技术处理;全文索引有一个叫做停止词的概念(只会对生僻词创建索引,而一些常用词不会创建)。

④创建唯一索引(UNIQUE)

当表的某列指定为unique约束,那么就是一个唯一索引

唯一索引可以为空

create unique index on 表 (列);

2)查询索引

desc 表名 【缺点:不能够显示索引名】

show index(es) from 表名

show keys from 表名

3)删除索引

alter table 表名 drop index 索引名;

如果删除的是主键索引:

alter table 表名 drop primary key;

4)修改索引

先删除,再重新创建

5)索引的使用

影响:

对磁盘IO的占用

降低DML(增删改)语句的效率——要维护二叉树索引

6)使用索引的注意事项:

①对于创建的多列索引只要查询条件使用了最左边的列,索引一般就会被使用;

②对于like的查询,查询如果是’%aaa’不会使用索引,’aaa%’会用到索引——在like查询时关键字的前面不能加’%’或者’_’这样的字符,如果一定要在前面有变化的值,则考虑使用全文索引;

③如果条件语句中有or,即使其中有条件带索引也不会使用。换言之就是要求使用的所有字段都必须建立索引;

④如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不会使用索引;

⑤如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

为什么创建索引之后查询的速度就会变快呢?

二叉树算法(BTREE)[效率 log2N 比如10次检索理论上可以扫描1024个数据]

explain可以帮助我们在不真正执行某个sql语句时,就知道mysql怎样执行,我们可以使用该指令去分析sql指令

7)查询索引的使用率

show status like “Header_read%”;

这个值越高表明索引的使用率越高

8)大批量添加数据

对于MyISAM:

alter table table_name disable keys;

Loading data//insert 语句

alter table table_name enable keys;

对于InnoDB:

将要导入的数据按照主键排序

set unique_checks=0,关闭唯一性校验

set autocommit=0,关闭自动提交

9)sql优化小技巧

在使用group by分组查询是默认分组后,还要进行排序,这样可能会降低速度

在group by之后加上order by null这样就会阻止排序

尽量使用left join(左外连接)代替多表查询

10)选择合适的存储引擎

MyISAM:

如果表对事务要求不高,同时是以查询和添加为主,考虑使用MyISAM存储引擎,比如文章表、回复表

InnoDB:

对事务要求高,保存的数据都是重要数据,建议使用InnoDB,比如订单表,账户表

Memory:

如果数据变化频繁,同时频繁查询和修改,考虑使用memory存储引擎;

速度极快(数据放在内存中)

MyISAM和InnoDB的区别:

事务安全;查询和添加速度;支持全文索引;锁机制(表锁和行锁);外键

如果你的数据库存储引擎是MyISAM,一定要定时进行碎片清理(否则删除的数据永远不会丢失):

optimize table table_name;

3.分表技术(水平分割、垂直)

关键是要找到分割的标准

1)水平分割

添加数据:

查询数据:

2)垂直分割

把某个表的某些字段在查询时并不是经常关心的,但是数据量又很大,可以把这些字段单独拿出来以提高效率。使用频率没有水平分割大。

4.读写[写:update/add/delete]分离

如果数据库压力很大,一台机器支撑不了,那么可以用mysql复制实现多台机器同步,将数据库的压力分散。

5.存储过程(不用多次编译、利于模块化编程)

6.对MySQL配置的优化(最大并发数、缓存大小)

1)最重要的参数就是内存,我们主要用的innodb引擎,所以下面两个参数调的很大

innodb_additional_mem_pool_size = 64M

innodb_buffer_pool_size =1G

2)对于myisam,需要调整key_buffer_size

当然调整参数还是要看状态,用show status语句可以看到当前状态,以决定改调整哪些参数

3)在my.ini修改端口3306,默认存储引擎和最大连接数

7.Mysql服务器硬件的升级(内存、CPU)

如果你的机器内存超过4G,那么毋庸置疑应当采用64位操作系统和64位mysql 5.5.19

8.定时清除不需要的数据,并且定时进行碎片整理(尤其是MyISAM引擎)

选择适当的字段类型,特别是主键

保小不保大,尽量节省空间

PHP定时完成数据库的备份

1)手动备份

mysqldump -uxxx -pxxx 数据库 [table1 table2]> xxx.sql

恢复数据

source xxx.sql

2)自动备份

把备份指令写入到一个批处理文件,然后通过任务调度器定时执行(crontab)

问题是每次都覆盖原来的备份文件,不利于分阶段备份,可以通过php代码来解决这个问题

xxx.sql’;

exec($command);

>

然后定时执行这个php代码

MySQL增量备份

增量备份的原理

Mysql数据库会以二进制的形式把用户对Mysql数据库操作记录到文件中。

当用户希望恢复的时候可以使用备份文件进行备份。

增量备份会记录DML语句,创建表的语句,不会记录select语句

记录:操作语句本身、操作的时间、操作的位置

如何进行增量备份和恢复

1)配置my.ini或者my.conf,启用二进制备份(5.1以后支持):

log-bin = /path/to/log

2)启动mysql得到文件:

mylog.index 索引文件 有哪些增量备份文件

mylog.000001 存放用户对数据库操作的文件

3)可以使用mysqlbinlog程序来查看备份文件的内容

mysqlbinlog 备份文件路径

1)恢复数据

可以根据时间点进行恢复,也可以根据位置进行恢复

截止时间

mysql --stop-datetime=”2014-02-27 17:37:58” /path/to/log | mysql -uxxx -p

mysql --stop-position=”111” /path/to/log | mysql -uxxx -p

开始时间

--start-datetime / --start-position

时间段

mysql --start-datetime=”2013-02-27 17:37:58” --stop-datetime=”2014-02-27 17:37:58” /path/to/log | mysql -uxxx -p

如何在工作中将全备份和增量备份配合使用:

每周一做一个全备份,启用增量备份,过期时间设为>=7的天数;

增量备份不用启用定时器;

如果数据库崩溃,就可以通过时间和位置进行恢复。

上一篇:Linux下如何配置nginx、PHP7详解
下一篇:如何阅读程序的源代码

马上预约七天免费体验课

姓名:

电话:

php安全编程——python测试实例编写

PHP 处理表单数据实例操作

PHP翻页类 调用实例

“#smoosh门”引发Web兼容性上的挑战

选择城市和中心
贵州省

广西省

海南省

达内教育

有位老师想和您聊一聊