走向DBA之存储引擎(1)
2019-06-22 本文已影响0人
国王12
一、存储引擎介绍：
类似于linux系统中的文件系统，但又略高于linux的文件系统
二、功能了解：
1.数据读写
2.数据安全和一致性
3.提高性能
4.热备份
5.自动故障恢复
6.高可用方面支持
7.等等
三、存储引擎种类：
3.1Oracle的MySQL
InnoDB 主流
MyISAM
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV
3.2MySQL支持的其他存储引擎：
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持: TokuDB RocksDB MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高 现在很多的NewSQL,使用比较多的功能特性.
3.3查看当前数据库支持的存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
#yes代表支持，no代表暂不支持，default代表默认。
3.3查看所有库下的所有表分别使用的什么存储引擎
select table_schema,table_name ,engine from information_schema.tables;
次数省略若干行...
四、InnoDB存储引擎介绍：
image.png
4.1InnoDB核心特性，也是与MyISAM最大的区别（InnoDB有的，MyISAM没有）
事务（Transaction）
行级锁 (Row-level Lock)
MVCC（Multi-Version Concurrency Control多版本并发控制）
外键
热备份(Hot Backup)
ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
复制 Replication: Group Commit , GTID (Global Transaction ID) ,
多线程(Multi-Threads-SQL )
五、存储引擎操作类语句:
5.1使用select确认会话存储引擎
SELECT @@default_storage_engine;
5.2模糊查找
mysql> show variables like '%engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)
5.3修改默认存储引擎
在线修改MySQL参数，分为两种级别
第一种：只影响当前会话(会话级别)
set default_storage_engine=myisam; 修改当前会话的存储引擎为myisam
第二种：不影响当前会话和历史会话，仅影响新会话（全局级别）
set global default_storage_engine=myisam; 修改新会话存储引擎为myisam
注意：以上两种修改在重启MySQL之后，所有修改均失效
若想要永久修改存储引擎，需要写到MySQL的配置文件里，即my.cnf
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
注意：存储引擎是表级别的，每个表建立的时候可以指定不同的存储引擎，但是建议统一为innodb村粗引擎
5.4show确认每个表的存储引擎：
SHOW CREATE TABLE City\G; City为表名
SHOW TABLE STATUS LIKE 'CountryLanguage'\G
5.5INFORMATION_SCHEMA 确认每个表的存储引擎
select table_schema,table_name ,engine
from information_schema.tables
where table_schema not in ('sys','mysql','information_schema','performance_schema');
(排除sys,mysql,information_schema,performance_schema)
5.6修改一个表的存储引擎
alter table t1 engine innodb;
将t1表的存储引擎更改为innodb
注意：此语句可反复执行，不仅有修改存储引擎之功效，亦有对此表碎片整理之作用。
批量修改oldboy库下的所有表，存储引擎改成innodb
mysql> use oldboyl; 进入oldboy库
create table ceshi(id int) engine=myisam; 创建一个测试表存储引擎为nyisam
mysql> show create table ceshi; 查看ceshi表的建表语句
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------+
| ceshi | CREATE TABLE `ceshi` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
select concat("alter table oldboy.",table_name," engine innodb;") 拼接修改语句
from information_schema.tables
where table_schema='oldboy'
into outfile '/tmp/alter.table.sql'; 把语句打入/tmp下命名为alter.oldboy.sql
source /tmp/alter.oldboy.sql; 执行刚才拼接出来的语句所在的文件即可
5.7碎片处理
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去
现在:
对表进行按月进行分表(partition,中间件)
删除业务替换为truncate方式（这种删除是立即释放磁盘空间）
定时执行：
碎片整理的语句： alter table 表名 engine='innodb'；
它不仅是一个修改存储引擎的语句，多次运行可以整理碎片。
