Mysql索引介绍

什么是索引?我们可以这么理解，索引就好比是一本书的目录，拿一本你不了解的书给你，让你找某一个知识点最快的方式就是先查找目录，定位页数然后直接翻到具体页数。

使用索引的好处

• 避免了检索数据时磁盘的 全表扫描，可以节省扫描时间，可以节省记录排队进入内存的和CPU的时间
• 对于多表连接和嵌套查询更加有利;select * from stu_info where id in(select s_id from marks_info where marks>60)
• 对特定的索引列找出MAX()或MIN()值。

使用索引的缺点

• 索引文件占用磁盘空间
• 如果建立的索引过多，那么扫描，索引目录文件浪费时间过多;
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，MySQL不仅要保存数据，还要保存一下索引文件。

查看索引

• desc tbname;

  Key字段： MUL普通 pri主键 uni唯一

• show index from 表名;//查看表的索引

• explain 分析sql的索引使用情况

Mysql中主要有4类索引

1. 主键索引（primary key）
2. 唯一索引（unique）
3. 常规索引（index）
4. 全文索引（fulltext）

根据记录的特点

• 普通索引：index(常规索引)

• • 表中的任何字段都可以建立普通索引（记录可以为null，记录可以重复）

    Create index 索引名 on 表名(字段名)

  • 常规索引技术是关系型数据查询中最重要的技术，如果要提升数据库的性能，索引优化是首先应该考虑的，因为他能使我们的数据库得到最大性能方面的提升。如果没有索引的数据表，就没有排序的数据集合，要查询数据就需要进行全表扫描。有索引的表是一个在索引列上排序了的数据表，所以可通过索引快速定位记录。

  • 在Myisam数据表中数据行保存在数据文件中，索引保存在索引文件中。但是在Innodb数据表中，数据与索引放在同一个文件中。

  • 常规索引也存在缺点，例如，占用磁盘空间，而且还会减慢在索引数据列上的插入、删除、修改操作，他们也需要按照索引列上的排序格式执行。

  • 因此索引应该创建在搜索、排序、分组等操作所涉及的数据列上。也就是在where子句，多表查询中的from子句、order by 或 group by 子句中出现过的数据列最合适用来创建这种索引。但是不要建太多索引，索引会消耗资源。

• 主键索引: 记录不能为空，记录不能重复

  主键索引是关系型数据库中最常见的索引类型，主要作用是确定数据表中的一条特定的数据的位置。数据表会根据主键的唯一性来唯一标识每条记录，这样可以加快寻址定位的速度，最好每张表都指定一个主键

  create table 表名(id int(5) primary key;

create table test (

​ Id int(5) not null auto_increment,

​ Name varchar(4) not null,

​ Primary key(id)

);

• 唯一索引：unique index

• • 唯一索引的记录可以为空[null]，但是记录不能重复，与主键的不同之处在于每个数据表中只能有一个主键索引，但是可以有多个唯一索引，如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引时，就应该使用 unique 把它定义为一个唯一索引

  • 在有新纪录插入的时候，就会自动检测新纪录的这个字段值，是否已经在某个现有的记录的这个字段里出现过了，如果是，Mysql 将拒绝插入这条记录

  • 其实创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据重复。

    Create unique index 索引名 on 表名(字段名)

根据索引的个数

单列索引,以上讲的都是单列

多列索引

就是索引内容为多个字段，建立一个多列索引就相当于建立多个单列索引,建立两个字段的多列索引就相当于建立了三个索引,在创建索引的时候，尽量创建多列索引。

• 多列普通索引

  create index 索引名 on 表（字段1,字段2）

• 多列唯一索引

  create unique index 索引名 on 表（字段1,字段2）

• 多列主键索引（复合主键）

  错误 :

  create table news(
    id int(2) auto_increment primary key,
    name varchar(20) primary key,
  );
  
  

  正确：

  create table 表名(
    字段1,
    字段2,
    ...
    primary key(字段1，字段2……)
  );
  
  

根据内容索引

根据字段的value长度进行建立索引

• 短索引:

  建立短索引的字段特点: 字符型varchar或者文本型text的字段,字段的内容前半部分出现重复的情况比较少;

  如：河南信阳男子小学门口砍杀24人获死刑，日韩改变态度允许民航向中国通报飞行计划，驻欧盟使团发言人：“法轮功”是反华政治组织，湾强制大陆资本21日内撤出有线电视领域，彭丽媛撰文怀念恩师感谢提携之情 称其为伯乐

  title varchar(120)

  create index 索引名 on 表(字段(20))

  create index title on news(title(20))

• 单列普通短索引

  create index 名 on 表(字段1(长度))

• 单列唯一短索引

  create unique index 名 on 表(字段1(长度))

• 单列主键短索引

  create table aa(

  ​ id int(11) ,

  ​ ...

  ​ primary key(id(3)

  )

• 多列普通短索引

  create index 名 on 表(字段1(长度),字段2(长度))

• 多列唯一短索引

  create unique index 名 on 表(字段1(长度),字段2(长度))

• 多列主键短索引

  create table news_info (

  ​ id int (2),

  ​ title varchar(100),

  ​ author varchar(50),

  ​ content varchar(500),

  ​ primary key(title(20),author(10))

  );

• 全文索引 fulltext index

• • 如果查询的字段的部分内容在该内容中出现率>50%或者表中记录<2条的时候，全文索引没用

  • 50% 全文索引也没用

    Create fulltext index 索引名称 on tbname(column);

  • innodb引擎中没用

  • MYSQL 默认不开启 全文索引

    修改mysql配置文件：window服务器为my.ini，linux服务器为my.cnf，

    在 [mysqld] 后面加入一行“ft_min_word_len=1”，然后重启Mysql。

• 全文索引的使用： 不区分大小写

  Select xxx from tbname WHERE MATCH (索引字段) AGAINST (‘关键字’);

  REPAIR TABLE table_name; 重新加载表内存

Sphinx，dede

删除索引

drop index 索引名 on 表名

索引没有名称怎么删除？

ALTER TABLE table_name DROP PRIMARY KEY

建立索引注意要点

• 检索速度:主键-->唯一-->普通--->没有索引

• 索引内容匹配的长度: 能建立短索引尽量的建立短索引

• 尽量建立多列索引(但是多列索引不能过多)

• 建立的索引(字段)必须是在where后经常频繁出现的字段

• 1. 表的主键、外键必须有索引(主键默认有索引,外键是表与表之间联系的字段)
  2. 数据量超过300条记录的表应该有索引
  3. 经常与其他表进行连接的表，在连接字段上应该建立索引（外键）
  4. 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引
  5. 索引应该建在选择性高的字段上
  6. 对于大的文本字段甚至超长字段应该建立全文索引