索引结构

image.png
免费开源版本5.7(最稳定)
8.0面向企业收费,性能提升,Oracle优化

Mysql8.0新特性

MySQL从5.7一跃直接到8.0，这其中的缘由，咱就不关心那么多了，有兴趣的朋友自行百度，本次的版本更新，在功能上主要有以下6点：

  1. 账户与安全
  2. 优化器索引
  3. 通用表表达式
  4. 窗口函数
  5. InnoDB 增强
  6. JSON 增强

1、索引分类

索引是帮助MYSQL高效获取数据的数据结构(有序)
1.普通索引2.唯一索引3.主键索引4.组合索引5.全文索引
Mysql目前有一下四种索引:

  1. BTREE索引: 最常见的索引类型,大部分索引都支持B树索引
  2. HASH索引: 只有Memory引擎支持,使用场景简单
  3. R-Tree索引(空间索引): 空间索引是MYISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少,不做特别介绍
  4. Full-text(全文索引): 全文索引也是MyISAM的一个特殊索引类型,主要用于全文索引,InnoDB从Mysql5.6版本开始支持全文索引

MyISAM , InnoDB , Memory三种存储引擎对各种索引类型的支持
image.png


2、BTree和B+Tree的区别

B+TREE和BTREE的区别:

  1. n叉B+TREE最多含有N个key , 而BTree最多含有n-1个key
  2. B+Tree的叶子节点保存所有的key的信息,依key大小顺序排列
  3. 所有的非叶子节点都可以看作是key的索引部分

由于B+TREE只有叶子节点保存key信息,查询任何key都要从root走到叶子,所以B+Tree的查询效率更加稳定


3、索引优缺点

优点:

  • 类似于书籍的目录索引 , 提高数据检索的效率 , 降低数据库的IO成本
  • 通过索引列对数据进行排序, 降低数据库排序的成本 , 降低CPU的消耗

缺点:

  • 实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录 , 所以索引列也是要占用空间的

  • 虽然索引大大提高了查询效率, 同时却也降低了更新表的速度 , 如对表进行insert , update , delete,因为更新表时 , mysql不仅要保存数据 , 还要保存一下索引文件每次更新添加了索引列的字段 , 都会调整因为更新所带来的键值变化后的索引信息


    4、存储引擎的区别 InnoDB和MyISAM

    MyISAM:

  • 不支持事务,但每次查询都是原子性的

  • 支持表级锁,即每次操作是对整个表加锁
  • 存储表的总行数
  • 一个MyISAM表有三个文件: 索引文件 , 表结构文件 , 数据文件
  • 采用非聚集索引 , 索引文件的数据域存储指向数据文件的指针 , 辅索引与主索引基本一致 , 但是辅索引不用保证唯一性

InnoDB:

  • 支持ACID的事务 , 支持事务的四种隔离级别
  • 支持行级锁以及外键约束 , 因此可以支持写并发
  • 不存储总行数
  • 一个InnoDB引擎存储在一个文件空间(共享表空间 , 表大小不受操作系统控制, 一个表可能分布在多个文件夹里),也有可能为多个(设置为独立表空 , 表大小受操作系统文件大小限制,一般为2G) , 受操作系统文件大小的限制
  • 主键索引采用聚集索引(索引的数据域存储数据文件本身),辅索引的数据域存储主键的值 , 因此从辅索引查找数据,需要先通过辅索引找到主键值 , 在访问主索引 , 最好使用自增主键 , 防止插入数据时 , 为维持B+树结构 , 文件大调整


5、SQL优化步骤

  • 怎么定位慢查询SQL
    • 可以通过以下两种方式定位:
    • 慢查询日志: 通过慢查询日志定位那些执行效率较低的SQL语句,用 log -slow - queriesf[= file_name] 选项启动时,mysql写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件
    • show processlist : 慢查询日志在查询结束以后才记录 , 所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题 , 可以使用show processlist命令查看当前mysql在进行的线程 , 包括线程的状态 , 是否锁表等 , 可以实时的查看SQL的执行情况 , 同时对一些锁表操作进行优化

image.png

  • 怎么分析SQL慢原因

使用explain关键字

  • 怎么解决
    • 首先查询慢的原因:
      • 查询速度慢的原因很多，常见如下几种：
        1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)
        2、I/O吞吐量小，形成了瓶颈效应。
        3、没有创建计算列导致查询不优化。
        4、内存不足
        5、网络速度慢
        6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）
        7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)
        8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
        9、返回了不必要的行和列
        10、查询语句不好，没有优化
    • 解决(优化)方案:
      • 1、 查询语句的词法、语法检查
        2、 将语句提交给DBMS的查询优化器
        3、 优化器做代数优化和存取路径的优化
        4、 由预编译模块生成查询规划
        5、 然后在合适的时间提交给系统处理执行
        6、 最后将执行结果返回给用户其次，看一下SQLSERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。

7、注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢。重复的记录在查询里是没有问题的
8、查询时不要返回不需要的行、列
9、如果使用了IN或者OR等时发现查询没有走索引，使用显示申明指定索引： Select * FROM PersonMember(INDEX = IX_Title) Where processid IN (‘男’，’女’)
10、没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION和UNION ALL一样的道理。

  • 验证结果

6、 explain分析执行计划（重点）

各个字段及其含义:

字段 含义
id select查询的序列号, 是一组数字 ,表示的是查询中执行select子句或者是操作表的顺序
select_type 表示select的类型 常见的取值有SIMPLE(简单表 , 即不使用表连接或者子查询) , PRIMARY(主查询 , 即外层的查询) , UNION(UNION中的第二个或者后面的查询语句) , SUBQUERY(子查询中的第一个SELECT)等
table 输出结果集的表
type 表示表的连接类型 , 性能由好到差的连接类型为(system -> const -> eq_ref -> ref-> ref_or_null-> index_merge -> index_subquery-> range-> index-> all)
possible_keys 表示查询时 , 可能使用的索引
key 表示实际使用的索引
key_len 索引字段的长度
rows 扫描行的数量
extra 执行情况的说明和描述
  • type 和 key 验证索引快慢的标准参考

7、常见的索引失效有哪些？ 怎么解决？

常见的索引失效:

  1. 违反最左前缀法则,索引失效
  2. 范围查询右边的列,不能使用索引
  3. 在索引列上进行运算操作 , 字段上使用函数 , 索引将失效
  4. 字符串不加单引号, 造成索引失效(数据类型出现隐式转化)
  5. 组合索引 , 不是使用第一列索引 , 索引失效
  6. or语句前后没有同时使用索引
  7. 以%开头的Like模糊查询,索引失效 ->解决: 使用覆盖索引
  8. isNull , isNotNull 有时索引失效
  9. not in 索引失效
  10. 在索引字段上使用not , <>, != 不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描 , 优化(key<>0 改为 key>0 或者key <0)
  11. 当全表扫描速度比索引速度快时 , mysql会使用全表扫描 , 此时索引失效

解决方案:

  1. 全值匹配 ,对索引中所有列都指定具体值
  2. 如果索引了多列 , 要遵守最左前缀法则 , 指的是查询从索引的最左前列开始 , 并且不跳过索引中的列
  3. 不要在索引列上进行运算操作
  4. 字符串加上单引号
  5. 尽量使用覆盖索引 , 避免select *
  6. 使用UNION关键字替代or条件
  7. 模糊查询使用覆盖索引来解决
  8. 如果mysql评估使用索引比全表更慢, 则不使用索引
  9. 使用in 不使用not in
  10. 尽量使用复合索引 , 少使用单列索引


    8、什么是覆盖索引

    通常开发人员会根据查询的where条件来创建合适的索引 , 但是优秀的索引设计应该考虑到整个查询 , 其实mysql可以使用索引来直接获取列的数据 . 如果索引的叶子节点包含了要查询的数据 , 那么就不用回表查询了 , 也就是说这种表引包含(亦称覆盖) 所有需要查询的字段的值 , 我们称这种索引为覆盖索引


    9、如何解决大表数据分页的问题？ 2亿数据如何查询最后一页的数据？

    在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
    image.png


10、优化排序和分组的问题

排序优化(order by):

  1. 第一种是通过对返回数据进行排序 , 也就是通常说的filesort排序 , 所有不是通过索引直接返回排序结果的排序都叫FileSort排序
  2. 第二种是通过有序索引顺序扫描直接返回有序数据 ,这种情况即为using index , 不需要额外排序,操作效率高
  3. 优化目标: 尽量减少额外的排序 , 通过索引直接返回有序数据 , where条件和order by 使用相同的索引 , 并且order by 的顺序和索引顺序相同 , 并且order by 的字段都是升序 ,或者都是降序, 否则肯定需要额外的操作,这样就会出现FileSort
  4. FileSort优化:
    1. 通过创建合适的索引 , 能够减少FileSort的出现 , 但是在某些情况下 ,条件限制 不能让FIleSort消失 , 那就需要加快FIleSort的排序操作
    2. Mysql有两种排序算法:
      1. 两次扫描算法: ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据 行指针回表读取记录，该操作可能会导致大量随机I/O操作。
      2. 一次扫描算法: 一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时 内存开销较大，但是排序效率比两次扫描算法要高。

分组优化(group by):

  • 如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。
    ```sql drop index idx_emp_age_salary on emp;

explain select age,count(*) from emp group by age;

  1. 优化后:
  2. ```sql
  3. explain select age,count(*) from emp group by age order by null;

从上面的例子可以看出，第一个SQL语句需要进行”filesort”，而第二个SQL由于order by null 不需要进行 “filesort”， 而上文提过Filesort往往非常耗费时间。

11. 聚簇索引和非聚簇索引

  • 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
  • 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

image.png
聚簇和非聚簇扩展