Mysql-Mysql高级-索引优化介绍（1）

索引优化介绍

性能下降SQL慢,执行时间长,等待时间长

• 原因
  • 查询语句写的不好
  • 索引失效
    • 单值索引
      • 创建索引语句 create index idx_student_name on student(name);
    • 复合索引
      • 创建索引语句 create index idx_student_name_id on student(name,id);
  • 关联查询太多join（设计缺陷或不得已的需求）
  • 服务器调优及各个参数设置（缓冲、线程数等）
• 新
  • 数据过多–分表分库
  • 关联了太多的表，太多join — SQL优化
  • 没有充分利用到索引 — 索引建立
  • 服务器调优各个参数设置 – 调整my.cnf

常见通用的Join查询

• SQL执行顺序

  • SQL本身

    • SELECT DISTINCT
      	<select_list>
      FROM
      	<left_table> <join_type>
      JOIN <right_table> ON <join_condition>
      WHERE
      	<where_condition>
      GROUP BY
      	<group_by_list>
      HAVING
      	<having_condition>
      ORDER BY
      	<order_by_condition>
      LIMIT <limit_number>
      

  • 机读顺序

    • FROM <left_table>
      ON <join_condition>
      <join_type> JOIN <right_table>
      WHERE <where_condition>
      GROUP BY <group_by_list>
      HAVING <having_condition>
      SELECT
      DISTINCT <select_list>
      ORDER BY <order_by_condition>
      LIMIT <limit_number>
      

    • 解释

      • FROM #对FROM的左边的表和右边的表计算笛卡尔积，产生虚拟表VT1。
      • ON #对虚拟表VT1进行ON筛选，只有符合条件的行才会被记录在虚拟表VT2中。
      • JOIN #如果指定了外连接（比如 left join，right join），那么保留在表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3。
      • WHERE #虚拟表VT3进行WHERE条件过滤，只有符合条件的行才会被插入到虚拟表VT4中。
      • GROUP BY #根据group by子句中的列，对VT4中的记录进行分组操作，产生虚拟表VT5.
      • AVG，SUM… #对虚拟表VT5进行AVG或者SUM操作，产生虚拟表VT6。
      • HAVING #对虚拟表VT6应用having过滤，只有符合条件的记录才会被插入虚拟表VT7中。
      • SELECT #select操作，选择指定的列，插入到虚拟表VT8中。
      • DISTINCT #对VT8中的记录进行去重。产生虚拟表VT9。
      • ORDER BY #将虚拟表VT9中的记录按照指定列进行排序，产生虚拟表VT10。
      • LIMIT #取出指定行的记录，产生虚拟表VT11，并将结果返回

• JOIN图

  • 

索引介绍

• 概述
  • MySQL官方对索引的定义为：索引（index）是帮组MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
    • 索引的目的在于提高查询效率，可以类比字典。
    • 如果要根据查“mysql”这个单词，可以先定位到m字母，然后从下往上找到y字母，最后找到剩下的sql。
    • 如果没有缩影，那么开发者可能需要遍历查询a—z。
    • 索引会影响where 关键字后的字段，和order by后的排序字段
    • 可以理解为：索引是排好序的快速查找数据结构
  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引忘完以索引文件的形式存储在磁盘上
  • 平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚簇索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）等。
• 索引详解
  • 在数据之外，数据库系统还维护满足特定查找算法的数据结构，这个数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
  • 如图

    • 

      • 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址

    • 为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

• 结论
  • mysql数据库中，除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法
  • 这种数据结构就是索引。
• 索引的优势
  • 提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
• 索引的劣势
  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的
  • 虽然索引大大提高了查询速度，但同时也会降低更新表的速度，如对表进行INSERT，UPDATE和DELETE。因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件。每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
  • 索引只是提高效率的一种因素，如果你的MYSQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

时间复杂度

• 同一问题可用不同算法解决，而一个算法的质量优劣将影响到算法乃至程序的效率。算法分析的目的在于选择合适算法和改进算法。

  • 数据结构	查找	插入	删除	遍历
    数组	O(N)	O(1)	O(N)	-
    有序数组	O(logN)	O(N)	O(N)	O(N)
    链表	O(N)	O(1)	O(N)	-
    有序链表	O(N)	O(N)	O(N)	O(N)
    二叉树（一般情况）	O(logN)	O(logN)	O(logN)	O(N)
    二叉树（最坏情况-也就是有序链表）	O(N)	O(N)	O(N)	O(N)
    平衡树（一般情况和最坏情况）	O(logN)	O(logN)	O(logN)	O(N)
    哈希表	O(1)	O(1)	O(1)	-