返回顶部
首页 > 资讯 > 数据库 >浅谈Mysql哪些字段适合建立索引
  • 1848
分享到

浅谈Mysql哪些字段适合建立索引

Mysql字段索引Mysql字段建立索引 2022-05-25 18:05:28 1848人浏览 安东尼
摘要

1 数据库建立索引常用的规则如下: 表的主键、外键必须有索引; 2、数据量超过300的表应该有索引; 3、经常与其他表进行连接的表,在连接字段上应该建立索引; 4、经常出现在Where子句中的字段,特别是

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

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

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

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

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

2、对千万级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

添加索引示例:


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区别如下:

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


SQL> create table test (id number, name varchar2(20)); 
Table created.

然后重新开一个session:


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

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


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

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

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

到此这篇关于浅谈Mysql哪些字段适合建立索引的文章就介绍到这了,更多相关Mysql字段索引内容请搜索自学编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持自学编程网!

您可能感兴趣的文档:

--结束END--

本文标题: 浅谈Mysql哪些字段适合建立索引

本文链接: https://www.lsjlt.com/news/10154.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • 浅谈Mysql哪些字段适合建立索引
    1 数据库建立索引常用的规则如下: 表的主键、外键必须有索引; 2、数据量超过300的表应该有索引; 3、经常与其他表进行连接的表,在连接字段上应该建立索引; 4、经常出现在Where子句中的字段,特别是...
    99+
    2022-05-25
    Mysql字段索引 Mysql 字段建立索引
  • mysql中适合加索引的字段有哪些
    这篇文章将为大家详细讲解有关mysql中适合加索引的字段有哪些,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。数据库中字段适合添加索引的有表中某个字段值离散度越高越适合,占...
    99+
    2024-04-02
  • mysql索引建立规则有哪些
    mysql索引建立规则有哪些,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。  mysql建立索引常用的规则如下:  1、表的主...
    99+
    2024-04-02
  • mysql的datetime字段建立索引并比较大小
    文章目录 1、问题背景2、优化过程1) 操作索引2)是否走索引判断3)datetime使用索引查询 1、问题背景 最近测试库查询一个表的数据,需要用到唯一的一个日期类型字段作为 where 的子查询(查询当天的数据...
    99+
    2023-08-20
    mysql 数据库 sql
  • 浅谈mysql哪些情况会导致索引失效
    下面有一些培训教学机构的口诀和我个人的一些总结: 为了讲解以下索引内容,我们先建立一个临时的表 test02 CREATE TABLE `sys_user` ( `id` v...
    99+
    2024-04-02
  • mysql中datetime字段建立索引并比较大小详解
    目录1、问题背景2、优化过程补充:mysql 时间Datetime 索引不生效问题总结1、问题背景 最近测试库查询一个表的数据,需要用到唯一的一个日期类型字段作为 where 的子查询(查询当天的数据),就正常...
    99+
    2022-12-15
    mysql datetime字段 mysql datetime类型
  • mysql建立索引要注意哪些事项
    在建立索引时,需要注意以下几个事项: 选择合适的列:只有在频繁用作查询条件的列上建立索引才会带来性能提升。通常,选择主键、唯一约...
    99+
    2024-04-09
    mysql
  • sql建立索引的方法有哪些
    在 SQL 中建立索引的方法有以下几种: CREATE INDEX 语句:可以使用 CREATE INDEX 语句来创建索引,语法...
    99+
    2024-04-02
  • 浅谈MySql整型索引和字符串索引失效或隐式转换问题
    目录问题概述问题重现问题引申结论问题概述 今天在上班时,DBA突然找出来一段sql,表示该sql存在隐式转换,不走索引。经过我们的查看后,发现是类型varchar的字段, 我们使用条...
    99+
    2024-04-02
  • MySQL数据库中建立索引的优缺点有哪些
    MySQL数据库中建立索引的优缺点有哪些,相信很多没有经验的人对此束手无策,为此本文总结了问题出现的原因和解决方法,通过这篇文章希望你能解决这个问题。为什么要创建索引呢  这是因为,创建索引可以大大提高系统...
    99+
    2024-04-02
  • MySQL为JSON字段创建索引方式(Multi-Valued Indexes 多值索引)
    目录多值索引简介创建多值索引jsON对象字段索引JSON数组对象索引在组合索引中创建多值索引多值索引的局限应用场景多值索引简介 从mysql 8.0.17 开始, InnoDB支持创建多值索引(Multi-Valued ...
    99+
    2024-04-02
  • MySQL为JSON字段创建索引方式(Multi-Valued Indexes 多值索引)
    目录多值索引简介创建多值索引JSON对象字段索引JSON数组对象索引在组合索引中创建多值索引多值索引的局限应用场景多值索引简介 从MySQL 8.0.17 开始, InnoDB支持创...
    99+
    2024-04-02
  • mysql创建联合索引要注意哪些事项
    创建联合索引时,需要注意以下事项: 联合索引包含多个字段,字段的顺序会影响查询性能。一般来说,将最经常用于查询的字段放在索引的前...
    99+
    2024-04-09
    mysql
  • Mysql创建json字段索引的两种方式
    目录一、前言二、通过虚拟列添加索引(Secondary Indexes and Generated Columns)三、多值索引(Using multi-valued Indexes)四、官网地址一、前言 jsON 数据...
    99+
    2023-06-09
    Mysql创建json字段索引 Mysql json创建索引
  • MySQL索引优化之适合构建索引的几种情况详解
    目录结论建立索引的场景小结结论 在where后面的过滤字段上建立索引(select/update/delete后面的where都是适用的),使用索引加快过滤效率,不用进行全表扫描在具有唯一要...
    99+
    2022-07-29
    MySQL 索引优化 MySQL 索引构建
  • MySQL中如何创建高效且合适的索引
    这篇文章主要介绍了MySQL中如何创建高效且合适的索引,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。1. 当使用索引列进行查询的时候尽量不要...
    99+
    2024-04-02
  • Laravel与ASP框架:哪个更适合构建索引?
    在当今数字化时代,构建索引已经成为了一个很普遍的需求。索引可以用于加速搜索、排序、过滤等操作。对于开发人员来说,选择一个适合构建索引的框架至关重要。Laravel和ASP框架是两个常用的框架,那么哪个更适合构建索引呢? 首先,让我们看一下...
    99+
    2023-08-29
    框架 索引 laravel
  • Mysql为json字段创建索引的两种方式
    目录 一、前言二、通过虚拟列添加索引(Secondary Indexes and Generated Columns)三、多值索引(Using multi-valued Indexes)四、官...
    99+
    2023-09-24
    mysql json 数据库
  • sqlserver索引的原理及索引建立的注意事项有哪些
    本篇文章为大家展示了sqlserver索引的原理及索引建立的注意事项有哪些,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。聚集索引,数据实际上是按顺序存储的,数据页就...
    99+
    2024-04-02
  • MySQL索引优化之不适合构建索引及索引失效的几种情况详解
    目录结论不建议建立索引的场景索引失效的场景小结结论 具体案例下文有详尽描述 不适合建立索引的场景: 数据量比较小的表不建议建立索引有大量重复数据的字段上不建议建立索引(类似:性别字段)需要进行频繁更新的表不建议建立索引w...
    99+
    2022-07-29
    MySQL 索引优化 MySQL 不适合构建索引的场景
软考高级职称资格查询
编程网,编程工程师的家园,是目前国内优秀的开源技术社区之一,形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容,为IT开发者提供了一个发现、使用、并交流开源技术的平台。
  • 官方手机版

  • 微信公众号

  • 商务合作