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

转载于:https://www.cnblogs.com/HiDeveloper/p/9160566.html