mysql 给表里的某个字段建立索引

2023-11-12 来源：华拓网

1.添加PRIMARY KEY（主键索引）：

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) ‘table_name‘ 表名column 字段将table_name表的column字段设置为主键

2.添加UNIQUE(唯一索引) ：

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加INDEX(普通索引) ：

写法一：ALTER TABLE `table_name` ADD INDEX index_name ( `column` )写法二：CREATE INDEX index_name ON `table_name`(`column1`,`column2`,`column3`)

4.添加FULLTEXT(全文索引) ：

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引(联合索引)：

写法一：ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )写法二：CREATE INDEX index_name ON `table_name`(`column1`,`column2`,`column3`)

6. 查询表索引

SHOW INDEX FROM `table_name`;

7. 删除索引

DROP INDEX index_name ON `table_name`;

mysql 给表里的某个字段建立索引

标签：column 添加联合全文索引 alter 字段 unique 唯一索引 lte

小编还为您整理了以下内容，可能对您也有帮助：

在mysql数据库中为字段添加索引，是什么意思，有什么好处，谢谢！！

在mysql数据库中为字段添加索引，意思是对数据库某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页进行排序，它是逻辑指针清单。

索引提供指向存储在表的指定列中的数据值的指针，然后根据指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

扩展资料：

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。

对某个列建立UNIQUE索引后，插入新记录时，数据库管理系统会自动检查新纪录在该列上是否取了重复值，在CREATE TABLE 命令中的UNIQE约束将隐式创建UNIQUE索引。

mysql数据库怎么建索引？

MySQL数据库建立索引步骤：

1、使用SQLPlus或者PLSQL登录MySQL数据库。

2、执行语句create index indexname on table(col) tablespace tablespacename;其中

"indexname"为需要建立索引的索引名；

"table"是需要建立索引的表；

"col"为需要建立索引的字段列;

”tablespacename“为建立索引存放的空间(如果不需要制定表空间则使用“create index indexname on table(col)”)。

MySQL如何为表字段添加索引

1.添加PRIMARY KEY（主键索引）：

2.添加UNIQUE(唯一索引) ：

3.添加INDEX(普通索引) ：

4.添加FULLTEXT(全文索引) ：

5.添加多列索引：

MySQL如何为表字段添加索引

1.添加PRIMARY KEY（主键索引）：

2.添加UNIQUE(唯一索引) ：

3.添加INDEX(普通索引) ：

4.添加FULLTEXT(全文索引) ：

5.添加多列索引：

如何正确合理的建立MYSQL数据库索引

MySQL索引类型包括：

（1）普通索引

这是最基本的索引，它没有任何。它有以下几种创建方式：

◆创建索引

CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

◆修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

◆创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 删除索引的语法：

DROP INDEX [indexName] ON mytable;

（2）唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

◆创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

◆修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

◆创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

（3）主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); 当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

（4）组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); 为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age usernname,city usernname 为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

SELECT * FROM mytable WHREE username="admin" AND city="郑州" SELECT * FROM mytable WHREE username="admin" 而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city="郑州" SELECT * FROM mytable WHREE city="郑州"

（5）建立索引的时机

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

SELECT * FROM mytable WHERE username like'admin%' 而下句就不会使用：

SELECT * FROM mytable WHEREt Name like'%admin' 因此，在使用LIKE时应注意以上的区别。

（6）索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

◆虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

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

（7）使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

◆索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

◆使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

◆索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

◆like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

◆不要在列上进行运算

select * from users where YEAR(adddate)<2007; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select * from users where adddate<‘2007-01-01’;

◆不使用NOT IN和<>操作

mysql创建索引的原则

1．选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2．为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4．*索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

6．尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

8 . 最左前缀匹配原则，非常重要的原则。

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

9 .=和in可以乱序。

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

。
10 . 尽量选择区分度高的列作为索引。

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

11 .索引列不能参与计算，保持列“干净”。

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

12 .尽量的扩展索引，不要新建索引。
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

mysql创建索引的原则

Mysql如何适当的添加索引介绍

这里先简单介绍一下索引：

添加索引是为了提高数据库查询性能，索引是最物美价廉的东西了，不用加内存，不用改程序，不用调sql，只要执行个正确的create index ,查询的速度就可能提高百倍千倍，这可是有力的，可是天下没有没费的午餐，查询的速度的提高是以牺牲insert update delete的速度为代价的。而且索引大小一般是数据的三分之一，再加上索引要加载进内存的，如果全部字段都加索引会以牺牲内存为代价的，所以才要设当的添加索引。

这里简单介绍一下mysql中常用索引：

在添加索引之前最好先查看一下该表中已存在哪些索引：show index from 表名；

1、主键索引

注意：主键索引一张表中只能有一个，但是可以添加多个索引比如：唯一索引、普通索引、全文索引。

主键索引：一般在建表的时候就添加了比如：id 一般是主键索引加自动递增。

建表后添加主键索引：alter table table_name add primary key (column name);

主键索引的特点：不能为空且唯一。

2、普通索引

创建普通索引：alter table table_name add index 索引名(column1,column2);

3、唯一索引

创建唯一索引：ALTER TABLE `table_name` ADD UNIQUE (`column`)；

唯一索引与主键索引的区别：

唯一索引：可以有多个null 但数据内容不能重复

主键索引：不能为null，且内容只能唯一。

两个区别就在于主键索引不能为null 唯一索引可以有多个null 其余都一样。

4、全文索引

全文索引只有MyISAM有效(mysql5.6之后InnoDB也支持了全文索引)[5.7不支持MyISAM]

全文索引主要针对文本文件，比如文章、标题。

在创建表时创建全文索引：

CREATE TABLE `article` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(200) DEFAULT NULL,

` content` text,

PRIMARY KEY (`id`),

FULLTEXT KEY `title` (`title`,`content`)

) ENGINE=MyISAM（5.6之后InnoDB也支持全文索引、5.7不支持MyISAM引擎） DEFAULT CHARSET=utf8;

在现有表中创建全文索引：

ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);

创建完全文索引之后使用也有要注意的地方：

众所周知在数据库中进行模糊查询是使用like关键字进行查询的，例如：

SELECT * FROM article WHERE content LIKE ‘%查询字符串%';

那么，我们在使用全文索引也这样使用吗？当然不是，我们必须使用特有的语法才能使用全文索引进行查询，例如，我们想要在article表的title和content列中全文检索指定的查询字符串，我们可以如下编写SQL语句：

SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串');

强烈注意：MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx（斯芬克斯）/Coreseek技术来处理中文。

注：目前，使用MySql自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。

如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。

删除索引sql语句：alter table table_name drop index 索引名;

通过上面的简单介绍后，那么应该在哪些字段上添加索引呢？

1、频繁查询的字段，应该创建索引。

2、更新非常频繁的字段，不应该创建索引。

3、唯一性太差的字段，比如 gender字段，就不应该创建索引。

4、不会出现在where条件之后的字段，不应该创建索引。

满足一下条件，应该创建索引：

1、频繁要查询的字段，经常出现在where条件后面的字段，应该创建索引。

2、更新不频繁的字段，可以创建索引。

索引使用的注意事项

1.对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。

比如我们对title,content 添加了复合索引

select * from table_name where title = 'test';会用到索引

select * from table_name where content = 'test';不会用到索引

2.对于使用like的查询，查询如果是‘%a'不会使用到索引,而 like 'a%'就会用到索引。最前面不能使用%和_这样的变化值

3.如果条件中有or，即使其中有条件带索引也不会使用。

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。

查看索引的使用情况：show status like‘Handler_read%';

handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。

总结

mysql 数据库里搜索商品名称，但是name(varchar)字段没建立索引，用什么方法效率最高？

单独的索引和复合索引的索引。单一索引，只包含单个列，一个表可以有多个单独的索引，而不是索引的组合的索引。综合指数，即包含多个列的电缆。

MySQL索引的类型包括：

（1）一般指数

这是最基本的索引，它没有任何。它创造了几种方法：

◆创建一个索引

CREATE INDEX INDEXNAME ON mytable的（用户名（长度））;

如果CHAR，VARCHAR类型，长度可以小于的实际长度场，如果是BLOB和TEXT类型，必须指定长度，下同。

◆表结构

ALTER mytable的ADD INDEX [INDEXNAME]开（用户名（长度））

◆当您创建一个表直接指定

CREATE TABLE mytable的（ID INT NOT NULL ，用户名VARCHAR（16）NOT NULL，索引[INDEXNAME]（用户名（长度）））;

删除索引的语法：

DROP INDEX [INDEXNAME] ON mytable的;

（2）唯一索引

它类似于以前的一般指标，不同的是：索引列的值必须是唯一的，但是允许空值。如果它是一个综合指数相结合，列值？？必须是唯一的。它创造了几种方法：

◆创建一个索引

CREATE UNIQUE INDEX INDEXNAME ON mytable的（用户名（长度））

◆表结构

ALTER mytable的ADD UNIQUE [INDEXNAME]开启（用户名（长度））

◆当您创建一个表直接指定

CREATE TABLE mytable的（ID INT NOT NULL，用户名VARCHAR（16）NOT NULL，UNIQUE [INDEXNAME]（用户名（长度））） ;

（3）主键索引

这是一个特殊的唯一索引不允许空值。一般的时候同时创建主键索引的表的结构：

CREATE TABLE mytable的（ID INT NOT NULL，用户名VARCHAR（16）NOT NULL，PRIMARY KEY（ID））;

>当然，你可以使用ALTER命令。请记住：一个表只能有一个主键。

（4）综合指数

对于图像的对比度和组合索引单列索引，添加更多的字段的表：

CREATE TABLE mytable的（ID INT NOT NULL，用户名VARCHAR（16）NOT NULL ，城市VARCHAR（50）NOT NULL，年龄INT NOT NULL）;

为了进一步榨取MySQL的效率，就要考虑建立组合索引。是的名称，城市，年龄建成一个索引，其中：

ALTER TABLE mytable的ADD INDEX name_city_age（名（10），城市，年龄）;

建立表时，16 usernname长度，其中10。这是因为在正常情况下不超过10名，这将加快查询速度索引的长度，该指数将减少文件大小，提高INSERT的更新速度。

如果您创建的每个usernname一个单独的索引，城市，年龄，所以该表有三个单独的索引，上面的查询和索引的组合效率会有很大的不同，远远超过我们的组合索引少。虽然此时有了三个索引，但MySQL只可以使用它们认为这似乎是最有效的单一指标。

建立这样的组合索引，其实是三组相当于建立了以下综合指数：

usernname，城市，年龄usernname，城市usernname

为什么没有城市，年龄该做组合索引？它是一个综合指数，因为MySQL的结果“最左前缀”一个。这只是意味着，左侧只有从一开始的组合。不仅包含了这三个疑问将在综合指数中使用，下面的几个SQL就会使用这个组合索引：

SELECT * FROM mytable的WHREE用户名=“管理员”和城市=“郑州”SELECT * FROM mytable的WHREE用户名= “管理员”

接下来的几个未使用：

SELECT * FROM mytable的WHREE年龄为20及城市=“郑州”SELECT * FROM mytable的WHREE城市=“郑州”

（5）索引时间

在这里，我们已经学会了建立索引，那么我们需要在什么情况下创建索引？通常，当连接列和出现在需要建立索引，但也不完全如此，因为MySQL只<，，> =，BETWEEN，IN，LIKE，有的时候会使用索引。例如：

选择t.Name FROM mytable的吨LEFT JOIN mytable的M于t.Name = m.username WHERE m.age = 20和m.city ='郑州'

这时候就需要城市与年龄索引，由于userame mytable的表也出现在JOIN子句中，也有必要建立索引。

只是它需要像索引的时候一定提及。因为在一开始通配符％和_的查询，MySQL不会使用索引。例如，下面的语句将使用索引：

SELECT * FROM mytable的其中username like'admin％'

下一句不会使用：

SELECT * FROM mytable的WHEREt名称LIKE'％ admin的

因此，更应注意使用LIKE差异。

（6）指数

的不足之处上面是说使用索引的好处，但过量使用索引将会造成滥用。因此，该指数也有其缺点：

◆虽然索引大大提高了查询速度，但会降低更新表，如表的INSERT，UPDATE的速度和DELETE。因为当你更新表，MySQL只保存数据，而且还节省大约索引文件。

◆建立索引会占用磁盘空间的索引文件。通常这个问题不是太严重，但如果你是在一个大表创建各种复合索引，索引文件将很快扩大。

指数只是其中的一个因素，以提高效率，如果你有大量的数据的MySQL表，你需要花时间去研究建立最佳的索引，或优化查询。

（7）使用索引

注意事项使用索引时，下面的一些技巧和注意事项：？？

◆索引不会包含NULL值的列只要列中包含 BR />有NULL值？将不会被包含在索引中，复合索引，只要有包含NULL值的列，那么这个列是此复合索引无效。所以，我们不要让默认字段为NULL的数据库设计。

就用短串联指数指标，如果可能的话，你应该指定一个前缀长度。例如，如果一个CHAR（255）列，如果在前10或20个字符之内，多个值？是独一无二的，那就不要索引整个列。短索引不仅可以提高查询速度而且可以节省磁盘空间和I / O操作。

◆索引列排序

MySQL查询只使用一个索引，因此如果where子句已在索引中使用，然后按列的顺序将不会使用索引。因此，不要使用默认的数据库排序时排序操作即可满足要求;尽量不要包含多个排序列，如果需要的话最好创建这些列的一个综合指数。

◆like语句

在正常情况下运行不鼓励使用类似的操作，如果非使用不可，如何使用也是一个问题。如“％AAA％”不会使用索引而不是像“AAA％”可以使用索引。

◆不要在列选择*从用户那里YEAR（录入）<2007年

操作将在每个行上进行的，这将导致失败而进行全表索引扫描，所以我们可以改变

选择*从用户那里录入时间<'2007-01-01';

◆不使用NOT IN和>

更多操作上的MySQL索引类型进行了介绍。

转移： http://www.zbite.com/action-viewthread - TID-33491

显示全文

全部频道

mysql 给表里的某个字段建立索引