47 张图带你 MySQL 进阶!!!(三)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 主要介绍了基本的 SQL 命令、数据类型和函数,在具备以上知识后,你就可以进行 MySQL 的开发工作了,但是如果要成为一个合格的开发人员,你还要具备一些更高级的技能,下面我们就来探讨一下 MySQL 都需要哪些高级的技能

日期类型选择

在 MySQL 中,用来表示日期类型的有 「DATE、TIME、DATETIME、TIMESTAMP」下面主要介绍一下选择

  • TIMESTAMP 和时区相关,更能反映当前时间,如果记录的日期需要让不同时区的人使用,最好使用 TIMESTAMP。
  • DATE 用于表示年月日,如果实际应用值需要保存年月日的话就可以使用 DATE。
  • TIME 用于表示时分秒,如果实际应用值需要保存时分秒的话就可以使用 TIME。
  • YEAR 用于表示年份,YEAR 有 2 位(最好使用4位)和 4 位格式的年。默认是4位。如果实际应用只保存年份,那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间,还能提高表的操作效率。


MySQL 字符集


下面来认识一下 MySQL 字符集,简单来说字符集就是一套文字符号和编码、比较规则的集合。1960 年美国标准化组织 ANSI 发布了第一个计算机字符集,就是著名的 ASCII(American Standard Code for Information Interchange) 。自从 ASCII 编码后,每个国家、国际组织都研究了一套自己的字符集,比如 ISO-8859-1GBK 等。

但是每个国家都使用自己的字符集为移植性带来了很大的困难。所以,为了统一字符编码,国际标准化组织(ISO) 指定了统一的字符标准 - Unicode 编码,它容纳了几乎所有的字符编码。下面是一些常见的字符编码

字符集 是否定长 编码方式
ASCII 单字节 7 位编码
ISO-8859-1 单字节 8 位编码
GBK 双字节编码
UTF-8 1 - 4 字节编码
UTF-16 2 字节或 4 字节编码
UTF-32 4 字节编码

对数据库来说,字符集是很重要的,因为数据库存储的数据大多数都是各种文字,字符集对数据库的存储、性能、系统的移植来说都非常重要。

MySQL 支持多种字符集,可以使用 show character set; 来查看所有可用的字符集

21.png

或者使用

select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;

来查看。

使用 information_schema.character_set 来查看字符集和校对规则。

22.png


索引的设计和使用


我们上面介绍到了索引的几种类型并对不同的索引类型做了阐述,阐明了优缺点等等,下面我们从设计角度来聊一下索引,关于索引,你必须要知道的一点就是:「索引是数据库用来提高性能的最常用工具」


索引概述


所有的 MySQL 类型都可以进行索引,对相关列使用索引是提高 SELECT 查询性能的最佳途径。MyISAM 和 InnoDB 都是使用 BTREE 作为索引,MySQL 5 不支持函数索引,但是支持 前缀索引

前缀索引顾名思义就是对列字段的前缀做索引,前缀索引的长度和存储引擎有关系。MyISAM 前缀索引的长度支持到 1000 字节,InnoDB 前缀索引的长度支持到 767 字节,索引值重复性越低,查询效率也就越高。

在 MySQL 中,主要有下面这几种索引

  • 全局索引(FULLTEXT):全局索引,目前只有 MyISAM 引擎支持全局索引,它的出现是为了解决针对文本的模糊查询效率较低的问题,并且只限于 CHAR、VARCHAR 和 TEXT 列。
  • 哈希索引(HASH):哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构,很适合作为索引。HASH 索引具有一次定位的好处,不需要像树那样逐个节点查找,但是这种查找适合应用于查找单个键的情况,对于范围查找,HASH 索引的性能就会很低。默认情况下,MEMORY 存储引擎使用 HASH 索引,但也支持 BTREE 索引。
  • B-Tree 索引:B 就是 Balance 的意思,BTree 是一种平衡树,它有很多变种,最常见的就是 B+ Tree,它被 MySQL 广泛使用。
  • R-Tree 索引:R-Tree 在 MySQL 很少使用,仅支持 geometry 数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种,相对于 B-Tree 来说,R-Tree 的优势在于范围查找。

索引可以在创建表的时候进行创建,也可以单独创建,下面我们采用单独创建的方式,我们在 cxuan004 上创建前缀索引

23.png

我们使用 explain 进行分析,可以看到 cxuan004 使用索引的情况

24.png

如果不想使用索引,可以删除索引,索引的删除语法是

25.png


索引设计原则


创建索引的时候,要尽量考虑以下原则,便于提升索引的使用效率。

  • 选择索引位置,选择索引最合适的位置是出现在 where 语句中的列,而不是 select 关键字后的选择列表中的列。
  • 选择使用唯一索引,顾名思义,唯一索引的值是唯一的,可以更快速的确定某条记录,例如学生的学号就适合使用唯一性索引,而学生的性别则不适合使用,因为不管搜索哪个值,都差不多有一半的行。
  • 为经常使用的字段建立索引,如果某个字段经常用作查询条件,那么这个字段的查询速度在极大程度上影响整个表的查询速度,因此为这样的字段建立索引,可以提高整个表的查询速度。
  • 不要过度索引,限制索引数目,索引的数目不是越多越好,每个索引都会占据磁盘空间,索引越多,需要的磁盘空间就越大。
  • 尽量使用前缀索引,如果索引的值很长,那么查询速度会受到影响,这个时候应该使用前缀索引,对列的某几个字符进行索引,可以提高检索效率。
  • 利用最左前缀,在创建一个 n 列的索引时,实际上是创建了 MySQL 可利用的 n 个索引。多列索引可以起到几个索引的作用,利用索引最左边的列来匹配行,这样的列称为最左前缀。
  • 对于使用 InnoDB 存储引擎的表来说,记录会按照一定的顺序保存。如果有明确的主键定义,那么会按照主键的顺序进行保存;如果没有主键,但是有唯一索引,那么就按照唯一索引的顺序进行保存。如果既没有主键又没有唯一索引,那么表中会自动生成一个内部列,按照这个列的顺序进行保存。一般来说,使用主键的顺序是最快的
  • 删除不再使用或者很少使用的索引


视图


MySQL 从 5.0 开始就提供了视图功能,下面我们对视图功能进行介绍。


什么是视图


视图的英文名称是 view,它是一种虚拟存在的表。视图对于用户来说是透明的,它并不在数据库中实际存在,视图是使用数据库行和列动态组成的表,那么视图相对于数据库表来说,优势体现在哪里?

视图相对于普通的表来说,优势包含下面这几项

  • 使用视图可以简化操作:使用视图我们不用关注表结构的定义,我们可以把经常使用的数据集合定义成视图,这样能够简化操作。
  • 安全性:用户对视图不可以随意的更改和删除,可以保证数据的安全性。
  • 数据独立性:一旦视图的结构 确定了, 可以屏蔽表结构变化对用户的影响, 数据库表增加列对视图没有影响;具有一定的独立性


对视图的操作


视图的操作包括创建或者修改视图、删除视图以及查看视图定义。


创建或修改视图


使用 create view 来创建视图

为了演示功能,我们先创建一张表 product 表,有三个字段,id,name,price,下面是建表语句

create table product(id int(11),name varchar(20),price float(10,2));

然后我们向其中插入几条数据

insert into product values(1, "apple","3.5"),(2,"banana","4.2"),(3,"melon","1.2");

插入完成后的表结构如下

30.png

然后我们创建视图

create view v1 as select * from product;

然后我们查看一下 v1 视图的结构

31.png

可以看到我们把 product 中的数据放在了视图中,也相当于是创建了一个 product 的副本,只不过这个副本跟表无关。

视图使用

show tables;

也能看到所有的视图。

删除视图的语法是

drop view v1;

32.png

能够直接进行删除。

视图还有其他操作,比如查询操作

你还可以使用

describe v1;

33.png

查看表结构

更新视图

update v1 set name = "grape" where id = 1;

34.png

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4月前
|
存储 SQL 关系型数据库
MySQL语句详解:从基础到进阶的全面指南
MySQL语句详解:从基础到进阶的全面指南
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第三篇(MySQL性能优化)
MySQL数据库进阶第三篇(MySQL性能优化)
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
|
5月前
|
SQL 关系型数据库 MySQL
MySQL数据库进阶第五篇(锁)
MySQL数据库进阶第五篇(锁)
|
5月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
5月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
4月前
|
存储 关系型数据库 MySQL
MySQL数据库开发进阶:精通数据库表的创建与管理22
【7月更文挑战第22天】数据库的创建与删除,数据表的创建与管理
48 1
|
5月前
|
JSON 关系型数据库 MySQL
MySQL常用函数解读:从基础到进阶的全方位指南
MySQL常用函数解读:从基础到进阶的全方位指南
|
5月前
|
SQL 关系型数据库 MySQL
Python进阶第二篇(Python与MySQL数据库)
Python进阶第二篇(Python与MySQL数据库)
|
5月前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)