1、MySQL索引使用注意事项

1.1、 索引哪些情况会失效

• 查询条件包含or，可能导致索引失效
• 如果字段类型是字符串，where时一定用引号括起来，否则索引失效
• like通配符可能导致索引失效。
• 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
• 在索引列上使用mysql的内置函数，索引失效。
• 对索引列运算（如，+、-、*、/），索引失效。
• 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
• 索引字段上使用is null， is not null，可能导致索引失效。
• 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
• mysql估计使用全表扫描要比使用索引快,则不使用索引。

1.2 、索引不适合哪些场景

• 数据量少的不适合加索引
• 更新比较频繁的也不适合加索引
• 区分度低的字段不适合加索引（如性别） 对比 部件表 rest_status_id 就不需要添加索引。

• 索引区分度越明显，查询效率越高，可以使用以下公示，预估索引是否需要添加：

区分度 = 用到索引的记录数 / 总数 （select count(*) from a where idx = xxxx / select count(*)）

2、InnoDB与MyISAM的区别

• InnoDB支持事务，MyISAM不支持事务
• InnoDB支持外键，MyISAM不支持外键
• InnoDB 支持 MVCC(多版本并发控制)，MyISAM 不支持
• select count(*) from table时，MyISAM更快，因为它有一个变量保存了整个表的总行数，可以直接读取，InnoDB就需要全表扫描。
• Innodb不支持全文索引，而MyISAM支持全文索引（5.7以后的InnoDB也支持全文索引）
• InnoDB支持表、行级锁，而MyISAM支持表级锁。
• InnoDB表必须有主键，而MyISAM可以没有主键
• Innodb表需要更多的内存和存储，而MyISAM可被压缩，存储空间较小，。
• Innodb按主键大小有序插入，MyISAM记录插入顺序是，按记录插入顺序保存。
• InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，与 MyISAM 比 InnoDB 写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引

3、数据库索引的原理，为什么要用B+树，为什么不用二叉树

可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数，为什么不是二叉树，为什么不是平衡二叉树，为什么不是B树，而偏偏是B+树呢？

为什么不是一般二叉树？

如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

为什么不是平衡二叉树呢？

我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

那为什么不是B树而是B+树呢？

1）B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

2）B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

4、limit 1000000加载很慢的话，怎么解决的呢

方案一：如果id是连续的，可以这样，返回上次查询的最大记录(偏移量)，再往下limit

连续id:
select id，name from employee where id>1000000 limit 10

方案二：order by + 索引（id为索引）

order by 主键:
select id，name from employee order by id  limit 1000000，10

方案三：利用延迟关联或者子查询优化超多分页场景。（先快速定位需要获取的id段，然后再关联）

联表查询：
SELECT a.* FROM employee a, (select id from employee where 条件 LIMIT 1000000,10 ) b where a.id=b.id

5、如何写sql能够有效的使用到复合索引

复合索引，也叫组合索引，用户可以在多个列上建立索引,这种索引叫做复合索引。

当创建一个组合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

联合索引：
select * from table where k1=A AND k2=B AND k3=D

6、mysql中in 和exists的区别

举例说明下吧：

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下SQL:

in 操作：
select * from A where deptId in (select deptId from B);

等价于：

先查询部门表B

select deptId from B

再由部门deptId，查询A的员工

select * from A where A.deptId = B.deptId

显然，除了使用in，我们也可以用exists实现一样的查询功能，如下：

exists查询：
select * from A where exists (select 1 from B where A.deptId = B.deptId);

因为exists查询的理解就是，先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果（true或者false），来决定主查询的数据结果是否得意保留。

执行相当于 ：

    select * from A,先从A表做循环

    select * from B where A.deptId = B.deptId,再从B表做循环.

结论：
exists 与 in 操作正好相反。。。。。

数据库最费劲的就是跟程序链接释放。假设链接了两次，每次做上百万次的数据集查询，查完就走，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，这样系统就受不了了。

即mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。

因此，我们要选择最外层循环小的，也就是，如果B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exists，这就是in和exists的区别

7、创建索引有什么原则

• 最左前缀匹配原则
• 频繁作为查询条件的字段才去创建索引
• 频繁更新的字段不适合创建索引
• 索引列不能参与计算，不能有函数操作
• 优先考虑扩展索引，而不是新建索引，避免不必要的索引
• 在order by或者group by子句中，创建索引需要注意顺序
• 区分度低的数据列不适合做索引列(如性别）
• 定义有外键的数据列一定要建立索引。
• 对于定义为text、image数据类型的列不要建立索引。
• 删除不再使用或者很少使用的索引

8、百万级别或以上的数据，如何删除

• 想要删除百万数据的时候可以先删除索引
• 然后批量删除其中无用数据
• 删除完成后重新创建索引。

Q：没明白为什么要先删除索引。。。。我的理解，先删除，不就锁表了吗？

还有一种删除思路：

删除百万数据：
create table test_a like test_b;  会保留索引相关的信息
create table test_a as test_b;   不推荐
 
 
insert into test_a select * from test_b where id >0 and id<50000 # 50000条数据导一次
 
 
最后
rename table test_b to test_b_bak 备份
rename table test_a to test_b

9、覆盖索引、回表

• 覆盖索引：查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
• 回表：二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。

10、B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据

• 在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。
• 当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

11、drop、delete与truncate的区别

12、UNION与UNION ALL的区别

• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
• Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
• UNION的效率高于 UNION ALL

13、Sql的执行顺序

图片上传失败。。

14.MySQL数据库cpu飙升，怎么处理

排查过程：

• 使用top 命令观察，确定是mysqld导致还是其他原因。
• 如果是mysqld导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。
• 找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理：

• kill 掉这些线程(同时观察 cpu 使用率是否下降)，
• 进行相应的调整(比如说加索引、改 sql、改内存参数)
• 重新跑这些 SQL。

15 .MySQL的复制原理以及流程

主从复制原理，简言之，就三步曲，如下：

• 主数据库有个bin-log二进制文件，记录了所有增删改Sql语句。（binlog线程）
• 从数据库把主数据库的bin-log文件的sql语句复制过来。（io线程）
• 从数据库的relay-log重做日志文件中再执行一次这些sql语句。（Sql执行线程）

图片上传失败。。

上图主从复制分了五个步骤进行：

步骤一：主库的更新事件(update、insert、delete)被写到binlog

步骤二：从库发起连接，连接到主库。

步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库。

步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log

步骤五：还会创建一个SQL线程，从relay log里面读取内容，从ExecMasterLog_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

16、MySQL的Explain

Explain 执行计划包含字段信息如下：分别是 id、selecttype、table、partitions、type、possiblekeys、key、key_len、ref、rows、filtered、Extra 等12个字段。

重点关注的是type，它的属性排序如下：

system > const > eq_ref > ref > ref_or_null >index_merge > unique_subquery > index_subquery >range > index > ALL

17、Innodb的事务与日志的实现方式

innodb两种日志redo和undo

日志的存放形式

• redo：在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log 的文件系统缓存里面(fwrite)，然后再同步到磁盘文件（ fsync）。
• undo：在 MySQL5.5 之前， undo 只能存放在 ibdata文件里面， 5.6 之后，可以通过设置 innodbundotablespaces 参数把 undo log 存放在 ibdata之外。

事务是如何通过日志来实现的

• 因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo 的 redo， 然后修改数据页，再记数据页修改的 redo。Redo（里面包括 undo 的修改） 一定要比数据页先持久化到磁盘。
• 当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的 状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo把该事务的修改回滚到事务开始之前。
• 如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。

18、数据库是否支持emoji表情存储，如果不支持，如何操作

更换字符集utf8→utf8mb4 （5.6以上的版本才支持）

19、一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录

1、如果A表TID是自增长,并且是连续的,B表的ID为索引

大数据快速查询01：
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。

大数据快速查询02：
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

20、Innodb的事务实现原理

• 原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。
• 持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。
• 隔离性：通过锁以及MVCC,使事务相互隔离开。
• 一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性

关注不迷路~持续输出~