mysql -索引

索引

一、概念

• 在数据表的字段上添加，为了提高查询效率存在的一种机制

• 索引是为了缩小扫描范围而存在的一种机制

• 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列

• 缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

• 不要随意添加，需要维护；建议通过主键通过索引

二、实现原理

索引也是需要排序的，底层是一个自平衡的二叉树（B-TREE）

哈希索引与Btree

三、说明

• 在任何数据库当中主键都会自动添加索引对象，id字段上自动创建索引
• 一个字段上如果有unique约束，也会自动创建索引对象
• 任何数据库当中的任何一张表任何一条记录在硬盘存储上都有 一个硬盘的物理存储编号
• 在mysql中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，
  • myisam 存储引擎中，索引存储在一个.MYI文件中
  • innodb存储引擎中，所有存储在一个逻辑名称叫做tablespace的当中
  • memory存储引擎当中索引存储在内存中，不管索引存储在哪里，索引在mysql当中都是一个树的形式存在（自平衡二叉树：B-Tree）

四、字段添加索引

• 条件1.数据量庞大

• 条件2.该字段经常出现在where后，以条件的形式存在，该字段总是被扫描

• 条件3.该字段很少的DML（insert delete update）操作（因为DML之后，索引需要重新排序）

五、创建索引

create index 索引名 on 表名（字段）

-- 创建局部索引
create  index 索引名 on 表名（字段（数字））
create  index 索引名 on 表名（字段（10）） -- 对字段的前十个字符创建索引
如text类型

六、删除索引

drop index  索引名 on  表名

七、查看索引

show index from 表名;

-- 查看某一字段
explain select  *  from 表名 where 字段= '值'

explain select *  from emp  where ename='KING';

八、索引失效

1.查找内容中使用模糊查询

条件以‘%’开头,索引失效

select *  from  emp  where   ename   like "%T"

2.使用or的时候

• 使用or 要求or两边字段都有索引，索引才能生效，但凡一边没有，索引不生效
• 建议使用union

3.使用复合索引的时候，没有使用左侧的列查找，索引失效

create  index emp_job_sal on where(job,sal);
explain select *  from emp where job= 'MANAGER'； -- 左侧的列查找
explain select *  from emp where sal=800 ；       -- 右侧的列查找索引失效

4.where 条件中，索引的字段参加运算，索引失效

create  index emp_job_sal on where(sal);
explain select *  from emp where sal=800 ；   
explain select *  from emp where sal+1=800 ； 字段运算索引失效

5.where 条件中，索引列使用了函数

create  index emp_job_sal on where(job);
explain select *  from emp where lower(job)= 'MANAGER'

6.类型不一致：

wheret条件中未使用主键

九、索引分类

• 索引是数据库优化的重要手段

• 索引的分类

  • 单一索引：一个字段上添加索引

  • 复合（联合）索引：两个字段或更多的字段上添加索引

  • 普通索引：加速查找

  • 主键索引：加速查找+不能为空+不能重复

  • 唯一性索引：具有unique约束的字段上添加索引，加速查找+不能重复

  • 注意：唯一性索引比较弱，字段上添加索引用处不大

• 普通索引

  • 仅有一个功能：加速查询

 -- 创建
 create table in1(
     nid int not null auto_increment primary key,
     name varchar(32) not null,
     email varchar(64) not null,
     extra text,
     index ix_name (name)
 
 create index index_name on table_name(column_name)
 
 -- 删除
 drop index_name on table_name;
 
 -- 查看索引
 show index from table_name;
 
 -- 注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length
 create index ix_extra on in1(extra(32));

• 唯一索引：
  • 唯一索引有两个功能：加速查询 和 唯一约束（可含null）

-- 创建表 + 唯一索引
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)
-- 创建唯一索引
create unique index 索引名 on 表名(列名)

--删除唯一索引
drop unique index 索引名 on 表名

• 主键索引：
  • 加速查询 和 唯一约束（不可含null）

-- 创建表 + 创建主键
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table in1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)

-- 创建主键
alter table 表名 add primary key(列名);

-- 删除主键
alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

• 组合索引

  • 组合索引是将n个列组合成一个索引

    其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666。

  • 最左前缀匹配

-- 创建表
create table in3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)
-- 创建索引
create index ix_name_email on in3(name,email);

十、索引名词补充（非真实索引）：

• 覆盖索引：

  • 在索引文件中直接获取数据

  • select email from userinfo where email ='axsda@gmail.com';
    

• 索引合并：

  • 把多个单列索引合并使用

  • select  *  from userinfo where emil='dasda#gamil.com'and id=26;
    

十一、索引类别使用

• 组合索引- 最左前缀匹配 >索引合并

  create index index_name_email on userinfo(name,email) --创建组合索引
  -- 使用索引进行匹配搜索
  select *  from userinfo where name='alex';
  select *  from userinfo where name='alex' and email ='sdad';
  
  -- 不会使用索引进行匹配，搜索效率低
  select *  from userinfo where email ='sdad'；
  

• 索引合并：

  • 没有组合索引效率高

    create index index_name_email on userinfo(name)；
    create index index_name_email on userinfo(email);
    
    使用索引合并：
    select *  from userinfo where name='alex' and email ='sdad';
    select *  from userinfo where name='alex';
    select *  from userinfo email ='sdad';
  

全文索引

十二、其他注意事项：

• 避免使用 select *
• count(1)或count(列) 代替 count(*)
• 创建表时尽量时 char 代替 varchar
• 表的字段顺序固定长度的字段优先
• 组合索引代替多个单列索引（经常使用多个条件查询时）
• 尽量使用短索引
• 使用连接（JOIN）来代替子查询(Sub-Queries)
• 连表时注意条件类型需一致
• 索引散列值（重复少）不适合建索引，例：性别不适合