MYSQL索引-学习笔记

什么是索引：

帮助mysql进行高效查询的数据结构（有序），在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引（换言之，索引就是某种数据结构）。如下图所示：

左边是数据表，一共7行记录；最左边记录了数据的物理地址（注意逻辑上相邻记录的数据在物理上不一定是相邻的）。为了加快col2的查找效率，可以维护右侧所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录的物理地址指针，这样就可以利用二叉查找树高效查找
一般来说，索引本身也很大，不可能全部储存到内存中，因此，索引往往以文件形式储存到磁盘上。

索引数据结构

二叉查找树

二叉平衡树

红黑树

B树

B+树

Hash

索引分类

主键索引

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,  // 自增主键
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),   // 主键索引
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

唯一索引/联合唯一索引

唯一索引：

create UNIQUE index "test1_flag_idx" on test1(flag);

联合唯一索引：

create UNIQUE index "test1_flag_flag2_unique_idx" on 
test1(flag, flag2);

普通索引

create index "test1_flag_idx" on test1(flag);

局部索引

create index "test1_flag_idx" on test1(flag) 
where flag='read';

复合索引

CREATE INDEX test1_flag_flag2_idx ON public.test1 USING btree (flag1, flag2);

全文索引

CREATE fulltext INDEX test1_flag_fulltext_idx ON public.test1 USING btree (flag1);

索引失效

• 如果条件中有or，即使其中有部分条件带索引也可能不会使用(这也是为什么尽量少用or的原因)，例子中user_id无索引
  注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
• 对于复合索引，如果不使用前列，后续列也将无法使用，类电话簿。 like查询是以%开头
• 存在索引列的数据类型隐形转换，则用不上索引，比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
• where 子句里对索引列上有数学运算，用不上索引
• where 子句里对有索引列使用函数，用不上索引
• 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
• where 子句里对索引列使用不等于（<>），使用索引效果很一般

Explain工具

MySQL Explain详解.

索引带来的弊端

影响数据库写操作的效率（更新索引的数据）