mysql中的索引优化

1.索引是如何加快数据查询速度的

   合理使用索引能大大提升查询性能，就要理解mysql是如何组织和维护索引的。

   1）索引是存储引擎用于快速找到记录的一种数据结构，这是索引的基本功能之一。注[1]

   2）人们谈论的经藏谈论的索引，如果没有特别指明，那多半是指B-TREE索引。注[2]

　1.1.当你给数据库添加索引的时候，可以选择索引方法，一般是B-Tree,如图1.1，添加索引之后，mysql负责索引组织和维护。

                图  1.1  添加索引

1.2 .B-Tree索引树（多路平衡查找树）的一般组织形式如下图，图1.2和更多树形结构以及B-Tree更详细的介绍参见

https://www.cnblogs.com/vianzhang/p/7922426.html

                   1.2  B-Tree树

因为B-Tree索引树的组织特殊组织方式，如果这个索引是主键索引，那么在当前节点的叶子页（叶子页是一个没有指向其子节点的特殊节点，但是叶子也中存放的是被索引的数据行）中存放对应的数据行，根据索引找到主键，就能找到得到对应的数据，试想如果没有索引，mysql势必会采取全表扫描的方式去查询数据记录，就要从磁盘大量读取，比较，再读取......速度自然不会快了。

2.如何查看sql的执行过程，是否走了索引

如果你想要通过添加索引的方式优化你的sql语句，首先你要知道sql语句时怎么执行，通过mysql中的 EXPLAIN 关键字，可以查看sql执行计划，执行sql之后看到如下信息（在图形化界面工具中的暂时效果）。

    　　　　　　　　图 2.1 查看sql执行计划

表中的信息重点关照 type 字段，key字段，rows字段（关于表中各个字段的详细介绍参见这篇文章：

https://blog.csdn.net/xiaocai9999/article/details/80012353）

  2.1  上来先看rows：为了找到目标数，mysql预估需要扫描的记录条数，数字越小效率越高，那你就得去找原因了，接着往下看

  2.2  type:指的是数据访问类型，大白话说就是采取什么方式去找到你需要的数据，其中比较常见的type类型的值如下（注意不同查询方式）：

    1）ALL:扫描表，最低效的方式，如果数据访问类型为all，那就说明查询没有走索引， 或者索引失效了，在你查询数据不走索引的时候你应该考虑要建索引了

　　　　　　　　　　　　　　　　　　　　2.2.1  无索引查询，即便只查询一条数据 ，也会扫描很多记录

  2）index:扫描索引区，不扫描数据区

  3）rang:范围扫面。表明查询肯定走索引了，再一定范围进行了扫面：比如添加了索引，但是查询的时候是用模糊查询，且字段前面是确定的，或者查询没有走索引，但是在筛选（WHERE条件语句）数据的时候给出了明确限定了范围。如果查询的字段没有添加索引，那么此处的模糊查询仍然 是扫描全表。在优化sql语句的时候最差要优化到这一层次。

  4）ref:通过普通索引进行查询，可能命中一个记录，也有可能命中多个记录，性能比让rang猛。如下图：

2.3 possible_key : 当前表可能用到的索引，可以有一个，也可以有多个

2.4 Key : 本次查询实际用到的索引，如果时全表扫描，则不会用到索引，本栏则为null，查询压根没有索引，要考虑优化

3  sql优化的一般思路：

　　1）添加适当的索引，索引字段必须要能最大限度的区分全表数据，才能达到比较好的效果。

　　2）在查询语句中带上where条件，过滤条件最好已经添加了索引，可以避免全表扫描。

　　3）注意索引失效问题。

1. 前置模糊查询会导致索引失效；
2. 索引字段参与函数计算会导致索引失效
3. 在筛选条件中OR关键字的两端，一个字段有索引，另一字段无索引， 索引失效
4. 隐式转换，穿入字段类型和索引字段类型不一样，但是能匹配上，mysql 就会在不改变字段内容的前提下，把出入字段转换成数据库中对应的字段类型进行匹配，此时也不走索引。

4）在使用复合索引的时候要要遵循最左原则，筛选条件中的字段顺序和复合索 引的字段顺序保持一致

5）需要哪些字段就查询哪些字段，不要用*号，避免过多回表操作

6）sql优化的其他思路：

     I.对热点数据进行缓存，

    II.读写分离主从架构

    III.分库分表，应用曾开发难度大，一般不这么玩，常用的分库分表中间件有mycat,sharing-jdbc。

4 sql优化时很有必要了解的其它东西:注[3]

     1）聚簇索引：不是一种单独的索引类型，而是一种数据存储结构，“聚簇”的字面意思是：数据行和和相邻的键值紧凑的存储在一起，在同一个结构同时保存了索引

             和数据行，在索引的叶子页中保存了索引对应的完整的数据记录，找到索引，就能找到对应的数据。一张数据库表有且只有一个聚簇索引，因为不能把

              同一个数据同时存储在不同地方。注意，数据行是存储在叶子页中，但是节点页只有索引行。

     2) 覆盖索引：如果索引的叶子节点中已经包含了要查询的数据，那就不必回表，一个索引包含了所需查询的字段的值，就称之为覆盖索引，覆盖索引必须存储索引列

               的值

     3)回表：出现回表操作的根本原因：二级索引（辅助索引）指的是除了主键索引之外的索引，因为二级索引树叶子节点中储存的不是索引值对应的数据行，二级索引中

          保存的‘行指针’不是指向行的物理位置，而是指向行的主键值，要再次根据主键值再次到表中查询数据行，于是就产生了回表操作。

-----------------------------------------纸上得来终觉浅，绝知此事要躬行 -------------------------------------------

参考书目和网络资源：

1.  [1][2][3]:《mysql高性能》第三版   Baron Schwarzt, Peter Zaitsev, Vadim Tkachnko 著   宁海元  周振兴  彭立勋  翟卫祥  刘辉  译

2. 博大的java世界：explain分析sql语句字段的解释和含义：https://blog.csdn.net/xiaocai9999/article/details/80012353）

3.过来啊小莲：BTree和B+Tree详解：https://www.cnblogs.com/vianzhang/p/7922426.html

mysql中的索引优化

标签：cat   关键字   扫面   bar   图片   语句   没有   怎么   分表