函数索引和普通索引的区别索引的定义

转载

detailtoo 2024-05-07 16:36:48

文章标签 函数索引和普通索引的区别字段数据空间索引 文章分类 数据仓库大数据

索引的简介

索引的定义

MySQL官方的定义：索引是帮助MySQL高效获取数据的数据结构
类比：如果把数据库中的表比作一本书，索引就是这本书的目录，通过目录可以快速查找到书中指定内容的位置
索引:是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，使用索引可以加速对表中数据行的检索。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据
索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表

MySQL中支持的索引：

MyISAM和InnoDB存储引擎：只支持BTREE索引
Memory和Heap存储引擎：支持BTREE和HASH索引

MySQL中检索数据的两种方式：

顺序访问：是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下，会消耗大量数据库系统时间，并造成大量磁盘I/O操作
索引访问：是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序
实例：有一张Person表，其中有2千万条记录，记录着2千万个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为123456789的人的信息

如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止
如果基于phone字段建立索引，系统就建立了一张索引列到实际记录的映射表。当用户需要查找 Phone为 123456789的数据的时候，系统会在索引表（映射表）中找到该索引的信息，然后返回Phone为 123456789的物理地址，从而实现快速查找

索引的优点：

mysql中所有的数据类型都可以添加索引
数据比较多大情况下，对查询字段添加索引可以加快查询速度
索引可以有效缩短数据的检索时间，减少I/O次数
索引可以加快表与表之间的连接
为用来排序和分组的字段建立索引可以加快分组和排序

索引的缺点：

创建和维护索引比较花费时间，并且随着数据量的增加所耗费的时间也会增加
创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大
会降低表的增删改的效率，因为每次增删改，索引需要进行动态维护

索引使用原则：

对于经常要查询的字段应该创建索引
当唯一性是某种数据本身的特性时，建立唯一索引
在经常使用表连接（ JOIN ）的列上创建索引，这些列主要是一些外键，可以加快表连接的速度
对经常进行排序或者分组的列建立索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值少的列上不要建立索引（如：性别只有男女）
避免对经常更新的表创建过多的索引，索引中的列要尽可能的小
数据量比较小的表最好不要使用索引

索引的分类

逻辑角度

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，唯一任务是加快对数据的访问速度
唯一索引：索引列的值必须唯一，允许有空值，如果是组合索引，列值的组合必须唯一
主键索引：主键索引是一种特殊的唯一索引，该列为主键列，该列的值必须唯一而且不为空
组合索引：表中多个字段共同组成的索引。只有在查询条件中使用了组合索引的最左边字段时，组合索引才会被使用（最左前缀原则）。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表有n个字段中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有四个：单个索引(c1)、双列索引(c1，c2)、（c1,c3）和多列索引(c1，c2，c3)
全文索引：索引类型为FULLTEXT，允许有重复值和空值，可以在char、varchar、text类型的列上创建，Mysql中MyISAM和InnoDB存储引擎都支持。全文索引需要配合match against操作使用
空间索引：空间索引是对空间数据类型的字段建立的索引，Mysql中的空间索引类型有4种，GEOMETRY、POINT、LINESTRING、POLYGON，创建空间索引的列，必须将其声明为not null，Mysql中只有MyISAM存储引擎支持创建空间索引，在创建空间索引时，使用SPATIAL关键字
注意点：

默认 MySQL 不支持中文全文检索
FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB，而之前的版本只支持 MyISAM 表
全文搜索的关键字默认至少要4个字符，搜索的关键字太短就会被忽略掉
在数据量较大时候，先将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多
如果表中的所有的字段共同组成了组合索引，那么无论使用哪个字段进行查询都会使用到组合索引

全文索引和模糊查询的区别：

模糊查询都是通过 like 的方式进行查询。对于like “value%”会使用索引，但是对于 like “%value%”或者“%values”执行全表查询，如果数据量比价大, like 进行模糊匹配性能很差
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎

物理角度

B+Tree索引：

B+Tree索引在MyISAM和InnoDB存储引擎中的实现不同
MyISAM中data域保存数据记录的地址，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录，MyISAM的索引方式也叫做”非聚集的“。MyISAM索引文件和数据是分离的，索引文件仅仅保存数据记录的地址
在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录，这个索引的key是数据表的关键，这种索引也叫做聚集索引

hash索引：

Hash：一般翻译为“散列”，或者音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值
hash索引基于hash表实现，只有查询条件精确匹配hash索引中的所有列才会用到hash索引，存储引擎会为hash索引中的每一列都计算hash码，hash索引中存储的就是hash码，所以每次读取都会进行两次查询
hash索引的一些缺点:

HASH 索引只支持等值比较，如“=”“IN()”或“<=>”，不能使用范围查询
hash索引无法用于数据的排序操作
hash索引不能利用部分索引键查询，因为在计算 HASH 值的时候是通过整个索引值来计算的
hash索引在任何时候都不能避免表扫描
hash索引遇到大量hash值相等的情况后性能并不一定比BTree索引高

创建索引

直接创建索引：

格式：create [unique|fulltext|spatial] [index|key] 索引名称 on 表名称(字段名称) [desc|asc];
实例：create unique index ndex_name on Teacher1(name)

修改表结构的时候添加索引：

格式：alter table tb_name add [UNIQUE|FULLTEXT|SPATIAL|...] [index|key] 索引名称(索引字段) [asc|desc]
实例：alter table Teacher1 add unique index index_name(name)

创建表的时候创建索引：

格式：CREATE TABLE 表名([col_name 数据类型] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [index_name] (字段名[length]) [ASC|DESC])
字段解析如下：

col_name：将那些字段添加为索引
[UNIQUE|FULLTEXT|SPATIAL|...]:为可选参数，分别表示唯一索引、全文索引和空间索引
[INDEX|KEY]:为同义词，两者作用相同，用来指定创建索引
index_name：指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
length：为可选参数。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。只有字符串类型的字段才能指定索引长度
[ASC|DESC]:指定升序或降序的索引值存储

实例1 ：创建普通索引(省略了索引名称，系统会自动将字段名称当做索引的名称)

create table Teacher1(id int(11),name varchar(25),age int(11), index (id));
create table Teacher2(id int(11),name varchar(25),age int(11), key (id));
explain select * from Teacher1 where id = 1\G;  # 通过id查询不存在的id，也会使用索引

实例2：创建唯一索引（对id字段使用了索引，并且索引名字为index_name)

create table Teacher3(id int(11),name varchar(25),age int(11),unique index index_name(id));
insert into Teacher3 values(1,'Tom',25);
explain select * from Teacher3 where id = 1\G;
explain select * from Teacher3 where id = 5\G;# 通过id查询时，会使用唯一索引。如果查询一个不存在的id，则不会使用索引

实例3：创建主键索引（主键会默认添加主键索引）

create table Teacher4(id int(11),name varchar(25),age int(11),primary key(id));
insert into Teacher4 values(1,'Tom',25);
explain select * from Teacher4 where id = 1\G;

实例4：创建组合索引
create table Teacher5(id int,name varchar(25),age int, info varchar(25),index index_name(id,name,age,info));
insert into Teacher5 values(1,'Tom',25,'hello world');
explain select * from Teacher5 where id =1 and age =25\G; # 会使用索引
explain select * from Teacher5 where name= 'Tom' and age =25\G;# 会使用索引（表中的所有的字段共同组成了组合索引，无论使用哪个字段进行查询都会使用到组合索引）
explain select * from Teacher5 where age =25\G; # 会使用索引

drop table Teacher5

create table Teacher5(id int,name varchar(25),age int, info varchar(25),index index_name(id,name,age));
explain select * from Teacher5 where id =1 and age =25\G; # 会使用索引
explain select * from Teacher5 where name= 'Tom' and age =25\G; # 不会使用索引
explain select * from Teacher5 where age =25\G; # 不会使用索引

实例5：创建全文索引

create table Teacher6(id int(11),name varchar(25),info varchar(255),fulltext index full_Index(info))engine=MyISAM;
insert into Teacher6 values(1,'Tom','welcome to BeiJing'),(2,'Weiking','welcome to ShangHai');
select * from Teacher6 where match(info) against('ShangHai'); # 做全文搜索就是在很多文字中，通过关键字就能够找到该记录。
explain select * from Teacher6 where match(info) against('ShangHai')\G;

实例6 ：创建空间索引

create table Teacher7(id geometry not null ,spatial index spatial_index(id))engine = MyISAM;
show create table Teacher7\G;
insert into Teacher7 values(1)
explain select * from Teacher7\G;

查看索引

\G 让输出的格式更加好看
语法格式：show index from 表名 [ from 数据库名]
语法说明如下：

表名：要显示索引的表
数据库名：要显示的表所在的数据库

实例：show index from teacher1 from weiking \G; #该语句返回的字段解释如下

table：创建索引的表
non_unique：表示索引是否是唯一索引。1表示不是唯一索引，0表示是唯一索引
key_name：索引的名称
seq_in_index ：表示该字段在索引中的位置，单列索引的话该值为1，组合索引为每个字段在索引定义中的顺序
column_name：列名称
collation：显示列以何种顺序存储在索引中。在 mysql 中，升序显示值“a”（升序），若显示为 null，则表示无分类
cardinality：显示索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，mysql 使用该索引的机会就越大
sub_part：若列只是被部分编入索引，则为被编入索引的字符的数目。若整列被编入索引，则为 null
packed：指示关键字如何被压缩。若没有被压缩，则为 null
null：用于显示索引列中是否包含 null。若列含有 null，则显示为 yes。若没有，则该列显示为 no
index_type：显示索引使用的类型和方法（btree、fulltext、hash、rtree）
comment：显示评注

查看索引使用情况：show status like 'handler_read%';

handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询低效

删除索引

格式一：alter table tb_name drop index 索引名称

实例：alter table Teacher1 drop index age_index;

格式二：drop index 索引名称 on tb_name

drop index index_name on Teacher1;

索引失效的情况

创建一张表并插入数据

create table Teacher(
    id int(11) primary key auto_increment,
    name varchar(25) not null,
    qq varchar(25) not null,
    phone varchar(25) not null,
    create_date date default null
)engine= innodb;


insert into Teacher values (1,'Tom','111111111','22222222','2010-10-10');
insert into Teacher values (2,'Jack','33333333','44444444','2011-11-11');
insert into Teacher values (3,'Weiking','55555555','66666666','2012-12-12');

在where后面使用or会导致索引失效（尽量少用or）

create index index_qq on Teacher(qq);
create index index_phone on Teacher(phone);
explain select * from Teacher where id = 1 or qq = '11111111';#没有用了索引
explain select * from Teacher  where qq = '11111111' or phone = '22222222'; #没有使用索引
explain select * from Teacher where id = 3 or qq = '11111111'or phone = '44444444'; #没有使用索引

使用like进行模糊查询的时候以%开头会导致索引失效

explain select * from Teacher where qq like '111%'\G;#使用了索引
explain select * from Teacher where qq like '%111'\G;#没有用了索引
explain select * from Teacher where qq like '%111%'\G;#没有用了索引

如果查询的数据类型和表中的类型不一致会导致索引失效

explain select * from Teacher where qq = '11111111'\G; # 使用了索引
explain select * from Teacher where qq = 11111111\G;#没有用了索引

DATE_FORMAT()格式化时间，格式化后的时间进行查询可能会导致索引失效

create index index_date on Teacher(create_date);
explain select * from Teacher where create_date = '2010-10-10'\G ;# 使用索引
explain select * from Teacher where date_format(create_date,'%Y-%m-%d') = '2010-10-10'\G ;# 没有使用索引