MySQL学习_索引_入门理解

1、索引

索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。
索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

1.1、索引选取类型

• 1、越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
• 2、简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。
• 3、尽量避免NULL：应该指定列为NOT nuLL,在MySQL中， 含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂

1.2、什么场景不适合创建索引

• 第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因 为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
• 第二，对于那 些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了，所以没有必要。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比 例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
• 第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少
• 第四，当修改性能远远大于检索性能时，不应该创建索 引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建索引。

1.3、什么样的字段适合创建索引

• 1、表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询 ，在innodb中，主键默认为主键索引
• 2、数据量超过300的表应该有索引，数据量大，没有索引，查询效率很低
• 3、经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引
• 4、经常出现在Where子句中的字段，**加快判断速度，**特别是大表的字段，应该建立索引，建立索引，一般用在select ……where f1 and f2 ，我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用
• 5、经常用到排序的列上，因为索引已经排序
• 6、经常用在范围内搜索的列上创建索引

2、索引优缺点

2.1、优点

• 索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度
  索引的优点是可以提高检索数据的速度

2.2、缺点

• 索引的缺点是创建和维护索引需要耗费时间，索引可以提高查询速度，会减慢写入速度，在插入和修改的时候，可能会重建索引。
• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

3、索引分类

3.1、普通索引：

• 仅加速查询 最基本的索引，没有任何限制，是我们大多数情况下的使用的索引。

  create index index_name on table_name(列名);
  alter table table_name add index index_name(列名)
  

3.2、唯一索引

• 与普通索引类型，不同的是，加速查询+列值唯一，可以有null值

  create unique index index_name on table_name(列名)
  alter table table_name add unique index index_name(列名)
  

3.3、全文索引

全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。MySQL5.6.4，Innodb对全文索引也做了支持，但是对汉字的查询依旧效率不高

3.4、组合索引

• 将几个列作为一条索引进行检索，使用最左匹配原则

  最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。

3.5、主键索引

• 主键索引不可以为空
• 主键索引可以做外键
• 一张表中只能有一个主键索引

3.6、创建索引的三种方式以及删除索引

3.6.1在创建表的时候同时创建

create table test_index (
  id   bigint(20)  NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT  '主键',
  name  VARCHAR(32) NOT NULL COMMENT '姓名',
  email VARCHAR(64) NOT NULL COMMENT  '邮箱',
  message text DEFAULT  NULL COMMENT '个人信息',
  INDEX index_name (name) COMMENT '索引name'
) COMMENT  = '索引测试表';

create table table_name (
	列名1 ...
    ...
    index index_name(列名) 
)

3.6.2在存在的表上创建索引(create,alter)

create index index_name on table_name(col_name);
create index ine on teacher(username);
alter table table_name add index index_name(col_name,[col_name2]);

3.6.3在对于创建索引时如果是blob 和 text 类型，必须指定length。

CREATE INDEX idx_extra ON healerjean(message(200));

3.6.4 删除索引

delete index_name from table_name;
alter table table_name drop index_name;
-- index_name 索引名
-- table_name 表名

3.6.5 查看表中的索引

show index from table_name;

3.6.6 对于使用like的查询

1.对于使用 like 的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用。

2.如果对大的文本进行搜索,使用全文索引而不用使用 like ‘%…%’