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数据库的原因

• 1. 开源免费
  • 成本低廉，适合个人、初创公司和中小企业使用，不需要高昂的授权费用。
• 2. 使用广泛、社区活跃
  • 拥有庞大的用户基础和丰富的资源，文档、教程、插件多，遇到问题容易获得支持。
• 3. 良好的性能表现
  • 在中小型应用和读写压力不极端的场景下，表现优异，响应速度快。
• 4. 支持事务和ACID特性
  • 支持完整的事务，确保数据一致性和可靠性，适合金融、用户管理等需要严格数据完整性的场景。
  • ACID是数据库事务的四个基本特性
• 5. 易用性强
  • 操作简单、配置灵活，支持多平台，管理工具（如phpMyAdmin）丰富。
• 6. 丰富的功能和扩展性
  • 支持存储引擎选择（InnoDB、MyISAM）等，支持视图、存储过程和触发器，满足多样化需求。
• 7. 高度兼容性和可扩展性
  • 支持多种开发语言（PHP、Python、Java等）集成，适应各种技术架构。
• 8. 适用范围广
  • 适用于网站、电商平台、内容管理系统、后台管理等多个领域。

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记录修改前记录，用于回滚和多版本并发控制。