MySQL索引及其优化查询

MySQL-索引

索引优势：提高检索效率、降低数据库的IO成本、通过索引对数据进行排序、降低数据排序成本、降低CPU性能消耗

劣势：索引也是一张表、表中保存了主键与索引字段、并指向实体表的记录、索引也占内存空间

• 索引分类

• 单值索引
  • 一个索引只包含单个列、一个表有多个单列索引
• 唯一索引
  • 索引列的值必须唯一、但可以为null
• 复合索引
  • 一个索引包含多个列

• MyISAM和In noDB索引区别

• MyISAM
  • 不支持事务、每次查询都是原子性的
  • 支持表级锁、每次操作都是针对整张表
  • 存储表的总行数、一个MyISAM有3个文件(类结构文件、索引文件、数据文件)
• InnoDB
  • 支持ACID事务
  • 支持四种隔离级别（读未提交、读已提交、可重复读、序列化）
  • 支持行级锁、外键约束、写并发
  • 不仅缓存索引、还缓存数据

• 索引底层采用B+Tree：在所有叶子节点存储下一个节点的指针（真实数据只保存于叶子节点中）、非叶子节点不保存数据、只存储指引搜索方向的数据项。

• 哪些情况不需要建立索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该简历索引
• 查询中与其他表关联的字段（通过外键建立索引）

• 事务的特性

• 一致性
• 原子性
• 隔离性
• 持久性

• SQL优化

• SQL语句及其索引优化
• 数据库表结构优化
• 系统配置优化、硬件优化

• 分库分表策略

• 按照权重进行分配
• 按照业务进行分配
• 按照一致性哈希算法进行分配

SQL优化

查看执行计划：explan+sql语句

1. 开启慢查询日志、设置阈值、将超过阈值的sql抓取出来
2. 使用explan+sql语句进行分析
3. 查看id、type、key、rows、Extra
4. Show profile（查询sql在mysql服务器里面执行的细节和声明周期情况）
5. 然后进行参数调优

• id：select 查询序号包含一组数字、表示查询中执行select子句操作表的顺序

• id相同执行顺讯由上到下
• Id不同、如果是子查询，id序号会自增，id值越大，优先级越高，越先被执行

• type:查询显示的类型：system>const>eq_ref>ref>range>index>all

• system:表示只有一行记录
• Const：通过索引一次就可以查询到
• eq_ref：唯一索引扫描、对于每一个索引键、都只有一条记录与其匹配、常见于主键或唯一键索引
• Ref：非唯一索引扫描、返回匹配某个值的所有行
• Range：只检查给定范围的行、使用一个索引来进行选择
• Index：遍历索引树
• All：遍历全表查找匹配的行

• Key：实际使用的索引、如果为null则没有使用索引

• Rows：每张表有多少行被优化查询

• 如何避免索引失效？

• 最佳左前缀原则
  • 如果索引了多列，要遵循最佳左前缀原则，并且中间的索引列不能断
• 不在索引列做任何操作（计算、函数会导致索引失效）
• 使用覆盖引擎（按需获取数据、避免使用Select *）
• 范围之后全失效
• 不使用!= 、is null、is not null
• ☆☆☆ Like ☆☆☆ 通配符%在前面会进行全表查询-会导致索引失效
• 字符串不加单引号
• 少用or

既然我们要通过索引来快速查找内容，那么如何设计索引就是我们的重点内容，因为索引是存储在硬盘上的，跟我们之前使用的HashMap之类的不同，它们都是在内存中的，但是硬盘的读取速度远小于内存的速度，每一次IO操作都会耗费大量的时间，我们也不可能把整个磁盘上的索引全部导入内存，因此我们需要考虑尽可能多的减少IO次数，索引的实现可以依靠两种数据结构，一种是我们在JavaSE阶段已经学习过的Hash表，还有一种就是B-Tree。

通过对Key进行散列值计算，我们可以直接得到对应数据的存放位置，它的查询效率能够达到O(1)，但是它也存在一定的缺陷：

• Hash索引仅仅能满足“=”，“in”查询条件，不能使用范围查询。
• Hash碰撞问题。
• 不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。
  那么，既然要解决这些问题，我们还有一种方案就是使用类似于二叉树那样的数据结构来存储索引，但是这样相比使用Hash索引，会牺牲一定的读取速度。
  但是这里并没有使用二叉树，而是使用了BTree，它是专门为磁盘数据读取设计的一种度为n的查找树：
• 树中每个结点最多含有m个孩子（m >= 2）
• 除根结点和叶子结点外，其它每个结点至少有[ceil(m / 2)]个孩子。
• 若根结点不是叶子结点，则至少有2个孩子。
• 所有叶子结点都出现在同一层。
• 每个非终端结点中包含有n个键值信息： (P1，K1，P2，K2，P3，…，Kn，Pn+1)。其中：
  1. Ki (i=1…n)为键值，且键值按顺序升序排序K(i-1)< Ki。
  2. Pi为指向子树根的结点，且指针P(i)指向的子树中所有结点的键值均小于Ki，但都大于K(i-1)。
  3. 键值的个数n必须满足： [ceil(m / 2)-1] <= n <= m-1。
     比如现在我们要对键值为10的记录进行查找，过程如下：

1. 读取根节点数据（目前进行了一次IO操作）
2. 根据根节点数据进行判断得到10<17，因为P1指向的子树中所有值都是小于17的，所以这时我们将P1指向的节点读取（目前进行了两次IO操作）
3. 再次进行判断，得到8<10<12，因为P2指向的子树中所有的值都是小于12大于8的，所以这时读取P2指向的节点（目前进行了三次IO操作）
4. 成功找到。
   我们接着来看，虽然BTree能够很好地利用二叉查找树的思想大幅度减少查找次数，但是它的查找效率还是很低，因此它的优化版本B+Tree诞生了，它拥有更稳定的查询效率和更低的IO读取次数：
   我们可以发现，它和BTree有一定的区别：

• 有n棵子树的节点中含有n个键值，BTree只有n-1个。
• 所有的键值信息只在叶子节点中包含，非叶子节点仅仅保存子节点的最小（或最大）值，和指向叶子节点的指针，这样相比BTree每一个节点在硬盘中存放了更少的内容（没有键值信息了）
• 所有叶子节点都有一个根据大小顺序指向下一个叶子节点的指针Q，本质上数据就是一个链表。
  这样，读取IO的时间相比BTree就减少了很多，并且查询任何键值信息都需要完整地走到叶子节点，保证了查询的IO读取次数一致。因此MySQL默认选择B+Tree作为索引的存储数据结构。

InnoDB与MyISAM实现的不同之处：

• 数据本身就是索引的一部分（所以这里建议主键使用自增）
• 非主键索引的数据实际上存储的是对应记录的主键值（因此InnoDB必须有主键，若没有也会自动查找替代）

锁机制-隔离级别
在JavaSE的学习中，我们在多线程板块首次用到了锁机制，当我们对某个方法或是某个代码块加锁后，除非锁的持有者释放当前的锁，否则其他线程无法进入此方法或是代码块，我们可以利用锁机制来保证多线程之间的安全性。
在MySQL中，就很容易出现多线程同时操作表中数据的情况，如果要避免潜在的并发问题，那么我们可以使用之前讲解的事务隔离级别来处理，而事务隔离中利用了锁机制。

• 读未提交(Read Uncommitted)：能够读取到其他事务中未提交的内容，存在脏读问题。
• 读已提交(Read Committed RC)：只能读取其他事务已经提交的内容，存在不可重复读问题。
• 可重复读(Repeated Read RR)：在读取某行后不允许其他事务操作此行，直到事务结束，但是依然存在幻读问题 mysql-innodb默认可重复读。
• 串行读(Serializable)：一个事务的开始必须等待另一个事务的完成。

读锁和写锁
从对数据的操作类型上来说，锁分为读锁和写锁：

• 读锁：也叫共享锁，当一个事务添加了读锁后，其他的事务也可以添加读锁或是读取数据，但是不能进行写操作，只能等到所有的读锁全部释放。
• 写锁：也叫排他锁，当一个事务添加了写锁后，其他事务不能读不能写也不能添加任何锁，只能等待当前事务释放锁。
  全局锁、表锁和行锁
  从锁的作用范围上划分，分为全局锁、表锁和行锁：
• 全局锁：锁作用于全局，整个数据库的所有操作全部受到锁限制。
• 表锁：锁作用于整个表，所有对表的操作都会收到锁限制。
• 行锁：锁作用于表中的某一行，只会通过锁限制对某一行的操作（仅InnoDB支持）