MySQL(InnoDB)

2019独角兽企业重金招聘Python工程师标准>>>

MySQL可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能
在关系型数据库中，数据的存储是以表的形式来实现的。所以，存储引擎也可以称为表类型。所以，实际上，存储引擎就是一种存储数据、查询数据的技术
MySQL支持的存储引擎包括MyISAM、InnoDB、Memory、CSV、Archive、BlackHole(黑洞引擎)。

引擎特点/MyISAM/InnoDB/Memory/Archive
存储限制／256T　/64T　　/有限制／无限制
事务安全／不支持／　支持／不支持／不支持　
支持索引／　支持／　支持／　支持／　支持
并发控制／　表锁／　行锁／　表锁／　行锁
数据压缩／　支持／不支持／不支持／　支持
支持外键／不支持／　支持／不支持／不支持

innodb vs myisam
1.InnoDB不支持FULLTEXT类型的索引。
2.InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。
6.InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”
7.innodb适合oltp, myisam适合olap

配置默认存储引擎(my.cnf)
default-storage-engine = engine

指定存储引擎
CREATE TABLE table_name(  ... ) ENGINE = engine;

修改存储引擎
ALTER TABLE table_name ENGINE [=] engine_name;

innodb是用的新技术
  使用MVCC来获取高并发性
  next-key locking的策略来避免换读现象
  插入缓存(insert buffer)
  二次写(double write)
  自适应哈西索引(adaptive hash index)
  预读(read ahead)
  ...

innodb架构：
  1.内存池（多个内存块组成）
    　  缓冲池(buffer pool-innodb_buffer_pool)
          索引页(index page)
          数据页(data page)
          插入缓存页(insert buffer)
          自适应hash索引(adaptive hashindex)
          innodb存储的锁信息(lock info)
          数据字典信息(data dictionary)
          未做页(undo page)
      重做日志缓存(redo log buffer-log_buffer)

      额外的内存池(additional memory pool-innodb_addtional_mem_pool_size, buffer pool中frame buffer和
                 buffer control block对应的LRU、锁、等待待方面的信息)

  2.后台线程（后台有7个线程）
      4个IO线程(insert buffer thread,log thread,read thread,write thread)
      1个master thread
      一个lock监控线程
      一个error监控线程

innodb文件：
  　数据文件
    　　 frm:独立表结构定义文件，还可以用来存放视图的定义．一个表一个.
        idb:独立表空间文件,即数据文件, 存储数据和索引.一个表一个.

        ibdata1:数据文件, 存储数据和索引
        ibtmp1:临时表空间文件，即临时表数据文件
        ib_buffer_pool:内存预热数据｜内存dump数据
    日志文件(redo日志)
    进程文件(mysqld.pid)
    套接字文件(mysqld.sock)
    重做日志文件(ib_logfile0)

innodb工作方式：
    1.将数据文件按页(每页16K)读入InnoDB buffer pool，然后按最近最少使用算法(LRU)保留缓存数据，数据文件修改时，先修改缓存池中的页（即脏页），然后按一定频率将脏页刷新到文件。

  2.对于数据读操作，通常用户线程触发的数据块请求读是同步读，如果开启了数据预读机制的话，预读的数据块则为异步读，由后台IO线程进行。其他后台线程也会触发数据读操作，例如Purge线程在无效数据清理，会读undo页和数据页；Master线程定期做ibuf merge也会读入数据页。崩溃恢复阶段也可能触发异步读来加速recover的速度。

  3.对于数据写操作，InnoDB和大部分数据库系统一样，都是WAL模式，即先写日志，延迟写数据页。事务日志的写入通常在事务提交时触发，后台master线程也会每秒做一次redo fsync。数据页则通常由后台Page cleaner线程触发。但当buffer pool空闲block不够时，或者没做checkpoint的lsn age太长时，也会驱动刷脏操作，这两种场景由用户线程来触发。
 

note:
    1、由于硬件的发展，现在的硬件性能已经提高了很多，如果innodb每秒最大刷新100个脏页，那么效率会很低，为了解决这个问题，innodb plugin提供了一个参数innodb_io_capacity，用来表示磁盘IO的吞吐量，
    默认值是200，规则如下：在合并插入缓存时，合并插入缓存的数量为innodb_io_capacity的5%；在从缓冲区刷新脏页时，啥新脏页的数量为innodb_io_capacity。

    2、关于innodb_max_dirty_pages_pct值的争议，如果值过大，内存也很大或者服务器压力很大，那么效率很降低，如果设置的值过小，那么硬盘的压力会增加，建议是在75-80.并且innodb plugin引进了
    innodb_adaptive_flushng(自适应的刷新)，该值影响每秒刷新脏页的数量。

innodb表类型：
  innodb表类似oracle的IOT表(索引聚集表-indexorganized table)，在innodb表中每张表都会有一个主键，如果在创建表时没有显示的定义主键则innodb如按照如下方式选择或者创建主键：
首先表中是否有唯一非空索引(unique not null)，如果有则该列即为主键。不符合上述条件，innodb存储引擎会自动创建一个6字节大小的指针，rowid()。

innodb逻辑存储结构：
  innodb的逻辑存储单元由大到小表空间tablespace,段segment,区extent,页page(block)组成

innodb物理存储结构：
  innodb引擎由共享表空间，日志文件(redo log, 每页512字节)，表结构定义文件组成。

innodb表空间tablespace：
  　所有数据都是存放在表空间中的，启用了参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间中，每张表空间内存放的只是数据，索引和插入缓冲，其他类的数据，如undo信息，系统事务信息，二次写缓冲等还是存放在原来你的共享表空间。
innodb段segment：
　　常见的segment有数据段、索引段、回滚段。innodb是索引聚集表，所以数据就是索引，索引就是数据，那么数据段即是B+树的页节点(leaf node segment)，索引段即为B+树的非索引节点(non-leaf node segment)。而且段的管理是由引擎本身完成的。
innodb区extent：
 　　区是由64个连续的页主成，每个页大小为16K，即每个区的大小为(64*16K)=1MB,对于大的数据段，mysql每次最多可以申请4个区，以此保证数据的顺序性能。
innodb页page：
　　页是innodb磁盘管理最小的单位，innodb每个页的大小是16K，且不可更改。常见的类型有：数据页 B-tree Node；undo页 Undo Log Page；系统页 System Page；事务数据页 Transaction system Page；插入缓冲位图页 Insert Buffer Bitmap；插入缓冲空闲列表页 Insert Buffer freeBitmap；未压缩的二进制大对象页Uncompressed BLOB Page；压缩的二进制大对象页 Compressed BLOB Page。
innodb行row：
    innodb存储引擎是面向行的(row-oriented),也就是说数据的存放按行进行存放。每个页最多可以存放16K/2～200行,也就是7992个行。

innodb行记录格式（两种）：
    compact和redundant

innodb数据页结构（七部分）：
  File Header：文件头( 38 bytes )
  Page Header：页头( 56 bytes )
  Infimum + Supremum Records：页中上/下界记录
  Users Records：用户记录，即行记录
  Free Space：空闲空间
  Page Directory：叶目录
  File Trailer：文件结尾信息

innodb索引：
　innodb支持常见的两种索引，B+树索引和hash索引

innodb约束：
  innodb提供了以下四种约束：Primary key，Unique Key，Foreign Key，Default，Not NULL
  触发器的作用是在insert,delete和update命令之前或之后自动调用sql命令或者存储过程。所以一个表最多可以建立6个触发器。

innodb视图:
  innodb的视图不支持物化视图，都是虚拟的。

innodb分区:
  分区表不是在存储引擎中完成的，所以不止innodb支持分区表功能。
  mysql的分区表是水平分区，并不是垂直分区，mysql的分区表是局部分区索引，一个分区中既存储数据又存放索引。
  支持以下几种类型的分区：
    Range分区，行数据基于属于一个给定连续区间的列值放入分区，这个值只能是整数。VALUE LESS THAN需指定MAXVALUE值的分区，主要用于日期列的分区。对于RANGE分区的查询，优化器只能对YEAR() TO_DAYS() TO_SECONDS()和UNIX_TIMESTAMP()函数进行优化选择。
    LIST分区和range类似，只是list分区里面是离散的值，这个值只能是整数。(VALUE IN对于未定义的插入，MySQL会抛出异常。对于多条记录同时插入过程中存在未定义的值时，MyISAM分区会允许之前的行数据插入，而拒绝之后的行数据插入，但是InnoDB将其视为一个事务从而ROLLBACK整个插入。
    HASH分区，根据用户自定义的表达式的返回值 返回值不为负(PARTITION BY HASH (expr) 将数据均匀分布还可按LINEAR HASH分区区别在于算法不同)。hash分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据量大致一致。
    KEY分区，根据mysql数据库提供的哈西函数进行分区。key分区和hash分区相似，不同在于hash分区是用户自定义函数进行分区，key分区使用mysql数据库提供的函数进行分区。
    columns分区，mysql-5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的进化，COLUMNS分区可以直接使用非整形数据进行分区。
    RANGE COLUMNS分区可对多个列的值进行分区。不论什么类型的分区，如果表中存在主键和唯一索引，那么分区列必须是主键或者唯一索引的一个组成部分。否则回报错。

innodb子分区：
  mysql允许在RANGE和LIST分区上再进行HASH或者key的子分区。每个分区上的子分区数量必须相同。在每个分区内，子分区的名称是唯一的，分区可以放到不同磁盘上。

innodb性能调优
show engine innodb status;
show variables like 'Innodb%';
show status like 'Innodb%';

内存利用方面
innodb_buffer_pool_size
    作用：这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。为Innodb加速优化首要参数。
    分配原则：默认分配只有8M。这个参数不能动态更改，所以分配需多考虑。分配过大，会使Swap占用过多，致使Mysql的查询特慢。如果你的数据比较小，那么可分配是你的数据大小+10％左右做为这个参数的值。
    设置方法(配置文件)：innodb_buffer_pool_size=4G
innodb_additional_mem_pool：
    作用：用来存放Innodb的内部目录
    分配原则：这个值不用分配太大，系统可以自动调。不用设置太高。通常比较大数据设置16Ｍ够用了，如果表比较多，可以适当的增大。如果这个值自动增加，会在error log有中显示的。
    设置方法(配置文件)：innodb_additional_mem_pool=16M

关于日志方面
innodb_log_file_size
    作用：指定日志的大小。
    分配原则：几个日志成员大小加起来差不多和你的innodb_buffer_pool_size相等。上限为每个日志上限大小为4G.一般控制在几个Log文件相加大小在2G以内为佳。具体情况还需要看你的事务大小，数据大小为依据。
    说明：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。
    设置方法(配置文件)：innodb_log_file_size=256M
innodb_log_files_in_group
    作用：指定你有几个日值组
    分配原则：一般我们可以用２－３个日值组。默认为两个。
    设置方法(配置文件)：innodb_log_files_in_group=3
innodb_log_buffer_size：
    作用：事务在内存中的缓冲。
    分配原则：控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。在Oracle等数据库了解这个，一般最大指定为3M比较合适。)
    说明：如果这个值增长过快，可以适当的增加innodb_log_buffer_size, 另外如果你需要处理大理的TEXT，或是Blob字段，可以考虑增加这个参数的值。
    设置方法(配置文件)：innodb_log_buffer_size=3M
innodb_flush_logs_at_trx_commit
    作用：控制事务的提交方式
    分配原则：这个参数只有３个值，0,1,2。请确认一下自已能接受的级别。默认为１，主库请不要更改了。性能更高的可以设置为０或是２，但会丢失一秒钟的事务。
    说明：这个参数的设置对InnoDB的性能有很大的影响，所以在这里给多说明一下。
    当这个值为1时：innodb 的事务Log在每次提交后写入日值文件，并对日值做刷新到磁盘。这个可以做到不丢任何一个事务。
    当这个值为2时：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的是，由于进程调用方面的问题，并不能保证每秒100％的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。
    当这个值为0时：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。mysqld进程的崩溃会删除崩溃前最后一秒的事务。

    从以上分析，当这个值不为1时，可以取得较好的性能，但遇到异常会有损失，所以需要根据自已的情况去衡量。
    设置方法(配置文件)：innodb_flush_logs_at_trx_commit=1

文件IO分配，空间占用方面
innodb_file_per_table
    作用：使每个Innodb的表，有自已独立的表空间。如删除文件后可以回收那部分空间。
    分配原则：只有使用不使用。但DB还需要有一个公共的表空间。
    设置方法(配置文件)：innodb_file_per_table=1
innodb_file_io_threads
    作用：文件读写IO数，这个参数只在Windows上起作用。在LINUX上只会等于４
    设置方法(配置文件)：innodb_file_io_threads=4
innodb_open_files
    作用：限制Innodb能打开的表的数据。
    分配原则：如果库里的表特别多的情况，请增加这个。这个值默认是３００。
    说明：请适当的增加table_cache
    设置方法(配置文件)：innodb_open_files=800

其它相关参数
innodb_flush_method
    作用：InnoDB和系统打交道的一个ＩＯ模型
    分配原则：Windows不用设置。
            Unix可以设置：fsync() or O_SYNC/O_DSYNC　如果系统可以禁止系统的Cache那就把他禁了。
            Linux可以选择：O_DIRECT 直接写入磁盘，禁止系统Cache了
    设置方法(配置文件)：innodb_flush_method=O_DIRECT

innodb_max_dirty_pages_pct
    作用：控制Innodb的脏页在缓冲中在那个百分比之下，值在范围1-100,默认为90.
    这个参数的另一个用处：当InnoDB的内存分配过大，致使Ｓｗａｐ占用严重时，可以适当的减小调整这个值，使达到Swap空间释放出来。建义：这个值最大在90％，最小在15％。太大，缓存中每次更新需要致换数据页太多，太小，放的数据页太小，更新操作太慢。
    设置方法：innodb_max_dirty_pages_pct＝90
    动态更改需要有Super权限：
      set global innodb_max_dirty_pages_pct=50;
 

转载于:https://my.oschina.net/igooglezm/blog/825977