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

建表语句

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());

回顾上节（key_len的计算）：

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会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

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

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

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

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
    
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';（不走）

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

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';

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

尽量只访问索引的查询（索引列包含查询列），减少 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';

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

在做操作之后（计算、函数、（自动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`;

6：like以通配符开头

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

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

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

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

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

Mysql有可能可以帮忙做转换

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

8：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';

9：范围查询优化

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`;

10：小总结

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做的一个优化  （而范围查找没有做这个优化）