优化查询、访问量大时的优化

1.优化查询的方法

1.1使用索引

应尽量避免全表扫描，首先应考虑在 where 及 order by 、group by 涉及的列上建立索引。

1.1.1 为出现在where字句的字段建立一个索引

SELECT category_name FROM mytable
WHERE category_id=1;

为category_id建立一个索引：

CREATE INDEX mytable_categoryid ON mytable(category_id);

若语句为：

SELECT category_name FROM mytable
WHERE category_id=1 AND user_id=2;

若再给user_id建立一个索引，这不是最佳方法。可建立多重索引：

CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);

命名方式：表名—字段1名—字段2名

1.1.2 为order by子句中的字段建立一个索引

SELECT category_name FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY adddate DESC;

创建索引：

CREATE INDEX mytable_categoryid_useris_adddate ON mytable(category_id,user_id,adddate);

注意：mytable_categoryid_uerid_adddate将会被截断为：mytable_categoryid_uerid_addda

1.1.3 索引机制

本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

1.2优化SQL语句

1.2.1 通过explain来查看SQL语句执行效果

将explain移到前面，查看执行计划；

可帮助选择更好的索引和优化查询语句，写出更好的优化语句。

通常我们可以对比较复杂的尤其是涉及到多表的 SELECT 语句， 把关键字 EXPLAIN 加到前面， 查看执行计划。

• 例如： explain select name from news;

1.2.2 任何地方都不要使用 select * from t

用具体的字段列表代替“*” ， 不要返回用不到的任何字段.

1.2.3 不在索引列做运算或者使用函数。

1.2.4 使用 limit

查询尽可能使用 limit 减少返回的行数， 减少数据传输时间和带宽浪费。

1.3 优化数据库对象

1.3.1 优化表的数据类型

使用 procedure analyse()函数对表进行分析， 该函数可以对表中列的数据类型提出优化建议。

能小就用小。

表数据类型第一个原则是： 使用能正确的表示和存储数据的最短类型。 这样可以减少对磁盘空间、 内存、 cpu 缓存的使用。

使用方法： select * from 表名 procedure analyse();

1.3.2 对表进行拆分

通过拆分表可以提高表的访问效率。

有 2 种拆分方法：

1. 垂直拆分：把主键和一些列放在一个表中， 然后把主键和另外的列放在另一个表中。 如果一个表中某些列常用， 而另外一些不常用， 则可以采用垂直拆分。
2. 水平拆分：根据一列或者多列数据的值把数据行放到二个独立的表中。

1.3.3 使用中间表来提高查询速度

创建中间表， 表结构和源表结构完全相同， 转移要统计的数据到中间表， 然后在中间表上进行统计， 得出想要的结果。

1.4 硬件优化

1.4.1 CPU 的优化

选择多核和主频高的 CPU。

1.4.2 内存的优化使用更大的内存。

将尽量多的内存分配给 MYSQL 做缓存。

1.4.3 磁盘 I/O 的优化

1.使用磁盘阵列

RAID 0 没有数据冗余， 没有数据校验的磁盘陈列。 实现 RAID 0至少需要两块以上的硬盘， 它将两块以上的硬盘合并成一块， 数据
连续地分割在每块盘上。

RAID1 是将一个两块硬盘所构成 RAID 磁盘阵列， 其容量仅等于一块硬盘的容量， 因为另一块只是当作数据“镜像”。

使用 RAID-0+1 磁盘阵列。 RAID 0+1 是 RAID 0 和 RAID 1 的组合形式。 它在提供与 RAID 1 一样的数据安全保障的同时， 也提供了与 RAID 0 近似的存储性能。

2.调整磁盘调度算法

选择合适的磁盘调度算法， 可以减少磁盘的寻道时间。

1.5 MySQL 自身的优化

对 MySQL 自身的优化主要是对其配置文件 my.cnf 中的各项参数进行优化调整。

• 如指定 MySQL 查询缓冲区的大小，
• 指定 MySQL 允许的最大连接进程数等。

1.6 应用优化

1.6.1 使用数据库连接池

1.6.2 使用查询缓存

它的作用是存储 select 查询的文本及其相应结果。

如果随后收到一个相同的查询， 服务器会从查询缓存中直接得到查询结果。

查询缓存适用的对象是更新不频繁的表， 当表中数据更改后， 查询缓存中的相关条目就会被清空。

2.如果有一个特别大的访问量到数据库上， 怎么做优化？

2.1.使用优化查询的方法（见上面）

2.2.主从复制， 读写分离， 负载均衡

目前，大部分的主流关系型数据库都提供了主从复制的功能，通过配置两台（或多台）数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。

网站可以利用数据库的这一功能，实现数据库的读写分离，从而改善数据库的负载压力。

一个系统的读操作远远多于写操作，因此写操作发向 master，读操作发向 slaves 进行操作（简单的轮循算法来决定使用哪个 slave）。

利用数据库的读写分离，Web 服务器在写数据的时候，访问主数据库（Master），主数据库通过主从复制机制将数据更新同步到从数据库（Slave），这样当 Web 服务器读数据的时候，就可以通过从数据库获得数据。这一方案使得在大量读操作的 Web 应用可以轻松地读取数据，而主数据库也只会承受少量的写入操作，还可以实现数据热备份，可谓是一举两得的方案。

2.2.1 主从复制的原理：

影响 MySQL-A 数据库的操作，在数据库执行后，都会写入本地的日志系统 A 中。

假设，实时的将变化了的日志系统中的数据库事件操作，通过网络发给 MYSQL-B。

MYSQL-B 收到后，写入本地日志系统 B，然后一条条的将数据库事件在数据库中完成。

那么，MYSQL-A 的变化，MYSQL-B 也会变化，

这样就是所谓的 MYSQL 的复制。

在上面的模型中，MYSQL-A 就是主服务器，即 master，MYSQL-B 就是从服务器，即slave。

日志系统 A，其实它是 MYSQL 的日志类型中的二进制日志，也就是专门用来保存修改数据库表的所有动作，即 bin log。

【注意 MYSQL 会在执行语句之后，释放锁之前，写入二进制日志，确保事务安全】

日志系统 B，并不是二进制日志，由于它是从 MYSQL-A 的二进制日志复制过来的，并不是自己的数据库变化产生的，有点接力的感觉，称为中继日志，即 relay log。

可以发现，通过上面的机制，可以保证 MYSQL-A 和 MYSQL-B 的数据库数据一致，但是时间上肯定有延迟，即 MYSQL-B 的数据是滞后的。

2.2.2 简化版

mysql 主(称 master)从(称 slave)复制的原理：

1. master 将数据改变记录到二进制日志(binary log)中，也即是配置文件 log-bin 指定的文件(这些记录叫做二进制日志事件， binary log events)
   1. 从图中可以看出，Slave 服务器中有一个 I/O线程(I/O Thread)在不停地监听 Master的二进制日志(Binary Log)是否有更新：
      1. 如果没有，它会睡眠等待 Master 产生新的日志事件；
      2. 如果有新的日志事件(Log Events)， 则会将其拷贝至 Slave 服务器中的中继日志(Relay Log)。
2. slave 将 master 的二进制日志事件（binary log events）拷贝到它的中继日志(relay log)
3. slave 重做中继日志中的事件,将 Master 上的改变反映到它自己的数据库中。 ， 所以两端的数据是完全一样的。
   1. 从图中可以看出， Slave 服务器中有一个 SQL 线程(SQL Thread)从中继日志读取事件， 并重做其中的事件， 从而更新 Slave 的数据， 使其与 Master 中的数据一致。
   2. 只要该线程与 I/O 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小。

2.2.3 简要原理图：

2.2.4 主从复制的方式

1.同步复制

主服务器在将更新的数据写入它的二进制日志（Binlog）文件中后，必须等待验证所有的从服务器的更新数据是否已经复制到其中，之后才可以自由处理其它进入的事务处理请求。

2.异步复制

主服务器在将更新的数据写入它的二进制日志（Binlog）文件中后，无需等待验证更新数据是否已经复制到从服务器中，就可以自由处理其它进入的事务处理请求。

3.半同步复制

主服务器在将更新的数据写入它的二进制日志（Binlog）文件中后，只需等待验证其中一台从服务器的更新数据是否已经复制到其中，就可以自由处理其它进入的事务处理请求，其他的从服务器不用管。

2.3 数据库分表， 分区， 分库

2.3.1 分表

见上面描述。

2.3.2 分区

把一张表的数据分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上。

分区后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘 I/O 读写性能，实现比较简单。

包括水平分区和垂直分区。

2.3.3 分库

根据业务不同把相关的表切分到不同的数据库中，比如 web、bbs、blog 等库。