【mysql】索引及常见面试题

常见面试题：

jdbc连接数据库的步骤：

1. 加载jdbc驱动程序：Class.forName(“com.mysql.jdbc.Driver”);
   2.提供jdbc连接的url
   3.创建数据库连接：DriverManager.getConnectin()
   4.创建一个statement
   5.执行sql语句
   6.处理返回结果
   7.关闭连接

聚簇索引、覆盖索引（查询内容在索引里面就有）不用回表查询，其他索引需要回表，innodb中只有主键索引是聚簇索引。

mysql事务隔离级别：

mvcc怎么解决幻读的：

innodb默认的事务隔离级别为可重复读，mysql5.7之后已经解决了幻读的问题，解决方式：
mvcc（多版本并发控制）：事务在操作行时会默认为行加一个tas_id作为版本控制，时间越往后id越大，在某个事务获取到行锁开始事务时会记录对应行的快照，并阻塞其他事务，读取的数据与其事务隔离开了，所以解决了幻读。

对于一个快照来说，它能够读到那些版本数据，要遵循以下规则：
(1)当前事务内的更新，可以读到；
(2)版本未提交，不能读到；
(3)版本已提交，但是却在快照创建后提交的，不能读到；
(4)版本已提交，且是在快照创建前提交的，可以读到.

mysql索引的长度限制：

innodb引擎的每个索引列长度限制为767字节（bytes），所有组成索引列的长度和不能大于3072字节
myisam引擎的每个索引列长度限制为1000字节，所有组成索引列的长度和不能大于1000字节

为什么innodb必须要有主键，主键为什么要选用自增的整型？

为了维护B+tree的索引结构，即使你没有创建主键，innodb会默认从字段中选择一列作为主键，无法选择时就默认创建一列rowid作为主键。
自增主键在tree中比对时效率更高，高于uuid的大小比对效率。

mysql索引：

索引是 加快数据查询的一种数据结构，能提高查询数据的效率

常见的索引模型

1.哈希表:
使用场景: 等值匹配、适用于NoSQL引擎
弊端: 范围查询很慢
2.数组:
使用场景: 等值查询、范围查询、一般用在静态存储引擎中（数据一般不会变更）
弊端: 插入数据较慢
3.搜索树:
查询和搜索的速度比较快、适应磁盘的访问模式、（减少单次磁盘访问IO 的次数）
B-tree：
（1）叶子节点具有相同的深度
（2）所有索引元素不重复
（3）节点中的数据索引从左到右递增排列
B+tree:
（1）非叶子节点不存储data，只存储索引，可以放更多索引
（2）叶子节点包含所有索引字段
（3）叶子节点使用指针连接，提高区间访问的性能

InnoDB引擎

1.InnoDB 引擎特点:
表根据主键顺序以索引的形式存放（索引组织表）、每一个索引在InnoDB中对应一棵树。

2.普通索引和主键索引的区别：
查询的时候 普通索引会回表（普通索引的叶子节点存放的是主键值、通过主键值要去重新扫描主键索引数查询数据）

3.为什么MySQL建议使用自增主键:
避免发生频繁的页分裂。递增插入，每次插入数据都是追加操作，不涉及挪动其他记录、从而避免页分裂代来的性能损失

页分裂：如果遇到不规则的插入数据、则会造成频繁的页分裂。不规则插入每次插入都会改变数之前的数据状态、从而导致页分裂。
页分裂弊端：大量移动数据，会严重影响插入数据的效率；页分裂会降低空间利用率。
使用业务信息作为主键，往往会占用更多的空间（普通索引的叶子节点也会存储主键信息）

4.重建索引：
重建普通索引
alter table T drop index k；
alter table T add index k；

重建主键索引
alter table T drop primary key；
alter table T add primary key(id)；（这句指令会使得上句指令直接无效。）

为什么要重建索引：
（1）索引可能因为删除和页分裂等原因，导致数据页的空洞，重建索引会使得数据按序插入、页面利用率更高、更省空间。
（2）重建普通索引是推荐的。可以节省空间
（3）无论是删除主键还是、添加主键都会重构整个表（alter table T engine=InnoDB代替）

5.覆盖索引：
当前需要查询的信息、直接可以通过当前索引树定位到完整数据、不需要回表。 直接减少了搜索树的次数，提示数据查询的性能。

6.最左前缀原则：
最左前缀可以是联合索引（多个索引组合在一起）的最左N个字段，也可以是字符串索引的最左M个字符。
联合索引的建立原则：
（1）如果调整顺序，可以少维护一个索引，那么这个顺序就要被优先考虑。
（2）空间原则

7.索引下推：
MySQL5.6 引入，索引遍历过程中、对索引中包含的字段先做判断、直接过滤掉不满足的条件，减少回表次数。

普通索引和唯一索引的选择

1.查询过程：

• 模拟查询语句：select id from T where k=5；（k有索引）通过B+树的树根、逐层搜索到叶子节点，（数据页内部可以认为通过二分法来定位记录）。
• InnoDB读取数据是以页为单位。当需要读取一条数据，会将整页数据加载到内存中（默认每页16kb）
• 唯一索引：查到第一个满足条件的记录、则直接返回。
• 普通索引：查到第一个满足的记录后，需要查找下一个记录，直到碰到一个不匹配当前索引值的数据才返回。
• 两种索引查询性能的差不微乎其微（查找的数据基本都会在当前页中、整形字段一页可以放近千个key）

2.更新过程：

• 当前更新数据目标页已经在内存中，则都是直接更新，普通索引和唯一索引基本没有区别
• 当需要更新一个数据时候，如果数据页在内存中则直接更新；如果不在内存中，则会被缓存到change buffer中，这样就不需要从磁盘中读取数据来直接更新了，下次访问这个数据页的时候，将数据页读入内存，然后执行change buffer中这个页相关的操作。保证数据逻辑正确性。
• 当前更新数据目标页不在内存中：
  • 普通索引，记录到change buffer中，语句执行结束。（记录redo log【包含更新语句、change buffer数据】）
  • 唯一索引，将相关数据页读入内存（涉及随机IO访问 - 成本很高），判断是否违背唯一性约束，执行并结束。

3.change buffer

• change buffer在内存中有拷贝、数据可以持久化
• change buffer中的操作应用到原数据页，得到最新的结果的过程称为merge。
• merge的时机：
  • 访问这个数据页
  • 后台线程定期
  • 数据库正常关闭（shutdown）
• 机制：将更新操作缓存在change buffer中，减少磁盘随机访问IO，且数据入内存需要占用buffer pool，这样还可以减少内存占用，提高内存利用率。

4.change buffer 使用场景：
（1） 只限于普通索引
（2） merge之前，change buffer中数据越多（减少随机访问的IO次数越多）收益就越大
（3） 适合业务模型：账单类、日志类的系统
（4）不适合在更新之后里面就要访问的数据（会提取触发merge操作）、这种IO次数不仅没减少，还增加了维护change buffer的代价
（5）适用写多读少的环境。

5.索引的选择：
（1） 普通索引、唯一索引在查询上来看基本没有区别
（2）主要考虑对更新性能的影响，建议尽量选择普通索引。
（3）如果更新后伴随着对这个记录的查询，那么应该关闭change buffer。其他情况下cahnge buffer都能提升性能。

6.change buffer 和 redo log
（1）redo log 减少了随机写IO的次数
（2） change buffer 减少了随机读IO的次数

索引优化器

1.SQL语句执行的快慢

• 扫描行数越少、访问磁盘次数越少、消耗CPU资源越少
• 是否使用临时表
• 是否排序等

2.MySQL如何统计扫描行数

• 是不准确的（都是估算的）

3.解决MySQL优化器索引选择异常

• force index 强制走我们所期望的索引
• 考虑修改SQL语句，引导MySQL使用我们期望的索引
• 某些场景，可以新建一个更合适的索引，供优化器做选择，或删除误用的索引。

binlog、undo log、redo log

binlog（逻辑日志）：
复制 或 主从同步用日志，记录update/insert/delete等语句。

redo log（物理日志 innodb才有）：
持久化用日志，数据库数据变更是先写到内存，在异步写到磁盘，在写入过程中同步记录redo log日志，通过redo log来进行持久化写入。

undo log：
undo log主要有两个作用：回滚和多版本控制(MVCC)

在数据修改的时候，不仅记录了redo log，还记录undo log，如果因为某些原因导致事务失败或回滚了，可以用undo log进行回滚
undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。
这也应该容易理解，毕竟回滚嘛，跟需要修改的操作相反就好，这样就能达到回滚的目的。因为支持回滚操作，所以我们就能保证：“一个事务包含多个操作，这些操作要么全部执行，要么全都不执行”。【原子性】
因为undo log存储着修改之前的数据，相当于一个前版本，MVCC实现的是读写不阻塞，读的时候只要返回前一个版本的数据就行了

redo log保证mysql事务的持久性， redo log和binlog保证事务的一致性，事务开始前写入redo log日志，事务提交前记录binlog日志，两个日志都写入成功时事务才提交成功。