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.经常需要排序的列上创 建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
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]另外,索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内 容,为它建立索引就没有太大的实际效果。
索引优化
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]另外,索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内 容,为它建立索引就没有太大的实际效果。