mysql常用的存储引擎以及各个使用场景

时间：2022-09-18 21:10:13
 

 

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支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
 

5、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）
 

存储引擎的选择



不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：
 




功  能 
   MYISAM 
   Memory 
   InnoDB 
   Archive 
  



存储限制 
   256TB 
   RAM 
   64TB 
   None 
  



支持事物 
   No 
   No 
   Yes 
   No 
  



支持全文索引 
   Yes 
   No 
   No 
   No 
  



支持数索引 
   Yes 
   Yes 
   Yes 
   No 
  



支持哈希索引 
   No 
   Yes 
   No 
   No 
  



支持数据缓存 
   No 
   N/A 
   Yes 
   No 
  



支持外键 
   No 
   No 
   Yes 
   No 
  




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

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

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

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



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


 

什么是反范式设计？常用的反范式设计有哪些？
 

　反范式是通过增加冗余数据或数据分组来提高数据库读性能的过程。在某些情况下， 反范式有助于掩盖关系型数据库软件的低效。关系型的范式数据库即使做过优化， 也常常会带来沉重的访问负载。
 

后一个范式都是在满足前一个范式的基础上建立的.

1NF:无重复的列.表中的每一列都是不可分割的基本数据项.不满足1NF的数据库不是关系数据库.
如联系人表(姓名,电话),一个联系人有家庭电话和公司电话,则不符合1NF,应拆分为(姓名,家庭电话,公司电话).

2NF:属性完全依赖于主键.不能存在仅依赖于关键一部分的属性.
如选课关系(学号,课程名称,成绩,学分),组合关键字(学号,课程名称)作为主键.其不满足2NF，因为存在决定关系:课程名称->学分,即存在组合主键中的部分字段决定非主属性的情况.会导致数据冗余,更新/插入/删除异常.

3NF:属性不传递依赖于其它非主属性.非主键列必须直接依赖于主键，而不能传递依赖。即不能是：非主键A依赖于非主键B，非主键B依赖于主键.
如学生表(学号,姓名,学院编号,学院名称),学号是主键,姓名、学院编号、学院名称都完全依赖于学号,满足2NF,但不满足3NF,因为学院名称直接依赖的是学院编号 ,它是通过传递才依赖于主键.

范式可以避免数据冗余，减少数据库的空间，减轻维护数据完整性的麻烦.但等级越高的范式设计出来的表越多,可能会增加查询所需时间.当我们的业务所涉及的表非常多，经常会有多表连接，并且我们对表的操作要时间上要尽量的快，这时可以考虑我们使用“反范式”.也就是用空间来换取时间,把数据冗余在多个表中,当查询时可以减少或者是避免表之间的关联.
比如两个表(用户id,好友id)和(用户id,用户昵称,用户邮箱,联系电话)符合3NF,如果需查询某个用户的好友(昵称)名单,此时需对2个表进行连接查询,可以把第一个表修改成(用户id,好友id,好友昵称)这样只需要查询第一个表就可获取所有好友昵称.




 

mysql在什么情况下使用了索引，但是却不起作用？
 

 

比如以下几种情况，将导致索引失效：
 

 1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
 

 

注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
 

2.对于多列索引，不是使用的第一部分，则不会使用索引
 

3.like查询是以%开头
 

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
 

5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
 


 

 

MYSQL查看执行计划
 
  

 

1、 概述：
 

 

       执行计划的查看是进行数据库的sql语句调优时依据的一个重要依据，mysql的执行计划查看相对oracle简便很多，功能也相对简单很多的SQL语句都不能直接查看。



本文档整理了mysql执行计划的生成方法和查看。
 

 
 

2、 执行计划的生成和查看
 

      2.1 执行计划的生成方法：explain select …………….
 

      生成的方法很简单在相应的select前面加explain即可
 

 
 

      2.2 执行计划的查看
 

     Id：包含一组数字，表示查询中执行select子句或操作表的顺序；
 

           执行顺序从大到小执行；
 

           当id值一样的时候，执行顺序由上往下；
 

     Select_type:表示查询中每个select子句的类型（简单OR复杂），有以下几种
 




? SIMPLE：查询中不包含子查询或者UNION


? PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY


? SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY


? DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）


? 若第二个SELECT出现在UNION之后，则被标记为UNION；


? 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED


? 从UNION表获取结果的SELECT被标记为：UNION RESULT

 
  



 
 

 
 

Type：表示MySQL在表中找到所需行的方式，又称“访问类型”，常见有以下几种
 




? ALL：Full Table Scan， MySQL将进行全表扫描；


? index：Full Index Scan，index与ALL区别为index类型只遍历索引树；


? range：range Index Scan，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询；


? ref：非唯一性索引扫描，返回匹配摸个单独值的所有行。常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找；


? eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描


? const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量


? NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

 
  


 
 

 
 

possible_keys：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用；



 
 

key：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。当查询中若使用了覆盖索引，则该索引仅出现在key列表中
 

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
 

ref：表示上述表的连接匹配条件，即那些列或常量被用于查找索引列上的值；
 

rows：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数；
 

Extra：包含不适合在其他列中显示但十分重要的额外信息；
 




? Using where：表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）,如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集


? Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询；


? Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”；

 
  



 
 

 
 

2.3 mysql执行计划的局限
 

 
 




EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况


EXPLAIN不考虑各种Cache


EXPLAIN不能显示MySQL在执行查询时所作的优化工作


部分统计信息是估算的，并非精确值


EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

 
  


3、 对于非select语句查看执行计划
 

在实际的工作中也经常需要查看一些诸如update、delete的执行计划，（mysql5.6的版本已经支持直接查看）但是这时候并不能直接通过explain来进行查看，而需要通过改写语句进行查看执行计划
 
 


 


标签：
相关文章