mysql索引初识一

提示：关于索引的一些笔记

联合索引

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

以该表为例，新建一个联合索引

• 联合索引第一个字段用范围不会走索引，联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不
  如就全表扫描

• 强制走索引force index() 虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表
  扫描高，因为回表效率不高

  EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 A
ND position ='manager'

• 覆盖索引优化,如果select查询的结果集在索引字段中，不需要回表，那此次查询效率就很高

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manag
er';

• in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

• like 'LiLei% 一般情况都会走索引，%在后边一般都会走索引，like 'LiLei%其实就是用到了索引下推优化

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

• 什么是索引下推？
  MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据
• mysql自动选择索引是否使用索引

 EXPLAIN select * from employees where name > 'a';

如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果。而对于mysql是否选择走索引，我们可以用trace工具来一查究竟。开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭。

• trace工具用法
  https://blog.csdn.net/weixin_43364551/article/details/122873356

常见sql深入优化

• Order by与Group by优化
  case1

EXPLAIN SELECT * FROM employees WHERE name ='LiLei' AND posotion=  'dev' order by age；

利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

case2

EXPLAIN SELECT * FROM employees WHERE name ='LiLei' order by posotion；

查询使用了name索引，由于用了position进行排序，跳过了
age，出现了Using filesort。

case3

EXPLAIN SELECT * FROM employees WHERE name ='LiLei' order by age,posotion；

查找只用到索引name，age和position用于排序，无Using filesort

case4

EXPLAIN SELECT * FROM employees WHERE name ='LiLei' order by posotion,age；

出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了

case5

EXPLAIN SELECT * FROM employees WHERE name ='LiLei' and age=18 order by posotion,age；

并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort

case6

EXPLAIN SELECT * FROM employees WHERE name in('LiLei','Zhangsan')  order by age,posotion；

对于排序来说，多个相等条件也是范围查询

优化总结

• 1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index
  效率高，filesort效率低。
• 2、order by满足两种情况会使用Using index。
  • 2.1order by语句使用索引最左前列。
  • 2.2使用where子句与order by子句条件列组合满足索引最左前列。
• 3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
• 4、如果order by的条件不在索引列上，就会产生Using filesort。
• 5、能用覆盖索引尽量用覆盖索引
• 6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group
  by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

索引设计原则

• 代码先行，索引后上,一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。
• 联合索引尽量覆盖条件，比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
• 不要在小基数字段上建立索引,索引基数是指这个字段在表里总共有多少个不同的值,比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。
• 长字符串我们可以采用前缀索引,对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。
• where与order by冲突时优先where,在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?
  一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。
  因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可
  能会小很多。
• 基于慢sql查询做优化

、