mysql 数据库储存引擎

mysql存储引擎

查看存储引擎

mysql> show engines \G;
*************************** 1. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
5 rows in set (0.00 sec

myisam

是默认的存储引擎，使用高级缓存和索引机制。当各种应用程序需要快速检索数据而不需要事务的时候，myisam将是很好的选择

memory

memory存储引擎是内存中的存储器，他使用哈希机制频繁检索被使用的数据，这样可以更快的检索。使用于：静态数据被频繁使用并且很少被改变时（例如查找表）

merge

merge存储引擎的最佳特性就是速度，他允许将一个大表分割成许多不同的小块，并存储到不同的磁盘上，可以使用merge表将这些小块合并，并且可以同时访问这些小块。缺点就是：必须使用相同的myisam表组成一个合表，替换操作不可用。

myisam存储引擎的优化

提高性能的方法分为3种：优化磁盘存储，通过监控和优化key cache来有效使用内存，以及优化数据库表
1）优化数据表
Analyze table用于检测和重组表的关键字分布情况当通过字段而非长量的方式进行表链接的时候，Mysql通过关键字的分布情况来决定表的链接顺序。
Repair table用于恢复和修复奔溃或运行很慢的表，
Optimize table命令用于恢复被删除的快和重组表；
2）按照索引的顺序存储表
Myisqmchk -R 并指定所使用的索引，索引编号从1开始
3）压缩表
压缩数据库可以节约空间，
Myisqmpack -b /usr/local/mysql/data/test/data1
压缩数据之前，用备份选项-b来创建表的备份副本，这样可以在不重新运行myisampack命令的情况下，使表变位可写的。
为什么要压缩只读表：首先它可以易于压缩，节约存储空间，其次，当查询读取压缩表时，并通过主健或唯一索引来查找表中数据行时，在较其他条件之前，仅对单行数据进行解压缩。
4）监控key cache
key cache是myisam存储引擎提高其性能的主要机制，他缓存了进程被频繁访问的索引块（指向数据的指针），以使索引搜索（和随后的数据）可以被快速的获取
key cache仅用于myisam，索引作为链接列表存储在内存中，可以很快被检测到，key cache在第一个myisqm数据表读取时自动创建。每次查询myisam数据表之前，都会检查一遍key cache。如果在缓存中找到索引，则直接在内存中执行索引检索，而不需要先在磁盘上读取索引。key cache是myisam的快速查询比其他存储引擎都快的秘密武器
查询主缓存的状态和系统变量

mysql> show status like 'Key%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 6694  |
| Key_blocks_used        | 1     |
| Key_read_requests      | 5     |
| Key_reads              | 2     |
| Key_write_requests     | 0     |
| Key_writes             | 0     |
+------------------------+-------+
mysql> show variables like 'Key%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| key_buffer_size          | 8384512 |
| key_cache_age_threshold  | 300     |
| key_cache_block_size     | 1024    |
| key_cache_division_limit | 100     |
+--------------------------+---------+

如果想提高缓存命中率的话，可以使用一下两种方法：预加载缓存；使用多个key cache并为默认的key cache分配更多的内存
1)预加载缓存
将索引预加载到key cache可以加快查询速度，因为索引已经加载到缓存，并且是按顺序加载的，并且是按顺序加载的，然而必须保证缓存中有足够的空间存储索引，预加载缓存是提高查询速度的有效方法。
预加载缓存到主内存中：

mysql> load index into cache stu ignore leaves;
+------------+--------------+----------+----------------------------------+
| Table      | Op           | Msg_type | Msg_text                         |
+------------+--------------+----------+----------------------------------+
| school.stu | preload_keys | Error    | Table 'school.stu' doesn't exist |
| school.stu | preload_keys | status   | Operation failed                 |
+------------+--------------+----------+----------------------------------+

Ignore leaves语句表明只预加载索引的非叶子节点
2)使用多个key cache
创建多个key cache或自定义key cache，以减少对默认key cache的争用.该特性允许将一个或多个表的索引加载到自定义到特殊缓存中，这就意味着按任务分配内存。如果某段时间内对一组表执行大量的查询操作。且频繁引用这些表上的索引，那么这种策略将大大提高数据库系统的性能。
要创建一个二级key cache，首先需要使用set命令来分配内存，然后执行一个或多个cache index 命令去加载表的索引，可以通过将二级key cache的大小设置为0将其移除。
使用二级主缓存

mysql> set global student_cache.key_buffer_size=128*1024;
Query OK, 0 rows affected (0.08 sec)
mysql> cache index stu in student_cache;

mysql> set global student_cache.key_buffer_size=0;
Query OK, 0 rows affected (0.00 sec)

可以通过以下方式查看一个二级缓存是否存在；

mysql> select @@global.student_cache.key_buffer_size;
+----------------------------------------+
| @@global.student_cache.key_buffer_size |
+----------------------------------------+
|                                 131072 |
+----------------------------------------+
1 row in set (0.00 sec)

由于二级key cache是全局的因此只有将其大小设置为0或重新启动服务器时，才存在二级key cache；

innodb存储引擎

innodb表使用聚集索引，聚集索引是一种数据结构，他不仅存储索引，还存储数据本身，也就说一旦定位到索引中的某个值时，就可以直接检索数据而无须而外的磁盘寻道。主键索引或者表的第一个唯一索引都采用聚集索引来创建。
如果创建了二级索引，聚集索引的关键字信息都会保存在二级索引中，这样可以快速定位和回访聚集索引中的原始数据，

缓冲池是用于管理事务和读写磁盘数据的缓存机制，如果配置得到，将会提高磁盘的访问速率。缓冲池同时还是崩溃回复的一个重要组成部分。
innodb使用缓冲池来存储数据变更和事务，innodb通过将数据变更保存到缓冲池中的数据页进行缓存，每次引用数据页都会放到缓冲池中，发生改变以后就标记为dirty，然后，这个改变被写到磁盘以更新数据，并向日志中写入一个副本。这些日志文件的名字为ib_logfile/
1)使用show engine

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 

2)监控日志文件

mysql> show status like 'Innodb%log%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Innodb_log_waits             | 0     |当文件太小时，操作必须等待日志刷新
| Innodb_log_write_requests    | 0     日志写入请求的数量
| Innodb_log_writes            | 1     |数据被写入日志的次数
| Innodb_os_log_fsyncs         | 3     |操作系统同步文件的数量
| Innodb_os_log_pending_fsyncs | 0     |长期大于0，需要检查磁盘访问问题，
| Innodb_os_log_pending_writes | 0     |阻塞日志写请求的次数
| Innodb_os_log_written        | 512   | 写道日志中的字节总量
+------------------------------+-------+
7 rows in set (0.00 sec)

3）监控缓冲池
缓冲池是innodb缓存访问数据的地方，对缓冲内数据的任何更新也会被缓存。缓冲池还存储当前事务的相关信息。因此缓冲池是关乎性能的关键机制

mysql> show status like 'Innodb%buf%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_pages_data     | 19    |
| Innodb_buffer_pool_pages_dirty    | 0     |
| Innodb_buffer_pool_pages_flushed  | 0     |
| Innodb_buffer_pool_pages_free     | 493   |
| Innodb_buffer_pool_pages_misc     | 0     |
| Innodb_buffer_pool_pages_total    | 512   |
| Innodb_buffer_pool_read_ahead_rnd | 1     |
| Innodb_buffer_pool_read_ahead_seq | 0     |
| Innodb_buffer_pool_read_requests  | 77    |
| Innodb_buffer_pool_reads          | 12    |
| Innodb_buffer_pool_wait_free      | 0     |
| Innodb_buffer_pool_write_requests | 0     |
+-----------------------------------+-------+