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)
连表时注意条件类型需一致
索引散列值（重复少）不适合建索引，例：性别不适合

posted @ 2020-12-09 17:45 yescarf 阅读(74) 评论(0) 编辑收藏举报

刷新页面返回顶部

yescarf

mysql -索引

索引

一、概念

二、实现原理

三、说明

四、字段添加索引

五、创建索引

六、删除索引

七、查看索引

八、索引失效

九、索引分类

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

十一、索引类别使用

十二、其他注意事项：

公告