文档章节

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

 屌丝男神
发布于 06/22 11:55
字数 1649
阅读 4
收藏 0
点赞 0
评论 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
后台开发常问面试题集锦(问题搬运工,附链接)

Java基础问题 String的’+’的性能及原理 java之yield(),sleep(),wait()区别详解-备忘笔记 深入理解Java Stream流水线 抽象 & abstract关键字 Java final 修饰符知识点总结(必看篇) Java中的...

大黄有故事
2017/11/18
0
0
Mysql索引介绍及常见索引(主键索引、唯一索引、普通索引、全文索引、组合索引)的区别

Mysql索引概念: 说说Mysql索引,看到一个很少比如:索引就好比一本书的目录,它会让你更快的找到内容,显然目录(索引)并不是越多越好,假如这本书1000页,有500也是目录,它当然效率低,目...

zhdan
2016/05/26
196
0
写会 MySQL 索引

原文出处:andyqian 前言 继上篇《读懂MySQL执行计划》后,在文章末尾,我们谈到了索引的概念,那么,今天我们就一起来聊一聊MySQL索引。 什么是索引? 百度百科是这样描述的: 索引是为来加...

andyqian
2017/11/29
0
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
后端开发:深入浅出的知识准备体系分享

博主渣渣本科,挣扎到十一月秋招终于结束了。面过百度/腾讯/小米/网易/搜狗/知乎/京东/360/瓜子。期间总结了一些面试题目,现在放上来。由于是博主自己的面经记录,所以涵盖不全面的话诸位请...

牛客网
01/05
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

崛起于Springboot2.X之集成MongoDb使用mongoTemplate CRUD(27)

1、pom依赖 <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.7</version></dependency><dependency> <groupId>log4j</......

木九天
13分钟前
0
0
切分log日志

新建logback.xml放到resource里面 <?xml version="1.0" encoding="utf-8"?><configuration> <appender name="consoleLog" class="ch.qos.logback.core.ConsoleAppender"> <......

talen
18分钟前
0
0
spring @Resource 和 @Autowired 的使用区别

这两个 注解 @Resource 和 @Autowired , 常识都知道 @Resource 是 JAVAEE 自带的,@Autowired 是 spring 的自定义注解。 一般情况下, 使用 bean的时候, 这两个注解 随便使用一个即可。 但...

之渊
23分钟前
0
0
springboot集成elasticsearch客户端问题记录

1背景说明 服务端ES版本为5.5.2,springboot版本为1.5.6。 工程中添加如下依赖 2问题记录 2.1 NetworkPlugin类找不到 报错java.lang.ClassNotFoundException: org.elasticsearch.plugins.Net...

zjg23
25分钟前
1
0
快速构建ceph可视化监控系统

前言 ceph的可视化方案很多,本篇介绍的是比较简单的一种方式,并且对包都进行了二次封装,所以能够在极短的时间内构建出一个可视化的监控系统 本系统组件如下: ceph-jewel版本 ceph_expor...

万建宁
25分钟前
0
0
Java构造器使用注意

public class 父类A {int age = 10;protected void say() {System.out.println("父类A");}public 父类A() {override();}public void override() {Syst...

咸鱼AI
25分钟前
0
0
TensorFlow 线性分类

构造直线 z = 2 * x - 3 * y + 4 x0*w0+x1*w1+b=0 x1=-x0* w0/w1-b/w1 斜率 k= -w0/w1 截距 -b/w1 随机生成数据,加入一定的偏差,用直线将二维平面分为两部分 使用线性模型拟合参数 损失函数...

阿豪boy
29分钟前
0
0
翻译冒泡排序测试

翻译一个冒泡排序: var a = [1,3,2,4,6,5];var f = 0;var n = a.length ;for( var i =1; i<= n; i++) { for( var j = n-1 ; j >= i; j --) { if(a[j] < a[j+1]) { ......

钟元OSS
30分钟前
0
0
maven父、子级版本号同时修改

命令: mvn versions:set -DnewVersion=0.0.2-SNAPSHOT

沉默的懒猫
31分钟前
1
0
Spring boot中的异常处理之注解响应

Controller层 return patientRepository.findById(id) .orElseThrow(() -> new NotFoundException(String.format("Patient %d not found", id))); Exception类 @ResponseS......

亚林瓜子
32分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部