mysql之存储引擎

一 存储引擎概念介绍

MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎
存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式

  一 MySQL常用的存储引擎：

1 MyISAM  

存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式
MySQL常用的存储引擎：MylSAM,InnoDB。
MySQL数据库中的组件，负责执行实际的数据I/O操作。
MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储。

 MyISAM特点：

①MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的

访问速度快，对事务完整性没有要求；

②MyISAM 适合查询、插入为主的应用场景

③MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为：

.frm 文件存储表结构的定义

数据文件的扩展名为 .MYD (MYData)

索引文件的扩展名是 .MYI (MYIndex)

④表级锁定形式，数据在更新时锁定整个表

你在操作表时，所有人不能进行写入和读取，称为表级锁定

⑤数据库在读写过程中相互阻塞: ————》串行操作，按照顺序操作，每次在读或写的时候会把全表锁起来

会在数据写入的过程阻塞用户数据的读取
也会在数据读取的过程中阻塞用户的数据写入
⑥数据单独写入或读取，速度过程较快且占用资源相对少

⑦MyISAM 表支持 3 种不同的存储格式：

（1）静态(固定长度)表

静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。
固定长度10
存储非常迅速，容器缓存，故障之后容易恢复
id（5）  char(10)
000000001

（2）动态表

动态表包含可变字段（varchar），记录不是固定长度的，

优点：是占用空间较少，

缺点：频繁的更新、删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。

（3）压缩表 

压缩表由 myisamchk 工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

 MyISAM适用的生产场景

1、公司业务不需要事务的支持

2、单方面读取或写入数据比较多的业务

3、MyISAM存储引擎数据读写都比较频繁场景不适合

4、使用读写并发访问相对较低的业务

5、数据修改相对较少的业务

6、对数据业务一致性要求不是非常高的业务

7、服务器硬件资源相对比较差

MyIsam 是表级锁定，读或写无法同时进行
优点：分开执行时，速度快、资源占用相对较少（相对）

2 InnoDB

MySQL数据库中的组件，负责执行实际的数据I/O操作
MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储

InnoDB特点

1、支持事务，支持4个事务隔离级别

2、MySQL从5.5.5版本开始，默认的存储引擎为 InnoDB

3、读写阻塞与事务隔离级别相关

4、能非常高效的缓存索引和数据

5、表与主键以簇的方式存储

6、支持分区、表空间，类似oracle数据库

7、支持外键约束，5.5前不支持全文索引，5.5后支持全文索引

8、对硬件资源要求还是比较高的场合

9、行级锁定，但是全表扫描操作仍然会是表级锁定

10、InnoDB 中不保存表的行数，如 select count() from table;（统计表中所有字段的行数） 时，InnoDB 需要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。需要注意的是，当 count()语句包含 where 条件时 MyISAM 也需要扫描整个表

11、对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字段一起建立组合索引

12、清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表

InnoDB适用生产场景分析

1、业务需要事务的支持

2、行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成

3、业务数据更新较为频繁的场景——论坛，微博等

4、业务数据一致性要求较高——银行业务

5、硬件设备内存较大，利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘I0的压力

3 企业选择存储引擎的依据

①需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景

② 支持的字段和数据类型

1 所有引擎都支持通用的数据类型

2 但不是所有的引擎都支持其它的字段类型，如二进制对象

③锁定类型:不同的存储引擎支持不同级别的锁定

1表锁定: MyISAM 支持

2 行锁定: InnoDB 支持

3 死锁
（1）MyISAM:表级锁定 innodb :行级锁定
当两个请求分别访问/读取2行记录，同时又需要读取对方的记录数据，因为（行锁的限制)而造成了阻寨的现象

（2）如何解决死锁

业务场景如果并发两大，什么并发量大，读写的并发量大，那我们建议使用innoDB
如果单独的写入或是插入单独的查询，那我们建议使用没有INNODB
表级锁：开销小，加锁快:不会出现死锁:锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢:会出现死锁;锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景。

锁定类型：不同存储引擎支持不同级别的锁定

4 索引的支持

1建立索引在搜索和恢复数据库中的数据时能显著提高性能

2 不同的存储弓|擎提供不同的制作索引的技术

3 有些存储引擎根本不支持索引

5 事务处理的支持

提高在向表中更新和插入信息期间的可靠性

可根据企业业务是否要支持事务选择存储引擎

二 存储引擎的相关命令

1 查看系统支持的存储引擎

show engines;

字段涵义： 

2 查看表使用的存储引擎

方法一
show table status from 库名 where name='表名'\G；

例：show table status from SCHOOL where name='info'\G;

方法二
use 库名;
show create table 表名;

例：use SCHOOL;
show create table info;

3 修改存储引擎

方法一：通过 alter table 修改

use 库名;
alter table 表名 engine=MyISAM;

例：use SCHOOL;
alter table info engine=myisam;
show create table info;

方法二：通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务

mysql> show tables;
+--------------------+
| Tables_in_kgc_ky35 |
+--------------------+
| class              |
+--------------------+
1 row in set (0.00 sec)

mysql> create table class35 (id int,name varchar(10) not null,hobby char (60) );
Query OK, 0 rows affected (0.01 sec)

mysql>  show create table class35;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                              |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| class35 | CREATE TABLE "class35" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(10) NOT NULL,
  "hobby" char(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: kgc_ky35

+--------------------+
| Tables_in_kgc_ky35 |
+--------------------+
| class              |
| class35            |
+--------------------+
2 rows in set (0.05 sec)

quit
vim /etc/my.cnf
[mysqld]
default-storage-engine=INNODB

另一个端口看一下

再变化

systemctl restart mysqld.service
修改完记得重启mysql服务
#注意：此方法只对修改了配置文件并重启mysql服务后新创建的表有效，已经存在的表不会有变更。

再举例：

方法三 通过 create table 创建表时指定存储引擎

use 库名;
create table 表名(字段1 数据类型,...) engine=MyISAM;

例：mysql -u root -p
use SCHOOL;
create table hellolic (name varchar(10),age char(4)) engine=myisam;

什么是死锁？以及怎么解决/预防？

死锁是指两个或多个事务在同一个资源上相互占用，并请求对方的锁定资源，从而导致恶性循环的现象，业务阻塞。

设置事务超时等待时间，innodb_lock_wait_timeout

开启死锁检测，innodb_deadlock_detect

使用更合理的业务逻辑，尽量按照顺序去处理表的操作，避免同时锁定两个资源；

保持事务剪短，减少对资源的占用时间和占用范围；

为表添加合理的索引，减少表锁发生的概率；

首先如果是由索引的情况下修改或删除数据使用where限定条件字段为索引字段，只会进行行级锁定。如果是where指定的条件的字段并没有索引，那么就会进行表级锁定。

如果业务允许的情况下，减低隔离级别，比如采用RC提交读隔离级别；可以避免掉很多因为间隙锁造成的死锁。
尽量避免脏读所以不使用RU，如果业务允许的恶化，可以降低隔离级别为RC，比如MySQL的默认隔离级别为RR,可以将其改为RC，支持较少的锁定

建议开发人员尽量采用乐观锁机制；

InnoDB如何避免全表扫描?

首先要知道全表扫描时非常耗时的，占用cpu资源，所以需要避免全表扫描

怎么看是否会不会全表扫描？

不是创建了索引就一定会在查询的时候用到索引的

使用explain加上select查询语句进行分析，如果是type字段的值为NULL那就说明没有用索引，会全表扫描。

如何避免全表扫描？

1、需要避免where的条件有null的判断；

2、避免where的条件是!=和<> 这都是在判断是否空；

3、避免where后面的条件中的字段存在运算，比如

select * from 表名 where age/2=15;
这种就是字段存在运算 
 
需要修改为
select * from 表名 where age=15 * 2;

4、避免where后面的条件中 字段使用了函数，这一点跟第3点很像

5、避免在使用like模糊查询的时候，引号内的关键字为%开头