SQL优化

SQL优化
针对SQL进行调整，在写SQL的时候遵循最左前缀原则，向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，范围列可以用到索引，但是范围列后面的列无法用到索引。

like以通配符%开头索引失效会变成全表扫描的操作。

如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描。

只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。

不要使用select *，改用select加字段名称，因为select *走的聚集索引，会进行全表扫描，如果一定要使用select *的话，mysql至少使用5.6版本，这个版本有一个离散读的优化，离散读的优化是将离散度大的列放到联合索引的前面，举个例子，select * from user where staff_id = 2 and customer_id = 584，这个时候索引优化会将customer_id放到前面，因为它的离散度更高，可以通过select count(distinct customer_id),count(distinct staff_id) from user查看列的离散度。

5.6版本有一个ICP的优化，以往根据索引查找记录，再根据WHERE条件来过滤记录。使用ICP优化后，会在取出索引的同时，直接根据WHERE条件过滤，将WHERE的部分过滤操作放在了存储引擎层。在某些查询下可以大大减少上层SQL层对记录的索取，从而提高性能。

5.6版本还有一个MRR优化，是批量处理对键值的查询操作ICP优化，减少缓冲池中页被替换的次数，使数据访问变得较为顺序。辅助索引查询得到书签后，先对主键进行排序，再按序进行查找。

另外在写sql的时候，尽量使用它的一个执行计划，去看我们的索引是不是失效了。
索引失效的几种情况
如果条件中有or，即使其中有部分条件带索引也不会使用。

对于复合索引，如果不使用前列，后续列也将无法使用。

like以%开头。列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引。

where中索引列有运算，有函数的，不使用索引。

如果mysql觉得全表扫描更快的时候，数据少的情况下，不使用索引。

聚集索引
InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clusteredindex）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。每个数据页都通过一个双向链表来进行链接由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据如：用户需要查询一张注册用户的表，查询最后注册的10位用户，由于B+树索引是双向链表的，用户可以快速找到最后一个数据页，并取出10条记录SELECT * FROM Profile ORDER BY id LIMIT 10;虽然使用ORDER BY对主键id记录进行排序，但是在实际过程中并没有进行所谓的filesort操作，而这就是因为聚集索引的特点。另一个是范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。如：SELECT * FROM Profile where id>1 and id < 100;

辅助索引
对于辅助索引（Secondary Index，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。