MySQL中的部分问题（2）
索引失效
运算或函数影响列的使用
- 当查询条件中对索引列用了函数或运算，索引会失效。
例：假设有索引：index idx_name (name)
select * from users where upper(name) = 'ALICE'; -- 索引失效
因为upper(name)会对列内容进行函数处理，不能直接利用索引。
使用范围条件（如 <, >, BETWEEN）影响索引选择
- 对索引列使用范围条件后，通常索引的部分数据会被扫描，但如果查询使用了多个条件，索引失效可能会发生。
例:
select * from orders where order_date >= '2025-01-01' and order_date <= '2025-05-01';
在单列索引的情况下会利用索引，但如果在多列索引的第二列有条件，索引失效的可能性增大。
- 在前字段使用范围查询可以利用索引，但是不能都使用或者后面字段单独使用
- 当然在联合索引中，即使顺序不同依然可以使用索引。
使用不等于（!=，<>）或OR连接的条件
- 对于or和某些操作符，索引也可能失效。
select * from products where category_id = 1 or category_id = 2; -- 索引可能失效
MySQL可能会选择全表扫描。
没有使用比较符（如LIKE没有匹配前缀）
- like的使用影响索引。如果模式以通配符%开头，索引通常失效。
例：
select * from customers where name like '%John'; -- 索引失效
因为未匹配前缀。
联合索引没有使用最左匹配
例：有索引：(user_id, created_at)
select * from logs where created_at = '2025-06-01'; -- 索引失效
因为没有在user_id条件上，MySQL可能不使用索引。
出现隐式类型转换，如字符型索引使用数字匹配
例:假设有一个表 users，有一个字符类型的索引列 user_id（比如 VARCHAR类型）
create index idx_user_id on users (user_id);
如果用数字去匹配：
select * from users where user_id = 123;
MySQL会将数字123隐式转换成字符 ‘123’，因为列user_id是字符型。
- 本质：
- 索引失效的原因在于： 索引列的类型与查询条件的值类型不一致，导致MySQL进行类型转换，失去索引的作用。
索引列使用运算
例：
假设有一张表 employees，包括字段 salary，并在这个字段上建立索引：
create table employees (id int primary key,name varchar(50),salary int,index idx_salary (salary)
);
正常查询（索引生效）
select * from employees where salary = 5000;
- 这里直接比较salary，索引可以被利用，实现快速查找。
使用运算（索引失效）
select * from employees where salary + 1000 = 6000;
- 这里在 salary 上用了加法运算 索引失效
select * from employees where salary * 2 > 10000;
- 这里用了乘法，同样导致索引无法被利用。
排查索引失效
- 使用explain查看查询计划，确认索引是否被用到。
- 查看type列：
- ALL 表示全表扫描（索引未使用）
- index 表示只用索引（全索引扫描）
- range 表示范围扫描（索引有效）
调整查询条件，优化索引设计。
type值 | 说明 | 是否用到索引 |
|---|---|---|
ALL | 全表扫描 | 用到索引，但不利用索引（劣化的情况） |
index | 全索引扫描（只扫描索引结构） | 用到索引，更快，但还要读取所有索引条目 |
ref | 索引查找（基于索引的查找） | 用到索引，效率较高 |
range | 范围扫描（索引范围查找） | 用到索引 |
const | 常数值查找，单行匹配 | 用到索引 |
不同数据库的特点
关系型数据库（SQL数据库）
MySQL
- 开源、免费，广泛应用
- 支持事务、多表连接、ACID特性
- 适合中小型网站、应用后台
- 存储引擎多（InnoDB、MyISAM）
- 生态丰富，社区活跃
PostgreSQL
- 开源、功能强大
- 支持复杂查询、完整的事务和ACID
- 支持扩展（如自定义数据类型、函数）
- 持续优化性能
- 更符合标准SQL，支持复杂的关系操作
Oracle
- 商业数据库，功能强大
- 支持大规模、高并发、复杂事务
- 丰富的企业级功能（数据分区、复制、备份等）
- 高昂的授权费用
Microsoft SQL Server
- 商业数据库，集成微软生态
- 易用性强，界面友好
- 强大的分析和报表功能
- 适合企业级应用
非关系型数据库（NoSQL）
MongoDB（文档型）
- 存储BSON格式（类似JSON）
- 灵活的模式（Schema-free）
- 易于水平扩展
- 适用场景：内容管理、实时分析
Redis（键值存储）
- 内存数据库，高速
- 支持丰富的数据结构（字符串、哈希、列表、集合）
- 用于缓存、消息队列、排行榜
Cassandra（宽列存储）
- 高度可扩展，支持大规模数据
- 高可用，无单点故障
- 适合分布式环境、物联网
Couchbase
- 结合了文档与键值
- 支持弹性扩展
- 多模式存储和实时同步
关系型数据库与非关系型数据库的不同
1. 数据模型
-
关系型数据库（SQL）
- 使用结构化的表（表格）
- 数据以行和列形式存储
- 有固定的模式（schema），预定义字段和数据类型
- 表之间可以通过外键关联（关系）
-
非关系型数据库（NoSQL）
- 不一定用表结构，多样化模型：
- 文档（MongoDB）
- 键值（Redis）
- 列族（Cassandra）
- 图（Neo4j）
- 不一定用表结构，多样化模型：
-
模式灵活（schema-free）
-
可存储非结构化或半结构化数据
2. 事务和一致性
-
关系型数据库
- 支持复杂的事务（如银行转账）
- 遵循ACID原则（原子性、一致性、隔离性、持久性）
- 适合对数据一致性要求高的场景
-
非关系型数据库
- 多数场景追求高性能和扩展性，可能牺牲部分一致性（遵循BASE原则）
- 一些支持事务（例如MongoDB的多文档事务），但通常不追求全文事务支持
- 适合对数据一致性要求不极端的场景
3. 扩展性
-
关系型数据库
- 一般垂直扩展（升级硬件）
- 水平扩展较复杂（需分区分库）
-
非关系型数据库
- 天生设计为水平扩展（易于添加节点）
- 更适合大规模分布式环境
4. 查询语言
-
关系型数据库
- 标准SQL查询
- 强调复杂查询、联结、聚合
-
非关系型数据库
- 使用各自的API或专用查询语言
- 查询可能不如SQL灵活，但对特定场景优化更快
应用场景
| 类型 | 典型应用场景 |
|---|---|
| 关系型数据库（SQL） | 事务型应用（财务、ERP、CRM）、复杂查询、严格一致性需求 |
| 非关系型数据库（NoSQL） | 实时大数据、内容管理、网络应用、缓存、物联网、大规模分布式存储 |
总结
| 特点 | 关系型数据库（SQL） | 非关系型数据库（NoSQL） |
|---|---|---|
| 数据模型 | 结构化表格（固定schema） | 灵活多样（文档、键值、列族、图） |
| 事务支持 | 强（支持ACID） | 轻量或有限（追求扩展性，可能牺牲一致性） |
| 扩展性 | 垂直扩展较强，水平扩展复杂 | 水平扩展，易于做大规模分布式系统 |
| 查询方式 | SQL | API/专用查询语言 |
| 适用场景 | 需要复杂关系和强一致性系统 | 高扩展性、可变结构、实时性能要求场景 |
时序数据库
InfluxDB
- 针对时间序列数据优化（如传感器数据、监控数据）
- 高效存储和查询时间段数据
- 支持聚合、连续查询
图数据库
Neo4j
- 适合存储和分析复杂的关系（社交网络、推荐系统）
- 图结构存储，关系查询高效
- 支持Cypher查询语言
主要区别总结
| 特点 | 关系型（SQL） | 非关系型（NoSQL） | 时序数据库 | 图数据库 |
|---|---|---|---|---|
| 模式设计 | 固定 schema | Schemaless/灵活 | 时间敏感模型 | 图结构存储 |
| 扩展性 | 垂直扩展（纵向升级） | 水平扩展 | 水平扩展 | 水平扩展 |
| 事务支持 | 强（ACID） | 弱或无（最终一致性） | 视情况支持 | 支持有限或无事务 |
| 查询方式 | SQL | API（接口、查询语言） | 时间范围查询 | 图关系查询（Cypher） |
| 应用场景 | 业务系统、财务、CRM | 大数据、内容管理等 | 监控、传感器存储 | 关系强、连接复杂的场景 |
选择MySQL数据库的原因
-
- 开源免费
- 成本低廉，适合个人、初创公司和中小企业使用，不需要高昂的授权费用。
-
- 使用广泛、社区活跃
- 拥有庞大的用户基础和丰富的资源，文档、教程、插件多，遇到问题容易获得支持。
-
- 良好的性能表现
- 在中小型应用和读写压力不极端的场景下，表现优异，响应速度快。
-
- 支持事务和ACID特性
- 支持完整的事务，确保数据一致性和可靠性，适合金融、用户管理等需要严格数据完整性的场景。
- ACID是数据库事务的四个基本特性
-
- 易用性强
- 操作简单、配置灵活，支持多平台，管理工具（如phpMyAdmin）丰富。
-
- 丰富的功能和扩展性
- 支持存储引擎选择（InnoDB、MyISAM）等，支持视图、存储过程和触发器，满足多样化需求。
-
- 高度兼容性和可扩展性
- 支持多种开发语言（PHP、Python、Java等）集成，适应各种技术架构。
-
- 适用范围广
- 适用于网站、电商平台、内容管理系统、后台管理等多个领域。
MySQL的核心优势在于开源免费、性能稳定、支持事务和ACID特性、操作简单、社区资源丰富，适合中小型应用开发和部署。同时，它具有良好的扩展性和兼容性，能够满足多种业务场景的需求。而其他数据库则在特定方面（比如企业级事务、复杂查询、多样的数据模型）具有更明显的优势。
MySQL日志
MySQL的日志系统是数据库管理的重要组成部分，用于记录数据库的运行信息、错误、查询操作等，有助于监控、调试和性能优化。
- bin log（二进制日志）是InnoDB引擎特有的，只记录该引擎中表的修改记录。binlog是MySQL的Server层实现的，会记录所有引擎对数据库的修改。
- 开启方式
[mysqld]
log_bin = /path/to/mysql-binlog
- redo log（物理日志），记录的是在具体某个数据页上做了什么修改; binlog是逻辑日志，记录的是这个语句的原始逻辑。
- undo log（回滚日志）是循环写的，空间固定会用完; binlog是可以追加写入的, binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
- error Log(错误日志)，记录MySQL启动、关闭，以及运行中出现的错误信息、警告、注意事项，包括启动信息、崩溃信息、权限问题、配置错误等。
- 配置
[mysqld]
log_error = /path/to/error.log
-
General Query Log（查询日志），记录所有客户端发来的查询请求（包括连接、断开、执行的所有SQL语句）。调试和审计，但会带来性能开销，不建议长期开启。
- 开启方式
set global general_log = 'ON';
set global log_output = 'FILE'; -- 输出到文件
- 补充
- 1、redolog记录修改内容(哪一页发生了什么变化)，写于事务开始前，用于数据未落磁盘，但数据库挂了后的数据恢复。
- 2、binlog记录修改SQL，写于事务提交时，可用于读写分离。
- 3、undolog记录修改前记录，用于回滚和多版本并发控制。