mysql 索引

索引

利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。
索引的关键字一定是排序的。

类型

4种类型：
主索引，唯一索引，普通索引，全文索引。
无论任何类型，都是通过建立关键字与位置的对应关系来实现的。
以上类型的差异：对索引关键字的要求不同。
关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）。

类型 语法 定义 普通索引 index 对关键字没有要求 唯一索引 unique index 要求关键字不能重复。同时增加唯一约束。 主键索引 primary key 要求关键字不能重复，也不能为NULL。同时增加主键约束。 全文索引 fulltext index 关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。

关键字的来源可以是某个字段，也可以是某些字段。如果一个索引通过在多个字段上提取的关键字，称之为 复合索引。前缀索引，建立索引关键字一种方案。通常会使用字段的整体作为索引关键字。有时，即使使用字段前部分数据，也可以去识别某些记录。使用index_name前N个字符建立的索引。使用N长度所达到的辨识度，极限接近于使用全部长度的辨识度即可！

Index `index_name` (`index_field`(N)) --N长度选取方法--得出该字段的辨识度 10000为表记录总数select 10000/count(distinct username) from tab;--得出前N个字符所占的辨识度，越接近上面的越好select 10000/count(distinct substring(username,1,5) from tab;

管理

创建

create table index_demo(    id int unsigned not null,    name varchar(64) not null default '',    age int not null default '',    num varchar(12) not null default '',    description text not null default '',    primary key (`id`), -- 主索引    index `name` (`name`), -- 普通索引    unique index `num` (`num`), -- 唯一索引    fulltext index `des` ( `description`) -- 全文索引)engine=myisam charset=utf8；

索引可以起名字，但是主索引不能起名字，因为一个表仅仅可以有一个主索引，auto_increment依赖于KEY，其他索引可以出现多个。名字可以省略，mysql会默认生成，通常使用字段名来充当。

更新

alter table index_demo    add primary key (`id`),    add index `name` (`name`),    add unique index `num` (`num`),    add fulltext index `des` ( `description`);

1， 如果表中存在数据，数据符合唯一或主键的约束才可能创建成功。
2， Auto_increment属性，依赖于一个KEY。

删除

alter table index_demo    drop primary key,    drop index `name`,    drop index `num`,    drop index `des`;

Tip:可以通过在select语句前使用 explain，来获取该查询语句的执行计划，而不是真正执行该语句。通过explain可以知道mysql是怎样执行sql语句的，是否会使用到索引，以及使用到哪个索引。

索引使用场景：

• 索引检索
  常用在select上的where条件过滤
• 索引排序
  如果order by 排序需要的字段，上存在索引，可能使用到索引。
• 索引覆盖
  索引拥有的关键字内容，覆盖了查询所需要的全部数据，此时，就不需要在数据区获取数据，仅仅在索引区即可。

建立索引索引时，不要仅仅考虑where检索了吧，同时考虑其他的使用场景。（在所有的where字段上增加索引，就是不合理的）

索引使用原则

索引存在，没有满足使用原则，导致索引无效
建立索引时，建立满足使用原则的字段上。

列独立

如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧。

左原则

Like：匹配模式必须要左边确定不能以通配符开头。
业务逻辑上出现: field like ‘%keywork%’;类似查询，需要使用全文索引。

复合索引

一个索引关联多个字段。
仅仅针对左边字段有效果。

OR的使用

必须要保证 OR 两端的条件都存在可以用的索引，该查询才可以使用索引。

MySQL智能选择

即使满足了上面说原则，MySQL也能弃用索引 。
弃用索引的主要原因：
查询即使使用索引，会导致出现大量的 随机IO，相对于从数据记录的第一条遍历到最后一条的顺序IO开销，还要大。

全文索引

MYSQL提供的全文索引，不能对中文起作用！
该类型的索引特殊在：关键字的创建上。
为了解决 like ‘%keyword%’这类不符合左原则查询的匹配问题。

--match(匹配字段) against(目标关键字)--全文索引索引的的关键字，不是整个字段数据，而是从数据中提取的关键词。跟 where description like '%keyword%' 搜索原则不一样;select * from tab where match('description') against('keyword');--返回记录的匹配度 0~1select match('description') against('keyword') from tab;

索引数据结构 B-Tree

B-Tree的结构如下：
每个节点，存储多个关键字。关键字也会对应记录地址。
以上设计为了解决，一次性磁盘IO开销，可以读取到更多的关键字数量。
每个关键字之间，存在子节点指针。

如果是复合索引：
关键字的排序先排左侧字段，在左侧字段相同的情况下，再排序右侧字段，这就是为什么存在左原则的情况。

聚集索引，聚簇索引

在innodb的存储引擎上，主索引是与数据记录 存储在一起的（聚簇在一起的）。

带来的问题：
Innodb的其他索引，非主键索引（二级索引）：
关键字对应的不再是记录的地址，而是记录的主键。
检索需要 二次检索。先检索到主键ID，再检索记录。