MySQL介绍和优化分享,2009-07,MySQL基本介绍 MySQL优化方式 MySQL技巧分享 Q & A,目录索引,MySQL基本介绍,什么是MySQL,MySQL是一个小型关系型数据库管理系统，开发者为瑞典MySQL AB公司。目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。 MySQL官方网站：,谁在用MySQL,MySQL历史,1979年， 报表工具，数据引擎 1996年，MySQL 1.0 (3.11.1) 发布，支持SQL 2000年，成立 MySQL AB 公司 2008年1月，Sun公司以10亿美元收购MySQL AB公司 2009年4月，Oracle公司以74亿美元收购Sun公司,MySQL里程碑,3.11.1 First public release 3.23 集成Berkeley DB, 支持事务，抽象出Storage Engine 4.0 集成InnoDB 4.1 重大改进，子查询、unicode、c/s通信协议 5.0 stored procedure、view、triggers、query optimizer 5.1 File NDB、record replication,MySQL架构,MySQL存储引擎比较,MyISAM 特点,MyISAM vs InnoDB,数据存储方式简单，使用 B+ Tree 进行索引 使用三个文件定义一个表：.MYI .MYD .frm 少碎片、支持大文件、能够进行索引压缩 二进制层次的文件可以移植 (Linux Windows) 访问速度飞快，是所有MySQL文件引擎中速度最快的 不支持一些数据库特性，比如 事务、外键约束等 Table level lock，性能稍差，更适合读取多的操作 表数据容量有限，一般建议单表数据量介于 50w200w 增删查改以后要使用 myisamchk 检查优化表,MyISAM 存储结构,MyISAM vs InnoDB,MyISAM 索引结构,MyISAM vs InnoDB,InnoDB 特点,MyISAM vs InnoDB,使用 Table Space 的方式来进行数据存储 (ibdata1, ib_logfile0) 支持 事务、外键约束等数据库特性 Rows level lock , 读写性能都非常优秀 能够承载大数据量的存储和访问 拥有自己独立的缓冲池，能够缓存数据和索引 在关闭自动提交的情况下，与MyISAM引擎速度差异不大,InnoDB 数据结构,MyISAM vs InnoDB,MyISAM vs InnoDB 性能测试,测试结果,MyISAM vs InnoDB 性能测试,可以看出在MySQL 5.0里面，MyISAM和InnoDB存储引擎性能差别并不是很大，针对InnoDB来说，影响性能的主要是 innodb_flush_log_at_trx_commit 这个选项，如果设置为1的话，那么每次插入数据的时候都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为0效率能够看到明显提升，当然，同样你可以SQL中提交“SET AUTOCOMMIT = 0”来设置达到好的性能。 同时也可以看出值得使用 InnoDB 来替代 MyISAM 引擎来进行开发，毕竟InnoDB 有多数据库特性、更良好的数据存储性能和查询性能,MySQL优化方式,系统优化：硬件、架构 服务优化 应用优化,MySQL优化方式,使用好的硬件，更快的硬盘、大内存、多核CPU，专业的存储服务器（NAS、SAN） 设计合理架构，如果 MySQL 访问频繁，考虑 Master/Slave 读写分离；数据库分表、数据库切片（分布式），也考虑使用相应缓存服务帮助 MySQL 缓解访问压力,系统优化,配置合理的MySQL服务器，尽量在应用本身达到一个MySQL最合理的使用 针对 MyISAM 或 InnoDB 不同引擎进行不同定制性配置 针对不同的应用情况进行合理配置 针对 f 进行配置，后面设置是针对内存为2G的服务器进行的合理设置,服务优化,MySQL配置原则,服务优化,公共选项,服务优化,MyISAM 选项,服务优化,InnoDB 选项,设计合理的数据表结构：适当的数据冗余 对数据表建立合适有效的数据库索引 数据查询：编写简洁高效的SQL语句,应用优化,应用优化方式,应用优化,表结构设计原则 选择合适的数据类型：如果能够定长尽量定长 不要使用无法加索引的类型作为关键字段，比如 text类型 为了避免联表查询，有时候可以适当的数据冗余，比如 邮箱、姓名这些不容易更改的数据 选择合适的表引擎，有时候 MyISAM 适合，有时候 InnoDB适合 为保证查询性能，最好每个表都建立有 auto_increment 字段， 建立合适的数据库索引 最好给每个字段都设定 default 值,应用优化,索引建立原则 一般针对数据分散的关键字进行建立索引，比如ID、QQ， 像性别、状态值等等建立索引没有意义 尽量使用短索引，一般对int、char/varchar、date/time 等 类型的字段建立索引 需要的时候建立联合索引，但是要注意查询SQL语句的编写 谨慎建立 unique 类型的索引（唯一索引） 一般建议每条记录最好有一个能快速定位的独一无二定位的 唯一标示（索引） 不要过度索引，单表建立的索引不要超过5个，否则更新索 引将很耗时,应用优化,编写高效的 SQL 能够快速缩小结果集的 WHERE 条件写在前面，如果有恒量条件， 也尽量放在前面 尽量避免使用 GROUP BY、DISTINCT 、OR、IN 等语句的使用， 避免使用联表查询和子查询，因为将使执行效率大大下降 能够使用索引的字段尽量进行有效的合理排列，如果使用了 联合索引，请注意提取字段的前后顺序 针对索引字段使用 , =, =, , =, IF NULL和BETWEEN 将会使用 索引， 如果对某个索引字段进行 LIKE 查询，使用 LIKE %abc% 不能使用索引，使用 LIKE abc% 将能够使用索引 如果在SQL里使用了MySQL部分自带函数，索引将失效，同时将无法 使用 MySQL 的 Query Cache，比如 LEFT(), SUBSTR(), TO_DAYS() DATE_FORMAT(), 等，如果使用了 OR 或 IN，索引也将失效 使用 Explain 语句来帮助改进我们的SQL语句,MySQL技巧分享,MySQL技巧分享,常用技巧 使用 Explain/ DESC 来分析SQL的执行情况 使用 SHOW PROCESSLIST 来查看当前MySQL服务器线程 执行情况，是否锁表，查看相应的SQL语句 设置 f 中的 long-query-time 和 log-slow-queries 能够 记录服务器那些SQL执行速度比较慢 另外有用的几个查询：SHOW VARIABLES、SHOW STATUS、SHOW ENGINES 使用 DESC TABLE xxx 来查看表结构，使用 SHOW INDEX FROM xxx 来查看表索引 使用 LOAD DATA 导入数据比 INSERT INTO 快多了 SELECT COUNT(*) FROM Tbl 在 InnoDB 中将会扫描全表 MyISAM 中则效率很高,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 和 Using temporary，性能杀手,MySQL 技巧分享,索引实践,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 where 修改后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 PRIMARY PRIMARY 4 const 1 2 UNION city const PRIMARY PRIMARY 4 const 1 3 UNION city const PRIMARY PRIMARY 4 const 1 NULL UNION RESULT ALL NULL NULL NULL NULL NULL,MySQL 技巧分享,MySQL Slow Log 分析工具 mysqldumpslow - mysql官方提供的慢查询日志分析工具 mysqlsla - 推出的一款日志分析工具，功能 非常强大 mysql-explain-slow-log 德国工程师使用Perl开发的把Slow Log 输出到屏幕，功能简单 mysql-log-filter - Google code 上一个开源产品，报表简洁 myprofi - 纯PHP开发的开源log查看工具，功能详细,MySQL 技巧分享,MySQL优化网站/书籍分享 MySQL Performance Blog MySQL 中文网 MySQL性能调优与架构设计 深入浅出MySQL,Q & A,结束,谢谢大家！,知识回顾Knowledge Review,