。默认情况下大多使用Btree索引,该索引就是通常所见 唯一索引、聚簇索引等等,Btree用在OLTP,加快查询速度

查询表索引

show  index  from  tablename

   查询表结构

Desc tablename

   删除表索引

Drop index index_name on table_name

//表测试用

select area from tt where area='三亚'

CREATE INDEX index_name ON tt (area)

drop index index_name on tt

 

 

在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

 

创建普通索引、UNIQUE索引或PRIMARY KEY索引


1.ALTER TABLE 修改表结构添加索引
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
2.CREATE INDEX 直接添加索引
CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。

3.创建表的时候同时创建索引

CREATE TABLE  mytable( 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, UNIQUE [indexName] (username(length)) );

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

4.单列索引、多列索引

多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引

5.组合索引(最左前缀)

平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。例如上表中针对titletime建立一个组合索引:ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))。建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:

–title,time     –title

为什么没有time这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引,如下面的几个SQL所示:

(1)–使用到上面的索引

SELECT * FROM article WHREE title='测试' AND time=1234567890;

SELECT * FROM article WHREE utitle='测试';

2–不使用上面的索引

SELECT * FROM article WHREE time=1234567890;

6. 删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
其中,前两条语句是等价的,删除掉table_name中的索引index_name。
3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。


7.索引类型
在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。
PRIMARY KEY索引和UNIQUE索引非常类似。事实上,PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY,因为一个表中不可能具有两个同名的索引。
下面的SQL语句对students表在sid上添加PRIMARY KEY索引。
ALTER TABLE students ADD PRIMARY KEY (sid)


索引使用场景

1.普通索引
最基本的索引,没有任何限制
2.唯一索引
索引列的值必须唯一,但允许有空值
3.主键索引
主键索引属于一种特殊的唯一索引,不允许有空值
4.单列索引
单个多列索引(组合索引)效率高于多个单列索引
5.最左前缀(Leftmost Prefixing):多列索引
多列索引只有在where条件中含有索引中的首列字段时才有效 
例如:fname_lname_age索引,
以下的搜索条件MySQL都将使用 :
fname_lname_age索引:firstname,lastname,age;firstname,lastname;firstname,其他情况将不使用

 

 

索引存储结构

 按照存储结构划分btree,hash,bitmap,fulltext

(参考http://blog.csdn.net/diqi77/article/details/51482482)

MySQL何时使用索引

对一个键码使用>, >=, =, <, <=, IS NULLBETWEEN

  1. SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5; 
  2. SELECT * FROM table_name WHERE key_part1 IS NULL; 

当使用不以通配符开始的LIKE

  1. SELECT * FROM table_name WHERE key_part1 LIKE 'jani%' 

在进行联结时从另一个表中提取行时

  1. SELECT * from t1,t2 where t1.col=t2.key_part 

找出指定索引的MAX()MIN()

  1. SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10 

一个键码的前缀使用ORDER BYGROUP BY

  1. SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3 

在所有用在查询中的列是键码的一部分时

  1. SELECT key_part3 FROM table_name WHERE key_part1=1 

索引的不足之处

 

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

1.虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERTUPDATEDELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

 

索引失效的常见情况

 

1.WHERE字句的查询条件里有不等于号(WHERE column!=…),MYSQL将无法使用索引 

 

2.类似地,如果WHERE字句的查询条件里使用了函数(如:WHERE DAY(column)=…),MySQL将无法使用索引

 

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

 

4.如果WHERE 子句的查询条件里使用了比较操作符LIKEREGEXPMYSQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说,如果查询条件 是LIKE 'abc%',MYSQL将使用索引;如果条件是LIKE '%abc'MYSQL将不使用索引。

 

5.ORDER BY操作中,MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此,在涉及多个数据表的查询里,即使有索引可用,那些索引在加快ORDER BY操作方面也没什么作用。

 

6.如果某个数据列里包含着许多重复的值,就算为它建立了索引也不会有很好的效果。比如说,如果某个数据列里包含了净是些诸如“0/1”“Y/N”等值,就没有必要为它创建一个索引。

 

7.索引有用的情况 下就太多了。基本只要建立了索引,除了上面提到的索引不会使用的情况下之外,其他情况只要是使用在WHERE条件里,ORDER BY 字段,联表字段,一般都是有效的。 建立索引要的就是有效果。 不然还用它干吗? 如果不能确定在某个字段上建立的索引是否有效果,只要实际进行测试下比较下执行时间就知道。

 

8.如果条件中有or(并且其中有or的条件是不带索引的),即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引

 

9.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引

 

10.如果mysql估计使用全表扫描要比使用索引快,则不使用索引