索引是快速搜索的关键,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和<>操作