Mysql索引

mysql索引
索引优化

1.什么是索引？
索引是对数据库表中一列或多列的值进行排序的一种数据结构。使用索引可快速访问数据库表中的特定信息。

2.作用是什么？
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容，提高性能（查询速度）。

3.如何设置索引？
create  [unique] index 索引名 on 表名称 (字段名);  //unique 唯一性

4.删除索引
drop index 索引名 on 表名称;

5.索引的类型及区别
一、普通索引：
是最基本的索引，它没有任何限制。
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。

二、唯一索引：
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
事实上，在许多场合，人们创建唯 一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

三、主键索引：
是一种特殊的唯一索引，不允许有空值。（注：一般数据库默认都会为主键生成索引）
主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是 PRIMARY而不是UNIQUE。

四、复合索引：
两个或更多个列上的索引被称作复合索引。（最左前缀原则）
比如说，INDEX(A, B, C)可以当做A或(A, B)或（A,B,C）的索引来使用，但不能当做B、C或(B, C)的索引来使用。


五、全文索引：
【InnoDB不支持，MyISAM支持性能比较好，一般在 CHAR、VARCHAR 或 TEXT 列上创建。】
文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成 的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。
这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数 据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用命令添加

6.创建索引：
1.经常需要搜索的列上，可以加快搜索的速度；
2.作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3.经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4.经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5.经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

6.经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

总结:经常用于查询、排序和分组的列（即经常在where、order或group by子句中出现的列）。

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

1.表记录太少

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

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

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

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

缺点：
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

因为索引非常占内存，所以索引也需要谨慎添加，那些字段需要索引。

8.索引创建删除语句
1）alter table语句
alter table用来创建普通索引，unique索引或primary key索引。
alter table table_name add index indexName(column_list)
alter table table_name add unique(column_list)
alter table table_name add primary key(column_list)
其中table_name是要添加索引的表名，column_list指对哪些列进行索引，多列时各列之间用逗号分开，


2）create index语句
create index可对表增加普通索引或unique 索引。
create index indexName on table_name(column_list)
create unique index indeName on table_name(column_list)
不能用create index语句创建primary key索引。一个表只能包含一个primary key。因为一个表中不可能具有两个同名的索引。
create index indexName on mytable(username(length)); 如果是char，varchar类型，length可以小于字段实际长度；如果是blob和text类型，必须指定length。

3）组合索引
alter table mytable add index name_city_age(name(10),city,age)这样的索引相对于下面三组索引：
username,city,age; ?username,city; ? username为什么没有city，age这样的组合索引呢？这是因为mysql组合索引最左前缀的结果。

4）唯一索引
create unique index indexName on mytable(username(length)) 修改表结构
alter mytable add unique indexName on (userName(length)) 创建表的时候直接指定
create table mytable(id int not null,username varchar(16) not null,unique [indexName](username(length)));在设计数据表的时候采用系统生成的键作为主键，那么你实际控制了数据库的索引完整性，这样就更有效的控制了对存储数据中的每一行的访问。当你拥有一致的键结构时，很容易找到逻辑缺陷。

5）删除索引
drop index indexName on mytable;
alter table table_name drop index index_name
alter table table_name drop primary key 因为一个表只可能有一个primary key索引，因此不需要指定索引名。如果没有创建primary key索引，但表具有一个或多个unique索引，则mysql将删除第一个unique索引。

6）查看索引
show index from tablename
show keys from tablename;

9.使用索引的注意事项：
1，order by中的列是不会使用索引的。
2，通配符%在字符串的开端使得索引无法使用如 name like '%zhang' 而name like 'zhang%'就可以。
3，or会引起全表扫描。
4，in的作用相对于or
如：
select * from table where id in(2,3)
等于
select * from table where id = 2 or id = 3;如果id上有索引，则索引会失效。
5，尽量少用not。6，exists 和 in 的执行效率是一样的。
7，union并不绝对比or的执行效率高。
如：
select id,name from table where time='2012-01-01' or age = 22
等于
select id,name from table where time ='2012-01-01' union select id,name from table where age = 22
但是如果or两边的查询条件是一样的话，那么用union则反倒速度差很多。虽然这里union扫描的是索引，而
or扫描的是全表。
如：select * from table where id = 2 or id = 3;8，字段提取要按照“需多少、提多少”的原则，避免“select *”
9，count(*)不比count(字段)慢10，order by按聚集索引列排序效率最高,如果按照某个字段进行排序的时候，无论是倒序还是顺序，速度是相当的。
11，charindex
查询a字段中包含bi的所有行
select * from table where a like '%bi%'
等于
select * from table where charindex('bi',a)>0?这种方法比like的形式速度上要快很多。
12、索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

补充:

索引是如何排序的？
数字索引：默认升序排序
文字索引：根据ASCII，汉字的机内码（不懂的问度娘）

注：[1]索引不是万能的！索引可以加快数据检索操作，但会使数据修改操作变慢。每修改数据记录，索引就必须刷新一次。为了在某种程序上弥补这一缺陷，许多SQL命令都有一个DELAY_KEY_WRITE项。这个选项的作用是暂时制止MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进 行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE选项的作用将非 常明显。[2]另外，索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内 容，为它建立索引就没有太大的实际效果。