平时项目中也不太注重索引有没有生效，也对索引命中规则没有明确的认识，所以自己来测试一下。
创建表
订单表
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
`id` int(32) NOT NULL AUTO_INCREMENT,
`create_time` timestamp NULL DEFAULT NULL,
`update_time` timestamp NULL DEFAULT NULL,
`order_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`amount` decimal(10,0) DEFAULT NULL,
`payer_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`receiver_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`pay_type` tinyint(1) DEFAULT NULL,
`pay_time` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_order_orderId` (`order_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=200002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;订单详情表
DROP TABLE IF EXISTS `order_detail`;
CREATE TABLE `order_detail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`order_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`column1` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
`column2` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`column3` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`column4` tinyint(32) DEFAULT NULL,
`column5` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
`column6` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`column7` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`column8` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`column9` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`column10` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=300001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;两张表通过order_id字段来关联。
造假数据
创建存储过程并执行
- 往order表插入数据
drop procedure if exists data_insert;
DELIMITER $$
CREATE PROCEDURE data_insert()
BEGIN
DECLARE i int unsigned DEFAULT 0 ;
WHILE i<100000 DO
INSERT into `order` VALUES(i,SYSDATE(),SYSDATE(),CONCAT('0000000000',i),i,CONCAT('aaaaaaaa',i),CONCAT('bbbbbbbb',i),0,SYSDATE());
SET i = i+1;
END WHILE;
END $$
DELIMITER ;
CALL data_insert- 往order_detail表插入数据
DELIMITER $$
CREATE PROCEDURE `data_detail_insert`()
BEGIN
DECLARE i int unsigned DEFAULT 0 ;
DECLARE j int UNSIGNED default 0;
WHILE i<100000 DO
WHILE j<3 DO
INSERT into order_detail(create_time,update_time,order_id,column1,column2,column3,column4,column5,column6,column7,column8,column9,column10) VALUES(SYSDATE(),SYSDATE(),CONCAT('0000000000',i),j,j,j,j,j,j,j,j,j,j);
set j = j+1;
END WHILE;
set j=0;
SET i = i+1;
END WHILE;
END $$
DELIMITER ;
call data_detail_insert;备注：存储过程解释：先删除存储过程，如果存在的话 创建存储过程：由于存储过程是一系列的语句集，而sql默认是以;表示语句的结束，所以将$$符号定义为语句的结束标志，该符号可以任意。通过该存储过程往order表插入10w条数据，往order_detail表插入30w条数据，1对多。
创建索引
为order表创建联合索引（b+tree）。使用的列为order_id,payer_id,receiver_id
ALTER TABLE `order` ADD INDEX `IDX_ORDERDETAIL_ORDERID_PAYERID_RECEIVERID` USING BTREE(`order_id`, `payer_id`, `receiver_id`);为order_detail表建立单列索引（b+tree）。使用的列为order_id。
ALTER TABLE `order_detail` ADD INDEX `IDX_ORDER_ORDERID` USING BTREE(`order_id`);无索引：select * from order_detail where order_id = '000000000010'；执行时间为0.195s。
加了索引：order_id列加了普通索引，使用的是b+树，同样的语句，执行时间为0.001s。
通过explain可以看到，用到了索引，type为ref，扫描的行数为3行。
执行计划
现在我们来具体了解一下,MySQL执行计划中,每个属性代表的是什么意思。
- id: 表示查询中select操作表的顺序,按顺序从大到依次执行（不是表中的自增主键！）id值相同执行顺序从上到下。id值不同时id值大的先执行。
- select_type:这一列显示了对应行是简单还是复杂SELECT.取值如下:SIMPLE值意味着查询不包括子查询和UNION。查询有任何复杂的子部分，则最外层标记为PRIMARY.取值如下：
- type:该属性表示访问类型,有很多种访问类型。最常见的其中包括以下几种: ALL(全表扫描), index(索引扫描),range(范围扫描),ref (非唯一索引扫描),eq_ref(唯一索引扫描,),(const)常数引用, 访问速度依次由慢到快。其中 : range(范围)常见于 between and …, 大于 and 小于这种情况。提示 : 慢SQL是否走索引,走了什么索引,也就可以通过该属性查看了。
table:输出数据行所在的表的名称
possible_keys 顾名思义,指出MySQL能使用哪些索引来优化查询，查询所涉及的列上的索引都会被列出，但不一定会被使用，算是个提示作用
key:显示MySQL实际使用的索引,其中就包括主键索引(PRIMARY),或者自建索引的名字。如果没有可用的索引，则显示为NULL
key_len:表示索引字段的最大可能长度，KEY_LEN的长度由字段定义计算而来，并非数据的实际长度，当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中 MySQL 会真正使用了哪些索引。
ref :连接匹配条件,如果走主键索引的话,该值为: const, 全表扫描的话,为null值表示哪些列或常量被用于查找索引列上的值
rows :扫描行数,也就是说，需要扫描多少行,才能获取目标行数,一般情况下会大于返回行数。通常情况下,rows越小,效率越高, 也就有大部分SQL优化，都是在减少这个值的大小。注意: 理想情况下扫描的行数与实际返回行数理论上是一致的,但这种情况及其少,如关联查询,扫描的行数就会比返回行数大大增加
Extra:该属性中包括执行SQL时的真实情况信息,以下将介绍它的几种值，每个值都代表你的SQL语句的缺陷：
Using filesort：主要出现在 order by 排序、复合索引跨列；出现原因：where条件是a列，却根据b列排序。所以对于单索引，如果排序和查找是同一个字段，则不会出现using filesort;如果排序和查询不是同一个字段，那就会出现using filesort。避免策略：你where那些字段，就order by那些字段~
Useing temporary:主要出现在：group by分组中,它同样也是性能损耗较大，用到了临时表。和Using filesort相似，where条件是a列，却用b列来分组。
Using index：查询的列被索引覆盖：using index表示这条sql性能提升了，此次查询，不读取源文件，只从索引文件中获取数据（不需要回表查询）；
Using where：查询的列被索引覆盖，并且where筛选条件是索引列的一个范围或者索引没有生效。
Using index condition：是MySQL5.6添加的，用于优化数据查询（条件下推）。
条件下推：没有条件下推之前，先读取索引信息，根据索引中的id将整条数据读取出来，在通过where里面的条件判断数据是否符合，符合的话返回；优化之后，先读取索引信息，在根据用到的索引列判断是否符合条件，如果符合的话把数据读出来，再根据其他的条件判断是否符合要求，符合的话返回数据。
索引命中规则：
看一下索引的一些口诀：
全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
LIKE符号写最右，覆盖索引不写星
不等空值还有or，索引失效要少用
var引号不能丢，SQL高级也不难
分组之前必排序，一定要上索引啊
解释一下索引失效的口诀
- 全值匹配我最爱：建立几个复合索引字段，最好就用上几个字段。且按照顺序来用。
- 最左前缀要遵守，带头大哥不能死
索引列的三个条件都存在时：
不存在索引列的第一个列时，索引失效：
- 中间兄弟不能断：
当索引的中间列不作为条件时，索引只会命中前面的索引列。通过key_len可以看出。
- 索引列上少计算：在索引列上计算会导致索引失效，全表扫描
- 范围之后全失效：在索引的某个列进行返回查询后，之后的列索引将会失效
在中间的索引列用了范围查询:
在最后一列用了范围查询后：
通过key_len的变化，可以看出中间列作为范围查询后，后面的索引就失效了：
附索引key_len计算规则：
备注：我的表用的字符集是utf8mb4，所以varchar的长度为4个字节。varchar(32)*4+2+1 = 131。key_len=262说明联合索引中只有两列用到了索引。
- LIKE符号写最右：百分号写在后面的时候是可以命中索引的，如果写在前面则会是索引失效
百分号写右边：
百分号写左边：会导致索引失效，造成全表扫描
- 覆盖索引不写星
不写select * 的情况下，extra： using where ,using index；
如果写*的情况下：extra： using where。
- 不等空值还有or，索引失效要少用
不等于：导致索引失效
is not null：索引失效；is null 的时候索引有效？（口诀这条不适用于mysql吗还是测试有误呢？待考证）
or：如果or的条件中有非索引列，有索引列的条件索引也会失效。
- varchar引号不能丢:如果索引是字符串类型的，则条件也必须是字符串类型的，否则会导致全表扫描
索引建立原则
- 最适合索引的列是出现在 WHERE子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。使用惟一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。
例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“ M”和“F”，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。
- 如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。
例如，如果有一个 CHAR(200)列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 IO 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
- 利用最左前缀
在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
- 不要过度索引
不要以为索引“越多越好”，什么东西都用索引是错误的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。
附查看索引创建语句的sql：
SELECT
CONCAT('ALTER TABLE `',TABLE_NAME,'` ', 'ADD ',
IF( any_value(NON_UNIQUE )= 1,
CASE UPPER(any_value(INDEX_TYPE))
WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
ELSE CONCAT('INDEX `',
INDEX_NAME,
'` USING ',
any_value(INDEX_TYPE)
)
END,
IF(UPPER(INDEX_NAME) = 'PRIMARY',
CONCAT('PRIMARY KEY USING ',
any_value(INDEX_TYPE)
),
CONCAT('UNIQUE INDEX `',
INDEX_NAME,
'` USING ',
any_value(INDEX_TYPE)
)
)
),'(', GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`') ORDER BY SEQ_IN_INDEX ASC SEPARATOR ', '), ');') AS 'Show_Add_Indexes'
FROM information_schema.STATISTICS
WHERE TABLE_NAME='order'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME ASC, INDEX_NAME ASC
