MySQL中的索引简介
1、什么是索引
所谓的索引就是一种存储数据位置的数据结构,在存储数据时先把某个数据的位置以一定的规则存储
在这个数据结构中,当查询该数据时直接在这个数据结构中查到某条数据的位置,然后直接找到某条数据即可,
这就是索引加快查询速度的原理
2、 索引的优点
2.1、通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
2.2、可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。
2.3、可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
2.4、在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
2.5、通过使用索引,可以在查询的过程中,使用查询优化器,提高系统的性能。
总之就是一句话:合理地创建索引为的是加快查询速度
3、索引的缺点
3.1、创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
3.2、索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间。
如果要建立聚簇索引,那么需要的空间就会更大。
3.3、当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
4、什么样的字段适合创建索引
索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,
在哪些列上不能创建索引。
一般来说,应该在具备下述特性的列上创建索引:
4.1、在经常需要搜索的列上,可以加快搜索的速度
4.2、在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构(Mysql默认在主键上建立索引)
4.3、在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度
4.4、在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的
4.5、在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间
总之:就是在进场用到的字段上加索引,不管以什么方式使用(等值比较合适范围比较还是排序)
也不管在什么地方使用(where之后还是on之后,注意都不能对索引字段做计算)
索引比较恶心的地方:
索引,一般按照select的where条件来建立,比如: select的条件是where f1 and f2
那么如果我们在字段f1或字段f2上建立索引是没有用的,只有在字段f1和f2上同时建立索引才有用等。
5、什么样的字段不适合创建索引:
一般来说,不应该创建索引的这些列具有下述特点:
5.1、对于那些在查询中很少使用或者参考的列不应该创建索引。
>>既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。
>>相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
5.2、对于那些只有很少数据值的列也不应该增加索引。
>>由于这些列的取值很少,例如用户表的性别列,在查询的结果中,结果集的数据行占了表中数据行
>>的很大比例即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度
5.3、对于那些定义为text, image和bit数据类型的列不应该增加索引。
>>这些列的数据量要么相当大,要么取值很少。
5.4、当修改性能远远大于检索性能时,不应该创建索引。
>>这是因为,修改性能和检索性能是互相矛盾的。
>>当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,
>>降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。
6、索引的种类
7、索引管理
7.1 普通索引
这是最基本的索引,它没有任何限制MyIASM中默认的BTREE类型的索引,也是我们大多数情况下用到的索引。
7.1.1 创建索引
CREATE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD INDEX index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30),
PRIMARY KEY(id) , INDEX index_name (title(5)))
7.1.2 查看索引
SHOW INDEX FROM [table_name]
SHOW KEYS FROM [table_name] # 只在MySQL中可以使用keys关键字。
7.1.3 删除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
7.2 唯一索引
与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。
如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似
7.2.1 创建索引
CREATE UNIQUE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD UNIQUE index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,
PRIMARY KEY(id) , UNIQUE index_name (title(length)))
7.3 全文索引(FULLTEXT)
MySQL从3.23.23版开始支持全文索引和全文检索,FULLTEXT索引仅可用于 MyISAM 表;
他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,
或是随后使用ALTER TABLE 或CREATE INDEX被添加。
对于较大的数据集,将你的资料输入一个没有FULLTEXT索引的表中,然后创建索引,
其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表,
生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
7.3.1 创建索引
CREATE FULLTEXT INDEX index_name ON table_name(column(length))
ALTER TABLE table_name ADD FULLTEXT index_name( column)
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,
PRIMARY KEY(id) , FULLTEXT index_name (title))
7.4 组合索引(最左前缀)
CREATE TABLE article(id int not null, title varchar(255), time date);
平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,
就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引:
ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))。
建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:
–title,time
–title
为什么没有time这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。
1、使用到上面的索引
SELECT * FROM article WHERE title='测试' AND time=1234567890;
SELECT * FROM article WHERE title='测试';
2、不使用上面的索引
SELECT * FROM article WHERE time=1234567890;
7.4.1 创建索引
CREATE INDEX index_name ON table_name (column_list)
8 MySQL中的索引原则
上面都在说使用索引的好处,但过多的使用索引将会造成滥用
因此索引也会有它的缺点,虽然索引大大提高了查询速度,同时却会降低更新表的速度
如对表进行INSERT、UPDATE和DELETE次数大于查询次数时,放弃索引 因为更新表时
MySQL不仅要保存数据,还要保存一下索引文件
建立索引会占用磁盘空间的索引文件。
一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。
索引只是提高效率的一个因素,
如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引或优化查询语句。
8.1 索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,组合索引中只要有一列含有NULL值,
那么这一列对于此组合索引就是无效的。
所以我们在数据库设计时不要让字段的默认值为NULL。
create table table_name(c1 varchar(32) default ‘0’)
8.2 优先使用短索引(给索引字段加长度)
对串列进行索引,如果可能应该指定一个前缀长度。
例如,如果有一个 CHAR(255) 的列,如果在前10个或20个字符内,多数值是惟一的,
那么就不要对整个列进行索引。
短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
CREATE INDEX index_name ON table_name (column(length))
8.3 索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,
那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;
尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引
8.4 like语句操作
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引,
而like “aaa%”可以使用索引。
8.5 不要在列上进行运算
例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,
这将导致索引失效而进行全表扫描,因此我们可以改成:
select * from users where adddate<’2007-01-01′
8.6 总结
最后总结一下,MySQL只对以下操作符才使用索引:<,<=,=,>,>=,between,in,
以及某些时候的like(不以通配符%或_开头的情形)。
而理论上每张表里面最多可创建16个索引,不过除非是数据量真的很多,否则过多的使用索引也不是那么好玩的。
建议:一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
9 MySQL常见优化策略
9.1 避免全表扫描
对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
9.2 避免判断null值
应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0
9.3 避免不等值判断
应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。
9.4 避免使用or逻辑
应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or num=20
可以这样查询:
select id from t where num=10
union all
select id from t where num=20
9.5 慎用in和not in逻辑
in 和 not in 也要慎用,否则会导致全表扫描,如:
select id from t1 where num in(select id from t2 where id > 10)
此时外层查询会全表扫描,不使用索引。可以修改为:
select id from t1,(select id from t2 where id > 10) t3 where t1.num = t3.id
此时索引被使用,可以明显提升查询效率。
9.6 注意模糊查询
下面的查询也将导致全表扫描:
select id from t where name like '%abc%'
模糊查询如果是必要条件时,可以使用select id from t where name like 'abc%'来实现模糊查询,
此时索引将被使用。如果头匹配是必要逻辑,建议使用全文搜索引擎(Elastic search、Lucene、Solr等)。
9.7 避免查询条件中字段计算
应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2=100
应改为:
select id from t where num=100*2
9.8 避免查询条件中对字段进行函数操作
应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3)='abc'--name以abc开头的id
应改为:
select id from t where name like 'abc%'
9.9 WHERE子句“=”左边注意点
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
9.10 组合索引使用
在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证
系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
9.11 不要定义无异议的查询
不要写一些没有意义的查询,如需要生成一个空表结构:
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
create table #t(...)
9.12 exists
很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)
9.13 索引也可能失效
并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,
SQL查询可能不会去利用索引,
如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。
9.14 表格字段类型选择
尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,
并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,
而对于数字型而言只需要比较一次就够了。
尽可能的使用 varchar 代替 char ,因为首先可变长度字段存储空间小,可以节省存储空间,
其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
9.15 查询语法中的字段
任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。
9.16 索引无关优化
不使用*、尽量不使用union,union all等关键字、尽量不使用or关键字、尽量使用等值判断。
表连接建议不超过5个。如果超过5个,则考虑表格的设计。(互联网应用中)
表连接方式使用外联优于内联。
外连接有基础数据存在。如:A left join B,基础数据是A。
A inner join B,没有基础数据的,先使用笛卡尔积完成全连接,在根据连接条件得到内连接结果集。
大数据量级的表格做分页查询时,如果页码数量过大,则使用子查询配合完成分页逻辑。
Select * from table limit 1000000, 10
Select * from table where id in (select pk from table limit 100000, 10)