Mysql的优化策略，提升PHP的运行效率

为了提升PHP的运行效率，程序员不光需要写出逻辑清晰，效率很高的代码，还要能对query语句进行优化。虽然我们对数据库的读取写入速度上却是无能为力，但在一些数据库类扩展像memcache、mongodb、redis这样的数据存储服务器的帮助下，PHP也能达到更快的存取速度，所以了解学习这些扩展也是非常必要。

大型存储方面优化

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

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上。
水平切分：在表不多，而表里的数据量非常大的时候，为了加快查询，可以用哈希等算法，将一个数据表分为几个，分别放到不同的服务器上，加快查询。水平切分和数据表分区的区别在于其存储介质上的不同。
联合切分：更多的情况是数据表和表中的数据量都非常大，则要进行联合切分，即同时进行垂直和水平分表，将数据库切分为一个分布式的矩阵来存储。
这些数据库的优化方式，每一种拿出来都可以写作一篇文章，可谓是博大精深，了解并记忆了这些方式，可以在有需要的时候进行有目的的选择优化，达到数据库效率的高效。

索引方面优化

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，下面主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

图1

这里设表一共有三列，假设我们以Col1为主键，则图1是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

 

图2 

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

图3

图3是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图4为定义在Col3上的一个辅助索引：

图4

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

数据查询方面优化

在每一个消耗大量时间的查询案例中，都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

一、首选要优化数据访问

查询性能底下最基本的原因是访问的数据太多。所以，对于低效的查询，一般通过两个步骤来分析：

确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。确认MySQL服务器层是否在分析大量超过需要的数据行。

1.1、是否向数据库请求了不需要的数据

在访问数据库时，应该只请求需要的行和列，请求多余的行和列会消耗MySQL服务器的CPU和内存资源，并增加网络开销。

1、在处理分页时，应该使用LIMIT限制MySQL只返回需要的数据，而不是向应用程序返回全部数据后，再由应用程序过滤不需要的行。

2、多表关联时，或获取单表数据时，尽量避免不加思考地使用SELECT *

3、当一些数据被多次使用时可以考虑将数据缓存起来，避免每次使用都要到MySQL查询。

1.2、MySQL是否在扫描额外的记录，应该让MySQL使用最合适的方式查询数据

对于MySQL，最简单的衡量查询开销有三个指标：响应时间、扫描的行数和返回的行数。这里主要考虑提高扫描的方式，即查询数据的方式。

查询数据的方式有全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。这些查询方式，速度从慢到快，扫描的行数也是从多到少。可以通过EXPLAIN语句中的type列反应查询采用的是哪种方式。

通常可以通过添加合适的索引改善查询数据的方式，使其尽可能减少扫描的数据行，加快查询速度。

例如，当发现查询需要扫描大量的数据行但只返回少数的行，那么可以考虑使用覆盖索引，即把所有需要用到的列都放到索引中。这样存储引擎无须回表获取对应行就可以返回结果了。

二、重构查询的方法

设计查询的时候需要考虑是否需要把一个复杂的查询分成多个简单的查询。在我的印象中，曾经无数次听到一个经验法则：可以在数据库中做的事不要放在应用程序中，数据库比我们想象的要厉害的多。这个经验法则是在华夏基金使用Oracle编写SQL时一位Oracle牛人告诉我的，后来我把它使用到MySQL上，真是吃尽苦头。

当然这其中的原因有Oracle和MySQL原本就不是一样的处理逻辑，并且现在的网络通信、查询解析和优化的代价并没有以前那么高啦。再次说明，经验法则有在某种特定笼子里才有效。

分解复杂的查询：

可以将一个大查询切分成多个小查询执行，每个小查询只完成整个查询任务的一小部分，每次只返回一小部分结果。

删除旧的数据是一个很好的例子。

如果只用一条语句一次性执行一个大的删除操作，则可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源、阻塞很多小的但重要的查询。将一个大的删除操作分解成多个较小的删除操作可以将服务器上原本一次性的压力分散到多次操作上，尽可能小地影响MySQL性能，减少删除时锁的等待时间，同时也减少了MySQL主从复制的延迟。这个方法我一直在用。

另一个例子是分解关联查询，即对每个要关联的表进行单表查询，然后将结果在应用程序中进行关联。我在之前一家公司和一位在阿里待过很多年的同事一起编码时，他就是这么干的。后来我在心中默默地鄙视着他，因为我心里有这么一个经验法则（可以在数据库中做的事不要放在应用程序中，数据库比我们想象的要厉害的多），并且我在行动上也是保持能用一个SQL解决的事绝对不会用两个SQL。

这么做当然处理经验法则的原因之外还有一个原因是：获取数据的逻辑尽量与业务代码分离，这样以后在切换数据库时也很方便。实际上是这样吗？未必啊。那次的无知让我吃尽苦头啊，后来因为SQL的性能问题再把我写的大部分SQL进行分解。

用分解关联查询的方式重构查询有如下的优势：

让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。将查询分解后，执行单个查询可以减少锁的竞争。在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。查询本身效率也可能会有所提升。可以减少冗余记录的查询。在应用层做关联查询，

意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

数据库设计方面优化

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支持

几条MySQL小技巧

1、SQL语句中的关键词最好用大写来书写，第一易于区分关键词和操作对象，第二，SQL语句在执行时，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后紧跟用户名密码，无空格）。

以上内容希望帮助到大家，有需要的可以添加下方二维码进群交流学习新技术。