mysql数据库 innodb存储引擎 创建索引的一些小认知

前言

          相信所有从事互联网开发的同学们都知道数据库的重要性，因为数据库的读写是磁盘读写，所以数据
          库很大一部分来说就是系统性能的瓶颈，其中最重要的就是索引，要创建好的索引并不是一件简单的事情

sql怎么写

 现在有一些自动生成sql的工具，看似方便，但当我看见这样的sql时，说实话，我很害怕。数据库有多重
 要，我们写sql的时候就要多严谨。写sql并不难，但是，不要真的觉得这并不难。
 1、我们的每条带有where的语句，查询条件都应该很明确    方便创建索引
 2、能越有效的过滤条件应该放在越前面
 3、写完所有sql结合第二条以及索引的最左匹配原则，考虑是否调整查询条件顺序，复用索引
 4、注意$、#符的使用
 5、请用简单查询

如果你需要更多sql优化的信息：

Profile是可以看到sql使用情况的功能，但是要在数据库开启这个功能才能统计和查看。

索引怎么建

   当你写一个sql的时候你就要为它所走的索引负责，所以喜欢用自动生成sql的同学是否应该认真思考，自动生成的查询条件语句有多可怕，索引并不是越多越好，索引需要存储，需要维护。所以怎么做到一个平衡，就是我们需要去思考的。那我们怎么去思考尼，mysql为我们提供了explain关键字。
   那我们怎么解读explain的结果尼

其中标注了有颜色数字的节点都是非常重要的，其中type和Extra列出的是我认为比较重要的几个结果，并不是这两个关键字所有的结果，其中key关键字告诉我们MySql数据库执行的时候走了哪个索引，type、row、extra告诉我们这条sql执行详情
另外我们可能需要记住一些不走索引的情况：
SQL什么条件不会使用索引？

1、（!=或<>）不等于操作不能使用索引、

2、经过普通运算或函数运算后的索引字段不能使用索引

   function(INDEX_COLUMN) = ?

  INDEX_COLUMN + 1 = ?

   INDEX_COLUMN || 'a' = ?

3、含前导模糊查询的Like语法不能使用索引（like ‘%…’）

4 where name is null 不走索引

5、给索引查询的值应是已知数据，不能是未知字段值。

      a.INDEX_COLUMN=a.COLUMN_1

6、使用聚合函数以后的order By不走索引
7、范围查找之后，不走索引（使用index-condition-putdown以后的版本可以走）
8、当访问的数据占表的蛮大一部分的时候（20%），优化器会选择聚集索引来查找数据（选择顺序读，替代随机读）
9、如果sql语句中有类型转换不能走所有
10、两个表的编码不同会导致连表查询的时候，不走索引
例如字符集一个表是 utf8mb4 一个表是 utf8 的超集，这种情况需要需要将被驱动数据表里的字段一个个地转换成 utf8mb4，再做比较。
SQL 性能优化的目标:至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
range：对索引进行范围查找

索引

索引的分类

 下图列出了inndb的索引分类，因为目前我们有关字符串的搜索面向用户都使用ElasticSearch所以这里忽略了全文索引（倒排索引），而哈希索引也不是我们人为可以干预。所以这边我们仅对下图的索引做一些简介。

B+树是由平衡二叉树进化得来的，它不仅是一个平衡二叉树，其左右兄弟节点之间构成一个双向链表的结构。平衡二叉树的结构让数据库走索引时n条数据的最大时间复杂度为log₂n,
也是因为B+树的结构，当修改索引相关的数据时，就需要维护索引树，包括插入、判断当前B+树的平衡，左旋右旋来达到B+树再平衡的一个过程。这就是建立索引所要付出的代价之一
这也是为什么我们需要索引，并且还不能胡乱建索引，要去平衡我们的索引是否可以被多个查询条件复用的原因。
从上面的索引分类图我们可以了解到，在innoDB存储引擎中，索引主要分为B+树索引和主键。B+树索引分为聚集索引和辅助索引。其中聚集索引是主键组成的B+树索引，有了聚集索引我们遍历主键的时候的平均时间复杂度由(1+n)/2变成了(1+log_mn)/2.
主键存储的都是整行的数据，而辅助索引的叶子节点存储的是主键值。所以我们使用辅助索引的时候最后都会再走一遍聚集索引才能拿到整行的数据。据此当我们的查询条件中最后需要根据主键去查找和正排序的时候，联合索引里可以不包含主键，但是，如果我们需要根据主键到排序的时候，联合索引里需要包含主键倒排序。
为什么我们要在索引里面完成排序？除开数据库性能，和排序所做的计算等，如果数据库要去排序，它有两种方式：
1、数据库内存足够，它会加载所有的你需要的列到内存，然后排序，并返回
2、数据库内存不够，它仅加载能够确定排序的字段和主键值到内存，排序，然后根据排序的结果，取所有需要的字段，并返回，这里会产生大量的随机读（i/o），可能每页的数据只有一条需要，数据库预读失效。
关于索引的最左匹配原则，要从联合索引的B+的结构说起：对于一个2个列（a列和b列）是联合索引B+树的结构如下：

联合索引创建的时候是a_b的顺序，B+树内的节点按照（a,b）的顺序存放。在B+树中，顺序读可以取到a的有序序列，b列在a的确定的情况下有序，这就是为什么索引查询要遵循最左匹配原则，如果a不确定的情况下，b是无序的。

结束语

以上我说得都比较简单，如果我们读一些关于sql的书去了解，能更好的理解，并且形成自己的索引观，以及数据库使用的其他观点。
关于mySql还有更多关于索引使用，优化的知识，由于内容实在很多，所以我只能介绍到这里啦，如果我们在平时的工作中，能时刻牢记这些点，我觉得数据库在我们这些小开发手里已经出不了什么大乱子了。希望我们的服务越做越好～与君共勉