Mysql从入门到放弃--存储引擎

最新推荐文章于 2024-08-26 00:58:31 发布

Petrus_shuai

最新推荐文章于 2024-08-26 00:58:31 发布

阅读量77

点赞数

分类专栏：数据库文章标签： MySQL 存储引擎

本文链接：https://blog.csdn.net/luan7liem/article/details/90523282

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

一.索引压力测试

先准备压力测试文件
为了测试我们创建一个web01的库创建一个t1的表，然后导入50万行数据，脚本如下：

1.创建一个slap.sh的文件
vim slap.sh
2.编辑测试脚本
#!/bin/bash  
HOSTNAME="localhost" 
PORT="3306" 
USERNAME="root" 
PASSWORD="123" 
DBNAME="web01" 
TABLENAME="t1" 
#create database 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}" 
create_db_sql="create database if not exists ${DBNAME}" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}" 
#create table 
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1)   
not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100)default null)" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}" 
#insert data to table 
i="1" 
while [ $i -le 500000 ]  
do  
insert_sql="insert into ${TABLENAME}  values($i,'alexsb_$i','1','110011198809163418','1990-05-16','2017-09-13','oldboyedu')" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}" 
let i++  
done  
#select data  
select_sql="select count(*) from ${TABLENAME}" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"
3.执行测试脚本
[root@web01 ~]# sh slap.sh 1>/dev/null 2>&1   --标准输出到空设备文件，标准错误输出也重定向到空设备文件
4.检查数据可用性
mysql> select count(*) from t1;
5.在没有优化之前我们使用mysqlslap来进行压力测试
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t1 where stuname='alexsb_100'" engine=innodb \
--number-of-queries=200000 -uroot -p123456 -verbose

二.存储引擎

存储引擎的种类

InnoDB
MyISAM
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV
还可以使用第三方存储引擎。
插件类存储引擎,是在表级别设定的.
MySQL 5.5 以后默认存储引擎innodb
TokuDB ---->zabbix : insert 2-3 ,压缩比 8-10倍
myrocks

MySQL存储引擎介绍(1)
MySQL存储引擎介绍(2)
1.文件系统
操作系统组织和存取数据的一种机制
文件系统是一种软件

2.类型ext2 3 4 ，xfs 数据
不管使用什么文件系统，数据内容不会变化
不同的是，存储空间、大小、速度。

3.MySQL引擎
可以理解为，MySQL的“文件系统”，只不过功能更加强大。

4.MySQL引擎功能
除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。
Innodb存储引擎与MyISAM区别

Innodb	MyISAM
MVCC(多版本并发控制)	不支持
锁(行级别)	表级别
外键	不支持
事务	不支持
热备	温备
CSR(断电时,故障自动恢复)	不支持
表的名字.frm，表的名字.ibd	表的名字.frm，表的名字.MYD，表的名字.MYI

查看存储引擎

show engines;                    
show create table world.city;

SELECT @@default_storage_engine;   --查看默认的存储引擎
show table status like 'city';
show table status like 'city'\G

Innodb存储引擎物理存储方式
表空间(TBS)管理模式:

来自于Oracle
共享表空间:----->类似于Oracle管理模式
独立表空间:每个表单独使用表空间存储

5.5默认:共享表空间
5.6以后:表默认管理模式是独立表空间,共享表空间也存在

表空间存储数据分类

共享表空间: ibdata1 
存了什么?
系统数据
undo日志
磁盘临时表

独立表空间:
t1.ibd    ---->t1表的数据和索引
t1.frm    ---->t1表的列相关信息

表空间操作

共享表空间:
我们建议,在初始化数据的时候就配置好
一般建议2-3个,512M
/etc/my.cnf
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend
mysql_install_db 

独立表空间: 
一个表一个ibd文件,存储表数据和索引
show variables like 'innodb_file_per_table';

innodb 的表
表数据和索引:t1.ibd 
列的基本信息:t1.frm 
其他的属性信息:基表

alter table t1 discard tablespace;   将表的ibd文件删掉
alter table t1 import tablespace;    将idb文件导入回来

企业案例

jira(bug追踪) 、 confluence(内部知识库)    ------>LNMT 

-------------------
联想服务器（IBM）   
磁盘500G 没有raid
centos 6.8
mysql 5.6.33  innodb引擎  独立表空间
备份没有，日志也没开
------------------

编译→制作rpm

/usr/bin/mysql

/var/lib/mysql

confulence   jira

所有软件和数据都在"/"
------------
断电了，启动完成后“/” 只读

fsck   重启

启动mysql启动不了。

结果：confulence库 在  ， jira库不见了
------------
求助：
	这种情况怎么恢复？
	
我问：
	有备份没
求助：
	连二进制日志都没有，没有备份，没有主从
我说：
	没招了，jira需要硬盘恢复了。
求助：
	1、jira问题拉倒中关村了
	2、能不能暂时把confulence库(107张表)先打开用着	
	将生产库confulence，拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的
	
问：有没有工具能直接读取ibd

我说：我查查，最后发现没有


我想出一个办法来：
create table xxx
alter table  confulence.t1 discard tablespace;
alter table confulence.t1 import tablespace;


虚拟机测试可行。

CREATE TABLE `city_new` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  KEY `idx_popu` (`Population`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;


面临的问题，confulence库中一共有107张表。
1、创建107和和原来一模一样的表。

他有2016年的历史库，我让他去他同事电脑上 mysqldump备份confulence库
mysqldump -uroot -ppassw0rd -B  confulence --no-data >test.sql

拿到你的测试库，进行恢复
	到这步为止，表结构有了。
	
2、表空间删除。
select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
	source /tmp/discard.sql
	
	执行过程中发现，有20-30个表无法成功。主外键关系
	很绝望，一个表一个表分析表结构，很痛苦。
	set foreign_key_checks=0 跳过外键检查。

	把有问题的表表空间也删掉了。
3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中
	select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
	
4、验证数据
	表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）