MySQL面试题（一）

select语句的执行流程1.连接器与客户端进行TCP三次握手建立连接校验客户端的用户名和密码读取权限2. 查询缓存较为鸡肋执行更新操作表的缓存就会被清空3. 解析器词法分析语法分析4. 预处理器查询语句的字段和表是否存在5. 优化器选择哪个索引6. 执行器三种方式 主键索引查询、全表扫描、索引下推MySQL存储文件在数据库下通常包括三个文件db.opt存放默认字符集和字符校验规则t_order.frm存放表的元数据信息主要包换表结构定义t_order.ibd存放表数据表文件结构行row数据库的记录是按行存取的页pageInnoDB的数据是按页读写的默认页大小为16KB区extent当表中数据量比较大时为索引分配空间按照区为单位分配段segment表空间由多个段组成索引段存放B树非叶子节点的集合、数据段存放B树的叶子节点的区的集合、回滚段存放回滚数据的区的集合索引分类按数据结构分类Btree索引、Hash索引、Full-text索引按物理存储分类聚簇索引、二级索引按字段特性分类主键索引、唯一索引、普通索引、前缀索引按字段个数分类单列索引、联合索引Btree的优势相对于B tree,Btree不在叶子节点上存储数据可以存储更多数据且支持范围查询相对于二叉树当数据达到千万级别Btree依然维持在3~4层相对于Hash可以进行范围查询什么时候不需要索引字段中存在大量重复数据区分度低的例男女表数据太少的经常更新的字段不利创建索引怎么优化索引前缀索引优化使用某个字段中字符串的前几个字符建立索引覆盖索引优化建立联合索引覆盖查询的字段从二级索引的叶子节点中直接查到避免回表查询主键索引最好是自增当插入新数据时直接追加可以防止页分裂防止索引失效使用左右模糊匹配like %xx或like %xx%在查询条件中对索引列做了计算、函数、类型转换操作联合索引没有正确的使用最左原则匹配where后的条件OR,OR后的列不是索引列索引就会失效Btree的特点Btree的节点内容是数据页默认大小16KB只有叶子节点才存放数据非叶子节点仅用来存放目录项作为索引所有节点按照索引键大小排序构成一个双向链表聚簇索引与二级索引的区别聚簇索引存放的是包含所有列的实际数据二级索引存放的是主键值聚簇索引有主键则作为索引键没有主键选择不含NULL的唯一列作为索引键两个都没有自动生成一个隐式自增id列Btree与B tree的区别单点查询B tree的平均时间更快但是波动更大Btree非叶子节点不存数据存索引查数据都要查到最低层插入和删除效率Btree有大量冗余节点直接删除有冗余直接插入没有冗余节点自动分裂范围查询Btree叶子节点之间存在双向链表便于范围查询索引失效情况使用左右模糊匹配like %xx或like %xx%在查询条件中对索引列使用函数在查询条件中对索引列进行表达式计算索引列发生隐式类型转换联合索引没有正确的使用最左原则匹配where后的条件OR,OR后的列不是索引列索引就会失效count(*)、count(1)、count(主键字段)、count(字段)的区别count(主键索引)优先遍历二级索引再遍历聚簇索引count(1)在表中没有二级索引的时候效率高于count(主键索引)有则相同count(*) 执行过程跟 count(1) 执行过程基本一样的性能没有什么差异count(字段)的效率最差会走全表扫描为什么InnoDB要通过遍历来计数MyISAM引擎中每张表都有一个meta存储row_count值。但是在InnoDB引擎中支持事务由于MVCC同一时间有多个查询