PHP数据库编程之MySQL优化策略概述

联合索引的BTREE建立方法：对第一个条件建立索引，在第一个索引的BTREE区域对第二个条件建立索引，以此类推，所以，在使用索引时，不用第一个条件用第二个条件也不会用到联合索引。使用索引时要条件要有顺序，有序列的使用。

4、索引长度对查询也有很大影响，我们应该尽量建立短的索引长度，我们可以使用查询列

SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename  来测试对column列建立索引时选取不同的长度，索引的覆盖率有多大，我们选择一下接近饱和的n个长度来建立索引
ALTER TABLE tablename ADD INDEX (column(n));  来对某一列的前n个字符建立索引。若前n个字符相同，我们甚至可以对字符串进行反转存储，然后建立索引。

5、对于经常修改导致的索引碎片的维护方式：ALTER TABLE tablename ENGINE oldengine;即再次应用一下表存储引擎，使其自动维护；也可以用 OPTIMIZE tablename 命令来进行维护。

数据查询方面优化

数据库操作尽量少查询，有查询时尽量不在数据库层面上进行数据操作，而是返回到PHP脚本中操作数据，减轻数据库压力。

一旦发现有数据库性能问题，要及时解决，一般用慢查询日志记录查询很"慢"的语句，用EXPLAIN分析查询和索引使用情况，用PROFILE分析语句执行时的具体资源消耗。

慢查询日志：

1、在my.ini或my.cnf的[mysqld]下添加

slow_query_log_file=/path //设置日志存储路径
long_query_time=n //设置如果语句执行时间达到n秒，就会被记录下来

2、然后在MySQL里设置SET slow_query_log='ON'来开启慢查询。

3、记录下日志后，我们用/bin/目录下的mysqldumpslow filename来查看日志，其常用参数如下:

-g pattern 使用正则表达式
-t n返回前n条数据
-s c/t/l/r 以记录次数/时间/查询时间/返回记录数来排序

EXPLAIN语句

使用方法，在要执行的查询语句前面加EXPLAIN

EXPLAIN SELECT * FROM user;

得到形如下图的结果：

下面是对每一项的解释：

id 查询语句的id，简单查询无意义，多重查询时可以看出执行查询的顺序
select-type 执行的查询语句的类型，对应多重查询，有simple/primary/union等。
tabel 查询语句查询的数据表
type  获得数据的类型 常见的类型效率从高到低为 null>const>eq_ref>ref>range>index>all
possible-keys:可能使用到的索引
key 使用到的索引
key_len索引长度
ref 使用哪个列与索引一起从表中选择。
rows  查找到数据要扫描的大概行数，可看出索引的优劣
extra  常见的有
using filesort 查询到数据后进行文件排序，较慢，需要优化索引
using where 读取整行数据后进行判断过滤，是否符合where条件
using index 索引覆盖，即在牵引中已经有这存储了目标数据，直接读取索引，很快。

PROFILE

用SELECT @@frofiling来查看PROFILE的开启状态。
如果未开启，用SET profiling=1来开启。
开启之后，再执行查询语句，MySQL会自动记录profile信息。
应用show profiles查看所有的sql信息，结果为 Query_ID Duration Query三列结果，分别是查询ID，用时和所用的sql语句。
我们可以使用

SHOW PFROFILE [type[,type]][FOR QUREY Query_ID][Limit rwo_count [OFFSET offset]]

type常见有ALL(全部) BLOCK IO(显示IO相关开销) CPU(CPU开销) MEMORY(内存开销)等

大型存储方面优化

数据库主从复制和读写分离

1、master将改变记录到二进制日志中，slave将master的二进制拷贝到它的中继日志中，重新将数据返回到它自己的数据中，达到复制主服务器数据的目的。

主从复制可以用作：数据库负载均衡、数据库备份、读写分离等功能。

2、配置主服务器master

修改my.ini/my.conf

[mysqld]
log-bin=mysql-bin //启用二进制日志
server-id=102 //服务器唯一ID

3、配置从服务器slave

log-bin=mysql-bin //启用二进制日志
server-id=226 //服务器唯一ID

4、在主服务器上授权从服务器

GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'

5、在从服务器上使用

change master to
master_host="masterip",
master_user="masteruser",
master_password="masterpasswd";

6、然后使用start slave命令开始进行主从复制。

不要忘记在每次修改配置后重启服务器，然后可以在主从服务器上用show master/slave status查看主/从状态。