MySQL之索引、主键索引、唯一索引、常规索引、全文索引、索引使用场景、索引失效场景、sql优化的规则等

您所在的位置：网站首页 › mysql索引五种索引类型是什么意思 › MySQL之索引、主键索引、唯一索引、常规索引、全文索引、索引使用场景、索引失效场景、sql优化的规则等

MySQL之索引、主键索引、唯一索引、常规索引、全文索引、索引使用场景、索引失效场景、sql优化的规则等

2024-07-13 15:50| 来源: 网络整理| 查看: 265

索引 1.什么是索引2.索引的缺点3.索引分类4.索引的管理5.索引的使用规则6.索引使用的场景7.索引失效的场景8.SQL优化原则

1.什么是索引

索引是对数据库中一列或多列的值进行排列的一种结构，使用索引可快速访问数据库表中的特定信息。

四个特性：

高效性：利用索引可以提高数据库的查询效率。唯一性：索引可以确保所查的数据的唯一性。完整性：用户可以加速表和表之间的连接，实现表与表之间的参照完整性。特殊能力：通过使用索引，可以在查询过程中，使用优化隐藏器，提高系统性能。 2.索引的缺点虽然索引大大提高了查询速度，同时却会降低表更新的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引回占用磁盘空间的索引文件。如果在一个大表上创建了多种组合索引，索引文件的膨胀很快。

索引只是提高效率的一个因素，如果MySQL有大量数据的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

3.索引分类

索引的分类：

主键索引在数据库关系图中为表定义一个主键将自动创建主键索引。唯一索引不允许具有索引值相同的行，从而禁止重复的索引或键值。常规索引最基本的索引类型，没有唯一性之类的限制。全文索引搜索引擎的关键技术，用于检索文本信息，可以是词语或者段落。聚簇索引（主键） / 非聚簇索引（唯一，常规，全文）

1、主键索引

CREATE TABLE `表名` ( `字段1` INT(11) AUTO_INCREMENT PRIMARY KEY, #或 PRIMARY KEY(`字段1`) )

说明：

1.某一个属性组能唯一标识一条记录；

2.最常见的索引类型；

3.确保数据记录的唯一性；

4.确定特定数据记录在数据库中的位置。

2、唯一索引

CREATE TABLE `表名` ( `字段1` INT(11) NOT NULL UNIQUE, #或 UNIQUE KEY(`字段1`) )

说明：

1.快速定位特定数据；

2.index和key关键字都可设置常规索引；

3.应加在查找条件的字段；

4.不宜添加太多常规索引，影响数据的插入、删除和修改操作。

3、常规索引

CREATE TABLE `表名` ( `字段1` INT(11) NOT NULL, INDEX/KEY(`字段1`) )

说明：

1.避免同一个表中某数据列中的值重复；

2.对比主键索引只能有一个,唯一索引可有多个。

4、全文索引

CREATE TABLE `表名` ( `字段1` VARCHAR(32) NOT NULL,, fulltext key (字段名，字段名，字段名) with parser ngram )ENGINE=innodb 用法： SELECT FROM WHERE MATCH(字段) AGAINST (‘要搜索的关键词’);

1.Mysql 5.6之前版本，只有myisam支持全文索引，5.6之后，Innodb和myisam均支持全文索引。

2.只有char、varchar、text类型字段能创建全文索引。

3.当大量写入数据时，建议先写入数据，后再建立全文索引，提高效率。

4.Mysql内置ngram 解析器，可以解析中日韩三国文字。有汉字的一定要启用它。

5.英文分词用空格，逗号；中文分词用 ngram_token_size 设定.

注意需要在配置文件中加入（默认检索子的个数，优先检索2个子的，如果存储全是成语的话，可以设置成4）：

[mysqld]

ngram_token_size=2

4.索引的管理

1、查看索引

SHOW INDEX(或KEYS) FROM 表名;

2、删除索引

ALTER TABLE 表名 DROP PRIMARY KEY; DROP INDEX 索引名 ON 表名; ALTER TABLE 表名 DROP INDEX 索引名;

3、修改索引

ALERT TABLE 表名 ADD 索引类型（数据列名）; ALTER TABLE add FULLTEXT INDEX (字段名1，字段2，，) [ WITH PARSER ngram]; 5.索引的使用规则

1、最左前缀匹配原则

对于联合索引，总是从索引的最前面字段开始，接着往后，中间不能跳过。

比如创建了多列索引(name,age,sex)，会先匹配name字段，再匹配age字段，再匹配sex字段的，中间不能跳过。

mysql会一直向右匹配直到遇到范围查询（>、 3 and d = 4，

如果建立（a,b,c,d）顺序的索引，d是用不到索引的，

如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2、尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)

表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，

使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。（比如，我们会选择学号做索引，而不会选择性别来做索引。）

3、=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

4、索引列不能参与计算，保持列“干净”

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。

所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

例如：select * from users where YEAR(adddate)或

【本文地址】

MySQL之索引、主键索引、唯一索引、常规索引、全文索引、索引使用场景、索引失效场景、sql优化的规则等

MySQL之索引、主键索引、唯一索引、常规索引、全文索引、索引使用场景、索引失效场景、sql优化的规则等

今日新闻

推荐新闻