mysql索引使用 三

引擎
Mysql引擎常用的有两种，MyIsam和InnoDB。默认是InnoDB

Sql执行计划

Explain + sql语句 查看执行计划

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
SIMPLE 查询对应的表名 REF
RANGE 可能用到的索引 真正用到的索引 使用索引的长度

1. id越大，优先执行。Id相同，从上往下执行。
2. select_type：因为现在都是分库分表，所以我们的sql都是很简单的一个查询语句，基本没有什么联合查询或者子查询。所以select_type一般是SIMPLE，表示简单的SELECT语句。
3. type：
   system > const > eq_ref > ref > range > index > ALL

1. System：主键或唯一索引查找常量值，只有一条记录，并且是MyISAM引擎。
   示例：
   CREATE TABLE employee_myisam (
   id int(11) NOT NULL AUTO_INCREMENT,
   first_name varchar(20) COLLATE utf8_bin DEFAULT NULL,
   last_name varchar(30) COLLATE utf8_bin DEFAULT NULL,
   gender int(2) DEFAULT NULL,
   email varchar(50) COLLATE utf8_bin DEFAULT NULL,
   PRIMARY KEY (id)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
   插入一条记录。

explain select * from employee_myisam;—没用主键的查询，type也是SYSTEM。
±—±------------±----------------±-----------±-------±--------------±-----±--------±-----±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±----------------±-----------±-------±--------------±-----±--------±-----±-----±---------±------+
| 1 | SIMPLE | employee_myisam | NULL | system | NULL | NULL | NULL | NULL | 1 | 100 | NULL |
±—±------------±----------------±-----------±-------±--------------±-----±--------±-----±-----±---------±------+

explain select * from employee_myisam where id = 1; —用主键的查询，type也是SYSTEM。
±—±------------±----------------±-----------±-------±--------------±-----±--------±-----±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±----------------±-----------±-------±--------------±-----±--------±-----±-----±---------±------+
| 1 | SIMPLE | employee_myisam | NULL | system | PRIMARY | NULL | NULL | NULL | 1 | 100 | NULL |
±—±------------±----------------±-----------±-------±--------------±-----±--------±-----±-----±---------±------+
2) CONST：命中主键或者唯一索引

但是SYSTEM和CONST一般我们达不到，我们的优化目标就是达到REF。
3) REF
4) RANGE：只要有BETWEEN 、 IN 、 > 等，就会有RANGE。
4. key_len
int null 4+1
int not null 4
long
tinyint 1
bigint 8
char+为空 303+1
char+不为空 303
varchar+为空 303+2+1
varchar+不为空 303+2

5. Extra
   using filesort，有order by 就会出现
   using where，有where条件，就会出现
   using index

实例：

CREATE TABLE s_province (
PROVINCE_ID int(11) DEFAULT NULL,
PROVINCE_NAME varchar(255) COLLATE utf8_bin DEFAULT NULL,
COUNTRY_ID tinyint(3) NOT NULL DEFAULT ‘1’ COMMENT ‘ZH’,
ADD_TIME varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

EXPLAIN SELECT * FROM S_PROVINCE WHERE COUNTRY_ID = 1 ORDER BY PROVINCE_ID ASC;
±—±------------±-----------±-----------±-----±--------------±-----±--------±-----±-----±---------±----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±-----------±-----------±-----±--------------±-----±--------±-----±-----±---------±----------------------------+
| 1 | SIMPLE | S_PROVINCE | NULL | ALL | NULL | NULL | NULL | NULL | 34 | 10 | Using where; Using filesort

因为需要使用COUNTRY_ID做为查询条件，所以给COUNTRY_ID增加索引。
EXPLAIN SELECT * FROM S_PROVINCE WHERE COUNTRY_ID = 1 ORDER BY PROVINCE_ID ASC;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | s_province | NULL | ref | IDEX_COUNTRYID | IDEX_COUNTRYID | 5 | const | 74 | 100.00 | Using index condition |

一般的优化，就是让type达到ref，extra出现using index。

索引失效原则：

1. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
2. or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
3. 复合索引（column1，column2，column3），必须复合最左原则，不要夸列使用，否则索引失效。
4. 数据类型出现隐式转化，索引失效。比如字段是varchar类型，然后sql拼接未加单引号。
5. 在索引字段上使用not，<>，!=。优化方案如：key>0 or key<0
6. 对索引字段进行计算操作、字段上使用函数。

order by优化策略:

7. 选择使用单路排序或双路排序，调整buffer大小
8. 避免select *
9. 排序字段，保证排序的一致性（全升序或者全降序）