索引

https://dev.mysql.com/doc/refman/5.6/en/create-index.html

https://dev.mysql.com/doc/refman/5.6/en/index-btree-hash.html

语法：

CREATE [ONLINE | OFFLINE] [UNIQUE | FULLTEXT | SPATIAL] 
INDEX index_name [index_type]
ON tbl_name (key_part,...) [index_option] [algorithm_option | lock_option] ...

Storage Engine	Permissible Index Types
InnoDB	BTREE
MyISAM	BTREE
MEMORY/HEAP	HASH, BTREE
NDB	HASH, BTREE (see note in text)

 

目前能用到的引擎InnoDB和MyISAM 的索引类型只有 BTREE

BTREE 支持列比较  = ，> , >= , < , <= , between , like

HASH 只支持 = ， <=> 

关于like 特别说明：

必须常量

必须常量开头+%

‘a’ < 'b'

'aa' < 'b'

 

测试建表语句：

DROP TABLE IF EXISTS `tbl_name`;
CREATE TABLE `tbl_name` (
  `key_col` varchar(10) NOT NULL,
  `index_part1` int(10) DEFAULT NULL,
  `index_part2` int(10) DEFAULT NULL,
  `index_part3` int(10) DEFAULT NULL,
  `index` int(10) DEFAULT NULL,
  `index1` int(10) DEFAULT NULL,
  `index2` int(10) DEFAULT NULL,
  `index3` int(10) DEFAULT NULL,
  `A` int(10) DEFAULT NULL,
  `other_column` int(10) DEFAULT NULL,
  PRIMARY KEY (`key_col`),
  KEY `idx_123` (`index_part1`,`index_part2`,`index_part3`),
  KEY `idx` (`index`),
  KEY `idx1` (`index1`),
  KEY `idx2` (`index2`),
  KEY `idx3` (`index3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

索引生效：

EXPLAIN SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'; # 'Patrick' <= key_col < 'Patricl'

 

 

EXPLAIN SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'; # 'Pat' <= key_col < 'Pau'

 

 

 

EXPLAIN SELECT * FROM tbl_name WHERE index_part1=1 AND index_part2=2 AND other_column=3; # 联合索引 part1 和 part2 生效

 

 

explain select * from tbl_name  WHERE index_part1 > 1 

 

 

explain select * from tbl_name  WHERE index_part1 > 1 and index_part2 = 2 # 只有part1 生效

 

 

explain select * from tbl_name  WHERE index1 = 1 and index2 = 2 # 只有index1 生效

 

 

 

 

explain select * from tbl_name  WHERE index2 = 1 and index1 = 2 # AND 连接独立索引，顺序无影响，自动优化

 

 

 

索引失效：

1）字段类型不匹配，举例： varchar 字段 的 where 条件 one_varchar_column = 1  （这个不生效，需要修改为 = ‘1’）

2) where 条件中使用了 or （版本 5.6 ，table engine is InnoDB）

 

Tips:

1) 联合索引遵循 leftmost prefix （从最左连续至右，遇到范围则终止后续）

2）EXPLAIN Output Columns 中的 key 表明使用的是哪个索引

3）key_len 与 create index 的 key_part 有关，utf-8 字符集的 varchar(10) 可 null  的 len 是 (10 + 1) * 3 = 33 byte

4) [Err] 1071 - Specified key was too long; max key length is 767 bytes （utf-8 唯一索引最大长度 255 ， 具体最大长度需要看引擎和版本）

 

 5） 官方文档开阔视野 InnoDB Limits