MySQL知识体系——存储引擎

Wesley13
• 阅读 394

你需要get的小点

  • 存储引擎是MySQL有别于其他数据库管理系统的最大特色。

  • MySQL中的数据用各种不同的“技术”存储在文件(或者内存)中。每一种“技术”都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作**表类型:***储引擎的使用级别是数据表)。

  • 对于存储引擎的选择,往往是由业务决定的。

  • 全文索引是指对char、varchar和text中的每个词(停用词除外)建立倒排序索引。

    • 停用词(stopword)也叫停止词/字

    • 在全文索引中,如果一个词被认为是太普通或者太没价值,那么它将会被搜索索引和搜索查询忽略

    • 查看停用词 SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

存储引擎(本文简述InnoDB和MyIsam)

  1. MyIsam
  2. InnoDB
  3. Memory
  4. Blackhole
  5. CSV
  6. Performance_Schema
  7. Archive
  8. Federated 
  9. Mrg_Myisam

InnoDB(默认)

特点

  1. 支持事务(提供了对数据库ACID事务的支持)
  2. 行级锁定(锁的粒度更小,*如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表)
  3. 支持外键约束
  4. 实现了SQL标准的四种隔离级别
  5. 索引和数据在一起,数据文件本身就是主键索引文件,这样的索引被称为“聚簇索引”
  6. 不支持FULLTEXT类型的索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好
  7. MySQL运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引
  8. 不保存表的行数(当SELECT COUNT(*) FROM TABLE时需要扫描全表)

最佳实践(以下内容来自官方https://dev.mysql.com/doc/refman/8.0/en/innodb-best-practices.html)

  • 指定最频繁查询的字段为每个表的主键,如果没有明显的主键,则指定自动增量值。

这里涉及到InnoDB的索引知识,聚集索引__(clustered index)和辅助索引(secondary index)(具体详见“MySQL知识体系——索引”一文)。一般来说,聚集索引就是按照表中主键的顺序构建一颗 B+ 树,并在叶节点中存放表中的行记录数据。

  • 对于具有关联关系的表使用连接。要获得快速连接性能,请在连接列上定义 外键,并在每个表中声明具有相同数据类型的列。添加外键可确保对引用的列建立索引,从而提高性能。外键还会将删除或更新传播到所有受影响的表,如果父表中不存在相应的ID,则会阻止在子表中插入数据。

  • 关闭自动提交。每秒提交数百次会限制性能(受存储设备写入速度的限制)。

InnoDB的AUTOCOMMIT默认是打开的,即每条SQL语句会默认被封装成一个事务,自动提交,这样会影响速度,所以最好是把多条SQL语句显示放在begin和commit之间,组成一个事务去提交。

  • 把相关的DML 操作(数据操作语言,它是对表记录的操作(增、删、改))组合在一起,然后用START TRANSACTION(开启事务)COMMIT(提交)包裹起来。这样可以避免过于频繁地提交,和继续数小时而不提交的大批量 INSERTUPDATE或者[DELETE](https://www.oschina.net/action/GoToLink?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fdelete.html) 。

  • 不使用LOCK TABLES 语句。InnoDB可以同时处理多个会话,同时读取和写入同一个表,而不会牺牲可靠性或高性能。要获得对一组行的独占写访问权,请使用 SELECT ... FOR UPDATE语法仅锁定要更新的行。

  • 启用 innodb_file_per_table选项或使用通用表空间将表的数据和索引放入单独的文件中,而不是 系统表空间

    innodb_file_per_table 默认启用。

  • 评估您的数据和访问模式是否受益于InnoDB表或页面 压缩功能。您可以在不牺牲读/写功能的情况下压缩InnoDB表。

  • 打开 --sql_mode=NO_ENGINE_SUBSTITUTION 选项,防止 在CREATE TABLE的 ENGINE= 子句中指定的引擎有问题时,使用其他存储引擎创建表。

MyIsam

特点

  1. 不支持事务
  2. 只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁
  3. 不支持外键约束
  4. 索引和数据分离,天生非聚簇索引,最多有一个unique的性质
  5. 支持FULLTEXT类型的索引(但是不支持中文分词,必须由使用者分词后加入空格再写到数据表里,而且少于4个汉字的词会和停用词一样被忽略掉)
  6. 内置了一个计数器(当SELECT COUNT(*) FROM TABLE时直接从计数器中读取)

最佳实践

  1. 设置合适的索引(缓存机制)
  2. 调整读写优先权限、根据业务需求、确保重要操作更有执行权限
  3. 启用延时插入(尽量批量插入、降低写的频率)
  4. 写数据的时候、顺序操作、让insert数据都写入到尾部、减少阻塞
  5. 分解大的时间长的操作、降低单个操作的阻塞时间
  6. 降低并发数(减少数据库的访问、高并发场景的话、可以使用队列机制)
  7. 对于静态更新不频繁的数据库数据、充分利用Query Cache或者Memcached缓存服务、极大可能的提高访问效率
  8. count的时候、只有count(*)会直接返回行数、才是效率最高的;带有where条件的count都需要进行全部数据的访问
  9. 配置主从数据库的时候,可以采用主数据库使用InnoDB,从数据库使用MyISAM,进行读写分离

总结

在MySQL知识体系中,存储引擎独占鳌头,作为第一章节是十分履顺的,而对于Java后端开发人员来说,我觉得以上知识点基本够用了。存储引擎其实包含了MySQL三大知识点:索引、事务和锁。以后随着我们的修行,对于存储引擎的认识也会更加深入。

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Easter79 Easter79
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
mysql存储引擎
什么是存储引擎?MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySql的存储引擎:MyISAM节省数据库空间,当数据读远大于修改时,可以使用该存储引擎InnoDB支持事务,
Wesley13 Wesley13
2年前
mysql常见的存储引擎
_1.什么是存储引擎_   不同的技术以及配套的相关功能在MySQL中被称作存储引擎。存储机制、索引技巧、锁定水平等等。_2.常见操作_  查看默认存储引擎    showvariableslike'%engine%';  查看数据库支持的存储引擎    showengines\\G;    show
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
3个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这