8-存储引擎、缓存

1、存储引擎

1.1 myisan与innodb

• 存储引擎决定数据以何种方式存储在硬盘里。mysql最常用myisam和innodb两种，默认使用innodb。

show engines\G                                   //查看当前版本数据库可支持的存储引擎
show variables like '%storage_engine%';          //查看当前存储引擎
set storage_engine=myisam                        //修改存储引擎，临时生效。
注：若永久生效，需在配置文件[mysqld]组下添加{default-storage-engine=myisam}

• linux版mysql数据默认存储路径/var/lib/mysql
• 对比：

引擎类别	优点	缺点	适用场景	存储数据方式
myisan	读取数据的速度比较快 支持全文索引 支持表级锁	不支持外键 不支持事务	多读场景	每张表分成三个文件来存储: - 表名.frm: 存放表结构 - 表名.MYD: 存放表数据 - 表名.MYI: 存放表索引
innodb	支持外键 支持事务 支持行级锁	容易出现死锁	大并发量 多写入	表数据存在表空间中: - 表名.frm: 存放表结构 - 表名.ibd: 存放表数据

1.2 innodb的表空间

• 表空间即存储数据的文件，共享表空间所有数据存放在同一个表空间里，独立表空间将数据存储在每个以表命名的文件里。mysql表空间与oracle表空间不是一个概念。
• 查看表空间方法：

mysql> show variables like '%innodb_data%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+
2 rows in set, 1 warning (0.01 sec)

其中ibdata1就是共享表空间。

• 查看是否开启独立表空间：

mysql> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set, 1 warning (0.01 sec)

# 修改方式：需在配置文件[mysqld]组下添加{innodb_file_per_table=off}配置。
注：若在开启独立表空间之前，已有innodb表，把表数据同步到独立表空间，需要手动执行以下语句：
mysql> alter table 表名 engine=innodb;

• 当ibdata1增长到一定大小时，用其他文件来存储数据。配置方法如下：

[mysqld]组下添加{innodb_data_file_path=ibdata1:250M;ibdata2:10M:autoextend}
注：当ibdata1数据达到250M时，数据存储在初始10M并能自动扩展的ibdata2表空间
若ibdata2不想存储在默认的/var/lib/mysql下时，可在[mysqld]组下添加{innodb_data_home_dir=/xxx}

• 当删除数据时，表空间文件大小不会释放。

2、缓存

2.1 连接缓存

• 每个客户端连接都要分配一个id，都要消耗宿主服务器资源。
• 缓存：连接池可以先预备几个连接需要消耗的资源，无需再分配资源，提高连接速度。
• 参数查看：

show processlist;                                 查看建立的连接
show variables like '%thread_cache_size%';        查看可用连接的值，默认是0
参考：宿主服务器内存为nG,n小于4时，设置8n个，大于4设置32个。

• 设置方法：

mysql>set global thread_cache_size=4;
上述为临时生效，若永久生效，需在配置文件添加配置

2.2 表缓存

show open talbes;                                 查看放在缓存的表
flush tables;                                     将表缓存写入磁盘
show variables like '%table_open_cache%';         可以缓存多少张表

2.3 查询缓存

• 缓存查询结果，当下次有相同查询语句进行查询时，直接再缓存中返回结果。查询缓存默认开启。
• 查询参数：

set profiling=1;                                   开启统计功能，0为关闭
show profiles;                                     查看统计结果

show variables like '%query_cache_type%';          查看是否开启查询内存
show variables like '%query_cache_size%';          查看查询缓存空间大小
set global query_cache_size=1024*1024              设置1M查询缓存

2.4 索引缓存（了解）

• 查看索引缓存大小：

show variables like '%key_buffer_size%';
注：若索引缓存空间用完，若读取新数据产生新索引缓存时，会删除最近最少使用的索引缓存（即LRU原则）

• 设置特定表的索引缓存，无法被LRU清除。

mysql> set global 缓存名字.key_buffer_size=1024*1024;      创建一个1M大小的索引缓存
mysql> cache index 表名 in 缓存名字;                        将表名与索引缓存做关联
mysql> load index into cache 表名;                         将表名加到缓存里