3：索引最佳实践（常用）

目录与学习目标

1：前置建表语句_DDL
2：回顾上节的索引结构图以及key_len的计算
3：全值匹配（联合索引）
4：最左前缀法则（联合索引）
5：存储引擎不能使用索引中范围条件  右边的列  （联合索引）
6：尽量使用覆盖索引 （联合索引）
7：不在索引列上做任何操作
8：like以通配符开头
9：字符串不加单引号索引失效
10：mysql在使用范围查询有时候导致的无法使用索引
11：范围查询优化    
12：小总结

1：前置建表语句_DDL

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 = 4 DEFAULT CHARSET = utf8 COMMENT = '员工记录表';
INSERT INTO employees ( NAME, age, position, hire_time ) VALUES    ('LiLei',22,'manager',NOW());
INSERT INTO employees ( NAME, age, position, hire_time ) VALUES ('HanMeimei',23,'dev',NOW());
INSERT INTO employees ( NAME, age, position, hire_time ) VALUES ('Lucy',23,'dev',NOW());

2：回顾上节的索引结构图以及key_len的计算

索引必须排好序  如果不限制 name  那么在整张表里面age并没有排好序。
即是：name中排好序后，再到相同name下的age排好序，最后才到相同name下的相同的age下的position排序。

key_len计算规则如下(了解即可)：
    字符串: char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，
            如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节        
    char(n)：如果存汉字长度就是 3n 字节
    varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，
                  因为varchar是变长字符串。
    数值类型：
        tinyint：1字节
        smallint：2字节
        int：4字节
        bigint：8字节
   时间类型：
     date：3字节
     timestamp：4字节
     datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

3：全值匹配（联合索引）

KEY `idx_name_age_position` ( `name`, `age`, `position` ) USING BTREE 
全值匹配 匹配的索引数量越多 那么查询的效率也就越高
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei'；
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
name设置的Varchar的大小是24
24*3 + 2 = 74

再加一个int类型  74+4 =78

20*3+2 = 62  78+62 = 140

4：最左前缀法则（联合索引）

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
KEY `idx_name_age_position` ( `name`, `age`, `position` ) USING BTREE 
因为 age  在多个索引子页中可能有多个  也就导致了实际上age是全局无序的
只有当 age限制在 name下面  那么这个age在全局是无序的
但在这name的索引子页里面是有序的
EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31; （走索引）
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';（不走）
EXPLAIN SELECT * FROM employees WHERE position = 'manager';（不走）

5：存储引擎不能使用索引中范围条件 右边的列 （联合索引）

SQL1的position走索引 
SQL2的position没走索引 
1：先查找name的索引
2：查找该name下面的age的索引
3：由于age是一个范围的  age = 23  age = 24   age=25
4：假如   age=23  age =24  age =25 下面全都有 manager  manager1  manager2
5：那么 manager 已经是在age > 22 下面 已经变成无序的了
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

6：尽量使用覆盖索引 （联合索引）

尽量只访问索引的查询（索引列包含查询列），减少 select *语句
EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

7：不在索引列上做任何操作

在做操作之后（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
修改name的字段之后   就在索引树里面找不到这个name了
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';
给hire_time增加一个普通索引：
ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
使用date() 函数转换  已经在查询的修改这个数据的本身 因此一定不会走索引
EXPLAIN select * from employees where date(hire_time) ='2018‐09‐30';
转化为日期范围查询，有可能会走索引：
 EXPLAIN select * from employees where hire_time >='2018‐09‐30 00:00:00' and hire_time <='2018‐09‐30 23:59:59';
还原最初索引状态
ALTER TABLE `employees` DROP INDEX `idx_hire_time`;

8：like以通配符开头

（'$abc...'）mysql索引失效会变成全表扫描操作 

 百分号在前面的话 代表着前面的还有字符   直接就乱序了
 百分号在后面的话 代表着后面的还有字符  还能可以走索引的前缀的

 EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

 EXPLAIN SELECT * FROM employees WHERE name like 'Lei%

解决方案：
1：使用覆盖索引，建议查询字段建立覆盖索引字段  （那么这个时候查询的就是 联合索引）
2：如果不能使用覆盖索引则可能需要借助搜索引擎

9：字符串不加单引号索引失效

Mysql有可能可以帮忙做转换

EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;

10：mysql在使用范围查询有时候导致的无法使用索引

mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引（后面会详细说明）

在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描
< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

 EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

 is null,is not null 一般情况下也无法使用索引
 EXPLAIN SELECT * FROM employees WHERE name is null

少用or或in，用它查询时，mysql不一定使用索引，
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

11：范围查询优化

mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。

       注意：评估的结果：
    1：走索引：获取到该索引的主键ID  通过主键ID回表查询
    2：不走索引：进行全表查

给age新增索引：
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;

explain select * from employees where age >=1 and age <=2000;

可能使用 idx_age索引  但最终还是没有使用

优化方法：可以将大的范围拆分成多个小范围

    explain select * from employees where age >=1 and age <=1000;
    explain select * from employees where age >=1001 and age <=2000;    

     mysql觉得：第一个需要走全表扫描  而第二个才需要走索引

还原最初索引状态
    ALTER TABLE `employees` DROP INDEX `idx_age`;

12：小总结

like KK%相当于=常量，%KK和%KK% 相当于范围

特别注意：
    where a = 3 and b like 'k%kk%' and c =4

有用到 a b c 的索引 
1：a肯定用到
2：b like ‘k%’ 这个时候不会去管 k后面有什么字符 会直接把 第一个字母等于 k 的都找出来
3：此时已经找到 a 下面 前缀为 k 的所有数据
4：然后 在这个时候  k（整体）下面的 c（整体）已经是排好序了  

最后这步（第4步）这里暂时先认为是 mysql做的一个优化  （而范围查找没有做这个优化）