数据库优化查询的方法有:
1、使用索引
尽量避免全表扫描,首先应考虑在where及order by,group by涉及的列上建立索引。
2、优化SQL语句
- 通过explain来查看SQL语句的执行效果,可以帮助选择更好的索引和优化查询语句。例如:
explain select * from news;
- 不要返回用不到的字段
- 不在索引列做运算或者使用函数
- 查询尽可能使用limit减少返回的行数,减少数据传输时间和带宽浪费
3、优化数据库对象
- 优化表的数据类型
select * from 表名 procedure analyse();
- 对表进行拆分:
可以提高表的访问效率。有两种拆分方法:
- 垂直拆分:把主键和一些列放在一个表中,然后把主键和另外的列放在另一个表中。
使用场景:如果一个表中某些列常用,另外一些不常用,就可以垂直拆分。 - 水平拆分:根据一列或者多列数据的值把数据行放到两个独立的表中。
- 垂直拆分:把主键和一些列放在一个表中,然后把主键和另外的列放在另一个表中。
- 使用中间表来提高查询速度:
创建中间表,表结构和源表结构完全相同,转移要统计的数据到中间表,然后在中间表上进行统计,得出想要的结果。
4、硬件优化
- CPU 优化:选择多核和主频高的CPU
- 内存优化:尽量多的内存分配给MySQL做缓存
- 磁盘I/O的优化:使用磁盘阵列;调整磁盘调度算法
5、MySQL自身的优化
对my.cnf中的各项参数进行优化调整。
6、应用优化
- 使用数据库连接池
- 使用查询缓存
问题:如果有一个特别大的访问量到数据库上,怎么优化?
1、使用优化查询的方法;
2、主从复制、读写分离、负载均衡:
主从复制通过配置两台(或多台)数据库的主从关系,可以将一台数据库服务器的数据更新到另一台服务器上。
网站可以利用这一功能,实现数据库的读写分离,从而改善数据库的负载压力。一个系统的读操作远远多于写操作,因此写操作发向master,读操作发向slaves进行操作(简单的轮询算法来决定使用哪个slave)。
利用数据库的读写分离,web服务器在写数据的时候,访问主数据库(master),主数据库通过主从复制机制将数据同步更新到从数据库(slave),这样当web服务器读数据的时候,就可以通过从数据库获取。如此,主数据库只需要承受少量的写入操作,还可以实现数据热备份。
主从复制的原理:
(1)master将数据改变记录到二进制日志(binary log)中,也是配置文件log-bin指定的文件(注意MySQL会在执行语句之后,释放锁之前,写入二进制日志,确保事务安全)
(2)slave将master的二进制日志事件拷贝到中继日志(relay log)
(3)slave重做中继日志中的事件。将master上的改变反映到自己的数据库中。所以主从数据库的数据是完全一致的。
注意:时间上肯定有延迟,slave的数据是滞后的!
主从复制的几种方式:
(1)同步复制:主服务器在将更新的数据写入他的二进制日志文件后,必须等待验证所有的从服务器的更新数据是否已经复制到其中,之后才可以自由处理其他的事务处理请求。
(2)异步复制:主服务器在将更新的数据写入到他的二进制日志文件后,无需等待验证从服务器是否完成复制。就可以自由处理其他事务请求。
(3)半同步复制:主服务器在将更新的数据写入到他的二进制日志文件后,只需等待其中一台服务器的更新数据是否完成复制,就可以自由处理其他事务请求,其他的从服务器不用管。
3、数据库分表,分区,分库
分表见以上;
分区:把一张表的数据分成多个区块,这些区块可以在一个磁盘上,也可以在不同的磁盘上,分区后,表面上还是一张表,但数据散列在多个位置,这样一来,多块硬盘同时处理不同的请求,从而提高磁盘I/O读写性能,实现比较简单。包括水平分区和垂直分区。
分库:根据业务不同把相关的表切分到不同的数据库中,比如web、bbs、blog等库。