mysql索引

索引

四种索引类型

• B-Tree索引
• Hash索引：只有memory引擎支持
• R-Tree索引(空间索引):空间索引是MyISAM的一个特殊索引类型
• Full-text索引(全文索引)：全文索引也是MyISAM的一个特殊索引类型，InnoDB从5.6版本开始提供全文索引的支持

索引	MyISAM	InnoDB	Memory
B-Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-Tree索引(空间索引)	不支持	支持	不支持
Full-text索引(全文索引)	支持	暂不支持	不支持

注：hash索引合适key-value查询，不适合范围查询。如果使用Memory/Heap引擎，where条件中只有使用了”=”才会使用索引，否则不使用索引。

使用索引的场景

• 匹配全值：对索引中所有列都指定具体值
• 匹配值的范围查询：对索引的值能够进行范围查找
• 匹配最左前缀
• 仅仅对索引进行查询
• 匹配列前缀，仅仅使用索引中的第一列
• 索引匹配部分精确，其他部分范围匹配
• ICP（Index Condition Pushdown）,复合索引

存在索引但不能使用索引的典型场景

• 以%开头的like查询不能够利用B-Tree索引，执行计划中key的值为null表示没有使用索引
• 数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在where条件中把字符常量值用引号引起来，否则即便这个列上有索引，mysql也不会用到，因为mysql默认把输入的常量值进行转换以后才进行检索。
• 复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则leftmost,是不会使用复合索引的
• 如果mysql估计使用索引比全表扫描更慢，则不使用索引。
• 用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

查看索引使用情况

show status like 'Handler_read%';

Handler_read_key的值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高。Handler_read_rnd_next的值高则意味着查询运行低效，而且应该建立索引补救。这个值的含义是数据文件中读下一行的请求数。

定期分析表和检查表

analyze [local|no_write_to_binlog] table tbl_name

定期优化表

optimize [local|no_write_to_binlog] table tbl_name;

注意：analyze，check，optimize，alter table执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。

MyISAM和InnoDB存储引擎默认创建都是BTREE索引，mysql目前不支持函数索引，但是支持前缀索引。只有MyISAM储存引擎支持FULLTEXT索引。默认情况下，MEMORY存储引擎使用HASH索引，但也支持BTREE索引。

设计索引原则

• 搜索的索引列，不一定是所要选择的列。最合适索引的列是出现在where子句中的列，不是select关键字后的列
• 使用唯一索引。索引的列基数越大，索引效果越好。
• 使用短索引。字符串列进行索引，应制定一个前缀长度
• 利用最左前缀。
• 不要过度索引。

btree索引 vs hash索引

hash索引特征

• 只用于使用=或者<=>操作符的等式比较
• 优化器不能使用hash索引来加速order by操作
• mysql不能确定在两个值之间大约有多少行，如果将一个MyISAM表改为hash索引的memory表，会影响一些查询的执行效率。
• 只能使用整个关键字来搜索一行

对于btree索引，当使用>、<、>=、<=、between、!=、<>或者like操作符时，都可以使用相关列上的索引。

主键索引

A表

CREATE TABLE `a` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`message_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`msg` varchar(1024) DEFAULT NULL,
`gmt_create` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`,`message_id`),
KEY `idx_gmt_create` (`gmt_create`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

B表

CREATE TABLE `b` (
`user_id` int(11) NOT NULL,
`message_id` int(11) NOT NULL,
`msg` varchar(1024) DEFAULT NULL,
`gmt_create` datetime NOT NULL,
PRIMARY KEY (`user_id`,`message_id`),
KEY `idx_gmt_create` (`gmt_create`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

记录	空间	优点	缺点	使用场景
A表	500万	509M	主键ID自增,在写入数据的时候,Btree分裂成本低,写性能高	物理空间相对较多如果根据user_id来查记录,需要走两次IO
B表	500万	361M	1.物理空间相对减少 2.根据user_id查数据,直接走主键拿到数据，无需回表	(user_id,message_id)为随机写入,Btree分裂成本高,写性能低

字符串隐式转换

需要注意字符串隐式转换，有时候，字符串隐式转换后，索引就会失效，从而会进行全表遍历，直接影响性能。比如1隐式转换成’0001’