mysql 访问速度_大型网站提升访问速度关键技术 —— MySQL优化

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的天数；

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

如果数据库崩溃，就可以通过时间和位置进行恢复。返回搜狐，查看更多