索引哪些情况会失效？

• 查询条件包含or，会导致索引失效（or前后都有索引，且都有序会生效？）。
• 隐式类型转换。会导致索引失效，例如age字段类型是int，我们where age = “1”，这样就会触发隐式类型转换。
• like通配符会导致索引失效。注意："ABC%“会走range索引，”%ABC"索引才会失效。
• 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
• 对索引字段进行函数运算。
• 对索引列运算（如，+、-、*、/），索引失效。
• 索引字段上使用（!= 或者 < >，not in）时，会导致索引失效。
• 索引字段上使用is null， is not null，可能导致索引失效。
• 相join的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算。
• mysql估计使用全表扫描要比使用索引快,则不使用索引。

索引不适合哪些场景？

• 数据量少的不适合加索引。
• 更新比较频繁的也不适合加索引。
• 离散性低的字段不适合加索引（如性别）。

MySQL 遇到过死锁问题吗，你是如何解决的？

• 查看死锁日志show engine innodb status;
• 找出死锁Sql
• 分析sql加锁情况
• 模拟死锁案发
• 分析死锁日志
• 分析死锁结果

日常工作中你是怎么优化SQL的？

• 加索引
• 避免返回不必要的数据
• 适当分批量进行
• 优化sql结构

分库分表方案？

分表：

• 水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
• 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

分库：

• 水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
• 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

分库分表可能遇到的问题？

• 事务问题：需要用分布式事务
• 跨节点连表查询问题：解决这一问题可以分两次查询实现
• 跨节点的count,order by,group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。
• 数据迁移，容量规划，扩容等问题。
• ID问题：数据库被切分后，不能再依赖数据库自身的主键生成机制，最简单可以考虑UUID。
• 跨分片的排序分页问题

InnoDB与MyISAM的区别？

• InnoDB支持事务，MyISAM不支持事务
• InnoDB支持外键，MyISAM不支持外键
• InnoDB 支持 MVCC(多版本并发控制)，MyISAM 不支持
• select count(*) from table时，MyISAM更快，因为它有一个变量保存了整个表的总行数，可以直接读取，InnoDB就需要全表扫描。
• Innodb不支持全文索引，而MyISAM支持全文索引（5.7以后的InnoDB也支持全文索引）
• InnoDB支持表、行级锁，而MyISAM支持表级锁。

聚集索引与非聚集索引的区别？

• 一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。
• 聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
• 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
• 聚集索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

limit 1000000 加载很慢的话，你是怎么解决的呢？

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

select id，name from employee where id>1000000 limit 10.
方案二：在业务允许的情况下限制页数：
建议跟业务讨论，有没有必要查这么后的分页。因为绝大多数用户都不会往后翻太多页。

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

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

如何选择合适的分布式主键方案呢？

• 雪花算法

• Redis生成ID

• 利用zookeeper生成唯一ID

什么是事务的隔离性？

隔离性是指，多个用户的并发事务访问同一个数据库时，一个用户的事务不应该被其他用户的事务干扰，多个并发事务之间要相互隔离。

事务的隔离级别有哪些？

• 读未提交(Read Uncommitted)

• 读提交(Read Committed, RC)

• 可重复读(Repeated Read, RR)

• 串行化(Serializable)

InnoDB的四种事务的隔离级别，分别是怎么实现的？

InnoDB使用不同的锁策略(Locking Strategy)来实现不同的隔离级别。

读未提交(Read Uncommitted)：

• 这种事务隔离级别下，select语句不加锁。

• 此时，可能读取到不一致的数据，即“读脏”。这是并发最高，一致性最差的隔离级别。

串行化(Serializable)：

• 这种事务的隔离级别下，所有select语句都会被隐式的转化为select … in share mode。

• 这可能导致，如果有未提交的事务正在修改某些行，所有读取这些行的select都会被阻塞住。

• 这是一致性最好的，但并发性最差的隔离级别。 在大数据量，高并发量的场景下，几乎不会使用上述两种隔离级别。

可重复读(Repeated Read, RR)：

这是InnoDB默认的隔离级别，在RR下：

• 普通的select使用快照读(snapshot read)，这是一种不加锁的一致性读(Consistent Nonlocking Read)，底层使用MVCC来实现；
• 加锁的select(select … in share mode / select … for update), update, delete等语句，它们的锁，依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(unique search condition)，或者范围查询条件(range-type search condition)：
  • 在唯一索引上使用唯一的查询条件，会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)。
  • 范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，以及避免不可重复的读

读提交(Read Committed, RC)：

这是互联网最常用的隔离级别，在RC下：

• 普通读是快照读；
• 加锁的select, update, delete等语句，除了在外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会封锁区间，其他时刻都只使用记录锁；

此时，其他事务的插入依然可以执行，就可能导致，读取到幻影记录。

在高并发情况下，如何做到安全的修改同一行数据？

要安全的修改同一行数据，就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和乐观锁两种方案：
悲观锁：当前线程要进来修改数据时，别的线程都得拒之门外~ 比如，可以使用select…for update
乐观锁：有线程过来，先放过去修改，如果看到别的线程没修改过，就可以修改成功，如果别的线程修改过，就修改失败或者重试。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？

• show status 命令了解各种 sql 的执行频率
• 通过慢查询日志定位那些执行效率较低的 sql 语句
• explain 分析低效 sql 的执行计划（这点非常重要，日常开发中用它分析Sql，会大大降低Sql导致的线上事故）

select for update有什么含义，会锁表还是锁行还是其他？

• select查询语句是不会加锁的，但是select for update除了有查询的作用外，还会加锁，而且是悲观锁。

• 至于加了是行锁还是表锁，这就要看是不是用了索引/主键。 没用索引/主键的话就是表锁，否则就是是行锁。

MySQL事务得四大特性以及实现原理？

原子性： 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
一致性： 指在事务开始之前和事务结束以后，数据不会被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。
隔离性： 多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。
持久性： 表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

事务ACID特性的实现思想？

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

如果某个表有近千万数据，CRUD比较慢，如何优化？

• 分库分表，分表（水平分表，垂直分表）
• 优化表结构
• 索引优化

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

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

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

• 复合索引，我们需要关注查询Sql条件的顺序，确保最左匹配原则有效，同时可以删除不必要的冗余索引。

mysql中in 和exists的区别？

mysql优化原则是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。主表数据量大适合用in，关联表数据量大适合用exists。

数据库自增主键可能遇到什么问题？

使用自增主键对数据库做分库分表，可能出现诸如主键重复等的问题。解决方案的话，简单点的话可以考虑使用UUID， 自增主键会产生表锁，从而引发问题 自增主键可能用完问题。

说一下大表查询的优化方案？

• 优化shema、sql语句+索引

• 可以考虑加缓存

• 主从复制，读写分离

• 分库分表

InnoDB引擎中的索引策略？

当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效，对于中到大型的表，索引就非常有效。

如何争取的创建和使用缩影？

• 索引列不能是表达式的一部分，也不是函数的参数。
• 长字符串只索引左边开始的部分字符。
• 选择合适的索引的选择性：select count(distinct left(city,6))/count(*) from skill.city_demo
• 尽量使用联合索引

前缀索引缺点？

MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

联合索引列选择原则？

• 经常用的列优先【最左匹配原则】
• 选择性（离散性）高的优先【离散度高原则】
• 宽度小的列优先【最少空间原则】
• 选择合适的索引列顺序

一条sql执行过长的时间，你如何优化，从哪些方面入手？

• 查看是否涉及多表和子查询，优化Sql结构，如去除冗余字段，是否可拆表等
• 优化索引结构，看是否可以适当添加索引
• 数量大的表，可以考虑进行分离/分表（如交易流水表）
• 数据库主从分离，读写分离
• explain分析sql语句，查看执行计划，优化sql
• 查看mysql执行日志，分析是否有其他方面的问题

Blob和text有什么区别？

Blob用于存储二进制数据，而Text用于存储大字符串。
Blob值被视为二进制字符串（字节字符串）,它们没有字符集，并且排序和比较基于列值中的字节的数值。
text值被视为非二进制字符串（字符字符串）。它们有一个字符集，并根据字符集的排序规则对值进行排序和比较。

MySQL里记录货币用什么字段类型比较好？

货币在数据库中MySQL常用Decimal和Numric类型表示，这两种类型被MySQL实现为同样的类型。他们被用于保存与金钱有关的数据。
DECIMAL和NUMERIC值作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度。

如何使用普通锁保证一致性？

• 操作数据前加锁，实施互斥，不允许其他的并发任务操作。

• 操作完成后释放锁，让其他任务执行，来保证一致性。

普通锁存在什么问题？

简单的锁住太过粗暴，连“读任务”也无法并行，任务执行过程本质上是串行的。

InnoDB有哪几种锁？

• 共享/排它锁(Shared and Exclusive Locks)：

  • 共享锁（Share Locks，记为S锁），读取数据时加S锁
  • 排他锁（eXclusive Locks，记为X锁），修改数据时加X锁

  共享锁之间不互斥，读读可以并行。

  排他锁与任何锁互斥，写读，写写都不可以并行。

  共享锁和排它锁存在读写互斥，对并发度有较大的影响。

• 意向锁(Intention Locks)：

  • 意向锁，是一个表级别的锁(table-level locking)。
  • 意向锁分为：
    • 意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁
    • 意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁
    • select … lock in share mode，要设置IS锁；select … for update，要设置IX锁；
  • 意向锁协议(intention locking protocol)：
    • 事务要获得某些行的S锁，必须先获得表的IS锁
    • 事务要获得某些行的X锁，必须先获得表的IX锁
  • 由于意向锁仅仅表明意向，它是比较弱的锁，意向锁之间并不相互互斥，其兼容互斥表如下：

  	IS	IX
  IS	兼容	兼容
  IX	兼容	兼容

  • 意向锁会与共享锁/排它锁互斥，其兼容互斥表如下：

  	S	X
  IS	兼容	互斥
  IX	互斥	互斥

  InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，实际应用中，InnoDB使用的是意向锁。

意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

意向锁解决什么问题？

如果事务 A 获取了某一行的排它锁，事务 B 想要获取表的锁（共享或者排他锁）。因为共享锁与排它锁互斥，所以事务 B 在试图对表锁的时候，必须保证：

• 当前没有其他事务持有表的排它锁。
• 当前没有其他事务持有表中任意一行的排它锁 。

为了检测是否满足第二个条件，事务 B 必须去检测表中的每一行是否存在排它锁。这是一个效率很差的做法，但是有了意向锁之后，事务A持有了表的意向排它锁，就可得知事务A必然持有该表中某些数据行的排它锁，而无需去检测表中每一行是否存在排它锁。

Hash索引和B+树区别是什么？你在设计索引是怎么抉择的？

B+树可以进行范围查询，Hash索引不能。
B+树支持联合索引的最左侧原则，Hash索引不支持。
B+树支持order by排序，Hash索引不支持。
Hash索引在等值查询上比B+树效率更高。
B+树使用like 进行模糊查询的时候，like后面（比如%开头）的话可以起到优化的作用，Hash索引根本无法进行模糊查询。

mysql 的内连接、左连接、右连接有什么区别？

Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集。
left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

什么是内连接、外连接、交叉连接、笛卡尔积呢？

内连接（inner join）：取得两张表中满足存在连接匹配关系的记录。
外连接（outer join）：取得两张表中满足存在连接匹配关系的记录，以及某张表（或两张表）中不满足匹配关系的记录。
交叉连接（cross join）：显示两张表所有记录一一对应，没有匹配关系进行筛选，也被称为：笛卡尔积。

主从复制binlog格式有哪几种？有什么区别？

• STATEMENT：基于语句的日志记录，把所有写操作的sql语句写入 binlog （默认）

  • 优点：
    成熟的技术。
    更少的数据写入日志文件。当更新或删除影响许多行时，这将导致日志文件所需的存储空间大大减少。这也意味着从备份中获取和还原可以更快地完成。
    日志文件包含所有进行了任何更改的语句，因此它们可用于审核数据库。
  • 缺点：
    有很多函数不能复制，例如now()、random()、uuid()等

• ROW：基于行的日志记录，把每一行的改变写入binlog，假设一条sql语句影响100万行，从节点需要执行100万次，效率低。

  • 优点：可以复制所有更改，这是最安全的复制形式
  • 缺点：如果该SQL语句更改了许多行，则基于行的复制可能会向二进制日志中写入更多的数据。即使对于回滚的语句也是如此。这也意味着制作和还原备份可能需要更多时间。此外，二进制日志被锁定更长的时间以写入数据，这可能会导致并发问题。

• MIXED：混合模式，如果 sql 里有函数，自动切换到 ROW 模式，如果 sql 里没有会造成主从复制不一致的函数，那么就使用STATEMENT模式。（存在问题：解决不了系统变量问题，例如@@host name，主从的主机名不一致）

索引有哪些优缺点？

优点：

• 唯一索引可以保证数据库表中每一行的数据的唯一性

• 索引可以加快数据查询速度，减少查询时间

缺点：

• 创建索引和维护索引要耗费时间
• 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
• 以表中的数据进行增、删、改的时候，索引也要动态的维护。

索引有哪几种类型？

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
覆盖索引：查询列要被所建的索引覆盖，不必读取数据行
组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

创建索引的三种方式？

在执行CREATE TABLE时创建索引
使用ALTER TABLE命令添加索引
使用CREATE INDEX命令创建

百万级别或以上的数据，你是如何删除的？

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

组合索引是什么？为什么需要注意组合索引中的顺序？

组合索引，用户可以在多个列上建立索引,这种索引叫做组合索引。 因为InnoDB引擎中的索引策略的最左原则，所以需要注意组合索引中的顺序。

什么是死锁？怎么解决？

死锁：

• 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

• 死锁有四个必要条件：互斥条件，请求和保持条件，环路等待条件，不剥夺条件。

解决：

• 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。
• 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。
• 如果业务处理不好可以用分布式事务锁或者使用乐观锁。

MySQL锁介绍？

从并发的角度：

• 共享锁/排他锁

• 意象共享锁/意象排他锁

从一致性锁定读的角度：

• 行锁/表锁

• 间隙锁/临建锁

左前缀匹配规则？

当使用联合索引时，索引使用满足左前缀规则。

例子：当对字段A、B、C创建联合索引时，相当于创建了A索引，A、B索引和A、B、C三个索引。

• 顺序条件都会走索引：

  • where A = 'a'
  • where A = 'a' and B = 'b'
  • where A = 'a' and B = 'b' and C = 'c'

• 从哪儿缺失从哪儿停止走索引：

  • where A = 'a' and C = 'c' A走索引，C不走索引
  • where C = 'c' 不走索引
  • where B = 'b' and C = 'c' 不走索引

• 乱序条件会走索引：

  • where B = 'b' and A = 'a' and C = 'c' 由于优化器优化，依然会走A、B、C索引

• 模糊查询根据条件走索引：

  • where A like 'a%' 走index或者range索引
  • where A like '%a' 不走索引
  • where A like '%a%' 不走索引
  • where A = 'a' and b like 'b%' A走索引，b走范围或者index索引

• 范围查询走索引：

• where A = 'a' and B > 1 order by C A走索引，b走范围索引，C不走索引，会导致文件排序。此时可以优化成 A、C索引，这样能走A、C索引，避免文件排序。