MySQL--索引

一个节点对应一个数据页，一个数据页为16K大小。
聚簇索引非叶子节点存储索引键信息和指向下一层面的指针，叶子节点存储的数据页，数据页中是数据行，多行之间用槽分组，叶中通过二分查找数据，
每次读取都需要加载整页、即整个节点的数据到内存中。
普通索引非叶子节点存储索引键信息和指向下一层面的指针，而叶子节点存储关键字和主键id。

https://blog.csdn.net/weixin_35871519/article/details/113303881

B+树的优势：
1.单一节点存储更多的元素（这样该节点下分支变多了，树变矮胖了），使得查询的IO次数更少。
2.所有查询都要查找到叶子节点，查询性能稳定。
3.所有叶子节点形成有序链表，便于范围查询。
唯一索引默认使用B+树索引。

索引分类
单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引
索引列的值必须唯一，但允许有空值
复合索引
即一个索引包含多个列

索引与磁盘
如图为B+Tree

参考：https://www.jianshu.com/p/8991cbca3854

5.3 创建索引的场景
主键自动创建唯一索引
频繁更新的不需要创建索引。
数据重复出现且平均分配，建索引不会有太大的作用。

单表优化案例
创建表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

执行查询：

EXPLAIN SELECT * FROM user WHERE birthday = '2018-01-22' and age>10 ORDER BY age DESC;

执行结果：

发现出现了文件排序。
尝试创建索引：

create index index_birthday_age on user(birthday,age);

创建索引后再次查询：

发现type为范围查询，文件排序没有了。
顺便提一下，创建索引后，查询涉及到队列中第一列为范围，第二列为精确查询，将不会用到索引，由B-Tree结构导致：

EXPLAIN SELECT * FROM user WHERE birthday > '2018-01-22' and age=10 ORDER BY age DESC;

总结：在B-Tree中，索引根据第一列进行排序，如果相同，根据第二列排序 ，如果相同根据第三列进行排序，依次类推，如果用到范围查询，将导致后续无法利用索引再对后面继续检索。

两张表优化案例
在涉及到多表时，连表将不可避免，如left join，连表时，将以左表为单位，通过外键关联子表，这样就会将以左表为核心，全部数据去关联右表，所以往往关联都是右表中的主键id，右表的type类型为eq_ref，即唯一性索引，如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_birthday_age` (`birthday`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

公司表：
CREATE TABLE `company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) DEFAULT NULL,
  `create_id` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

user对应多个company，一对多，
如下查询：

explain select c.* from company AS c LEFT JOIN user As u ON c.create_id=u.id;

所以，往往建索引往往在右表中建立，left join不行可以变换一下采用right join。

总结：
永远用小结果驱动大的结果集。
优先优化子查询，
保证Join语句中被驱动表上Join条件字段已经被索引。

索引失效避免规则
全值匹配我最爱，
最佳左前缀法则，如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。（开头列不存在将会导致索引直接用不上，跳过了将会导致后续的列用不到索引列，口诀：带头大哥不能死，中间兄弟不能断。。。）。
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。（口诀：索引列上少计算）
存储引擎不能使用索引中范围条件右边的列。（范围之后不生效,范围不包括等于号）
尽量使用覆盖索引（只使用索引的查询（索引列和查询列一致）），避免select *，按需取数据。
mysql在使用！=或者<>的时候无法使用索引，会导致全表扫描。
is null,is not null可以使用到索引
like 以通配符开头（’%abc’）mysql索引失效会变成全表扫描的操作。
用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到，
案例：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name_age_createtime` (`name`,`age`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

解决like’%字符串’时索引失效的问题：
采用覆盖索引，即只查询出索引包含的列，如下，我创建了name、age、create_time的联合索引，只取这三个字段：

取主键列时也可用到索引：

如果取的是非索引列的列，则索引失效

字符串不加单引号索引失效。
如图中name为varchar类型，传name=数字1能正常查出来数据，但是mysql底层会发生隐式的转换，导致索引失效，而在1上面加单引号则不会出现该问题。

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `number` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_num` (`number`)
) ENGINE=InnoDB AUTO_INCREMENT=3145665 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- 没有使用到索引
explain 
select * from t_user where name=1

-- 使用到了索引
explain 
select * from t_user where number="1"

-- 3
select 1+"2"

-- 3
select "1"+2

结论：mysql采用向下转换，即字段一个字符串，一个数字，统一转换数字为数字后进行比较，那么如果索引列为字符串，那么就会出现转换，类似于在列上进行了函数操作。

少用or,用它来连接时会索引失效

在timestamp字段上创建索引，扫描的数据多时不走索引

扫描的数据多时不走索引

案例：

总结：
1 where中按不同的列查询是一样的，不一定要按创建索引的顺序，由Mysql查询优化器自动优化，按照创建索引的列的顺序进行查询，如1+2+3和2+1+3是一样的，一般按索引的列顺序来查询，如

2 创建索引不仅仅是为了查询，还有排序。

如图中，三列，在中间的age字段被索引使用到的时候，create_time被用来排序了，但是在order by中列的顺序不能要和创建的索引的列的顺序一致。
3 GROUP BY

分组之前必排序，同时group by 一般会产生临时表，如图中，没有按照索引的顺序来进行排序，会导致临时表和文件排序，但是根据索引的顺序，就不会产生该问题。