mysql常⽤的存储引擎以及各个使⽤场景
InnoDB存储引擎
InnoDB是事务型数据库的⾸选引擎，⽀持事务安全表（ACID），⽀持⾏锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：
1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能⼒的事物安全（ACID兼容）存储引擎。InnoDB锁定在⾏级并且也在SELECT语句中提供⼀个类似的⾮锁定读。这些功能增加了多⽤户部署和性能。在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 RAM64TB 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是⼀个好的选择
mysql安装路径在哪如果数据表主要⽤来插⼊和查询记录，则MyISAM引擎能提供较⾼的处理效率
php中switch语句如果只是临时存放数据，数据量不⼤，并且不需要较⾼的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使⽤该引擎作为临时表，存放查询的中间结果
如果只有INSERT和SELECT操作，可以选择Archive，Archive⽀持⾼并发的插⼊操作，但是本⾝不是事务安全的。Archive⾮常适合存储归档数据，如记录⽇志信息可以使⽤Archive
使⽤哪⼀种引擎需要灵活选择，⼀个数据库中多个表可以使⽤不同引擎以满⾜各种性能和实际需求，使⽤合适的存储引擎，将会提⾼整个数据库的性能
什么是反范式设计？常⽤的反范式设计有哪些？python基本操作
　反范式是通过增加冗余数据或数据分组来提⾼数据库读性能的过程。在某些情况下，反范式有助于掩盖关系型数据库软件的低效。关系型的范式数据库即使做过优化，也常常会带来沉重的访问负载。
后⼀个范式都是在满⾜前⼀个范式的基础上建⽴的.
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.对于多列索引，不是使⽤的第⼀部分，则不会使⽤索引
2.对于多列索引，不是使⽤的第⼀部分，则不会使⽤索引
3.like查询是以%开头
4.如果列类型是字符串，那⼀定要在条件中将数据使⽤引号引⽤起来，否则不使⽤索引
5.如果mysql估计使⽤全表扫描要⽐使⽤索引快，则不使⽤索引
MYSQL查看执⾏计划
1、概述：
执⾏计划的查看是进⾏数据库的sql语句调优时依据的⼀个重要依据，mysql的执⾏计划查看相对oracle简便很多，功能也相对简单很多的SQL语句都不能直接查看。
本⽂档整理了mysql执⾏计划的⽣成⽅法和查看。
2.1 执⾏计划的⽣成⽅法：explain select …………….
⽣成的⽅法很简单在相应的select前⾯加explain即可
2.2 执⾏计划的查看
mysql查看所有存储过程
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列表中，MyS QL就能将该查询转换为⼀个常量
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在执⾏查询时所作的优化⼯作
部分统计信息是估算的，并⾮精确值
js插件推荐
EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执⾏计划
3、对于⾮select语句查看执⾏计划
在实际的⼯作中也经常需要查看⼀些诸如update、delete的执⾏计划，（mysql5.6的版本已经⽀持直接查看）但是这时候并不能直接通过explain来进⾏查看，⽽需要通过改写语句进⾏查看执⾏计划

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。