MySQL存储引擎

首先，我们的知道什么是存储引擎：MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

---

常见存储引擎：

1. InnoDB：MySQL5.5及以后默认存储引擎
   1. 支持事务，可通过一些机制和工具实现热备份；
   2. 实现了MVCC，支持行锁，实现了事务的四个隔离级别，默认REPETABLE READ，并通过间隙锁策略防止幻读出现；
   3. 数据表会分成 .frm(表结构)  .idb(索引和数据)两个文件进行存储；
   4. 支持外键，存储数据时每张表的数据均按照主键顺序存放，如果没指定主键，每行数据会默认生成1个6字节的ROWID，作为主键。
   5. 聚簇索引，主键查询性能很高，非主键索引必须包含主键列，且索引会很大。
2. MyISAM:MySQL5.5以前默认存储引擎
   1. 不支持事务；
   2. 表锁，读表时共享锁(读锁)，写表时排它锁(写锁)，并发写入效率低很多；
   3. 数据表会分成 .frm(表结构) .MYD(数据) .MYI(索引) 三个文件存储
   4. 不支持外键，但支持全文检索、压缩，延迟更新索引键等
      1. 全文索引：基于分词创建的索引，一般不用，采用全文索引工具(Sphinx、Lucene、迅搜)；
      2. 压缩：压缩后不能进行更新，但极大的减少磁盘占用，IO会少，从而提高查询性能；
      3. 延迟更新索引键：不将更新的索引数据立即写到磁盘中，而是写入内存中缓冲区，等清除缓冲区时才将索引写入对应的磁盘，这种方式大大提升了写的性能。
   5. 非聚簇索引；

下面来个图，对比看看各大存储引擎的区别(MyISAM、InnoDB、BDB、Memory)

 

---

扩展：

一：InnoDB如何实现热备份：

1. Xtrabackup：网上教程非常多。
2. MySQLDump

二：事务并发处理机制-MVCC：Multi-Version Concurrency Control，多版本并发控制。

1. 读不加锁，读写不冲突；
2. 仅隔离级别为读已提交和可重复读时生效。读已提交能读到未提交的事务行，串行化会锁表均不适用；
3. 保存某时间阶段快照，意味着事务可查到一致的数据视图，同样也意味着不同的事务在同一时间查到的数据可能不一致；
4. InnoDB实现方式：<隔离级别为读已提交，可重复读时MVCC工作>额外存储俩字段(存事务ID)，一个存创建事务ID，一个存删除事务ID，具体如何工作，下面具体说明：

   #假设最开始事务ID为1  <事务ID变更均发生在事务提交时>
   #新增
   insert into tmvcc (`id`, `value`) values (1, 'test');
   #对应数据
   id	value	create_id	delete_id
   1	test	1	
   
   #更新 先标记旧的数据为删除，再生成一条新记录
   update tmvcc set value='new';
   #对应数据
   id	value	create_id	delete_id
   1	test	1	        2
   1	new	    2	
   
   #删除
   delete from tmvcc where id=1;
   #对应数据
   id	value	create_id	delete_id
   1	test	2	        3
   
   #查询
    1、delete_id不存在或者大于当前事务ID，确保事务开启后读取的行未被删除；
    2、create_id<=当前版本号，确保读取的数据是事务开启前的数据；

   其实说到底和咱们业务中实现的乐观锁方式极其相似。

三：全文检索：

1. MyISAM支持全文检索，非常简单的按照空格、标点等分词，但中文成句不存在空格等标识，无法实现分词，故MySQL自带全文检索仅支持英文全文检索；
2. 创建全文索引：ALTER TABLE `table_name` ADD FULLTEXT INDEX fulltext_table(`field1`, `field2`);
3. 使用全文检索：我们平时做模糊查询使用 'like' 关键字，但并不能命中索引。语法：SELECT * FROM `table_name` WHERE MATCH(`field1`, `field2`) AGAINST ('string');
4. MySQL指定最小拆分字符长度，默认为4，修改：my.ini  ft_min_word_len = 2 #修改最小字符为2；
5. 匹配会有权重概念，如果一个检索词在多条记录中频繁出现权重会低(甚至会低到为0，没条数据中均出现)，MySQL默认权重高于50%不会返回；
6. 总结：在开发过程中自带的全文检索没用过，我到目前为止服务的客户群体均且仅提供中文服务，故需做中文全文检索。如果非要用MySQL提供全文检索，需在创建一索引表，将中文转化为英文，然后在进行匹配。
   1. base64编码 -输入拼音查询不到结果
   2. 转化为拼音<网上资料很多>
   3. 使用 中文检索引擎  如 Sphinx（斯芬克斯）、迅搜等等

四：压缩   MySQL提供工具：myisampack

#创建MyISAM引擎表
mysql> create table test( id int unsigned auto_increment primary key, name varchar(20) not null  )engine=myisam charset=utf8;
Query OK, 0 rows affected (0.01 sec)

#插入数据
mysql> insert into test(name) values('联想tinkpad'),('Dell外星人'),('雷神玄武'),('mac pro');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

#复制生成多条数据 多次执行
mysql> insert into test select null,name from test;
Query OK, 1048576 rows affected (2.34 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

#查看当前文件大小 找到MySQL数据文件位置
pro@localhost ~$ ps -ef | grep mysql
  501   413     1   0 五09上午 ??         0:00.03 /bin/sh /usr/local/opt/mysql/bin/mysqld_safe --bind-address=127.0.0.1 --datadir=/usr/local/var/mysql

#去数据文件位置查看数据文件大小
pro@localhost test$ ls -hl /usr/local/var/mysql/test/test.*
-rw-r-----  1 pro  admin    46M  7  6 10:05 /usr/local/var/mysql/test/test.MYD
-rw-r-----  1 pro  admin    21M  7  6 10:05 /usr/local/var/mysql/test/test.MYI
-rw-r-----  1 pro  admin   8.4K  7  6 10:02 /usr/local/var/mysql/test/test.frm

#压缩数据文件 myisampack
pro@localhost test$ myisampack test
Compressing test.MYD: (2097152 records)
- Calculating statistics
- Compressing file
46.63%     
Remember to run myisamchk -rq on compressed tables

#查看压缩后文件大小
pro@localhost test$ ls -hl /usr/local/var/mysql/test/test.*
-rw-r-----  1 pro  admin    25M  7  6 10:05 /usr/local/var/mysql/test/test.MYD
-rw-r-----  1 pro  admin   1.0K  7  6 10:11 /usr/local/var/mysql/test/test.MYI
-rw-r-----  1 pro  admin   8.4K  7  6 10:02 /usr/local/var/mysql/test/test.frm

#压缩后发现索引失效了，回复索引
pro@localhost test$ myisamchk -rq test
- check record delete-chain
- recovering (with sort) MyISAM-table 'test'
Data records: 2097152
- Fixing index 1

#在查看
pro@localhost test$ ls -hl /usr/local/var/mysql/test/test.*
-rw-r-----  1 pro  admin    25M  7  6 10:05 /usr/local/var/mysql/test/test.MYD
-rw-r-----  1 pro  admin    20M  7  6 10:14 /usr/local/var/mysql/test/test.MYI
-rw-r-----  1 pro  admin   8.4K  7  6 10:02 /usr/local/var/mysql/test/test.frm

#解压 
pro@localhost test$ myisamchk --unpack test
- recovering (with sort) MyISAM-table 'test'
Data records: 2097152
- Fixing index 1

#查看
pro@localhost test$ ls -hl /usr/local/var/mysql/test/test.*
-rw-r-----  1 pro  admin    44M  7  6 10:17 /usr/local/var/mysql/test/test.MYD
-rw-r-----  1 pro  admin    20M  7  6 10:17 /usr/local/var/mysql/test/test.MYI
-rw-r-----  1 pro  admin   8.4K  7  6 10:02 /usr/local/var/mysql/test/test.frm

五：延迟更新索引键

1. 应用：
   1. 表已经存在则启用   ALTER TABLE table_name DELAY_KEY_WRITE= 1;  
   2. 表创建是指定           DELAY_KEY_WRITE
2. 注意事项：
   1. 开启后索引更新不会直接写入磁盘，而是写入缓存区，只有清理缓存区或者关闭表是才会写入磁盘；
   2. 服务器崩溃，重启，断电等操作 缓存区索引丢失，需修复
      1. myisamchk  重建索引
      2. 启动mysql时加参数 --myisam-recover  #检查表并同步表和索引
      3. 修改配置文件启动项 ：

         [mysqld]
         myisam-recover=BACKUP,FORCE
         
         #值有4个   
         #DEFAULT  不开启
         #BACKUP   恢复过程中数据文件被修改，将数据文件备份
         #FORCE    数据文件丢失多行数据也恢复
         #QUICK    如果没删除块，不检查表中行