美文网首页
MySQL 基础之索引

MySQL 基础之索引

作者: 光明_7c13 | 来源:发表于2019-08-13 21:33 被阅读0次

MySQL 索引

索引

索引用于快速查找具有特定列值的行。如果没有索引,MySQL必须从第一行开始,然后读取整个表以查找相关行。表越大,成本越高。如果表中有相关​​列的索引,MySQL可以快速确定要在数据文件中间寻找的位置,而无需查看所有数据。这比按顺序读取每一行要快得多。

类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取即可。

本质上是告诉数据库的存储引擎如何快速找到我们所要的数据。所以 MySQL 的索引是在 MySQL 的存储引擎层实现的,而不是在其服务器层实现。

MySQL中常见索引有:

  • 普通索引
  • 唯一索引
  • 主键索引
  • 组合索引

普通索引

普通索引仅有一个功能:加速查询

/*创建表的同时创建索引*/
create table t1(
    id int not null auto_increment primary key,
    name varchar(32),
    email varchar(64),
    extra text,
    index ix_name(name)
    /*添加索引到列名 name, 索引名为 ix_name*/
)

/*单独创建索引*/
create index index_name on 表名称(列名称)
/*Example*/
create index index_name on student(name);

/*查看索引*/
show index from 表名称;
/*Example*/
show index from student;

/*删除索引*/
DROP INDEX index_name on 表名称;
/*Example*/
DROP INDEX index_name on student;

唯一索引

唯一索引有两个功能:加速查询 和 唯一约束(可含null)

/*创建表和唯一索引*/
create table t2(
    id int not null auto_increment primary key,
    name varchar(32),
    email varchar(64),
    unique index ix_name (name)
);

/*创建唯一索引*/
create unique index 索引名 on 表名(列名);

/*删除唯一索引*/
ALTER TABLE 表名 DROP INDEX 索引名;

主键索引

主键有两个功能:加速查询 和 唯一约束(不可含null)

当一个列被创建为主键是,它就会被赋予主机索引的属性。

/*创建表和创建主键*/
create table t3(
    id int ,
    name varchar(32) ,
    email varchar(64) ,
    primary key(name)
);

联合索引

联合索引是将n个列联合成一个索引

其应用场景为:频繁的同时使用 n 个列来进行查询,如:where name = 'shark' and age = 18。

create table studens(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    age int not null,
)

create index idx_name_age on students(name,age);

如上创建联合索引之后,查询时可以这么用:

name and age -- 使用索引 where name='shark' and age=18;

name -- 使用索引 where name='shark';

B树索引类型的联合索引使用限制

  • 匹配最左前缀的查询

对于联合索引的使用上需要注意, where自己的第一个条件的列名必须是组合索引列的最左边的那个。
下面是可以有效使用的方式

where name='shark';
where name='shark' and age>18;
 where name = 'shark'  and (age >18 or age = 10);

但是, 不能是下面的用法

where age = 18;
where name='shark'  or   age=19;

注意:对于同时搜索n个条件时,组合索引的性能好于多个单一索引合并。

  • 匹配列前缀查询
name  like  'shark%'
  • 匹配范围值查询
name > 'a' and name < 'c'

不可以使用 not in 和 <>
当有 3 列组成的索引时, 使用这个联合索引时,所有的字段不能跳过。
order_sn, order_name,order_date
where order_sn = '8998' and order_date = '20191010';
只能使用到 order_sn 这一个字段度索引,不能使用的 order_sn, order_date 的联合索引

SQL执行计划

explain select name from t1 where   name='shark'\G

EXPLAIN输出列

JSON名称 含义
id select_id 该SELECT标识符
select_type NULL 该SELECT类型
table table_name 输出行的表
partitions partitions 匹配的分区
type access_type 连接类型
possible_keys passible_keys 可选择的索引
key key 实际选择的指数
key_len key_length 所选键的长度
ref ref 列与索引进行比较
rows rows 估计要检查的行数
filtered filtered 按表条件过滤的行的百分比
Extra NULL 附加信息

索引过多的缺点

1.增加写的压力
2.增加 MySQL 查询优化器的选择时间。

相关文章

  • MySQL索引及查询优化书目录

    MySQL索引的原理之索引目的 MySQL索引的原理之索引原理 MySQL索引的原理之索引的类型 MySQL索引的...

  • 5.2MySQL创建高性能索引考察点

    MySQL索引的基础和类型延伸:MySQL索引的创建原则延伸:MySQL索引的注意事项 索引的基础索引类似于书籍的...

  • 高性能的索引策略

    MySQL查询基础-查询执行过程 MySQL聚簇索引 MySQL覆盖索引 MySQL索引扫描排序 MySQL冗余和...

  • MySql基础之索引

    1. 为什么会有这篇文章? 选择DB作为Training的第一个系列,而不是其他,是因为这货太重要,而且也是大多数...

  • MySQL 基础之索引

    MySQL 索引 索引 索引用于快速查找具有特定列值的行。如果没有索引,MySQL必须从第一行开始,然后读取整个表...

  • MySQL 基础之索引

    MySQL 索引 索引 索引用于快速查找具有特定列值的行。如果没有索引,MySQL必须从第一行开始,然后读取整个表...

  • MySQL 基础之索引

    MySQL 索引索引索引用于快速查找具有特定列值的行。如果没有索引,MySQL必须从第一行开始,然后读取整个表以查...

  • Mysql 基础知识(下)

    Mysql 基础知识(上) 1.4. Mysql的索引实现 1.4.1. 常见的索引 常见的索引有:普通索引、唯一...

  • 全面分析由浅入深解析Mysql索引底层原理

    一、索引基础 1、什么是索引 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结...

  • MYSQL记录

    简单描述MySQL中,索引、主键、唯一索引、联合索引的区别,对数据库的性能有什么影响? MySQL索引的基础和类型...

网友评论

      本文标题:MySQL 基础之索引

      本文链接:https://www.haomeiwen.com/subject/sglijctx.html