mysql的innodb优化举例_mysql innodb优化相关

这篇文章主要是高性能mysql 以及innodb两本书一些关键点，包括但不限于这两本书， 可能有一些是之前看博文记录的笔记，如果有侵犯到你的权益请联系我。

其中下面用红色大写字体标记的是实际写SQL时候经常会用到的

首先是这张大图， 这张图讲了tablespace 和 segment, extent, page 之间的关系。

这张图摘自高性能mysql， 讲的是逻辑架构图

重要且基础的知识: 顺序I/O 和随机I/O .每次访问磁盘的一个块时，磁臂就需移动到正确的磁道上(这段时间为寻址时间/寻道时间)，然后盘片就需旋转到正确的扇区上(这叫旋转时延)。这套动作需要时间，正如寿司在传送带上传送需要时间一样。寻道时间远大于旋转时延. 那下一个磁盘块又是如何呢?如果它在磁盘的某个地方，访问它会有同样的寻道和旋转时延，我们就把这种方式的IO叫做随机IO;但是如果它刚好就在你刚才访问的那一个磁盘块的后面，磁头就能立刻遇到，不需等待，这种IO就叫顺序IO. 主要时间是花在寻道时间上面, 所以传统机械磁盘最大的问题在于读写磁头。

1: 存储引擎不会去解析SQL, 但是INNODB是一个例外,它会解析外键定义,因为MYSQL服务器本身没有实现该功能.

2:对于select语句, 在解析查询之前,服务器会先检查查询缓存, 如果能够在其中找到对应的查询,服务器就不必再执行查询解析,优化和执行的整个过程.而是直接返回查询缓存中的结果集.

3:一种提高共享资源并发的方式就是让锁定的对象更有选择性.尽量只锁定需要修改的部分数据, 而不是所有的资源.更理想的方式是,只对会修改的数据片进行精确的锁定.任何时候,在给定的资源上,锁定的数据量越少,则系统的并发程度越高,只要相互之间不发生冲突即可.。

4:行级锁只在存储引擎层实现,而MYSQL服务器层(如有必要,请回顾前文的逻辑架构图)没有实现.服务器层完全不了解存储引擎中的锁实现.所有的存储引擎都以自己的方式实现了锁机制.InnoDB 就实现了表级锁,其他的存储引擎自己查文档。

6:在事务中混合使用存储引擎: 如果需要ROLLBACK 的话会导致数据不一致, 因为不支持事务的存储引擎没法ROLLBACK.

7:在文件系统中, MYSQL 将每个数据库(也可以称之为schema)保存为数据目录下的一个子目录. 是不是可以理解为一个数据库就是一个schema.？

8: 如果表在创建并导入数据以后, 不会再进行修改操作, 那么这样的表或许适合采用MyISAM压缩表.

9: 很多人在优化的时候都将精力放在修改一些东西上面,却很少去进行精确的测量.我们的做法相反, 将花费非常多, 甚至90% 的时间来测量响应时间花在哪里.如果通过测量没有找到答案,要么是测量的方式错了, 要么是测量的不够完整..

10: 性能剖析是测量和分析时间花费在哪里的主要方法.性能剖析一般有两个步骤: 测量任务所花费的时间; 然后对结果进行统计和排序,将重要的任务排在前面.

11: 在MySQL的当前版本中, 慢查询日志是开销最低,精度最高的测量查询时间的工具.如果还在担心开启慢查询日志会带来额外的I/O开销,那大可放心.我们在I/O密集型场景做过基准测试, 慢查询日志带来的开销可以忽略不计(实际上在CPU密集型场景的影响还稍微大一些).更需要担心的是日志可能消耗大量的磁盘空间.在MYSQL5.1以及更新的版本中,慢日志的功能已经被加强, 可以通过设置long_query_time 为0来捕获所有的查询,而且查询的响应时间单位已经可以做到微秒级.

14: 为了提升查询的速度, 经常会需要创建一些额外的索引,增加冗余列, 甚至是创建缓存表和汇总表. 这些方法会增加写查询的负担,也需要额外的维护任务, 但是在设计高性能数据库时, 这些都是常见的技巧: 虽然写操作变的更慢了, 但更显著地提高了读操作的性能. 然而写操作变慢并不是读操作变的更快所付出的唯一代价,还可能同时增加了读操作和写操作的开发难度.

15:设计数据库的部分心得总结:

* 尽量避免过度设计, 例如会导致及其负责查询的schema设计, 或者有很多列的表设计

* 使用小而简单的合适数据类型, 除非真是数据模型中有确切的需要, 否则应该尽可能的避免使用NULL 值

* 尽量使用相同的数据类型存储相似或者相关的值,尤其是要在关联条件中使用的列

* 注意可变长字符串, 其在临时表和排序时可能导致悲观的按最大长度分配内存.

* 尽量使用整形定义标识列

* 避免使用MYSQL 已经遗弃的特性, 例如指定浮点数的精度,或者整数的显示宽度.

* 小心使用ENUM 和 SET.虽然他们用起来很方便,但是不要滥用, 否则有时候会变成陷阱.最好避免使用BIT

15: 在MYSQL中,索引是在存储引擎层而不是服务器层实现的. INNODB 使用的B-TRee索引实际上是B+Tree这种结构存储索引, InnoDB按照原数据格式进行存储使用B-Tree索引.MYISAM索引通过数据的物理位置引用被索引的行, 而INNODB则根据主键引用被索引的行. 主键引用被索引的行的内容可以参考聚簇索引.

16: MYSQL 5.5之后, 查询条件的顺序不必和索引顺序完全一致也能命中索引,这个在之前的版本中是不可以的.

17:select id, project from tb1 where uid=21, 在表的存储引擎是INNODB的情况下, 如果有索引UID, PROJECT 那么这个语句不需要读表的内容,只需要读索引即可(这个叫做索引覆盖扫描).因为INNODB 是根据主键引用被索引的行, 但是如果语句变成 select id, project, mtk from tb1 where uid=21 那么这个语句还是要去查询表的.

18: 单列索引, 如果查询中的列不是独立的, 则MYSQL就不会使用索引."独立的列" 是指索引列不能是表达式的一部分. >, + , - 之类的都是表达式, 所以不会使用索引. 例如,有 索引 driver_id , 但是 select * from co_order where driver_id > 20 是不会使用索引的. 但是GROUP BY 会使用索引

19: 前缀索引, 是指选择某一列的部分前缀作为索引,因为这些列太长了,mysql不允许索引这些列的完整长度.诀窍在于选择足够长的前缀以保证较高的选择性,同时又不能太长(以便节约空间), 前缀应该足够长,以使得前缀索引的选择性接近于索引整个列(比如 BLOB , TXT, 非常长的VARCHAR列).

20: 在一个多列B-Tree索引中, 索引列的顺序意味着索引首先按照最左列进行排序, 其次是第二列, 等等,.所以, 索引可以按照升序或者降序进行扫描, 以满足精确符合列顺序的ORDER BY, GROUP BY 和DISTINCT等字句的查询需求.

21:对于如何选择索引的列顺序有一个经验法则: 将选择性最高的列放到索引最前列. 但是这个仅仅是经验法则, 不能作为标准.

22: 聚簇索引(实际上就是主键)有一些重要的有点:

* 可以把相关数据保存在一起.例如实现电子邮箱时, 可以根据用户ID来聚集数据, 这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件.如果没有使用聚簇 索引,则没封邮件都可能导致一次磁盘I/O

* 数据访问更快. 聚簇索引将索引和数据保存在同意B-Tree中, 因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快.

* 使用覆盖索引扫描的查询可以直接使用页节点中的主键值.

*** 更新聚簇索引列的代价很高, 因为会强制INNODB 将每个被更新的行移动到新的位置.

*** 基于聚簇索引的表在插入新行, 或者主键被更新导致需要移动行的时候, 可能面临"页分裂 page split"的问题.当行的主键值要求必须将这一行插入到某个已满的页中时, 存储引擎会将该页分裂成两个页面来容纳该行, 这就是一次页分裂操作.页分裂会导致占用更多的磁盘空间.所以需要用合适长度的字段.

*** 聚簇索引可能会导致权标扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候.

*** 二级索引(非聚簇索引, 就是我们平常说的索引了)可能比想象中的要大, 因为在二级索引的叶子节点包含了引用行的主键列. 所以用索引进行查询的时候, 是先找到叶子节点,然后从叶子节点读取主键,再从聚簇索引中读取记录. 所以二级索引访问需要两次索引查找, 因为二级索引叶子节点保存的不是指向行的物理位置的指针, 而是行的主键值.

23: 索引覆盖查询, explain 的时候 extra : index

如果一个索引包含(或者说覆盖) 所有需要查询的字段的值, 我们就称之为"覆盖索引查询"

如果select a, 主键 from tb1 where m = 23 的 m, a刚好组成一个索引,那么这个查询就是覆盖索引查询

24: MySQL不允许在BLOB/TEXT,TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, LONGTEXT,VARCHAR建索引，因为前面那些列类型都是可变长的，MySQL无法保证列的唯一性，只能在BLOB/TEXT前n个字节上建索引, 这就是前缀索引.这一点非常重要， 尤其是varchar类型的。

25: 延迟关联, 自己看一下,这个逼格很高, 通过使用覆盖索引查询返回需要的主键, 再根据这些主键关联原表获得需要的行.这个可以减少MYSQL 扫描哪些需要丢弃的行数.这个在实际操作中写SQL经常用到。

26: 冗余索引, (a, b) (a) 那么(a)就是冗余索引, 但是(b, a)不是冗余索引. 当然这种只限于相同类型的索引, 在大多数情况下都不需要冗余索引, 应该尽量扩展已有的索引而不是创建新索引.但也有时候出于性能方面的考虑需要冗余索引, 因为扩展已有的索引会导致其变得太大, 从而影响其他使用该索引的查询性能.

27: explain 的EXTRA 列出现了 'Using where', 表示MYSQL 服务器将存储引擎返回行以后再应用WHERE的过滤条件.

28: INNODB 读锁, 在读取该行之后会锁定该行, 行级锁.

29: 减少索引和数据的碎片

B-Tree索引 可能会碎片化, 这会降低查询的效率.碎片化的索引可能会很差或者无需的方式存储在磁盘上.根据设计, B-Tree需要随机磁盘访问才能定位到叶子页, 所以随机访问时不可避免的.然而, 如果叶子页在物理分布上是顺序且紧密的, 那么查询的性能就会更好.否则, 对于范围查询,索引覆盖扫描等操作来说, 速度可能会降低很多倍; 对于索引覆盖扫描这一点更明显.

碎片类型: 行碎片, 行间碎片, 剩余空间碎片

30: 访问类型, 在EXPLAIN语句中的type 列反应了访问类型.访问类型有很多种, 从全表扫描到索引扫描, 范围扫描, 唯一索引查询, 常数引用(const)等. 这里列的这些速度从慢到快, 扫描行数也是从小到大(是不是翻译出错了,感觉应该是从大到小).

31: 要记得分解关联查询.

32: mysql 的 in() 很快, 将in()列表中的数据先进行排序, 然后通过二分查找的方式来确定列表中的值是否满足条件, O(logn) 复杂度的操作., 但是 in 子查询的效率非常低, 低到令人发指, 比如in(select id from tb1 where mi=23), 会非常慢