mysql知识点更新

什么是预编译

我们通常在JDBC中使用PreparedStatement来实现预编译的功能。当客户端发送一条sql语句给服务端后，服务器总要检查sql语句是否正确，然后把sql语句编译成可执行的函数，最后才执行sql语句。其中检验语法和编译所花的时间可能比执行sql语句花的时间都要多。如果我们需要执行多次insert语句，但每次插入的值不同，mysql服务器也需要每次都去检查sql语句的语法格式以及编译，这样就大大浪费了时间。使用预编译功能，只对sql语句进行一次语法校验和编译，效率会很高。

什么是sql注入

将恶意代码插入到sql语句中并使其执行。

如何防止sql注入

• 普通用户与系统管理员的权限要有严格的区分：即使sql语句中有恶意的代码，但因为权限的限制代码也无法执行
• 使用参数化语句：用户输入的变量不是直接嵌入到sql语句中，而是通过参数来传递这个变量。可以通过preparedStatement来防止sql注入，因为preparedStatement中可以不包含数据，只包含操作，不需要数据来拼接sql
• 加强对用户输入的验证

索引失效的原因总结

• 条件中用or，即使其中有条件带索引也不会使用索引查询(用in吧)；使用or，又想使索引生效，只能将or条件中的每一列都加上索引
• 对单字段建立了索引，where条件多字段
• 建立联合索引(多个字段组合而成的索引)，where条件单字段，与上面的情况正好相反，但这里需要注意的是当这个单字段是索引的第一个字段时索引是有效的(面试的时候被问到再这样回答)
• like模糊查询以%开头，索引失效
• 如果类型是字符串，那一定要在条件中将数据使用引号引起来，否则不会使用索引
• 如果mysql预计全表扫描比使用索引要快，则不会使用索引

mysql中的锁

• 表级锁：开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
• 行级锁：开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
• 页面锁：开销和加锁时间介于表锁和行锁之间，会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

mysql中B+树索引与Hash索引的区别

B+树是一个平衡的多叉树，B+树从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动。InnoDB引擎使用的B+树索引
Hash索引采用Hash算法，把键值转换成哈希值，不需要逐级查找，只需要一次哈希计算就可以定位到位置。MEMORY引擎使用的是Hash索引

两者的区别

• 哈希索引在等值查询上有绝对的优势
• 哈希索引不支持范围查询
• 哈希索引不支持索引完成排序
• 哈希索引不支持联合索引的最左前缀匹配规则

那什么是最左前缀原则呢？在创建多列索引时，根据业务需求，where字句中使用最频繁的一列放在最左边，例如当创建(a,b,c)复合索引时，想要索引生效的话，只能使用a和a,b和a,b,c三种组合。

哪些情况索引会失效

• 如果条件中有or，即使其中有条件带索引也不会使用
• 对于多列索引，不满足最左前缀匹配原则的话索引会失效
• like查询是以%开头
• 如果列类型是字符串，那一定要在条件中将数据使用引号引起来，否则不会使用索引
• 在索引列上进行计算或使用函数索引将失效
• where子句中使用参数会导致全表扫描

B树和B+树

m阶B树：每个节点至多拥有m个子节点
B树特点：

• 任意非叶子节点最多有M个儿子，M>2
• 根节点儿子个数为[2,M]
• 除根节点以外非叶子节点儿子个数为[M/2,M]
• 每个节点的关键字个数为[M/2-1,M-1]
• 非叶子节点的关键字个数=指向儿子的指针个数-1
• 非叶子节点的关键字：k[1],k[2],.....k[M-1]，且k[i]<k[i+1]
• 非叶子节点的指针：p[1],p[2],....p[M]，其中p[1]指向关键字小于k[1]的
• 所以叶子节点位于同一层

B+树相对于B树做的改进：

• 非叶子节点指针个数和关键字个数相同
• 非叶子节点的指针p[i]，指向关键字值属于(k[i],k[i+1])的子树
• 为所有叶子节点增加一个链指针
• 所有关键字都在叶子节点出现

B+树相对于B树的好处：

• 性能稳定：B+树数据只存在于叶子结点，查询性能稳定
• 查询简单：B+树不需要中序遍历树，只要遍历链表即可

B+树的使用场景：

• 数据库索引
• 文件索引系统

Mysql的内连接，左连接，右链接，外链接

• 内连接：返回两个表的交集
• 左连接：返回左表中的所有记录以及右表中符合条件的记录
• 右链接：返回右表中的所有记录以及左表中符合条件的记录

联合索引的最左匹配原则

简单的来说就是从联合索引的最左边开始匹配
假设我们在一个user表中对username,password,age三个字段建立了联合索引
下面两个sql语句会使用到联合索引：

select * from user where username="xxx" and password="xxx" and age="xxx";
select * from user where username="xxx";

而下面的语句则不会使用联合索引：

select * from user where password="xxx" and age="xxx";

为什么说使用联合索引比使用多个单列索引效率高

还使用上面的例子，假设对username，password和age分别建立了索引，但是在一条语句中使用多个单列索引的话，Mysql只会选择一个选择性最高的字段作为索引，另外的索引则不会使用
使用联合索引的话则会根据三个索引唯一的确定一条记录

MVCC

MVCC：多版本并发控制；在多线程过程中保证事务的隔离，降低锁竞争的压力；每开启一个事务，会生成一个唯一的递增的版本号，被操作的数据会生成一条新的临时数据行，新版本数据在提交前对其他事务是不可见的，保证了每个事务的操作都是互不影响的；事务提交时比较版本号，比较成功则更新数据