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

编程之家2023-10-2096次浏览

这篇文章给大家聊聊关于mysql数据库索引，以及如何正确合理的建立MYSQL数据库索引对应的知识点，希望对各位有所帮助，不要忘了收藏本站哦。

如何正确合理的建立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数据库索引

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。

在数据库表中，对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL);我们随机向里面插入了10000条记录，其中有一条：5555, admin。

在查找username="admin"的记录 SELECT* FROM mytable WHERE username=‘admin‘;时，如果在username上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。相反，MySQL会扫描所有记录，即要查询10000条记录。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

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）组合索引

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

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

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="郑州"

比如有一条语句是这样的：select* from users where area=’beijing’ and age=22;

如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。

如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为“最佳左前缀”特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

（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值的列

◆使用短索引

◆索引列排序

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索引类型进行了介绍。

######################################################################################################################

表的主键

自动建立唯一索引

如zl_yhjbqk（用户基本情况）中的hbs_bh（户标识编号）

表的字段唯一约束

ORACLE利用索引来保证数据的完整性

如lc_hj（流程环节）中的lc_bh+hj_sx（流程编号+环节顺序）

直接条件查询的字段

在SQL中用于条件约束的字段

如zl_yhjbqk（用户基本情况）中的qc_bh（区册编号）

select* from zl_yhjbqk where qc_bh=<2000 andqc_bh>=5000;

查询中与其它表关联的字段

字段常常建立了外键关系

如zl_ydcf（用电成份）中的jldb_bh（计量点表编号）

select* from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’

查询中排序的字段

排序的字段如果通过索引去访问那将大大提高排序速度

select* from zl_yhjbqk order by qc_bh（建立qc_bh索引）

select* from zl_yhjbqk where qc_bh=’7001’ order by cb_sx（建立qc_bh+cb_sx复合索引，注：只是一个索引，其中包括qc_bh和cb_sx字段）

查询中统计或分组统计的字段

select max(hbs_bh) from zl_yhjbqk

select qc_bh,count(*) from zl_yhjbqk group by qc_bh

什么情况下应不建或少建索引

表记录太少

如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。

如表zl_sybm（使用部门）一般只有几条记录，除了主键外对任何一个字段建索引都不会产生性能优化，实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如：

select* from zl_sybm where sydw_bh=’5401’（对sydw_bh建立索引不会产生性能优化）

经常插入、删除、修改的表

对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如 zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等业务表。

数据重复且分布平均的表字段（如：性别字段）

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为 50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

经常和主字段一块查询但主字段索引值比较多的表字段

如gc_dfss（电费实收）表经常按收费序号、用户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的情况，如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收费序号索引就已经将记录减少到只有几条，如果再按后面的几个字段索引查询将对性能不产生太大的影响。

对千万级MySQL数据库建立索引的事项及提高性能的手段

一、注意事项：

首先，应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据，在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

其次，在对建立索引的时候要对表进行加锁，因此应当注意操作在业务空闲的时候进行。

二、性能调整方面：

首当其冲的考虑因素便是磁盘I/O。物理上，应当尽量把索引与数据分散到不同的磁盘上（不考虑阵列的情况）。逻辑上，数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。

其次，我们知道，在建立索引的时候要对表进行全表的扫描工作，因此，应当考虑调大初始化参数 db_file_multiblock_read_count的值。一般设置为32或更大。

再次，建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作，因此，应当调整排序区的大小。

9i之前，可以在session级别上加大sort_area_size的大小，比如设置为100m或者更大。

9i以后，如果初始化参数workarea_size_policy的值为TRUE，则排序区从pga_aggregate_target里自动分配获得。

最后，建立索引的时候，可以加上nologging选项。以减少在建立索引过程中产生的大量 redo，从而提高执行的速度。

######################################################################################################################

一般来说，以下的栏位都必须记得加上索引：

1、关联查询的外键字段要加索引(Foreign key)

2、会被排序的栏位(被放在order方法中)

3、会被查询的栏位(被放在where方法中)

4、会被group的栏位(被放在group方法中)

写少读多就多建索引,写多读少就少建索引,数据量少就不建索引。

设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。

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

标签：

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

bitsCN.com

普通MySQL运行，数据量和访问量不大的话，是足够快的，但是当数据量和访问量剧增的时候，那么就会明显发现MySQL很慢，甚至down掉，那么就要考虑优化我们的MySQL了。其中优化MYSQL的一个重要环节就是为数据库建立正确合理的索引。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。也就是说索引可以大大减少数据库管理系统查找数据的时间。索引有哪些优点？ 1、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 2、可以大大加快数据的检索速度，这也是创建索引的最主要原因。 3、可以加速表和表之间的连接，这在实现数据的参考完整性方面特别有意义。 4、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。索引有哪些缺点？ 1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 2、除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，需要的空间就会更大。 3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。索引有哪些类型？ 1、普通索引这是最基本的索引类型，而且它没有唯一性之类的限制。 2、唯一性索引这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。 3、主键它是一种特殊的唯一索引，不允许有空值。 4、全文索引 MySQL从3.23.23版开始支持全文索引和全文检索。单列索引和组合索引：单列索引就是把索引单独建立在一个字段上。组合索引复合索引就是一个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。建立和使用索引有哪些注意事项： 1、索引要建立在经常进行select操作的字段上。这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 2、索引要建立在值比较唯一的字段上。这样做才是发挥索引的最大效果。，比如主键的id字段，唯一的名字name字段等等。如果索引建立在唯一值比较少的字段，比如性别gender字段，寥寥无几的类别字段等，刚索引几乎没有任何意义。 3、对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。 4、当修改性能远远大于检索性能时，不应该创建索引。修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。 5、在WHERE和JOIN中出现的列需要建立索引。 6、在以通配符%和_开头作查询时，MySQL索引是无效的。但是这样索引是有效的：select* from tbl1 where name like'xxx%'，所以谨慎的写你的SQL是很重要的。用一个例子详细说明单列索引与组合索引的区别及索引使用中的一些细节建一个表： CREATE TABLE myIndex( i_testID INT NOT NULL AUTO_INCREMENT, vc_Name VARCHAR(50) NOT NULL, vc_City VARCHAR(50) NOT NULL, i_Age INT NOT NULL, i_SchoolID INT NOT NULL, PRIMARY KEY(i_testID));在这10000条记录里面7上8下地分布了5条vc_Name="erquan"的记录，只不过city,age,school的组合各不相同。来看这条T-SQL： SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='郑州' AND i_Age=25;首先考虑建单列索引：在vc_Name列上建立了索引。执行T-SQL时，MYSQL很快将目标锁定在了vc_Name=erquan的5条记录上，取出来放到一中间结果集。在这个结果集里，先排除掉vc_City不等于"郑州"的记录，再排除i_Age不等于25的记录，最后筛选出唯一的符合条件的记录。虽然在vc_Name上建立了索引，查询时MYSQL不用扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样的，在vc_City和i_Age分别建立的单列索引的效率相似。为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将vc_Name,vc_City,i_Age建到一个索引里： ALTER TABLE myIndex ADD INDEX name_city_age(vc_Name(10),vc_City,i_Age);--注意了，建表时，vc_Name长度为50，这里为什么用10呢？因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。执行T-SQL时，MySQL无须扫描任何记录就到找到唯一的记录！！肯定有人要问了，如果分别在vc_Name,vc_City,i_Age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率一样吧？嘿嘿，大不一样，远远低于我们的组合索引~~虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。建立这样的组合索引，其实是相当于分别建立了 vc_Name,vc_City,i_Age vc_Name,vc_City vc_Name这样的三个组合索引！为什么没有vc_City,i_Age等这样的组合索引呢？这是因为mysql组合索引"最左前缀"的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个T-SQL会用到： SELECT* FROM myIndex WHREE vc_Name="erquan" AND vc_City="郑州" SELECT* FROM myIndex WHREE vc_Name="erquan"而下面几个则不会用到： SELECT* FROM myIndex WHREE i_Age=20 AND vc_City="郑州" SELECT* FROM myIndex WHREE vc_City="郑州" bitsCN.com

关于本次mysql数据库索引和如何正确合理的建立MYSQL数据库索引的问题分享到这里就结束了，如果解决了您的问题，我们非常高兴。

win10无法解析服务器的dns地址 win10无法解析DNS怎么办主机硬件，电脑主机的五大硬件是什么