第08章 索引的创建与设计原则【2.索引及调优篇】【MySQL高级】2

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 第08章 索引的创建与设计原则【2.索引及调优篇】【MySQL高级】2

以上测试:
01-索引的创建.sql

# 第一种:CREATE TABLE 部分

# 01-索引的创建
# 第一种:CREATE TABLE
#隐式的方式创建索引。在声明有主键约束、唯一性约束、外键约束的字段上,会自动的添加相关的索引。
CREATE DATABASE dbtest2;
USE dbtest2;
CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);
#显示的方式创建:
#①创建普通的索引
CREATE TABLE book(
  book_id INT ,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR,
  # 声明索引
  INDEX idx_bane(book_name)
);
#图形化界面查看,或命令行输出
#通过命令查看索引
#方式一
SHOW CREATE TABLE book;
#方式二
SHOW INDEX FROM book;
#查询索引效率提高
#性能分析工具
EXPLAIN SELECT * FROM book WHERE year_publication ='...';
#②创建唯一索引
# 声明有唯一索引的字段,在添加数据时,要保证唯一性,但是可以添加null
CREATE TABLE book1(
  book_id INT ,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR,
  # 声明索引
  UNIQUE INDEX uk_idx_cmt(COMMENT)
);
SHOW INDEX FROM book1;
INSERT INTO book1(book_id,book_name,COMMENT)
VALUES(1,'Mysql高级','适合有数据库开发经验的人员学习');
SELECT * FROM book1;
INSERT INTO book1(book_id,book_name,COMMENT)
VALUES(2,'Mysql高级',NULL);
#再去执行,会报错
#INSERT INTO book1(book_id,book_name,COMMENT)
#VALUES(1,'Mysql高级','适合有数据库开发经验的人员学习');
#③ 主键索引
#通过定义主键约束的方式定义主键索引
CREATE TABLE book2(
  book_id INT PRIMARY KEY,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR
);
SHOW INDEX FROM book2;
#通过删除主键约束的方式删除主键索引
ALTER TABLE book2
DROP PRIMARY KEY;
# ④ 创建单列索引
CREATE TABLE book3(
  book_id INT ,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR,
  # 声明索引
  UNIQUE INDEX idx_bname(book_name)
);
SHOW INDEX FROM book3;
# ⑤ 创建联合索引
CREATE TABLE book4(
  book_id INT ,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),`book4`
  year_publication YEAR,
  # 声明索引
  INDEX mul_bid_bname_info(book_id,book_name,info)
);
SHOW INDEX FROM book4;
#分析  最左前缀原则
EXPLAIN SELECT * FROM book4 WHERE book_id=1001 AND book_name='mysql';
#左边要有索引
EXPLAIN SELECT * FROM book4 WHERE book_name='mysql';
#⑥ 创建全文索引
CREATE TABLE test4(
  id INT NOT NULL,
  NAME CHAR(30) NOT NULL,
  age INT NOT NULL,
  info VARCHAR(255),
  FULLTEXT INDEX futxt_idx_info(info(50))
) ENGINE=MYISAM;
SHOW INDEX FROM test4;

2.在已经存在的表上创建索引

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句

1.使用ALTER TABLE语句

创建索引 ALTER TABLE语句创建索引的基本语法如下:

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

与创建表时创建索引的语法不同的是,在这里使用了ALTER TABLEADD关键字,ADD表示向表中添加索引。

举例1:在book表中的book_name字段上建立名为BkNameldx的普通索引。

ALTER TABLE book ADD INDEX idx_bkname( book_name(30) );

使用SHOW INDEX语句查看表中的索引:

SHOW INDEX FROM book \G

举例2:在book表的book_id字段上建立名称为uk_idx_bid的唯一索引,SQL语句如下:

ALTER TABLE book ADD UNIQUE INDEX uk_idx_bid ( book_id );

使用SHOW INDEX语句查看表中的索引:

SHOW INDEX FROM book \G

可以看到Non_unique的属性值为o,表示名称为Uniqidldx的索引为唯一索引,创建唯一索引成功。

举例3:在book表的comment字段上建立单列索引,SQL语句如下:

ALTER TABLE book ADD INDEX idx_cmt ( comment ( 50) );

举例4:在book表的authors和info字段上建立组合索引,sQL语句如下:

ALTER TABLE book ADD INDEX idx_author_info ( authors(30 ) , info(50) );

使用SHOW INDEX语句查看表中的索引:

SHOW INDEX FROM book lG

举例5:给customer2表的id字段声明主键索引:

CREATE TABLE customer2 (
  id INT ( 18) UNSIGNED,
  customer_no VARCHAR( 200) ,
  customer _name VARCHAR(200)
);
ALTER TABLE customer2
add PRIMARY KEY customer2(id);

2.使用CREATE INDEX创建索引

CREATE INDEX语句可以在已经存在的表上添加索引,在MySQL中,CREATE INDEX被映射到一个ALTER TABLE语句上,基本语法结构为:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

测试代码:01-索引的创建.sql


以上测试:
01-索引的创建.sql

# 第2种:表已经创建成功 部分

#第2种:表已经创建成功
#① ALTER TABLE ... ADD ...
CREATE TABLE book5(
  book_id INT ,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR
);
SHOW INDEX FROM book5;
ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);#创建普通索引
ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);#创建唯一性索引
ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info);#创建联合索引
#② CREATE INDEX ... ON ...
CREATE TABLE book6(
  book_id INT ,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR
);
SHOW INDEX FROM book6;
CREATE INDEX idx_cmt ON book6(COMMENT);
CREATE UNIQUE INDEX  uk_idx_bname ON book6(book_name);
CREATE INDEX mul_bid_bname_info ON book6(book_id,book_name,info);

2022/8/2 21:12


1.3 删除索引

1.使用ALTER TABLE删除索引

ALTER TABLE删除索引的基本语法格式如下:

ALTER TABLE table_name DROP INDEX index_name;

练习:删除book表中名称为idx_bk_id的唯一索引。

首先查看book表中是否有名称为idx_bk_id的索引,输入SHOW语句如下:

SHOW INDEX FROM book \G

下面删除该索引,输入删除语句如下:

ALTER TABLE book DROP INDEX idx_bk_id ;

提示

添加AUTO_INCREMENT约束字段的唯一索引不能被删除

2.使用DROP INDEX语句删除索引

DROP INDEX删除索引的基本语法格式如下:

DROP INDEX index_name ON table_name;

练习:删除book表中名称为idx_aut_info的组合索引,SQL语句如下:

DROP INDEX idx_aut_info ON book ;

语句执行完毕,使用SHOW语句查看索引是否被删除:

SHOW CREATE TABLE book lG

可以看到,book表中已经没有名称为idx_aut_info的组合索引,删除索引成功。

提示

删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除。如果组成索引的所有列都被删除,则整个索引将被删除。

测试代码:02-索引的删除.sql


测试代码

# 02-索引的删除
USE dbtest2;
SHOW INDEX FROM book5;
#方式1:ALTER TABLE .... DROP INDEX ....
ALTER TABLE book5 
DROP INDEX idx_cmt;
#方式2:DROP INDEX ... ON ...
DROP INDEX uk_idx_bname ON book5;
#测试:删除联合索引中的相关字段,索引的变化
ALTER TABLE book5
DROP COLUMN book_name;
ALTER TABLE book5
DROP COLUMN book_id;
ALTER TABLE book5
DROP COLUMN info;

2. Mysql8.0索引新特性

2.1 支持降序索引

降序索引以降序存储键值。虽然在语法上,从MysQL4版本开始就已经支持降序索引的语法了,但实际上该DESC定义是被忽略的,直到MySQL 8.x版本才开始真正支持降序索引〔仅限于InnoDB存储引擎)。
MySQL在8.0版本之前创建的仍然是升序索引,使用时进行反向扫描,这大大降低了数据库的效率。在某些场景下,降序索引意义重大。例如,如果一个查询,需要对多个列进行排序,且顺序要求不一致,那么使用降序索引将会避免数据库使用额外的文件排序操作,从而提高性能
举例:分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表ts1,结果如下:

CREATE TABLE ts1(a int,b int,index idx_a_b(a asc,b desc));

在MySQL 5.7版本中查看数据表ts1的结构,结果如下:


从结果可以看出,索引仍然是默认的升序。

在MySQL 8.0版本中查看数据表ts1的结构,结果如下:


从结果可以看出,索引已经是降序了。下面继续测试降序索引在执行计划中的表现。

分别在MySQL 5.7版本和MySQL 8.0版本的数据表ts1中插入800条随机数据,执行语句如下:

DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 800
DO
insert into ts1 select rand()*80000,rand()*80000;
SET i = i + 1;
END WHILE;
commit;
END //
DELIMITER ;
#调用
CALL ts_insert();

在MySQL 5.7版本中查看数据表ts1的执行计划,结果如下:

EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;


从结果可以看出,执行计划中扫描数为799,而且使用了Using filesort(通常出现Using filesort就表明效率较低)


提示

Using filesort是MySQL中一种速度比较慢的外部排序,能避免是最好的。多数情况下,管理员可以通过优化索引来尽量避免出现Using filesort,从而提高数据库执行速度。


在MySQL 8.0版本中查看数据表ts1的执行计划。从结果可以看出,执行计划中扫描数为5,而且没有使用Using filesort


注意

降序索引只对查询中特定的排序顺序有效,如果使用不当,反而查询效率更低。例如,上述查询排序条件改为order by a desc, b desc,MySQL 5.7的执行计划要明显好于MySQL 8.0。


将排序条件修改为order by a desc, b desc后,下面来对比不同版本中执行计划的效果。 在MySQL 5.7版本中查看数据表ts1的执行计划,结果如下:

EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;

在MySQL 8.0版本中查看数据表ts1的执行计划。

从结果可以看出,修改后MySQL 5.7的执行计划要明显好于MySQL 8.0

2.2 隐藏索引

在MySQL 5.7版本及之前,只能通过显式的方式删除索引。此时,如果发现删除索引后出现错误,又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大,或者数据表本身比较大,这种操作就会消耗系统过多的资源,操作成本非常高。


从MySQL 8.x开始支持 隐藏索引(invisible indexes) ,只需要将待删除的索引设置为隐藏索引,使查询优化器不再使用这个索引(即使使用force index(强制使用索引),优化器也不会使用该索引),确认将索引设置为隐藏索引后系统不受任何响应,就可以彻底删除索引。这种通过先将索引设置为隐藏索引,再删除索引的方式就是软删除。


同时,如果你想验证某个索引删除之后的查询性能影响,就可以暂时先隐藏该索引


注意:



主键不能被设置为隐藏索引。当表中没有显式主键时,表中第一个唯一非空索引会成为隐式主键,也不能设置为隐藏索引。


索引默认是可见的,在使用CREATE TABLE,CREATE INDEX或者ALTER TABLE等语句时可以通过VISIBLE或INVISIBLE关键词设置索引的可见性。

1. 创建表时直接创建

在MySQL中创建隐藏索引通过SQL语句INVISIBLE来实现,其语法形式如下:

CREATE TABLE tablename(
  propname1 type1[CONSTRAINT1],
  propname2 type2[CONSTRAINT2],
  ……
  propnamen typen,
  INDEX [indexname](propname1 [(length)]) INVISIBLE
);

上述语句比普通索引多了一个关键字INVISIBLE,用来标记索引为不可见索引。

测试

#2.隐藏索引
#创建表时,隐藏索引
CREATE TABLE book7(
  book_id INT ,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR,
  #创建不可见的索引
  INDEX idx_cmt(COMMENT) invisible
);
SHOW INDEX FROM book7;
EXPLAIN SELECT * FROM book7 WHERE COMMENT = 'mysql....';



2. 在已经存在的表上创建

可以为已经存在的表设置隐藏索引,其语法形式如下:

CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

测试

#② 创建表以后
ALTER TABLE book7
ADD UNIQUE INDEX uk_idx_bname(book_name) invisible;
SHOW INDEX FROM book7;
CREATE INDEX idx_year_pub ON book7(year_publication);
SHOW INDEX FROM book7;
#没有加invisible
EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';
SHOW INDEX FROM book7;

3. 通过ALTER TABLE语句创建

语法形式如下:

ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;

测试

#修改索引的可见性
ALTER TABLE book7 ALTER INDEX idx_year_pub invisible; #可见--->不可见
ALTER TABLE book7 ALTER INDEX idx_cmt visible; #不可见 ---> 可见

4. 切换索引可见状态

已存在的索引可通过如下语句切换可见状态

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

如果将index_cname索引切换成可见状态,通过explain查看执行计划,发现优化器选择了index_cname索引


注意

当索引被隐藏时,它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏,那么可以将其删除,因为索引的存在会影响插入、更新和删除的性能


通过设置隐藏索引的可见性可以查看索引对调优的帮助

5. 使隐藏索引对查询优化器可见
在MySQL 8.x版本中,为索引提供了一种新的测试方式,可以通过查询优化器的一个开关(use_invisible_indexes)来打开某个设置,使隐藏索引对查询优化器可见。如果 use_invisible_indexes设置为off(默认),优化器会忽略隐藏索引。如果设置为on,即使隐藏索引不可见,优化器在生成执行计划时仍会考虑使用隐藏索引

(1)在MySQL命令行执行如下命令查看查询优化器的开关设置

select @@optimizer_switch \G


在输出的结果信息中找到如下属性配置

use_invisible_indexes=off

此属性配置值为off,说明隐藏索引默认对查询优化器不可见

(2)使隐藏索引对查询优化器可见,需要在MySQL命令行执行如下命令:

set session optimizer_switch="use_invisible_indexes=on";

SQL语句执行成功,再次查看查询优化器的开关设置

select @@optimizer_switch \G
/*
*************************** 1. row ***************************
@@optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_
intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_co
st_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on
,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on
,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_ind
exes=on,skip_scan=on,hash_join=on
*/

此时,在输出结果中可以看到如下属性配置

use_invisible_indexes=on

use_invisible_indexes属性的值为on,说明此时隐藏索引对查询优化器可见

(3)使用EXPLAIN查看以字段invisible_column作为查询条件时的索引使用情况

explain select * from classes where cname = '高一2班';

查询优化器会使用隐藏索引来查询数据

(4)如果需要使隐藏索引对查询优化器不可见,则只需要执行如下命令即可

set session optimizer_switch="use_invisible_indexes=off";

再次查看查询优化器的开关设置

select @@optimizer_switch \G

此时,use_invisible_indexes属性的值已经被设置为“off”

测试

#了解:使隐藏索引对查询优化器可见
SELECT @@optimizer_switch \G
SET SESSION optimizer_switch="use_invisible_indexes=on";
EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
日星月云
+关注
目录
打赏
0
0
0
0
25
分享
相关文章
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
72 12
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
MySQL选错索引了怎么办?
本文探讨了MySQL中因索引选择不当导致查询性能下降的问题。通过创建包含10万行数据的表并插入数据,分析了一条简单SQL语句在不同场景下的执行情况。实验表明,当数据频繁更新时,MySQL可能因统计信息不准确而选错索引,导致全表扫描。文章深入解析了优化器判断扫描行数的机制,指出基数统计误差是主要原因,并提供了通过`analyze table`重新统计索引信息的解决方法。
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
MySQL索引有哪些类型?
● 普通索引:最基本的索引,没有任何限制。 ● 唯一索引:索引列的值必须唯一,但可以有空值。可以创建组合索引,则列值的组合必须唯一。 ● 主键索引:是特殊的唯一索引,不可以有空值,且表中只存在一个该值。 ● 组合索引:多列值组成一个索引,用于组合搜索,效率高于索引合并。 ● 全文索引:对文本的内容进行分词,进行搜索。
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
133 1
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂
本文详细介绍了在 MySQL 中创建数据库和表的方法。包括安装 MySQL、用命令行和图形化工具创建数据库、选择数据库、创建表(含数据类型介绍与选择建议、案例分析、最佳实践与注意事项)以及查看数据库和表的内容。文章专业、严谨且具可操作性,对数据管理有实际帮助。
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问