• 1. Mysql 查询优化 MySQL Performance Tuning
  • 2. 调优的理由线上应用跑的慢 客户体验差/投诉 老板说要提高系统性能 自己能动性决定 服务器性能检测异常
  • 3. 线上应用跑的慢静态页面显示速度快,动态的慢,那很有可能是数据库的问题了。 最坏的状况,发现数据库表没有需要的索引。 建表的时候加了索引,但是发现没有起到应有的效果。 设计表的时候忽略了一些细节上的问题。 其他。
  • 4. 如何定位那里有问题explain slow query show status/show processlist/show engine innodb status iostat,top等系统级别的工具 其他
  • 5. Mysql存储引擎Myisam Myisam 文件系统存储,不支持事务,表级锁,适合读 innodb Innodb 表空间存储,支持事务,行级锁,read committed隔离级别,占用空间略大于Myisam,并发写入性能较好
  • 6. Mysql其他存储引擎NDB 支持事务,用户cluster,实现高可用,性能欠佳。 Memory(heap)内存存储,速度快,注意重启带来的问题。 BDB (KV)最早具有事务支持的mysql表,目前用的比较少。 其他等等
  • 7. 常用Mysql引擎Myisam&innodb 最常使用的2种引擎,建表默认使用myisam(5.x以前)。 二者比较 大部分情况下性能差别不是很大 select count(*) from t myisam 快(有计数) select count(*) from t where pk=100 差不多 Myisam 成熟,稳定,易于管理,迁移方便。 需要事务,安全,并发修改比较多的可以考虑使用innodb
  • 8. 引擎案例-数据库同步复制
  • 9. explain 介绍分析select语句的信息。 表的读取顺序。 查询使用了那些索引。 查询优化器从每个表中读取的行数。 提示是否使用了临时表,额外排序等. 通过上述分析建立合理的索引。
  • 10. explain extra 解释 Table t(a,b,c) index on t(a) explain select count(*) from t where a=1; Using where;Using index 只读取索引,不引起回表 explain select b from t where a=1; Using where 读取索引,引起回表 explain select b from t where a=1 order by b desc; Using where;Using filesort 不仅回表,还引起临时排序 explain select count(*) from t where a=1 group by b; Using where;using temporary;using filesort 还创建一个临时表
  • 11. 查询优化和索引确保建立合理的索引,尽可能多的使用联合索引。 选择区分度比较好的字段做索引。 数据库系统适当的加大查询缓存。 尽量使用固定格式的查询语句,减少函数使用。 数据表字段选择合适的数据类型。 分解复杂查询 选择合适的引擎 复杂的查询先用explain分析 表连接时数据量小的放在前面 对myisam来说,char比varchar好,但是对innodb来说恰好相反。 其他一些调优手段及网络,硬件等上的优化。
  • 12. 联合索引之最左前缀原则index on t(a,b,c) 能用到索引的 a,b,c a,b a a, b desc, c desc / a, b asc, c asc 不能用到索引的 b,c c 不确定的 a,c
  • 13. 联合索引之隐式索引Table t(a,b,c,d,e) on index(a,b,c,d) 假如sql语句如下: select b,c,d from t where a=100; explain 发现: Using where;Using index 将尽可能多的查询数据放到index中,从而避免过多的回表操作。
  • 14. 索引数据结构B-TreeB-treeIndex entry headerKey column lengthKey column valueROWIDRootBranchLeaf
  • 15. 索引如何检索数据假定一棵索引树的高度为4 select:从索引树自顶向下完成一次搜索,访问5个结点(包括根节点),然后根据rowid访问表再作一次I/O, 一共6次I/O update:删除一个叶子节点,增加一个叶子节点,可能 引起相邻的叶子节点与上层导航块合并/分裂,最多可以影响到根结点及根结点下一层的一个结点。大约有7次I/O
  • 16. 估计查询性能 以B-Tree为例 计算公式: log(row_count)/log(index_block_length/3 * 2/(index_length + data_pointer_length))+1 (索引缓存区填充率为2/3 ) 在MySQL中,索引块通常是1024个字节,数据指针通常是4个字节,这对于有一个长度为3(中等整数)的索引的500,000行的表,通过公式可以计算出log(500,000)/log(1024/3*2/(3+4))+1= 4次搜索。 估计索引的量:500,000 * 7 * 3/2 = 5.2MB,cache更多的索引,仅需要1-2调用从OS读数据来找出行。
  • 17. 从查询高度看分表策略分表&拆表? 分表:横向分割表(行) 拆表:纵向分割表 (列) 分表的好处 插入/更新比较快(对myisam) 更小的维护窗口 分表的复杂度 维护更多的子表 查询难度增大 分表对查询的影响 log(50,000,000)/log(1024/3*2/(3+4))+1= 5 log(500,000)/log(1024/3*2/(3+4))+1= 4 数据有100倍的差距,而查询高度仅仅减少了1,可以看出分表对查询的 影响不是很大。
  • 18. 内存对数据库的影响大的内存可以cache更多的索引数据。 为啥要cache更多的索引数据? 创建好的索引,适当的提高索引缓存的大小,使检索更多的在内存中进行,降低I/O。 在依赖索引的基础上,cache原始数据对提高检索的速度没有太大的帮助。 事实证明,cache索引是明智的。
  • 19. 索引误区不是所有的表建索引索引就能提高速度。 区分度低的字段,比如性别做索引不一定是好主意。 有的时候走索引反而会比不走索引慢? table t(a,b,c) index(a,b) select * from t where a=100 and b>=10 and b<30; 原因:查询会在索引和数据块之间访问,增大了I/O,如果这些索引没有被 cache的话,速度会有下降。而如果直接访问数据块就会减少I/O.
  • 20. 创建索引的一般原则包含查询所必须的关键字段。 where条件后是索引创建的基础。 除条件字段外增加使用字段。 避免不必要的回表。 回表:索引中存放指向原始数据的指针,能直接从索引中 读取就尽量避免去原始数据表中去读取。目的是减少 I/O。 具体情况具体分析。
  • 21. 现实是什么数据库仍是整个应用的性能瓶颈之一。 既便设计好的索引,在高并发环境和数据 日益增长所带来的压力还是存在。 数据规划还是一个不断探索和改进的课题。 好消息是我们还有很多办法来弥补这些问题。 可以从手边的项目开始。
  • 22. 我们可以优化横/纵向分割数据库/表 开启Mysql复制,实现读、写分离。 频繁读写的表,可以分离成父表和子表(内存)。 对统计表定时更新统计结果,而不是时时统计原始表。 编写存储过程/函数代替复杂的应用程序交互。 用cache做大的缓存,减少应用和数据库的交互。 节约数据库连接,尽可能多的复用。 定期的对数据库执行 optimize table 整理碎片。 想优化总是有办法的。
  • 23. Mysql配置几个重要参数提高查询效率 key_buffer_size: MyISAM表的索引块分配了缓冲区,由所有线程共享。key_buffer_size是索引块缓冲区的大小。 innodb_buffer_pool_size: InnoDB用来缓存它的数据和索引的内存缓冲区的大小 。 更多信息参考mysql文档。
  • 24. Mysql带来的解决方案5.1.X系列提供的分区表(partition)。 采用集群+复制(MySQL 6.0+)。 Mysql+Memcached。 等等。
  • 25. 其他数据存储解决方案NoSQL 1,KV Memcached,Redis等 2,其他存储模式 Hadoop ,Google BigTable等
  • 26. 参考文献&推荐参考文献 Mysql官方手册中文版。 Mysql 5权威指南。 推荐 http://dev.mysql.com/doc/ http://www.mysqlperformanceblog.com/