精华内容
下载资源
问答
  • mysql数据库引擎
    千次阅读
    2017-02-24 11:10:52

    看你的mysql现在已提供什么存储引擎:
    mysql> show engines;

    看你的mysql当前默认的存储引擎:
    mysql> show variables like '%storage_engine%';

    你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
    mysql> show create table 表名;

     

    MySQL数据库引擎详解

    作为Java程序员,MySQL数据库大家平时应该都没少使用吧,对MySQL数据库的引擎应该也有所了解,这篇文章就让我详细的说说MySQL数据库的Innodb和MyIASM两种引擎以及其索引结构。也来巩固一下自己对这块知识的掌握。

    Innodb引擎

    Innodb引擎提供了对数据库ACID事务的支持,并且实现了SQL标准的四种隔离级别,关于数据库事务与其隔离级别的内容请见数据库事务与其隔 离级别这篇文章。该引擎还提供了行级锁和外键约束,它的设计目标是处理大容量数据库系统,它本身其实就是基于MySQL后台的完整数据库系统,MySQL 运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引,而且它没有保存表的行数,当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时,该引擎当然是首选。由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用Innodb引擎 会提升效率。但是使用行级锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。

    MyIASM引擎

    MyIASM是MySQL默认的引擎,但是它没有提供对数据库事务的支持,也不支持行级锁和外键,因此当INSERT(插入)或UPDATE(更 新)数据时即写操作需要锁定整个表,效率便会低一些。不过和Innodb不同,MyIASM中存储了表的行数,于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持,那么MyIASM也是很好的选 择。

    两种引擎的选择

    大尺寸的数据集趋向于选择InnoDB引擎,因为它支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短,InnoDB可以利用事务日志 进行数据恢复,这会比较快。主键查询在InnoDB引擎下也会相当快,不过需要注意的是如果主键太长也会导致性能问题,关于这个问题我会在下文中讲到。大 批的INSERT语句(在每个INSERT语句中写入多行,批量插入)在MyISAM下会快一些,但是UPDATE语句在InnoDB下则会更快一些,尤 其是在并发量大的时候。

    Index——索引

    索引(Index)是帮助MySQL高效获取数据的数据结构。MyIASM和Innodb都使用了树这种数据结构做为索引,关于树我也曾经写过一篇文章树是一种伟大的数据结构,只是自己的理解,有兴趣的朋友可以去阅读。下面我接着讲这两种引擎使用的索引结构,讲到这里,首先应该谈一下B-Tree和B+Tree。

    B-Tree和B+Tree

    B+Tree是B-Tree的变种,那么我就先讲B-Tree吧,相信大家都知道红黑树,这是我前段时间学《算法》一书时,实现的一颗红黑树,大家 可以参考。其实红黑树类似2,3-查找树,这种树既有2叉结点又有3叉结点。B-Tree也与之类似,它的每个结点做多可以有d个分支(叉),d称为B- Tree的度,如下图所示,它的每个结点可以有4个元素,5个分支,于是它的度为5。B-Tree中的元素是有序的,比如图中元素7左边的指针指向的结点 中的元素都小于7,而元素7和16之间的指针指向的结点中的元素都处于7和16之间,正是满足这样的关系,才能高效的查找:首先从根节点进行二分查找,找 到就返回对应的值,否则就进入相应的区间结点递归的查找,直到找到对应的元素或找到null指针,找到null指针则表示查找失败。这个查找是十分高效 的,其时间复杂度为O(logN)(以d为底,当d很大时,树的高度就很低),因为每次检索最多只需要检索树高h个结点。

    接下来就该讲B+Tree了,它是B-Tree的变种,如下面两张图所示:


    vcHLx/i85LLp0a/Qp8LKoaM8L3A+DQo8aDMgaWQ9"myisam引擎的索引结构">MyISAM引擎的索引结构

    MyISAM引擎的索引结构为B+Tree,其中B+Tree的数据域存储的内容为实际数据的地址,也就是说它的索引和实际的数据是分开的,只不过是用索引指向了实际的数据,这种索引就是所谓的非聚集索引

    Innodb引擎的索引结构

    MyISAM引擎的索引结构同样也是B+Tree,但是Innodb的索引文件本身就是数据文件,即B+Tree的数据域存储的就是实际的数据,这种索引就是聚集索引。这个索引的key就是数据表的主键,因此InnoDB表数据文件本身就是主索引。

    因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

    并且和MyISAM不同,InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址,所以当以辅助索引查找时,会先根据辅助索引找到主 键,再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键,否则会使辅助索引变得过大。建议使用自增的字段作为主键,这样B+Tree的 每一个结点都会被顺序的填满,而不会频繁的分裂调整,会有效的提升插入数据的效率。

     

    MySQL查看最大连接数和修改最大连接数

    1、查看最大连接数
    show variables like '%max_connections%';
    2、修改最大连接数
    set GLOBAL max_connections = 200;

      以下的文章主要是向大家介绍的是MySQL最大连接数的修改,我们大家都知道MySQL最大连接数的默认值是100, 这个数值对于并发连接很多的数据库的应用是远不够用的,当连接请求大于默认连接数后,就会出现无法连接数据库的错误,因此我们需要把它适当调大一些。在使 用MySQL数据库的时候,经常会遇到这么一个问题,就是“Can not connect to MySQL server. Too many connections”-mysql 1040错误,这是因为访问MySQL且还未释放的连接数目已经达到MySQL的上限。通常,mysql的最大连接数默认是100, 最大可以达到16384。

        常用的修改最大连接数的最常用的两种方式如下:

        第一种:命令行查看和修改最大连接数(max_connections)。

      >mysql -uuser -ppassword(命令行登录MySQL)

         mysql>show variables  like  'max_connections' ;(查可以看当前的最大连接数)
         msyql> set  global  max_connections=1000;(设置最大连接数为1000,可以再次查看是否设置成功)
         mysql>exit   

        这种方式有个问题,就是设置的最大连接数只在mysql当前服务进程有效,一旦mysql重启,又会恢复到初始状态。因为mysql启动后的初始化工作是从其配置文件中读取数据的,而这种方式没有对其配置文件做更改。

        第二种:通过修改配置文件来修改mysql最大连接数(max_connections)。

        这种方式说来很简单,只要修改MySQL配置文件my.ini 或 my.cnf的参数max_connections,将其改为max_connections=1000,然后重启MySQL即可。但是有一点最难的就是 my.ini这个文件在哪找。通常有两种可能,一个是在安装目录下,另一种是在数据文件的目录下,安装的时候如果没有人为改变目录的话,一般就在 C:/ProgramData/MySQL往下的目录下,linux系统中一般在/etc目录下。

      其他需注意的:

       在编程时,由于用MySQL语句调用数据库时,在每次之执行语句前,会做一个临时的变量用来打开数据库,所以你在使用MySQL语句的时候,记得在每次调用完MySQL之后就关闭MySQL临时变量。

        另外对于访问量大的,可以考虑直接写到文本中,根据预测的访问量,先定义假若是100个文件文件名,需要的时候,再对所有文本文件中的数据进行分析,再导入数据库。 

     

    如果是root帐号,你能看到所有用户的当前连接。如果是其它普通帐号,只能看到自己占用的连接

    怎么进入mysql命令行呢? mysql的安装目录下面有个bin目录,先用命令行进入该目录,然后用 mysql -uroot -p123456 来登录(注意:用户名和密码不用包含“”)


    命令: show processlist; 
    如果是root帐号,你能看到所有用户的当前连接。如果是其它普通帐号,只能看到自己占用的连接。


    show processlist;只列出前100条,如果想全列出请使用show full processlist; 
    mysql> show processlist;


    命令: show status;
    命令:show status like '%下面变量%'; 
    Aborted_clients                由于客户没有正确关闭连接已经死掉,已经放弃的连接数量。 
    Aborted_connects            尝试已经失败的MySQL服务器的连接的次数。 
    Connections                     试图连接MySQL服务器的次数。 
    Created_tmp_tables          当执行语句时,已经被创造了的隐含临时表的数量。 
    Delayed_insert_threads     正在使用的延迟插入处理器线程的数量。 
    Delayed_writes                用INSERT DELAYED写入的行数。 
    Delayed_errors                用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。 
    Flush_commands            执行FLUSH命令的次数。 
    Handler_delete                 请求从一张表中删除行的次数。 
    Handler_read_first          请求读入表中第一行的次数。 
    Handler_read_key           请求数字基于键读行。 
    Handler_read_next           请求读入基于一个键的一行的次数。 
    Handler_read_rnd           请求读入基于一个固定位置的一行的次数。 
    Handler_update              请求更新表中一行的次数。 
    Handler_write                 请求向表中插入一行的次数。 
    Key_blocks_used            用于关键字缓存的块的数量。 
    Key_read_requests         请求从缓存读入一个键值的次数。 
    Key_reads                      从磁盘物理读入一个键值的次数。 
    Key_write_requests         请求将一个关键字块写入缓存次数。 
    Key_writes                      将一个键值块物理写入磁盘的次数。 
    Max_used_connections    同时使用的连接的最大数目。 
    Not_flushed_key_blocks   在键缓存中已经改变但是还没被清空到磁盘上的键块。 
    Not_flushed_delayed_rows      在INSERT DELAY队列中等待写入的行的数量。 
    Open_tables                  打开表的数量。 
    Open_files                     打开文件的数量。 
    Open_streams               打开流的数量(主要用于日志记载) 
    Opened_tables              已经打开的表的数量。 
    Questions                     发往服务器的查询的数量。 
    Slow_queries                要花超过long_query_time时间的查询数量。 
    Threads_connected       当前打开的连接的数量。 
    Threads_running          不在睡眠的线程数量。 
    Uptime                        服务器工作了多少秒。

    更多相关内容
  • MySQL数据库引擎取决于MySQL在安装的时候是如何被编译的。要添加一个新的引擎,就必须重新编译MYSQL。在缺省情况下,MYSQL支持三个引擎:ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEY(BDB),也常常可以使用。...
  • mysql数据库引擎

    2017-11-23 15:46:18
    mysql数据库引擎分析讲解,里面包括innodb,数据库引擎介绍
  • 几种MySQL数据库引擎优缺点对比

    千次阅读 2020-10-02 15:46:16
    MySQL数据库引擎 数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还...

    MySQL数据库引擎

    数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎

    SHOW ENGINES  # 命令来查看MySQL提供的引擎
    
    SHOW VARIABLES LIKE 'storage_engine'; # 查看数据库默认使用哪个引擎
    

    InnoDB存储引擎

    InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键,InnoDB是默认的MySQL引擎。InnoDB主要特性有:

    1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全(ACID兼容)存储引擎

    InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中,可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来,甚至在同一个查询中也可以混合

    2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的

    3、InnoDB存储引擎完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中,表空间可以包含数个文件(或原始磁盘文件)。这与MyISAM表不同,比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上

    4、InnoDB支持外键完整性约束

    5、存储表中的数据时,每张表的存储都按主键顺序存放,如果没有显示在表定义时指定主键,InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键

    6、InnoDB被用在众多需要高性能的大型数据库站点上

    InnoDB不创建目录,使用InnoDB时,MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件,以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

    MyISAM存储引擎

    MyISAM基于ISAM存储引擎,并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度,但不支持事物。MyISAM主要特性有:

    1、大文件(达到63位文件长度)在支持大文件的文件系统和操作系统上被支持

    2、当把删除和更新及插入操作混合使用的时候,动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块,以及若下一个块被删除,就扩展到下一块自动完成

    3、每个MyISAM表最大索引数是64,这可以通过重新编译来改变。每个索引最大的列数是16

    4、最大的键长度是1000字节,这也可以通过编译来改变,对于键长度超过250字节的情况,一个超过1024字节的键将被用上

    5、BLOB和TEXT列可以被索引

    6、NULL被允许在索引的列中,这个值占每个键的0~1个字节

    7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩

    8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列,当INSERT和UPDATE操作的时候该列被更新,同时AUTO_INCREMENT列将被刷新。所以说,MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快

    9、可以把数据文件和索引文件放在不同目录

    10、每个字符列可以有不同的字符集

    11、有VARCHAR的表可以固定或动态记录长度

    12、VARCHAR和CHAR列可以多达64KB

    使用MyISAM引擎创建数据库,将产生3个文件。文件的名字以表名字开始,扩展名之处文件类型:frm文件存储表定义、数据文件的扩展名为.MYD(MYData)、索引文件的扩展名时.MYI(MYIndex)

    MEMORY存储引擎

    MEMORY存储引擎将表中的数据存储到内存中,未查询和引用其他表数据提供快速访问。MEMORY主要特性有:

    1、MEMORY表的每个表可以有多达32个索引,每个索引16列,以及500字节的最大键长度

    2、MEMORY存储引擎执行HASH和BTREE缩影

    3、可以在一个MEMORY表中有非唯一键值

    4、MEMORY表使用一个固定的记录长度格式

    5、MEMORY不支持BLOB或TEXT列

    6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引

    7、MEMORY表在所由客户端之间共享(就像其他任何非TEMPORARY表)

    8、MEMORY表内存被存储在内存中,内存是MEMORY表和服务器在查询处理时的空闲中,创建的内部表共享

    9、当不再需要MEMORY表的内容时,要释放被MEMORY表使用的内存,应该执行DELETE FROM或TRUNCATE TABLE,或者删除整个表(使用DROP TABLE)

    存储引擎的选择

    不同的存储引擎都有各自的特点,以适应不同的需求,如下表所示:
    在这里插入图片描述

    如果要提供提交、回滚、崩溃恢复能力的事物安全(ACID兼容)能力,并要求实现并发控制,InnoDB是一个好的选择

    如果数据表主要用来插入和查询记录,则MyISAM引擎能提供较高的处理效率

    如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果

    如果只有INSERT和SELECT操作,可以选择Archive,Archive支持高并发的插入操作,但是本身不是事务安全的。Archive非常适合存储归档数据,如记录日志信息可以使用Archive

    使用哪一种引擎需要灵活选择,一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求,使用合适的存储引擎,将会提高整个数据库的性能

    展开全文
  • mysql数据库引擎常用面试总结

    万次阅读 多人点赞 2017-07-27 10:55:50
    一次面试被问到了MySQL的相关问题,一个是对mysql了解多少,引擎有什么,主要区别是什么?第二个被问的更多,给你一个百万级别的表怎么查询优化: 今天整理了一些资料回答第一个问题: ...MySQL数据库引擎详解

    一次面试被问到了MySQL的相关问题,一个是对mysql了解多少,引擎有什么,主要区别是什么?第二个被问的更多,给你一个百万级别的表怎么查询优化:

    今天整理了一些资料回答第一个问题:

    总的思想:

    MyIASM引擎是为了查和增加,效率高。所有功能都围绕这这个

    Innodb引擎功能更强(事务等)效率低一些。


    MySQL数据库引擎详解

    第一篇:原文链接:http://www.2cto.com/database/201503/385669.html

    Innodb引擎

    Innodb引擎提供了对数据库ACID事务的支持,并且实现了SQL标准的四种隔离级别,关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这篇文章。该引擎还提供了行级锁和外键约束,它的设计目标是处理大容量数据库系统,它本身其实就是基于MySQL后台的完整数据库系统,MySQL运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引,而且它没有保存表的行数,当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时,该引擎当然是首选。由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。

    MyIASM引擎

    MyIASM是MySQL默认的引擎,但是它没有提供对数据库事务的支持,也不支持行级锁和外键,因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表,效率便会低一些。不过和Innodb不同,MyIASM中存储了表的行数,于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持,那么MyIASM也是很好的选择。

    两种引擎的选择

    大尺寸的数据集趋向于选择InnoDB引擎,因为它支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短,InnoDB可以利用事务日志进行数据恢复,这会比较快。主键查询在InnoDB引擎下也会相当快,不过需要注意的是如果主键太长也会导致性能问题,关于这个问题我会在下文中讲到。大批的INSERT语句(在每个INSERT语句中写入多行,批量插入)在MyISAM下会快一些,但是UPDATE语句在InnoDB下则会更快一些,尤其是在并发量大的时候。

    Index——索引

    索引(Index)是帮助MySQL高效获取数据的数据结构。MyIASM和Innodb都使用了树这种数据结构做为索引,关于树我也曾经写过一篇文章树是一种伟大的数据结构,只是自己的理解,有兴趣的朋友可以去阅读。下面我接着讲这两种引擎使用的索引结构,讲到这里,首先应该谈一下B-Tree和B+Tree。

    B-Tree和B+Tree

    B+Tree是B-Tree的变种,那么我就先讲B-Tree吧,相信大家都知道红黑树,这是我前段时间学《算法》一书时,实现的一颗红黑树,大家可以参考。其实红黑树类似2,3-查找树,这种树既有2叉结点又有3叉结点。B-Tree也与之类似,它的每个结点做多可以有d个分支(叉),d称为B-Tree的度,如下图所示,它的每个结点可以有4个元素,5个分支,于是它的度为5。B-Tree中的元素是有序的,比如图中元素7左边的指针指向的结点中的元素都小于7,而元素7和16之间的指针指向的结点中的元素都处于7和16之间,正是满足这样的关系,才能高效的查找:首先从根节点进行二分查找,找到就返回对应的值,否则就进入相应的区间结点递归的查找,直到找到对应的元素或找到null指针,找到null指针则表示查找失败。这个查找是十分高效的,其时间复杂度为O(logN)(以d为底,当d很大时,树的高度就很低),因为每次检索最多只需要检索树高h个结点。

    B-Tree

    接下来就该讲B+Tree了,它是B-Tree的变种,如下面两张图所示:

    B+Tree
    MyISAM引擎的索引结构

    MyISAM引擎的索引结构为B+Tree,其中B+Tree的数据域存储的内容为实际数据的地址,也就是说它的索引和实际的数据是分开的,只不过是用索引指向了实际的数据,这种索引就是所谓的非聚集索引。

    Innodb引擎的索引结构

    Innodb引擎的索引结构同样也是B+Tree,但是Innodb的索引文件本身就是数据文件,即B+Tree的数据域存储的就是实际的数据,这种索引就是聚集索引。这个索引的key就是数据表的主键,因此InnoDB表数据文件本身就是主索引。

    因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

    并且和MyISAM不同,InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址,所以当以辅助索引查找时,会先根据辅助索引找到主键,再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键,否则会使辅助索引变得过大。建议使用自增的字段作为主键,这样B+Tree的每一个结点都会被顺序的填满,而不会频繁的分裂调整,会有效的提升插入数据的效率。

    原文 http://blog.csdn.net/voidccc/article/details/40077329
    InnoDB是Mysql的默认存储引擎(Mysql5.5.5之前是MyISAM,文档)。本着高效学习的目的,本篇以介绍InnoDB为主,少量涉及MyISAM作为对比。

    2 Mysql的存储引擎和索引

            可以说数据库必须有索引,没有索引则检索过程变成了顺序查找,O(n)的时间复杂度几乎是不能忍受的。我们非常容易想象出一个只有单关键字组成的表如何使用B+树进行索引,只要将关键字存储到树的节点即可。当数据库一条记录里包含多个字段时,一棵B+树就只能存储主键,如果检索的是非主键字段,则主键索引失去作用,又变成顺序查找了。这时应该在第二个要检索的列上建立第二套索引。  这个索引由独立的B+树来组织。有两种常见的方法可以解决多个B+树访问同一套表数据的问题,一种叫做聚簇索引(clustered index ),一种叫做非聚簇索引(secondary index)。这两个名字虽然都叫做索引,但这并不是一种单独的索引类型,而是一种数据存储方式。对于聚簇索引存储来说,行数据和主键B+树存储在一起,辅助键B+树只存储辅助键和主键,主键和非主键B+树几乎是两种类型的树。对于非聚簇索引存储来说,主键B+树在叶子节点存储指向真正数据行的指针,而非主键。

            InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。

            MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。

            为了更形象说明这两种索引的区别,我们假想一个表如下图存储了4行数据。其中Id作为主索引,Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。




            我们重点关注聚簇索引,看上去聚簇索引的效率明显要低于非聚簇索引,因为每次使用辅助索引检索都要经过两次B+树查找,这不是多此一举吗?聚簇索引的优势在哪?

            1 由于行数据和叶子节点存储在一起,这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。

            2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时辅助索引的维护工作,使用主键值当作指针会让辅助索引占用更多的空间,换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置(实现中通过16K的Page来定位,后面会涉及)会随着数据库里数据的修改而发生变化(前面的B+树节点分裂以及Page的分裂),使用聚簇索引就可以保证不管这个主键B+树的节点如何变化,辅助索引树都不受影响。

    3 Page结构

            如果说前面的内容偏向于解释原理,那后面就开始涉及具体实现了。

            理解InnoDB的实现不得不提Page结构,Page是整个InnoDB存储的最基本构件,也是InnoDB磁盘管理的最小单位,与数据库相关的所有内容都存储在这种Page结构里。Page分为几种类型,常见的页类型有数据页(B-tree Node)Undo页(Undo Log Page)系统页(System Page) 事务数据页(Transaction System Page)等。单个Page的大小是16K(编译宏UNIV_PAGE_SIZE控制),每个Page使用一个32位的int值来唯一标识,这也正好对应InnoDB最大64TB的存储容量(16Kib * 2^32 = 64Tib)。一个Page的基本结构如下图所示:


            每个Page都有通用的头和尾,但是中部的内容根据Page的类型不同而发生变化。Page的头部里有我们关心的一些数据,下图把Page的头部详细信息显示出来:


            我们重点关注和数据组织结构相关的字段:Page的头部保存了两个指针,分别指向前一个Page和后一个Page,头部还有Page的类型信息和用来唯一标识Page的编号。根据这两个指针我们很容易想象出Page链接起来就是一个双向链表的结构。

            再看看Page的主体内容,我们主要关注行数据和索引的存储,他们都位于Page的User Records部分,User Records占据Page的大部分空间,User Records由一条一条的Record组成,每条记录代表索引树上的一个节点(非叶子节点和叶子节点)。在一个Page内部,单链表的头尾由固定内容的两条记录来表示,字符串形式的"Infimum"代表开头,"Supremum"代表结尾。这两个用来代表开头结尾的Record存储在System Records的段里,这个System Records和User Records是两个平行的段。InnoDB存在4种不同的Record,它们分别是1主键索引树非叶节点 2主键索引树叶子节点 3辅助键索引树非叶节点 4辅助键索引树叶子节点。这4种节点的Record格式有一些差异,但是它们都存储着Next指针指向下一个Record。后续我们会详细介绍这4种节点,现在只需要把Record当成一个存储了数据同时含有Next指针的单链表节点即可。


            User Record在Page内以单链表的形式存在,最初数据是按照插入的先后顺序排列的,但是随着新数据的插入和旧数据的删除,数据物理顺序会变得混乱,但他们依然保持着逻辑上的先后顺序。


            把User Record的组织形式和若干Page组合起来,就看到了稍微完整的形式。


            现在看下如何定位一个Record:

            1 通过根节点开始遍历一个索引的B+树,通过各层非叶子节点最终到达一个Page,这个Page里存放的都是叶子节点。

            2 在Page内从"Infimum"节点开始遍历单链表(这种遍历往往会被优化),如果找到该键则成功返回。如果记录到达了"supremum",说明当前Page里没有合适的键,这时要借助Page的Next Page指针,跳转到下一个Page继续从"Infimum"开始逐个查找。

    详细看下不同类型的Record里到底存储了什么数据,根据B+树节点的不同,User Record可以被分成四种格式,下图种按照颜色予以区分。

    1 主索引树非叶节点(绿色)

            1 子节点存储的主键里最小的值(Min Cluster Key on Child),这是B+树必须的,作用是在一个Page里定位到具体的记录的位置。

            2 最小的值所在的Page的编号(Child Page Number),作用是定位Record。

    2 主索引树叶子节点(黄色)

            1 主键(Cluster Key Fields),B+树必须的,也是数据行的一部分

            2 除去主键以外的所有列(Non-Key Fields),这是数据行的除去主键的其他所有列的集合。

            这里的1和2两部分加起来就是一个完整的数据行。

    3 辅助索引树非叶节点非(蓝色)

            1 子节点里存储的辅助键值里的最小的值(Min Secondary-Key on Child),这是B+树必须的,作用是在一个Page里定位到具体的记录的位置。

            2 主键值(Cluster Key Fields),非叶子节点为什么要存储主键呢?因为辅助索引是可以不唯一的,但是B+树要求键的值必须唯一,所以这里把辅助键的值和主键的值合并起来作为在B+树中的真正键值,保证了唯一性。但是这也导致在辅助索引B+树中非叶节点反而比叶子节点多了4个字节。(即下图中蓝色节点反而比红色多了4字节)

            3 最小的值所在的Page的编号(Child Page Number),作用是定位Record。

    4 辅助索引树叶子节点(红色)

            1 辅助索引键值(Secondary Key Fields),这是B+树必须的。

            2 主键值(Cluster Key Fields),用来在主索引树里再做一次B+树检索来找到整条记录。


            下面是本篇最重要的部分了,结合B+树的结构和前面介绍的4种Record的内容,我们终于可以画出一幅全景图。由于辅助索引的B+树与主键索引有相似的结构,这里只画出了主键索引树的结构图,只包含了"主键非叶节点"和"主键叶子节点"两种节点,也就是上图的的绿色和黄色的部分。


            把上图还原成下面这个更简洁的树形示意图,这就是B+树的一部分。注意Page和B+树节点之间并没有一一对应的关系,Page只是作为一个Record的保存容器,它存在的目的是便于对磁盘空间进行批量管理,上图中的编号为47的Page在树形结构上就被拆分成了两个独立节点。


            至此本篇就算结束了,本篇只是对InnoDB索引相关的数据结构和实现进行了一些梳理总结,并未涉及到Mysql的实战经验。这主要是基于几点原因:

            1 原理是基石,只有充分了解InnoDB索引的工作方式,我们才有能力高效的使用好它。

            2 原理性知识特别适合使用图示,我个人非常喜欢这种表达方式。

            3 关于InnoDB优化,在《高性能Mysql》里有更加全面的介绍,对优化Mysql感兴趣的同学完全可以自己获取相关知识,我自己的积累还未达到能分享这些内容的地步。

            另:对InnoDB实现有更多兴趣的同学可以看看Jeremy Cole的博客(参考文献三篇文章的来源),这位老兄曾先后在Mysql,Yahoo,Twitter,Google从事数据库相关工作,他的文章非常棒!

    展开全文
  • mysql默认的数据库引擎是MyISAM,不支持事务和外键,也可使用支持事务和外键的InnoDB。 查看当前数据库的所支持的数据库引擎以及默认数据库引擎 数据库支持的引擎和默认数据库引擎代码: 代码如下:show engines; ...
  • MySQL数据库引擎及修改引擎的方法

    千次阅读 2021-05-17 18:14:35
    数据库存储引擎数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以 获得特定的...

    一、MySQL引擎介绍
    数据库存储引擎是数据库重要的存储机制,数据库管理系统主要是使用数据引擎进行创建、查询、更新和删除数据。
    不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。在面对不同的数据库情况时,也支持使用多种数据引擎。
    在MySQL中,其核心机制就是存储引擎。

    二、MySQL引擎

    1、存储引擎查看
    可在mysql中通过指令 show engines 进行查看;

    show engines;
    

    在这里插入图片描述
    通过输出结果:可看到 DEFAULT ,默认引擎为MyISAM。
    看到MySQL给用户提供了这么多存储引擎,包括处理事务安全表的引擎和出来了非事物安全表的引擎。

    也可以通过下列命令 查看数据库默认使用哪个引擎:

    show VARIABLES LIKE 'storage_engine';
    

    在这里插入图片描述
    注:在MySQL中,不需要在整个服务器中使用同一种存储引擎,针对具体的要求,可以对每一个表使用不同的存储引擎。Support列的值表示某种引擎是否能使用:YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎 。

    2、常见的几种引擎
    2.1、InnoDB存储引擎
    InnoDB在事务型数据库中应用最多,其主要支持事务安全表(ACID),行锁定和外键。
    介绍下InnoDB的主要特性:
    1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全(ACID兼容)存储功能。
    2、InnoDB是为处理巨大数据量的最大性能设计。
    3、InnoDB存储引擎完全与MySQL服务器整合,高度适配。
    4、InnoDB支持外键完整性约束。
    5、InnoDB被用在众多需要高性能的大型数据库站点上

    注意:InnoDB不创建目录,使用InnoDB时,MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件,以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

    2.2、MyISAM存储引擎
    MyISAM基于ISAM存储引擎,并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度,但不支持事物
    MyISAM主要特性有:
    1、大文件(达到63位文件长度)在支持大文件的文件系统和操作系统上被支持
    2、当把删除和更新及插入操作混合使用的时候,动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块,以及若下一个块被删除,就扩展到下一块自动完成
    3、每个MyISAM表最大索引数是64,这可以通过重新编译来改变。每个索引最大的列数是16

    注意:使用MyISAM引擎创建数据库,将产生3个文件。
    文件的名字以表名字开始,扩展名之处文件类型:frm文件用来存储表定义、数据文件的扩展名为.MYD(MYData)、索引文件的扩展名为.MYI(MYIndex)

    2.3、MEMORY存储引擎
    MEMORY存储引擎将表中的数据存储到内存中,为查询和引用其他表数据提供快速访问。
    MEMORY主要特性有:
    1、MEMORY表的每个表可以有多达32个索引,每个索引16列,以及500字节的最大键长度
    2、MEMORY存储引擎执行HASH和BTREE缩影
    3、可以在一个MEMORY表中有非唯一键值

    3、存储引擎的选择
    不同的存储引擎都有各自的特点,以适应不同的需求,如下表所示:
    在这里插入图片描述
    总结如下:
    1、要提供提交、回滚、崩溃恢复能力的事物安全(ACID兼容)能力,并要求实现并发控制,InnoDB。
    2、数据表主要用来插入和查询记录,则MyISAM引擎能提供较高的处理效率
    3、如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果
    4、如果INSERT和SELECT操作较多,可以选择Archive。Archive非常适合存储归档数据,如记录日志信息可以使用Archive

    三、修改engine引擎的方法
    例如:将mysql的引擎修改为INNODB,【INNODB支持外键,事务等】
    方法:找到自己mysql目录下的my.ini 文件,进行如下编辑:
    在[mysqld] 下,进行如下添加:

    default-storage-engine=INNODB
    

    在这里插入图片描述
    wq保存退出,
    重启mysql服务即可

    systemctl restart mysqld
    
    展开全文
  • Mysql数据库常用两种引擎的介绍

    千次阅读 2021-01-19 02:13:48
    1.数据库引擎介绍MySQL数据库引擎取决于MySQL在安装的时候是如何被编译的。要添加一个新的引擎,就必须重新编译MYSQL。在MySQL数据库中,常用的引擎主要就是2个:Innodb和MyIASM。下面介绍这两种数据库引擎:(1) ...
  • Mysql四种常见数据库引擎

    千次阅读 2022-03-23 17:32:11
    InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键,上图也看到了,InnoDB是默认的MySQL引擎。InnoDB主要特性有: MyISAM存储引擎 MyISAM基于ISAM存储引擎,并对其进行扩展。它是在Web、...
  • MySQL数据库引擎

    千次阅读 2019-07-25 10:19:50
    一、概念 数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的...1.数据库引擎查询 1.1 查询存储引擎: -- 查询数据库版本 select ...
  • MySQL数据库提供了多种存储引擎,用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据自己的需要编写自己的存储引擎,MySQL的核心就是存储引擎。 ;MySQL 5.7支持的存储引擎有:InnoDB、MyISAM、...
  • 本文主要介绍了修改MySQL数据库引擎为INNODB的方法,希望能对您有所帮助。
  • MYSQL 数据库引擎

    千次阅读 2012-11-26 10:57:52
    MySQL数 据库引擎取决于MySQL在安装的时候是如何被编译的。要添加一个新的引擎,就必须重新编译MYSQL。在缺省情况下,MYSQL支持三个引擎:...下面介绍几种数据库引擎:  ISAM:ISAM是一个定义明确且历经时间考验
  • MySQL数据库引擎详解

    千次阅读 2018-06-15 10:00:43
    作为经常使用MySQL数据库的人,不得不对MySQL的数据库引擎有一定的了解,在之前我也是只会使用MySQL,但两次的面试中都被面试官问及项目用到的数据库引擎(血与泪),因此,有必要写一篇文章,好好总结一下MySQL的...
  • 2、如果有数据库或数据表使用了InnoDB引擎,恢复的时候,必须连同MySQL数据库目录下的ibdata1文件一起拷贝过来。 解决办法: 1、停止MySQL服务 service mysqld stop 2、找之前的备份数据库文件 cd /home/mysql_bak/m
  • 对于MySQL数据库,如果你要使用事务以及行级锁就必须使用INNODB引擎。如果你要使用全文索引,那必须使用myisam,那如何修改修改MySQL的引擎为INNODB呢,下面介绍一个修改方法
  • 如何查看mysql数据库引擎

    千次阅读 2018-10-08 12:13:54
    一般情况下,mysql会默认提供多种存储引擎,你可以通过下面的查看: 看你的mysql现在已提供什么存储引擎: mysql> show engines; 看你的mysql当前默认的存储引擎: mysql> show variables like '%storage_...
  • Mysql数据库引擎

    万次阅读 2018-02-01 15:36:47
    一、MyIsam 1)不支持事务。 2)不支持外键。...在MySQL 5.7,InnoDB是默认的MySQL存储引擎 主要优势包括: 1)DML操作遵循ACID模型,具有commit , rollback 和 crash-recovery,保护数据的安全性; ...
  • 3 数据库存储引擎 3.1 InnoDB 3.2 MyISAM 3.3 MEMORY 3.4 存储引擎的选择 首先数据库是指长期存储在计算机内、有组织的和可共享的数据集合。简而言之,数据库就是一个存储数据的地方。只是,其存储方式有特定的规律...
  • 使用clickhouse的数据库引擎mysql创建一个库用来同步mysql中的数据,命令如下: CREATE DATABASE mysql_db ENGINE = MySQL('localhost:3306','click_test','root','123456'); 结果报如下错误: CREATE DATABASE ...
  • 数据库-Mysql剑谱之数据库引擎全文共2页,当前为第1页。数据库-Mysql剑谱之数据库引擎全文共2页,当前为第1页。Mysql剑谱之数据库引擎 数据库-Mysql剑谱之数据库引擎全文共2页,当前为第1页。 数据库-Mysql剑谱之...
  • mysql数据库引擎切换(InnoDB,MyISAM)

    千次阅读 2017-08-17 18:03:33
    mysql默认的数据库引擎是MyISAM,不支持事务和外键,也可使用支持事务和外键的InnoDB。 查看当前数据库的所支持的数据库引擎以及默认数据库引擎 数据库支持的引擎和默认数据库引擎代码: 复制代码代码...
  • MySQL常用数据库引擎

    千次阅读 2019-05-24 18:02:49
    MySQL常用数据库引擎 查看MySQL数据库使用的引擎 SHOW ENGINES; 查看数据库默认使用哪个引擎 SHOW VARIABLES LIKE 'storage_engine'; MyISAM存储引擎 MyISAM基于ISAM存储引擎,并对其进行扩展。它是在...
  • Mysql数据库引擎类型与特性对比

    千次阅读 2016-05-16 23:04:22
    原文来自:MySQL数据库的各种存储引擎详解  MySQL有多种存储引擎,每种存储引擎有各自的优缺点,大家可以择优选择使用: MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE...
  • MySQL数据库面试题(2020最新版)

    万次阅读 多人点赞 2020-03-10 17:20:40
    数据类型mysql有哪些数据类型引擎MySQL存储引擎MyISAM与InnoDB区别MyISAM索引与InnoDB索引的区别?InnoDB引擎的4大特性存储引擎选择索引什么是索引?索引有哪些优缺点?索引使用场景(重点)............
  • InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。 基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行...
  • MySQL数据库引擎取决于MySQL在安装的时候是如何被编译的。事务是指一组相互依赖的操作行为,事务的成功取决于这些相互依赖的操作行为是否都能执行成功,只要有一个操作行为失败,就意味着整个事务失败。数据库隔离...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 364,056
精华内容 145,622
关键字:

mysql数据库引擎

mysql 订阅