文档章节

MySQL数据库索引

满小茂
 满小茂
发布于 2016/08/23 10:25
字数 1364
阅读 94
收藏 4

 

MySQL数据库索引分成三类:普通索引,唯一索引和主索引

1、普通索引

  普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件 (WHEREcolumn=)或排序条件(ORDERBYcolumn)中的数据列创建索引。只要有可能,就应该选择一个数据最整齐、最紧凑的数据列(如 一个整数类型的数据列)来创建索引。

2、唯一索引

  普通索引允许被索引的数据列包含重复的值。比如说,因为人有可能同名,所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。

  如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好 处:一是简化了MySQL对这个索引的管理工作,这个索引也因此而变得更有效率;二是MySQL会在有新记录插入数据表时,自动检查新记录的这个字段的值 是否已经在某个记录的这个字段里出现过了;如果是,MySQL将拒绝插入那条新记录。也就是说,唯一索引可以保证数据记录的唯一性。事实上,在许多场合, 人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。

3、主索引

  在前面已经反复多次强调过:必须为主键字段创建一个索引,这个索引就是所谓的“主索引”。主索引与唯一索引的唯一区别是:前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

4、外键索引

  如果为某个外键字段定义了一个外键约束条件,MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

5、复合索引

  索引可以覆盖多个数据列,如像INDEX(columnA,columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索 引。如果查询操作只需要用到columnA数据列上的一个索引,就可以使用复合索引INDEX(columnA,columnB)。不过,这种用法仅适用 于在复合索引中排列在前的数据列组合。比如说,INDEX(A,B,C)可以当做A或(A,B)的索引来使用,但不能当做B、C或(B,C)的索引来使 用。

6、创建索引方法

 查看索引  show index from table_name
alter table 数据库add index 索引名称(数据库字段名称)
PRIMARY KEY(主键索引)
   ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
UNIQUE(唯一索引)
   ALTER TABLE `table_name` ADD UNIQUE (`column`)
INDEX(普通索引)
   mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
FULLTEXT(全文索引)
   ALTER TABLE `table_name` ADD FULLTEXT ( `column')

多列索引
        ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

7.删除索引

    DROP INDEX index_name ON talbe_name
 ALTER TABLE table_name DROP INDEX index_name
 ALTER TABLE table_name DROP PRIMARY KEY

8.强制索引

        强制使用索引 use index(索引键的名字)和 force index(索引键的名字) 都是强制mysql优化器 使用索引来查询。

            select *from table force index(索引键的名字)  where xxxx=xxxx

            select * from table force index(PRI) limit 2;(强制使用主键)

      禁止某个索引  ignore index(索引名或者主键PRI)

            select *from table ignore index(索引键的名字)  where xxxx=xxxx

9.MySQL建立索引的原则

1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。


2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式


3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录


4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);


5.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可

© 著作权归作者所有

满小茂
粉丝 79
博文 122
码字总数 138354
作品 0
成都
程序员
私信 提问
SQL基础-DDL数据定义语言

SQL数据定义语言-DDL语句 DDL语句部分使我们有能力创建或删除表格。我们也可以定义索引(键),规定表之间的链接,以及施加表间的约束。 语句 释义 CREATE DATABASE 创建新数据库 ALTER DATA...

ZHAO_JH
2018/08/10
37
0
MariaDB/Mysql的用法

要使用MariaDB/Mysql,先要利用yum安装 [root@localhost ~]# yum install -y mariadb-* //利用这条命令能把所有关于MariaDB的软件工具安装上 若本来Linux(CentOS)已经安装,可用 [root@loca...

Vayne36
2018/07/23
0
0
mysql和oracle的不同

一、日期处理 二、连接字符串在Oracle中用 ,SqlServer中用+,MySQL中用concat('a','b','c') 三、mysql与oracle数据库实现自增列的区别: mysql可以实现自增列,只要在建表时设置auto_incremen......

-wangming-
2016/06/06
93
0
SQL-SQL优化-索引

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

掘金官方
2017/12/25
0
0
MySql数据库 学习记录

什么事MySQL数据库? 通常意义上,数据库也就是数据的集合,具体到计算机上数据库可以是存储器上一些文件的集合或者一些内存数据的集合。我们常说的MySQL数据库,sql server数据库等其实是数...

杨春炼
2016/12/24
37
0

没有更多内容

加载失败,请刷新页面

加载更多

目标检测中 yolo 的mAP是什么含义?

mAP定义及相关概念 P => precision,即 准确率 R => recall,即 召回率 PR曲线 = >即 以 precision 和 recall 作为 纵、横轴坐标 的二维曲线。一般来说,precision 和 recall 是 鱼与熊掌 的...

小松1
8分钟前
2
0
用jdk1.8的断言来做非空判断

Assert.notNull(user, "没有获得登录用户信息"); 看源码如下: public static void notNull(Object object, String message) { if (object == null) { throw new IllegalArgum......

architect刘源源
13分钟前
3
0
免费节假日api每一时间更新 2020年 部分节假日安排

根据国务院办公厅关于2020年部分节假日安排的通知国办发明电〔2019〕16号.免费节假日api每一时间更新 2020年 部分节假日安排 http://tool.bitefu.net/jiari/ 各省、自治区、直辖市人民政府,...

xiaogg
16分钟前
4
0
2018NOIP各省一等奖分数线

提高组 普及组

SamXIAO
25分钟前
6
0
常见的PPT时间轴怎么制作,这几种方法你要知道

在PPT当中,时间轴是一个非常重要的一个版块,很多PPT会用它来表示公司的发展历程和项目进度。但是对于PPT时间轴的制作很多人做法是一条直线上添几个点,标注出事件就完成了,可是这样也太过...

TeFuiro
31分钟前
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部