Mysql

Mysql存储引擎

MyISAM

MyISAM:官方提供的存储引擎,基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法。不是事务安全的,不支持外键。

  • 查询效率高,执行大量的查询MyISAM比较适合。
  • 不支持事务,回滚将造成不完全回滚,不具有原子性。
  • 不支持外键
  • 只支持表级锁,不支持行锁定,这样同一个表上的读锁和写锁是互斥的,MyISAM并发读写时如果等待队列中既有读请求又有写请求,默认写请求的优先级高,即使读请求先到,所以MyISAM不适合于有大量查询和修改并存的情况,那样查询进程会长时间阻塞。因为MyISAM是锁表,所以某项读操作比较耗时会使其他写进程饿死。在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。表锁开销小,加锁快,不会出现死锁,粒度大,锁冲突概率高,并发效率低。
  • 支持全文搜索,全文索引是指对char、varchar和text中的每个词(停用词除外)建立倒排序索引。MyISAM的全文索引其实没啥用,因为它不支持中文分词,必须由使用者分词后加入空格再写到数据表里,而且少于4个汉字的词会和停用词一样被忽略掉。

DELETE 表时,先drop表,然后重建表
MyISAM 表被存放在三个文件:frm 文件存放表格定义,数据文件是MYD (MYData) ,索引文件是MYI (MYIndex)引伸。
跨平台很难直接拷贝
MyISAM中可以使AUTO_INCREMENT类型字段建立联合索引
表格可以被压缩。
MyISAM支持GIS数据,InnoDB不支持。GIS数据是指空间数据对象:Point,Line,Polygon,Surface等。
MyISAM保存表的具体行数,在查询不带where时,直接返回保存的行数。所以没有where的count(*)使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器,count(*)时它直接从计数器中读;而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般要伴随where,且where中要包含主键以外的索引列。这里特别强调“主键以外”,是因为InnoDB中primary index是和raw data存放在一起的,而secondary index则是单独存放,然后有个指针指向primary key。所以只是count(*)的话使用secondary index扫描更快,而primary key则主要在扫描索引同时要返回raw data时的作用较大。

InnoDB

InnoDB,支持外键、行锁、事务是他的最大特点。如果有大量的update和insert,建议使用InnoDB,特别是针对多个并发和QPS较高的情况。5.5版本后成为默认存储引擎。

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

update、insert、delete自动加隐式排它锁,或者select for update显示排它锁。不加锁读取

在Mysql5.6版本开始支持FullText类型的索引,5.7.6版本支持了中文索引。
DELETE 表时,是一行一行的删除
InnoDB 把数据和索引存放在表空间里面
跨平台可直接拷贝使用
InnoDB中必须包含AUTO_INCREMENT类型字段的索引
表格很难被压缩
InnoDB的主键范围更大,最大是MyISAM的2倍
innodb属于索引组织表,有两种存储方式,共享表空间存储和多表空间存储,两种存储方式的表结构和myisam一样,以表名开头,扩展名是.frm。如果使用共享表空间,那么所有表的数据文件和索引文件都保存在一个表空间里,一个表空间可以有多个文件,通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字,一般共享表空间的名字叫ibdata1-n。如果使用多表空间,那么每个表都有一个表空间文件用于存储每个表的数据和索引,文件名以表名开头,以.ibd为扩展名。

TokuDB

第三方开发的存储引擎,写速度快,

支持数据压缩存储,适合访问频率低的数据,历史数据归档。不适合大量读取

支持在线添加索引不影响读写操作,

3.对比选择:
MyISAM相对简单,在效率上要优于InnoDB。从效率上来看,如果系统读多写少,对原子性要求低,那么MyISAM最好的选择,且MyISAM恢复速度快,可直接用备份覆盖恢复。如果系统读少写多,尤其是高并发写入,InnoDB就是首选了。

MyISAM与InnoDB两者之间区别与选择,详细总结,性能对比
https://www.cnblogs.com/y-rong/p/8110596.html

数据库锁级别

  • 表级锁:开销小,加锁快,不会出现死锁,粒度大,锁冲突概率高,并发效率低。
  • 行级锁:开锁大,加锁慢,可能出现死锁,粒度小,锁冲突概率低,并发效率高。
  • 共享锁:读锁。其它事务可读不可写。
  • 排它锁:写锁。其它事务不可读不可写。

存储过程与函数

在Mysql服务器中运行,减少网络数据传输。

  • 存储过程实现复杂数据库操作,是独立的sql语句调用。与数据库实现绑定,降低了程序的可移植性。
  • 函数实现针对性强的功能,例如按特殊策略求和。用户定义函数不能修改数据库全局状态。

Mysql8.0新特性

  • 默认字符集修改为了utf-8
  • 增加了隐式索引,隐藏后的索引不会被查询优化器使用,这个特性可用了性能调试。
  • 通用表表达式,复杂查询中的嵌入语句表达更清晰。
  • 窗口函数,与集合函数类似,可实现新的查询方式。不会将多行查询结果合并,不需要group by

Mysql索引

需要额外磁盘空间来保存索引,插入更新删除会增加额外的开销,索引适合读多写少的场景。

索引类型:

  • 唯一索引:值可为NULL。
  • 主键索引:不允许出现空值。
  • 普通索引
  • 联合索引:最左原则即查询条件中的字段必须符合在索引字段中从左开始连续
  • 全文索引:只能在char、varchar、text类型字段上使用。

索引实现:

  • B-Tree:mysql中普遍使用B+Tree索引,但在实现方式上又分为聚簇索引和非聚簇索引。
  • R-Tree
  • Hash
  • FullText:倒排索引,关键字与文档关系。

聚簇索引和非聚簇索引

  • 聚簇索引:指主索引文件和数据文件为同一份文件,聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中B+Tree的叶子节点上的data就是数据本身,key为主键。
  • 非聚簇索引:指B+Tree的叶子节点上的data并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中。非聚簇索引比聚簇索引多了一次读取数据的IO操作,所以查找性能上会差。

utf8mb4

MySQL在5.5.3之后增加了utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。
utf8mb4是utf8的超集,为了节省空间,一般情况下使用utf8就够了。

int(11)

int(n)中,n表示查询显示时的最小宽度,即最小的字符数。若不够最小长度,则前面补0。

自定义函数

转换三点分版本号为纯数字,以便于比较版本大小:

CREATE DEFINER=`root`@`%` FUNCTION `formatVersion3`(version varchar(11)) RETURNS varchar(9) CHARSET latin1
BEGIN
	declare result VARCHAR(9);
	set result = CONCAT(LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 1), '.', -1), 3, '0'),
			    LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1), 3, '0'),
			    LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 3), '.', -1), 3, '0'));
	RETURN result;
END

发表评论