mysql 性能优化

malin1857

贡献于2012-09-11

字数:3882 关键词: MySQL 数据库服务器

mysql性能优化 explain分析查询 使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到: – 表的读取顺序 – 数据读取操作的操作类型 – 哪些索引可以使用 – 哪些索引被实际使用 – 表之间的引用 – 每张表有多少行被优化器查询 EXPLAIN字段: ØTable:显示这一行的数据是关于哪张表的 Øpossible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 Økey:实际使用的索引。如果为NULL,则没有使用索引。MYSQL很少会选择优化不足的索引,此时可以在SELECT语句中使用USE INDEX(index)来强制使用一个索引或者用IGNORE INDEX(index)来强制忽略索引 Økey_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好 Øref:显示索引的哪一列被使用了,如果可能的话,是一个常数 Ørows:MySQL认为必须检索的用来返回请求数据的行数 Øtype:这是最重要的字段之一,显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL system、const:可以将查询的变量转为常量.  如id=1; id为 主键或唯一键. eq_ref:访问索引,返回某单一行的数据.(通常在联接时出现,查询使用的索引为主键或惟一键) ref:访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生 range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西,并且该字段上建有索引时发生的情况(注:不一定好于index) index:以索引的顺序进行全表扫描,优点是不用排序,缺点是还要全表扫描 ALL:全表扫描,应该尽量避免 ØExtra:关于MYSQL如何解析查询的额外信息,主要有以下几种 using index:只用到索引,可以避免访问表.  using where:使用到where来过虑数据. 不是所有的where clause都要显示using where. 如以=方式访问索引. using tmporary:用到临时表 using filesort:用到额外的排序. (当使用order by v1,而没用到索引时,就会使用额外的排序) range checked for eache record(index map:N):没有好的索引.   profiling分析查询 通过慢日志查询可以知道哪些SQL语句执行效率低下,通过explain我们可以得知SQL语句的具体执行情况,索引使用等,还可以结合show命令查看执行状态。 如果觉得explain的信息不够详细,可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。 profiling默认是关闭的。可以通过以下语句查看     打开功能: mysql>set profiling=1; 执行需要测试的sql 语句: mysql> show profiles\G; 可以得到被执行的SQL语句的时间和ID mysql>show profile for query 1; 得到对应SQL语句执行的详细信息 Show Profile命令格式: SHOW PROFILE [type [, type] … ]                                         [FOR QUERY n]                                                                 [LIMIT row_count [OFFSET offset]]                              type:                                                                                       ALL                                                                                  | BLOCK IO                                                                         | CONTEXT SWITCHES                                                      | CPU                                                                                 | IPC                                                                                   | MEMORY                                                                               | PAGE FAULTS                                                                  | SOURCE                                                                           | SWAPS                         以上的16rows是针对非常简单的select语句的资源信息,对于较复杂的SQL语句,会有更多的行和字段,比如converting HEAP to MyISAM 、Copying to tmp table等等,由于以上的SQL语句不存在复杂的表操作,所以未显示这些字段。通过profiling资源耗费信息,我们可以采取针对性的优化措施。   测试完毕以后 ,关闭参数:mysql> set profiling=0     2     索引及查询优化   索引的类型 Ø 普通索引:这是最基本的索引类型,没唯一性之类的限制。 Ø 唯一性索引:和普通索引基本相同,但所有的索引列值保持唯一性。 Ø 主键:主键是一种唯一索引,但必须指定为”PRIMARY KEY”。 Ø 全文索引:MYSQL从3.23.23开始支持全文索引和全文检索。在MYSQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。 大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B树中存储。空间列类型的索引使用R-树,MEMORY表支持hash索引。 单列索引和多列索引(复合索引) 索引可以是单列索引,也可以是多列索引。对相关的列使用索引是提高SELECT操作性能的最佳途径之一。 多列索引: MySQL可以为多个列创建索引。一个索引可以包括15个列。对于某些列类型,可以索引列的左前缀,列的顺序非常重要。 多列索引可以视为包含通过连接索引列的值而创建的值的排序的数组。一般来说,即使是限制最严格的单列索引,它的限制能力也远远低于多列索引。 最左前缀 多列索引有一个特点,即最左前缀(Leftmost Prefixing)。假如有一个多列索引为key(firstname lastname age),当搜索条件是以下各种列的组合和顺序时,MySQL将使用该多列索引: firstname,lastname,age firstname,lastname firstname 也就是说,相当于还建立了key(firstname lastname)和key(firstname)。 索引主要用于下面的操作: Ø 快速找出匹配一个WHERE子句的行。 Ø 删除行。当执行联接时,从其它表检索行。 Ø 对具体有索引的列key_col找出MAX()或MIN()值。由预处理器进行优化,检查是否对索引中在key_col之前发生所有关键字元素使用了WHERE key_part_# = constant。在这种情况下,MySQL为每个MIN()或MAX()表达式执行一次关键字查找,并用常数替换它。如果所有表达式替换为常量,查询立即返回。例如: SELECT MIN(key2), MAX (key2)  FROM tb WHERE key1=10; Ø 如果对一个可用关键字的最左面的前缀进行了排序或分组(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。如果所有关键字元素后面有DESC,关键字以倒序被读取。 Ø 在一些情况中,可以对一个查询进行优化以便不用查询数据行即可以检索值。如果查询只使用来自某个表的数字型并且构成某些关键字的最左面前缀的列,为了更快,可以从索引树检索出值。 SELECT key_part3 FROM tb WHERE key_part1=1 有时MySQL不使用索引,即使有可用的索引。一种情形是当优化器估计到使用索引将需要MySQL访问表中的大部分行时。(在这种情况下,表扫描可能会更快些)。然而,如果此类查询使用LIMIT只搜索部分行,MySQL则使用索引,因为它可以更快地找到几行并在结果中返回。例如:   合理的建立索引的建议: (1)  越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。  (2)  简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。 (3)  尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值   这部分是关于索引和写SQL语句时应当注意的一些琐碎建议和注意点。 1. 当结果集只有一行数据时使用LIMIT 1 2. 避免SELECT *,始终指定你需要的列 从表中读取越多的数据,查询会变得更慢。他增加了磁盘需要操作的时间,还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟,仅仅是因为数据不必要的在服务器之间传输。 3. 使用连接(JOIN)来代替子查询(Sub-Queries)        连接(JOIN).. 之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。 4. 使用ENUM、CHAR 而不是VARCHAR,使用合理的字段属性长度 5. 尽可能的使用NOT NULL 6. 固定长度的表会更快 7. 拆分大的DELETE 或INSERT 语句 8. 查询的列越小越快    Where条件 在查询中,WHERE条件也是一个比较重要的因素,尽量少并且是合理的where条件是很重要的,尽量在多个条件的时候,把会提取尽量少数据量的条件放在前面,减少后一个where条件的查询时间。 有些where条件会导致索引无效: Ø where子句的查询条件里有!=,MySQL将无法使用索引。 Ø where子句使用了Mysql函数的时候,索引将无效,比如:select * from tb where left(name, 4) = ‘xxx’ Ø 使用LIKE进行搜索匹配的时候,这样索引是有效的:select * from tbl1 where name like ‘xxx%’,而like ‘%xxx%’ 时索引无效    

下载文档,方便阅读与编辑

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

需要 5 金币 [ 分享文档获得金币 ]
1 人已下载

下载文档

相关文档