Mysql学习之索引

作者:南丞 时间:2019-01-07 02:00

Mysql学习之索引学习

  • 优点: 索引可以大大提高MySQL的检索速度
  • 缺点: 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE.因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
  • 使用原则:通过上面说的优点和缺点,我们应该可以知道,并不是每个字段度设置索引就好,也不是索引越多越好,而是需要自己合理的使用。
    1、对经常更新的表就避免对其进行过多的索引,对经常用于查询的字段应该创建索引, 2、数据量小的表最好不要使用索引,因为由于数据较少,可能查询全部数据花费的时间比遍历索引的时间还要短,索引就可能不会产生优化效果。 3、在一同值少的列上(字段上)不要建立索引,比如在学生表的”性别”字段上只有男,女两个不同值。相反的,在一个字段上不同值较多可是建立索引。

索引分类

索引我们分为四类来
   单列索引(普通索引,唯一索引,主键索引)、组合索引、全文索引、空间索引、
单列索引:一个索引只包含单个列,但一个表中可以有多个单列索引。 这里不要搞混淆了。  
    |
    |
    |----普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。
    |
    |----唯一索引:索引列中的值必须是唯一的,但是允许为空值,
    |
    |----主键索引:是一种特殊的唯一索引,不允许有空值。


组合索引: 在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合

全文索引:全文索引,只有在MyISAM引擎上才能使用,只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引,介绍了要求,说说什么是全文索引,就是在一堆文字中,通过其中的某个关键字等,就能找到该字段所属的记录行,

空间索引: 空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有四种,GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时,使用SPATIAL关键字。要求,引擎为MyISAM,创建空间索引的列,必须将其声明为NOT NULL。

准备:

  • 创建表:
CREATE TABLE IF NOT EXISTS `demo1`(
ID INT NOT NULL,   
name VARCHAR(16) NOT NULL  
);
  • 插入数据:
INSERT INTO `demo1` VALUES(1,'A'),(2,'B'),(3,'C'),(4,'A'),(5,'A'),(2,'B'),(2,'B'),(2,'B'),(5,'A'),(6,'A'),(6,'A'),(6,'A'),(9,'A');

INSERT INTO `demo1` SELECT * FROM  `demo1`;
  • 查询测试一下:
SELECT * FROM `demo1` WHERE `name` = 'A'; 

结果:

[SQL]SELECT * FROM `demo1` WHERE `name` = 'A';
受影响的行: 0
时间: 0.003s

普通索引

  • 创建索引

这是最基本的索引,它没有任何限制。它有以下几种创建方式:

CREATE INDEX index_name ON demo1(`name`)
  • 查看表结构
CREATE TABLE `demo1` (
  `ID` int(11) NOT NULL,
  `name` varchar(16) NOT NULL,
  KEY `index_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  • 查询
SELECT * FROM demo1 WHERE name = 'A'
  • 结果:
[SQL]SELECT * FROM demo1 WHERE name = 'A'

受影响的行: 0
时间: 0.023s

会看到比上一次查询快了 注意 在创建索引的时候,如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。

添加索引的方式:

修改表结构(添加索引)

ALTER table demo1 ADD INDEX index_name(name)

删除索引 DROP INDEX [indexName] ON mytable;

唯一索引:

注意:索引列的值必须唯一,但允许有空值。

  • 创建索引
CREATE UNIQUE INDEX nickname ON demo1(nickname)

有四种方式来添加数据表的索引:

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。

  • 查看表结构
CREATE TABLE `demo1` (
  `ID` int(11) NOT NULL,
  `name` varchar(16) NOT NULL,
  `nickname` varchar(255) DEFAULT NULL,
  UNIQUE KEY `nickname` (`nickname`),
  KEY `index_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  • 查询:
# 未加索引之前
SELECT * FROM demo1 WHERE nickname = 'd'

# 查询结果:
[SQL]SELECT * FROM demo1 WHERE nickname = 'd'

受影响的行: 0
时间: 0.002s

# 加索引之后查询结果:

[SQL]SELECT * FROM demo1 WHERE nickname = 'd'

受影响的行: 0
时间: 0.001s

主键索引:

  • 创建索引
ALTER TABLE `demo1` ADD PRIMARY KEY (`id`)
  • 查看表结构
CREATE TABLE `demo1` (
  `ID` int(11) NOT NULL,
  `name` varchar(16) NOT NULL,
  `nickname` varchar(255) DEFAULT NULL,
  UNIQUE KEY `nickname` (`nickname`),
  UNIQUE KEY `nickname1` (`nickname`),
  KEY `index_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

  • 查询:
# 未加索引之前
SELECT * FROM `demo1` WHERE ID = 12

# 查询结果:
[SQL]SELECT * FROM `demo1` WHERE ID = 12

受影响的行: 0
时间: 0.003s

# 加索引以后查询结果:
[SQL]SELECT * FROM `demo1` WHERE ID = 12
受影响的行: 0
时间: 0.001s

组合索引:

组合索引就是在多个字段上创建一个索引

  • 添加索引
  ALTER TABLE `demo1` ADD INDEX MultiIdx1(`ID`, `age`)
  • 查询:
# 未加索引之前
SELECT * FROM demo1 WHERE id <9 AND age <10

# 查询结果:
[SQL]SELECT * FROM demo1 WHERE id <9 AND age <10

受影响的行: 0
时间: 0.002s

# 加索引以后查询结果:
[SQL]SELECT * FROM demo1 WHERE id <9 AND age <10

受影响的行: 0
时间: 0.001s

注意:

组合索引的索引文件以B-Tree格式保存,在创建组合索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。组合索引的第一个字段必须出现在查询组句中,这个索引才会被用到。

组合索引就是遵从了最左前缀,利用索引中最左边的列集来匹配行,这样的列集称为最左前缀,不明白没关系,举几个例子就明白了,例如,这里由id、age 2个字段构成的索引,索引行中就按id/age的顺序存放,索引可以索引下面字段组合(id,age)、或者(id)。如果要查询的字段不构成索引最左面的前缀,那么就不会是用索引,比如,age 就不会使用索引查询

例如

# 如果有一个组合索引(col_a,col_b,col_c),下面的情况都会用到这个索引:
col_a = "some value";
col_a = "some value" and col_b = "some value";
col_a = "some value" and col_b = "some value" and col_c = "some value";
col_b = "some value" and col_a = "some value" and col_c = "some value";

# 不会用到索引
col_b = "aaaaaa";
col_b = "aaaa" and col_c = "cccccc";

全文索引:

注意

旧版的MySQL的全文索引只能用在MyISAM表格的char、varchar和text的字段上。 不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引,所以具体信息要随时关注官网.

字段类型:char、varchar和text

  • 添加索引:
ALTER TABLE `demo1` ADD FULLTEXT (`name`);
  • 查询:

# 通常未加全文索引前,检索某个名字 我们一般都是模糊查询
SELECT * FROM demo1 WHERE `name` like 'A%'

# 运行结果:
[SQL]SELECT * FROM demo1 WHERE `name` like 'A'

受影响的行: 0
时间: 0.003s

# 添加全文索引以后
SELECT * FROM `demo1` WHERE MATCH(`name`) AGAINST('thisis')

# 运行结果
[SQL]SELECT * FROM `demo1` WHERE MATCH(`name`) AGAINST('thiss')

受影响的行: 0
时间: 0.001s

注意

mysql指定了最小字符长度,默认是4,必须要匹配大于4的才会有返回结果,也就是 AGAINST() 里面的字符比如是大于4的 不然就gameover了 可以用SHOW VARIABLES LIKE ‘ft_min_word_len’ 来查看指定的字符长度,也可以在mysql配置文件my.ini 更改最小字符长度,方法是在my.ini 增加一行 比如:ft_min_word_len = 2,改完后重启mysql即可。

mysql在集和查询中的对每个合适的词都会先计算它们的权重,一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重),因为在这个特定的集中,它有较低的语义值。否则,如果词是较少的,它将得到一个较高的权重,mysql默认的阀值是50%,上面‘you’在每个文档都出现,因此是100%,只有低于50%的才会出现在结果集中。

但是如果不考虑权重 MySQL提供了布尔全文检索 上面的sql就写成了 SELECT * FROM demo1 WHERE MATCH (name) AGAINST (‘thiss’ IN BOOLEAN MODE );

布尔全文检索语法:

上面通过IN BOOLEAN MODE指定全文检索模式为布尔全文检索。MySQL还提供了一些类似我们平时使用搜索引擎时用到的的语法:逻辑与、逻辑或、逻辑非等。具体通过几个SQL语句例子来说明:

SELECT * FROM demo1 WHERE MATCH (name) AGAINST ('+apple -banana' IN BOOLEAN MODE);
# + 表示AND,即必须包含。- 表示NOT,即不包含。

SELECT * FROM demo1 WHERE MATCH (name) AGAINST ('apple banana' IN BOOLEAN MODE);
# apple和banana之间是空格,空格表示OR,即至少包含apple、banana中的一个。

SELECT * FROM demo1 WHERE MATCH (name) AGAINST ('+apple banana' IN BOOLEAN MODE);
# 必须包含apple,但是如果同时也包含banana则会获得更高的权重。

SELECT * FROM demo1 WHERE MATCH (name) AGAINST ('+apple ~banana' IN BOOLEAN MODE);
#  ~ 是我们熟悉的异或运算符。返回的记录必须包含apple,但是如果同时也包含banana会降低权重。但是它没有 +apple -banana 严格,因为后者如果包含banana压根就不返回。

SELECT * FROM demo1 WHERE MATCH (name) AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE);
# 返回同时包含apple和banana或者同时包含apple和orange的记录。但是同时包含apple和banana的记录的权重高于同时包含apple和orange的记录。

注意

   A. MySQL全文检索默认不支持中文,且对英文检索时忽略大小写

   B. MySQL全文检索时,默认检索长度为4,即关键词的长度必须大于5才能被捕获

   C. MySQL全文检索时,所有FULLTEXT索引列必须使用相同的字符集

   D. MySQL全文检索返回结果集时还会考虑权重

   E. MySQL全文检索还支持灵活的布尔全文检索模式


评论:

回到顶部
回到顶部