什么是索引?我们可以这么理解，索引就好比是一本书的目录，拿一本你不了解的书给你，让你找某一个知识点最快的方式就是先查找目录，定位页数然后直接翻到具体页数。
使用索引的好处
- 避免了检索数据时磁盘的 全表扫描，可以节省扫描时间，可以节省记录排队进入内存的和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类索引
- 主键索引（primary key）
- 唯一索引（unique）
- 常规索引（index）
- 全文索引（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后经常频繁出现的字段
- 表的主键、外键必须有索引(主键默认有索引,外键是表与表之间联系的字段)
- 数据量超过300条记录的表应该有索引
- 经常与其他表进行连接的表，在连接字段上应该建立索引（外键）
- 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引
- 索引应该建在选择性高的字段上
- 对于大的文本字段甚至超长字段应该建立全文索引
网友评论