索引基础

查看表中已经存在 index：show index from table_name;

索引的创建与删除

索引的创建可以在CREATE TABLE语句中进行(一般是主键索引)，也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。

（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) ;

其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式，table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。

（2）使用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）删除索引。删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。

DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

***********************************************************************************

索引的分类：

mysql支持的存储引擎：Innodb MyIsam NDB Memory Archive。

mysql索引按存储结构:支持4种索引分别是：full-text，b-tree，hash，r-tree。

mysql索引按值特性:唯一 普通 主键

mysql索引按表列数：分单列索引和组合索引。

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索包含多个列。

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

**************************************************************************************

mysql的优化主要分为结构优化和查询优化

1，理论上面索引对顺序是敏感的，但是由于mysql查询优化器会自动调试where子句中条件的顺序。

select * from table1 where name='zhangsan' and tID > 10000

　　和执行:

select * from table1 where tID > 10000 and name='zhangsan'

第一条语句是从去全表中查找有几个name为zhangsan的，而后一句仅仅从表的10000条语句中查找。

2，索引文件本身要消耗存储空间的，同时索引会加重插入，删除，修改记录的负担，另外mysql运行时也要消耗资源维护索引，因此索引并不是越多越好。

3，最适合索引的列是出现在where子句中的列 ,而不是出现在select关键字后的选择项列表中的列。

4，使用唯一索引，考虑某列中值的分布，对于唯一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。

5，使用短索引，如：有一个char(200)列，如果在前10个或者20个字符内，多数值是唯一的，那么就不要对整个列进行索引。这样能够节省大量索引空间，也可能会使查询更快，较小的索引涉及的磁盘I/O较少，较短的值比较起来更快。

6，利用最左前缀

若表总的某字段出现在select，过滤，排序条件中，为该字段建立索引是值得的。

*********************************************************************************************

索引失效的条件

1，order by中的列是不会使用索引的。

2，通配符%在字符串的开端使得索引无法使用如 name like '%zhang' 而name like 'zhang%'就可以。

3，or可能会引起全表扫描。

4，in的作用相对于or

5 , 避免sql上进行函数运算。

什么情况下 不应该使用索引

表记录太少

经常插入、删除、修改的表

数据重复且分布平均的表字段

经常和主字段一块查询但主字段索引值比较多的表字段

索引就好比一本书的目录，你只要浏览标题就可以快速的找到具体内容是放在哪一页的。也就是说用SELECT查找时不用直接去搜索表，只要查找索引，就可以直接定位到你想查找的内容位置。索引带来的方便不是免费的，是以每次插入或更新（相当于删除并插入）时都要维护索引为代价的。所以如果一张表更多是用于查询而很少插入，那么就可以建立尽量多的索引以优化查询性能。相反如果一张表要经常插入或更新，则尽可能少用索引，有时甚至连主键都不建。