2019-1-08-Mysql学习之索引

最新推荐文章于 2024-07-12 02:12:30 发布

PFinal社区-【南丞】

最新推荐文章于 2024-07-12 02:12:30 发布

阅读量156

点赞数

本文链接：https://blog.csdn.net/weixin_41811317/article/details/105660761

版权

layout	title	date	author	desc	in_head
post	Mysql学习之索引	2019-01-07 10:00:02 +0800	南丞	MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。	<style> .article-content p{ text-indent: 2em; line-height: 1.75rem; }</style>

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全文检索还支持灵活的布尔全文检索模式