mysql学习五：索引

索引：由数据库中的一列或者多条组合而成，其作用是提高对表中数据的查询速度；类似于图书的目录，方便快速定位。

索引的优点：提高查询的速度；

缺点：创建和维护索引的时间增加了，但消耗不大；

索引的分类：

• 普通索：可以创建在任何数据类型中
• 唯一性索引：使用unique参数设置，在创建唯一索引时，该字段的值必须是唯一的
• 全文索引：使用fulltext参数设置；只有myisam引擎支持该索引；全文索引只能创建在char，varchar，text类型的字段上。主要作用就是提高查询较大字符串类型的速度
• 单列索引：单列索引，可以是普通索引，也可以是唯一索引，或全文索引
• 多列索引：是在表的多个字段上创建一个索引
• 空间索引：使用spatital参数可以设置空间索引；空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率；只有myisam引擎支持

 什么情况下添加索引？

• 该字段数据量比较大
• 该字段很少出现DML操作
• 该字段经常出现在where条件中

查看索引：

show index from 表名;

 删除索引，使用drop或alter

drop index 索引名 on 表名;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

 

 创建索引，使用create或alter：

create [unique|fulltext|spatital] index 索引名 on 表名 (字段名 [(长度)] [asc|desc]);

ALTER TABLE 表名 ADD 索引类型（unique,primary key,fulltext,index）[索引名]（字段名）

 

 

一、创建索引：

1.在创建表的时侯创建索引：

create table 表名(
字段名 类型类型 [完整性约束条件],
.....
[unique|fulltext|spatital] index|key [别名] (字段名 [(长度)] [asc|desc])
);

 创建普通索引：

create table user1 (id int,
userName varchar(20),
password varchar(20),
index (userName)
);

 创建唯一索引：

create table user2 (id int,
userName varchar(20),
password varchar(20),
unique index (userName)
);

 

给索引起别名：

create table user3 (id int,
userName varchar(20),
password varchar(20),
unique index alias_username (userName)
);

 

创建全文索引：

create table user4 (id int,
userName varchar(20),
password varchar(20),
fulltext index alias_username (userName)
);

 

创建多列索引：

create table user5 (id int,
userName varchar(20),
password varchar(20),
index alias_username (userName, password)
);

 为了进一步榨取mysql的效率，就可以考虑建立组合索引，如将用户的NAME,CITY,AGE建到一个索引里：

# 将name的前12个字节和cig，age组成组合索引
alter table user add index index_name_city_age (name(12), city, age);

 

建表时，NAME长度为64，这里用12，是因为一般情况下名字的长度不会超过12，这样会加快索引查询速度，还会减少索引文件的大小，提高INSERT，UPDATE的更新速度。 

  如果分别给NAME,CITY,AGE建立单列索引，让该表有3个单列索引，查询时和组合索引的效率是大不一样的，甚至远远低于我们的组合索引。虽然分别建立了三个索引，但mysql只能用到其中的那个它认为似乎是最有效率的单列索引，另外两个是用不到的，也就是说还是一个全表扫描的过程。

对于以上组合索引，相当于三组索引：

name,city,age
name,city
name

 

为什么没有CITY,AGE等这样的组合索引呢？这是因为mysql组合索引“最左前缀”的结果。简单的理解就是只从最左边的开始组合，并不是只要包含这三列的查询都会用到该组合索引。也就是说name_city_age(NAME(12),CITY,AGE)从左到右进行索引，如果没有左前索引，mysql不会执行索引查询。

 如果索引列长度过长,这种列索引时将会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行索引，前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值就可以创建)。

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- 这个值大于0.31就可以创建前缀索引,Distinct去重复

ALTER TABLE `user` ADD INDEX `uname`(title(10)); -- 增加前缀索引SQL,将人名的索引建立在10,这样可以减少索引文件大小,加快索引查询速度

 

2.在已经存在的表上创建索引

create [unique|fulltext|spatital] index 索引名 on 表名 (字段名 [(长度)] [asc|desc]);

 创建一个没有索引的表：

create table user6 (id int,
userName varchar(20),
password varchar(20)
);

 创建普通索引：

create index index_alias_username on user6(userName);

 创建唯一性索引：

create unique index index_alias_username1 on user6(userName);

 

创建多列索引：

create index index_alias_username2 on user6(userName, password);

 

 

3.用alter table语句来创建索引

alter table 表名 add [unique|fulltext|spatital] index 索引名 (字段名 [(长度)] [asc|desc]); 

 

create table user7 (id int,
userName varchar(20),
password varchar(20)
);

创建普通索引：

alter table user7 add index index_alias_username (userName);

创建唯一性索引：

alter table user7 add unique index index_alias_username1 (userName);

创建多列索引：

alter table user7 add index index_alias_username2 (userName, password);

 

 

二、删除索引:

drop index 索引名on 表名;

drop index index_alias_username1 on user7;

 

三、

以下情形不会使用索引，应尽量避免

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引,因为所有索引列参与了计算
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引,因为使用了函数运算,原理与上面相同
SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引
-- 正则表达式不使用索引
-- 字符串与数字比较不使用索引;
CREATE TABLE `a` (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引
select * from dept where dname='xxx' or loc='xx' or deptno=45 除非or的左右都可以使用索引，否则不会使用索引
-- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

 

     使用索引时，有一些技巧：

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

       只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。

    2.使用短索引

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

    3.索引列排序

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

    4.like语句操作

      一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。

    5.不要在列上进行运算

    6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

    7.索引要建立在经常进行select操作的字段上。

       这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

    8.索引要建立在值比较唯一的字段上。

    9.对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。

    10.在where和join中出现的列需要建立索引。

    11.where的查询条件里有不等号(where column != …),mysql将无法使用索引。

    12.如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。

    13.在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。