注:本篇文章是对菜鸟教程中的mysql索引(http://www.runoob.com/mysql/mysql-index.html)的翻译版本;添加了示例,便于理解;
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
普通索引
创建索引
这是最基本的索引,它没有任何限制。它有以下几种创建方式:
CREATE INDEX indexName ON mytable(username(length));
注:indexName是索引名称,username是字段名
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
举例:
CREATE TABLE employee(
id INT,
sname VARCHAR(50),
age INT ,
PASSWORD VARCHAR(50)
);
#创建索引
CREATE INDEX index_sname ON employee(sname(20)); #注意添加索引的长度必须小于建表时给字段定义的长度,也可以不给长度,如:CREATE INDEX index_sname ON employee(sname);
#删除索引
DROP INDEX index_sname ON employee;
修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)
举例:
ALTER TABLE employee ADD INDEX index_sname(sname);#这里可以不指定索引的长度
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
举例:
CREATE TABLE teacher(
id INT NOT NULL,
teachername VARCHAR(16) NOT NULL,
INDEX index_teachername(teachername(5))
);
CREATE TABLE tea(
id INT NOT NULL,
teaname VARCHAR(16) NOT NULL,
INDEX (teaname)
);
删除索引的语法
DROP INDEX [indexName] ON mytable;
举例:
DROP INDEX index_sname ON employee;
唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
举例:
CREATE TABLE tea(
id INT NOT NULL,
teaname VARCHAR(16) NOT NULL,
INDEX (teaname)
);
CREATE UNIQUE INDEX index_teaname ON tea(teaname(4));
CREATE UNIQUE INDEX index_teaname ON tea(teaname);#也可以不给长度
修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
举例:
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(50) NOT NULL COMMENT "学生姓名",
age VARCHAR(50) NOT NULL COMMENT "学生年龄"
);
ALTER TABLE student ADD UNIQUE index_sname (sname(20));
ALTER TABLE student ADD UNIQUE(sname);##也可以不指定长度
DROP INDEX index_sname ON student;
SHOW INDEX FROM student;
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
举例:
CREATE TABLE cat(
id INT ,
catName VARCHAR(20),
UNIQUE INDEX index_catName(catName(5))
);
CREATE TABLE dog(
id INT ,
dogName VARCHAR(20),
UNIQUE INDEX index_dogName(dogName)
);
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引:
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 ,用于全文索引。
也可以一次性给多个列添加索引(多列索引)
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 ): 添加多列索引.
以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
举例:
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(50) NOT NULL COMMENT "学生姓名",
age VARCHAR(50) NOT NULL COMMENT "学生年龄"
);
ALTER TABLE student ADD INDEX index_sname(sname);
你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX c;
举例:
ALTER TABLE student DROP INDEX index_sname;
使用 ALTER 命令添加和删除主键
主键只能作用于一个列上,添加主键索引时,你需要确保该主键默认不为空(NOT NULL)。实例如下:
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;#先修改为--不为空
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);#然后添加主键
举例:
CREATE TABLE cat(
id INT ,
catName VARCHAR(20),
UNIQUE INDEX index_catName(catName(5))
);
ALTER TABLE cat MODIFY id INT NOT NULL;
ALTER TABLE cat ADD PRIMARY KEY (id);
你也可以使用 ALTER 命令删除主键:
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
ALTER TABLE cat DROP PRIMARY KEY;
删除主键时只需指定PRIMARY KEY(一个表只有一个主键),但在删除索引时,你必须知道索引名。
显示索引信息
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
尝试以下实例:
mysql> SHOW INDEX FROM table_name;
MySQL索引语法+使用场景
MySQL索引语法 建表时添加索引 建表同时建立单索引 CREATE TABLE t_user1(id INT , userName VARCHAR(20), PASSWORD VARCHAR(20) ...
MySQL创建索引语法
1.介绍: 所有mysql索引列类型都可以被索引,对来相关类使用索引可以提高select查询性能,根据mysql索引数,可以是最大索引与最小索引,每种存储引擎对每个表的至少支持16的索引.总索引长度为 ...
MySQL ALTER语法的运用方法 &;&; 操作索引和字段
语法:alter_specification: ADD [COLUMN] create_definition [FIRST | AFTER column_name ] or ADD INDEX [in ...
MySQL 通过多个示例学习索引
最近在准备面试,关于索引这一块,发现很多以前忽略的点,这里好好整理一下 首先为什么要建立索引 一本书,有章.节.段.行这种单位. 如果现在需要找一个内容:第9章>第2节>第3段>第4 ...
MySQL索引与Index Condition Pushdown(employees示例)
实验 先从一个简单的实验开始直观认识ICP的作用. 安装数据库 首先需要安装一个支持ICP的MariaDB或MySQL数据库.我使用的是MariaDB 5.5.34,如果是使用MySQL则需要5.6版 ...
索引-mysql索引创建、查看、删除及使用示例
mysql索引创建.查看.删除及使用示例 1.创建索引: ALTER TABLE用来创建普通索引.UNIQUE索引或PRIMARY KEY索引. ALTER TABLE table_name ADD ...
mysql 索引2
/* 所有MySQL列类型可以被索引.根据存储引擎定义每个表的最大索引数和最大索引长度. 所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节.大多数存储引擎有更高的限制. 索引的存储类型 ...
MySQL索引分析与优化
1.MySQL能够在name的索引中查找“Mike”值,然后直接转到数据文件中相应的行,准确地返回该行的 peopleid(999).在这个过程中,MySQL只需处理一个行就可以返回结果.如果没有“n ...
mysql索引之四(索引使用注意规则:索引失效--存在索引但不使用索引)
但是如果是同样的sql如果在之前能够使用到索引,那么现在使用不到索引,以下几种主要情况: 1. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全 ...
随机推荐
【JBOSS】User not found SA
启动JBOSS 发现报User not found: SA 错误, 找了老半天才找到处理方法,异常日志如下: java.sql.SQLException: User not found: SA at ...
javascript中的内置对象总结
内置对象 标准内置对象 Object Object.create Object.prototype.toString Object.prototype.hasOwnProperty Boolean S ...
SQL Server 存储过程遇到“表 '#TT' 没有标识属性。无法执行 SET 操作”错误
创建临时表,往临时表插入数据的时候报的错误. 一开始提示没有打开主键,后来打开主键就提示上述错误异常. 从网上查找资料没有找到,然后又到群里问各位大牛,一位大牛告诉我是没有设置主键. 我又仔细看看提示 ...
Windows Internals学习笔记(七)Image Loader
参考资料: 1. 2. Fibers 知识点: ● 当一个进程在系统上启动时,内核将创建一个进程对象来代表它,并执行各种内核相关的初始化任务.然而, ...
Android 开发错误信息001
Error:Execution failed for task ':app:dexDebug'. > com.android.ide.common.process.ProcessExceptio ...
Oracle INV - SO line backorder API
--Sales Order Lines to backorder API--===================================--SET serveroutput on size ...
[转载] python+Eclipse+pydev环境搭建
转自:http://www.cnblogs.com/Bonker/p/3584707.html 编辑器:Python 自带的 IDLE 简单快捷, 学习Python或者编写小型软件的时候.非常有用. ...
BC 65 ZYB's Premutation (线段树+二分搜索)
题目简述:有一个全排列,一直每个前缀区间的逆序对数,还原这个排列. fi记录逆序对数,pi记录该位置数值,则k=fi-f(i-1)表示前i-1个数比pi大的数的个数,那么只要在剩余元素求出按大小顺序第 ...
python3.4 data type
#coding=utf-8 #Python 3.4 https://docs.python.org/3.4/library/ #IDE:Eclipse +PyDev Window10 import a ...
JDBC常见面试题
以下我是归纳的JDBC知识点图: 图上的知识点都可以在我其他的文章内找到相应内容. JDBC常见面试题 JDBC操作数据库的步骤 ? JDBC操作数据库的步骤 ? 注册数据库驱动. 建立数据库连接. ...