mysql学习整理

简单对最近mysql的学习总结一下

索引的分类

索引分类又很多种分法，这里我采用聚簇索引和非聚簇索引的分法：

• 聚簇索引主要是主键和隐式主键在使用

• 非聚簇索引包含平时建立的一些索引(普通索引、唯一索引等)

对于mysql建立索引时key和index关键字的区别：

key强调的是概念层面的约束(如唯一索引)，而index强调的是实现，在实际使用中，mysql对二者几乎没有区别(约束和实现绑定在一块)

如何建立高性能索引

有一个大的指导方向：

• 索引将相关记录放到一起则获得一星

• 索引中数据的顺序和查找中的排列顺序一致则获得二星

• 如果索引中的列包含了查询中需要的全部列则获得三星

如果要建立高性能索引，还需要了解一些mysql底层的知识：

• 索引的最左原则

建立索引的时候，如果是多个列会从最左边的列为准，执行查询的时候，不能跳过中间的某一列去使用索引，否则跳过的索引会失效。

• 覆盖索引

在查询的时候如果能从索引中返回需要的字段就不会再去数据表中拿字段，减少了回表的过程，提升了的性能

• 索引下推

能够支持在引擎查索引的时候将服务器层的部分一些where条件提前纳入进行过滤，减少了回表次数

• 其他原则

索引不支持右like，如%like，但支持左like，如like%，其实也属于最左原则。

mysql执行的时候大多数情况下都会使用一个索引去查询

对于联合索引，范围查询之后的列将失效

索引不支持条件有表达式的情况，包括函数

综上得出一些建立索引的常见策略有：

• 尽量对区分度高的列建立索引并放在前列，某些区分度不高但是使用频繁，也可以增加索引提高速度

• 尽可能包含需要使用的字段，使用覆盖索引，减少回表，对于jpa可能不太好用，统计模块比较好使(视情况而定)

• 一张表的索引数量不超过7、8个

• 对于某些字段比较长的列，可对其左前缀建立索引

• 建立联合索引时，经常使用范围查询的列尽量往后放

• 为连表查询的被驱动表的列建立索引

explain执行计划

https://www.cnblogs.com/acm-bingzi/p/mysqlExplain.html

ps：对于连表查询的执行计划，驱动表优先展示

项目中实践的地方

实际上并不容易做到标准的三星索引，需要根据具体的业务达到最优化的索引，下面是一些项目中索引梳理的地方：

• 存在大量的区分度低的单列索引

如del_flag、audit_status等字段，完全可以和其他常用的单列索引组成联合索引，既减少了空间又提升了查询效率，对插入效率也有所提高

• 存在一些对主键建立的唯一索引，或是已有的联合索引，且顺序也能对应上的唯一索引

• 部分联合索引的首列区分度低；部分外键列又单独建立了重复索引

• 对于%like%的地方可以采用覆盖索引去优化这样的语句，实测是支持覆盖索引的

• 延迟关联策略

写子查询，该子查询支持覆盖索引，再根据主键去拿原数据表的数据

• 拆分大量业务的sql

在项目中，特别是统计有大量的业务都在一个sql中执行，对于mysql来讲，连接的建立和释放都是比较轻量级的，完全可以拆分得更加细致，不仅增加代码复用性，而且业务利于他人理解

• 汇总表策略

统计中采用预统计表提升效率

• 非常慢的查询

减少扫描不需要的数据行；重新优化sql逻辑

• 因为基于B+树的索引是有序的，可以尽量使用索引进行排序