mysql存储引擎

 

1.引擎介绍&事务

引擎：数据存在数据库中不同的格式和方法

mysql最常用引擎：MyISAM和InnoDB，在mysql5.5.5以前，默认的存储引擎为MyISAM，之后版本默认为InnoDB

比较：InnoDB对事物完整性更好以及有更高的并发性

事务：逻辑上的一组SQL语句操作，组成这组操作的各SQL语句要么全成功要么全失败。

事务的四大特性（ACID）：原子性（不可分割的单位）、一致性、隔离性、持久性——四大特性适用于大多数关系型数据库

2.MyISAM

mysql MyISAM三个系统文件：

表定义文件user.frm、数据文件user.MYD、索引文件user.MYI                  #通过file该文件可以看到其中内容

MyISAM特点：

1）不支持事务

2）表级锁定（更新时锁整个表）

3）读写互相阻塞

4）只会缓存索引（key_buffer_size），以大大提高访问性能减少磁盘IO，但是不会缓存数据

5）读取速度较快，占用资源相对少

6）不支持外键约束，但支持全文索引

MyISAM适用的场景：

1）不需要事务支持的业务

2）一般为读数据比较多的网站应用，读写都频繁的场景不适合，读多或写多的场合适合

3）并发访问相对比较低的业务（纯读纯写高并发也可以）

4）数据修改相对较少的业务

5）以读为主的业务，如www，blog，图片信息数据库，用户数据库，商品库等业务

6）对数据一致性要求不是很高的业务

7）硬件资源比较差的机器还想打到比较好的效果可用MyISAM

MyISAM引擎调优：

1）设置合适的索引

2）调整读写优先级，根据实际需求保证重要操作更优先执行

3）启用延迟插入改善大批量写入性能（降低写入频率，尽可能多条数据一次性写入）

4）尽量顺序操作让insert数据都写入到尾部，减少阻塞

5）分解大的时间长的操作，降低单个操作的阻塞时间

6）降低并发数（减少对mysql访问），对于高并发应用场景可采用排队机制

7）对于更改不频繁的数据，可用Query Cache或memcached缓存服务可以极大的提高访问效率

关于mysql缓存的几个参数：

query_cache_size = 256M

query_cache_limit = 1M

query_cache_min_res_unit = 2k

8）MyISAM的count只有在全表扫描的时候高效，带有其他条件的count都需要进行实际的数据访问

9）把主从同步的主库使用InnoDB，从库使用MyISAM引擎，但是出于维护数据库的角度也不推荐此方法。

3.InnoDB

mysql InnoDB文件：

InnoDB特点：

1）支持事务：ACID

2）行级锁定（更新时一般是锁定当前行），通过索引实现，全表扫描仍然是锁表，所以要建立索引

3）读写阻塞与事务隔离级别有关

4）具有高效的缓存特性

5）整个表和主键以cluster方式存储，组成一棵平衡树

6）所有secondary index都会保存主键信息

7）支持分区，表空间，类似oracle

8）支持外键约束，5.5以前的版本不支持全文索引，以后支持 

9）对硬件资源的要求相比MyISAM高

InnoDB适用的场景：

1）需要事务支持的业务

2）行级锁对高并发有很好的适应能力，但需确保查询通过索引完成

3）数据读写及更新较为频繁的场景

4）数据一致性要求较高的业务

5）硬件设备内存较大，可用InnoDB较好的缓存能力来提高内存利用率，尽可能减少IO

InnoDB的参数，可查：grep innodb /etc/my.cnf

如果想将内容存放在不同的ibdata文件，可通过调整以下参数：

【mysqld】

innodb_file_per_table                             #按表生成文件

innodb_data_home_dir = /data/XXX

6）相比MyISAM引擎，InnoDB引擎更消耗资源，速度没有MyISAM引擎快

InnoDB引擎调优：

1）主键尽可能小，避免secondary index带来过大的空间负担

2）避免全盘扫描

3）尽可能缓存所有的索引和数据，提高响应速度，减少磁盘IO消耗

4）在大批量小插入的时候，尽可能自己控制事务而不是使用autocommit自动提交，有开关可以控制提交方式

5）合理设置innodb_flush_log_at_trx_commit参数值（代表刷到磁盘上的频率），不要过度追求安全

6）避免主键更新，因为会造成大量的数据移动

4.mysql引擎实战

如何查看mysql里有几个引擎可以用？

更改引擎：

方法1）alter table student ENGINE=MyISAM;

方法2）使用sed对备份内容进行引擎转换，数据量会很大：

mysqldump > oldboy_1.sql

nohup sed -e 's/MyISAM/InnoDB/g' oldboy_2.sql > oldboy_1.sql

mysql < oldboy_1.sql

方法3）mysql_convert_table_format命令修改：

#!/bin/bash

cd /usr/local/mysql/bin

echo 'Enter Host Name:'

read HOSTNAME

echo 'Enter User Name:'

read USERNAME

...

./mysql_convert_table_format --host=$HOSTNAME --user=$USERNAME --password=$PASSWD --socket=$SOCKETPATH --type=$TBTYPE $DBNAME $TBNAME

即mysql_convert_table_format --user=root --password=456 --socket /data/3306/mysql.sock --engine=MyISAM oldboy t2                 #改oldboy库表t2的引擎模式

#对于my.cnf要配置的参数，可以在以下位置找到官方的配置参数文件：

 

2018年11月7日

祝好！