课程咨询 :17156168575 QQ:2720475033

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

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

  • 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高级开发工程师就业班

PHP高级开发工程师就业班

开班日期:11月30日

PHP高级开发工程师周末班

PHP高级开发工程师周末班

开班日期:11月30日

济南PHP培训班就业班

济南PHP培训班就业班

开班日期:11月30日

PHP高级开发工程师周末班

PHP高级开发工程师周末班

开班日期:11月30日

 扫一扫,关注一下! 济南:历下区山大路47号数码港大厦 济南:历下区趵突泉北路三联商社
青岛:市南区金坛路17号 潍坊:奎文区东风东街299号建行大厦
烟台:海港路25号阳光100城市广场 临沂:兰山区红旗路1号苏宁易购
淄博:张店区金晶大道68号华润大厦 济宁:市中区太白路10号苏宁生活广场
课程培训电话:17156168575 QQ:2720475033 全国服务监督电话:400-111-8989    服务邮箱 tousu@tedu.cn

2001-2017 达内时代科技集团有限公司 版权所有 京ICP证8000853号-56