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~1 select match('description') against('keyword') from tab;

索引数据结构 B-Tree

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

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

聚集索引，聚簇索引

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

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