MySQL

set autocommit = 0；

1、索引

 1.1 索引概述

   索引是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这种数据结构通过某种方式指向数据。

   没有建立索引时，对数据查找是通过对表结构进行整表扫描；有索引后能通过索引快速查找数据

 1.2 优势劣势

   优势：

   1）提高数据库检索的效率，降低数据库的IO成本

   2）降低数据排序的成本

    劣势：

    1）消耗空间   2）降低更新表的速度，每次更新数据，索引也要随着更新

 1.3 BTree结构

   BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下

• 树中每个节点最多包含m个孩子
• 除根节点与叶子结点外，每个节点至少有【ceil(m/2)】个孩子
• 若根节点不是叶子节点，则至少有两个孩子
• 所有的叶子节点都在同一层
• 每个非叶子结点由n个key与n+1个指针组成，其中【ceil(m/2)-1】<=n<=m-1

  优点：B树和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，B树的层级结构比二叉树小，因此搜索速度快。

 1.4 B+Tree结构

   B树与B+树的区别：

    1）m叉B+树最多包含m个key，而B树最多包含m-1个key

    2）B+树的叶子结点保存所有的key信息，依key大小顺序排列。

    3）所有的非叶子节点都可以看做是key的索引部分

    

  优点：由于B+树只有叶子结点保存key信息，查询任何key都要从root走到叶子，所以B+树的查询效率更加稳定。

 1.5 MySQL中的B+Tree

   MySQL索引数据结构对经典的B+Tree进行了优化。在原有的B+Tree的基础上增加了一个指向相邻叶子结点的链表指针，提高了范围搜索能力以及区间访问性能。

2、SQL优化

创建索引：create  index  idx_name_email  on  tb_user(name,email);

 explain  select  ....  能查看细节

 2.1 大批量插入数据

  1）主键顺序插入

   InnoDB类型的表是按照主键的顺序保存的

  2）关闭唯一校验

   在导入数据前执行set unique_check=0，关闭唯一校验，在导入结束后执行 set unique_check=1，恢复唯一性校验。因为在导入数据的时候会验证这个数据在表中有没有

  3）手动提交事务

   set autocommit=0，关闭自动提交，导入结束后再执行set autocommit=1

 2.2 优化insert语句

  1）多条数据合并一条 

    insert  into test values(),(),(),();

  2）在事务中进行数据插入

     start  transaction；

     insert into test values(1,'tom');

     commit；

  3）数据有序插入

 2.3 优化order by语句 

  两种排序方式  using  filesort    ；   using  index

  对多字段排序时，尽量对复合索引的列进行排序查询，并且尽量都是升序或者降序。减少额外排序。

 2.4 优化group by语句

   group  by  同样会进行排序操作，只是比order  by多了分组操作，可以利用索引来排序，还可以关闭排序  order by null

 2.5 优化嵌套查询

   多表联查替代子查询

 2.6 优化OR条件

   建议使用union来替换OR

 2.7 优化分页查询

  优化一：在索引上完成排序分页，根据主键关联返回原表查询所需要列的内容

  优化二：适用于主键自增的表，可以把Limit查询转换某个位置查询 

 2.8 使用SQL提示

  指定使用或不适用某个索引来优化

use  index()

 ignore  index()

3、什么是脏读、不可重复读、幻读

 脏读：在事务的执行过程中，读到了其他事务未提交的数据

 不可重复读：在一个事务过程中，多次查询结果不一致（比如说另一个事务，修改了值，这个事务后面读到的结果就变了）

 幻读：在一个事务过程中，用同样的操作查询数据，得到的记录数不相同（比如说另一个事务插入了数据，本事务读到的就多了几条数据）

 处理方式：加锁、事务隔离、MVCC

 加锁：

  1、脏读：在修改时加排它锁，直到事务提交才释放，读取时加共享锁，读完释放锁

  2、不可重复读：读数据时加共享锁，写数据时加排它锁

  3、幻读：加范围锁

4、索引失效

 1）进行算术操作的时候

 2）使用is null 或者is not null时

 3）使用不等于的时候

 4）使用范围查询的时候

 5）字符串必须带引号

 6）like 操作时 余号不要放在最左边 ，非要放值的话可以用覆盖索引解决

 7）少用or

口诀：

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

5、锁

1）按对数据操作类型分：共享锁（读锁）、排它锁（写锁）

2）按粒度分：

 行锁（偏写）：操作事务的时候用到了行锁

 表锁（偏读）

 页锁（介于行锁和表锁之间，了解）

 

什么是间隙锁？

当我们用范围条件而不是相等条件检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙

锁定一行：select * from user where name='a' for update;

如何分析行锁定？

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

innodb_row_lock_current_watis：当前正在等待锁定的数量；

innodb_row_lock_time：从系统启动到现在锁定总时间的长度；