MySQL 知识点总结

MySQL 知识点总结目录核心概念SQL 语法分类数据类型索引事务存储引擎性能优化备份与恢复常见问题一、核心概念术语说明数据库Database存储数据的集合MySQL 中以目录形式存在表Table数据的组织形式由行记录和列字段组成行Row一条具体的数据记录列Column表中的一个字段定义数据类型和约束主键Primary Key, PK唯一标识记录非空且唯一外键Foreign Key, FK关联两个表的字段引用另一表的主键索引Index优化查询速度的数据结构B树类似书籍目录事务Transaction一组不可分割的 SQL 操作要么全部执行要么全部回滚SQL操作关系型数据库的标准语言分为 DQL、DML、DDL、DCL二、SQL 语法分类1. DDL数据定义语言操作数据库/表结构-- 创建数据库指定字符集CREATEDATABASEIFNOTEXISTSdbnameCHARACTERSETutf8mb4;-- 删除数据库谨慎使用DROPDATABASEIFEXISTSdbname;-- 创建表CREATETABLEIFNOTEXISTSuser(idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(50)NOTNULLUNIQUE,ageTINYINTUNSIGNEDDEFAULT0,emailVARCHAR(100)UNIQUE,create_timeDATETIMEDEFAULTCURRENT_TIMESTAMP)ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 修改表结构ALTERTABLEuserADDphoneVARCHAR(11)UNIQUE;-- 添加字段ALTERTABLEuserMODIFYageTINYINTUNSIGNEDNULL;-- 修改字段类型ALTERTABLEuserCHANGE phone mobileVARCHAR(11);-- 修改字段名ALTERTABLEuserDROPmobile;-- 删除字段-- 删除表DROPTABLEIFEXISTSuser;2. DML数据操作语言操作表中数据-- 插入数据INSERTINTOuser(username,age,email)VALUES(zhangsan,20,zhangsanxxx.com),(lisi,25,lisixxx.com);-- 更新数据必须加 WHERE 条件UPDATEuserSETage26WHEREusernamelisi;-- 删除数据DELETEFROMuserWHEREage30;-- 可回滚TRUNCATETABLEuser;-- 不可回滚自增主键重置3. DQL数据查询语言查询表中数据核心重点-- 基础查询SELECTusername,ageFROMuser;-- 查询指定字段SELECT*FROMuser;-- 查询所有字段不推荐SELECTDISTINCTageFROMuser;-- 去重-- 条件查询SELECT*FROMuserWHEREageBETWEEN20AND30;SELECT*FROMuserWHEREusernameIN(zhangsan,lisi);SELECT*FROMuserWHEREusernameLIKEzhang%;-- 模糊查询SELECT*FROMuserWHEREemailISNOTNULL;-- 排序SELECT*FROMuserORDERBYageASC;-- 升序SELECT*FROMuserORDERBYcreate_timeDESC;-- 降序-- 分页起始索引从0开始SELECT*FROMuserLIMIT0,10;-- 第1页10条SELECT*FROMuserLIMIT10,10;-- 第2页10条-- 聚合查询SELECTCOUNT(*)AStotalFROMuser;-- 计数SELECTSUM(balance)AStotal_balanceFROMuser;-- 求和SELECTAVG(age)ASavg_ageFROMuser;-- 平均值SELECTMAX(age)ASmax_ageFROMuser;-- 最大值-- 分组查询SELECTage,COUNT(*)ASuser_numFROMuserGROUPBYage;SELECTage,COUNT(*)ASuser_numFROMuserGROUPBYageHAVINGuser_num1;-- 多表连接SELECTu.username,o.goods_nameFROMuseruINNERJOINorderoONu.ido.user_id;SELECTu.username,o.goods_nameFROMuseruLEFTJOINorderoONu.ido.user_id;-- 子查询SELECT*FROMuserWHEREidIN(SELECTuser_idFROMorderWHEREprice1000);4. DCL数据控制语言权限管理-- 创建用户CREATEUSERtest%IDENTIFIEDBY123456;-- 授权GRANTSELECT,INSERTONtest_db.*TOtest%;GRANTALLPRIVILEGESONtest_db.*TOtest%;-- 刷新权限FLUSHPRIVILEGES;-- 查看权限SHOWGRANTSFORtest%;-- 撤销权限REVOKEINSERTONtest_db.*FROMtest%;-- 删除用户DROPUSERtest%;-- 修改密码MySQL 8.0ALTERUSERrootlocalhostIDENTIFIEDBYnew_password;三、数据类型1. 数值类型类型字节范围说明tinyint1-128127有符号0255无符号小整数smallint2-32768~32767中整数int4-231~231-1整数常用bigint8-263~263-1大整数主键常用decimal(M,D)-M 总位数D 小数位数定点数金额常用float4-3.4e38~3.4e38单精度浮点数double8-1.8e308~1.8e308双精度浮点数2. 字符串类型类型说明长度限制char(N)固定长度字符串1~255varchar(N)可变长度字符串常用1~65535text长文本最多 65535 字节longtext超长文本最多 4GBenum枚举类型只能选一个值3. 日期时间类型类型格式说明date2025-11-25日期time14:30:00时间datetime2025-11-25 14:30:00日期时间常用timestamp2025-11-25 14:30:00时间戳自动关联时区year2025年份4. 常用约束约束说明关键字非空约束字段值不能为 nullNOT NULL唯一约束字段值在表中唯一UNIQUE主键约束非空 唯一PRIMARY KEY自增约束主键自动递增AUTO_INCREMENT默认约束未赋值时使用默认值DEFAULT外键约束关联另一表的主键FOREIGN KEY ... REFERENCES检查约束限制字段值范围CHECK(条件)MySQL 8.0四、索引1. 索引的作用优点缺点加速查询占用磁盘空间加速排序/分组降低写入效率2. 索引类型类型说明B树索引MySQL 默认适合范围查询、排序哈希索引适合等值查询不支持范围查询全文索引用于文本内容模糊查询功能分类说明主键索引默认创建查询效率最高唯一索引基于 UNIQUE 约束自动创建普通索引手动创建仅用于加速查询复合索引基于多个字段遵循最左前缀原则全文索引FULLTEXT INDEX3. 索引操作-- 创建索引建表时CREATETABLEuser(idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(50)NOTNULL,INDEXidx_username(username),INDEXidx_name_age(username,age),FULLTEXTINDEXidx_email(email));-- 建表后创建索引CREATEINDEXidx_ageONuser(age);CREATEUNIQUEINDEXidx_emailONuser(email);CREATEFULLTEXTINDEXidx_contentONarticle(content);-- 删除索引DROPINDEXidx_ageONuser;-- 查看索引SHOWINDEXFROMuser;4. 索引使用原则适合创建索引的场景频繁作为查询条件的字段频繁排序/分组的字段ORDER BY、GROUP BY外键字段关联查询时加速不适合创建索引的场景数据量小的表索引开销大于收益频繁更新/插入/删除的字段维护成本高重复率高的字段如 gender区分度低索引失效场景避免字段使用函数SUBSTRING(username, 1, 3) zha字段使用运算符age 1 20模糊查询以%开头username LIKE %san复合索引不满足最左前缀原则OR 连接的字段中有未加索引的字段五、事务1. ACID 特性特性说明示例原子性Atomicity要么全执行要么全回滚转账扣款和到账必须同时成功或失败一致性Consistency数据总状态保持一致转账前后双方总金额不变隔离性Isolation事务相互隔离避免脏读、不可重复读、幻读持久性Durability提交后数据永久保存即使宕机也不丢失2. 事务隔离级别级别脏读不可重复读幻读说明Read Uncommitted✅✅✅最低级别允许读取未提交数据Read Committed❌✅✅只能读取已提交数据Oracle 默认Repeatable ReadMySQL 默认❌❌❌同一事务内多次查询结果一致Serializable❌❌❌事务串行执行性能最低3. 事务操作-- 开启事务STARTTRANSACTION;-- 或 BEGIN;-- 执行 SQL 操作UPDATEuserSETbalancebalance-100WHEREid1;UPDATEuserSETbalancebalance100WHEREid2;-- 提交事务持久化COMMIT;-- 回滚事务出错时ROLLBACK;-- 查看当前隔离级别SELECTtransaction_isolation;-- 设置隔离级别SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;六、存储引擎常见存储引擎对比引擎事务行级锁外键适用场景InnoDB默认✅✅✅大多数应用场景MyISAM❌❌❌读多写少的场景MEMORY❌❌❌临时数据速度快ARCHIVE❌❌❌归档数据高压缩比操作存储引擎-- 查看支持的存储引擎SHOWENGINES;-- 修改表的存储引擎ALTERTABLEuserENGINEInnoDB;七、性能优化1. 查询优化-- 分析查询执行计划EXPLAINSELECT*FROMuserWHEREusernamezhangsan;-- EXPLAIN 结果关键字段说明-- type访问类型ALL index range ref eq_ref const-- key使用的索引-- rows扫描的行数-- Extra额外信息Using index、Using where、Using filesort2. 数据库设计优化规范化设计避免冗余数据使用合适的数据类型减少存储空间分区表以提高查询性能3. 配置优化my.cnf / my.ini[mysqld] # InnoDB 缓冲池大小建议设为物理内存的 50%-70% innodb_buffer_pool_size 4G # 查询缓存MySQL 8.0 已移除 query_cache_type ON # 慢查询日志 slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 2 # 查询超过2秒记录 # 连接数 max_connections 1000 # 字符集 character_set_server utf8mb4 collation_server utf8mb4_general_ci八、备份与恢复1. 备份mysqldump# 备份单个数据库mysqldump-uroot-pdbnamebackup.sql# 备份多个数据库mysqldump-uroot-p--databasesdbname1 dbname2backup.sql# 备份所有数据库mysqldump-uroot-p--all-databasesbackup.sql# 备份指定表mysqldump-uroot-pdbname user orderbackup.sql2. 恢复# 恢复数据库mysql-uroot-pdbnamebackup.sql# 创建数据库后恢复mysql-uroot-p-eCREATE DATABASE dbname;mysql-uroot-pdbnamebackup.sql九、常见问题1. 连接问题错误代码原因解决方案1045用户名或密码错误检查用户名和密码2003无法连接到服务器检查网络和防火墙1044权限不足授权用户权限2. 性能问题问题解决方案慢查询使用 EXPLAIN 分析优化索引和 SQL锁等待检查事务隔离级别避免长时间持有锁连接数过多增加 max_connections 或优化连接池3. 数据一致性问题问题解决方案主从复制延迟检查网络带宽优化复制配置数据丢失开启二进制日志定期备份十、常用命令速查-- 查看所有数据库SHOWDATABASES;-- 切换数据库USEdbname;-- 查看当前数据库中的表SHOWTABLES;-- 查看表结构DESCRIBEuser;-- 或 DESC user;-- 查看数据库版本SELECTVERSION();-- 查看当前用户SELECTUSER();-- 查看当前数据库SELECTDATABASE();-- 退出连接EXIT;-- 或 QUIT;