AutoCAD 3DMAX C语言 Pro/E UG JAVA编程 PHP编程 Maya动画 Matlab应用 Android
Photoshop Word Excel flash VB编程 VC编程 Coreldraw SolidWorks A Designer Unity3D
 首页 > MySQL

19个MySQL性能优化要点解析

51自学网 2016-09-18 http://www.51zixue.net
 
14、垂直分割
 “垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。(以前,在银行做过项目,见过一张表有100多个字段,很恐怖)
 
示例一:在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢? 这样会让你的表有更好的性能,大家想想是不是,大量的时候,我对于用户表来说,只有用户ID,用户名,口令,用户角色等会被经常使用。小一点的表总是会有好的性能。
 
示例二: 你有一个叫 “last_login” 的字段,它会在每次用户登录时被更新。但是,每次更新时会导致该表的查询缓存被清空。所以,你可以把这个字段放到另一个表中,这样就不会影响你对用户 ID,用户名,用户角色的不停地读取了,因为查询缓存会帮你增加很多性能。
 
另外,你需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去Join他们,不然的话,这样的性能会比不分割时还要差,而且,会是极数级的下降。
 
15、拆分大的 DELETE 或 INSERT 语句
 如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。
 
Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。
 
如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你泊WEB服务Crash,还可能会让你的整台服务器马上挂了。
 
所以,如果你有一个大的处理,你定你一定把其拆分,使用 LIMIT 条件是一个好的方法。下面是一个示例:
 
while (1) { //每次只做1000条 mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000"); if (mysql_affected_rows() == 0) {     // 没得可删了,退出!     break; } // 每次都要休息一会儿 usleep(50000); }
 
16、 越小的列会越快
 对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。所以,把你的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问。
 
参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。
 
如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。
 
当然,你也需要留够足够的扩展空间,不然,你日后来干这个事,你会死的很难看,参看Slashdot的例子(2009年11月06日),一个简单的ALTER TABLE语句花了3个多小时,因为里面有一千六百万条数据。
 
17、选择一个正确的存储引擎
 在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情。
 
MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。
 
InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。他是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。
 
18、小心“永久链接”
 “永久链接”的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了,它会永远处在连接的状态,就算是数据库操作已经结束了。而且,自从我们的 Apache开始重用它的子进程后——也就是说,下一次的HTTP请求会重用Apache的子进程,并重用相同的 MySQL 链接。
 
PHP手册:mysql_pconnect()
 
在理论上来说,这听起来非常的不错。但是从个人经验(也是大多数人的)上来说,这个功能制造出来的麻烦事更多。因为,你只有有限的链接数,内存问题,文件句柄数,等等。
 
而且,Apache 运行在极端并行的环境中,会创建很多很多的了进程。这就是为什么这种“永久链接”的机制工作地不好的原因。在你决定要使用“永久链接”之前,你需要好好地考虑一下你的整个系统的架构。
 
19、当查询较慢的时候,可用Join来改写一下该查询来进行优化
 
mysql> select sql_no_cache * from guang_deal_outs where deal_id in (select id from guang_deals where id = 100017151) ; Empty set (18.87 sec)  mysql> select sql_no_cache a.* from guang_deal_outs a inner join guang_deals b on a.deal_id = b.id where b.id = 100017151;  Empty set (0.01 sec) 
 
原因
 
 
mysql> desc select sql_no_cache * from guang_deal_outs where deal_id in (select id from guang_deals where id = 100017151) ; +----+--------------------+-----------------+-------+---------------+---------+---------+-------+----------+-------------+ | id | select_type  | table   | type | possible_keys | key  | key_len | ref | rows  | Extra  | +----+--------------------+-----------------+-------+---------------+--------- +---------+-------+----------+-------------+ | 1 | PRIMARY   | guang_deal_outs | ALL | NULL   | NULL |  NULL | NULL | 18633779 | Using where | | 2 | DEPENDENT SUBQUERY | guang_deals  | const | PRIMARY  | PRIMARY |  4  | const |  1 | Using index | +----+--------------------+-----------------+-------+---------------+--------- +---------+-------+----------+-------------+ 2 rows in set (0.04 sec) mysql> desc select sql_no_cache a.* from guang_deal_outs a inner join guang_deals b on a.deal_id = b.id where b.id = 100017151; +----+-------------+-------+-------+---------------------- +----------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys  | key      | key_len | ref | rows | Extra  | +----+-------------+-------+-------+---------------------- +----------------------+---------+-------+------+-------------+ | 1 | SIMPLE  | b  | const | PRIMARY    | PRIMARY     | 4  | const | 1 | Using index | | 1 | SIMPLE  | a  | ref | idx_guang_dlout_dlid |  idx_guang_dlout_dlid | 4  | const | 1 |    | +----+-------------+-------+-------+---------------------- +----------------------+---------+-------+------+-------------+ 2 rows in set (0.05 sec) 
 
其实在  guang_deal_outs 在deal_id 上也是有索引的。 其实我想把子查询设置为
 
 
select * from guang_deal_outs where deal_id in (select id from guang_deals where id = 100017151); 
 
变成下面的样子
 
 
select * from guang_deal_outs where deal_id in (100017151); 
 
但不幸的是,实际情况正好相反。MySQL试图让它和外面的表产生联系来“帮助”优化查询,它认为下面的exists形式更有效率
 
select * from guang_deal_outs where exists (select * from guang_deals where id = 100017151 and id = guang_deal_outs.deal_id); 
 
这种in子查询的形式,在外部表(比如上面的guang_deals)数据量比较大的时候效率是很差的(如果对于较小的表,不会造成显著地影响)
 
以上就是MySQL性能优化19个要点解析,希望对大家优化MySQL性能有所帮助。
 

 
上一篇:MYSQL 数据库导入导出命令  下一篇:MySQL性能优化的一些技巧帮助你的数据库