mysql的优化索引问题

MySQL

一. Innodb与MyISAM存储引擎区别

MySQL目前最新版本是 8.X，已经弃用了MyISAM.

1. Innodb支持事务，MyISAM不支持。
2. Innodb是行锁，MyISAM是表锁
3. Innodb支持外键，MyISAM不支持。
4. MyISAM保存的有行数，而InnoDB没有。select count(*) from user
5. 在早期的版本中，InnoDB是不支持全文检索的，MyISAM支持，从5.7 之后InnoDB开始支持全文检索。

搜索引擎优化：如果数据库表，查询居多(支付流水、日志记录)，用MyISAM存储引擎。

二. 索引问题

创建索引的方式：

1. 自动创建，primary key、unique()、foreign key()
2. 手动创建: create index index_name on table_name(字段名)；

2.1 B-Tree

在早期的MySQL中使用的是 B-Tree， 不是二叉树，是N叉树，将数据和索引的节点放到一起了，带来问题就是Buffer Size大小固定的情况下，存放的数据变少了，从而导致树高变高了，IO次数增加了。

2.2 B+Tree

InnoDB存储引擎采用的是 B+Tree. 将数据方在放到一起的，按照从小大到有序摆放。

MySQL也叫做索引组织表。

三. SQL优化

3.1 插入优化

插入优化，不要执行一条条的插入，而使用批量插入语句。

insert into user(name) values('XXX'), ('AAAA'), ('BBBB')

3.2 查询优化

1. 在查询中在where之后的条件判断字段加索引

create index name_index on user(name);

2. 在索引的字段上不要使用前缀模糊查询

explain select * from user where name like '%x';

3. 在有索引的字段上不要使用函数。

explain select * from user where substr(name,0, 2) = 'xx';
explain select * from user where year(birthday) = 2016;

4. 在某个字段有索引的场景下，is null 是会使用索引的。

# 使用索引
explain select name,gender from user where name is null;
explain select * from user where name is null;

is not null 如果在select后只是当前字段，会使用索引。

# 会使用索引
explain select name from user where name is not null;
# 不会使用索引
explain select name,gender from user where name is not null;

5. 使用union查询代替 in 查询。

# type 是 range
explain select * from user where id in (4, 5);

# type是const
explain select * from user where id = 4 union select * from user where id = 5;

6. 联合索引，避免回表查询，对几个字段同时创建索引，能够避免产生过多的索引树的问题，在针对其中一个字段查询其他字段的时候，能够使用到索引，避免会表查询。

# 创建联合索引
create index name_index on usre(name, birthday, gender);

# 会使用索引
explain select name from user where birthday = '2016-12-12';
# 不会使用索引
explain select * from user where birthday = '2016-12-12';
# 会使用索引
explain select name, gender from user where birthday = '2016-12-12';

# 不会使用索引 
explain select name, gender,id,email from user where birthday = '2016-12-12';

7. 在联合索引场景下，查询除开联合索引以外的字段，需要符合最左原则。

# 走索引
explain select * email from user where name = 'xx';

# 走索引
explain select * from user where name = '23' and gender = 'M';

# 不走索引
explain select * from user where birthday = '23' and gender = 'M';

3.4 分库分表

面试：

InnoDB和MyISAM存储引擎的区别。

SQL优化：

1. 批量插入的优化，尽量使用批量插入语句。
2. 查询优化：

​ A. 在经常出现的where后的字段上添加索引和关联字段上加索引。

​ B. 在有索引的场景下，不用使用like ‘%xx’ 查询。

​ C. 不要在有索引的字段使用数据库的函数。

​ D. 使用union代替in查询。

​ E. 尽量使用联合索引，好处在于可以有效避免回表查询，减少索引树。

什么场景下索引会失效？

​ A. 使用前缀模糊查询。

​ B. 联合索引场景下，违背最左原则。

​ C. 在索引列使用函数。

​ D. 在使用 is not null 的时候，查询了非索引字段。

在做MySQL面试题的时候总共四种题型：

1. 表关联查询，join.
2. 行列转换： https://blog.csdn.net/ningmengban/article/details/76489886，

解题思路：case when then when then else end;

3. 分组查询，group by, min max avg count sum
4. exists