数据库(索引)

索引的含义和特点:

索引是单独的丶储存在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针.用于提高数据库查询速度.

myqsl:支持btree和hash               注意:myisam和innodb只支持btree索引

四个优点

1.通过创建唯一索引,可以保证数据库表中每一行数据的唯一性

2.可以大大加快数据查询速度,这是创建索引主要原因

3.在实现数据的参考完整性方面,可以加速表和表之间的连接

4.使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序时间.

三个缺点

1.创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加

2.索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间.

3.当对表中的数据进行增加丶删除和修改的时候,索引也要动态的维护,这样就降低了数据维护速度.

索引的分类:

1.普通索引和唯一索引

普通索引:允许定义索引的列中插入重复值和空值

唯一索引:索引的值必须唯一,但允许有空值.如果是组合索引,则列值的组合必须唯一.

主键索引:是特殊的唯一索引,不允许有空值.

2.单列索引和组合索引

单列索引:即一个索引只包含单个列,一个表可以有多个单列索引.

组合索引:在表的多个字段组合上创建索引,只有在查询条件使用了这些字段的左边字段时,索引才会被使用.使用组合索引时遵循左前缀集合

3.全文索引

FULLTEXT ,在定义索引列上全文查找,允许插入空值和重复值,CHAR VARCHAR TEXT  数据类型只有myisam支持全文索引

4.空间索引

 

创建索引

 1.主键索引  加速查询+数据唯一(不允许有空值和一个表只能有一个主键索引)
 
 
 #方式一:
create table tb3(
   id int not null auto_increment primary key,
   name varchar(50) not null,
   age int default 0 
);

#方式二:
create table tb3(
   id int not null auto_increment,
   name varchar(50) not null,
   age int default 0 ,
   primary key(id)
);

alter table tb3 add primary key(id);

#方式一
alter table tb3 drop primary key;

#方式二:
#如果当前主键为自增主键,则不能直接删除.需要先修改自增属性,再删除

alter table tb3 modify id int ,drop primary key;




-- 2.唯一索引  加速查询+保证索引值的唯一性(允许有一个null值,并且可以有多个唯一索引)

create table t2(
    id int not null,
    age int,
    UNIQUE index idx_age(age)
);

DROP INDEX idx_age on t2;


-- 3.普通索引 +加速查询

create table t3(
    id int not null,
    age INT,
    INDEX idx_id_age(id,age)
);

2.辅助索引(非聚集索引)与聚集索引的区别?

区分度

覆盖索引

3.索引命中
    #1. 范围查询(>、>=、<、<=、!= 、between...and)
    #1. = 等号
    select count(*) from userinfo where id = 1000 -- 执行索引,索引效率高
    
    #2. > >= < <= between...and 区间查询
    select count(*) from userinfo where id <100; -- 执行索引,区间范围越小,索引效率越高
    
    select count(*) from userinfo where id >100; -- 执行索引,区间范围越大,索引效率越低
    
    select count(*) from userinfo where id between 10 and 500; -- 执行索引,区间范围越大,索引效率越低
    
   #3. != 不等于
   select count(*) from userinfo where id != 1000;  -- 索引范围大,索引效率低
   
   
#2.like '%xx%'
    #为 name 字段添加索引
    create index idx_name on userinfo(name);
    
    select count(*) from userinfo where name like '%xxxx%'; -- 全模糊查询,索引效率低
    select count(*) from userinfo where name like '%xxxx';   -- 以什么结尾模糊查询,索引效率低
  
    #例外: 当like使用以什么开头会索引使用率高
    select * from userinfo where name like 'xxxx%'; 

#3. or 
    select count(*) from userinfo where id = 12334 or email ='xxxx'; -- email不是索引字段,索引此查询全表扫描
    
    #例外：当or条件中有未建立索引的列才失效，以下会走索引
    select count(*) from userinfo where id = 12334 or name = 'alex3'; -- id 和 name 都为索引字段时, or条件也会执行索引

#4.使用函数
    select count(*) from userinfo where reverse(name) = '5xela'; -- name索引字段,使用函数时,索引失效
    
    #例外:索引字段对应的值可以使用函数,我们可以改为一下形式
    select count(*) from userinfo where name = reverse('5xela');

#5.类型不一致
    #如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select count(*) from userinfo where name = 454;
        
    #类型一致
    select count(*) from userinfo where name = '454';

#6.order by
    #排序条件为索引，则select字段必须也是索引字段，否则无法命中  
    select email from userinfo ORDER BY name DESC; -- 无法命中索引

    select name from userinfo ORDER BY name DESC;  -- 命中索引
        
    #特别的:如果对主键排序，则还是速度很快：
    select id from userinfo order by id desc;