存储工具（二）数据库的表与字段

背景介绍

上一节存储工具（一）序介绍了数据库的重要性，而在本节，将充分介绍数据库的表、字段的创建与优化。

表的创建与优化

如果我们要设计一张表，需要存储用户的个人信息，比如用户名、密码、邮箱、手机号，还要支持对用户进行禁用/解禁。

最开始，我们理解需求，根据业务需求创建一张符合所有业务字段的表。

CREATE TABLE users (
    username VARCHAR(512) NOT NULL COMMENT '用户名',
    password_hash VARCHAR(512) NOT NULL COMMENT '密码',
    email VARCHAR(100) NOT NULL DEFAULT '' COMMENT '邮箱',
    phone VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
    status BIGINT UNSIGNED NOT NULL COMMENT '状态: 1-正常, 0-禁用'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户基础信息表';

让我们看看这一张表

存储了所有需要的字段。
将对用户的启用禁用抽象为状态字段。

让我们来分析一下它有哪些值得优化的点。

首先数据库最好有一个主键id字段，方便通过索引按id查询、更新、删除等，也为后续关联其他表做好准备。
用户名建议符合业务需求，而不是直接填写一个特别长的值。
对于一张表，最好记录创建和修改时间，便于了解某一行的入库时间和更新时间。
密码不能明文存储，建议哈希化。
邮箱和手机号一般不重复，可以建立唯一索引，方便入库的校验和后续查询。
状态字段主要为1和0的简单数字，可以改为TINYINT，尽可能使用最高效（最小）的数据类型。
字符集最好使用utf8mb4，支持 Emoji 表情等特殊字符。
MySQL 5.7 及以下‌，按utf8mb4_unicode_ci 排序，‌可以获得更好的排序准确性。

下面是优化过的表

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    username VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户名',
    password_hash CHAR(64) NOT NULL DEFAULT '' COMMENT '密码哈希值',
    email VARCHAR(100) NOT NULL DEFAULT '' COMMENT '邮箱',
    phone VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
    status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态: 1-正常, 0-禁用',
    last_login_at DATETIME DEFAULT NULL COMMENT '最后登录时间',
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    KEY idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户基础信息表';

mysql支持多种新增方式

直接使用INSERT新增或者批量新增；
使用INSERT ... SELECT，将其他表当来源新增；
使用LOAD DATA INFILE，从文件中新增；

表的更新和删除

绝大多数情况，我们通过自增的主键id来进行更新或者删除。这样会有几个好处。

查询速度快，数据库会给自增的主键创建主键索引，通过它查询效率很高。
不易出错，自增的主键id不重复，不会因为操作失误影响其他行。

批量删除/更新
某些情况，我们会遇到批量删除或者更新，这时候可以根据查询的自增主键ID利用in查询，一批一批处理（比如500个一组，并在一组新增后加上50ms的sleep，防止锁表）。

表的查询和优化

如果需要对表字段进行查询，我们推荐使用where来约束查询条件，这样可以减少查询行，加快查询效果。

索引
索引可以理解为在表上加了一本字典，方便我们快速查询，注意索引不是万能的，每次增加数据都会在索引上新增该部分，所以可以移除没用上的索引。

主键索引（Primary Key Index），PRIMARY KEY
唯一索引（Unique Index），创建方式‌：使用UNIQUE
普通索引（Regular Index），创建方式‌：使用INDEX或KEY关键字
组合索引（Composite Index）

唯一索引与主键索引类似，字段都是不能重复的，主键索引一般用自增的bigint，唯一索引一般用于手机号，用户名，邮箱等不重复的字段。
普通索引和组合索引，是可以重复的字段的。一般用于查询频率高，字段值比较多。（组合索引值支持前缀，下面会说明）

引申
问：若用户名、邮箱、手机号三个字段查询较多，如何提高查询效率？
答：按查询频率创建联合索引。
如下创建索引（遵循“最左前缀”原则, 三个字段都能通过索引查询到）

ALTER TABLE users ADD INDEX idx_username_email_phone(username, email, phone);
ALTER TABLE users ADD INDEX idx_email_phone(email, phone);

反例：比如

-- 新增联合索引
ALTER TABLE table1 ADD INDEX idx_a_b_c(a,b,c);
-- 可以命中索引
SELECT a,b,c FROM table1 where a=? and b=?;
-- 无法命中索引 
SELECT a,b,c FROM table1 where b=? and c=?;

如下使用索引(只查询索引项，不回表)

select username, email, phone from users where username = 'xxxx' and email = 'xxxx' and phone = 'xxxx';

问：若有一个字段比较长，如何创建索引？
答：可以只索引前缀。
比如对于长字符串字段（如 location VARCHAR(512)），如果前几个字符就能区分大部分数据，可以只索引前缀，节省空间。

CREATE INDEX idx_name_prefix ON users(location(32));

问：若有一个字段仅有两个值，是否适合创建索引？
答：基数低、区分度差的字段不适合创建普通索引。
比如性别和状态这类字段，不适合创建索引，因为MySQL优化器会选择全表扫描。

问：哪些情况下索引会失效

非前缀匹配的like查询（只有前缀匹配支持索引 LIKE 'abc%'）
对索引字段进行函数操作，算数计算或者类型转化（也包含隐式转化）
联合索引没有查询最左前缀的字段。

问：如果线上遇到慢sql如何处理

mysql开启慢sql日志；
通过 explain 判断是否使用上了索引；

type列出现ALL就是全表扫描，必须优化；
key列为NULL说明没有用到索引；
rows列估算扫描行数越小，SQL执行效率越高。
若sql为全表扫描，可以查看where查询写的是否正确，如果sql无误，则考虑新增索引，并再次用 explain 判断索引是否生效。

使用索引的最佳实践

通过explain语句判断sql是否使用索引。
创建索引遵循“最左前缀”原则。
使用索引尽量只查询索引列，减少回表。
低区分度列不建议创建索引。

尾声

在数据量比较小，查询较少时，关系数据库对我们来说已经足够使用，可以思考遇到一些下面的情况，我们将怎么实现？

如果遇到一个热点数据，读取量很大，怎么实现热点数据读取时间快速？
如果遇到一个数据，存储结构经常变更，如何存放？
若遇到多篇文章，文字量很大，如何实现全文高效搜索？
如果引入新的存储介质，又会带来什么问题，该如何解决？

在下一节我们介绍非关系型数据库，以及一些个人总结的最佳实践，同时会解答上面的问题。

posted @ 2026-08-23 01:24 Kwanwooo 阅读(4) 评论(0) 收藏举报

刷新页面返回顶部

Kwanwooo

存储工具（二）数据库的表与字段

背景介绍

表的创建与优化

表的更新和删除

表的查询和优化

尾声

公告