首先,我们的知道什么是存储引擎:MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。
常见存储引擎:
- InnoDB:MySQL5.5及以后默认存储引擎
- 支持事务,可通过一些机制和工具实现热备份;
- 实现了MVCC,支持行锁,实现了事务的四个隔离级别,默认REPETABLE READ,并通过间隙锁策略防止幻读出现;
- 数据表会分成 .frm(表结构) .idb(索引和数据)两个文件进行存储;
- 支持外键,存储数据时每张表的数据均按照主键顺序存放,如果没指定主键,每行数据会默认生成1个6字节的ROWID,作为主键。
- 聚簇索引,主键查询性能很高,非主键索引必须包含主键列,且索引会很大。
- MyISAM:MySQL5.5以前默认存储引擎
- 不支持事务;
- 表锁,读表时共享锁(读锁),写表时排它锁(写锁),并发写入效率低很多;
- 数据表会分成 .frm(表结构) .MYD(数据) .MYI(索引) 三个文件存储
- 不支持外键,但支持全文检索、压缩,延迟更新索引键等
- 全文索引:基于分词创建的索引,一般不用,采用全文索引工具(Sphinx、Lucene、迅搜);
- 压缩:压缩后不能进行更新,但极大的减少磁盘占用,IO会少,从而提高查询性能;
- 延迟更新索引键:不将更新的索引数据立即写到磁盘中,而是写入内存中缓冲区,等清除缓冲区时才将索引写入对应的磁盘,这种方式大大提升了写的性能。
- 非聚簇索引;
下面来个图,对比看看各大存储引擎的区别(MyISAM、InnoDB、BDB、Memory)
扩展:
一:InnoDB如何实现热备份:
- Xtrabackup:网上教程非常多。
- MySQLDump
二:事务并发处理机制-MVCC:Multi-Version Concurrency Control,多版本并发控制。
- 读不加锁,读写不冲突;
- 仅隔离级别为读已提交和可重复读时生效。读已提交能读到未提交的事务行,串行化会锁表均不适用;
- 保存某时间阶段快照,意味着事务可查到一致的数据视图,同样也意味着不同的事务在同一时间查到的数据可能不一致;
- 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<=当前版本号,确保读取的数据是事务开启前的数据;
其实说到底和咱们业务中实现的乐观锁方式极其相似。
三:全文检索:
- MyISAM支持全文检索,非常简单的按照空格、标点等分词,但中文成句不存在空格等标识,无法实现分词,故MySQL自带全文检索仅支持英文全文检索;
- 创建全文索引:ALTER TABLE `table_name` ADD FULLTEXT INDEX fulltext_table(`field1`, `field2`);
- 使用全文检索:我们平时做模糊查询使用 'like' 关键字,但并不能命中索引。语法:SELECT * FROM `table_name` WHERE MATCH(`field1`, `field2`) AGAINST ('string');
- MySQL指定最小拆分字符长度,默认为4,修改:my.ini ft_min_word_len = 2 #修改最小字符为2;
- 匹配会有权重概念,如果一个检索词在多条记录中频繁出现权重会低(甚至会低到为0,没条数据中均出现),MySQL默认权重高于50%不会返回;
- 总结:在开发过程中自带的全文检索没用过,我到目前为止服务的客户群体均且仅提供中文服务,故需做中文全文检索。如果非要用MySQL提供全文检索,需在创建一索引表,将中文转化为英文,然后在进行匹配。
- base64编码 -输入拼音查询不到结果
- 转化为拼音<网上资料很多>
- 使用 中文检索引擎 如 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
五:延迟更新索引键
- 应用:
- 表已经存在则启用 ALTER TABLE table_name DELAY_KEY_WRITE= 1;
- 表创建是指定 DELAY_KEY_WRITE
- 注意事项:
- 开启后索引更新不会直接写入磁盘,而是写入缓存区,只有清理缓存区或者关闭表是才会写入磁盘;
- 服务器崩溃,重启,断电等操作 缓存区索引丢失,需修复
- myisamchk 重建索引
- 启动mysql时加参数 --myisam-recover #检查表并同步表和索引
- 修改配置文件启动项 :
[mysqld] myisam-recover=BACKUP,FORCE #值有4个 #DEFAULT 不开启 #BACKUP 恢复过程中数据文件被修改,将数据文件备份 #FORCE 数据文件丢失多行数据也恢复 #QUICK 如果没删除块,不检查表中行