mysql原理问题整理

目录

1、mysql的innnodb和myisam存储引擎的区别

2、索引的作用

3、innodb用自增id作为主键的好处

4、Drop、Delete与Truncate的共同点和区别

5、不可重复度和幻读的区别

6、mysql有哪些索引类型

7、视图的作用是什么

---

1、mysql的innnodb和myisam存储引擎的区别

innodb

1、支持事务，实现了四个事务的隔离级别

2、主索引是聚簇索引，索引的顺序就是数据存储地址的顺序，极大提高查询效率，其中主键自增id就是一个很好的例子

3、支持在线热备份

4、支持外键

5、不保存表的具体行数

6、不支持全文索引

7、支持表、行级别的锁，包括间隙锁

8、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键）

myisam

1、主索引是非聚簇索引

2、MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）

3、MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高

4、MyISAM表格可以被压缩后进行查询操作

5、支持merge连表引擎，分表查询，可以直接到主表进行查询，不用计算数据在哪一张表

6、设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它

总结

MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢

2、索引的作用

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

• 帮助服务器避免排序和临时表

• 将随机IO变为顺序IO。

• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义

3、innodb用自增id作为主键的好处

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table）来重建表并优化填充页面

4、Drop、Delete与Truncate的共同点和区别

drop->truncate->delete

drop会删除所有数据+表结构，由于没有日志生成，不能回滚也不能触发触发器，所有的数据和索引都会被删除

truncate会删除所有数据+表初始化（释放表空间）+自增id重新开始，由于没有日志生成，不能回滚也不能触发触发器，表结构及其列、约束、索引等保持不变

delete可以删除指定的数据，rollback日志有记录，可以回滚，会触发dml触发器，删除大量数据比较占用系统资源，删除效率低，自增序列id不会充值，表空间不会被释放

5、不可重复度和幻读的区别

不可重复度重在指读的相同的一条数据

幻读指在读的时候，其它事务新增了一条或者删了一条，给人一种幻觉的感觉

6、mysql有哪些索引类型

• FULLTEXT ：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

• HASH ：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

• BTREE ：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

• RTREE ：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

7、视图的作用是什么

保护原数据不被修改