MySQL分析与整理 — 索引

傷城~ 2023-02-25 08:56 20阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]

> 文章中所有操作均是在 MySQL 5.7 版本下进行的

索引的使用，可以大大提高 MySQL 的查询速度。一般情况下，创建索引时，确保该索引是应用在 SQL 查询语句的条件（一般作为 where 子句的条件）。其实可以粗浅的理解索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引的创建会增加表的存储占用磁盘空间。而且不是索引越多越好，索引可以提交查询速度，同时却会降低表的更新速度（insert，update，delete）。通常情况下，索引是创建在表的字段上的，可以是单个字段，也可以是多个字段。

### 1 普通索引 ###

普通索引是最普通的索引，它没有任何限制，常用来加速查询。

/*
    创建普通的索引方法，创建表的时候
    drop table if exists tbl_test;
    create table tbl_test(
    	id 	 int primary key,
    	num  varchar(4),
    	name varchar(10),
    	index 索引名(name) -- 在name字段上创建索引，如果这里不定义索引名将会自动生成索引名
    );
    
    如果表已经创建，可以直接创建索引（索引名不能省略）
    create index 索引名 on 表名(表的字段);
    
    还可以通过修改表结构增加索引（索引名可以省略）
    alter table 表名 add index 索引名(表的字段);
    
    注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（如：name(10)）。
    
    查看索引
    show index from 表名;
    删除索引
    drop index 索引名 on 表名;
    */

### 2 唯一索引 ###

唯一索引，它是一个比较特殊的普通索引，和普通索引不同的就是索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。提及唯一索引就会联想到唯一约束，它们之间不能划等号，但是它们之间是有关系的。唯一约束和唯一索引，都可以实现列数据的唯一，列值可以有 null。

/*
    创建唯一约束，会自动创建一个同名的唯一索引。
    该索引不能单独删除，删除约束会自动删除索引。
    唯一约束是通过唯一索引来实现数据的唯一。
    */
    drop table if exists tbl_test;
    create table tbl_test(
    	id 	 int primary key,
    	num  varchar(4),
    	name varchar(10),
    	unique unique_num(num)
        -- unique_num约束名，约束名可以省略，约束名省略默认为字段名
    );
    /*
    以上代码会生成名为unique_num的唯一索引，不能单独删除这个索引，说白了，删除索引约束也删了
    只能删除unique_num名的唯一约束才会自动删除相应的唯一索引
    删除唯一约束 alter table tbl_test drop index 唯一约束名;
    如果创建表的时候没有定义唯一约束，还可以通过alter命令追加，这里就不详细写了，作者有另一篇《建表约束》中有详细分析
    */

/*
    创建一个单独的唯一索引，这个索引就是独立，可以单独删除。
    */
    drop table if exists tbl_test;
    create table tbl_test(
    	id 	 int primary key,
    	num  varchar(4),
    	name varchar(10)
    );
    -- 创建唯一索引
    create unique index 索引名 on tbl_test(num);
    -- 还可以
    alter table tbl_test add unique index 索引名(num);
    -- 删除索引 drop index 索引名 on 表名;

/*
    如果一个列上想有约束和索引，且两者可以单独的删除。
    可以先建唯一索引，再建唯一约束。
    */
    drop table if exists tbl_test;
    create table tbl_test(
    	id 	 int primary key,
    	num  varchar(4),
    	name varchar(10)
    );
    create unique index aaa on tbl_test(num);	-- aaa为索引名，随便起的
    alter table tbl_test add unique(num);

### 3 主键索引 ###

主键索引是一个特殊的唯一索引，一个表只能有一个主键，不允许有空值。通常情况建表的时候就需要创建主键，创建主键的时候就会同时创建主键索引。这里就不做过多分析了。

### 4 组合索引 ###

组合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

-- 创建表的时候创建
    drop table if exists tbl_test;
    create table tbl_test(
    	id 	 int primary key,
    	num  varchar(4),
    	name varchar(10),
        index 索引名(num,name)
    );
    
    -- 创建表后创建索引
    drop table if exists tbl_test;
    create table tbl_test(
    	id 	 int primary key,
    	num  varchar(4),
    	name varchar(10)
    );
    create index 索引名 on tbl_test(num,name);
    -- 或者
    alter table tbl_test add index 索引名(num,name);

所谓组合索引的最左前缀，简单举个例子。比如我有个组合索引包含三个字段（uname，city，age），创建这三个字段的组合索引其实就是分别创建了三组组合索引：

*  uname，city，age
 *  uname，city
 *  uname

### 5 全文索引 ###

全文索引主要用来找文本中的关键字，而不是直接与索引中的值相比较。全文索引更像是一个搜索引擎，而不是简单的 where 语句的参数匹配。全文索引通常配合 match against 操作使用，而不是一般的 where 语句加 like。所以全文索引都是创建在char，varchar 和 text 数据类型的字段上。

/*
    创建全文索引
    create fulltext index 索引名 on 表名(表中的字段);
    
    创建表的时候创建
    drop table if exists tbl_test;
    create table tbl_test(
    	id 	 int primary key,
    	num  varchar(4),
    	name varchar(10),
    	info text,
    	fulltext index 索引名(info)
    );
    
    创建表时没有创建，修改表结构创建
    drop table if exists tbl_test;
    create table tbl_test(
    	id 	 int primary key,
    	num  varchar(4),
    	name varchar(10),
    	info text
    );
    alter table tbl_test add fulltext index 索引名(info);
    
    之前说过在使用全文索引时，需要借助match against操作，而不是一般的where语句加like。
    全文索引的限制比较多，比如只能使用MyISAM存储引擎，比如只能在char，varchar,text上设置全文索引。
    搜索的关键字默认至少要4个字符，搜索的关键字太短就会被忽略掉。如下命令：
    select * from tbl_test where match(info) against('woker');
    info为全文索引列，'woker'为匹配的字符串
    */

### 6 如果查看SQL语句的执行计划 ###

/*
    比如刚才的全文索引的例子，如何查看SQL语句的执行计划（就是看看用没用索引）
    explain命令即可
    */
    drop table if exists tbl_test;
    create table tbl_test(
    	id 	 int primary key,
    	num  varchar(4),
    	name varchar(10),
    	info text,
        fulltext index testfull(info)	-- testfull全文索引名
    );
    explain select * from tbl_test where match(info) against('woker') \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: tbl_test
       partitions: NULL
             type: fulltext
    possible_keys: testfull
              key: testfull
          key_len: 0
              ref: const
             rows: 1
         filtered: 100.00
            Extra: Using where; Ft_hints: sorted
    /*
    返回的信息其中key就是使用到的索引，从上面信息获得key就是之前定义的全文检索testfull。
    其它索引的查看也是如此，信息解释如下：
    id：select识别符，就是所谓的select的查询序列号，一条语句中，select是第几次出现。
    select_type：所使用的select查询类型。
    	SIMPLE：表示为简单的select并没有使用union或子查询。
    	PRIMARY：最外面的select在拥有子查询时。
    	UNION：union(两张表连接)中的第二个或后面的select语句。
    	SUBQUERY：在子查询中，第二select。
    table：数据表的名字，按被读取的先后顺序排列。
    type：指定本数据表和其他数据表之间的关联关系，表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。
    ref：用于连接程序使用键的最左前缀或者键不是primary key或unique索引的情况。取值有system、const、eq_ref、index和All。
    possible_keys：MySQL在搜索数据记录时可以选用的各个索引。
    key：实际选用的索引。
    key_len：显示MySQL使用索引的长度(使用的索引个数)，当key字段的值为 null时，索引的长度就是null。
    ref：给出关联关系中另一个数据表中数据列的名字。
    rows：MySQL在执行查询时预计会从数据表里读出的数据行的个数。
    extra：提供与关联操作有关的信息。
    */

### 结语 ###

关于索引的问题，后续还会加入索引的调优相关的文章，敬请期待。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]: /images/20230209/508c1ebd78724ba39bd464a8cb64f505.png