索引

1. 索引是什么

1. 索引是数据库用于快速查询数据的一种数据结构。就好比字典的目录，使用索引能够对数据进行排序，能够快速定位数据的位置。索引的数据结构有B+树和hash。索引一般是存放在磁盘，需要占用磁盘空间。如果没有特别说明，一般都是使用B+树索引

2. 索引的优缺点

1. 索引的优缺点
   1. 优：加快查询，B+树结构，减少磁盘IO次数，能够对数据进行排序
   2. 缺：索引会占用磁盘空间，更新索引字段开销大

3. 索引的数据结构

1. hash索引：HashMap结构，key存放索引，value存放数据
2. B+树索引：分为聚簇索引和非聚簇索引
   1. innodb使用聚簇索引，聚簇索引默认是主键，将数据和索引存放在一起。由两棵B+树组成，分别是主键索引和二级索引，如果是主键查询，直接在主键索引的叶子节点处找到数据。如果是非主键查询，先在二级索引的叶子节点找到辅助键对应的主键，然后回表到主键索引的叶子节点处找到主键对应的数据。
   2. mysiam使用非聚簇索引，非聚簇索引将数据和索引分开存放，主键和非主键查询没有区别，都需要在叶子节点处找到索引对应的数据所在的地址，然后去到对应的地址中读取数据
3. 聚簇索引优缺点
   1. 优：将数据和索引放在一起，查询效率高
   2. 缺：如果需要回表查询，效率变低。更新主键的同时需要更新对应的数据，开销大
4. 非聚簇索引优缺点
   1. 优：将数据和索引分开存放，占用的空间小
   2. 缺：需要去到磁盘中进行读取，查询效率低

4.功能索引

1. 根据功能来分
   1. primary主键，主键不能为null，一张表只能有一个主键
   2. unique唯一，唯一索引可以为null，一张表可以存在多个唯一索引，当没有设置主键时，非空唯一索引可以作为主键
   3. index普通索引
   4. fulltext全文索引，只能用于char和varchar，varchar的长度是可变的，char是不可变的

5. 联合索引、回表查询、覆盖索引、索引下推

1. 联合索引：多个字段组合而成一个索引，叫联合索引，使用联合索引可以减少回表次数，减少索引空间开销
2. 回表查询：针对聚簇索引，先在二级索引的叶子节点找到辅助键对应的主键，然后回表到主键索引查找主键对应的数据。查询了两棵B+树
   1. 比如有主键id，普通索引name。sql语句为：select * from test where name = "tom"。这个语句需要先在二级索引的叶子节点找到name="tom"的辅助键，得到这个辅助键对应的主键id，然后回表到主键索引中查找主键对应的数据
3. 覆盖索引：针对聚簇索引，直接在二级索引的叶子节点中直接得到数据，叫覆盖索引。只查询了一棵B+树
   1. sql语句为：select id,name from test where name="tom"。这个语句可以在二级索引的叶子节点找到name="tom"和它的id值，不需要回表到主键索引
4. 索引下推：用于between、%、>、<，先过滤掉不符合条件的数据，回表到符合条件的数据中查找，减少回表次数

6. 什么时候使用用索引，什么时候不用索引

1. 用：主键、经常查询、排序、数据量大
2. 不用：经常增删、数据量小、大量重复

7. 索引失效情况

1. where条件有or，前后两个字段不全是索引字段，索引失效
2. like以%开头
3. mysql自行优化，不使用索引
4. 不符合最右匹配原则，mysql优化字段位置后，向右匹配，直到遇到between、like、>、<，之后的字段不能使用索引

8. 唯一索引和主键索引

1. 唯一索引可以为null，一张表可以有多个唯一索引
2. 主键不能为null，一张表只能有一个主键
3. 当没有设置主键时，非空唯一索引可以作为主键，如果都没有，innodb引擎会创建一个6字节的指针作为主键

7. 索引为什么要使用B+树作为数据结构

1. 二叉树和BST
   1. 节点存一个值，树高度高，磁盘IO次数多，效率低
   2. 容易形成单链表形式，如果是单链表结构，查询时间复杂度On，如果是二叉树结构，查询时间复杂度Ologn
2. AVL和红黑树
   1. 节点存一个值，树高度高，磁盘IO次数多，效率低
   2. 虽然能够保持二叉树的结构，查询时间复杂度为Ologn，但是增删改需要进行旋转来保持平衡，旋转操作开销大
3. B树
   1. 节点存多个值(M阶高度存M-1个值)，树高度降低，磁盘IO次数变少，效率提高
   2. 查询不稳定，数据可能在任意一层。
4. B+树
   1. 节点存多个值(M阶高度存M个值)，树高度降低，磁盘IO次数变少，效率提高
   2. 叶子节点存数据，非叶子节点存索引，叶子节点数据按照升序排序
   3. 查询效率稳定，数据只在叶子节点

MySQL日志

1. MySQL日志系统：redo log、undo log、bin log

1. redo log：重做日志，实现事务的持久性。记录事务的更新操作，不管事务是否提交都会记录，记录时先将数据写入内存的日志缓冲区，空闲时同步到磁盘
2. undo log：回滚日志，实现事务的原子性。保存事务的快照版本，记录时先将数据写入内存的日志缓冲区，空闲时同步到磁盘
3. bin log：二进制日志，记录MySQL的更新操作，有三种模式
   1. statement：记录sql语句
   2. row：记录数据行变化
   3. mixed：mysql自行决定使用statement还是row
4. redo log和bin log的区别
   1. 记录的范围不同：redo log记录事务的更新操作，bin log记录mysql的更新操作
   2. 记录的内容不同：redo log记录数据页的变化，bin log记录sql语句或者数据行的变化
   3. 记录的时间不同：redo log在事务期间一直有数据写入，bin log只在事务提交之前写入一次

MySQL事务

1. MySQL事务

1. A原子性：通过undo log实现，事务要么全部执行要么全部不执行，事务出错，回滚事务
2. C一致性：通过redo log实现，事务开始到事务结束，数据库从一个一致性的状态转换到另一个一致性的状态，不存在中间状态。比如转账操作
3. I隔离性：通过MVCC+lock锁实现，针对并发事务，事务内部对外部不见，外部无法影响当前事务内部
4. D持久性：通过undo log和redo log实现，事务对数据库的修改会永久保存在数据库

2. MySQL的事务隔离级别

1. read uncommitted读未提交，事务可以读到其他未提交事务修改的数据，会出现脏读、不可重复读和幻读
2. read committed读已提交，事务只能读到其他提交事务修改后的数据，会出现不可重复读和幻读
3. repeatable read可重复读，mysql的默认隔离级别，事务对于同一笔数据的多次读取结果保持一致，会出现幻读
4. serializable序列化，事务有序执行，不存在幻读

3. 隔离级别存在的问题：脏读、不可重复读、幻读

1. 脏读：读到未提交事务的脏数据，比如回滚事务的数据
2. 不可重复读：事务A的两次读操作之间插入了事务B的修改操作，前后读取结果不一致
3. 幻读：事务A的两次范围读之间插入了事务B的增删操作，前后结果量不一致

4. MySQL是如何解决幻读的

1. repeatable read隔离级别：MVCC + next key lock
   1. 快照读：普通的select语句。MVCC多版本并发控制，更新操作会创建快照，查询只能查询比当前版本旧的快照。事务只能读取自己创建的快照，这样就能排除其他对当前事务的干扰。但是不能完美解决幻读问题，如果当前事务对其他事务插入的数据进行了update，那么就会创建一个快照，下一个select时就能查询得到插入的数据，发生幻读
   2. 当前读：select for update。通过next key lock。next key lock是gap lock + records lock的结合。对当前行和指定的区间加锁进行锁定，其他事务对于该区间的操作会被阻塞
2. serializable序列化，有序执行，不存在幻读

MySQL引擎

1. innodb和mysiam区别

1. innodb支持事务，mysiam不支持事务
2. innodb有MVCC，mysiam没有
3. innodb使用聚簇索引，mysiam使用非聚簇索引
4. innodb使用行锁，mysiam使用表锁

MySQL其他

1. MySQL锁：锁粒度、读写锁、三种行锁

1. 锁粒度
   1. 行锁：加锁频繁、加锁快、粒度小、并发高、冲突小
   2. 表锁：加锁不频繁、加锁慢、粒度大、并发低、冲突高
2. 读写锁
   1. 共享锁S：多个事务可以同时读取，但是不能同时写
   2. 排他锁X：只能单个事务进行读写，会阻塞其他的读写锁
   3. 写锁优先级比读锁高
3. 行锁的三种实现
   1. records lock记录锁，只能作用在索引行
   2. gap lock间隙锁，作用在区间但是不包含自身行
   3. next key lock：gap lock + records lock。对自身行和指定区间加锁。如果字段是唯一索引，退化为records lock，如果是Read committed隔离级别，使用Records lock

2. explain查询：type、key、possible_key、rows、extra

1. Type字段
   1. All：全表搜索，没有使用索引，需要优化
   2. Index：使用了全部索引，也需要优化
   3. Range：使用了部分索引，一般出现在>、<这种范围查询
   4. ref：索引符合最右匹配原则
2. Key字段：本次查询使用了那个索引
3. Possible key字段：可能使用的索引
4. Rows字段：搜索的数据行数，越小越好
5. Extra字段
   1. Using index：使用了全部索引
   2. Using where：使用了where关键字来过滤数据
   3. Using index condition：索引下推，5.6之后新增，用于>、<、between、%，过滤掉不符合条件的数据，使用where条件查询符合过滤之后的数据

3. #和$区别

1. 是占位符，会对SQL语句进行预编译，将传入的数据当成一个字符串，自动添加双引号，可以防止sql注入攻击

2. $是拼接符，直接将传入的数据拼接到sql语句，容易被sql注入攻击

4. 三大范式和反范式

1. 第一范式：表的字段必须是不可再分
2. 第二范式：一张表不能有两种依赖，如用户信息和角色信息不能放在一张表中，需要分开存放
3. 第三范式：创建一个中间表来关联两张表，通过主键来关联两张表
4. 反范式：在中间表中添加冗余字段来减少回表次数

5. SQL语句的执行流程大致是怎样的

1. 连接器：进行身份认证，登陆到mysql
2. 如果8.0之前，查询缓存
3. 分析器：检查sql语句是否存在语法错误，分析sql语句要做什么
4. 优化器：mysql选择最优解
5. 执行器：执行sql

6. where和having、执行顺序

1. where用于分组前，从from查询得到的数据中筛选符合条件的。where可以用于select、update、delete
2. having用于分组后，从group by分组后的数据中筛选符合条件的。having只能用于select
3. 执行顺序：from、where、group by、having、聚合函数(max,min,avg,sum,count)、select、order by

7. 左连接、右连接、内连接

1. left join：左表为主表，结果显示左表字段，右表中没有的左表信息，用null填充
2. right join：右表为主表，结果显示右表字段，左表中没有的右表信息，用null填充
3. inner join：结果显示双方共有的

8. delete、drop、truncate

1. delete可以删除部分也可以删除全表，只删除数据不改变表结构
2. truncate清空全表，只删除数据不改变表结构
3. drop会删除表结构
4. 速度：drop、truncated、delete