MySQL面试必看

1.MySQL中的索引用的是什么数据结构

Innodb使用B+树数据结构

        1.Hash表：等值查询效率比较高、但是不支持范围查询。

        2.二叉树：时间复杂度log2n 缺点：有可能产生不平衡 类似于链表的结构 时间复杂度为o(n)。

        3.平衡二叉树avl/红黑树：使用旋转和变色方式保证平衡 它是一 棵空树或它的左右两个子树的高度差的绝对值不超过1。

缺点：如果存放几百条数据的情况下，树高度越高，查询效率会越慢

        4.B-Tree树：B树中每个节点都存放着索引和数据，数据遍布整个树结构，搜索可能在非叶子节点结束，最好的情况是O(1)。一般一棵 B 树的高度在 3 层左右，3 层就可满足 百万级别的数据量，也就是B树 叶子与非叶子节点 都会存放data数据

        5.B+树

                1.非叶子节点不存储data，只存放索引(冗余)

                2.叶子节点包含所有索引字段

                3.叶子节点用指针连接，提高区间访问的性能

B树与B+树有什么区别：

B+树是B树的一种变种，它与 B树 的 区别 是：

        1.B树叶子节点保存了完整的索引和数据，而B+树非叶子节点只保存索引值，因此它的查询时间固定为 log(n).

        2.B+树在叶子节点中有指向下一个叶子节点的指针，叶子节点类似于一个单链表

正因为叶子节点保存了完整的数据以及有指针作为连接，B+树可以增加了区间访问性，提高了范围查询，而B树的范围查询相对较差

        3.B+树更适合外部存储。因为它的非叶子节点不存储数据，只保存索引。

        4.B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。

2.MySQL中myisam和innodb引擎的区别

1.Innodb 引擎存在事务 Myisam 引擎不存在事务

2.mysql 默认使用存储引擎Innodb

3.mysql聚集索引和非聚集索引的区别 

1、聚集索引在叶子节点存储的是表中的数据（数据和索引都是在叶子节点中），而非聚集索引在叶子节点存储的是主键和索引列； 例如 我们额外创建一个普通索引需要回表查询。

2、聚集索引中表记录的排列顺序和索引的排列顺序一致，而非聚集索引的排列顺序不一致；

3、聚集索引每张表只能有一个，而非聚集索引（回表）可以有多个。

4.为什么INNODB引擎表必须有主键，并且推荐使用整型的自增方式？

不建议使用uuid作为数据库主键，因为叶子节点会根据索引key顺序排列 否则会发生乱序从而不支持范围查询。如果使用不采用自增id不断地调整数据的物理地址、分页 效率非常低，如果是自增的，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

5.MySQL 索引为什么使用B+树而不是B树

B树数据结构特征

1. 叶子和非叶子节点键值都对应存放data数据

2. B树不支持范围查询

3. B树 数据结构导致 每次磁盘io读取 键值就比较少。

B+树数据结构

1. 叶子节点存放data数据 非叶子节点是不存放data数据

2. 非叶子节点就存放很多键值key （每次磁盘io读取 读取更多键值）

3. 叶子节点使用链表结构相连接支持范围查询Mysql 存储引擎 B+树数据结构

6.什么是回表查询？

1.因为非主键索引 叶子节点上的data值存放的是主键id，并非整行完整数据。

2. 如果查询返回列的数据是完整数据，就需要回表在根据叶子节点中的data值查询主键索引返回整行数据。这也是为什么在mysql中必须要有主键，就算没有主键也会单独创建主键id，也会有rowid

7.MySQL索引优化

1. 首先定位慢查询；

2. 通过EXPLAIN 慢查询语句

慢查询语句响应慢： 没有使用索引或索引使用失效；

1. 先定位慢查询

2. 执行计划EXPLAIN 分析sql语句---索引失效原因

注意：一般在一些云数据库比如阿里云、百度云都是自带管理系统的形式定位慢查询sql语句。

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES ('4', 'meite', '22', 'ceo', '2020-10-13 19:33:27');
INSERT INTO `employees` VALUES ('5', 'xiaowei', '23', 'kefu', '2020-10-13 19:33:27');
INSERT INTO `employees` VALUES ('6', 'xiaomin', '23', 'kefu', '2020-10-13 19:33:27');

1.全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'meite'; 74

EXPLAIN SELECT * FROM employees WHERE name= 'meite' AND age = 22; 78

EXPLAIN SELECT * FROM employees WHERE name= 'meite' AND age = 22 AND position ='ceo'; 140

2.遵循最佳左前缀法则

EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='ceo';

EXPLAIN SELECT * FROM employees WHERE position = 'ceo';

EXPLAIN SELECT * FROM employees WHERE name = 'meite';

EXPLAIN SELECT * FROM employees WHERE name= 'meite' AND position ='ceo';

联合索引查询需要遵循 最左前缀法则

3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM employees WHERE name = 'meite';

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'meite';

LEFT()函数是一个字符串函数，它返回具有指定长度的字符串的左边部分

4.存储引擎不能使用索引中范围条件右边的列（范围之后全失效，不包括本身）

#若中间索引列用到了范围（>、<、like等），则后面的所以全失效

EXPLAIN SELECT * FROM employees WHERE name= 'meite' AND age = 22 AND position ='ceo';

EXPLAIN SELECT * FROM employees WHERE name= 'meite' AND age > 22 AND position ='ceo';

5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句

EXPLAIN SELECT name,age FROM employees WHERE name= 'meite' AND age = 23 AND position ='ceo';

EXPLAIN SELECT * FROM employees WHERE name= 'meite' AND age = 23 AND position ='ceo';

6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫

EXPLAIN SELECT * FROM employees WHERE name != 'meite'

7.is null,is not null 也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null

8.like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%mei';

EXPLAIN SELECT * FROM employees WHERE name like 'mei%';

问题：解决like'%字符串%'索引不被使用的方法？

        可以使用覆盖索引，查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name FROM employees WHERE name like '%mei';会全表扫描索引树

9.字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = 644064779;

EXPLAIN SELECT * FROM employees WHERE name = '644064779';

10.少用or,用它连接时很多情况下索引会失效

EXPLAIN SELECT * FROM employees WHERE name = 'meite' or name = 'wangmazi';

8.优化SQL语句 EXPLAIN Type需要达到什么级别

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts

最好。

说明：

1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。

2） ref 指的是使用普通的索引（normal index）。

3） range 对索引进行范围检索。

反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range还低

9.mysql索引如何避免回表查询

1.这先要从InnoDB的索引实现说起，InnoDB有两大类索引：

        1.1聚集索引(clustered index)主键索引叶子节点data值 包含了整行数据

        1.2普通索引(secondary index) 叶子节点data值 只包含主键索引的id

2.InnoDB聚集索引的叶子节点存储行记录，因此InnoDB必须要有，且只有一个聚集索引：如果没有创建主键，则InnoDB会创建一个隐藏的row-id作为聚集索引；InnoDB普通索引的叶子节点存储主键值。

3.如果根据普通索引查询 普通索引叶子节点的data只存放了主键的id，如果查询返回列需要返回不在联合索引的字段，则会回表根据主键id 查询到内容。

select _rowid from mayikt2;