七、MYSQL数据库存储引擎

一、MySQL存储引擎概述

什么是存储引擎？

在讲清楚什么是存储引擎之前，我们先来个比喻，我们都知道录制一个视频文件，可以转成不同的格式如mp4，avi，wmv等，而存在我们电脑的磁盘上也会存在于不同类型的文件系统中如Windows里常见的ntfs，fat32，存在于Linux里常见的ext3，ext4，xfs，但是，给我们活着用户看到实际视频内容都是一样的。直观区别是，占用系统的空间大小与清晰程度可能不一样。
那么，数据库表里的数据存储在数据库里及磁盘上和上述的视频格式及存储磁盘文件系统格式特征类似，也有很多存储方式。
但是，对于用户和应用程序来说同样一张表的数据，无论用什么引擎来存储，用户看到的数据都是一样的。不同的引擎存取，引擎功能占用的空间大小，读取性能等可能有区别。
MYSQL最常用存储引擎为：Myisam和innodb。全文索引：目前5.5版本，
Myisam和innodb都已经支持。

MySQL存储引擎的架构？

MYSQL的存储引擎是MYSQL数据库的重要组成部分，MYSQL常用的表的引擎为MyISAM和InnoDB两种。MYSQL的每种存储引擎在MYSQL里是通过插件的方式使用的，MYSQL可以同时支持多种存储引擎。下面是MYSQL存储引擎体系结构简图：

二、MyISAM引擎介绍

什么是MyISAM引擎？

MyISAM引擎是MySQL关系数据库管理系统的默认存储引擎(MYSQL5.5.5以前)。这种MYSQL表存储结构从旧的ISAM代码扩展出许多有用的功能。在新版本的MYSQL中InnoDB引擎由于其对事物参照完整性，以及更高的并发性等优点开始逐步的取代Myisam引擎。

每一个MyISAM表都对应硬盘上的三个文件。这三个文件有一样的文件名，但是有不同的扩展名指示其类型用途：frm文件保存表的定义，这个文件并不D是MyISAM引擎的一部分，而是服务器的一部分；MYD保存表的数据；MYI是表的索引文件。MYD和.MYI是MyISAM的关键点

MyISAM引擎特点

  • 不支持事务(事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全功能要么全失败)
  • 表级锁定（更新时锁整个表）：其锁定机制使表级索引，这虽然可以让锁定的现实成本很小但是也同时大大降低了其并发性能。
  • 读写互相阻塞:不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。
  • 只会缓存索引：MyISAM可以通过key_buffer_size缓存索引，以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据
    [root@localhost ~]# grep key_buffer /etc/my.cnf
    key_buffer_size = 16M
  • 读取速度较快。占用资源相对少
  • 不支持外键约束，但支持全文索引
  • MyISAM引擎是MYSQL 5.5.5前缺省的存储引擎

MyISAM引擎适用的生产业务场景

  • 不需要事务支持的业务(列如转账就不行，充值付款)，一般为读数据比较多
    网站应用。
    读写都频繁不合适，读多或者写多的都适合
  • 读写并发相对低的业务(纯读纯写高并发也可以)(锁定机制问题)
  • 数据修改相对较少的业务(阻塞问题)
  • 以读为主的业务，列如：www,blog,图片信息数据库，用户数据库，商品库等业务。
  • 对数据一致性要求不是非常高的业务
  • 中小型的网站的部分业务会用

小结：单一对数据库的操作都可以使用Myisam，所谓单一就是尽量纯读，或纯写(insert,update,delete)等。

MyISAM引擎调优

  • 设置合适的索引(缓存机制)
  • 调整读写优先级，根据实际需求确保重要操作更游戏执行
  • 启用延迟插入改善大批量写入性能(降低写入频率，尽可能多条数据一次性
    写入)
  • 尽量顺序操作让insert数据都写入到尾部，减少阻塞。
  • 分解大的操作，降低单个操作的阻塞时间
  • 降低并发数(减少对MYSQL访问)，某些高并发场景通过应用进行排队队列
    机制q队列。
  • 对于相对静态(更改不频繁)的数据库数据，充分利用query cache或memcached缓存服务可以极大的提高访问效率。
  • MyISAM的count只有在全表扫描的时候特别高效，带有其他条件的count
    都需要进行实际数据访问
  • 可以把主从同步的主库使用innodb，从库使用Myisam引擎

三、InnoDB引擎

什么是InnoDB引擎？

Innodb引擎是MYSQL数据库的另一个重要的存储引擎，正成为目前MYSQL AB所发行新版的标准，被包含在所在二进制安装包。和其他的存储引擎相比，Innodb引擎的优点是支持兼容ACID的事务(类似于postgreSQL ),以及
参考完整性(即对外键的支持)。Oracle公司与2005年10月收购了innobase。
Innobase采用双认证授权。它使用GUN发行，也允许其他想将innodb结合到商业软件的团队获取授权。

MYSQL5.5.5 以后的数据库的默认存储引擎为Innodb

InnoDB引擎特点

  • 支持事务：支持4个事务隔离级别，支持多版本读
  • 行级锁定（更新时一般是锁定当前行）：通过索引实现，全表扫描仍然会是
    表锁，注意间隙锁的影响。
  • 读写阻塞与事务隔离级别相关
  • 具有非常高效的缓存特性：能缓存索引，也能缓存数据
  • 整个表和主键以cluster方式存储，组成一颗平衡树
  • 所有secondary index都会保存主键信息
  • 支持分区，表空间，类似oracle数据库
  • 支持外键约束，不支持全文索引5.5以前，以后支持了
  • 和Myisam引擎比，innodb对硬件资源要求比较高

InnoDB引擎适用的生产业务场景

  • 需要事务支持的业务（具有较好的事务特性）
  • 行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
  • 数据更新较为频繁的场景，如BBS,SNS,微博等
  • 数据一定性要求较高的业务，列如：充值转账，银行卡转账
  • 硬件设备内存较大，可以利用innodb较好的缓存能力来提高内存利用率，
    尽可能减少磁盘IO
  • 相比Myisam引擎，innodb引擎更消耗资源，速度没有Myisam引擎快

InnoDB引擎调优

  • 主键尽可能小，避免给secodary index带过大的空间负担

  • 避免全表扫描，因为会使用表锁

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

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

  • 合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性
    如果innodb_flush_log_at_trx_commit的值为0，log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作

  • 避免主键更新，因为这会带来大量的数据移动

四、如果确定MYSQL服务器有哪些引擎可用？

你可以在mysql中使用显示引擎的命令得到一个可用引擎的列表：
mysql> show engines;   5.1版本
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

五、MYSQL引擎的my.cnf对应参数说明

Innodb 引擎重要参数：
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 16M         #缓存（内存的百分之50-80）
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
posted @ 2018-06-12 20:12  云原生运维社区  阅读(192)  评论(0)  编辑  收藏  举报