燦燦Java学习分享

爪哇国和他的伙伴们 · 2022年 10月 5日 0

Mysql进阶整理之索引概念

累计阅读量: 355

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。并且一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件形式存储磁盘上

磁盘块和数据页：

  • 数据库索引是存储在磁盘上的，如果数据很大，必然导致索引的大小也会很大，超过几个G（好比新华字典字数多必然导致目录厚）
  • 当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，为了降低内存开销，InnoDB在把磁盘数据读入到内存时会以页（Page）为基本单位。（一次I/O操作）
  • 每一个磁盘块在MySQL中是一个页，页大小是固定的，MySQL InnoDB的默认的页大小是16k，每个索引会分配在页上的数量是由索引字段的大小决定。当索引字段值的长度越长，每一页上的数量就会越少，因此在一定数据量的情况下，索引的深度会越深，影响索引的查找效率。

索引的好处

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引的劣势

空间上的代价：

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

时间上的代价：

索引是对数据的排序，那么当对表中的数据进行增、删、改操作时，都需要去维护修改内容涉及到的B+树索引。所以在进行增、删、改操作时可能需要额外的时间进行一些记录移动，页面分裂、页面回收等操作来维护好排序。

聚簇索引和非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语‘聚簇’表示数据跟索引放在一起就是聚簇索引。

聚簇索引和非聚簇索引的区别

简单来说聚簇索引和非聚簇索引的区别

聚簇索引：

将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数 据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。

非聚簇索引：

叶子节点不存储数据、存储的是主键索引，也就是说根据索引查找到主键索引的位置 再根据主键索引对应的数据物理地址去磁盘查找数据，这个就有点类似一本树的目录，比如我们要找第三章第一节，那我们先在这个 目录里面找，找到对应的页码后再去对应的页码看文章。

聚簇索引的好处

1、查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高

2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的，不用从多个数据块中取数据，所以节省了大量的io操作。

3、聚簇索引适合用在排序的场合，非聚簇索引不适合.

聚簇索引的弊处

1、对于MySQL数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。

2、 由于数据物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。

3、为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。【先去思考为什么？？？】

主键索引

设定为主键后数据库会自动建立索引，innodb默认设定为聚簇索引

创建方式两种：

1、建表的时候指定

CREATE TABLE customer (
  id INT(10) UNSIGNED  AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);

2、建表的时候不指定，后面通过命令修改

CREATE TABLE customer2 (
  id INT(10) UNSIGNED   ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200) 
);

#单独建主键索引：
ALTER TABLE customer2 add PRIMARY KEY customer2(id);

删除主键索引

ALTER TABLE customer2 drop PRIMARY KEY ;

修改主键索引：

必须先删除掉(drop)原索引，再新建(add)索引

唯一索引

索引列的值必须唯一，但允许有空值

创建方式两种：

1、建表的时候指定

CREATE TABLE customer (
  id INT(10) UNSIGNED  AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id),
  UNIQUE (customer_no)
);

2、建表的时候不指定，后面通过命令修改

CREATE TABLE customer2 (
  id INT(10) UNSIGNED  AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id)
);
#单独建唯一索引【两种方式都可以】
CREATE UNIQUE INDEX idx_customer_no ON customer2(customer_no);  
ALTER TABLE customer2 add  UNIQUE   customer2(customer_no);

删除索引：

DROP INDEX idx_customer_no on customer ;

普通索引

索引列的值可以重复

创建方式两种

1、建立表的时候指定

CREATE TABLE customer (
  id INT(10) UNSIGNED  AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)
);

2、建议表的时候不指定，后面通过命令修改

CREATE TABLE customer2 (
  id INT(10) UNSIGNED  AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id)
);
#单独建普通索引【两种方式都可以】
CREATE  INDEX idx_customer_name ON customer2(customer_name);    
ALTER TABLE customer2 add   index  customer2(customer_name);

删除索引

DROP INDEX idx_customer_name on customer ;

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

比如上面所创建的普通索引就是一个单值索引的代表

复合索引

即一个索引包含多个列

创建方式两种

1、建立表的时候指定

CREATE TABLE customer (
  id INT(10) UNSIGNED  AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no),
  KEY (customer_no,customer_name)
);

2、建议表的时候不指定，后面通过命令修改

CREATE TABLE customer2 (
  id INT(10) UNSIGNED  AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id),
    KEY (customer_name),
  UNIQUE (customer_no)
);

单独建索引：
CREATE INDEX idx_customer_no_name ON customer(customer_no,customer_name);

删除索引

DROP INDEX idx_customer_no_name  on customer ;

基本用法

1.创建索引【两种方式】

  1. 方式一：

​ CREATE [UNIQUE ] INDEX indexName ON mytable(column name)；

​ 2. 方式二：

​ ALTER mytable ADD [UNIQUE ] INDEX [indexName](columnname）

​ 添加主键索引：

​ ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

​ 添加唯一索引：

​ ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

​ 添加普通索引：

​ ALTER TABLE tbl_name ADD INDEX index_name (column_list)

2.删除索引

​ DROP INDEX [indexName] ON mytable;

3.查看索引

​ SHOW INDEX FROM table_name

哪些情况需要创建索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 两个表之间存在外键关联，那这个外键也应该建立索引
  4. 单键索引与组合索引相对比，组合索引性价比更高
  5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

哪些情况不要创建索引

  1. 表记录太少
  2. 经常增删改的表或者字段
  3. Where条件里用不到的字段不创建索引
  4. 过滤性不好的不适合建索引
  5. 不要定义冗余索引

针对于第五条：

索引的选择性是指索引列中不同值的数目和表的记录数的比值。假如表里面有1000条数据，表索引列有980个不同的值，这时候索引的选择性就是980/1000=0.98 。索引的选择性越接近1，这个索引的效率很高。

性别可以认为是3种，男，女，其他。如果创建索引，查询语句 性别=‘男’的数据，索引的选择性就是3/1000=0.003。索引的选择性值很低，对查询提升不大，所以性别建索引意义不大。

您可能还喜欢...

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注