旗下导航:搜·么
当前位置:网站首页 > MySQL教程 > 正文

mysql数据库索引范例有哪几种?竖立要领和优缺点【MySQL教程】,MySQL索引

作者:搜教程发布时间:2019-11-27分类:MySQL教程浏览:33评论:0


导读:本文将偏重形貌masql数据库索引的四种范例,数据库索引怎样竖立?在WHERE和JOIN中涌现的列须要竖立索引,但也不完全云云,因为MySQL只对<,<=,=,>...
本文将偏重形貌masql数据库索引的四种范例,数据库索引怎样竖立?在WHERE和JOIN中涌现的列须要竖立索引,但也不完全云云,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时刻的LIKE才会运用索引。愿望本文能协助到人人,起首先来相识索引是什么吧,用一句话归纳综合就是: 索引是疾速搜刮的症结。

MySQL索引的竖立关于MySQL的高效运转是很主要的。下面引见几种罕见的MySQL索引范例

在数据库表中,对字段竖立索引能够大大进步查询速率。假如我们建立了一个 mytable表:

代码以下:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL );

我们随机向内里插入了10000条纪录,个中有一条:5555, admin。

在查找username="admin"的纪录 SELECT * FROM mytable WHERE username='admin';时,假如在username上已竖立了索引,MySQL不必任何扫描,即正确可找到该纪录。相反,MySQL会扫描一切纪录,即要查询10000条纪录。

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表能够有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。

MySQL索引范例包含:

一、平常索引

这是最基本的索引,它没有任何限定。它有以下几种建立体式格局:

1.建立索引

代码以下:

CREATE INDEX indexName ON mytable(username(length));

假如是CHAR,VARCHAR范例,length能够小于字段现实长度;假如是BLOB和TEXT范例,必需指定 length,下同。

2.修正表构造

代码以下:
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 建立表的时刻直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

-- 删除索引的语法:

DROP INDEX [indexName] ON mytable;

二、唯一索引

它与前面的平常索引相似,差别的就是:索引列的值必需唯一,但许可有空值。假如是组合索引,则列值的组合必需唯一。它有以下几种建立体式格局:

代码以下:

CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修正表构造
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- 建立表的时刻直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

三、主键索引

它是一种特别的唯一索引,不许可有空值。平常是在建表的时刻同时建立主键索引:

代码以下:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

固然也能够用 ALTER 敕令。记着:一个表只能有一个主键。

四、组合索引

为了抽象地对照单列索引和组合索引,为表增加多个字段:

代码以下:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

为了进一步压迫MySQL的效力,就要斟酌竖立组合索引。就是将 name, city, age建到一个索引里:

代码以下:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]
建表时,usernname长度为 16,这里用 10。这是因为平常状况下名字的长度不会凌驾10,如许会加快索引查询速率,还会削减索引文件的大小,进步INSERT的更新速率。

假如离别在 usernname,city,age上竖立单列索引,让该表有3个单列索引,查询时和上述的组合索引效力也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到个中的谁人它以为似乎是最有效力的单列索引。

竖立如许的组合索引,现实上是相当于离别竖立了下面三组组合索引:

usernname,city,age usernname,city usernname 为何没有 city,age如许的组合索引呢?这是因为MySQL组合索引“最左前缀”的效果。简朴的明白就是只从最左面的最先组合。并非只需包含这三列的查询都邑用到该组合索引,下面的几个SQL就会用到这个组合索引:
[code]
SELECT * FROM mytable WHREE username="admin" AND city="郑州" SELECT * FROM mytable WHREE username="admin"

而下面几个则不会用到:

代码以下:
SELECT * FROM mytable WHREE age=20 AND city="郑州" SELECT * FROM mytable WHREE city="郑州"

五、怎样竖立索引

到这里我们已学会了竖立索引,那末我们须要在什么状况下竖立索引呢?平常来说,在WHERE和JOIN中涌现的列须要竖立索引,但也不完全云云,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时刻的LIKE才会运用索引。比方:

代码以下:

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'

此时就须要对city和age竖立索引,因为mytable表的userame也涌现在了JOIN子句中,也有对它竖立索引的必要。

适才提到只要某些时刻的LIKE才需竖立索引。因为在以通配符%和_开首作查询时,MySQL不会运用索引。例以下句会运用索引:

代码以下:
SELECT * FROM mytable WHERE username like'admin%'
而下句就不会运用:

代码以下:

SELECT * FROM mytable WHEREt Name like'%admin'

因而,在运用LIKE时应注重以上的区分。

六、索引瑕玷

上面都在说运用索引的长处,但过量的运用索引将会形成滥用。因而索引也会有它的瑕玷:

1.虽然索引大大进步了查询速率,同时却会下降更新表的速率,如对表举行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保留数据,还要保留一下索引文件。

2.竖立索引会占用磁盘空间的索引文件。平常状况这个题目不太严峻,但假如你在一个大表上建立了多种组合索引,索引文件的会膨胀很快。

索引只是进步效力的一个要素,假如你的MySQL有大数据量的表,就须要花时候研讨竖立最优异的索引,或优化查询语句。

七、运用索引时须要注重:

运用索引时,有以下一些技能和注重事项:

1.索引不会包含有NULL值的列

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

2.运用短索引

对串列举行索引,假如能够应当指定一个前缀长度。比方,假如有一个CHAR(255)的列,假如在前10个或20个字符内,多数值是唯一的,那末就不要对全部列举行索引。短索引不仅能够进步查询速率而且能够节约磁盘空间和I/O操纵。

3.索引列排序

MySQL查询只运用一个索引,因而假如where子句中已运用了索引的话,那末order by中的列是不会运用索引的。因而数据库默许排序能够符合要求的状况下不要运用排序操纵;只管不要包含多个列的排序,假如须要最好给这些列建立复合索引。

4.like语句操纵

平常状况下不勉励运用like操纵,假如非运用不可,怎样运用也是一个题目。like “%aaa%” 不会运用索引而like “aaa%”能够运用索引。

5.不要在列上举行运算

代码以下:
select * from users where YEAR(adddate)<2007;

将在每一个行上举行运算,这将致使索引失效而举行全表扫描,因而我们能够改成:

代码以下:

select * from users where adddate<‘2007-01-01';

6.不运用NOT IN和<>操纵

以上,就对个中MySQL索引范例举行了引见。愿望对人人有所协助。

索引的两种要领:

B-tree索引

注: 名叫btree索引,大的方面看,都用的均衡树,但细致的完成上, 各存储引擎稍有差别,比方,严厉的说,NDB引擎,运用的是T-tree
Myisam,innodb中,默许用B-tree索引,B-tree的理论查询时候复杂度为O(log2 (N-1)),N为数据表的行数

hash索引
运用memory存储引擎的表中,默许是hash索引, hash的理论查询时候复杂度为O(1)

相干引荐:

详解mysql竖立索引的运用方法及优瑕玷剖析

索引的长处和瑕玷第1/2页

以上就是mysql数据库索引范例有哪几种?竖立要领和优瑕玷的细致内容,更多请关注ki4网别的相干文章!

标签:MySQL索引


欢迎 发表评论: