数据库优化

　　　　　　　　PHP高级工程师之MYSQL优化

　　　　在这里和大家分享一下MYSQL整体优化的大致方面。

　　　　如有不善,多提意见（QQ：1595068971-邮箱：1595068971@qq.com）

　　　　我把优化数据库大致分成了这几类，从简单到复杂，相对而言还是比较偏向基础，相信看完本章会对大家在MYSQL优化有一个全面的认识。　

　　　　第一，设计角度（存储引擎，字段类型，设计范式）

　　　　第二，功能角度（索引，查询缓存，分区，分表存储过程）

　　　　第三，SQL语句优化和MYSQL服务器优化

　　　　第四，部署大负载MYSQL架构体系（主从复制，读写分离，主主复制，集群）

　　　　第五，硬件升级

　　下面我们来详细说一下：

　　第一，设计角度（存储引擎，字段类型，设计范式）

　　　　先了解（R / B）概念，通过在数据库中执行 show global status 得到系统当前状态。在这些变量中，形如Com_XXX的语句表示XXX语句执行的次数。如Com_select表示SELECT执行的次数，以此类推。通过计算读类型和写类型语句的比例，既可确定读写比例。理想的比例为100：1当读写比例达到10：1的时候，就认为是以写为主的数据库了。一般来说这个值在30：1左右。

　　存储引擎：

　　　　首先 show engines 查询mysql支持的存储引擎

　　　　　　

　　　　InnoDB:　　

　　　　　　B/W比较小，频繁更新大字段。

　　　　　　表数据量超过1000万，并发高。

　　　　　　安全性和可用性要求高。

　　　　　　注重事物

　　　　　　擅长数据更新和删除，支持事物处理，外键等5.5以后支持全文索引。

　　　　　　InnoDB是一个健壮的事物类型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上 安装的Mysql5.6.13版，InnoDB就是作为默认的存储疫情。InnoDB还引入了行级锁和外键约束，在以下场合下使用InnoDB是最理想的选择。

　　　　　　　　  1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。

　　　　　　　　2.事物。InnoDB存储引擎是支持事物的标准Mysql存储引擎。

　　　　　　　　3.自动灾难恢复。与其他存储引擎不通，InnoDB表能够自动从灾难中恢复。

　　　　　　　　4.外键约束。Mysql支持外键的存储引擎只有InnoDB.

　　　　　　　　5.支持自动增加列AUTO_INCREMENT属性。

　　　　　　一般来说，如果需要事物支持，并且有高校的病发读取频率，InnoDB是不错的选择。

　　　　Myisam:

　　　　　　R/W > 100:1且update较少。

　　　　　　并发不高，不需要事物。

　　　　　　表数据量小。

　　　　　　硬件资源有限。

　　　　　　注重性能

　　　　　　擅长查询和更新操作，不支持事物，支持全文索引。

　　　　　　MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移 植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会 在本地磁盘 上建立三个文件，文件名就是表明。例如，我建立了一个MyISAM引擎的tb_Demo 表，那么就会生成以下三个文件：

　　　　　　1.tb_demo.frm，存储表定义；

　　　　　　2.tb_demo.MYD，存储数据；

　　　　　　3.tb_demo.MYI，存储索引。

　　　　　　MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM 存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

　　　　　　　　1.选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最 突出的优点。

　　　　　　　　2.插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如： MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

　　　　　　MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移 植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会 在本地磁盘 上建立三个文件，文件名就是表明。例如，我建立了一个MyISAM引擎的tb_Demo 表，那么就会生成以下三个文件：

　　　　Memory:

　　　　　　  有足够内存。

　　　　　　对数据一直性要求不高，如在线人数和session等应用。

　　　　　　需要定期归档的数据。

　　　　　　如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关 系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数 量级。

　　　　　　使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用 的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性 能，但当 mysqld守护进程崩溃时，所有的Memory数据都会丢失。获得速度 的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长 度不 变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型， VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不 变 的CHAR类型，所以可以使用。

　　　　　　一般在以下情况下使用Memory存储引擎：

　　　　　　　　1.目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内 存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置 此参数，就可以限制Memory表的最大大小。

　　　　　　　　2.如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。

　　　　　　　　3.存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面 影响。

　　　　　　Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使 用部分查询和通配查询，也可以使用<、>和>=等 操作符方便数据挖掘。散列 索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散 列索引值适合使用在=和<>的操作符中，不 适合在<或>操作符中，也同样不适 合用在order by子句中。

　　第二，功能角度（索引，查询缓存，分区，分表，存储过程）

　　　　索引（InnoDB）是存储引擎快速找到数据的一种数据结构，分为两种单列索引和组合索引。引文的全文索引很简单：英文单词默认是用空格分离的，中文的全文索引很难：中文的词组成很麻烦，需要利用分词工具（sphinx）

　　　　如何选择索引？

　　　　　　1.选择唯一性索引。

　　　　　　　　唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例 如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定 某个学生的信息。如果使用姓名的话，可能存在同名现象，从 而降低 查询速 度。

　　　　　　2.为经常需要排序，分组和联合操作的字段建立索引。

　　　　　　　　经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序 操 作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

　　　　　　3.为常作为查询条件的字段建立索引。

　　　　　　　　如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表 的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

　　　　　　4.限制索引的数目。

　　　　　　　　索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需 要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引会使更新表变得很浪费时间。

　　　　　　5.尽量使用数据量少的索引。

　　　　　　　　如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100） 类型的字段进行全文检索需要的时间肯定要比对CHAR（10）类型的字段需要 的 时间要多。

　　　　　　6.尽量使用前缀来索引

　　　　　　　　如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG 类 型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字 符， 这样可以提高检索速度。

　　　　　　7.删除不再使用或者很少使用的索引。

　　　　　　　　表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引 可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少 索引对更新操作的影响。

　　　　　　注意：选择索引的最终目的是为了查询速度变快，上面给出的原则是最基本的准测，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

　　　　如何查看表结构？

　　　　　　desc 表名。

　　　　如何查看表结构？

　　　　　　show create table 表名。

　　　　如何添加一条索引？

　　　　　　alert table 表名 add unique key(字段)。

　　　　如何添加多条索引？

　　　　　　alert table 表名 add key(字段)，add fulltext key(字段)。

　　　　索引的几种类型：主键索引（Primary），普通索引（Normal-index），唯一索引（Unique），全文索引(Fulltextl)。

　　　　　　1.主键索引（Primary）：（唯一且不能为空，必须指定为“PRIMARY KEY”）。

　　　　　　　　例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

　　　　　　2.普通索引（Normal-index）：（这是最基本的索引，它没有任何限制）。

　　　　　　　　创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表); 修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表); 创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

　　　　　　3.唯一索引（Unique）：（表示唯一，不允许重复）。

　　　　　　4.全文索引（Fulltextl）：（搜索很长一篇文章的时候，效果最好）。

　　　　　　　　它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。

　　　　索引的两种方法：B-TREE HASH

　　　　　　1.B-tree通常意味着所有的值都按顺序存储的，并且每一个叶子页到跟距离相同（适合查找范围数据）

　　　　　　

　　　　　　B-tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的至真，存储引擎根据这些指针向下层查抄。通过比较节点页的值和要查找的值可以找到之和的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到了对应的值，要么该值不存在。

　　　　　　2.Hash..............

　　第三，MYSQL服务器优化和SQL语句优化。

　　　　使用mysql里面的慢查询日志。（默认不开启，默认时间10秒）

　　　　开启慢日志：（http://www.cnblogs.com/lavandachen/articles/1929537.html）

　　　　打开my.ini添加log-slow-queries=”d:/slow-log”和long_query_time=0.5

　　　　超过0.5以上的sql语句会记录并保存。

　　　　查看Mysql慢查询的状态：

　　　　　　show variables like '%slow%';

　　　　查看一共执行过几次慢查询：

　　　　　　show global status like '%slow%';

　　　　修改时间界限：

　　　　　　set long_query_time=时间界限

　　　　开启profiles机制：（记录每条sql语句执行时间）

　　　　　　开启：set profiling=1|0      值为1则是开启，为0则是关闭。

　　　　查看：show profiles

　　　　Mysql服务器优化：

　　　　　　1.关闭不必要的二进制日志和慢查询日志，仅在内存足够或者开发调试时打开他 们。

　　　　　　　　查询是否打开：show variables like '%slow%';

　　　　　 2.查看慢查询的条数，建议间断性打开慢查询日志来定位性能瓶颈

　　　　　　　　show global status like '%slow%';

　　　　　 3.适度使用query cache

　　　　　 4.增加mysql允许最大连接数，

　　　　　　　　查询语句：show variables like 'max_connections';

　　　　　 5.对于MyISAM表适当增加key_buffer_size(指定索引缓冲区的大小),当然这 需要根据key_cache的命中率进行计算

　　　　　　　　show global status like 'key_read%';

　　　　　　　

　　　　　　　　Key_read_requests:有多少个索引读取请求

　　　　　　　Key_reads:有多少个请求在内存中没有找到直接从硬盘读取索引

　　　　　　　　计算方式：

　　　　　　　　　　key_cache_miss_rate = Key_reads / Key_read_requests * 100%

　　　　　　　　所得结果最好尽可能的低，至少是1：100 1：1000更好

　　　　　　6.查看key_buffer_size的大小：

　　　　　　　　show variables like 'key_buffer_size';

　　　　　　7.MySQL服务器还提供了key_blocks_*参数：

　　　　　　　　show global status like 'key_blocks_u%';

　　　　　　　　

　　　　　　　　Key_blocks_unused表示未使用的缓存簇(blocks)数

　　　　　　　　Key_blocks_used表示 曾经用到的最大的blocks数

　　　　　　　　比如这 台服务器，所有的缓存都用到了，要么 增加key_buffer_size，要么就是过渡索引了，把缓存占满了。比较理想的设 置：

　　　　　　　　Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

　　　　　　8.对于MyISAM需要注意table_cache(5.1.3之后叫table_open_cache)

　　　　　　　　able_cache主要用于设置table高速缓存的数量。由于每个客户端 连接都 会至少访问一个表，因此此参数的值与max_connections有关。

　　　　　　　　在执行缓存操作之前，table_cache用于限制缓存表的最大数目：如果当前已 经缓存的表未达到table_cache，则会将新表添加进来；若已经达到此值， MySQL将根据缓存表的最后查询时间、查询率等规则释放之前的缓存。

　　　　　　　　设置过小会反复打开，造成性能损失。

　　　　　　　　设置过大会消耗CPU。

　　　　　　　　因此table_cache的值一定要合理。

　　　　　　9.缓存机制。

　　　　　　　　当某一连接访问一个表时，mysql会检查当前已缓存表的数量。如果该表已经 在缓存中打开，则会直接访问缓存中的表已加快查询速度；如果该表未被缓存， 则会将当前的表添加进缓存并进行查询。

　　　　　　　　执行show global status like 'open%_tables';

　　　　　　　　

　　　　　　　　Open_tables是当前正在打开表的数量

　　　　　　　Opened_tables是所有已经打开表的数量。

　　　　　　　　如果Open_tables的值已经接近table_cache的值，且Opened_tables还在不 断变大，则说明mysql正在将缓存的表释放以容纳新的表，此时可能需要加大 table_cache的值。对于大多数情况，

　　　　　　　　比较适合的值：　　

　　　　　　　　　　Open_tables / Opened_tables >= 0.85

　　　　　　　　　　Open_tables / table_cache <= 0.95

　　　　　　　　如果对此参数的把握不是很准，VPS管理百科给出一个很保守的设置建议：把MySQL数据库放在生产环境中试运行一段时间，然后把参数的值调整得比Opened_tables的数值大一些，并且保证在比较高负载的极端条件下依然比Opened_tables略大。

　　　　　　10.对于InnoDB,需要重点注意innodb_buffer_pool_size参数

　　　　　　　　innodb_buffer_pool_size参数表示缓冲池字节 大小。一般设置为物理内存的70%-80%。InnoDB缓存表和索引数据的内存区域。 mysql默认的值是128M。最大值与你的CPU体系结构有关，在32位操作系统， 最大值是 4294967295 (2^32-1) ，在64 位操作系统，最大值为 18446744073709551615 (2^64-1)。

　　　　SQL语句优化：　

　　　　　　对于同一条语句，第二次查询明显比第一次快，因为Mysql缓存了查询

　　　　　　EXPLAIN Mysql解释它如何处理select。

　　　　　　1.尽量避免在列上运算，会导致索引失效

　　　　　　　　Select * from A WHERE YEAR(d) >= 2017;

　　　　　　　　　　　　　　　　优

　　　　　　　　Select * from A WHERE d >= ‘2017-01-01’;

　　　　　　2.使用join时,应该用小结果集驱动大结果集。同时把复杂的join查询拆分成多个Query.因为join多个表时,可能导致更多的锁定和堵塞

　　　　　　　　Select * from A JOIN B ON A.id=B.id

　　　　　　　　LEFT JOIN C ON C.time=A.time

　　　　　　　　LEFT JOIN D ON D.pid=A.aid

　　　　　　　　LEFT JOIN E ON R.cid=A.did

　　　　　　3.注意like模糊 查询的使用，避免%%

　　　　　　　　Select * from A where name like ‘%de%’;

  　　　　　　　　　　　　　　 优

　　　　　　　　Select * from A where name >=’de’ AND name <’df’;

　　　　　　4.列出需要查询的字段,这对速度不会有影响,主要考虑节省内存。

　　　　　　　　Select * from A;

  　　　　　　　　　　 优

　　　　　　　　Select id,name from A;

　　　　　　5.使用批量插入语句节省交互

　　　　　　　　Insert into A (id,name) VALUES(1,’a’);

　　　　　　　　Insert into A (id,name) VALUES(2,’b’);

   　　　　　　　　　　　　　　优

　　　　　　　　Insert into A (id,name) VALUES(1,’a’),(2,’b’);

　　　　　　6.limide的基数比较大的时候使用between

　　　　　　　　Select * from A limit 10000,10;

   　　　　　　　　　　　　　　优

　　　　　　　　Select * from A where id between 10000 and 10010;

　　　　　　7.避免使用NULL

　　　　　　8.不要使用count(id),而是count(*);

　　　　　　9.不要做无畏的排序操作，而应尽可能在索引中完成排序。

　　第四，部署大负载MYSQL架构体系（主从复制，读写分离，主主复制，集群）

　　　　主从复制：

　　　　　　原理：

　　　　　　　　Bin文件会记录所有修改过数据库的sql语句，多台服务器都开启bin 日志，主服务器把sql语句放进去,从服务器读取然后执行。

　　　　　　　　

　　　　　　　　1.基本配置：

　　　　　　　　　　主服务器配置：

　　　　　　　　　　从服务器配置：

　　　　　　　　　　如果主服务器已有数据，最好是拿备份先还原到从服务器上，然后 再从该还原点进行复制。

　　　　　　　　　　为了保证主从服务器数据的一致性，请不要再从服务器上进行写操 作，除非你知道自己在干什么。

　　　　　　　　2.主服务器配置

　　　　　　　　　　开启bin-log日志，每台服务器设置唯一server-id，重启服务器。

　　　　　　　　　　给从服务器权限：

　　　　　　　　　　　　grant replication slave on *.* to 'wangge'@'101.200.54.120' identified by '123';

　　　　　　　　　　查看user表,权限是否成功：

　　　　　　　　　　　　select user,host,password from mysql.user;

　　　　　　　　　　查看主服务器状态：

　　　　　　　　　　　　Show master status;

　　　　　　　　3.从服务器配置

　　　　　　　　　　执行stop slave停止从服务器。

　　　　　　　　　　　　　　　　

　　　　　　　　　　执行start slave开启从服务器。

　　　　　　　　　　执行show slave status查看从服务器状态。

　　　　　　　　3.撤销从服务器

　　　　　　　　　　Stop slave

　　　　　　　　　　Reset slave all

　　　　　　　　4.总结

　　　　　　　　　　Slave_IO_Running:Yes

　　　　　　　　　　此进程负责从服务器从主服务器上读取binlog日志，并写入从 服务器上的中继日志。

　　　　　　　　　　Slave_SQL_Running:Yes

　　　　　　　　　　此进程负责读取并执行中继日志中的binlog日志。

　　　　　　　　　　两个都为yes表明成功,其中一个是NO,则失败,错误原因从 last_error字段的值看到

　　　　　　　　　　解决主从同步延迟问题：

　　　　　　　　　　搭建一个缓存服务器，把刚更新的数据暂时保存到缓存服务器中

　　　　读写分离：

　　　　　　如需做到读写分离，主从复制。必须手动配置开启主从复制模式

　　　　　　TP框架的读写分离：

　　　　　　在APP/Common/Conf/config.php下。

　　　　　　代码：

1. 1. 1. 　　　　　　<?php
      2. 　　return array(　　
      3. //'配置项'=>'　　配置值'　　
      4. 'URL_CASE INSENSITI　　VE' =>t　　rue,
      5. "DB_DEPLOY_TYPE"=>1， //是否启　　用分布　　式
      6. 'DB_RW_SEPARATE'=>true, //是否启用智能读写　　分离　　
      7. 'DB_TYPE' => 'mysql', //数据库类型　　　　
      8. 　　'DB_HOST' => '192.168.2.1,192.168.2.10', //服务　　器地址
      9. 'DB_NAME' 　　=> 'tp'， //数据库名　　
      10. 'DB_USER' => 'root,root'　　, //用户名　　
      11. 'DB_PWD' => 'root , root', //密码　　　　
      12. 'DB_PREFIX' => 'tpk_', //数据库表前缀　　　　
      13. "project_name"=>"项目名称"，　　　　
      14. 'TMPL_L_DELIM' => '<!--{',　　　　
      15. 'TMPL_R_DELIM' => '}-->',　　　　
      16. 'LAYOUT_ON'=>true,　　　　
      17. );　　　　
      18. ?>　　　　

　　　　　　测试代码：

1. <?　　　　php
2. class IndexAction extends Action {
3. //查询　　　　
4. public function index(){　　　　
5. $articleObj=M("Article");　　　　
6. $rows=$articleObj->select();　　　　
7. dump($rows);　　　　
8. }　　　　
9. //写入　　　　
10. public function add() {　　　　
11.  $articleObj=M("Article");　　　　
12. $data["title"]="读写分离测试"i　　　　
13. $data["add_user"]="ceiba";　　　　
14. $data["area"]="shanghai";　　　　
15.  $data["category"]=" 教育新闻";　　　　
16. $data["content"]="读写分离测试---内容";　　　　
17. if ($articleObj->add($data)) {　　　　
18. $this->success("数据添加成功") ;　　　　
19. }else {　　　　
20.  this->error("数据添加失败");　　　　
21. }　　　　
22. }　　　　
23. }　　　　
24. ?>　　　　

　　　　主主复制：

　　　　集群：

　　第五，硬件升级