【7天玩转MySQL】华为云RDS for MySQL学习笔记 Day02 【7天玩转MySQL】华为云RDS for MySQL学习笔记 Day01

【7天玩转Mysql】华为云RDS for MySQL学习笔记  Day02

第二天的内容主要是：表和索引

目标：了解MySQL的数据存储方式、数据访问及数据更新机制，如何定义表结构以有效利用空间；了解如何建立合适的索引以提高对数据整体使用的性能；了解建立索引需要的注意事项等。

一、数据库--库

库（Database或者Schema）是一系列相关的数据库对象的集合

二、数据库--表

---摘自维基百科：数据库表

数据库表的基本构成包括：表属性、字段、数据类型、类型描述

MySQL 支持五大数据类型：

数值型，如 BIGINT,FLOAT 和 DECIMAL 等

日期和时间类型，如 DATE ， TIME 和 TIMESTAMP 等

字符串类型，如VARCHAR,CHAR 和 BLOB 等

空间数据类型，如 GEOMETRY， POINT 和 POLYGON 等

JSON 数据类型

数据类型的选择取决于字段存储的具体数据：

确保正确存储数据

空间利用最优

同一个数据可能有多种存储方式，选择最合适的方式：

如 phone_number ，为什么选择 VARCHAR （20）?

字段数限制

MySQL 允许每张表支持最多 4096 个字段

InnoDB 对每张表最多字段限制是 1017 个字段

不建议一个表包含过多的字段，影响整体的使用体验

行长度限制

MySQL 允许每条记录最长达到 65535 字节——不考虑大对象字段

InnoDB 限制每条记录的长度不超过 8K （基于华为云 RDS 配置）

对于超过长度的场景，也可以处理，但是影响性能

按需定义字段类型，按需存储，缩减行数据占用空间

分区表

支持多种分区方式

RANGE/LIST/COLUMNS/KEY/HASH

支持子分区

支持分区操作

添加 / 删除 / 重整 / 合并 / 交换等操作

分区表和普通表可以相互转换

支持对指定分区进行查询

不是银弹

性能考虑

功能考虑

上图分区表创建后分区效果如下图所示：

三、数据库--索引

1）索引是什么

维基百科上对于索引的定义如下：

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

翻译如下：

数据库索引是一种数据结构，它可以提高对数据库表的数据检索操作的速度，但要维护索引数据结构，需要额外的写入和存储空间。索引用于快速定位数据，而不必每次访问数据库表时都搜索数据库表中的每一行。可以使用数据库表的一个或多个列来创建索引，为快速随机查找和高效访问有序记录提供了基础。

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

2）Btree索引

BTree（多路搜索树，并不是二叉的）是一种常见的数据结构。使用BTree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。按照翻译，B 通常认为是Balance的简称。这个数据结构一般用于数据库的索引，综合效率较高。

什么情况下使用Btree？

全值匹配的查询

匹配最左前缀的查询

匹配列前缀查询

范围查找

精确匹配左前列并范围匹配另外一列

只访问索引的查询

Btree有什么限制：

如果不是按照索引最左列开始查找，那么无法使用索引。

使用索引时不能跳过索引中的列。

not in、<>、!=操作无法使用索引。

如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引。

3）聚簇索引

聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。

聚簇索引的特点：

聚簇索引就是主键索引(Primary Key);

聚簇索引/主键索引只能一张表一个;

即使表没有定义主键索引，聚簇索引也存在;

聚簇索引一定是 Btree 索引;

聚簇索引直接影响了数据的排序;

索引的键值必须是唯一的;

聚簇索引生成示意图：

4）二级索引

同一张表上非主键（聚簇）索引即为二级索引，二级索引可以有多个，按照索引特性可以分为唯一索引和非唯一索引

下图展示了二级索引与聚簇索引的关联：

聚簇索引与其说是索引，不如说是InnoDB用来存储记录的数据容器更为恰当。

二级索引同样使用B-Tree数据结构，不同的是叶节点只存储二级索引的键值和聚簇索引键值（通常是Primary Key），聚簇索引键值是用于回表查询该条记录。

注意到上图中二级索引键值的顺序和聚簇索引键值顺序通常不同，所以二级索引做范围查询读取记录的性能通常不如聚簇索引高效（回表操作会有大量的随机IO）。因为二级索引会存储聚簇索引的键值，因此储聚簇索引键值的大小也会影响二级索引的大小，所以在选择聚簇索引键值时需要注意这点。

另外当SELECT的字段被二级索引覆盖的话，MySQL就不需要再回表查询了，这样执行速度更快。

5）唯一索引

聚簇索引必须是唯一索引

二级索引可以定义为唯一索引

唯一性约束保证每个索引键值都不会重复

定义前要确认数据能否满足唯一性

唯一性约束在特殊场景下可能被打破（华为云RDS for MySQL能最大程度避免这个问题）

四、最佳实践

1）表/聚簇索引

单表大小需要有效控制

太大的表不利于性能

考虑使用分区表（水平切分）或者拆表（垂直切分）

分区表的一个好处是，老的数据不用了，可以整个分区删除

聚簇索引必须按照 PK 排序

PK 键值尽可能小，减少索引整体大小，必须为 NOT NULL 字段

PK 覆盖的字段，最好保证数据能有序插入

如果表中每个字段都无法保证唯一、无法保证 NOT NULL ，或者不适合做索引，推荐自定义一个 ID 自增列作为主键，自动满足有序插入

定义 PK 的重要性——不定义会怎样?

lnnoDB 会使用隐藏的 ROW_ID 作为主键

表的一些访问会很低效

影响 Binlog 复制的应用的效率

强烈建议一定要自定义主键 !!

常见的就是自增字段：

2）索引的两面性

索引能加快查询，是不是越多越全越好?

并不是，取决于业务侧的重点和需求

正确的索引才能加速查询

索引是有代价的

额外的二级索引占用额外的存储空间

多个二级索引混合存储导致 IO 不连续

二级索引有维护的代价

1）增删改都需要同步每个受影响的二级索引

2）最差的是对 PK 的更新，需要同步每个二级索引

3）更新 first_name ，所有索引都要更新

4）更新 age ，所有二级索引都不涉及

5）更新 phone_number , address 的索引不涉及

3）二级索引

4）建库、表和索引语句

//建库 CREATE DATABASE 数据库名称 //建表 CREATE TABLE 表名称 ( 列名称1 数据类型, 列名称2 数据类型, ....... ) 如： CREATE TABLE Person  ( LastName varchar, FirstName varchar, Address varchar, Age int )  //建索引 CREATE UNIQUE INDEX 索引名称 ON 表名称 (列名称)  如： CREATE INDEX PersonIndex ON Person (LastName)

《7天玩转MySQL基础实战营》全系列笔记：

【7天玩转MySQL】华为云RDS for MySQL学习笔记 Day01

RDS MySQL 华为云数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。