MySQL技术内幕-InnoDB存储引擎读写笔记(性能调优)

1、CPU

    数据库一般是IO密集型的，其性能瓶颈在于IO。数据库服务器的选购偏内存，CPU可以适当降低其要求。

    InnoDB存储引擎相关后台线程：

    show engine innodb status\G;

    

 1、插入缓冲区线程 http://www.cnblogs.com/yuyue2014/p/3802779.html

         InnoDB insert buffer thread 线程主要是更新非唯一索引的索引数据，避免频繁随机的读写索引文件。（索引数据存放在表默认表空间中）

         log thread : InnoDB的默认指的就是redo日志,log thread 就是写 redo 日志的线程。

         read thread | write thread 读、写日志线程（文件 《----》内存）

      根据CPU适当调节innodb_read_io_threads与innodb_write_io_threads

         

2、内存

    内存的大小最能直接反应数据库的性能。InnoDB存储引擎既缓存数据，又缓存索引（存放在数据文件中，表空间），并放入一个很大的缓冲池中(InnoDB Buffer Pool)。    【innodb_buffer_pool的大小如果能等于数据文件的大小，则能缓存所有的数据文件，性能会得到极高的提升】（innodb_buffer_pool_size）,如何判断当前数据库的内存是否达到了瓶颈了呢。

    mysql提供  show global status 命令查看系统运行状况监控，（INFORMATION_SCHEMA GLOBAL_STATUS临时表）。

关注如下参数：

    innodb_buffer_pool_reads: 从物理磁盘读取页的次数

    innodb_buffer_pool_read_ahead:预读的次数

    innodb_buffer_pool_read_ahead_evicted:预读的页，但没有被读取就从缓冲区被替换的也的数量，一般用来判断预读的效率。

    innodb_buffer_pool_read_requests:从缓冲池中读取页的次数（可以理解为缓冲命中次数）

    innodb_data_read:总共读入的字节数

    innodb_data_reads:发起读请求的次数

    预读的效率：(1 -  (innodb_buffer_pool_read_ahead / innodb_buffer_pool_read)) * 100 %

    InnoDB缓存命中率： innodb_buffer_pool_read_requests /  ( innodb_buffer_pool_read_requests + innodb_buffer_pool_reads  + innodb_buffer_pool_read_ahead )  * 100 %,

    InnoDB缓存命中率通常要保持在99%以上，才表明内存无压力，这样性能杠杠的。

    平均每次读取的字节数= innodb_data_read/innodb_data_reads

    内存大于数据文件，并不意味着没有IO操作，Innodb会定时刷脏（将被修改的页刷写到数据文件），并且每次

    事务提交，会写redo日志。

3、磁盘对数据库性能的影响

     数据库是IO密集型应用，推荐使用raid10(磁盘冗余阵列)

4、SQL语句优化

    SQL语句的优化点基本上会落到是否能利用索引这个问题上来。

     1）养成好的SQL使用习惯

           a、禁止使用 select *，应写需要的数据字段。

           b、尽量使用exists代替in

     2）尽量利用索引来加速SQL数据的查询效率

          以下情况将无法利用索引：

          a、不支持 != 表达式，如果使用不等于，无法利用到索引。索引的存储结构是B+树，支持等值查询与范围查询。

          b、like '%a%'无法利用索引，但'a%'可以使用索引。

                原因分析，索引的存储结构是B+树，叶子节点存放数据，数据本身是排序的，输入值必须是一个明确的值，方便对比，a%开头的查询，是可以用a当查询关键字首先定位记录的，但%a%无法定位。

          c、复合索引，查询条件必须包含第一列（最左列）

               道理是一样的，索引的优势就是天生是排序的，查询类似于二分查询（每比较一次，就会缩短查询范围）。

              例如有两列的复合索引类似(  1, 1  ) (  1, 3  ) (  2, 1  )  (  2, 5  )   (  3, 1  ) 只有包含第一列，整个二元序列就是有序的，但如果剔除第一列，那序列为  1,3,1,5,1，无序序列的查找，复杂度为o(n)。

         d、对字段使用表达式或函数允许，将无法使用索引

     3）索引建立原则

          a、列高选择性（重复率低）

          b、频繁查询的列

          c、order by 或 group by字段（利用索引的有序性）

            

5、MySQL执行计划解读

MySQL提供explain查看查询语句的执行计划，使用案例如下：

EXPLAIN SELECT

    t.id,

    t.order_no,

    t.total_price,

    t.seller_id,

    t.creator,

    t.create_time

FROM

    (

        SELECT

            a.id,

            a.order_no,

            a.total_price,

            a.seller_id,

            a.creator,

            a.create_time

        FROM

            es_order a

        WHERE

            a.seller_id = 24

        LIMIT 20,

        10

    ) t

INNER JOIN es_order_item b ON b.order_id = t.id

相关字段：id、select_type、table、type、prossible_keys、key、ken_len、ref、rows、Extr

    1） id : 执行顺序，数字越大，先执行。

    2） select_type: (只是标记为查询类型)

          simple : 查询中不包含子查询或union

          primary:若查询中包含任何复杂的子查询，最外层查询标记为：PRIMARY

          subquery:子查询

          derived:驱动表，不是真实的物理表，但不同于临时表

          union:

          union result

     3）type:访问类型，重点关注

    取值如下：all、index、range、ref、  eq_ref、  const system、null

    all : 全表扫描

    index: Full Index Scan,index与all的区别为index只遍历索引树。

              例如：(select a.id from es_order a)

    range:索引范围扫描，常见于索引字段上的  between > <  like( like 'a%')

    ref : 非唯一索引的等值匹配，例如a.seller_id=24 (seller_id上建有索引)

    eq_ref:唯一索引的等值匹配

    const、system:当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。

          explain select a.id,a.name from es_order a where a.id=8078

   null : 不需要访问表，索引等。

    4）possible_keys

        指出mysql能使用哪个索引能在表中找到行，查询涉及到的字段若存在索引，则会列出，但不一定使用

    5）key （重点关注）

        使用到的索引

    6）key_len (索引用到的长度)

    7 ）ref 索引匹配的情况（列）【无需太关注】

    8）rows

        本次查询，预计需要扫描的行数

    9）Extra  额外的说明信息

         using index (covering Index)

         using where

            表示mysql服务器从存储引擎收到记录后，进行“后过滤”，如果未使用索引，提醒

         Using temporary

           表示使用了临时表空间（排序、分组）

        Using filesort

            Mysql中无法利用索引完成的排序操作成为文件排序。

6、数据库设计心得

1）数据库的表的设计方法论为理解ER关系

     一对一、一对多、多对对（分拆成两个1对多关系，也就是引入一个关系实体）

2）适当打破数据库第三范式

     适当增加冗余字段，减少表的连接操作。

     举例：订单表中有商品ID，由于订单反映的当时下单时商品的信息，我们完成可以增加一个冗余字段，商品名称goods_name,这样在展示订单信息

     时，无需join商品信息表。（冗余字段的增加可能会带来数据的不一致性，所以要结合实际情况，不能为了冗余而冗余）

3）数据库字段类型选择

     尽量选择满足需求的最小长度。比如订单状态，可以使用tinyint,而无需使用int。这样的直接好处就是减少数据行的长度，每页能存储更多的数据

     行，IO效率得到提高。（性能是一个积累的过程）

4）表的垂直分割

     将一个表的信息分割成多个表，这样主要是保证频繁使用的主表每行的数据尽量少，每页存放更多数据行，提高IO读效率。

     表分割后，如果每次查询主表，都需要连接查询从表，那就没有必要进行垂直分割。

     举例：

          比如一条资讯信息，咨询的内容使用的类型为(text)，在实际使用时，一般是先出资讯列表，然后点击到每一条资讯时，才去查询资讯内容，此时

          可以将咨询内容单独分割出一张表，在咨询主表中冗余一个字段（摘要），存放咨询内容的部分信息，用于列表展示。

     反例：

          比如一条评论，有评论内容，类型为text,此时就没必要单独将评论内容抽取出来，因为每次显示评论时，基本上都要查看全部的评论内容，如果

     分开，会进行多边连接，性能反而受影响。

5）表设计阶段应该考虑索引的建立

     应根据系统相关需求，在创建表时建立必要的索引。