如何创建mysql索引以及索引的优缺点

mysql教程：索引的使用以及索引的优缺点

如何创建mysql索引以及索引的优缺点：

1. 索引（index）是帮助MySQL高效获取数据的数据结构。

它对于高性能非常关键，但人们通常会忘记或误解它。

索引在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引，也能有好的性能，  但是当数据增加的时候，性能就会下降很快。

Tip:蠕虫复制,可以快速复制大量的数据

例：insert into emp select * from emp; 

2. MySQL中常见的索引

 ◆普通索引 ◆唯一索引 ◆主键索引  ◆组合索引 ◆全文索引◆外键 （只有innodb存储引擎才支持）

2.1普通索引：

这是最基本的索引，它没有任何限制。有以下几种创建方式：

有以下几种创建方式：

◆创建索引

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

◆修改表结构

ALTER tablename  ADD INDEX indexName (username(length))

Tip：length可以小于字段实际长度;如果是BLOB 和 TEXT 类型，必须指定length ,下同

◆创建表的时候直接指定

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

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

create index index1 on mytable(id);  //创建普通索引

◆删掉索引：

drop index index1 on mytable;

有一个概念，

行定义：在声明字段（列）的时候定义的，比如primary key

表定义：在所有字段（列）声明完之后定义的，比如primary key，index

CREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL,index index1(username));

3.0唯一索引（unique）

索引列的值必须唯一，但允许有空值。

1）创建索引：Create UNIQUE INDEX indexName ON tableName(tableColumns(length))

2）修改表结构：Alter tableName ADD UNIQUE [indexName] ON (tableColumns(length)

3）创建表的时候直接指定：Create TABLE tableName ( [...], UNIQUE [indexName](tableColumns(length)); 

4.0主键索引（primary  key）

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

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

当然也可以用 ALTER 命令。

Tip：记住：一个表只能有一个主键。主键索引就是我们所说的主键。在一个表中，主键只能有一个，但是普通索引和唯一索引可以有多个。

5.0组合索引

5.1多列索引，由多个列共同来组成一个索引。

增加组合索引

alter table mytable add index name_city_age(username,city,age);

对于组合索引，必须以最左索引为前缀，依次排列的，才可以使用到组合索引，中间不能有间隔。

下面的可以使用到组合索引：

  ◆ username,city,age   

  ◆ usernname,city   

  ◆ usernname 

下面的不可以使用到组合索引：

  ◆ city,age   

  ◆ city   

  ◆ age 

注意组合索引，如果在某个表中，有多个索引，我们可以考虑一下，使用组合索引来优化。

5.2 Explain语句：可以查询sql 查询语句使用的索引类型

explain 语句

6. 为什么有索引，查询加快？

这是因为，创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

6.1在MySQL中，BTREE，二叉树

二叉树排序

35   17  39 9 28 65 56 87                                 

6.2索引的优点：

加快查询速度。

6.2 索引的缺点：

占用大量的磁盘空间。但是对插入、删除和更新有影响。

6.3使用索引时，有以下一些技巧和注意事项：

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

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

◆使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

◆索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

◆like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “�a%” 不会使用索引，而like “aaa%”可以使用索引。

◆不要在列上进行运算

select * from users where YEAR(adddate)<2007; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

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

◆不使用NOT IN和<>操作

谢谢关注 websites 博客!

2015春夏新款女九分袖连衣裙V领中裙公主修身胖MM显瘦牛仔裙特价

2015春夏新款女九分袖连衣裙V领中裙公主修身胖MM显瘦牛仔裙特价