走索引的情况和不走索引的情况

返回：Mysql

in走索引

返回顶部

能不用in就不用in

in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内

select * from table where id in ('2','1');

范围查询走索引

返回顶部

select * from table where create_time > '2019-01-01 01:01:01' and create_time < '2019-12-12 12:12:12';

模糊查询只有左前缀使用索引

返回顶部

select * from table where id like '%1%';
select * from table where id like '%1';

select * from table where id like '1%';
-- 只有这个走索引，即左前缀

反向条件不走索引

返回顶部

反向条件不走索引

!=、<>、NOT IN、IS NOT NULL

对条件计算(使用函数或者算数表达式)不走索引

返回顶部

select * from table where length(id) <= 6;
select * from table where create_time < NOW();
select * from table where id/10 < 6;

解决方法

使用函数计算不走索引，无论是对字段使用了函数还是值使用了函数都不走索引，解决办法通过应用程序计算好，将计算的结果传递给sql，而不是让数据库去计算

查询时必须使用正确的数据类型

返回顶部

如果索引字段是字符串类型，那么查询条件的值必须使用引号，否则不走索引

or 使用索引和不使用索引的情况

返回顶部

or 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的

用union少用or

返回顶部

尽量避免使用or，因为大部分or连接的两个条件同时都进行索引的情况几率比较小，应使用uninon代替，这样能走索引的走索引，不能走索引的就全表扫描。

能用union_all就不用union

返回顶部

union all 不去重复，union去重复，union使用了临时表，应尽量避免使用临时表

复合索引

返回顶部

对于复合索引，如果单独使用右边的索引字段作为条件时不走索引的。即复合索引如果不满足最左原则leftmost不会走复合索引

create index idx_name_time on table(name,create_time);
select * from table where name='1';
select * from table where name='1' and create_time='2019-01-01 01:01:01';
select * from table where create_time='2019-01-01 01:01:01' and name='1';  
select * from table where create_time='2019-01-01 01:01:01';-- 不走索引

覆盖索引

返回顶部

order-by

返回顶部

mysql有两种排序方式：
1.通过有序索引顺序扫描直接返回有序数据，通过explain分析显示Using Index,不需要额外的排序，操作效率比较高。
2.通过对返回数据进行排序，也就是Filesort排序

order by 使用索引的严格要求：

• 索引的顺序和order by子句的顺序完全一致
• 索引中所有列的方向(升续、降续)和order by 子句完全一致
• 当多表连接查询时order by中的字段必须在关联表中的第一张表中

如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合 索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
正例:where a=? and b=? order by c; 索引:a_b_c
索引中有范围查找，那么索引有序性无法利用，如:WHERE a>10 ORDER BY b; 索引 a_b 无法排序

order by如果根据多个值进行排序，那么排序方式必须保持一致，要么同时升续，要么同时降续，排序方式不一致不走索引

group-by

默认情况下，group by column；
有两个作用，第一个就是根据指定的列进行分组，第二作用group by 不但分组，而且还为分组中的数据按照列来排序，(如果分组的字段创建了索引，那么排序也没什么毕竟排序走索引也很快)
但是如果group by指定的列没有走索引，而我们通常情况下只对分组中的数据进行统计，例如对分组中的数据求和，通常顺序无关紧要，此时就要关闭group by 的排序功能，使用Order By NULL;来关闭排序功能，避免排序对性能的影响。

分页limit

返回顶部

分页查询一般会全表扫描，优化的目的应尽可能减少扫描；

• 第一种思路：在索引上完成排序分页的操作，最后根据主键关联回原表查询原来所需要的其他列。这种思路是使用覆盖索引尽快定位出需要的记录的id，覆盖索引效率高些
• 第二中思路：limit m,n 转换为 n

之前分页查询是传pageNo页码, pageSize分页数量，
当前页的最后一行对应的id即last_row_id，以及pageSize，这样先根据条件过滤掉last_row_id之前的数据，然后再去n挑记录,此种方式只能用于排序字段不重复唯一的列，如果用于重复的列，那么分页数据将不准确

多表连接查询连接条件(也就是外键必须创建索引，否则大数据查询直接卡死)

如果全表扫描比使用索引快，就不会使用索引，比如 表的数量很少或者满足条件的数据量比较大也不走索引, 查询数据库记录时，查询到的条目数尽量小，当通过索引获取到的数据库记录> 数据库总记录的1/3时，SQL将有可能直接全表扫描，索引就失去了应有的作用。

in和exists

返回顶部

查询所有下过订单的用户(tbl_user 500w条数据，tbl_order 3条数据), 有两种方式，一种使用in另一种使用exists，但是两者效率相差很大;但是没有定论谁更快，需要尝试

强制索引

当查询时不走索引时可以通过force index 强制mysql使用指定的索引，一般情况下如果mysql不走索引它是认为全表扫描会更快些，可以通过强制走索引看一下查询时间，如果强制索引效果更好，查询速度更快就使用强制索引，如果强制索引没有明显效果就没必要使用了
select * from table force index(PRI);
select * from table force index(idx_xxx);

其它优化

返回顶部

禁止使用select *，需要什么字段就去取哪些字段
超过三个表禁止join。需要join的字段数据类型必须绝对一致;
多表关联查询时，保证被关联的字段需要有索引。说明:即使双表 join 也要注意表索引、SQL 性能。
尽可能避免复杂的join和子查询。
尽量使用左右连接，少使用内连接。
永远小结果集驱动大结果集(这点mysql会自动优化)
拒绝大SQL，拆分成小SQL

减少 IO 次数
IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

降低 CPU 计算
除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户（这些操作基本上都是 CPU 处理内存中的数据比较运算）。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标

索引面试

返回顶部

MySQL索引的原理和数据结构

mysql的索引说白了就是用一个数据结构组织某一列的数据，然后如果你要根据那一列的数据查询的时候，就可以不用全表扫描，只要根据那个特定的数据结构去找到那一列的值，然后找到对应的行的物理地址即可。

• mysql的索引是怎么实现的？
  不是二叉树，也不是一颗乱七八糟的树，而是一颗b+树。

说b+树之前，咱们还是先来聊聊b-树是啥，从数据结构的角度来看，b-树要满足下面的条件：
（1）d为大于1的一个正整数，称为B-Tree的度。
（2）h为一个正整数，称为B-Tree的高度。
（3）每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
（4）每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。
（5）所有叶节点具有相同的深度，等于树高h。
（6）key和指针互相间隔，节点两端是指针。
（7）一个节点中的key从左到右非递减排列。
（8）所有节点组成树结构。
（9）每个指针要么为null，要么指向另外一个节点。
（10）如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)，其中v(key1)为node的第一个key的值。
（11）如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)，其中v(keym)为node的最后一个key的值。
（12）如果某个指针在节点node的左右相邻key分别是keyi和keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)且大于v(keyi)。

比如说我们现在有一张表：

(
id int
name varchar
age int
)

我们现在对id建个索引：15、56、77、20、49

select * from table where id = 49
select * from table where id = 15

b+树是b-树的变种，啥叫变种？就是说一些原则上不太一样了，稍微有点变化，同样的一套数据，放b-树和b+树看着排列不太一样的。而mysql里面一般就是b+树来实现索引，所以b+树很重要。

• 每个节点的指针上限为2d而不是2d+1。
• 内节点不存储data，只存储key；叶子节点不存储指针。

mysql里不同的存储引擎对索引的实现是不同的。

3.2 myism存储引擎的索引实现

返回顶部

先来看看myisam存储引擎的索引实现。就拿上面那个图，咱们来现场手画一下这个myisam存储的索引实现，在myisam存储引擎的索引中，每个叶子节点的data存放的是数据行的物理地址，比如0x07之类的东西，然后我们可以画一个数据表出来，一行一行的，每行对应一个物理地址。
myisam最大的特点是数据文件和索引文件是分开的，大家看到了么，先是索引文件里搜索，然后到数据文件里定位一个行的。

3.3 innodb存储引擎的索引

返回顶部

好了，再来看看innodb存储引擎的索引实现，跟myisam最大的区别在于说，innodb的数据文件本身就是个索引文件 ，就是主键key，然后叶子节点的data就是那个数据的所在行。
innodb存储引擎，要求必须有主键，会根据主键建立一个默认索引，叫做聚簇索引，innodb的数据文件本身同时也是个索引文件，索引存储结构大致如下：
innodb表是要求必须有主键的，但是myisam表不要求必须有主键。另外一个是，innodb存储引擎下，如果对某个非主键的字段创建个索引，那么最后那个叶子节点的值就是主键的值，因为可以用主键的值到聚簇索引里根据主键值再次查找到数据，即所谓的回表

select * from table where name = ‘张三’

先到name的索引里去找，找到张三对应的叶子节点，叶子节点的data就是那一行的主键，id=15，然后再根据id=15，到数据文件里面的聚簇索引（根据主键组织的索引）根据id=15去定位出来id=15这一行的完整的数据

• 所以这里就明白了一个道理，为啥innodb下不要用UUID生成的超长字符串作为主键？因为这么玩儿会导致所有的索引的data都是那个主键值，最终导致索引会变得过大，浪费很多磁盘空间。
• 还有一个道理，一般innodb表里，建议统一用auto_increment自增值作为主键值，因为这样可以保持聚簇索引直接加记录就可以，如果用那种不是单调递增的主键值，可能会导致b+树分裂后重新组织，会浪费时间。

3.4 索引的使用规则

返回顶部

select * from table where a=1 and b=2 and c=3

你知道不知道，你要怎么建立索引，才可以确保这个SQL使用索引来查询

create index (shop_id,product_id,gmt_create)

• （1）全列匹配
  这个就是说，你的一个sql里，正好where条件里就用了这3个字段，那么就一定可以用到这个联合索引的：

select * from product where shop_id=1 and product_id=1 and gmt_create=’2018-01-01 10:00:00’

• （2）最左前缀匹配
  这个就是说，如果你的sql里，正好就用到了联合索引最左边的一个或者几个列表，那么也可以用上这个索引，在索引里查找的时候就用最左边的几个列就行了：

select * from product where shop_id=1 and product_id=1，这个是没问题的，可以用上这个索引的

• （3）最左前缀匹配了，但是中间某个值没匹配
  这个是说，如果你的sql里，就用了联合索引的第一个列和第三个列，那么会按照第一个列值在索引里找，找完以后对结果集扫描一遍根据第三个列来过滤，第三个列是不走索引去搜索的，就是有一个额外的过滤的工作，但是还能用到索引，所以也还好，例如：

select * from product where shop_id=1 and gmt_create=’2018-01-01 10:00:00’

就是先根据shop_id=1在索引里找，找到比如100行记录，然后对这100行记录再次扫描一遍，过滤出来gmt_create=’2018-01-01 10:00:00’的行 这个我们在线上系统经常遇到这种情况，就是根据联合索引的前一两个列按索引查，然后后面跟一堆复杂的条件，还有函数啥的，但是只要对索引查找结果过滤就好了，根据线上实践，单表几百万数据量的时候，性能也还不错的，简单SQL也就几ms，复杂SQL也就几百ms。可以接受的。

• （4）没有最左前缀匹配
  那就不行了，那就在搞笑了，一定不会用索引，所以这个错误千万别犯

select * from product where product_id=1;--这个肯定不行

• （5）前缀匹配
  这个就是说，如果你不是等值的，比如=，>=，<=的操作，而是like操作，那么必须要是like ‘XX%’这种才可以用上索引，比如说

select * from product where shop_id=1 and product_id=1 and gmt_create like ‘2018%’

• （6）范围列匹配
  如果你是范围查询，比如>=，<=，between操作，你只能是符合最左前缀的规则才可以范围，范围之后的列就不用索引了

select * from product where shop_id>=1 and product_id=1

这里就在联合索引中根据shop_id来查询了

• （7）包含函数
  如果你对某个列用了函数，比如substring之类的东西，那么那一列不用索引

select * from product where shop_id=1 and 函数(product_id) = 2

上面就根据shop_id在联合索引中查询

3.5 索引的缺点以及使用注意

索引是有缺点的，比如常见的就是会增加磁盘消耗，因为要占用磁盘文件，同时高并发的时候频繁插入和修改索引，会导致性能损耗的。
我们给的建议，尽量创建少的索引，比如说一个表一两个索引，两三个索引，十来个，20个索引，高并发场景下还可以。

字段，status，100行，status就2个值，0和1——你觉得你建立索引还有意义吗？几乎跟全表扫描都差不多了

select * from table where status=1;

相当于是把100行里的50行都扫一遍

你有个id字段，每个id都不太一样，建立个索引，这个时候其实用索引效果就很好，你比如为了定位到某个id的行，其实通过索引二分查找，可以大大减少要扫描的数据量，性能是非常好的
在创建索引的时候，要注意一个选择性的问题，select count(discount(col)) / count(*)，就可以看看选择性，就是这个列的唯一值在总行数的占比，如果过低，就代表这个字段的值其实都差不多，或者很多行的这个值都类似的，那创建索引几乎没什么意义，你搜一个值定位到一大坨行，还得重新扫描。
就是要一个字段的值几乎都不太一样，此时用索引的效果才是最好的
还有一种特殊的索引叫做前缀索引，就是说，某个字段是字符串，很长，如果你要建立索引，最好就对这个字符串的前缀来创建，比如前10个字符这样子，要用前多少位的字符串创建前缀索引，就对不同长度的前缀看看选择性就好了，一般前缀长度越长选择性的值越高。