MySQL查看表行数
MySQL查看表行数
引言
评估表规模、判断是否需要分表、统计数据量时都要看行数。COUNT(*)最准但大表慢，information_schema的TABLE_ROWS快但InnoDB是估算值，两个手段按场景选。
精确统计：COUNT(*)
SELECT COUNT(*) FROM users;精确返回真实行数，MyISAM直接读元数据很快，InnoDB要逐行扫描，大表千万级以上会锁读、耗时长。
带条件统计
SELECT COUNT(*) FROM orders WHERE status = 1;统计满足条件的行数，WHERE命中索引时走索引扫描，比全表快。
估算行数：information_schema
SELECT TABLE_NAME, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'blog' AND TABLE_NAME = 'users';秒出结果，适合先摸底表规模，但InnoDB的TABLE_ROWS是基于采样的估算值，可能和真实值差10%到20%。
一次查看库里所有表行数
SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH/1024/1024 AS data_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'blog'
ORDER BY TABLE_ROWS DESC;按行数排序，快速找出最大的几张表，配合DATA_LENGTH看占用空间。
大表COUNT优化思路
- 先看估算值判断量级，需要精确再COUNT；
- COUNT(*)走二级索引，别COUNT(主键)以外的可空字段；
- 历史归档、分表后单表控制在千万行以内；
- 实时统计用缓存或汇总表，不在线上大表直接COUNT。
注意事项
- InnoDB的COUNT(*)不加WHERE返回约数，精确值必须扫描；
- information_schema的TABLE_ROWS只是估算，别当准确值用于对账；
- 大表COUNT期间会产生一致性读，占用缓冲池，避开高峰；
- 表刚大量增删后元数据统计信息可能陈旧，先ANALYZE TABLE刷新；
- MyISAM的TABLE_ROWS是精确值，和InnoDB机制不同；
- 统计行数别用COUNT(1)和COUNT(*)纠结，性能基本一致。
总结
COUNT()精确统计行数，大InnoDB表慢且耗资源；information_schema.TABLE_ROWS秒出估算值用于摸底。先看估算判断表规模，需要精确再COUNT()，条件统计尽量走索引。大表COUNT避开业务高峰，增删数据后先ANALYZE TABLE刷新元数据。实时报表建议走汇总表或缓存，避免线上大表反复全表扫描。
MySQL删除数据库
MySQL删除数据库
引言
下线测试库、清理废弃项目时需要DROP DATABASE。这是高危操作，一旦执行库和所有表立即删除不可回滚，必须先确认库名、确认备份，避免误删生产库。
删除指定数据库
DROP DATABASE db_test;删除整个库及其下所有表、视图、存储过程，磁盘空间立即释放。
库存在才删除
DROP DATABASE IF EXISTS db_test;加IF EXISTS库不存在时不报错，脚本里更安全，避免中断。
删除前先确认
SHOW DATABASES LIKE 'db_test';
USE db_test;
SHOW TABLES;先列出库和表，确认删的是目标库而不是同名生产库。
删除后验证
SHOW DATABASES;库从列表消失即删除成功；磁盘空间由MySQL异步释放，大库可能需等一小会。
不要删错：先备份
mysqldump -u root -p db_test > /backup/db_test.sql删除前先mysqldump导出，万一删错还能恢复。生产库删除前务必先备份再操作。
注意事项
- DROP DATABASE不可回滚，DELETE/TRUNCLE都比它温柔；
- 库名区分大小写（Linux下），别拼错；
- 需要DROP权限，普通业务账号不该有这个权限；
- 删除大库会锁实例一小段时间，高峰期别执行；
- 删除库不会自动删用户授权，残留grant需手动清理；
- 删库前确认无业务连接，可先停应用再删。
总结
DROP DATABASE删除整库，加IF EXISTS避免报错中断，删前用SHOW DATABASES确认目标、mysqldump先备份，删后再SHOW DATABASES验证。这是高危不可回滚操作，业务账号不应有DROP权限，删大库避开高峰期，删库后记得清理残留的用户授权。
MySQL修改字段类型
MySQL修改字段类型
引言
上线后字段长度不够、类型选错是常见需求，ALTER TABLE MODIFY可以改字段类型，但收缩长度或跨类型可能丢数据，大表执行还会长时间锁表，动手前必须备份和评估。
修改字段类型与长度
ALTER TABLE users
MODIFY COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';MODIFY重写该列完整定义，要把NOT NULL、默认值、字符集一起带上，否则会被重置。
修改字段名：CHANGE
ALTER TABLE users
CHANGE COLUMN tel phone VARCHAR(20) NOT NULL;CHANGE既要写旧名也要写新名，顺便改类型，MODIFY只改类型不改名。
数字类型扩容
ALTER TABLE orders
MODIFY COLUMN amount DECIMAL(12,2) NOT NULL;金额从DECIMAL(10,2)扩到(12,2)属于安全扩容，精度扩大不丢数据。
字符串加长
ALTER TABLE posts
MODIFY COLUMN title VARCHAR(255) NOT NULL;varchar加长一般安全，注意字符集下长度是字符数不是字节。
大表谨慎执行
# 大表避开高峰，用pt-online-schema-change或gh-ost在线改直接ALTER在InnoDB可能重建整表并锁写，百万行以上务必在线工具。
改前必查
SELECT COUNT(*) FROM users WHERE CHAR_LENGTH(phone) > 20;先查有没有超长数据，收缩长度前先清理，否则直接截断或报错。
注意事项
- MODIFY要带全字段约束，别只写类型；
- CHANGE改名+改类型，MODIFY只改类型；
- 收缩长度、改窄类型可能截断丢数据，先备份；
- 大表用在线DDL工具，避开高峰；
- 改完用SHOW CREATE TABLE核对；
- 类型转换不兼容（如字符串转数字）会失败或变0，先清洗数据。
总结
MODIFY改字段类型需重写完整定义含约束默认值，CHANGE可同时改名，数字扩容和字符串加长相对安全，收缩长度跨类型前先查超长数据并备份，大表用pt-osc或gh-ost在线改避免锁表，改完SHOW CREATE核对。生产改表先备份再评估锁表风险。
MySQL创建数据库
MySQL创建数据库
引言
新项目第一步往往是建库，CREATE DATABASE语法简单，但字符集和排序规则选错会在后期引发中文乱码、表情存不下、排序不一致等麻烦，最好在建库时一次定好。
基本建库
CREATE DATABASE mydb;用默认字符集建库，生产环境不建议这样，最好显式指定。
指定字符集建库（推荐）
CREATE DATABASE mydb
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;utf8mb4支持完整Unicode和emoji，general_ci不区分大小写，绝大多数Web项目够用。
判断不存在再建
CREATE DATABASE IF NOT EXISTS mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;IF NOT EXISTS防止重复执行报错，初始化脚本常用。
查看建库语句
SHOW CREATE DATABASE mydb;确认字符集、排序规则和预期一致。
修改已有库字符集
ALTER DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;只改库默认值，已有表和列的字符集不会跟着变，需逐表处理。
删除数据库
DROP DATABASE IF EXISTS mydb;不可逆，删前必须备份，IF EXISTS避免报错中断脚本。
授权配合
CREATE USER 'appuser'@'localhost' IDENTIFIED BY '强密码';
GRANT ALL ON mydb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;建库后给专用账号授权，不用root跑业务。
注意事项
- 统一utf8mb4，别用过时的utf8（只占3字节）；
- 排序规则ci结尾不区分大小写，bin区分；
- 库名小写、下划线分隔，避免跨平台大小写坑；
- 业务账号最小权限，禁止给ALL on .；
- ALTER库字符集不改存量表，需单独刷表；
- 初始化脚本加IF NOT EXISTS保证可重复执行。
总结
CREATE DATABASE建库，生产显式指定utf8mb4和排序规则，IF NOT EXISTS让脚本可重复执行，SHOW CREATE核对，ALTER只改默认值，DROP不可逆先备份，配合专用账号最小授权。建库定好字符集能避免后期乱码，库名规范小写，权限按库授予，别用root直接跑业务。
MySQL查看表索引
MySQL查看表索引
引言
排查慢查询、判断索引是否生效前，先要搞清楚一张表上到底建了哪些索引、每个索引包含哪些列、是不是唯一索引。SHOW INDEX和information_schema都能查，前者直观，后者适合脚本批量统计。
SHOW INDEX查看索引
SHOW INDEX FROM users;列出users表全部索引，Key_name是索引名，Column_name是索引列，Seq_in_index是列在联合索引里的顺序，Non_unique为0表示唯一索引。
重点字段含义
- Key_name：索引名，PRIMARY是主键；
- Seq_in_index：联合索引中的列顺序，最左前缀从这里判断；
- Column_name：该索引包含的列；
- Non_unique：0唯一，1普通；
- Sub_part：前缀索引长度；
- Cardinality：基数，越接近总行数区分度越好。
建表语句里看索引
SHOW CREATE TABLE users\G直接看完整建表语句，KEY和UNIQUE KEY就是索引定义，适合快速人工核对。
information_schema批量查
SELECT table_name, index_name, column_name, seq_in_index, non_unique
FROM information_schema.statistics
WHERE table_schema = 'mydb'
ORDER BY table_name, index_name, seq_in_index;一次性导出整库所有表的索引结构，做索引巡检和冗余分析。
查某列是否被索引覆盖
SELECT DISTINCT index_name FROM information_schema.statistics
WHERE table_schema='mydb' AND table_name='users' AND column_name='phone';判断phone列有没有索引，决定查询能否走索引。
注意事项
- 联合索引顺序决定最左前缀，别只看有没有建；
- Cardinality是估算值，会随ANALYZE TABLE刷新；
- 前缀索引Sub_part只索引前N个字符；
- 重复冗余索引用pt-duplicate-key-checker之类工具清理；
- 索引不是越多越好，写多表要权衡插入成本；
- 查完索引再配合EXPLAIN看实际是否命中。
总结
SHOW INDEX FROM表看索引明细，SHOW CREATE TABLE看定义，information_schema.statistics批量导出整库索引。重点关注索引名、列顺序、唯一性和基数，联合索引按最左前缀使用，建完索引务必用EXPLAIN验证是否真正命中，定期清理重复冗余索引，避免写放大。
MySQL查看数据库列表
MySQL查看数据库列表
引言
刚连上一台MySQL服务器，先要看实例上有哪些库、当前账号能访问哪些库。SHOW DATABASES是最基础的入口，配合information_schema可拿到库名、字符集、容量等完整信息。
列出所有数据库
SHOW DATABASES;列出当前账号有权限看到的全部库，看不到的库是权限不足，不代表不存在。
查看当前所在库
SELECT DATABASE();切换库之前先确认当前连接落在哪个库，避免写SQL操作错库。
切换数据库
USE mydb;切库后后续不加库名的表操作都落在mydb。
查库字符集和排序规则
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA;拿到每个库的默认字符集，排查乱码时先看这里。
查看库占用空间
SELECT table_schema,
ROUND(SUM(data_length+index_length)/1024/1024,2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;按库汇总数据和索引大小，快速找出占空间大户。
注意事项
- SHOW DATABASES只显示有权限的库，根账号才看全；
- information_schema是系统库，不要往里写业务数据；
- 新库建库时指定utf8mb4，避免emoji乱码；
- 删库DROP DATABASE不可逆，先备份再操作；
- 多实例用端口区分，别把实例和库混淆；
- 脚本里用SELECT SCHEMA_NAME批量遍历，比SHOW好解析。
总结
SHOW DATABASES列库，SELECT DATABASE()看当前库，USE切库，information_schema.SCHEMATA查字符集，按table_schema聚合查容量。先确认当前库再写SQL，建库统一utf8mb4，删库前务必备份，脚本批量处理优先走information_schema。
MySQL查看表结构
MySQL查看表结构
引言
接手老库、写SQL、加字段前，先看清表里有哪些列、类型、主键、索引和默认值。DESC看字段概览，SHOW CREATE TABLE拿到完整建表语句，information_schema可批量查元数据。
DESC看字段概览
DESC users;
-- 等价
DESCRIBE users;输出Field、Type、Null、Key、Default、Extra六列，快速看字段类型和是否主键。
完整建表语句
SHOW CREATE TABLE users;拿到原始CREATE TABLE，包含引擎、字符集、索引、外键、注释，迁移和备份最有用。
查看表索引
SHOW INDEX FROM users;看有哪些索引、唯一性、列顺序，分析慢查询时用。
查表状态和引擎
SHOW TABLE STATUS LIKE 'users';看引擎、行数、行格式、数据和索引大小、自增值。
information_schema批量查
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='dbname' AND TABLE_NAME='users';程序里动态读表结构、批量比对字段时用。
注意事项
- DESC不显示完整索引和外键，要看SHOW CREATE TABLE；
- 大表SHOW TABLE STATUS的行数是估算值，非精确；
- 看字段注释用information_schema的COLUMN_COMMENT；
- 修改结构前先SHOW CREATE TABLE留底；
- 区分NULL和默认空串，Default列直接体现；
- 自增字段在Extra列显示auto_increment。
总结
日常DESC快速看字段，要完整定义用SHOW CREATE TABLE，查索引用SHOW INDEX，看容量引擎用SHOW TABLE STATUS，脚本批量取元数据查information_schema。改结构前先把建表语句留底，避免误删索引或默认值。
MySQL唯一索引创建
MySQL唯一索引创建
引言
邮箱、手机号、订单号这类业务字段要求不能重复，光靠应用层判断并发下仍可能插入重复值。唯一索引在数据库层直接约束重复，是防脏数据的最后一道保险，同时还能加速等值查询。
建表时定义唯一索引
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
UNIQUE KEY uk_email (email)
);uk_email是索引名，email列唯一。
已有表追加唯一索引
ALTER TABLE users ADD UNIQUE KEY uk_phone (phone);数据里已有重复值时会报错1062，需先清理重复再加。
组合唯一索引
多列组合唯一，比如同一商品同一活动只领一次券：
ALTER TABLE coupon ADD UNIQUE KEY uk_user_goods (user_id, goods_id);允许单列重复，但组合起来不能重复。
插入重复的报错处理
INSERT INTO users(email) VALUES('a@x.com');
-- 再次插入同email触发 1062 Duplicate entry应用层catch错误码，返回邮箱已注册，而不是让500报错。
插入或更新冲突
INSERT INTO users(email) VALUES('a@x.com')
ON DUPLICATE KEY UPDATE login_time=NOW();唯一键冲突时转成更新，适合登录时间刷新。
注意事项
- 加唯一索引前先查重：
SELECT email,COUNT(*) FROM users GROUP BY email HAVING COUNT(*)>1； - NULL在唯一索引里可以出现多个，MySQL不认为NULL相等；
- 唯一索引也是普通索引，等值查询能走索引；
- 大表加索引会锁表或耗时，高峰期用在线DDL；
- 删除索引
DROP INDEX uk_email ON users。
总结
唯一索引在建表时用UNIQUE KEY定义，已有表用ALTER TABLE ADD UNIQUE KEY追加，多列组合唯一用组合索引。加之前先清重复，插入冲突用ON DUPLICATE KEY UPDATE转更新，数据库层约束比应用层判断更可靠。
MySQL枚举与集合
MySQL枚举与集合
引言
状态、性别、标签这类取值固定的字段，有人用varchar存中文，导致拼写不一、无法约束。MySQL提供ENUM单选和SET多选两个字符串型，把合法值列在定义里，从源头限制脏数据。
ENUM单选
一个字段只能取定义值中的一个，适合性别、订单状态：
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
status ENUM('待付款','已付款','已发货','已完成','已取消') NOT NULL DEFAULT '待付款'
);插入不在列表的值，严格模式直接报错，非严格模式存空串，避免写错状态。
SET多选
一个字段可存多个定义值，适合兴趣、标签、权限位：
CREATE TABLE users (
id INT PRIMARY KEY,
interest SET('阅读','运动','音乐','旅行') NOT NULL
);
-- 插入多个值，逗号分隔
INSERT INTO users(interest) VALUES('阅读,音乐');底层按位存储，最多64个成员。
两者区别
- ENUM单选，SET多选；
- ENUM内部按序号1、2、3存储，SET按二进制位；
- ENUM常见于状态机，SET常见于标签和多选权限；
- SET查单个值用FIND_IN_SET：
SELECT * FROM users WHERE FIND_IN_SET('阅读', interest);
注意事项
- 新增/修改枚举值要ALTER TABLE，字段多、值不稳定就别用ENUM；
- 枚举值排序按内部序号而非字面字母序，容易误判；
- 中文枚举注意字符集一致；
- 关系型设计里多值标签更推荐关联表，SET只适合简单固定多选；
- 不要用ENUM存会频繁增长的分类。
总结
ENUM单选约束状态，SET多选存标签，底层分别用序号和位存储。取值固定且变动少的字段用它们防脏数据，值频繁增加或需要多对多关系时改用独立关联表更规范。
MySQL自增ID重置
MySQL自增ID重置
引言
测试表删完数据后，下一条插入的自增ID还接着上次的数往上跳，演示或本地开发时想让ID从1重新开始，就要重置AUTO_INCREMENT。DELETE清空不会归零，这是新手最容易踩的坑。
DELETE不会重置自增
DELETE FROM users;
INSERT INTO users(name) VALUES('张三');
-- 新行ID仍然接着旧最大值递增，不从1开始DELETE只删行，自增计数器保留在内存和表结构里，不受影响。
TRUNCATE直接归零
TRUNCATE TABLE users;TRUNCATE相当于重建空表，自增计数清零、表结构保留，速度远快于全表DELETE，但会清空全部数据且不可回滚，谨慎使用。
只改起始值不清数据
表里还有数据时，把自增起点设为当前最大值+1：
ALTER TABLE users AUTO_INCREMENT = 1;若表内已有大于1的数据，MySQL会自动取max(id)+1作为真实起点，不会产生主键冲突。
查看当前自增值
SHOW TABLE STATUS LIKE 'users';
-- 或
SELECT AUTO_INCREMENT FROM information_schema.TABLES
WHERE TABLE_NAME='users';注意事项
- 生产环境不要随意TRUNCATE，误删数据无法恢复；
- 自增ID一般不连续，删行出现空洞属正常，不必追求从1排列；
- 用DELETE后想重置，单独ALTER设置AUTO_INCREMENT；
- InnoDB重启后自增值从max(id)重算，与MyISAM行为不同；
- 若ID被其他表外键引用，重置前先确认依赖。
总结
DELETE不清零自增，TRUNCATE才归零重建表；保留数据用ALTER TABLE ... AUTO_INCREMENT=1设起点，查当前值用SHOW TABLE STATUS。开发演示可重置，生产环境优先只调起点、避免TRUNCATE误删。
MySQL连接查询用法
MySQL连接查询用法
引言
业务数据分散在用户表、订单表、商品表中，要一次性取出用户姓名加订单金额，就要用JOIN把多张表按关联字段拼起来。分清内连接和外连接，是写对多表查询的关键。
内连接 INNER JOIN
只保留两表匹配上的行：
SELECT u.name, o.amount, o.order_no
FROM users u
INNER JOIN orders o ON u.id = o.user_id;没有订单的用户不会出现，没有对应用户的订单也不会出现。
左连接 LEFT JOIN
以左表为主，左表全部保留，右表匹配不上补NULL：
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;每个用户都列出来，没下单的用户amount显示NULL，常用于统计哪些用户没购买。
右连接 RIGHT JOIN
以右表为主，和左连接方向相反，实际项目多数统一用LEFT JOIN，可读性更好。
多表连接示例
SELECT u.name, p.title, o.amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON o.product_id = p.id
WHERE o.status = 1
ORDER BY o.create_time DESC;注意事项
- 关联字段类型要一致，避免隐式转换导致索引失效；
- ON写连接条件，WHERE过滤结果，别把连接条件误放WHERE导致LEFT JOIN变内连接；
- 大表JOIN先确认关联字段有索引；
- 多表查询用表别名，字段加别名前缀避免歧义；
- 不建议无关联条件的多表笛卡尔积。
总结
INNER JOIN取交集，LEFT JOIN保留左表全部。写多表查询先确定以哪张表为主，连接条件放ON，过滤条件放WHERE，关联字段建索引，避免隐式转换和笛卡尔积。
MySQL事务ACID原理
MySQL事务ACID原理
引言
转账、下单、扣库存这类多步操作必须保证要么全成功要么全回滚，靠的就是数据库事务。ACID是事务四个特性的缩写，理解它才能用对事务、排查并发问题。
四个特性
A 原子性 Atomicity
事务内操作不可分割，全部成功提交，失败则整体回滚到事务前状态。InnoDB靠undo log回滚日志实现，记录修改前旧值，出错反向撤销。
C 一致性 Consistency
事务前后数据库从一个合法状态转到另一个合法状态，约束（主键、外键、唯一、余额非负）始终不被破坏。一致性是最终目标，靠ABD共同保障。
I 隔离性 Isolation
并发事务之间互不干扰，隔离级别从低到高：读未提交、读提交、可重复读（InnoDB默认）、串行化，用锁和MVCC实现。
D 持久性 Durability
事务一旦提交，修改永久落盘，宕机也不丢。靠redo log重做日志，先写日志再刷盘，崩溃后重放恢复。
事务用法示例
START TRANSACTION;
UPDATE account SET money = money - 100 WHERE id = 1;
UPDATE account SET money = money + 100 WHERE id = 2;
COMMIT;
-- 出错则 ROLLBACK;PHP中配合PDO：
$pdo->beginTransaction();
try {
$pdo->exec("UPDATE account SET money=money-100 WHERE id=1");
$pdo->exec("UPDATE account SET money=money+100 WHERE id=2");
$pdo->commit();
} catch (Exception $e) {
$pdo->rollBack();
}注意事项
- 事务不要包太长，避免长时间持锁；
- 只对必须一致的操作开事务；
- 一致性靠应用校验和数据库约束共同保证；
- 隔离级别越高并发越低，按业务权衡。
总结
ACID：原子性靠undo log、持久性靠redo log、隔离性靠锁与MVCC、一致性是目标。转账扣库存等关键链路用事务包裹，注意控制事务长度和隔离级别。
MySQL分页查询优化
MySQL分页查询优化
引言
后台列表、文章分页是最常见的SQL场景。数据量小的时候LIMIT偏移量查询很快，数据到几十万、上百万行，深分页会明显变慢，甚至拖垮数据库。
为什么深分页慢
LIMIT 100000,20 会先扫描前100000行再丢弃，只返回20行，扫描行数越多IO开销越大。
优化方案一：子查询定位主键
先查出起始主键，再用主键关联取数据，避免回表扫描：
SELECT a.* FROM article a
INNER JOIN (
SELECT id FROM article ORDER BY id LIMIT 100000,20
) b ON a.id=b.id;子查询走覆盖索引，速度明显提升。
优化方案二：游标/记住上一页最大ID
SELECT * FROM article
WHERE id > 100000
ORDER BY id LIMIT 20;每次只查比上一页最后一条ID大的记录，不走大偏移，适合无限滚动。
其他优化点
- 分页排序字段必须建索引，避免filesort；
- 只查询需要的字段，不用SELECT *；
- 禁止前端跳转到任意深页，后台限制最大页码；
- 超大表考虑分表、搜索引擎承接列表。
总结
深分页慢的根源是大偏移扫描。优先用主键游标翻页，配合覆盖索引；传统offset分页用子查询优化。列表数据量极大时，交给ES等搜索方案更合理。
MySQL慢查询定位
MySQL慢查询定位
引言
网站访问缓慢很多根源是执行时间过长的SQL。开启MySQL慢查询日志，可以捕获执行超时SQL，找到需要优化的语句，是数据库性能排查的核心手段。
慢查询基础配置
慢查询日志会记录执行时长超过指定阈值的SQL语句。
-- 查看慢查询是否开启
SHOW VARIABLES LIKE 'slow_query_log';
-- 设置阈值，单位秒，超过1秒就记录
SET GLOBAL long_query_time =1;常用分析工具
- mysqldumpslow：mysql自带工具，统计慢日志
mysqldumpslow -s t /var/lib/mysql/slow.log - pt-query-digest：percona工具，分析效果更强，适合大量日志。
优化排查流程
- 开启慢查询，收集慢SQL；
- 使用explain查看执行计划，判断是否缺少索引；
- 优化where条件，添加合适索引，改写SQL逻辑；
- 优化完成后，持续观察慢日志确认效果。
示例
EXPLAIN SELECT * FROM article WHERE title like '%PHP%';看type字段，ALL代表全表扫描，是需要优化的信号。
总结
慢查询日志用来抓取低效SQL，但是开启会少量增加服务器IO压力，生产环境可以按需开启。定位到慢SQL后优先检查索引，再调整SQL写法，解决数据库卡顿问题。
MySQL索引设计原则
MySQL索引设计原则
引言
MySQL索引相当于书籍目录，快速定位数据，避免全表扫描。不合理的索引不仅无法提升查询速度，还会降低写入性能。索引设计是数据库优化最基础、最高效的手段，建站、接口开发都需要掌握。
索引核心原理
InnoDB引擎索引基于B+树实现，主键索引叶子节点存储完整行数据；二级索引叶子节点存储主键值，查询会触发回表。索引字段越大，占用磁盘越多，内存缓存能存放的索引越少，查询越慢。
索引设计核心原则
- 选择性优先：优先给区分度高的字段建索引，如手机号、ID；性别、状态这类只有少量枚举值字段不适合单独建索引。
- 联合索引遵循最左匹配原则，字段顺序：等值查询在前，范围查询放后面。
- 避免在索引字段做函数运算，
where YEAR(create_time)=2026会直接失效索引。 - 不要过度建索引，每次新增、更新、删除数据，所有索引都需要同步更新，拖累写入。
示例SQL
-- 联合索引示例，适合 where uid=100 and create_time>xxx
CREATE INDEX idx_uid_ct ON user(uid,create_time);
-- 查看sql是否命中索引
EXPLAIN SELECT * FROM user WHERE uid=100;常见踩坑
- 字符串字段不加引号，隐式类型转换，索引失效；
- 模糊查询
%关键词，前置百分号不走索引； - 联合索引跳过左侧字段，无法触发索引。
总结
索引不是越多越好，按需创建。建索引前先用explain分析执行计划，优先优化高频查询SQL，控制索引数量，平衡查询与写入性能。