mysql性能优化.ppt

1、MySQL优化,MySQL优化方式 MySQL技巧分享 MySQL函数,目录索引,MySQL优化方式,系统优化：硬件、架构 服务优化 应用优化,MySQL优化方式,使用好的硬件，更快的硬盘、大内存、多核CPU，专业的存储服务器（NAS、SAN） 设计合理架构，如果 MySQL 访问频繁，考虑 Master/Slave 读写分离；数据库分表、数据库切片（分布式），也考虑使用相应缓存服务帮助 MySQL 缓解访问压力,系统优化,配置合理的MySQL服务器，尽量在应用本身达到一个MySQL最合理的使用 针对 MyISAM 或 InnoDB 不同引擎进行不同定制性配置 针对不同的应用情况进行合理配置

2、针对 f 进行配置，后面设置是针对内存为2G的服务器进行的合理设置,服务优化,MySQL配置原则,服务优化,公共选项,服务优化,MyISAM 选项,服务优化,InnoDB 选项,设计合理的数据表结构 对数据表建立合适有效的数据库索引 数据查询：编写简洁高效的SQL语句,应用优化,应用优化方式,应用优化,表结构设计原则 选择合适的数据类型：如果能够定长尽量定长 使用 ENUM 而不是 VARCHAR,ENUM类型是非常快和紧凑的，在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美 。 不要使用无法加索引的类型作为关键字段，比如 tex

3、t类型 为了避免联表查询，有时候可以适当的数据冗余，比如 邮箱、姓名这些不容易更改的数据 选择合适的表引擎，有时候 MyISAM 适合，有时候 InnoDB适合 为保证查询性能，最好每个表都建立有 auto_increment 字段， 建立合适的数据库索引 最好给每个字段都设定 default 值,应用优化,索引建立原则(一) 一般针对数据分散的关键字进行建立索引，比如ID、QQ， 像性别、状态值等等建立索引没有意义 字段唯一，最少，不可为null 对大数据量表建立聚集索引，避免更新操作带来的碎片。 尽量使用短索引，一般对int、char/varchar、date/time 等 类型的字段建立

4、索引 需要的时候建立联合索引，但是要注意查询SQL语句的编写 谨慎建立 unique 类型的索引（唯一索引） 大文本字段不建立为索引，如果要对大文本字段进行检索， 可以考虑全文索引 频繁更新的列不适合建立索引 一般建议每条记录最好有一个能快速定位的独一无二定位的 唯一标示（索引） 不要过度索引，单表建立的索引不要超过5个，否则更新索 引将很耗时,应用优化,索引建立原则(二) 1. order by 字句中的字段，where 子句中字段，最常用的sql 语句中字段，应建立索引。 2.唯一性约束，系统将默认为改字段建立索引。 3.对于只是做查询用的数据库索引越多越好，但对于在线实时 系统建议控制在

5、5个以内。 4.索引不仅能提高查询SQL性能，同时也可以提高带where字句 的update，Delete SQL性能。 5.Decimal 类型字段不要单独建立为索引，但覆盖索引可以包 含这些字段。 6.只有建立索引以后，表内的行才按照特地的顺序存储，按照 需要可以是asc或desc方式。 7.如果索引由多个字段组成将最用来查询过滤的字段放在前面 可能会有更好的性能。,应用优化,MYSQL 执行顺序 (8) SELECT (9) DISTINCT (11) (1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) WITH CUBE | ROLL

6、UP (7) HAVING (10) ORDER BY ,应用优化,编写高效的 SQL(一) 能够快速缩小结果集的 WHERE 条件写在前面，如果有恒量条件， 也尽量放在前面 尽量避免使用 GROUP BY、DISTINCT 、OR、IN 等语句的使用， 避免使用联表查询和子查询，因为将使执行效率大大下降 能够使用索引的字段尽量进行有效的合理排列，如果使用了 联合索引，请注意提取字段的前后顺序 针对索引字段使用 , =, =, , =, IF NULL和BETWEEN 将会使用 索引， 如果对某个索引字段进行 LIKE 查询，使用 LIKE %abc% 不能使用索引，使用 LIKE abc%

7、将能够使用索引 如果在SQL里使用了MySQL部分自带函数，索引将失效，同时将无法 使用 MySQL 的 Query Cache，比如 LEFT(), SUBSTR(), TO_DAYS() DATE_FORMAT(), 等，如果使用了 OR 或 IN，索引也将失效 使用 Explain 语句来帮助改进我们的SQL语句,应用优化,编写高效的 SQL(二) 1.不要在where 子句中的“=”左边进行算术或表达式运算，否则系统将 可能无法正确使用索引 select id from uinfo_jifen where jifen/60 10000;优化后:Select id from uinfo_

8、jifen where jifen600000; 2.尽量不要在where条件中使用函数，否则将不能使用索引(select uid from imid where datediff(create_time,2011-11-22)=0优化后select uid from imid where create_time =2011-11-21 and create_time2011-11-23;) 3.避免使用 select *, 只取需要的字段 4.对于大数据量的查询，尽量避免在SQL语句中使用order by 字句，避免 额为的开销 5.如果插入的数据量很大，用select into 替代 in

9、sert into 能带来更好的性能 6.采用连接操作，避免过多的子查询，产生的CPU和IO开销 7.只关心需要的表和满足条件的数据 8.适当使用临时表或表变量 9.对于连续的数值，使用between代替in 11.尽量不用触发器，10. where 字句中尽量不要使用CASE条件 特别是在大数据表上,应用优化,编写高效的 SQL(三) 1.更新触发器如果不是所有情况下都需要触发，应根据业务需要加 上必要判断条件 2.使用union all 操作代替OR操作，注意此时需要注意一点查询条 件可以使用聚集索引，如果是非聚集索引将起到相反的结果 3.当只要一行数据时使用 LIMIT 1 4.尽可能的

10、使用 NOT NULL填充数据库 5.拆分大的 DELETE 或 INSERT 语句,使用 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); 6.批量提交SQL语句,MySQL技巧分享,MySQL技巧分享,常用技巧 使用 SHOW PROCESSLIST 来查看当前MySQL服务器

11、线程 执行情况，是否锁表，查看相应的SQL语句 设置 my.ini 中的 long-query-time 和 log-slow-queries 能够记 录服务器哪些SQL执行速度比较慢 使用 DESC TABLE xxx 来查看表结构，使用 SHOW INDEX FROM xxx 来查看表索引 使用 LOAD DATA 导入数据比 INSERT INTO 快多了 SELECT COUNT(*) FROM Tbl 在 InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。,MySQ

12、L技巧分享,SELECT查询的速度 要想使一个较慢速SELECT . WHERE更快，应首先检查是否能增加一个索引。你可以使用EXPLAIN语句来确定SELECT语句使用哪些索引。 为了帮助MySQL更好地优化查询，在一个装载数据后的表上运行ANALYZE TABLE或myisamchk -analyze。这样为每一个索引更新指出有相同值的行的平均行数的值（当然，如果只有一个索引，这总是1。）MySQL使用该方法来决定当你联接两个基于非常量表达式的表时选择哪个索引。你可以使用SHOW INDEX FROM tbl_name并检查Cardinality值来检查表分析结果。myisamchk -d

13、escription -verbose可以显示索引分布信息。 要想根据一个索引排序一个索引和数据，使用myisamchk -sort-index -sort-records=1（如果你想要在索引1上排序）。如果只有一个索引，想要根据该索引的次序读取所有的记录，这是使查询更快的一个好方法。但是请注意，第一次对一个大表按照这种方法排序时将花很长时间！,MySQL技巧分享,使用 Explain 语句来帮助改进我们的SQL语句 如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。 EXPLAIN的每个输出行提供一个表的相关信息，并且

14、每个行包括下面的列:,MySQL技巧分享,MySQL 技巧分享,Explain效率检测使用 语法：EXPLAIN SELECT select_options Type: 类型，是否使用了索引还是全表扫描, const,eg_reg,ref,range,index,ALL Key: 实际使用上的索引是哪个字段 Ken_len: 真正使用了哪些索引，不为 NULL 的就是真实使用的索引Ref: 显示了哪些字段或者常量被用来和 key 配合从表中查询记录出来 Rows: 显示了MySQL认为在查询中应该检索的记录数 Extra: 显示了查询中MySQL的附加信息，关心Using filesort 和

15、 Using temporary，性能杀手,MySQL 技巧分享,函数和索引,MySQL 技巧分享,使用 UNION 来取代 IN 和 OR 原SQL：select * from city where id in (1,3,4) 或 select * from city where id = 1 or id = 3 or id = 4 explain 结果：id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE city ALL PRIMARY NULL NULL NULL 5 Using wher

16、e 修改后SQL：SELECT * FROM city where id = 1 UNION ALL SELECT * FROM city where id = 3 UNION ALL SELECT * FROM city explain 结果：id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY city const PRIMARYPRIMARY 4 const 1 2 UNION cityconst PRIMARYPRIMARY 4 const 1 3 UNION cityconst PRIMARYPRIMARY 4 const 1,MySQL 技巧分享,MySQL优化网站 MySQL优化专区 MySQL参考手册:优化,MySQL函数,MySQL 函数,MySQL函数分为几种类型: 控制流程函数 字符串比较函数 数学函数 日期和时间函数 全文搜索功能函数 加密