索引优化笔记！

索引可以理解为书的目录。方便快速查询。索引不是万能的，在有些情况下使用索引反而会让效率变低。比如占用存储空间、降低数据库写操作的性能、多个索引增加索引选择的时间

功能逻辑区分：普通索引、唯一索引、主键索引、全文索引

普通索引：基础的索引，没有任何约束，主要用于提高查询效率。
唯一索引：在普通索引的基础上增加了数据唯一性的约束。
主键索引：在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE
全文索引：MySQL 自带的全文索引只支持英文。我们通常可以采用专门的全文搜索引擎

其实前三种索引（普通索引、唯一索引和主键索引）都是一类索引，只不过对数据的约束性逐渐提升。
在一张数据表中只能有一个主键索引，这是由主键索引的物理实现方式决定的，
因为数据存储在文件中只能按照一种顺序进行存储。但可以有多个普通索引或者多个唯一索引。

物理实现区分：聚集索引、非聚集索引（二级索引或者辅助索引）

聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据位置。非聚集索引不会影响数据表的物理存储顺序。
一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。
使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。

字段个数进行划分：单一索引、联合索引
①数据量少使用索引反而执行更耗时。
②比如性别字段，取值少通常是不需要创建索引的。

 show profiles; #查看sql执行的query_id和持续时间（单位：毫秒）

show profile for query 2; #根据sql查询的query_id

 set profiling=1; #开启profile
show variables like "%profiling%";
show processlist;

组合索引有效性：show index from table from database查看database库中table表的所有索引
用户表user有字段ABC，创建了普通组合索引 uni_a_b_c 根据最左匹配原则，只有先确定了前一个（左侧的值）后，才能确定下一个值。所有以A开头的where条件sql都能使用到该索引。但根据如下所有情况的组合表格测试发现，包含A非A开头的sql也能使用到索引。这就是mysql的优化器的效果。最终不包含A的使用不到该组合索引,非A开头的AC使用不到索引！
in 和 = 都可以乱序，mysql优化器会调整顺序;当遇到范围查询(>、<、between、like)会停止匹配
①如果创建了组合索引ABC，那还有必要单独创建A的索引么？根据explain sql语句发现，possible_keys可能用到的索引有ABC,A。但key实际用到的索引是ABC。也就是说没必要再单独创建了。
②如果组合索引ABC和另一个字段的索引D在一条sql中使用到。是怎样的呢？根据测试发现， 索引约束强则优先级高。

A开头（name）	B开头（email）	C开头（mobile）
A	B（×）	C（×）
AB	BA	CA（√）
AC（√）	BC（×）	CB（×）
ABC	BAC	CAB
ACB	BCA	CBA

创建索引有哪些规律？

字段的数值有唯一性的限制
频繁作为 WHERE 查询条件的字段（包括select、update、delete的where）
需要经常 GROUP BY 和 ORDER BY 的列(若创建组合索引group字段要在order字段前，否则失效)
DISTINCT 去重字段需要创建索引
做多表 JOIN 连接操作时，创建索引需要注意以下的原则：
    连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率
    对用于连接的字段 和 WHERE 条件创建索引，连接字段类型要保持一致

什么时候不需要创建索引?

WHERE 条件（包括 GROUP BY、ORDER BY）里用不到的字段不需要创建索引
表记录太少,索引反而加大开销
字段中如果有大量重复数据，也不用创建索引
频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

什么情况下索引失效?

①如果对索引使用函数或表达式计算，则会失效。
`WHERE id+1 = 101`改成`WHERE id = 100`
`WHERE SUBSTRING(name, 1,3)='abc'`改成`WHERE name LIKE 'abc%'

②在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。

③当我们使用 LIKE 进行模糊查询的时候，后面不能是 %
`where like '%xxx%' # 左边有%不走索引
`where >= a and <= b`改成`where between and`

④索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效。
索引并不存储空值，所以最好在设计数据表的时候就将字段设置为 NOT NULL 约束

理想索引设计：三星索引 最小化碎片、避免排序、避免回表查询
三星索引会让索引变宽，好处就是不需要进行回表查询，减少了磁盘 I/O 的次数，弊端就是会造成频繁的页分裂和页合并，对于数据的插入和更新来说，效率会降低不少。

在 WHERE 条件语句中，找到所有等值谓词中的条件列，将它们作为索引片中的开始列；
将 GROUP BY 和 ORDER BY 中的列加入到索引中；
将 SELECT 字段中剩余的列加入到索引片中。

回表就是通过索引找到了数据行，但是还需要通过主键的方式在数据表中查找完成的记录。
这是因为 SELECT 所需的字段并不都保存在索引中，
因此我们可以将 SELECT 中的字段都保存在索引中避免回表的情况，从而提升查询效率。

索引概览 索引的使用原则
索引的数据结构
Hash索引
理想索引

mysql通配符：_表示一个字符,%表示一个或多个字符! 通配符不出现开头可走索引，出现在开头则不走索引，走全表扫描。

例如要模糊查询一个脱敏的用户信息：己方存的是未脱敏数据，对方提供查询的数据是脱敏数据，这时简单做法是，己方该表新建查询字段的冗余字段，存脱敏冗余数据。这样就可以直接根据对方提供的脱敏数据直接查

脱敏数据：
name: 李**
mobile: 188****8888
idcard: 3412************3411

通配符匹配：
name: 李__  或 李%
mobile: 188____8888 或 188%8888
idcard: 3412__________3411 或 3412%3411

优化之前写法

select * from users
 where  name like '李%' 
and mobile like '188%' 
and idcard like '3412%' 
and mobile like '%8888' 
and idcard like '%3411';

利用通配符_或%优化sql语句,使之能尽量走索引提升查询效率。

select * from users
 where  name like '李%'
and mobile like '188%8888'
and idcard like '3412%3411';

select * from users
 where  name like '李__'
and mobile like '188____8888'
and idcard like '3412__________3411';

数据库查询脱敏数据时,可以考虑建冗余字段存脱敏数据。不使用like而是=。例如像查手机号188****8888,数据库存的是18888888888，多存一个冗余字段188****8888