1、为什么使用索引?
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
- 帮助服务器避免排序和临时表
- 将随机IO变为顺序IO。
- 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
2、Innodb为什么要用自增id作为主键?
如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置, 频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE(optimize table)来重建表并优化填充页面。
3、MyISAM和InnoDB实现B树索引方式的区别是什么?
- MyISAM,B+Tree叶节点的data域存放的是数据记录的地址,在索引检索的时候,首先按照B+Tree搜索算法搜索索引,如果指定的key存在,则取出其data域的值,然后以data域的值为地址读取相应的数据记录,这被称为“非聚簇索引”
- InnoDB,其数据文件本身就是索引文件,相比MyISAM,索引文件和数据文件是分离的,其表数据文件本身就是按B+Tree组织的一个索引结构,树的节点data域保存了完整的数据记录,这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引,这被称为“聚簇索引”或者聚集索引,而其余的索引都作为辅助索引,辅助索引的data域存储相应记录主键的值而不是地址,这也是和MyISAM不同的地方。在根据主索引搜索时,直接找到key所在的节点即可取出数据;在根据辅助索引查找时,则需要先取出主键的值,再走一遍主索引。因此,在设计表的时候,不建议使用过长的字段为主键,也不建议使用非单调的字段作为主键,这样会造成主索引频繁分裂。
4、说一下MySQL是如何执行一条SQL的?具体步骤有哪些?
Server层按顺序执行sql的步骤为:
- 客户端请求->
- 连接器(验证用户身份,给予权限) ->
- 查询缓存(存在缓存则直接返回,不存在则执行后续操作)->
- 分析器(对SQL进行词法分析和语法分析操作) ->
- 优化器(主要对执行的sql优化选择最优的执行方案方法) ->
- 执行器(执行时会先看用户是否有执行权限,有才去使用这个引擎提供的接口)->
- 去引擎层获取数据返回(如果开启查询缓存则会缓存查询结果)
简单概括:
- 连接器:管理连接、权限验证;
- 查询缓存:命中缓存则直接返回结果;
- 分析器:对SQL进行词法分析、语法分析;(判断查询的SQL字段是否存在也是在这步)
- 优化器:执行计划生成、选择索引;
- 执行器:操作引擎、返回结果;
- 存储引擎:存储数据、提供读写接口。
5、你了解MySQL的内部构造吗?一般可以分为哪两个部分?
可以分为服务层和存储引擎层两部分,其中:
服务层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB,它从MySQL 5.5.5版本开始成为了默认的存储引擎。
6、说一说Drop、Delete与Truncate的共同点和区别
第一种回答
Drop、Delete、Truncate都表示删除,但是三者有一些差别:
Delete用来删除表的全部或者一部分数据行,执行Delete之后,用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除,会触发这个表上所有的delete触发器。
Truncate删除表中的所有数据,这个操作不能回滚,也不会触发这个表上的触发器,TRUNCATE比Delete更快,占用的空间更小。
Drop命令从数据库中删除表,所有的数据行,索引和权限也会被删除,所有的DML触发器也不会被触发,这个命令也不能回滚。
因此,在不再需要一张表的时候,用Drop;在想删除部分数据行时候,用Delete;在保留表而删除所有数据的时候用Truncate。
第二种回答
- Drop直接删掉表;
- Truncate删除表中数据,再插入时自增长id又从1开始 ;
- Delete删除表中数据,可以加where字句。
具体解析
- DELETE语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务