MySQL索引的基本语法

索引是排好序的数据结构！可以用在 where 条件查找的字段，和order by 排序的字段，有了索引，便可以快速地定位数据所在的物理地址并找出来。

索引的分类

1.普通索引(normal)：没有任何约束，主要用于提高查询效率

2.唯一索引(UNIQUE)：在普通索引的基础上增加了数据唯一性的约束，可以有多个

3.主键索引(primary key)：主键索引在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE，只能有一个

4.全文索引(FULLTEXT)：MySQL 自带的全文索引只支持英文。

一般使用专门的全文搜索引擎，比如 ES(ElasticSearch)

创建索引

#普通索引

ALTER TABLE 表名 ADD INDEX 索引名 (column_list);

#唯一索引

ALTER TABLE 表名 ADD UNIQUE 索引名(column_list);

#主键索引

ALTER TABLE 表名 ADD PRIMARY KEY 索引名(column_list);

#全文索引(字符串数据类型)

ALTER TABLE 表名 ADD fulltext 索引名(column_list);

#查看索引

show index from student \G

这里用\G 代替分号结束符；可以格式化输出数据信息。

#删除索引

修改表的方式删除索引

ALTER TABLE 表名 DROP INDEX 索引名;

删除主键索引

ALTER TABLE 表名 DROP PRIMARY KEY;

索引列为一列时为单一索引；
多个列组合在一起创建的索引叫做联合索引

什么情况下可以创建索引？

1.字段的数值有唯一性的限制，比如id

2.频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下

如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

3.需要经常 GROUP BY 和 ORDER BY 的列

4.UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引

原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除，非索引字段更新数据效果更明显；

如果索引太多了，在更新数据的时候，如果涉及到索引更新，就会造成负担。

5.DISTINCT 字段需要创建索引

6.做多表 JOIN 连接操作时，创建索引需要注意以下的原则

连接表的数量尽量不要超过 3 张，每增加一张表就相当于增加了 一次嵌套的循环，数量级增长会非常快 对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

什么时候不需要创建索引?

索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的

1.WHERE 条件（包括 GROUP BY、ORDER BY）里用不到的字段不需要创建索引；

2.表记录太少，比如少于 1000 行，那么是不需要创建索引；

3.字段中如果有大量重复数据，也不用创建索引，比如性别字段；

4.频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率；

什么情况下索引失效?

1.如果索引列进行了表达式计算，使用函数，则会失效

2.在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。

3.当我们使用 LIKE 进行模糊查询的时候，表达式不能是 % 开始

4.索引列尽量设置为 NOT NULL 约束

判断索引列是否为 NOT NULL，往往需要走全表扫描，因此我们最好在设计数据表的时候就将字段设置为 NOT NULL 约束比如你可以将 INT 类型的字段，默认值设置为 0。将字符类型的默认值设置为空字符串 ('')

以上就是MySQL索引的基本语法的详细内容，更多关于MySQL索引语法的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySql 存储引擎和索引相关知识总结

    存储引擎 什么是数据库存储引擎? 数据库引擎是数据库底层软件组件,不同的存储引擎提供不同的存储机制,索引技巧,锁定水平等功能,使用不同的数据库引擎,可以获得特定的功能 如何查看引擎? --如何查看数据库支持的引擎 show engines; ​ --查看当前数据的引擎: show create table 表名\G ​ --查看当前库所有表的引擎: show table status\G 建表时指定引擎 create table yingqin (id int,name varchar(20))

  • 深入了解mysql索引

    1.索引原理 索引被用来快速找出在一个列上用一特定值的行.没有索引,MySQL不得不首先以第一条记录开始,然后读完整个表直到它找出相关的行.表越大,花费时间越多.对于一个有序字段,可以运用二分查找(Binary Search),这就是为什么性能能得到本质上的提高.MYISAM和INNODB都是用B+Tree作为索引结构 (主键,unique 都会默认的添加索引) 2.创建索引 如果未使用索引,我们查询 工资大于 1500的会执行全表扫描 什么时候需要给字段添加索引: -表中该字段中的数据量庞大

  • MySQL中索引失效的常见场景与规避方法

    前言 之前有看过许多类似的文章内容,提到过一些sql语句的使用不当会导致MySQL的索引失效.还有一些MySQL"军规"或者规范写明了某些sql不能这么写,否则索引失效. 绝大部分的内容笔者是认可的,不过部分举例中笔者认为用词太绝对了,并没有说明其中的原由,很多人不知道为什么.所以笔者绝对再整理一遍MySQL中索引失效的常见场景,并分析其中的原由供大家参考. 当然请记住,explain是一个好习惯! MySQL索引失效的常见场景 在验证下面的场景时,请准备足够多的数据量,因为数据量少时

  • Mysql索引类型与基本用法实例分析

    本文实例讲述了Mysql索引类型与基本用法.分享给大家供大家参考,具体如下: 索引 MySQL目前主要有以下几种索引类型: 普通索引 唯一索引 主键索引 组合索引 全文索引 - 普通索引 是最基本的索引,它没有任何限制. CREATE INDEX IndexName ON `TableName`(`字段名`(length)) - 唯一索引 与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值.如果是组合索引,则列值的组合必须唯一. CREATE UNIQUE INDEX index

  • Mysql之组合索引方法详解

    对于任何DBMS,索引都是进行优化的最主要的因素.对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧下降. 如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效的查找.例如: 假设存在组合索引(c1,c2),查询语句select * from t1 where c1=1 and c2=2能够使用该索引.查询语句select * from t1 where c1=1也能够使用该索引.但是,查询语句select * from t1

  • MySQL 的覆盖索引与回表的使用方法

    两大类索引 使用的存储引擎:MySQL5.7 InnoDB 聚簇索引 * 如果表设置了主键,则主键就是聚簇索引 * 如果表没有主键,则会默认第一个NOT NULL,且唯一(UNIQUE)的列作为聚簇索引 * 以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引 InnoDB的聚簇索引的叶子节点存储的是行记录(其实是页结构,一个页包含多行数据),InnoDB必须要有至少一个聚簇索引. 由此可见,使用聚簇索引查询会很快,因为可以直接定位到行记录. 普通索引 普通索引也叫二级索引,除聚簇索引外的

  • MySQL 索引知识汇总

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度. 打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车. 拿汉语字典的目录页(索引)打比方,我们可以按拼音.笔画.偏旁部首等排序的目录(索引)快速查找到需要的字. 索引分单列索引和组合索引.单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引.组合索引,即一个索引包含多个列. 创建索引时,你需要确保该索引是应用在

  • MySQL 创建索引(Create Index)的方法和语法结构及例子

    CREATE INDEX Syntax CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_type] index_col_name: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH | RTREE} 复制代码 代码如下: -- 创建无索引的表格 create t

  • MySQL8新特性之降序索引底层实现详解

    什么是降序索引 大家可能对索引比较熟悉,而对降序索引比较陌生,事实上降序索引是索引的子集. 我们通常使用下面的语句来创建一个索引: create index idx_t1_bcd on t1(b,c,d); 上面sql的意思是在t1表中,针对b,c,d三个字段创建一个联合索引. 但是大家不知道的是,上面这个sql实际上和下面的这个sql是等价的: create index idx_t1_bcd on t1(b asc,c asc,d asc); asc表示的是升序,使用这种语法创建出来的索引叫做

  • Mysql数据库高级用法之视图、事务、索引、自连接、用户管理实例分析

    Mysql数据库高级用法之视图、事务、索引、自连接、用户管理实例分析

    本文实例讲述了Mysql数据库高级用法之视图.事务.索引.自连接.用户管理.分享给大家供大家参考,具体如下: 视图 视图是对若干张基本表的引用,一张虚表,只查询语句执行结果的字段类型和约束,不存储具体的数据(基本表数据发生了改变,视图也会跟着改变),方便操作,特别是查询操作,减少复杂的SQL语句,增强可读性. 1.----创建视图: create view 视图名称(一般以v_开头) as 查询语句; 2.----查看视图: select * from 视图名称; 3.----删除视图: dro

随机推荐