mysql性能优化


MySql性能优化 1. 为查询缓存优化你的查询为查询缓存优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相 同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果 了。 这里最主要的问题是,对于程序员来说,这个事情是很容易被忽略的。因为,我们某些查询语句会让MySQL不使用缓存。请看下面 的示例: 1. // 查询缓存不开启 2. $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); 3. // 开启查询缓存 4. $today = date("Y-m-d"); 5. $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'"); 上面两条SQL语句的差别就是 CURDATE() ,MySQL的查询缓存对这个函数不起作用。所以,像 NOW() 和 RAND() 或是其它的诸 如此类的SQL函数都不会开启查询缓存,因为这些函数的返回是会不定的易变的。所以,你所需要的就是用一个变量来代替MySQL 的函数,从而 开启缓存。 2. EXPLAIN 你的你的 SELECT 查询查询 使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。 EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的……等等,等等。 挑一个你的SELECT语句(推荐挑选那个最复杂的,有多表联接的),把关键字EXPLAIN加到前面。你可以使用phpmyadmin来做这 个事。然后,你会看到一张表格。 3. 当只要一行数据时使用当只要一行数据时使用 LIMIT 1 4. 为搜索字段建索引为搜索字段建索引 索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么,请为其建立索引吧。 从上图你可以看到那个搜索字串 “last_name LIKE ‘a%’”,一个是建了索引,一个是没有索引,性能差了4倍左右。 另外,你应该也需要知道什么样的搜索是不能使用正常的索引的。例如,当你需要在一篇大的文章中搜索一个词时,如: “WHERE post_content LIKE ‘%apple%’”,索引可能是没有意义的。你可能需要使用MySQL全文索引 或是自己做一个索引(比如说:搜索关键 词或是Tag什么的) 谨慎合理使用索引 改善查询、减慢更新 索引一定不是越多越好(能不加就不加,要加的一定得加) 覆盖记录条数过多不适合建索引,例如“性别” 5. 在在Join表的时候使用相当类型的例,并将其索引表的时候使用相当类型的例,并将其索引 如果你的应用程序有很多 JOIN 查询,你应该确认两个表中Join的字段是被建过索引的。这样,MySQL内部会启动为你优化Join的 SQL语句的机制。 而且,这些被用来Join的字段,应该是相同的类型的。例如:如果你要把 DECIMAL 字段和一个 INT 字段Join在一起,MySQL就无 法使用它们的索引。对于那些STRING类型,还需要有相同的字符集才行。(两个表的字符集有可能不一样) 1. // 在state中查找company 2. $r = mysql_query("SELECT company_name FROM users 3. LEFT JOIN companies ON (users.state = companies.state) 4. WHERE users.id = $user_id"); 5. // 两个 state 字段应该是被建过索引的,而且应该是相当的类型,相同的字符集。 6. 千万不要千万不要 ORDER BY RAND() 想打乱返回的数据行?随机挑一个数据?真不知道谁发明了这种用法,但很多新手很喜欢这样用。但你确不了解这样做有多么可怕 的性能问题。 如果你真的想把返回的数据行打乱了,你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的 问题是:MySQL会不得 不去执行RAND()函数(很耗CPU时间),而且这是为了每一行记录去记行,然后再对其排序。就算是你用 了Limit 1也无济于事(因为要排序) 下面的示例是随机挑一条记录 1. // 千万不要这样做: 2. $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1"); 3. // 这要会更好: 4. $r = mysql_query("SELECT count(*) FROM user"); 5. $d = mysql_fetch_row($r); 6. $rand = mt_rand(0,$d[0] - 1); 7. $r = mysql_query("SELECT username FROM user LIMIT $rand, 1"); 7. 避免避免 SELECT * 从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和WEB服务器是两台独立的服务器的话,这还 会增加网络传输的负载。 8. 永远为每张表设置一个永远为每张表设置一个ID 9. 使用使用 ENUM 而不是而不是 VARCHAR ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选 项列表变得相当的完美。 如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,你知道这些字段的取值是有限而且固定的,那么,你应该使 用 ENUM 而不是 VARCHAR。 10. 从从 PROCEDURE ANALYSE() 取得建议取得建议 PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据,并会给你一些有用的建议。只有表中有实际的数 据,这些建议才会变得有用,因为要做一些大的决定是需要有数据作为基础的。 例如,如果你创建了一个 INT 字段作为你的主键,然而并没有太多的数据,那么,PROCEDURE ANALYSE()会建议你把这个字段 的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段,因为数据不多,你可能会得到一个让你把它改成 ENUM 的建议。 这些建议,都是可能因为数据不够多,所以决策做得就不够准。 在phpmyadmin里,你可以在查看表时,点击 “Propose table structure” 来查看这些建议 一定要注意,这些只是建议,只有当你的表里的数据越来越多时,这些建议才会变得准确。一定要记住,你才是最终做决定的人。 11. 尽可能的使用尽可能的使用 NOT NULL 除非你有一个很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议,请往下看。 首先,问问你自己“Empty”和“NULL”有多大的区别(如果是INT,那就是0和NULL)?如果你觉得它们之间没有什么区别,那么你 就不要使用NULL。(你知道吗?在 Oracle 里,NULL 和 Empty 的字符串是一样的!) 不要以为 NULL 不需要空间,其需要额外的空间,并且,在你进行比较的时候,你的程序会更复杂。 当然,这里并不是说你就不 能使用NULL了,现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。 下面摘自MySQL自己的文档: 1. “NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.” 避免使用NULL字段 NULL字段很难查询优化 NULL字段的索引需要额外空间 NULL字段的复合索引无效 bad case:`name` char(32) default null`age` int not null good case:`age` int not null default 0 12. 把把IP地址存成地址存成 UNSIGNED INT 很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放,只需要4个字节,并且 你可以有定长的字段。而且,这会为你带来查询上的优势,尤其是当 你需要使用这样的WHERE条件:IP between ip1 and ip2。 我们必需要使用UNSIGNED INT,因为 IP地址会使用整个32位的无符号整形。 而你的查询,你可以使用 INET_ATON() 来把一个字符串IP转成一个整形,并使用 INET_NTOA() 把一个整形转成一个字符串IP。 在PHP中,也有这样的函数 ip2long() 和 long2ip()。 1. $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id"; 尽可能地使用最有效(最小)的数据类型 tinyint(1Byte) smallint(2Byte) mediumint(3Byte) int(4Byte) bigint(8Byte) bad case:int(1)/int(11) 13. 垂直分割垂直分割 “垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。(以 前,在银行做过项目,见过一张表有100多个字段,很恐怖) 示例一:在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个 人信息外,你并 不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢? 这样会让你的表有更好的性能,大家想想是不是, 大量的时候,我对于用户表来说,只有用户ID,用户名,口令,用户角色等会被经常使用。小一点的表总是会有 好的性能。 示例二: 你有一个叫 “last_login” 的字段,它会在每次用户登录时被更新。但是,每次更新时会导致该表的查询缓存被清空。所 以,你可以把这个字段放到另一个表中,这样就不会影响你对用户ID,用户名,用户角色的不停地读取了,因为查询缓存会帮你增 加很多性能。 另外,你需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去Join他们,不然的话,这样的性能会比不分割时还要 差,而且,会是极数级的下降。 14. 拆分大的拆分大的 DELETE 或或 INSERT 语句语句 如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停 止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。 Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接, 这是极大的占服务器资源的事情,尤其是内存。 如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链 接,打开的文件数,可能不仅仅会让你泊WEB服务Crash,还可能会让你的整台服务器马上掛了。 所以,如果你有一个大的处理,你定你一定把其拆分,使用 LIMIT 条件是一个好的方法。下面是一个示例: 1. while (1) { 2. //每次只做1000条 3. mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000"); 4. if (mysql_affected_rows() == 0) { 5. // 没得可删了,退出! 6. break; 7. } 8. // 每次都要休息一会儿 9. usleep(50000); 10. } 15. 越小的列会越快越小的列会越快 对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。所以,把你的数据变得紧凑会对这种情况非常有帮助,因为这减少 了对硬盘的访问。 参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。 如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。 当然,你也需要留够足够的扩展空间,不然,你日后来干这个事,你会死的很难看,参看Slashdot的例子(2009年11月06日),一个 简单的ALTER TABLE语句花了3个多小时,因为里面有一千六百万条数据。 16. 选择正确的存储引擎选择正确的存储引擎 在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论 和这个事情。 MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁 起来,而别的进程,就算是读进程都 无法操作直到读操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比 的。 InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。他是它支持“行锁” ,于是在写操作比较 多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。 17. 使用一个对象关系映射器(使用一个对象关系映射器(Object Relational Mapper)) 使用 ORM (Object Relational Mapper),你能够获得可靠的性能增涨。一个ORM可以做的所有事情,也能被手动的编写出来。但是, 这需要一个高级专家。 ORM 的最重要的是“Lazy Loading”,也就是说,只有在需要的去取值的时候才会去真正的去做。但你也需要小心这种机制的副作 用,因为这很有可能会因为要去创建很多很多小的查询反而会降低性能。 ORM 还可以把你的SQL语句打包成一个事务,这会比单独执行他们快得多得多。 目前,个人最喜欢的PHP的ORM是:Doctrine。 18.mysql 索引索引 索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高 多行检索的速度,而非聚簇索引对于单行的检索很快。 MySQL索引的类型 1. 普通索引普通索引 这是最基本的索引,它没有任何限制,比如上文中为title字段创建的索引就是一个普通索引,MyIASM中默认的BTREE类型的索引, 也是我们大多数情况下用到的索引。 2. 唯一索引唯一索引 与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯 一,创建方法和普通索引类似。 3. 全文索引(全文索引(FULLTEXT)) MySQL从3.23.23版开始支持全文索引和全文检索,FULLTEXT索引仅可用于 MyISAM 表;他们可以从CHAR、VARCHAR或TEXT列中 作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加。////对于较大的数据集,将你的 资料输入一个没有FULLTEXT索引的表中,然后创建索引,其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大 容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。 4. 单列索引、多列索引单列索引、多列索引 多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严 格的索引。 5. 组合索引(最左前缀)组合索引(最左前缀) 平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。例如上表中针对 title和time建立一个组合索引:ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立这样的组合索引,其 实是相当于分别建立了下面两组组合索引: –title,time –title 为什么没有time这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是 只要包含这两列的查询都会用到该组合索引,如下面的几个SQL所示: 19.MySQL的的btree索引和索引和hash索引的区别索引的区别 hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问 到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引。 可 能很多人又有疑问了,既然 Hash 索引的效率要比 B-Tree 高很多,为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢?任 何事物都是有两面性的,Hash 索引也一样,虽然 Hash 索引效率高,但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端, 主要有以下这些。 (1)Hash 索引仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询。 由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应 的 Hash 算法处理之后的 Hash 值的大小关系,并不能保证和Hash运算前完全一样。 (2)Hash 索引无法被用来避免数据的排序操作。 由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值,而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样,所 以数据库无法利用索引的数据来避免任何排序运算; (3)Hash 索引不能利用部分索引键查询。 对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值,所以通过组 合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。 (4)Hash 索引在任何时候都不能避免表扫描。 前面已经知道,Hash 索引是将索引键通过 Hash 运算之后,将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中,由于不同索引键存在相同 Hash 值,所以即使取满足某个 Hash 键值的数据的记录条数,也无法从 Hash 索引中直接完成查 询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。 (5)Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。 对于选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一 条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下。 20.MySQL索引的优化索引的优化 1. 何时使用聚集索引或非聚集索引?何时使用聚集索引或非聚集索引? 动作描述动作描述 使用聚集索引使用聚集索引 使用非聚集索引使用非聚集索引 列经常被分组排序 使用 使用 返回某范围内的数据 使用 不使用 一个或极少不同值 不使用 不使用 小数目的不同值 使用 不使用 大数目的不同值 不使用 使用 频繁更新的列 不使用 使用 外键列 使用 使用 主键列 使用 使用 频繁修改索引列 不使用 使用 事实上,我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如:返回某范围内的数据一项。比如您的某个表有一 个时间列,恰好您把聚合索引建立在了该列,这时您查询2004年1月1日至2004年10月1日之间的全部数据时,这个速度就将是很快 的,因为您的这本字典正文是按日期进行排序的,聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集 索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容。其实这个具体用法我还不是很理解,只能等 待后期的项目开发中慢慢学学了。 2. 索引不会包含有索引不会包含有NULL值的列值的列 只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。 所以我们在数据库设计时不要让字段的默认值为NULL。 3. 使用短索引使用短索引 对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟 一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。 4. 索引列排序索引列排序 MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认 排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。 5. like语句操作语句操作 一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索 引。 6. 不要在列上进行运算不要在列上进行运算 例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可 以改成:select * from users where adddate<’2007-01-01′。关于这一点可以围观:一个单引号引发的MYSQL性能损失。 http://blog.jobbole.com/86594/ 21.
还剩6页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

wuguan

贡献于2016-09-23

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf