首页 / MYSQL / （转）Mysql哪些字段适合建立索引

（转）Mysql哪些字段适合建立索引

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了（转）Mysql哪些字段适合建立索引，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9413字，纯文字阅读大概需要14分钟。

内容图文

数据库建立索引常用的规则如下：

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；

以上是一些普遍的建立索引时的判断依据。
索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。
因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
总的来说，小型表肯定不建索引，
或者数据库记录在亿条数据级以上，还是建议使用非关系型数据库。
还有些特殊字段的数据库，比如BLOB，CLOB字段肯定也不适合建索引。
其实这个问题更感觉偏向于做软件项目的一种经验。

对千万级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，从而提高执行的速度。

MySql在建立索引优化时需要注意的问题

设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。设计MySql索引的时候有一下几点注意：

1，创建索引

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

2，复合索引

比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效
率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,
salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀
特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

3，索引不会包含有NULL值的列

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

4，使用短索引

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

5，排序的索引问题

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

6，like语句操作

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

7，不要在列上进行运算

select * from users where
YEAR(adddate)

8，不使用NOT IN和操作

NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id

添加索引示例:

<code hljs="" markdown=""  has-numbering"="" style="box-sizing: border-box; outline: 0px; margin: 0px; padding: 8px; display: block; overflow-x: auto; color: #abb2bf; background: #282c34; border-radius: 4px; font-size: 14px; line-height: 22px; white-space: pre; word-wrap: break-word; font-family: Consolas, Inconsolata, Courier, monospace;">CREATE INDEX IDX_AUDITSTATUS ON [shanghaiDB].[dbo].[Activity](AUDITSTATUS) WITH(ONLINE=ON)
 
CREATE INDEX IDX_ANUMMID ON [nantongDB].[dbo].[Orders](ANUM,MID) WITH(ONLINE=ON)
 
CREATE INDEX IDX_SiteCode ON Usercenter.[dbo].MO(SiteCode) WITH(ONLINE=ON)
 
CREATE INDEX IDX_AccessDt ON [all].[dbo].[AccessLog](AccessDt) WITH(ONLINE=ON)

Create index注意n如果是大表建立索引，切记加上ONLINE参数

这几天在做数据库的优化，有个2亿记录的表，发现需要添加一个联合索引，结果就采用普通的create index index_name on tablename (entp_id,sell_date),结果悲剧了，把所有的DML语句都阻塞了，导致系统不能正常使用，还好是晚上10点，用户不是非常多，1个小时候，索引结束，阻塞解决；

上网查了一下，如果加上 online参数后，就可以在线做索引，而不需要阻塞所有的DML语句，血的教训，拿出来与各位共勉，具体online与不加online区别如下：

1. DML操作对create index 的影响。如果在create的时候，有其他的进程在对这个index 所对应的数据进行DML操作，create会受影响:

<code applescript="" has-numbering=""  java"="" style="box-sizing: border-box; outline: 0px; margin: 0px; padding: 8px; display: block; overflow-x: auto; color: #abb2bf; background: #282c34; border-radius: 4px; font-size: 14px; line-height: 22px; white-space: pre; word-wrap: break-word; font-family: Consolas, Inconsolata, Courier, monospace;">SQL> create table test (id number, name varchar2(20));
 
Table created.

然后重新开一个session:

<code oxygene="" has-numbering=""  delphi"="" style="box-sizing: border-box; outline: 0px; margin: 0px; padding: 8px; display: block; overflow-x: auto; color: #abb2bf; background: #282c34; border-radius: 4px; font-size: 14px; line-height: 22px; white-space: pre; word-wrap: break-word; font-family: Consolas, Inconsolata, Courier, monospace;">SQL> insert into test values (1,‘lms‘);
 
1 row created.
 
<no commit>
 
SQL> create index t1 on test(id);
create index t1 on test(id)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

2. 加online这个参数，这个参数加上以后，除了create过程中index 保持online状态，Oracle还会在create index之前等待所有DML操作结束，然后得到DDL锁，开始create.ndex altered.

SQL> create index t1 on test(id) online;
<hold before commit>
<after commit>
 
SQL> commit;
 
Commit complete.
 
Index altered.

如果不commit,上面的操作就会一直hold。

所以以后create索引和rebuild索引的时候最好加上online。

转自：https://blog.csdn.net/Su_Xingyu/article/details/79900290

mysql 索引类型以及创建

关于MySQL索引的好处，如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。还是以WordPress来说，其多个数据表都会对经常被查询的字段添加索引，比如wp_comments表中针对5个字段设计了BTREE索引。

一个简单的对比测试

以我去年测试的数据作为一个简单示例，20多条数据源随机生成200万条数据，平均每条数据源都重复大概10万次，表结构比较简单，仅包含一个自增ID，一个char类型，一个text类型和一个int类型，单表2G大小，使用MyIASM引擎。开始测试未添加任何索引。

执行下面的SQL语句：

`1`	`mysql>` `SELECT` `id,FROM_UNIXTIME(time)` `FROM` `article` `WHERE` `a.title=‘测试标题‘`

查询需要的时间非常恐怖的，如果加上联合查询和其他一些约束条件，数据库会疯狂的消耗内存，并且会影响前端程序的执行。这时给title字段添加一个BTREE索引：

`1`	`mysql>` `ALTER` `TABLE` `article` `ADD` `INDEX` `index_article_title` `ON` `title(200);`

再次执行上述查询语句，其对比非常明显：

MySQL索引的概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。上述SQL语句，在没有索引的情况下，数据库会遍历全部200条数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。如果我们把SQL语句换成“SELECT * FROM article WHERE id=2000000”，那么你是希望数据库按照顺序读取完200万行数据以后给你结果还是直接在索引中定位呢？上面的两个图片鲜明的用时对比已经给出了答案（注：一般数据库默认都会为主键生成索引）。

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

MySQL索引的类型

1. 普通索引

这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

01 –直接创建索引

<tbody style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margi 本文系统来源：https://www.cnblogs.com/abcdwxc/p/9855474.html

内容总结

以上是互联网集市为您收集整理的（转）Mysql哪些字段适合建立索引全部内容，希望文章能够帮你解决（转）Mysql哪些字段适合建立索引所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/501217.html

来源：【匿名】

【上一篇】mysql表锁、行锁、索引之间暧昧的关系【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【（转）Mysql哪些字段适合建立索引】教程文章相关的互联网学习教程文章

mysql 索引与优化like查询

详见：http://blog.yemou.net/article/query/info/tytfjhfascvhzxcyt1011. like %keyword 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。 2. like keyword% 索引有效。 3. like %keyword% 索引失效，也无法使用反向索引。 ==================================================================== 1. 使用下面的函数来进行模糊查询，如果出现的位...

MySQL索引背后的之使用策略及优化(高性能索引策略)【图】

本章的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。示例数据库　　为了讨论索引策略，需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的E-R关系图(引用自MySQL官方手册)：　　图12　　MySQL官方文档中关于此数据库的页面...

MySQL 8.0 隐藏索引【图】

隐式索引最明显的一个作用类似索引回收站。例如数据库长时间运行后，会积累很多索引，做数据库优化时，想清理掉没什么用的多余的索引，但可能删除某个索引后，数据库性能下降了，发现这个索引是有用的，就要重新建立。对于较大的表来说，删除、重建索引的成本是很高的，如果在清理索引时能先放入回收站，确认没影响后再彻底删除，有影响的话就恢复回来，这样就方便多了，把索引设置为隐藏/显示就可以实现这个需求。索引的隐藏不...

MySQL之left join表查询中发生字符集转换导致表索引失效【代码】【图】

一、出现的场景研发leader突然给了1条sql说这条sql在测试环境库db-stage执行非常的慢，放到线上db-read从库上执行非常的快。而且线上库从库的表的数据远多于测试环境库的表数据。让我分析下是什么问题？？二、sql内容如下 select odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id left join db_order.t_orders o on oid.order_id=o.order_id w...

MySQL索引背后的数据结构及算法原理

摘要本文以MySQL数据库为研究对象，讨论与数据库索引相关的一些话题。特别需要说明的是，MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引等等。为了避免混乱，本文将只关注于BTree索引，因为这是平常使用MySQL时主要打交道的索引，至于哈希索引和全文索引本文暂不讨论。文章主要内容分为三个部分。第一部分主要从数据结构及算法理论层面讨论M...

MYSQL索引

mysql的4种常用索引类型：唯一索引，主键索引，全文索引，以及普通索引。普通索引（INDEX）：普通索引为索引的核心部分，无限制，目的就是加快数据的访问速度，而主键索引，唯一索引，全文索引则是在普通索引延伸出来的有规则限制的索引，方便用于某些特定的业务场景。主键索引（PRIMARY）：可以理解主键（常用的那个id主键），每一张表都必须有一个主键，且不允许为空。唯一索引（UNIQUE）：内容具备唯一性，像手机号，身份证这种...

mysql测试索引在表中的作用【代码】

实验内容单表中的索引使用1．建表 create table t_book(id int,a1 varchar(200),a2 varchar(200),a3 varchar(200),a4 varchar(200),a5 varchar(200),a6 varchar(200),a7 varchar(200),a8 varchar(200),a9 varchar(200)) 2．写存储过程向表中插入较大数据//或其他方式使数据增大（1）绑定变量： PREPARE stmt1 FROM‘insert into t_book(id,a1,a2,a3,a4,a5,a6,a7,a8,a9)values(?,?,?,?,?,?,?,?,?,?)‘; （2）存储过程创建：delimite...

mysql中索引的使用【图】

索引是加速查询的主要手段，特别对于涉及多个表的查询更是如此。本节中，将介绍索引的作用、特点，以及创建和删除索引的语法。13.4.1 使用索引优化查询索引是快速定位数据的技术，首先通过一个示例来了解其含义及作用，详细的介绍请参考第14章。1．索引示例假设对于10.3节所建的表，各个表上都没有索引，数据的排列也没有规律，如表13.3所示。表13.3 没有索引的students表...

Mysql如何为表字段添加索引？？？

1.添加PRIMARY KEY（主键索引）：ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) ：ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) ：ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEXT(全文索引) ：ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 5.添加多列索引：ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `col...

MySql分区后创建索引加速单表查询和连表查询【代码】【图】

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。本文链接：https://blog.csdn.net/konkon2012/article/details/96482548为了加快查询，我们通常根据Where条件创建索引！那么分区后再创建索引，那就应该更快了！我们依据订单表和订单商品表举例，先创建表结构：CREATE TABLE `zstb_orders` ( `order_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `org_id` INT(10) UNSIGNED NOT NU...

mySql---剖析InnoDB索引原理【图】

摘要：本篇为参考别人的文章（http://blog.csdn.net/voidccc/article/details/40077329）1 各种树形结构本来不打算从二叉搜索树开始，因为网上已经有太多相关文章，但是考虑到清晰的图示对理解问题有很大帮助，也为了保证文章完整性，最后还是加上了这部分。先看看几种树形结构： 1 搜索二叉树：每个节点有两个子节点，数据量的增大必然导致高度的快速增加，显然这个不适合作为大量数据存储的基础结...

mysql索引整理

一.索引：1.创建一般索引 create index index_name on table_name(columnname[...]); 2.分析sql执行语句在select 之前加explain eg:explain select * from table_name;3.mysql从5.5.5之后默认采用innerdb为默认的存储引擎,该引擎支持的两种常见索引：b+树索引和hash索引，hash索引的效率高于b+树，但hash索引只支持=类型。b+树可以支持范围的eg:>< ，b+树一般三层即可表示，即访问3次io可找到数值。二.常见丢失索引的情况1.左前缀...

首页 / MYSQL / （转）Mysql哪些字段适合建立索引

（转）Mysql哪些字段适合建立索引

内容导读

内容图文

数据库建立索引常用的规则如下：

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

一、注意事项：

二、性能调整方面：

MySql在建立索引优化时需要注意的问题

1，创建索引

2，复合索引

3，索引不会包含有NULL值的列

4，使用短索引

5，排序的索引问题

6，like语句操作

7，不要在列上进行运算

8，不使用NOT IN和操作

添加索引示例:

Create index注意n如果是大表建立索引，切记加上ONLINE参数

1. DML操作对create index 的影响。 如果在create的时候，有其他的进程在对这个index 所对应的数据进行DML操作，create会受影响:

2. 加online这个参数，这个参数加上以后，除了create过程中index 保持online状态，Oracle还会在create index之前等待所有DML操作结束，然后得到DDL锁，开始create.ndex altered.

一个简单的对比测试

MySQL索引的概念

MySQL索引的类型

内容总结

内容备注

内容手机端

【（转）Mysql哪些字段适合建立索引】教程文章相关的互联网学习教程文章

MYSQL - 相关标签

字段 - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程

1. DML操作对create index 的影响。如果在create的时候，有其他的进程在对这个index 所对应的数据进行DML操作，create会受影响: