MySql数据结构、索引优化原则(Java劝退师)_myisam索引优化原则

MySql(InnoDB)随手记

            MySql索引为甚么选择的是B+。几种数据结构相比，二叉树、红黑树无法保证树的高度可控。当在内存中操作的时候，这些数据结构由于树深度造成的影响可能还能接受，

       但作为数据存储操作磁盘,数据高度觉得了磁盘的IO次数，不可控的树高是无法容忍的。B树的问题在于叶节点存储数据，大大的增加了树高度(单叶节点容量一定时可存储的数据变少了，必定导致树高度变高)。

       MySql的索引树是一个变种B+树，叶子节点是双向指针，末尾节点将指向头节点。Mysql InnoDb索引分为聚集和非聚集索引。。。也就是聚簇和非聚簇。(图片来源于网络....但也是精心挑选的。。。)。

       以下文字虽枯燥,但都是手打。。

             一、聚集索引：

                    1、表数据文件本身就是按B+Tree组织的一个索引结构文件 (表本身就是一个索引树，表数据存储于叶子节点)

                    2、聚集索引-叶节点包含了完整的数据记录 （非叶子节点存储的是文件索引，不存数据信息，单节点的存储能力大大增强，有效的减少了树高）

                    3、索引节点中的数据索引从左到右递增排列

                    当查找一个索引为30的数据是，先通过最底节点 15-56，再查找到第二层节点的区间20-49，再查找到第三层叶子节点的索引为30的数据 

                    

                 通过上面的方式可以看到，查找索引为30的数据时，一共经历了三层查找，任意数据在三次磁盘io内必定会拿到结果，而InnoDB的最底层索引树现在都是默认常驻内存的,所以通过主键查找的方式速度极其高效。

                 一般B+树的高度不会超过三层(也就是图示的高度)。我们可以大概估算一下一个三层的B+树可以存多少数据。Innodb中一个节点的大小为大概16KB(没有十年的DBA功底建议你不要修改它),以主键Long类型8B长度来算，

                 一个索引一个指向下一层的指针，指针大小为6B。那么一个索引加一个指针就是14B，16KB的话可以存放1170，以表中每行数据为1Kb来算的话(128个主键大小的列)，那么三层树存满的数据为 1170*1170*16 = 21902400个。

                所以。。。。基本没有超过三层树的索引结构的表，如果有，那肯定是搞分库分表或者表分区。。。等优化了，四层的索引树结构能容纳的数据量达到了256亿之巨。。简直丧心病狂。

                MySql之所以不建议主键用UUID等字符串，一是应为长度大，二是因为字符串的比较的索引排序效率远远低于数字类型，为何推荐自增或者递增序列就更好理解了,顺序的在右侧添加索引的效率叶远远高于随机添加的效率。尤其是在某节点的数据容量已满再进行插入将影响上层树的结构,导致索引做出比较大的结构调整。

         二、非聚集索引：

                      

                 非主键索引与主键索引结构一直，但是其叶子节点存储的是表的主键ID。查找某个数据时，先通过非主键索引树查找到其对应的主键ID，再用主键ID查询主键索引树拿到叶子节点的数据。所以说非聚集索引的效率略低于聚集索引。

                 非聚集索引的叶子节点不直接存储数据的主要原因是节省磁盘空间和避免数据一致性问题。磁盘空间好理解,每加一个索引就要重新存一份表的所有数据。一致性的问题主要是当更新某行的某个字段的时候,为了保证所有索引查出的数据都是一样的,那么势必要更新所有索引中的数据，并且全部成功之后才能返回成功，势必大大降低了写的性能。而其中保持每个索引的数据一致又增加了实现上和性能权衡的难度。

                 联合索引：多个列的组合索引，如 a、b、c三列，则以三列为索引的索引树。

                 

                 索引还有个Hash类型索引，以hash值找对应的数据，精确查找速度极快，但是由于没法很好的支持范围查找所以业务中很少使用，单对于一些固定查找的业务来说，Hash索引值得拥有。       

  三、索引优化和数据结构的联系 

          EXPLAIN 关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 。在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返 回执行计划的信息，而不是执行这条SQL。

          explain extended  和 explain partitions 是其变种工具。第一个配合show warnings 命令可以得到优化后的查询语句。第二个是对于表分区的表会多出表分区信息

          

         ID列的编号是 select 的序列号，有几个 select 就有几个id。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

         select_type：   

              simple：简单查询。查询不包含子查询、union             primary：复杂查询中最外层的 select        subquery：包含在 select 中的子查询（不在 from 子句中）

              DERIVED：被驱动的SELECT子查询，MySQL会将结果存放在一个临时表中，也称为派生表      MATERIALIZED:派生表物化     。。。。其他就不一一列出了

       type列：依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL 一般来说，得保证查询达到range级别，最好达到ref。如果是Null则表示在索引内就能满足查询需求，无需访问表,比如覆盖索引

           eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。ref：相比 eq_ref，是使用普通索引或者唯一性索引的部分前缀。range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。

       possible_keys列以及key列  可能用到的以及优化器最终使用的索引

       key_len列 ：这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。如下(索引为order_id和type联合索引，keylen为9表示值所了联合索引的order_id列)

      

     Extra列 ：附加信息。重要但不是必然，这点要铭记。

    Using index：使用覆盖索引 。       Using where：有元素过滤，查询的列未被索引覆盖只是其一，要看返回的数据是否被扫描的过滤了          Using index condition：查询的列不完全被索引覆盖         Using temporary：mysql需要创建一张临时表来处理查询(amount未加索引,需要查询后创建临时表去重)

    

    Using filesort：将用外部排序而不是索引排序(mysql默认升序,使用非索引降序这会出现)。次种情况分两种：

        单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。过程：

                     1、根据条件找出数据主键ID。  2、根据id找出数据放入sort_buffer     3、重复1/2直到取出所有   4、排序

        双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段

                    1、根据条件找出数据主键ID。  2、根据id找出数据把id和排序字段放入到sort_buffer中     3、重复1/2直到取出所有   4、排序  5、排序后根据ID重新查出数据返回

       可以看出。单路排序明显快，双路排序明显是用于大量列数据排序。

      MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节，非10年DBA慎动) 的大小和需要查询的字段总大小来 判断使用哪种排序模式。前者大单路排序模式。前者小使用回表排序

    实战总结：实战咱就不贴图了。

        1、如果索引了多列，要遵守最左前缀法则。根据上图的联合索引的数据结构，如果 where  a = xxx  and  c=xxx  其实是没法是能使用c索引，因为中间b断了，mysql无法得知第二列从何查起。其索引效率等价于 只用a 条件查询。

             再列如 where  b=xxx  也是无法索引到的。因为a是起始条件，理解起结构就不奇怪了。但是 where  a = xxx  and  c=xxx  and  b=xxxx  可以运用所有索引，虽然sql中顺序不对，但mysql优化器可以分辨(不会那么蠢)。不过出于节约优化

           器的步骤，任然推荐按顺序写条件。  同样的道理   %like  是无法命中索引的(覆盖索引除外)。   like%可以。   %like可以牺牲精度使用全文索引。

           覆盖索引除外。覆盖索引不会回查表，但是可以通过索引拿到全部数据，相比查表效率要高，所以任然会走索引。

       2、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。。。规定就是这样，至于为啥恐怕要读一下mysql的C源码。。。咱也看不懂。  字符串不加单引号，索引失效，隐式的转换

       3、范围条件右边的列索引是没法用的。。。根据上图的联合索引的数据结构。如果b是范围查找，c=xxx 其实是没用的。。因为它还是会挨个找到符合c的值。因为b范围里c=xxx的值可能位于索引节点最末尾，等于也就是直接全部查找

       4、.尽量使用覆盖索引、、不用说了，是人都知道，不用查表了。

       5、使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描. 是人都知道。第三点就是原因。

      7、is null,is not null 也无法使用索引。  很明显，这个null很尴尬，mysql会在索引里把null的全放到最前面，鬼知道null的索引里存什么，既然存什么都不知道，那索引无从谈起。强烈建议尽可能非null所有字段

      8、用or或in，用它查询时，mysql不一定使用索引。范围查询同样也是不一定走索引。 因为你查得数据可能数据量很多，可能比全表耗时。怎么选择由优化器决定，也就是第九条。

     9、mysql优化器如何选择索引以及是否走索引。通过用trace工具来工具可以看出。字段太多，就不列了。cost 字段代表使用索引成本，哪个方式成本小选哪个（开启trace影响性能，晚上没人的时候关灯打开）

          (set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace     set session optimizer_trace="enabled=off"; ‐‐关闭trace )

   10、Order by 同样适用最左前缀法则。其会导致的附加信息就是上面讲到的  Using filesort。 Group By 与之类似

    11、分页查询 ：select * from  order limit 20000,10;  看似是只查询了10条数据，其实是查询了20010条数据然后丢弃前面的20000条数据。在主键有序的情况下可以带上主键的条件。select * from  order where id>20000 limit 0,10可以缩小查找范围。

            非主键的order by排序的分页可以通过覆盖索引的方式。以上都无法适用时可以 先只查出id这一列。然后根据id再去查出数据。

    12、join关联查询优化(只针对inner join,left和right已经确定了主表的顺序) ：  小表驱动大表是主要原则，因为mysql的表关联有两种算法，NLJ和BNLJ。

           NLJ执行大致流程(假设O1是驱动表小表100行数据，O2是被驱动表大表10000行数据)：从O1中取出一行数据，根据O1中关联的字段到O2表中查找，找到后和原O1的行数据合并返回，知道O1中数据遍历结束。整个过程遍历O1取出100行数据，

           根据O1的字段精准查找O2一百行数据，整个过程扫描了200行数据。此算法关联字段必须要有索引才能如此高效，否则将是 100*10000的恐怖扫描。

           BNLJ执行大致流程：把O1表中的数据全部放入到 join_buffer中，把O2中每一行数据取出和O1进行对比，因此会扫描100+10000=10100行。但因为join_buffer中的数据是无序的，依次对比其对比次数为100*10000次。

           由此可以看出，有索引的时候，采用第一种算法的join关联效率大大的增加，在没有索引的情况下，BNLJ扫描数据的次数远小于NLJ，效率更快，因为100万次内存中数据对比的时间相比于磁盘扫描的时间微不足道。

          mysql可以自主的识别出大表和小表。但人为的指定好小表有利于优化器跳过分析增加性能，此时就是  straight_join 只适用于inner，并不适用于left、right join。不过慎重使用，因为很可能你判断不是那么准确。。

   13、in和exists优化：小的数据集驱动大的数据集这点原则是没有变得。查询都是遍历小表的数据集查询大表的数据。只不过In返回的数据，Exists返回的是布尔值。不过官方也说是查询的数据，只是返回的时候忽略了数据。。。究竟如何可能源码才能解释

   14、count查询：select count(1)、select count(id)、select count(非聚集索引字段)、select count(*) 。其中其他列如果存在索引的话，count(id)的效率将是最低的。count(id) 查询的是主键下的叶子节点数，其数据一般比非主键索引大，因而速度慢。count(1)

           count(*) 都会自动优化查询非主键索引的叶子节点，因其存的是主键id，数据小，因此快。不同于count(非聚集索引字段)，count(1)、count(*)是不管null值得，而count(非聚集索引字段)会判断不是null值才计算数值，因此也可以想出其性能稍低一点点。

          myisam的总行数是存在磁盘的，因此行数查询速度比较快。

    15、mysql的锁和事务隔离级别：MyISAM的锁是锁表，读锁阻塞写操作，写锁阻塞读写操作。InnoDB采用的是行锁，这也是于MyISAM的引擎的第二大区别(另一个重要区别是对事务的支持).  Mysql的默认隔离级别是可重复读，即除了幻读以外的所有的问题都

          得到了解决(间隙锁在某些情况下可以解决幻读的问题，也可以使用排它锁)。可重复读有个比较重要的特性即是多次读取同一sql，结果是相同的，但是更新操作是会使用数据库中已提交事务的最新数据。比如事务A中读取一个数据某字段值为10，事务B中对其更改成11而且提交了事务，此时A事务重复读取时它的值任然 是10，但是如果事务A对其进行了更新操作，如原字段+10，此时再读取的值不再是10+10=20，而是数据库原有的值 11+10=21. 可重复读大大提高了在并发访问时的代码可编写性，其实现机制归功于 MVCC机制(其实我觉得就是个乐观锁嘛,根据版本号控制 快照的版本).MVCC只适用于Msyql隔离级别中的读已提交（Read committed）和可重复读（Repeatable Read）。原因很简单，脏读会读到未提交事务的数据而MVCC版本创建和删除只在事务创建和提交后产生，其二串行化读表加锁，当然就没有了行版本 问题。MVCC是使用了行级锁，并不是数据库的行锁。其实现如下工作流程。下面主要看下MVCC是如何工作的：(begin并不能开启一个事务的一个版本号，只有执行第一条select语句的时候才会生成)

          1、每个事务都携带一个自身的事务id，将设有两个事务A，事务id为10。事务B，事务id为20；

          2、一张空表，student表为空。

          3、第三个事务C，事务id为 3，插入两条数据（事务id是全局递增的，事务id小的表示是先开启的事务），此时表如图，注意这个数据版本号和数据删除版本号是隐藏的(基于UnDolog实现)，其实还有个 回滚指针，用于事务回滚。

          

         4、此时事务A删除id=1的数据，其结果为：

                

    5、此时事务B修改id=2的 age值为21(将增加一条隐藏记录)

6、A事务查询全表数据：查找数据行版本小于或等于当前事务版本的数据行  and  查找删除版本号要么为NULL，要么大于当前事务版本号的数据行。  因此事务版本号<=10  删除版本号 > 10 的数据将被查出来

  

7、B事务查询全表数据。事务版本号<=20  删除版本号 > 20 或者是null的数据将被查出来

UndoLog:insert-Undo和Upate-Undo.

     1、当前事务版本生成 undo log，undo log 也会产生 redo log 来保证 undo log 的可靠性

     2、当事务提交之后，undo log 并不能立马被删除，而是放入待清理的链表(insert-Undo事务提交后会立即删除，因为新增的数据没有其他线程在使用)

     3、由 purge 线程判断是否有其它事务在使用 undo 段中表的上一个事务之前的版本信息，从而决定是否可以清理 undo log 的日志空间。

小知识点补充：

1、mysql行锁是锁的索引。。。没有索引的 update xxx set age = 1 where name =‘李四’  ，如果李四没有索引，将会升级到锁全表额。

2、查看锁竞争情况 show status like'innodb_row_lock%';