【Mysql】存储引擎，事务，索引，备份

最新推荐文章于 2024-07-21 22:55:00 发布

密西西凌

最新推荐文章于 2024-07-21 22:55:00 发布

阅读量103

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/weixin_43658009/article/details/89058749

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL5.5.5之前的默认存储引擎。
⽀持⼤⽂件，通常认为是 256TB。（相应操作系统实现的限制）
适合读多写少的应⽤场景。
每个MYISAM表的最⼤索引数是64，可以通过编译改变。
BLOB和TEXT类型可以被索引。
⽀持全⽂索引。（只有MYISAM⽀持全⽂索引）
仅⽀持表锁。（⽆论的读锁还是写锁）
⾮事务性引擎。

InnoDB

⽬前MySQL的默认存储引擎。
事务性引擎。
⽀持⾏锁和外键。
为巨⼤数据量的最⼤性能设计。
.ibdata1⾃动扩展数据⽂件。ib_logfile0/1⽇志⽂件。

memory

将数据存储到内存中的存储引擎。
快。
不⽀持BLOB和TEXT列。
⾮事务性引擎。

NDB

⽤于MySQL Cluster的集群存储引擎。
提供数据层⾯上的⾼可⽤性。
例如多表查询等复杂性查询性能相对不是优势。（对复杂性查询速度相⽐较MYISAM和InnoDB都差了很多）

在这里插入图片描述

查看数据库有的存储引擎 
> show engines \G;

create 定义存储引擎 
> create table emp(id int,name varchar(11))engine=myisam;


alter table语句修改
> alter table emp engine=innodb;

MySQL事务

是由⼀组SQL语句组成的逻辑处理单元，要么全部执行，要么全部不执行。
事务四大特性（ACID）

Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即事务不可分割、不可约简。
Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括未提交读（Read uncommitted）、提交读（read committed）、可重复读（repeatable read）和串行化（Serializable）。
Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务   transaction   指定⼀组sql语句
回滚事务    rollback      撤销指定的sql语句(只能回退insert delete update语句)
提交事务    commit        提交未存储的sql语句
保存回滚点  savepoint     事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)

回滚
mysql> start transaction;
mysql> delete from emp;
mysql> select * from emp;
mysql> rollback;       #回滚到开始之前

提交
mysql> begin;
mysql> insert into emp values (3,"power");
打开另⼀个终端执⾏查看
mysql> select * from emp ;
mysql> commit;

保留点
mysql> begin ;
mysql> insert into emp values (4,"nothing");
mysql> savepoint insert1;
mysql> select * from emp;   （增加了4）

mysql> delete from emp where id=4;
mysql> savepoint delete1;
mysql> select * from emp;    （减少了4）

mysql> delete from emp where id=3;
mysql> savepoint delete2;
mysql> select * from emp;    （减少了3）

mysql> rollback to delete1;
mysql> select * from emp;     （回到减少4那里）

mysql> rollback to insert1;
mysql> select * from emp;   （回到增加4那里）

事务的最后结果 要么是commot 要么是 rollback
如果发⽣错误 或者数据库重启了 系统重启了 做rollback

MySQL索引

索引优势

所有的Mysql列类型都可以被索引，也就是可以给任意字段设置索引
大大加快数据的查询时间，相对于的降低了增删改的能力

索引缺点

创建索引和维护索引需要耗费时间，并且随着数据量的增加所耗费的时间也会增加；
索引页需要占用空间，数据表中的数据也会有最大上限值；如果存在大量的索引，索引文件可能会比数据文件更快达到上限值；
当对表的数据进行增加、修改、删除时，索引也要动态维护，降低了数据的维护速度。

索引的分类

普通索引 （key） 是mysql的基本索引类型。
唯⼀索引 （unique key） 对应列的值必须唯⼀，但允许空值。如果是组合索引，则列值的组合必须唯⼀。
主键索引 （primary key） 是⼀种特殊的唯⼀索引，不允许有空值。
单列索引指只包含⼀个列的索引。⼀个表中可以有多个单列索引。
组合索引指表的多个字段组合上创建的索引。遵循做前缀集合。（⼀次给多个列创建索引）
全⽂索引 （fulltext） 。可以在 CHAR，VARCHAR或者TEXT类型的列上创建。
仅MYISAM⽀持空间索引，对空间数据类型的字段建⽴的索引。

查看列表索引
1.> show create table t_student \G;
2.> show index from t_student \G;

先建一个数据表t_student,包含四个字段id,name,age,address
> CREATE TABLE t_student (id int not null,name VARCHAR(255) not null,age int DEFAULT 0,address VARCHAR(255) null);


A. 创建一个普通索引   index 或者使用 key
> CREATE TABLE t_student (id int not null,name VARCHAR(255) not null,age int DEFAULT 0,address VARCHAR(255) null,
	INDEX/KEY(address));

如果表已经存在，那么可以使用(含义为以age为索引，创建名为ageIndex的索引)
1.> ALTER TABLE t_student ADD INDEX ageIndex(age);
2.> CREATE INDEX ageIndex on t_student(age);


B. 创建唯一索引     UNIQUE INDEX
对Id建立了唯一索引，并且所索引名为idIndex
> CREATE TABLE t_student (id int not null,name VARCHAR(255) not null,age int DEFAULT 0,address VARCHAR(255) null,
	UNIQUE INDEX idIndex(id));

表已经存在，那么应该为：
1.> CREATE UNIQUE INDEX idIndex on t_student(id desc)
2.> ALTER TABLE t_student add UNIQUE INDEX idIndex(id asc)


C. 创建主键索引    PRIMARY
> CREATE TABLE t_student (id int not null,name VARCHAR(255) not null,age int DEFAULT 0,address VARCHAR(255) null,
	PRIMARY KEY idIndex(id));

表已经存在，那么可以这样：
> ALTER TABLE t_student ADD PRIMARY KEY (id)


D. 创建组合索引
表已经存在，创建(id,name,age)三列为组合索引
1.> CREATE UNIQUE INDEX mulitIndex on t_student(id desc, name, age);
2.> ALTER TABLE t_student add UNIQUE INDEX idIndex(id asc,name, age);


E. 全文索引        FULLTEXT
MyISAM引擎上使用，只能作用到CHAR、VARCHAR、TEXT类型字段上使用全文索引。
> CREATE TABLE t_student (id int not null,name VARCHAR(255) not null,age int DEFAULT 0,address VARCHAR(255) null,
	FULLTEXT INDEX FullTextIndex(address)
 ) ENGINE = MyISAM;

表已经存在，则可以依赖：
1.> ALTER ALTER t_student add FULLTEXT INDEX FullTextIndex(address);
2.> CREATE FULLTEXT INDEX FullTextIndex on t_student(address);

查询时：
> SELECT * FROM t_student WHERE MATCH(address) AGAINST('your key word');


索引删除
1.>ALTER TABLE t_student DROP INDEX idIndex;
2.> DROP INDEX idIndex  on t_student;
删除的是主键索引，并且主键自增长，则需要alter modify先取消自增长再删除




在系统提示符下执行如下语句创建100万行数据
[root@linux ~]# for ((i=1;i<=1000000;i++)) ; do `mysql -uroot -p'Aabo6&W=6yfm' db1 -e "insert into t_15 values($i,floor($i+rand()*$i),md5($i));"`; done

清理查询缓存，防止缓存影响查询结果
> reset query cache;

> insert into t_16 select * from t_15;
> reset query cache;
> select num,pass from t_16 where id<50000 and id<50050;

MySQL数据备份与恢复概述

MYSQLDUMP备份

跨主机备份
使用下面的命令可以将192.168.233.201上的db1复制到192.168.233.202的test,前提是192.168.233.202主机上已经创建test数据库
[root@linux ~]# mysqldump --host=192.168.200.201 -u root -p'密码' db1 | mysql -
-host=192.168.233.202 -u root -p'密码' -C test
 -C指示主机间的数据传输使用数据压缩

逻辑备份mysqldump
备份db1数据库
[root@host1 ~]# mysqldump -uroot -p'密码' --databases db1 >/tmp/db1-backup.sql
恢复
[root@host1 ~]# mysql -uroot -p'密码' < /tmp/db1-backup.sql

备份整个数据库
[root@host1 ~]# mysqldump -uroot -p'密码' --all-databases >/tmp/alldb-backup.sql


备份数据库中的某个表
[root@host1 ~]# mysqldump -uroot -p'密码' db1 t_15 >/tmp/db1_t_15.sql
恢复
[root@host1 ~]# mysql -uroot -p'密码' db1 < /tmp/db1_t_15.sql

只备份表结构
[root@host1 ~]# mysqldump -uroot -p'密码' --no-data --databases db1 world > /tmp/multidb_backup.sql


结合Linux的cron命令实现定时备份。比如需要在每天凌晨1：30备份某个主机上的所有数据库并压缩dumpwen件为gz格式，那么可在/etc/crontab配置wen件中加入下面代码行：
30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip >/mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gzdate '+%m-%d-%Y'得到当前日期的MM-DD-YYYY格式。

INNOBACKUPEX备份

使⽤innobackupex做完整备份
创建一个最小权限的用户进行备份，则可基于如下命令创建此类用户：
mysql> create user 'username'@'localhost' identified by '密码';
mysql> revoke all privileges, grant option from 'username';
mysql> grant reload, lock tables, replication client on *.* to 'username'@'localhost';
mysql> flush privileges;


生成一个完整备份，这条命令将根据my.cnf里datadir的设置来备份数据库(定义数据库数据存放位置)
[root@linux ~]# vim /etc/my.cnf
在里面[mysqld]加入datadir=/var/lib/mysql
[root@linux ~]# innobackupex --user=root --password='密码'/tmp/backupex/

准备(prepare)⼀个完全备份
“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
[root@linux ~]# innobackupex --use-memory=512m --apply-log/tmp/backupex/2019-05-20_19-07-28/

从一个完全备份中恢复数据
[root@linux ~]# innobackupex --copy-back /tmp/backupex/2019-05-20_19-07-28/
这条命令将根据my.cnf里datadir的设置将备份恢复至数据库修改文件权限
[root@linux ~]# chown -R mysql.mysql /var/lib/mysql


使用innobackupex进行增量备份
[root@linux ~]# innobackupex --user=root --password='密码'/tmp/backupex/

第一个增量
[root@linux ~]# innobackupex --incremental /tmp/backupex/mon/ --incremental-basedir=/tmp/backupex/2019-05-20_19-07-28/ --user=root--password='密码'

第二个增量
[root@linux ~]# innobackupex --incremental /tmp/backupex/tue/ --incremental-basedir=/tmp/backupex/mon/2019-05-20_20-18-09/ --user=root --password='密码'

恢复操作就变成了：
[root@linux ~]# innobackupex --apply-log --redo-only/tmp/backupex/2019-05-20_19-07-28/ --user=root --password='密码'
接着执行：
[root@linux ~]# innobackupex --apply-log --redo-only/tmp/backupex/2019-05-20_19-07-28/ --incremental-dir=/tmp/backupex/mon/2017-05-20_18-18-09/ --user=root--password='密码'
而后是第二个增量：
[root@linux ~]# innobackupex --apply-log --redo-only/tmp/backupex/2019-05-20_19-07-28/ --incremental-dir=/tmp/backupex/tue/2019-05-20_20-23-55/ --user=root --password='密码'


其中/tmp/backupex/2019-05-20_19-07-28/ 指的是完全备份所在的目录，
而/tmp/backupex/mon/2019-05-20_20-18-09/ 指的是第一次增量备份的目录，
/tmp/backupex/tue/2019-05-20_20-23-55/ 指的是第二次增量备份的目录，
其它依次类推，即如果有多次增量备份，每一次都要执行如上操作；

恢复最终备份的⽅法与会恢复完整备份的⽅法⼀样。不要忘记修改⽂件和⽬录的权限。