vlambda博客
学习文章列表

存储引擎也是 MySQL 的组建，它是一种软件，它所能做的和支持的功能主要有

• 并发
• 支持事务
• 完整性约束
• 物理存储
• 支持索引
• 性能帮助

MySQL 默认支持多种存储引擎，来适用不同数据库应用，用户可以根据需要选择合适的存储引擎，下面是 MySQL 支持的存储引擎

• MyISAM
• InnoDB
• BDB
• MEMORY
• MERGE
• EXAMPLE
• NDB Cluster
• ARCHIVE
• CSV
• BLACKHOLE
• FEDERATED

默认情况下，如果创建表不指定存储引擎，会使用默认的存储引擎，如果要修改默认的存储引擎，那么就可以在参数文件中设置  default-table-type ，能够查看当前的存储引擎

   
     
     
   

    
      
      
    show variables like 'table_type';

   
     
     
   

奇怪，为什么没有了呢？网上求证一下，在 5.5.3 取消了这个参数

可以通过下面两种方法查询当前数据库支持的存储引擎

   
     
     
   

    
      
      
    show engines \g

   
     
     
   

在创建新表的时候，可以通过增加  ENGINE  关键字设置新建表的存储引擎。

   
     
     
   

    
      
      
    create table cxuan002(id int(10),name varchar(20)) engine = MyISAM;

   
     
     
   

上图我们指定了  MyISAM  的存储引擎。

如果你不知道表的存储引擎怎么办？你可以通过  show create table  来查看

如果不指定存储引擎的话，从MySQL 5.1 版本之后，MySQL 的默认内置存储引擎已经是 InnoDB了。建一张表看一下

如上图所示，我们没有指定默认的存储引擎，下面查看一下表

   
     
     
   

    
      
      
    alter table cxuan003 engine = myisam;

   
     
     
   

存储引擎特性

下面会介绍几个常用的存储引擎以及它的基本特性，这些存储引擎是 **MyISAM、InnoDB、MEMORY 和 MERGE **

MyISAM

在 5.1 版本之前，MyISAM 是 MySQL 的默认存储引擎，MyISAM 并发性比较差，使用的场景比较少，主要特点是

• 不支持 事务 操作，ACID 的特性也就不存在了，这一设计是为了性能和效率考虑的。
• 不支持 外键 操作，如果强行增加外键，MySQL 不会报错，只不过外键不起作用。
• MyISAM 默认的锁粒度是 表级锁 ，所以并发性能比较差，加锁比较快，锁冲突比较少，不太容易发生死锁的情况。
• MyISAM 会在磁盘上存储三个文件，文件名和表名相同，扩展名分别是  .frm(存储表定义) 、 .MYD(MYData,存储数据) 、 MYI(MyIndex,存储索引) 。这里需要特别注意的是 MyISAM 只缓存 索引文件 ，并不缓存数据文件。
• MyISAM 支持的索引类型有  全局索引(Full-Text) 、 B-Tree 索引 、 R-Tree 索引

  Full-Text 索引：它的出现是为了解决针对文本的模糊查询效率较低的问题。

  B-Tree 索引：所有的索引节点都按照平衡树的数据结构来存储，所有的索引数据节点都在叶节点

  R-Tree索引：它的存储方式和 B-Tree 索引有一些区别，主要设计用于存储空间和多维数据的字段做索引,目前的 MySQL 版本仅支持 geometry 类型的字段作索引，相对于 BTREE，RTREE 的优势在于范围查找。
• 数据库所在主机如果宕机，MyISAM 的数据文件容易损坏，而且难以恢复。
• 增删改查性能方面：SELECT 性能较高，适用于查询较多的情况

InnoDB

自从 MySQL 5.1 之后，默认的存储引擎变成了 InnoDB 存储引擎，相对于 MyISAM，InnoDB 存储引擎有了较大的改变，它的主要特点是

• 支持事务操作，具有事务 ACID 隔离特性，默认的隔离级别是 可重复读(repetable-read) 、通过 MVCC（并发版本控制） 来实现的。能够解决 脏读 和 不可重复读 的问题。
• InnoDB 支持外键操作。
• InnoDB 默认的锁粒度 行级锁 ，并发性能比较好，会发生死锁的情况。
• 和 MyISAM 一样的是，InnoDB 存储引擎也有  .frm文件存储表结构  定义，但是不同的是，InnoDB 的表数据与索引数据是存储在一起的，都位于 B+ 数的叶子节点上，而 MyISAM 的表数据和索引数据是分开的。
• InnoDB 有安全的日志文件，这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性。
• InnoDB 和 MyISAM 支持的索引类型相同，但具体实现因为文件结构的不同有很大差异。
• 增删改查性能方面，果执行大量的增删改操作，推荐使用 InnoDB 存储引擎，它在删除操作时是对行删除，不会重建表。

MEMORY

MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表实际只对应一个磁盘文件，格式是  .frm 。MEMORY 类型的表访问速度很快，因为其数据是存放在内存中。默认使用  HASH 索引 。

MERGE

MERGE 存储引擎是一组 MyISAM 表的组合，MERGE 表本身没有数据，对 MERGE 类型的表进行查询、更新、删除的操作，实际上是对内部的 MyISAM 表进行的。MERGE 表在磁盘上保留两个文件，一个是  .frm  文件存储表定义、一个是  .MRG  文件存储 MERGE 表的组成等。

选择合适的存储引擎

在实际开发过程中，我们往往会根据应用特点选择合适的存储引擎。

• MyISAM：如果应用程序通常以检索为主，只有少量的插入、更新和删除操作，并且对事物的完整性、并发程度不是很高的话，通常建议选择 MyISAM 存储引擎。
• InnoDB：如果使用到外键、需要并发程度较高，数据一致性要求较高，那么通常选择 InnoDB 引擎，一般互联网大厂对并发和数据完整性要求较高，所以一般都使用 InnoDB 存储引擎。
• MEMORY：MEMORY 存储引擎将所有数据保存在内存中，在需要快速定位下能够提供及其迅速的访问。MEMORY 通常用于更新不太频繁的小表，用于快速访问取得结果。
• MERGE：MERGE 的内部是使用 MyISAM 表，MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上， 可以有效地改善 MERGE 表的访问效率。

选择合适的数据类型

我们会经常遇见的一个问题就是，在建表时如何选择合适的数据类型，通常选择合适的数据类型能够提高性能、减少不必要的麻烦，下面我们就来一起探讨一下，如何选择合适的数据类型。

CHAR 和 VARCHAR 的选择

char 和 varchar 是我们经常要用到的两个存储字符串的数据类型，char 一般存储定长的字符串，它属于固定长度的字符类型，比如下面

可以看到，不管你的值写的是什么，一旦指定了 char 字符的长度，如果你的字符串长度不够指定字符的长度的话，那么就用空格来填补，如果超过字符串长度的话，只存储指定字符长度的字符。

这里注意一点：如果 MySQL 使用了非  严格模式 的话，上面表格最后一行是可以存储的。如果 MySQL 使用了  严格模式  的话，那么表格上面最后一行存储会报错。

如果使用了 varchar 字符类型，我们来看一下例子

可以看到，如果使用 varchar 的话，那么存储的字节将根据实际的值进行存储。你可能会疑惑为什么 varchar 的长度是 5 ，但是却需要存储 3 个字节或者 6 个字节，这是因为使用 varchar 数据类型进行存储时，默认会在最后增加一个字符串长度，占用1个字节（如果列声明的长度超过255，则使用两个字节）。varchar 不会填充空余的字符串。

一般使用 char 来存储定长的字符串，比如「身份证号、手机号、邮箱等」；使用 varchar 来存储不定长的字符串。由于 char 长度是固定的，所以它的处理速度要比 VARCHAR 快很多，但是缺点是浪费存储空间，但是随着 MySQL 版本的不断演进，varchar 数据类型的性能也在不断改进和提高，所以在许多应用中，VARCHAR 类型更多的被使用。

在 MySQL 中，不同的存储引擎对 CHAR 和 VARCHAR 的使用原则也有不同

• MyISAM：建议使用固定长度的数据列替代可变长度的数据列，也就是 CHAR
• MEMORY：使用固定长度进行处理、CHAR 和 VARCHAR 都会被当作 CHAR 处理
• InnoDB：建议使用 VARCHAR 类型

TEXT 与 BLOB

一般在保存较少的文本的时候，我们会选择 CHAR 和 VARCHAR，在保存大数据量的文本时，我们往往选择 TEXT 和 BLOB；TEXT 和 BLOB 的主要差别是 BLOB 能够保存 二进制数据 ；而 TEXT 只能保存 字符数据 ，TEXT 往下细分有

• TEXT
• MEDIUMTEXT
• LONGTEXT

BLOB 往下细分有

• BLOB
• MEDIUMBLOB
• LONGBLOB

三种，它们最主要的区别就是存储文本长度不同和存储字节不同，用户应该根据实际情况选择满足需求的最小存储类型，下面主要对 BLOB 和 TEXT 存在一些问题进行介绍

TEXT 和 BLOB 在删除数据后会存在一些性能上的问题，为了提高性能，建议使用  OPTIMIZE TABLE  功能对表进行碎片整理。

也可以使用合成索引来提高文本字段（BLOB 和 TEXT）的查询性能。合成索引就是根据大文本（BLOB 和 TEXT）字段的内容建立一个散列值，把这个值存在对应列中，这样就能够根据散列值查找到对应的数据行。一般使用散列算法比如 md5() 和 SHA1() ，如果散列算法生成的字符串带有尾部空格，就不要把它们存在 CHAR 和 VARCHAR 中，下面我们就来看一下这种使用方式

首先创建一张表，表中记录 blob 字段和 hash 值

日期类型选择

• TIMESTAMP 和时区相关，更能反映当前时间，如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。
• DATE 用于表示年月日，如果实际应用值需要保存年月日的话就可以使用 DATE。
• TIME 用于表示时分秒，如果实际应用值需要保存时分秒的话就可以使用 TIME。
• YEAR 用于表示年份，YEAR 有 2 位（最好使用4位）和 4 位格式的年。默认是4位。如果实际应用只保存年份，那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间，还能提高表的操作效率。

  
    
    
  

   
     
     
   select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;

  
    
    
  

索引概述

• 全局索引(FULLTEXT) ：全局索引，目前只有 MyISAM 引擎支持全局索引，它的出现是为了解决针对文本的模糊查询效率较低的问题，并且只限于 CHAR、VARCHAR 和 TEXT 列。
• 哈希索引(HASH) ：哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构，很适合作为索引。HASH 索引具有一次定位的好处，不需要像树那样逐个节点查找，但是这种查找适合应用于查找单个键的情况，对于范围查找，HASH 索引的性能就会很低。默认情况下，MEMORY 存储引擎使用 HASH 索引，但也支持 BTREE 索引。
• B-Tree 索引 ：B 就是 Balance 的意思，BTree 是一种平衡树，它有很多变种，最常见的就是 B+ Tree，它被 MySQL 广泛使用。
• R-Tree 索引 ：R-Tree 在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对于 B-Tree 来说，R-Tree 的优势在于范围查找。

索引设计原则

• 选择 索引位置 ，选择索引最合适的位置是出现在  where  语句中的列，而不是  select  关键字后的选择列表中的列。
• 选择使用 唯一索引 ，顾名思义，唯一索引的值是唯一的，可以更快速的确定某条记录，例如学生的学号就适合使用唯一性索引，而学生的性别则不适合使用，因为不管搜索哪个值，都差不多有一半的行。
• 为经常使用的字段建立索引，如果某个字段经常用作查询条件，那么这个字段的查询速度在极大程度上影响整个表的查询速度，因此为这样的字段建立索引，可以提高整个表的查询速度。
• 不要过度索引，限制索引数目，索引的数目不是越多越好，每个索引都会占据磁盘空间，索引越多，需要的磁盘空间就越大。
• 尽量使用 前缀索引 ，如果索引的值很长，那么查询速度会受到影响，这个时候应该使用前缀索引，对列的某几个字符进行索引，可以提高检索效率。
• 利用最左前缀，在创建一个 n 列的索引时，实际上是创建了 MySQL 可利用的 n 个索引。多列索引可以起到几个索引的作用，利用索引最左边的列来匹配行，这样的列称为最左前缀。
• 对于使用 InnoDB 存储引擎的表来说，记录会按照一定的顺序保存。如果有明确的主键定义，那么会按照主键的顺序进行保存；如果没有主键，但是有唯一索引，那么就按照唯一索引的顺序进行保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序进行保存。一般来说，使用主键的顺序是最快的
• 删除不再使用或者很少使用的索引

什么是视图

• 使用视图可以简化操作：使用视图我们不用关注表结构的定义，我们可以把经常使用的数据集合定义成视图，这样能够简化操作。
• 安全性：用户对视图不可以随意的更改和删除，可以保证数据的安全性。
• 数据独立性：一旦视图的结构 确定了， 可以屏蔽表结构变化对用户的影响， 数据库表增加列对视图没有影响；具有一定的独立性

对视图的操作

创建或修改视图

  
    
    
  

   
     
     
   create table product(id int(11),name varchar(20),price float(10,2));

  
    
    
  

  
    
    
  

   
     
     
   insert into product values(1, "apple","3.5"),(2,"banana","4.2"),(3,"melon","1.2");

  
    
    
  

  
    
    
  

   
     
     
   create view v1 as select * from product;

  
    
    
  

  
    
    
  

   
     
     
   show tables;

  
    
    
  

  
    
    
  

   
     
     
   drop view v1;

  
    
    
  

  
    
    
  

   
     
     
   describe v1;

  
    
    
  

  
    
    
  

   
     
     
   update v1 set name = "grape" where id = 1;

  
    
    
  

• 使用存储过程具有可封装性，能够隐藏复杂的 SQL 逻辑。
• 存储过程可以接收参数，并返回结果
• 存储过程性能非常高，一般用于批量执行语句

• 存储过程编写复杂
• 存储过程对数据库的依赖性比较强，可移植性比较差

存储过程使用

存储过程创建

  
    
    
  

   
     
     
   delimiter $$

  
    
    
  

  
    
    
  

   
     
     
   delimiter ;

  
    
    
  

  
    
    
  

   
     
     
   mysql> delimiter $$
mysql> create procedure sp_product()
 -> begin
 -> select * from product;
 -> end $$

  
    
    
  

  
    
    
  

   
     
     
   select * from product where id = 2;

  
    
    
  

存储过程删除

  
    
    
  

   
     
     
   drop procedure sp_product ;

  
    
    
  

存储过程查看

  
    
    
  

   
     
     
   show create procedure proc_name;
  
    
    
  

变量的使用

用户变量

  
    
    
  

   
     
     
   set @myId = "cxuan";

  
    
    
  

  
    
    
  

   
     
     
   exit

  
    
    
  

局部变量

会话变量

  
    
    
  

   
     
     
   show session variables;

  
    
    
  

  
    
    
  

   
     
     
   set session auto_increment_increment=1;

或者使用

set @@session.auto_increment_increment=2;

  
    
    
  

全局变量

  
    
    
  

   
     
     
   show global variables;

  
    
    
  

  
    
    
  

   
     
     
   set global sql_warnings=ON; -- global不能省略

/** 或者 **/

set @@global.sql_warnings=OFF;

  
    
    
  

MySQL 流程语句介绍

• IF

  
    
    
  

   
     
     
   IF ... THEN ...

  
    
    
  

• CASE

  
    
    
  

   
     
     
   CASE ...
 WHEN ... THEN...
 ...
END CASE

  
    
    
  

• LOOP

  
    
    
  

   
     
     
   label:LOOP
 ...
END LOOP label;

  
    
    
  

• LEAVE

• ITERATE

• REPEAT

  
    
    
  

   
     
     
   REPEAT
 ...
 UNTIL
END REPEAT;

  
    
    
  

• WHILE

创建触发器

  
    
    
  

   
     
     
   create trigger triggername triggertime triggerevent on tbname for each row triggerstmt

  
    
    
  

• triggername ：这个指的就是触发器的名字
• triggertime ：这个指的就是触发器触发时机，是  BEFORE  还是  AFTER
• triggerevent : 这个指的就是触发器触发事件，一共有三种事件：「INSERT、UPDATE 或者 DELETE」。
• tbname ：这个参数指的是触发器创建的表名，在哪个表上创建
• triggerstmt : 触发器的程序体，也就是 SQL 语句

  
    
    
  

   
     
     
   create table product_info(p_info varchar(20));

  
    
    
  

  
    
    
  

   
     
     
   insert into product values(4,"pineapple",15.3);

  
    
    
  

删除触发器

  
    
    
  

   
     
     
   drop trigger tg_pinfo;

  
    
    
  

查看触发器

触发器的作用

• 在添加一条数据前，检查数据是否合理，例如检查邮件格式是否正确
• 删除数据后，相当于数据备份的作用
• 可以记录数据库的操作日志，也可以作为表的执行轨迹

1. 触发程序不能调用将数据返回客户端的存储程序。也不能使用 CALL 语句的动态 SQL 语句。
2. 不能在触发器中开始和结束语句，例如 START TRANSACTION