开发者都需要了解的mysql索引四大存储引擎

MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列,索引项可以为null(唯一索引、组合索引等),但是只要列中有null值就不会被包含在索引中。

接着让我们来说一下其中比较常见的四大存储引擎吧。

InnoDB

InnoDBMySQLMySQL5.5以后)的默认存储引擎,支持事务、行级锁和外键,被用来处理大量短期事务。如果使用到外键、需求并发程度较高、数据一致性要求较高的话,那么通常选择InnoDB引擎,这也是互联网大厂使用InnoDB存储引擎的原因。除非有非常特别的原因需要使用其他的存储引擎,否则建议优先考虑InnoDB。但是对比MyISAMInnoDB写的处理效率会差一些,并且会占用更多的磁盘空间以保留数据和索引。

MyISAM

MyISAM提供了大量的特性,包含全文索引、压缩、空间行数等,支持3种不同的存储格式,分别是:静态表、动态表、压缩表。

静态表:表中的字段都是非变长字段,这样每个记录都是固定长度的。优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间通常比动态表多(因为存储时会按照列的宽度定义补足空格),在取数据的时候,默认会把字段后面的空格去掉,如果不注意会把数据本身带的空格也会忽略。

动态表:表中的字段都是变长字段,记录不是固定长度的。这样存储的优点是占用的空间相对较少;缺点是频繁的更新、删除数据容易产生碎片,需要定期执行OPTIMIZE TABLE或者myisamchk -r命令来改善性能,并且出现故障的时候恢复相对比较困难。

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

MyISAM中,数据文件和索引文件可以放置在不同的目录(在创建表的时候通过DATA DIRECTORYINDEX DIRECTORY语句指定文件的绝对路径),平均分配IO,获取更快的访问速度。但是MyISAM不支持事务,不支持外键,也不支持行级锁,支持表级锁,有个缺陷就是崩溃后无法恢复。如果应用程序以检索为主,只有少量的插入、更新和删除操作,并且对事物的完整性、并发程度不是很高的话,通常建议选择MyISAM存储引擎。

Memory

Memory存储引擎使用存在内存中的内容来创建表,所以它的访问速度非常快,并且默认使用HASH索引。但是一旦服务器关闭或者mysqld守护进程崩溃时,所有的Memory数据都会丢失,但表还会继续存在,获得速度的同时也带来了一些缺陷。

它要求存储在Memory数据表里的数据使用的是长度不变的格式,这意味着不能使用BLOBTEXT这样的长度可变的数据类型,VARCHAR是一种长度可变的类型,但因为它在MySQL内部当做长度固定不变的CHAR类型,所以可以使用。

服务器需要足够的内存来维持在同一时间内使用的MEMORY表,当不再使用MEMORY表时,要释放MEMORY表所占用的内存,应该执行DELETE FROMtruncate table或者删除整个表。每个MEMORY表中放置的数据量的大小,受到max_heap_table_size系统变量的约束,这个系统变量的初始值是16M,同时在创建MEMORY表时可以使用MAX_ROWS子句来指定表中的最大行数。它通常用于更新不太频繁的小表。

Merge

Merge存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同,Merge表本身没有数据,对Merge类型的表进行查询、更新、删除的操作,实际上是对内部的MyISAM表进行的。Merge表在磁盘上保留两个文件,一个是.frm文件存储表定义、一个是.MRG文件存储Merge表的组成等。MERGE表的优点在于可以突破对单个MyISAM表大小的限制,并且通过将不同的表分布在多个磁盘上,可以有效地改善MERGE表的访问效率。

我们可以通过show create table 表名 命令来查看表使用的引擎,由以下代码可以看出test表使用的是MyISAM存储引擎。

CREATE TABLE `test` (  `id` int(1) NOT NULL AUTO_INCREMENT,  `name` varchar(8) DEFAULT NULL,  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=46 DEFAULT CHARSET=utf8123456复制代码类型:[javascript]

相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAMMEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎(5.1之后就不直接支持了,因为BDBoracle收购了)采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。

(0)

相关推荐

  • 万字总结 MySQL核心知识,赠送25连环炮

    回复"000"获取大量电子书 前言 大家好,我是老田,之前写过 JVM.并发编程连环炮.然后有很多小伙伴私下找我就我继续把MySQL的连环炮整理出来,但是由于本人比较懒,又加上最近 ...

  • 80%业余都想不明白的乒乓四大难题!

    一.怎样培养抢攻意识? 其实这个抢攻意识,首先要有技术做保障.比如说发球后的侧身抢攻,侧一板失误一板,自然而然就不敢侧了,再有什么意识都没有用了. 所谓的意识更多的是一种下意识,你训练的时候就是这么练 ...

  • 蓝牙的4个冷知识!专业开发者都没关注过的几个重要特性!

    蓝牙耳机的音质,真的那么差? 传统蓝牙耳机,音质确实不怎么样.通常Android手机的蓝牙效果,比不上iPhone的蓝牙效果. 主要是因为编码方式:经典蓝牙,支持SBC和AAC两种编码格式,都是有损压 ...

  • MySQL索引失效底层原理分析,MySQL索引为什么失效,这篇文章全部告诉你

    前言 吊打面试官又来啦,今天我们讲讲MySQL索引为什么会失效,很多文章和培训机构的教程,都只会告诉你,在什么情况下索引会失效. 比如:没遵循最佳左前缀法则.范围查询的右边会失效.like查询用不到索 ...

  • 绝对经典!嵌入式开发者都该了解的10大算法

    算法一:快速排序法 快速排序是由东尼·霍尔所发展的一种排序算法.在平均状况下,排序 n 个项目要Ο(n log n)次比较.在最坏状况下则需要Ο(n2)次比较,但这种状况并不常见.事实上,快速排序通常 ...

  • MySQL索引和SQL调优

    MySQL索引 MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等.为了避免混乱,本文将只关注于BTree ...

  • 小心!装修公司都不会告诉你的四大花招!

    装修的各项事务非常繁忙,对于不懂装修的业主来说,找一家装修公司来负责整个工程真的是帮了很大的忙.但选择装修公司时一定要小心,有的装修公司在与业主进行沟通时,常常会在这四大方面说出一些谎言,业主朋友们千 ...

  • 看这篇就够了!MySQL 索引知识点超全总结

    作者:fanili,腾讯 WXG 后台开发工程师 知其然知其所以然!本文介绍索引的数据结构.查找算法.常见的索引概念和索引失效场景. 什么是索引? 在关系数据库中,索引是一种单独的.物理的对数据库表中 ...

  • MySQL索引是怎么支撑千万级表的快速查找?

    本文作者:何建辉(公众号:org_yijiaoqian) 前言 在 MySQL 官方提到,改善操作性能的最佳方法 SELECT 在查询中测试的一个或多个列上创建索引.索引条目的作用类似于指向表行的指针 ...

  • 再有人问你MySQL索引原理,就把这篇文章甩给他!

    作者 l zyz1992 来源 l Hollis(ID:hollischuang) 索引,可能让好很多人望而生畏,毕竟每次面试时候 MySQL 的索引一定是必问内容,哪怕先撇开面试,就在平常的开发中, ...