一、SQL注入的原理、预防？

原理

主要原因是对用户输入的绝对信任，相信用户所有的输入都是可信的，没有对用户输入的语句进行过滤或者筛选，直接放到sql语句中进行拼接，从而导致了sql注入的产生

预防

sql注入的本质是对用户输入的绝对信任，当我们对用户输入的数据不绝对信任的时候，就可以预防sql注入的问题了

  1. 输入数据长度的限制
  2. 关键字过滤：对每个参数的传递进行检测，对sql关键字如where、 select 、 insert 等，进行过滤，可以采用 正则 和 递归 过滤
  3. 对参数携带的特殊字符进行转义和过滤：大部分注入点都是在字符位置发生的，所以对每个参数进行闭合符号判断，如果参数携带的单引号没有闭合，会导致sql中一部分数据被视为字符串
  4. 预编译防注入：在数据库sql语句进行预编译之后，sql语句已经被数据库预编译化和优化了，并且运行数据库以参数化的形式进行查询，即使传递敏感字符也不会被执行，而是被当做参数处理

如果发生了sql注入，也不要直接显示错误原因，最好指定一个错误页面，尤其是sql注入，让不法分子盲注

二、MyISAM和InnoDB的区别

  • 5.5版本之前 ，MyISAM是MySQL的默认存储引擎，虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但是MyISAM不支持 事务 、 行级锁 ，最大的缺点就是 崩溃后无法安全恢复 。
  • 5.5版本之后 ，MySQL引入的InnoDB（事务性数据库引擎），且MySQL的默认存储引擎为InnoDB

注意： 大多数情况下，我们使用的都是InnoDB存储引擎，但是在某些情况下使用MyISAM也是合适的，比如 读密集 的情况下。（如果不介意MyISAM崩溃恢复问题的话）。

MyISAM和InnoDB两者对比

  1. 是否支持行级锁： MyISAM只有 表级锁 ；InnoDB支持 行级锁 、 表级锁 ，默认为行级锁
  2. 是否支持事务： MyISAM不支持事务；InnoDB提供事务支持，外部键等高级数据库功能。具有事务、回滚和崩溃修复能力。
  3. 崩溃后能否安全恢复： MyISAM崩溃后无法安全恢复，InnoDB可以。
  4. 是否支持外键： MyISAM不支持InnoDB支持。
  5. 是否支持MVCC: 仅InnoDB支持。

    三、MVCC

    3.1 什么是MVCC

    MVCC （Muti-Version Concurrency Control）：多版本并发控制；指的是“维持一个数据的多个版本，使得读写操作没有冲突”这么一个概念。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC在MySQL的InnoDB中的实现主要是为了提高数据库的并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突，也能做到不加锁，非阻塞并发读。

3.2 什么是当前读和快照读

  • 当前读

像 select lock in share mode （共享锁）、 select for update 、 update 、 insert 、 delete （排他锁）,这些操作都是一种当前读。之所以叫当前读，是因为它读取的记录是最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。悲观锁的实现。

  • 快照读

像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是 串行级别 ，串行级别下的快照读会退化成当前读；
快照读的目的是喂了提高并发性能，快照读的实现是基于多版本并发控制，即MVCC，可以认为MVCC是
行级锁的一个变种，它在很多情况下，避免了加锁操作，降低了开销，因为是基于多版本，所以快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

MVCC就是为了实现读-写冲突不加锁，而这个读指的是快照读，而非当前读，当前读实际上是一种加锁操作，这是悲观锁的实现。

3.3 当前读、快照读和MVCC的关系

快照读是MVCC理想模型的其中一个具体非阻塞读功能，相对而言，当前读就是悲观锁的具体实现。

3.4 MVCC能解决什么问题，好处是？

数据库的并发场景有三种，分别为：

  • 读-读：不存在任何问题，也不需要并发控制
  • 读-写：有线程安全问题，可能会造成事务隔离性为题，可能遇到脏读，幻读，不可重复读的情况

  • 写-写：有线程安全问题，可能会存在丢失修改的问题。

    MVCC带来的好处是？

    多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库快照。所以MVCC可以为数据解决以下问题。

  • 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作时也不用阻塞读操作，提高了数据库并发读写的性能

  • 同时还可以解决脏读、幻读、不可重复读的事务隔离性问题，但不能解决丢失修改的问题。

    使用MVCC可以形成两个组合

  • MVCC + 悲观锁：MVCC解决读-写冲突，悲观锁解决写-写冲突

  • MVCC + 乐观锁：MVCC解决读-写冲突，乐观锁解决写-写冲突

    3.5 MVCC的实现原理

    MVCC的目的就是实现多版本并发控制，在数据库中的实现就是为了解决读-写冲突，实现原理主要依赖记录中的： 3个隐式字段 、 Undo日志 、 Read View 来实现。

    3个隐式字段

    每行记录除了我们自己定义的字段外，还有数据库隐式定义的 DB_TRX_ID 、 DB_ROLL_PTR 、 DB_ROW_ID 等字段

  • DB_TRX_ID ：最近修改（修改/插入）事务ID：记录创建这条记录/最后一次修改这条记录的事务ID

  • DB_ROLL_PTR ：回滚指针，指向这条记录的上一个版本（存储在rollback segement里），用来配合undo日志，指向上一个旧版本。
  • DB_ROW_ID ：隐含的自增ID（隐藏主键），如果数据库表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

  • 实际还有一个删除 flag 隐藏字段，用来记录被更新或删除，并不是代表真的删除，而是删除的flag变了，就是逻辑删除。

    undo日志

  • insert undo log ：代表事务在 inset 记录时产生的undo log，只在事务回滚时需要，并且在事务提交之后可以被立即丢弃。

  • update undo log ：事务在进行update或delete时产生的undo log，不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清楚

    purge

    • 为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的delete_bit，并不是真正将过时记录删除
    • 为了节省磁盘空间，InnoDB有专门的purge线程来清理delete_bit为true的记录，为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

对MVCC有帮助的实质就是 update undo log ，undo log实际上就是存在rollback segement中的旧记录链。

ReadView（读视图）

什么是ReadView?

ReadView 主要是用来做可见性判断的，ReadView就是事务在进行快照读操作的时候生产的读视图，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护当前活跃事务的ID（当每个事务开启时，都会被分配一个ID，这个ID是递增的，所以新的事务，ID值越大）

当某个事物执行快照读的时候，对该记录创建一个ReadView读视图，把它必做条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新数据，也可能是该行记录的undo log里面某个版本的记录。

ReadView遵循一个可见性算法，主要是将要被修改的数据的最新记录中的 DB_TRX_ID （即当前事务ID）取出来，与系统中其他活跃的事务ID去对比，如果 DB_TRX_ID 跟ReadView的属性做了某些比较，不符合可见性，那就通过 DB_ROLL_PTR 回滚指针去取出Undo log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链表头部遍历到链表尾部，即从最近的一次修改查起），知道找到满足特定条件的DB_TRX_ID，那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最老新版本。

三个全局属性

  • trx_list ：用来维护生成时刻，系统正在活跃的事务ID
  • up_limit_id ：记录 trx_list 列表事务的最小ID
  • low_limit_id ：目前已出现过的事务ID的最大值 + 1

    比较过程

  1. 首先比较 DB_TRX_ID < up_limit_id ，如果小于，则当前事务能看到DB_TRX_ID所在的记录，否则就进入下一个判断
  2. 接下来判断 DB_TRX_ID 是否大于等于 low_limit_id ，如果大于等于则代表DB_TRX_ID所在的记录是在ReadView生成后才出现的，那对当前事务肯定是不可见的，如果小于就进入下一个判断。
  3. 判断 DB_TRX_ID 是否在 trx_list （活跃事务的ID列表）中，如果在，则代表ReadView生成的时刻，这个事务还在活跃，还没有Commit，所以当前事务对于该数据是不可见的；如果不在，则说明，你这个事务在ReadView生成之前就已经Commit了，那么这条数据的结果就是可见的。

    RR和RC级别下的InnoDB快照读有什么不同

  • RR ：Repeatable Read可重复读
  • RC ：Read Commited读已提交

关键点是 Read View 生成时机不同，从而造成RC、RR级别下的快照读的结果不同。在 RC 隔离级别下，每次快照读都会生成新的Read View，而在 RC 隔离级别下，同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View。

  • 在 RR 级别下的某个事务对某条记录的第一次快照读会创建一个快照以及Read View，将当前系统正在活跃的其他事务记录起来，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见。而早于Read View创建的事务所做的修改都是可见的。

  • 在 RC 级别下的事务中，每次快照读都会新生成一个快照和Read View，这就是在RC级别下的事务中，可以看到别的事务提交的原因。

    四、数据库索引

    4.1 为什么要使用索引？

  • 通过创建 唯一索引 ，可以保证数据库中的每一行数据的唯一性。

  • 可以大大加快数据的检索速度
  • 可以帮助服务器避免排序和构建临时表

  • 将随机IO变为顺序IO

    4.2 索引这么好，为什么不对每一个列创建一个索引

  • 会降低数据的维护速度，当对表中的数据进行增加、删除和修改的时候，索引也需要动态维护。

  • 索引需要占一定的物理空间，如果是建立聚簇索引，那么需要的空间会更大

  • 创建索引和维护索引需要消耗时间，这种时间随着数据量的增加而增加

    4.3 索引如何提高查询速度的

  • 将无序的数据变成相对有序的数据，就像查目录一样

    4.4 什么情况下构建索引

  • 在经常需要搜索的列上构建索引，可以加快搜索的速度

  • 在经常使用 where 子句上的列上面建索引，加快条件的判断速度
  • 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序时间。
  • 对中到大型表构建索引都是非常有效的，但是对特大型表构建索引的话，维护开销会很大，不适合建立索引。

  • 在经常用在连接的列上构建索引，这些列主要是一些外键，可以加快连接的速度。

    4.5 使用索引的注意事项

  • where 子句中对字段施加函数，会造成无法命中索引

  • 使用与业务无关的自增主键作为主键，即使用逻辑主键，不要使用业务主键
  • 将打算家索引的列设置为 NOT NULL ，否则将导致引擎放弃使用索引而进行全表扫描
  • 删除长期为使用的索引，不用的索引会造成不必要的性能损耗，MySQL 5.7可以通过查询sys库的 chema_unused_indexes 视图来查询哪些索引从未被使用

  • 在使用limit offset查询缓慢时，可以借助索引来提高性能。

    4.6 Mysql索引主要使用的两种数据结构

  • 哈希索引： 对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为带条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

  • BTree索引： Mysql的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎（MyISAM和InnoDB）的实现方式是不同的。

    4.7 InnoDB索引和MyISAM索引的区别

  • 主索引的区别： InnoDB的数据文件本身就是索引文件，而MyISAM的索引和数据是分开的

  • 辅助索引的区别： InnoDB的辅助索引的data域存储相应记录主键的值而不是地址，而MyISAM的辅助索引和主索引没有多大的区别。

    五、MyISAM和InnoDB实现BTree索引方式的区别

  • MyISAM ：B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这也被称为 非聚簇索引 。

  • InnoDB ：其数据文件本身就是索引文件，其数据文件本身就是按照B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录，索引的key是数据库表中的主键，因此InnoDB表数据文件本身就是主索引，也叫聚簇索引，而其余的索引都做为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址。根据主索引搜索时，直接找到key所在的节点即可以取出数据；根据辅助索引查找时，需要先找到主键的值，再根据该主键的值走一遍主索引。

正是因为InnoDB索引这样子的实现，所以：

  • 不建议使用过长的字段作为主键， 因为所有的辅助索引都是引用主索引，过长的主索引会令辅助索引变得过大。
  • 不建议使用非单调递增的字段作为主键在InnoDB中，因为InnoDB的数据文件本身就是一棵B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

四、聚簇索引和非聚簇索引