文档章节

浅谈mysql的索引设计原则以及常见索引的区别

 屌丝男神
发布于 06/22 11:55
字数 1649
阅读 4
收藏 0

索引定义:是一个单独的,存储在磁盘上的数据库结构,其包含着对数据表里所有记录的引用指针.

数据库索引的设计原则:

为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引。
那么索引设计原则又是怎样的?

1.选择唯一性索引

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

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

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

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

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

4.限制索引的数目

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

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

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

6.尽量使用前缀来索引

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

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

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

8.小表不应建立索引;包含大量的列并且不需要搜索非空值的时候可以考虑不建索引

----------------------------------------------------------

mysql索引相关小常识:

一、 经常被用来过滤记录的字段。

1. primary key 字段, 系统自动创建主键的索引;
2. unique key 字段,系统自动创建对应的索引;
3. foreign key 约束所定义的作为外键的字段;

4. 在查询中用来连接表的字段;

5. 经常用来作为排序(order by 的字段)基准的字段;

二、索引会占用磁盘空间,创建不必要的索引只会形成浪费。

三、索引的创建必须考虑数据的操作方式。

1.内容很少变动,经常被查询,为它多创建几个索引無所谓;

2.经常性,例行性变动的表而言,则需要谨慎地创建确实必要的索引;

四、primary key 和 unique key的区别

1. 作为Primary Key的域/域组不能为null。而Unique Key可以。

2. 在一个表中只能有一个Primary Key,而多个Unique Key可以同时存在。

更大的区别在逻辑设计上, Primary Key一般在逻辑设计中用作记录标识,这也是设置
Primary Key的本来用意, 而Unique Key只是为了保证域/域组的唯一性。

五、复合索引和单一索引

复合索引是指多字段联合索引,查询时经常需要这几个字段组合一起为条件再查询

唯一索引主要是用主键ID索引,存储结构顺序与物理结构一致

如:create  index  idx  on  tbl(a,b) 

先按a排序, a相同的按b排序,所以当你查a或ab的时候,

能够利用到这个索引.但当你只查b的时候,索引对你的帮助不大.可能可以跳跃查找.

---------------------------------------------

添加和删除索引的情况:

1、表的主键、外键必须有索引;

2、数据量超过300w的表应该有索引;

3、经常与其他表进行连接的表,在连接字段上应该建立索引;

4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;

5、索引应该建在选择性高的字段上;

6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;

7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:

A、正确选择复合索引中的主列字段,一般是选择性较好的字段;

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;

C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;

D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;

E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;

8、频繁进行数据操作的表,不要建立太多的索引;

9、删除无用的索引,避免对执行计划造成负面影响;

本文转载自:https://blog.csdn.net/Happy_wu/article/details/78223560

共有 人打赏支持
粉丝 0
博文 9
码字总数 121315
作品 0
浦东新
PHPer、Laravel常见面试题及答案

最近社区里面有一篇文章引起了最多程序猿的关注,分享一下 Laravel、PHPer 面试可能会遇到的问题,看评论区不少小伙伴们被难倒,对于一些问题同样难倒了我(其实有很多啦),趁着周末有空,又...

angkee
2017/09/24
0
0
PHPer面试指南-MySQL 篇

本书的 GitHub 地址:https://github.com/todayqq/PHPerInterviewGuide 什么是索引,作用是什么?常见索引类型有那些?Mysql 建立索引的原则? 索引是一种特殊的文件,它们包含着对数据表里所...

angkee
01/24
0
0
MySQL 索引选择原则分析(一)

目的 数据库中很重要的设计一部分,莫过于索引了。B+树索引是MySQL中设计的索引。B+树索引是基于B+树基础发展而来的。 然而,在理解了B+树索引结构以后,对优化SQL会事半功倍。还针对前面MyS...

tara_qri
2015/10/13
140
0
浅谈mysql执行计划之type

mysql执行计划作为分析一条sql的执行效率的工具十分有效,通过explain关键字便可查看select语句的具体执行计划,分析其是否按我们设计的执行,是否使用了索引,是否全表扫描等等。不过有很多...

SawyerZhou
2017/12/14
0
0
Mysql索引类型 Normal,Unique,Full Text

mysql索引类型normal,unique,full text的区别是什么? normal:表示普通索引 unique:表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique ...

郏高阳
2014/11/27
0
0

没有更多内容

加载失败,请刷新页面

加载更多

docker多容器部署lnmp环境

环境:RHEL7.5 ip:192.168.10.102,主机名:lb02 一、创建web、数据库目录 web网站目录为:/wwwroot,属主属组:www [root@lb02 ~]# mkdir /wwwroot[root@lb02 ~]# useradd -s /sbin/nolo...

人在艹木中
14分钟前
0
0
eclipse运行springboot项目报错‘找不到或无法加载主类’

这是一个很烦躁的问题~,往往困住大家好长时间,然后各种百度。借此,咱将这个问题有可能产生的原因进行一下总结。若有不完善之处欢迎大家在下面留言指出~~ Duang!问题出现 然后开始尝试解决...

Code辉
34分钟前
0
0
springboot oauth2 跨域设置

@Overridepublic void configure(HttpSecurity http) throws Exception { http .authorizeRequests() .antMatchers("/security/**") .authentica......

昆虫大侠
36分钟前
0
0
08-利用思维导图梳理JavaSE-泛型

08-利用思维导图梳理JavaSE-泛型 主要内容 1.泛型的基本概念 1.1.定义 1.2.使用前提 1.3.使用泛型的好处 2.泛型的使用 2.1.泛型类定义 2.2.泛型对象定义 2.3.泛型中的构造方法 2.4.泛型方法的...

飞鱼说编程
38分钟前
0
0
Docker 部署 Spring Boot 项目指南

仅想在Docker里运行一个Spring Boot项目,捣鼓了许久。。。 本文主要适用于Windows环境下的Docker 一、运行环境 Windows 10 Maven 3.5 Docker 18.06.1-ce-win73 (19507) 二、创建Spring Boot...

AmosWang
44分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部