« 上一篇下一篇 »

MySQL索引这样建立才更加高效可行

 索引是快速搜索的关键,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,下同。

◆修正表结构

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;

(2)仅有索引

它与前面的一般索引相似,不同的就是:索引列的值有必要仅有,但允许有空值。

假如是组合索引,则列值的组合有必要仅有。它有以下几种创立方法:

◆创立索引

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))   ); 

(3)主键索引

它是一种特别的仅有索引,不允许有空值。一般是在建表的时分同时创立主键索引:

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

当然也能够用 ALTER 指令。记住:一个表只能有一个主键。

(4)组合索引

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

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); 

建表时,usernname长度为 16,这儿用 10。

这是由于一般状况下姓名的长度不会超过10,这样会加快索引查询速度,

还会减少索引文件的大小,进步INSERT的更新速度。

假如别离在 usernname,city,age上树立单列索引,让该表有3个单列索引,

查询时和上述的组合索引功率也会大不一样,远远低于咱们的组合索引。

尽管此刻有了三个索引,但MySQL只能用到其间的那个它认为似乎是最有功率的单列索引。

树立这样的组合索引,其实是相当于别离树立了下面三组组合索引:

usernname,city,age   usernname,city   usernname  为什么没有 city,age这样的组合索引呢?

这是由于MySQL组合索引“最左前缀”的成果。

简略的了解就是只从最左面的开始组合。

并不是只需包括这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:

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="郑州"

(5)树立索引的机遇

到这儿咱们现已学会了树立索引,那么咱们需求在什么状况下树立索引呢?

一般来说,在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时应留意以上的差异。

(6)索引的不足之处

上面都在说运用索引的优点,但过多的运用索引将会形成滥用。因而索引也会有它的缺陷:

◆尽管索引大大进步了查询速度,同时却会下降更新表的速度,如对表进行INSERT、UPDATE和DELETE。

由于更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

◆树立索引会占用磁盘空间的索引文件。

一般状况这个问题不太严峻,但假如你在一个大表上创立了多种组合索引,索引文件的会膨胀很快。

索引只是进步功率的一个要素,假如你的MySQL有大数据量的表,就需求花时间研讨树立最优秀的索引,或优化查询句子。

(7)运用索引的留意事项

运用索引时,有以下一些技巧和留意事项:

◆索引不会包括有NULL值的列

只需列中包括有NULL值都将不会被包括在索引中,复合索引中只需有一列含有NULL值,

那么这一列关于此复合索引就是无效的。所以咱们在数据库设计时不要让字段的默许值为NULL。

◆运用短索引

对串列进行索引,假如可能应该指定一个前缀长度。

例如,假如有一个CHAR(255)的列,假如在前10个或20个字符内,多数值是专一的,

那么就不要对整个列进行索引。短索引不仅能够进步查询速度而且能够节约磁盘空间和I/O操作。

◆索引列排序

MySQL查询只运用一个索引,因而假如where子句中现已运用了索引的话,那么order by中的列是不会运用索引的。

因而数据库默许排序能够符合要求的状况下不要运用排序操作;

尽量不要包括多个列的排序,假如需求最好给这些列创立复合索引。

◆like句子操作

一般状况下不鼓励运用like操作,假如非运用不行,怎么运用也是一个问题。

like “%aaa%” 不会运用索引而like “aaa%”能够运用索引。

◆不要在列进步行运算

select * from users where YEAR(adddate)<2007; 

将在每个行进步行运算,这将导致索引失效而进行全表扫描,因而咱们能够改成

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

◆不运用NOT IN和<>操作