Mysql-索引

2021年09月16日 阅读数:4
这篇文章主要向大家介绍Mysql-索引,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

Mysql-索引

 

 

 

 

 

 

 

 

MySQL 索引

1.索引的概念sql

✎索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址(相似于C语言的链表经过指针指向数据记录的内存地址)。
✎使用索引后能够不用扫描全表来定位某行的数据,而是先经过索引表找到该行数据对应的物理地址而后访问相应的数据,所以能加快数据库的查询速度。
✎索引就比如是一本书的目录,能够根据目录中的页码快速找到所需的内容。
✎索引是表中一列或者若干列值排序的方法。
✎创建索引的目的是加快对表中记录的查找或排序。数据库

2.索引的做用性能

✎设置了合适的索引以后,数据库利用各类快速定位技术,可以大大加快查询速度,这是建立索引的最主要的缘由。
✎当表很大或查询涉及到多个表时,使用索引能够成千上万倍地提升查询速度。
✎能够下降数据库的IO成本,而且索引还能够下降数据库的排序成本。
✎经过建立惟一性索引,能够保证数据表中每一行数据的惟一性。
✎能够加快表与表之间的链接。
✎在使用分组和排序时,可大大减小分组和排序的时间。spa

3.索引的反作用3d

✎索引须要占用额外的磁盘空间。
对于 MyISAM 引擎而言,索引文件和数据文件是分离的,索引文件用于保存数据记录的地址。
而 InnoDB 引擎的表数据文件自己就是索引文件。
✎在插入和修改数据时要花费更多的时间,由于索引也要随之变更。指针

4.建立索引的原则依据blog

索引随能够提高数据库查询的速度,但并非任何状况下都适合建立索引。由于索引自己会消耗系统资源,在有索引的状况下,数据库会先进行索引查询,而后定位到具体的数据行,若是索引使用不当,反而会增长数据库的负担。排序

 

✎表的主键、外键必须有索引。由于主键具备惟一性,外键关联的是子表的主键,查询时能够快速定位。
✎记录数超过300行的表应该有索引。若是没有索引,须要把表遍历一遍,会严重影响数据库的性能。
✎常常与其余表进行链接的表,在链接字段上应该创建索引。
✎惟一性太差的字段不适合创建索引。
✎更新太频繁地字段不适合建立索引。
✎常常出如今 where 子句中的字段,特别是大表的字段,应该创建索引。
✎在常常进行 GROUP BY、OPDER BY 的字段上创建索引
✎索引应该建在选择性高的字段上。
✎索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引。索引

5.索引的分类和建立内存

1)普通索引:最基本的索引类型,没有惟一性之类的限制。
●直接建立索引
CREATE INDEX 索引名 ON 表名 (列名[(length)]);
#(列名(length)):length是可选项,下同。若是忽略 length 的值,则使用整个列的值做为索引。若是指定使用列前的 length 个字符
来建立索引,这样有利于减少索引文件的大小。 #索引名建议以“_index”结尾。 ●修改表方式建立 ALTER TABLE 表名 ADD INDEX 索引名 (列名); ●建立表的时候指定索引 CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名)); 2)惟一索引:与普通索引相似,但区别是惟一索引列的每一个值都惟一。惟一索引容许有空值(注意和主键不一样)。若是是用组合索引建立,则
列值的组合必须惟一。添加惟一键将自动建立惟一索引。 ●直接建立惟一索引: CREATE UNIQUE INDEX 索引名 ON 表名(列名); ●修改表方式建立 ALTER TABLE 表名 ADD UNIQUE 索引名 (列名); ●建立表的时候指定 CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名)); 3)主键索引:是一种特殊的惟一索引,必须指定为“PRIMARY KEY”。一个表只能有一个主键,不容许有空值。 添加主键将自动建立主键索引。 ●建立表的时候指定 CREATE TABLE 表名 ([...],PRIMARY KEY (列名)); ●修改表方式建立 ALTER TABLE 表名 ADD PRIMARY KEY (列名); 4)组合索引(单列索引与多列索引):能够是单列上建立的索引,也能够是在多列上建立的索引。须要知足最左原则,由于 select 语句的
where 条件是依次从左往右执行的,因此在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致,不然索引
将不会生效。 CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3)); select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...'; 5)全文索引(FULLTEXT):适合在进行模糊查询的时候使用,可用于在一篇文章中检索文本信息。在 MySQL5.6 版本之前 FULLTEXT 索引仅可用于 MyISAM 引擎,在 5.6 版本以后 innodb 引擎也支持 FULLTEXT 索引。全文索引能够在 CHAR、VARCHAR 或
者 TEXT 类型的列上建立。每一个表只容许有一个全文索引。 ●直接建立索引 CREATE FULLTEXT INDEX 索引名 ON 表名 (列名); ●修改表方式建立 ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名); ●建立表的时候指定索引 CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名)); #数据类型能够为 CHAR、VARCHAR 或者 TEXT ●使用全文索引查询 SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

例:

1)普通索引

直接建立索引

例:

 

 

 

 

 

 

修改表方式建立

例:

建立表的时候指定

例:

2)惟一索引

直接建立惟一索引

例:

 

修改表方式建立

例:

建立表的时候指定

例:

 

3)主键索引:主键索引,是一种特殊的惟一索引,必须指定为“PRIMARY KEY”。一个表只能有一个主键,不容许有空值。 添加主键将自动建立主键索引

添加主键索引

例:

4)组合索引

例:

5)全文索引

修改表方式建立

例:

使用全文索引查询

例:

6)删除索引

●直接删除索引
DROP INDEX 索引名 ON 表名;

●修改表方式删除索引
ALTER TABLE 表名 DROP INDEX 索引名;

●删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;

例:

7)查看索引

show index from 表名;
show keys from 表名;

 

各字段的含义以下:
Table:表的名称。
Non_unique:若是索引不能包括重复词,则为 0;若是能够,则为 1。
Key_name:索引的名称。
Seq_in_index:索引中的列序号,从 1 开始。
Column_name:列名称。
Collation:列以什么方式存储在索引中。在 MySQL 中,有值‘A’(升序)或 NULL(无分类)。
Cardinality:索引中惟一值数目的估计值。
Sub_part:若是列只是被部分地编入索引,则为被编入索引的字符的数目。若是整列被编入索引,则为 NULL。
Packed:指示关键字如何被压缩。若是没有被压缩,则为 NULL。
Null:若是列含有 NULL,则含有 YES。若是没有,则该列含有 NO。
Index_type:用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。
Comment:备注。

例: