21：mysql 索引 - 付杰博客

1、什么是索引 ?

index 中文有索引的意思，我们看的英文书 , 在目录部分 , 就写有 index；数据库帮我们存储数据 , 存储在文件里 , 我们之前也看到了那个文件，MYD 文件帮我存数据 , 当我们取数据的时候 , 我们希望取得比较快；

2、那如何能让数据取的比较快 ?

当数据越来越多 ,MYD 文件则越来越大，假设有 100000 行的数据；

select .. where id=100000

那它应该迅速就来到文件的第 10000 行 , 取出这个数据，那它如何知道 , 这个第 10000 行 , 是在这个 MYD 文件中的第一个字节开始取呢 ? 就是说 , 沿着这个文件 , 我们走多少字节 , 才能到第 10000 行呢？怎么样才能快速帮我们查到呢 , 这个时候就需要靠索引了就像我们数的目录一样 , 我们需要找哪篇文章 , 看一下目录 , 是在第几页 , 直接就能很快的找到文章的所在；

MYI 是索引文件；

3、索引文件是干什么的 ?

它里面也是存储的数据 , 只不过它这个数据 , 是通过某种数据结构帮你高效索引起来的；比如说它存储一个树形结构的 , 找起数据来就非常快速；

例如：有40亿的数据，不用索引来查询 ,40 亿的数据 , 运气不好 , 需要 40 亿此才能查到；用索引 , 当数据 40 亿次的时候 , 它顶多查 32 次就能查到了；索引是高效组织的数据结构；这个就是索引的作用 , 它就是一个书的目录；

4、索引是数据的目录 , 能快速定位行数据的位置；如果我们增加一行数据 , 删除一行数据 , 或者更改一行数据 ,是不是索引也需要相应的操作呢 ?

是的，索引也需要检查更新，检查更新索引也是需要时间的；因此索引提高了查询速度 , 降低了增删改的速度 , 并非加的越多越好；在实际中 , 有可能一张表的索引文件 , 比这张表的数据文件还要大 => 目录比书还厚；一般在查询频率的列上加 , 而且在重复度低列上加效果更好；

mysql索引的类型

1)：key, 普通索引

纯粹就是帮我们组织数据 , 提高查询速度

2)：unique key, 唯一索引

起到 2 个作用；

1. 是加快你的查询速度

2. 是帮你约束数据 , 怎么个约束法 ?这个数据不可能重复了 ,unique 中文唯一的意思；

3)：primary key , 主键索引

一张表中 , 只可能有一个 primary key

4)：fulltext 全文索引

注意：中文环境下 , 全文索引无效；为什么呢?因为英文 , 每个单词是分开的 , 它将每个单词分为别类 , 建立索引；你查这行中的任意一个单词 , 都有可能查询到这行数据；而中文的每个字是连在一起的 , 它无法区分每个字；所以就导致 , 在中文环境下 , 全文索引是无效的；在中文环境下 , 需要分词 + 索引才有效；一般用第三方解决方案 , 如 sphinx等；

5)：设置索引长度

设置索引长度是为了优化索引，建索引时 , 可以只定索引列的前一部分的内容；比如前 10 个字符；

例 :email 列后面都都是 @qq.com,@163.com等；@ 后面的内容区分度不高 , 都是类似的内容；那我们可不可以将 @ 前的 10 个字符内容给截取下来建索引？答案是：当然可以；

create table t24 (
name char(10),
email char(20),
key name(name),
unique key email(email(10))
);

注意：这个并不是限制的 email 长度 , 只能填 10 个字符；而是索引只取 10 个字符；

6)：多列索引

有的时候 , 一列索引还查询不出来什么效果；多列索引 , 就是把 2 列或多列的值 , 看成一个整体 , 然后建索引；

例 :我们注册国外网站的会员时 , 会让我们分别注册firstname,lastname 姓和名分开存姓名 , 可以方便以后查询不同姓氏的注册用户，但大部分使用还是将姓名连接在一起查询的；

注意：firstname,lastname建立了多列索引，如果你单个查firstname，索引是不生效的，多列索引必须多列一起查才会生效；

7)：冗余索引

就是在某个列上 , 可能存在多个索引，比如 xm(xing,ming) 我们单查 ming, 索引不发挥作用；ming(ming) 给 ming 也加上索引 , 单独查 ming 的时候 , 也是有索引发挥作用的 , 那如果 xing 和 ming 都查 , 对于 ming 则会后两个索引发挥作用；在开发中 , 为了提高查询速度 , 我们也经常会用到冗余索引；