phpmysql计算php之MySQL数据库优化策略的详解

2、如果我们们经对数据库中的数据行进行增删，那么会涌现数据ID过大的情形，用ALTER TABLE tablename AUTO_INCREMENT=N，使自增ID从N开始计数。

3、对int类型添加 ZEROFILL 属性可以对数据进行自动补0

4、导入大量数据时最好先删除索引再插入数据，再加入索引，不然，mysql会花费大量韶光在更新索引上。

5、创建数据库书写sql语句时 ，我们可以在IDE里创建一个后缀为.sql的文件，IDE会识别sql语法，更易于书写。
更主要的是，如果你的数据库丢失了，你还可以找到这个文件，在当前目录下利用/path/mysql -uusername -ppassword databasename < filename.sql来实行全体文件的sql语句（把稳-u和-p后紧跟用户名密码，无空格）。

数据库设计方面优化

1、数据库设计符合第三范式，为了查询方便可以有一定的数据冗余。

2、选择数据类型优先级 int > date,time > enum,char>varchar > blob，选择数据类型时，可以考虑更换，如ip地址可以用ip2long()函数转换为unsign int型来进行存储。

3、对付char(n)类型，在数据完全的情形下只管即便较小的的n值。

4、在建表时用partition命令对单个表分区可以大大提升查询效率，MySQL支持RANGE，LIST，HASH，KEY分区类型，个中以RANGE最为常用，分区办法为：

CREATE TABLE tablename{

}ENGINE innodb/myisam CHARSET utf8 //选择数据库引擎和编码

PARTITION BY RANGE/LIST(column),//按范围和预定义列表进行分区

PARTITION partname VALUES LESS THAN /IN(n),//命名分区并详细限定分区的范围

5、选择数据库引擎时要把稳innodb 和 myisam的差异。

存储构造：MyISAM在磁盘上存储成三个文件。
而InnoDB所有的表都保存在同一个数据文件中，一样平常为2GB

事务支持：MyISAM不供应事务支持。
InnoDB供应事务支持事务。

表锁差异：MyISAM只支持表级锁。
InnoDB支持事务和行级锁。

全文索引：MyISAM支持 FULLTEXT类型的全文索引(不适用中文，以是要用sphinx全文索引引擎)。
InnoDB不支持。

表的详细行数：MyISAM保存有表的总行数，查询count()很快。
InnoDB没有保存表的总行数,须要重新打算。

外键：MyISAM不支持。
InnoDB支持

索引方面优化

1、innodb是聚簇索引，存储索引时必须有主键，如果没有指定，引擎会自动天生一个隐蔽的主键，天生一个主索引，索引内存放的是主键的物理地址，数据靠主键存放，每次利用索引时要先找到主索引，然后找到主索引下的数据。

优点通过主键查找特殊快，缺陷是次级索引会变慢，由于须要先通过次级索引（次级索引里是主索引的位置。
）找到主索引，然后通过主索引找数据。
并且如果主键无规律，插入新值时须要移动较多数据块，会影响效率，以是要只管即便利用有规律递增的int型做主键。
还有由于数据紧随着主键放，以是如果数据中有数据量特殊大的列(text/blob)，innodb查询时会跳过很多数据块，也会导致慢。

2、myisam的索引各个索引都相同统一指向磁盘上各个行的地址，都是轻量级的指针数据。
缺陷是各个索引的建立不是通过主键，查询没有聚簇索引查找主键快。
但其由于存储的是地址，以是在插入新值时比较方面移动改变。

3、进行多条件查询时，对多条件分别建立索引时，实行sql查询时，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查看主/从状态。

实现数据库的读写分离要依赖MySQL的中间件，如mysql_proxy，atlas等。
通过配置这些中间件来对主从做事器进行读写分离，使从做事器承担被读取的任务，从而减轻主理事器的包袱。

数据库的sharding

在数据库中数据表中的数据量非常弘大的时候，无论是索引还是缓存等压力都很大，对数据库进行sharding，使之分别以多个数据库做事器或多个表存储，以减轻查询压力。

办法有垂直切分、水平切分和联合切分。

垂直切分：在数据表非常多的时候，把数据库中关系紧密(如同一模块，常常连接查询)的表切分出来分别放到不同的主从server上。

水平切分：在表不多，而表里的数据量非常大的时候，为了加快查询，可以用哈希等算法，将一个数据表分为几个，分别放到不同的做事器上，加快查询。
水平切分和数据表分区的差异在于其存储介质上的不同。

联合切分：更多的情形是数据表和表中的数据量都非常大，则要进行联合切分，即同时进行垂直和水平分表，将数据库切分为一个分布式的矩阵来存储。

这些数据库的优化办法，每一种拿出来都可以写作一篇文章，可谓是博大精湛，理解并影象了这些办法，可以在有须要的时候进行有目的的选择优化，达到数据库效率的高效。