试列举何种情况考虑创建索引，在SQL中

Question

如题所述

Answer 1

转：
---使用索引优化数据库查询效率

1.不宜创建索引的情形

(1)经常插入，修改和删除的表

(2)数据量比较小的表，因为查询优化器在搜索索引时所花费的时间可能会大于遍历全表的数据所需要的时间

2.适合创建索引的情形

(1)为where子句中出现的列创建索引

(2)创建组合索引

(3)为group by 子句中出现的列创建索引

3.聚集索引的设计原则

(1)该列的数值是唯一的或者很少有重复的记录

(2)经常使用between ...and..按顺序查询的列

(3)定义identity的唯一列.

(4)经常用于对数据进行排序的列.

---无法使用索引的select语句

1.对索引列使用了函数,如：

select * from tb where max(id)=100

2.对索引列使用了'%xx'，如：

select * from tb where id like '%1'

需要注意的不是所有使用like关键字的select 语句都无法使用索引,比如

select * from tb where id like '1%'就可以使用索引

3.在where子句中对列进行类型转换(其实也是使用到了函数)

4.在组合索引的第1列不是使用最多的列,如在下面3个查询语句中建立组合索引,按顺序包含col2,col1,id列；

select * from tb where id='1' and col1='aa'

select id,sum(col1) from tb group by id

select * from tb where id='2' and col2='bb'

则第一句和第二句无法使用到索引 所以需要注意组合索引的顺序

5.在where 子句中使用in关键字的某些句子

当在in关键字后面使用嵌套的select语句，将无法使用在该列上定义的索引

如：

select

*

from

ta

where

id

in

(select id from tb where ....)

--这样可以用到索引

select * from tb where id in('1','2')

Answer 2

SQL索引的建立对于SQL的高效运行是很重要的，索引可以大大提高SQL的检索速度。
打个比方，如果合理的设计且使用索引的SQL是一辆兰博基尼的话，那么没有设计和使用索引的SQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
以mysql为例，SQL目前主要有以下几种索引类型：
1.普通索引
2.唯一索引
3.主键索引
4.组合索引
5.全文索引
1.普通索引
是最基本的索引，它没有任何限制。它有以下几种创建方式：
直接创建索引
CREATE INDEX index_name ON table(column(length))

2.唯一索引
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
（1）创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))

3.主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL , PRIMARY KEY (`id`)
);

4.组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

5.全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
创建表的适合添加全文索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`),
FULLTEXT (content)
);