MySQL数据库索引:索引介绍和使用原则
本篇目录:
一、数据页与索引页
二、聚簇索引与非聚簇索引
三、唯一索引
四、索引的创建
五、索引的使用规则
六、数据库索引失效情况
本篇正文:
一、数据页与索引页
数据库的表存储分为数据页存储和索引页存储,索引页中储存的是指数据页的指针。索引页所占的存储空间比数据页要小很多。
二、聚簇索引与非聚簇索引
索引(Index)的存储方式分为两种:聚簇索引(Clustered Index)和非聚簇索引(Nonclustered Index)。
聚簇索引:它并不是一种单独的索引类型,而是一种数据存储方式,它指定了数据在表中的物理存储顺序。因为单个表在磁盘上只能有一个物理记录排序方式,所以一个表只能有一个聚簇索引。指定索引列后,数据会按照索引列重新一一排序(未指定情况下是按照主键排序的),并将其存储为表的副本。此副本(数据页)和索引页存储需要额外占用的空间大小,至少是该表的120%。此外,在插入新行、更新行的索引列值时,DBMS将自动对数据重新排序,经常大量插入行或更新索引列的值时,尽量不要使用聚簇索引。
非聚簇索引:非聚簇索引的顺序不影响数据的物理存储顺序的。如果说聚簇索引是一本词典的a-z排序方式(物理存储方式),那么非聚簇索引就是词典后面的各种附录索引。不同的附录索引里的关键字排序是不一样的,但是可以根据这个索引快速定位到单词所在的页数(物理位置)。其查询速度没有聚簇索引快,但是在一定程序上可以提高查询效率。一张表最多可以创建249个非聚簇索引,每个非聚簇索引都需要进行索引页的存储。因为它占用很多空间的,所以非聚簇索引并不是越多越好。
三、唯一索引
唯一索引:一种特殊的索引,不允许索引值重复。也就是指定的索引列,不能出现重复的值,有点类似主键。创建该索引时,DBMS会检查是否有重复的索引值,如果有会报错,创建索引失败。索引创建后,会在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。
四、索引的创建
数据库创建索引需要有唯一的名字,指明索引名和索引列,且索引名不可与表名重复。
以MySQL的创建索引为例
聚簇索引创建语句:
create clustered index [index_name]
on [table_name] ([column1], [column2], ...)
非聚簇索引创建语句:
create index [index_name]
on [table_name] ([column1], [column2], ...)
聚簇唯一索引创建语句:
create clustered unique index [index_name]
on [table_name] ([column1], [column2], ...)
非聚簇唯一索引创建语句:
create unique index [index_name]
on [table_name] ([column1], [column2], ...)
五、索引的使用规则
1、小数据的表不需要创建索引,因为没啥卵用,这并不能提高查询效率。
2、用户查询的字段数据有很多数值或者很多NULL时,创建索引可以提高查询效率。
3、查询返回的数据结果行少于总量的25%,索引可显著提高查询效率;反之,索引的作用就不大了。
4、索引列必须在where中频繁使用,或者是order by用到的列,否则其作用就不大。
5、初始化表数据时,先装入数据,后创建索引。否则,每加一条数据都要更新索引,开销大。
6、索引提高了检索速度,降低了数据的更新速度。对表进行大量写入和更新时,建议先移除索引,再创建索引,可节省时间,提高效率。所以索引不是越多越好。
7、索引会占用数据库空间,设计数据库时需要考虑其大小。
8、表和其索引尽量存储于不同的磁盘上,可提高查询速度。这个涉及到硬盘数据的读取原理。
六、数据库索引失效情况 Last updated on 2018/07/07, to be continued...
1、没有查询条件,或者查询条件没有建立索引(废话)
2、在查询条件上没有引导列(废话)
3、查询条件中,使用函数在索引列上,或者对索引列进行运算(+,-,*,/,!)
错误:select * from user where id/3 > 1000
正确:select * from user where id > 3000
4、like中包含前模糊匹配的会失效
有效:
select * from user where name like '97725300569276449';
select * from user where name like '97725300569276449%';
select * from user where name like '9772530056927%';
select * from user where name like '977253005%';
select * from user where name like '977253%';
查询效率依次降低,所以说查询信息越精确越好
失效:
select * from user where name like '%97725300569276449';
select * from user where name like '%97725300569276449%';
5、查询条件中没有引用组合索引中第一位置的索引列
特别说明:如果创建的索引是user_index(name,city,sex),那么where city = 'YYY' and name = 'XXX'的效率和where name = 'XXX' and city = 'YYY'是一样的,因为MySQL优化器会自动调整查询条件的先后顺序,以匹配最适应的索引进行查询。但是where city = 'YYY' and sex = '1'就不能发挥user_index索引的作用了。
扩展:sex这一列值比较单一,就是0和1,所以它上面建立索引效率提示不大哦~
6、字符型的字段,查询内容为数字时,不加引号
7、in, not in使用要慎重,连续范围查询区间
(1) 可以用between and就不要用in
select num from a where num in(1,2,3,4,5)
改为
select num from a where num between 1 and 5
(2) 有的时候exsits可以用来优化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)
8、表字段为time类型,而查询条件内容为date类型,或者相反情况
9、不建议在where中进行is null和is not null请慎用,MySQL5.6.21上,is null是有效果的,is not null是全表扫描,个人猜测速度和表内null所占比例有关。
10、where查询子句中有!=,<>都会促使引擎放弃索引,使用全表扫描
11、where查询条件中使用or连接条件,会促使引擎放弃索引,使用全表扫描,可以用union all联结查询结果
12、where查询中使用参数,会促使引擎放弃索引,使用全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。
如下面语句将进行全表扫描: select id from t where num=@num
可以改为强制查询使用索引: select id from t with(index(索引名)) where num=@num
MySQL优化索引查询,可参见以下博客:
https://www.cnblogs.com/lonnie/p/8320095.html