MySQL 存储引擎
存储引擎概述
数据库最核心的一点就是用来存储数据,数据存储就避免不了和磁盘打交道。那么数据以哪种方式进行存储,如何存储是存储的关键所在。所以存储引擎就相当于是数据存储的发动机,来驱动数据在磁盘层面进行存储。
MySQL 的架构可以按照三层模式来理解。
并发 支持事务 完整性约束 物理存储 支持索引 性能帮助
MyISAM InnoDB BDB MEMORY MERGE EXAMPLE NDB Cluster ARCHIVE CSV BLACKHOLE FEDERATED
default-table-type
,能够查看当前的存储引擎ENGINE
关键字设置新建表的存储引擎。MyISAM
的存储引擎。show create table
来查看InnoDB
。show create table
查看一下表的 sql 就知道存储引擎特性
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
支持事务操作,具有事务 ACID 隔离特性,默认的隔离级别是 可重复读(repetable-read)
、通过MVCC(并发版本控制)
来实现的。能够解决脏读
和不可重复读
的问题。InnoDB 支持外键操作。 InnoDB 默认的锁粒度 行级锁
,并发性能比较好,会发生死锁的情况。和 MyISAM 一样的是,InnoDB 存储引擎也有 .frm文件存储表结构
定义,但是不同的是,InnoDB 的表数据与索引数据是存储在一起的,都位于 B+ 数的叶子节点上,而 MyISAM 的表数据和索引数据是分开的。InnoDB 有安全的日志文件,这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题,保证数据的一致性。 InnoDB 和 MyISAM 支持的索引类型相同,但具体实现因为文件结构的不同有很大差异。 增删改查性能方面,果执行大量的增删改操作,推荐使用 InnoDB 存储引擎,它在删除操作时是对行删除,不会重建表。
MEMORY
.frm
。MEMORY 类型的表访问速度很快,因为其数据是存放在内存中。默认使用 HASH 索引
。MERGE
.frm
文件存储表定义、一个是 .MRG
文件存储 MERGE 表的组成等。选择合适的存储引擎
MyISAM:如果应用程序通常以检索为主,只有少量的插入、更新和删除操作,并且对事物的完整性、并发程度不是很高的话,通常建议选择 MyISAM 存储引擎。 InnoDB:如果使用到外键、需要并发程度较高,数据一致性要求较高,那么通常选择 InnoDB 引擎,一般互联网大厂对并发和数据完整性要求较高,所以一般都使用 InnoDB 存储引擎。 MEMORY:MEMORY 存储引擎将所有数据保存在内存中,在需要快速定位下能够提供及其迅速的访问。MEMORY 通常用于更新不太频繁的小表,用于快速访问取得结果。 MERGE:MERGE 的内部是使用 MyISAM 表,MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制,并且通过将不同的表分布在多个磁盘上, 可以有效地改善 MERGE 表的访问效率。
选择合适的数据类型
CHAR 和 VARCHAR 的选择
严格模式
的话,上面表格最后一行是可以存储的。如果 MySQL 使用了 严格模式
的话,那么表格上面最后一行存储会报错。MyISAM:建议使用固定长度的数据列替代可变长度的数据列,也就是 CHAR MEMORY:使用固定长度进行处理、CHAR 和 VARCHAR 都会被当作 CHAR 处理 InnoDB:建议使用 VARCHAR 类型
TEXT 与 BLOB
二进制数据
;而 TEXT 只能保存字符数据
,TEXT 往下细分有TEXT MEDIUMTEXT LONGTEXT
BLOB MEDIUMBLOB LONGBLOB
OPTIMIZE TABLE
功能对表进行碎片整理。非必要的时候不要检索 BLOB 和 TEXT 索引 把 BLOB 或 TEXT 列分离到单独的表中。
浮点数和定点数的选择
float
和 double
,定点数指的是 decimal
,定点数能够更加精确的保存和显示数据。下面通过一个示例讲解一下浮点数精确性问题日期类型选择
TIMESTAMP 和时区相关,更能反映当前时间,如果记录的日期需要让不同时区的人使用,最好使用 TIMESTAMP。 DATE 用于表示年月日,如果实际应用值需要保存年月日的话就可以使用 DATE。 TIME 用于表示时分秒,如果实际应用值需要保存时分秒的话就可以使用 TIME。 YEAR 用于表示年份,YEAR 有 2 位(最好使用4位)和 4 位格式的年。默认是4位。如果实际应用只保存年份,那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间,还能提高表的操作效率。
ASCII(American Standard Code for Information Interchange)
。自从 ASCII 编码后,每个国家、国际组织都研究了一套自己的字符集,比如 ISO-8859-1
、GBK
等。国际标准化组织(ISO)
指定了统一的字符标准 – Unicode 编码,它容纳了几乎所有的字符编码。下面是一些常见的字符编码show character set;
来查看所有可用的字符集information_schema.character_set
来查看字符集和校对规则。索引概述
SELECT
查询性能的最佳途径。MyISAM 和 InnoDB 都是使用 BTREE
作为索引,MySQL 5 不支持函数索引
,但是支持 前缀索引
。全局索引(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 的优势在于范围查找。
explain
进行分析,可以看到 cxuan004 使用索引的情况索引设计原则
选择 索引位置
,选择索引最合适的位置是出现在where
语句中的列,而不是select
关键字后的选择列表中的列。选择使用 唯一索引
,顾名思义,唯一索引的值是唯一的,可以更快速的确定某条记录,例如学生的学号就适合使用唯一性索引,而学生的性别则不适合使用,因为不管搜索哪个值,都差不多有一半的行。为经常使用的字段建立索引,如果某个字段经常用作查询条件,那么这个字段的查询速度在极大程度上影响整个表的查询速度,因此为这样的字段建立索引,可以提高整个表的查询速度。 不要过度索引,限制索引数目,索引的数目不是越多越好,每个索引都会占据磁盘空间,索引越多,需要的磁盘空间就越大。 尽量使用 前缀索引
,如果索引的值很长,那么查询速度会受到影响,这个时候应该使用前缀索引,对列的某几个字符进行索引,可以提高检索效率。利用最左前缀,在创建一个 n 列的索引时,实际上是创建了 MySQL 可利用的 n 个索引。多列索引可以起到几个索引的作用,利用索引最左边的列来匹配行,这样的列称为最左前缀。 对于使用 InnoDB 存储引擎的表来说,记录会按照一定的顺序保存。如果有明确的主键定义,那么会按照主键的顺序进行保存;如果没有主键,但是有唯一索引,那么就按照唯一索引的顺序进行保存。如果既没有主键又没有唯一索引,那么表中会自动生成一个内部列,按照这个列的顺序进行保存。一般来说,使用主键的顺序是最快的 删除不再使用或者很少使用的索引
什么是视图
view
,它是一种虚拟存在的表。视图对于用户来说是透明的,它并不在数据库中实际存在,视图是使用数据库行和列动态组成的表,那么视图相对于数据库表来说,优势体现在哪里?使用视图可以简化操作:使用视图我们不用关注表结构的定义,我们可以把经常使用的数据集合定义成视图,这样能够简化操作。 安全性:用户对视图不可以随意的更改和删除,可以保证数据的安全性。 数据独立性:一旦视图的结构 确定了, 可以屏蔽表结构变化对用户的影响, 数据库表增加列对视图没有影响;具有一定的独立性
对视图的操作
创建或修改视图
create view
来创建视图product
表,有三个字段,id,name,price,下面是建表语句使用存储过程具有可封装性,能够隐藏复杂的 SQL 逻辑。 存储过程可以接收参数,并返回结果 存储过程性能非常高,一般用于批量执行语句
存储过程编写复杂 存储过程对数据库的依赖性比较强,可移植性比较差
存储过程使用
存储过程创建
delimiter
的用法,delimiter 用于自定义结束符,什么意思呢,如果你使用;
是不能使 SQL 语句执行的,不信?我们可以看下;
但是我们却没有看到执行结果。下面我们使用;
替换为 $$
,下面是一个存储过程的创建语句mysql> create procedure sp_product()
-> begin
-> select * from product;
-> end $$
call
方法来调用这个存储过程call
调用这个存储过程存储过程删除
()
。存储过程查看
变量的使用
系统变量
和用户变量
,这是一种粗略的分法。但是根据实际应用又被细化为四种类型,即局部变量、用户变量、会话变量和全局变量。用户变量
会话变量
实现的,可以暂存,用户变量与连接有关,也就是说一个客户端定义的变量不能被其他客户端使用看到。当客户端退出时,链接会自动释放。我们可以使用 set
语句设置一个变量select
查询条件可以查询出我们刚刚设置的用户变量select
条件查询@myId
了。局部变量
declare
来声明。会话变量
或者使用
set @@session.auto_increment_increment=2;
全局变量
/** 或者 **/
set @@global.sql_warnings=OFF;
MySQL 流程语句介绍
IF
CASE
WHEN … THEN…
…
END CASE
LOOP
…
END LOOP label;
...
中不写 SQL 语句的话,那么就是一个简单的死循环语句LEAVE
ITERATE
REPEAT
…
UNTIL
END REPEAT;
WHILE
触发器
,触发器一般作用在表上,在满足定义条件时触发,并执行触发器中定义的语句集合,下面我们就来一起认识一下触发器。创建触发器
triggername
:这个指的就是触发器的名字triggertime
:这个指的就是触发器触发时机,是BEFORE
还是AFTER
triggerevent
: 这个指的就是触发器触发事件,一共有三种事件:「INSERT、UPDATE 或者 DELETE」。tbname
:这个参数指的是触发器创建的表名,在哪个表上创建triggerstmt
: 触发器的程序体,也就是 SQL 语句
for each now
表示任何一条记录上的操作都会触发触发器。trigger
tg_pinfo
的时候插入了的这条数据。删除触发器
drop
进行删除,具体删除语法如下查看触发器
show triggers
命令查看触发器的状态、语法等信息。information_schema.triggers
表,这个可以查询指定触发器的指定信息,操作起来方便很多触发器的作用
在添加一条数据前,检查数据是否合理,例如检查邮件格式是否正确 删除数据后,相当于数据备份的作用 可以记录数据库的操作日志,也可以作为表的执行轨迹
触发程序不能调用将数据返回客户端的存储程序。也不能使用 CALL 语句的动态 SQL 语句。 不能在触发器中开始和结束语句,例如 START TRANSACTION
声明:本站所有文章,如无特殊说明或标注,均为本站原创发布。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。