Mysql数据库优化问题

aloneship

MySQL 索引类型详解

索引是提升查询性能的核心手段。MySQL 支持多种索引类型，适用于不同场景。

1. B+Tree 索引（最常用）

• 存储引擎：InnoDB（默认）、MyISAM
• 结构：B+ 树，数据存储在叶子节点，支持范围查询。
• 适用场景：
  
  • 等值查询（=）
  • 范围查询（>, <, BETWEEN）
  • 排序（ORDER BY）
  • 分组（GROUP BY）
    
  
  

示例：

-- 创建普通索引CREATE INDEX idx_name ON user(name);-- 创建联合索引（最左前缀原则）CREATE INDEX idx_name_age ON user(name, age);

✅ InnoDB 的主键索引是聚簇索引（Clustered Index），数据行直接存储在主键 B+ 树的叶子节点。

---

2. 哈希索引（Hash Index）

• 存储引擎：MEMORY（默认）、InnoDB 支持自适应哈希索引
• 结构：哈希表，O(1) 查找
• 优点：等值查询极快
• 缺点：
  
  • 不支持范围查询
  • 不支持排序、模糊查询（LIKE 'abc%' 可以，LIKE '%abc' 不行）
  • 哈希冲突影响性能
    
  
  

示例：

-- MEMORY 引擎自动使用哈希索引CREATE TABLE temp (    id INT PRIMARY KEY,    name VARCHAR(50)) ENGINE=MEMORY;

⚠️ InnoDB 不支持用户创建哈希索引，但会自动为热点数据创建自适应哈希索引（AHI）。

---

3. 全文索引（Full-Text Index）

• 适用场景：文本内容搜索（如文章标题、内容）
• 支持引擎：InnoDB、MyISAM
• 语法：
  
  CREATE FULLTEXT INDEX idx_content ON article(content);-- 查询SELECT * FROM article WHERE MATCH(content) AGAINST('ThinkPHP' IN NATURAL LANGUAGE MODE);
  

✅ 适合博客、新闻等系统的关键词搜索。

---

4. 空间索引（R-Tree）

• 适用场景：地理空间数据（如经纬度）
• 支持引擎：MyISAM、InnoDB（MySQL 5.7+）
• 类型：POINT, LINESTRING, POLYGON
• 示例：
  
  CREATE TABLE location (    id INT PRIMARY KEY,    coord POINT NOT NULL);CREATE SPATIAL INDEX idx_coord ON location(coord);
  

---

5. 前缀索引（Prefix Index）

• 场景：对长字符串字段（如 VARCHAR(255)）只索引前 N 个字符
• 优点：节省空间，加快索引速度
• 缺点：可能降低选择性，影响性能
  

示例：

-- 只对 email 前 10 个字符建索引CREATE INDEX idx_email ON user(email(10));

🔍 选择性 = DISTINCT(前缀) / 总行数，越高越好。

---

6. 覆盖索引（Covering Index）

• 定义：查询的字段全部包含在索引中，无需回表
• 性能极佳：避免了磁盘 I/O
  

示例：

-- 联合索引CREATE INDEX idx_name_age_status ON user(name, age, status);-- 查询只用索引字段，无需访问数据行SELECT name, age FROM user WHERE name = '张三' AND age > 20;

✅ “索引即数据”，是优化的高级技巧。

aloneship

二、MySQL 数据库优化策略
1. SQL 优化
✅ 避免全表扫描
sql
-- ❌ 慢
SELECT * FROM user WHERE name LIKE '%三';

-- ✅ 快（前缀匹配）
SELECT * FROM user WHERE name LIKE '张%';
✅ 使用覆盖索引
sql
-- ❌ 需要回表
SELECT * FROM user WHERE name = '张三';

-- ✅ 覆盖索引，更快
SELECT id, name FROM user WHERE name = '张三';
✅ 避免在 WHERE 子句中对字段进行函数操作
sql
-- ❌ 无法使用索引
SELECT * FROM user WHERE YEAR(create_time) = 2025;

-- ✅ 使用范围查询
SELECT * FROM user WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01';
2. 索引优化原则
✅ 最左前缀原则（联合索引）
sql
CREATE INDEX idx_a_b_c ON table(a, b, c);

-- ✅ 有效：a, a+b, a+b+c
-- ❌ 无效：b, c, b+c
✅ 选择性高的字段放前面
sql
-- 好：status 选择性低（0/1），name 选择性高
CREATE INDEX idx_status_name ON user(status, name);

-- 更好：name 放前面
CREATE INDEX idx_name_status ON user(name, status);
✅ 避免过多索引
每个索引都占用空间，影响写性能（INSERT/UPDATE/DELETE 需要更新索引）
建议单表索引不超过 6 个
3. 表结构优化
✅ 使用合适的数据类型
INT vs BIGINT：根据业务选择
VARCHAR(255) 不要滥用，按需定义
使用 ENUM 替代字符串状态码（如 status ENUM('active','inactive')）
✅ 避免使用 SELECT *
只查询需要的字段，减少网络传输和内存占用
✅ 合理使用范式与反范式
范式化：减少冗余，适合 OLTP
反范式化：提高查询性能，适合 OLAP（如数据仓库）

aloneship

3. 表结构优化
✅ 使用合适的数据类型
INT vs BIGINT：根据业务选择
VARCHAR(255) 不要滥用，按需定义
使用 ENUM 替代字符串状态码（如 status ENUM('active','inactive')）
✅ 避免使用 SELECT *
只查询需要的字段，减少网络传输和内存占用
✅ 合理使用范式与反范式
范式化：减少冗余，适合 OLTP
反范式化：提高查询性能，适合 OLAP（如数据仓库）
4. 配置优化（my.cnf）
ini
[mysqld]
# 缓冲池，建议设置为物理内存的 70%-80%
innodb_buffer_pool_size = 2G

# 日志文件大小
innodb_log_file_size = 256M

# 日志缓冲
innodb_log_buffer_size = 64M

# 连接数
max_connections = 500

# 查询缓存（MySQL 8.0 已移除）
# query_cache_type = 1
# query_cache_size = 64M

# 排序缓冲
sort_buffer_size = 4M
join_buffer_size = 4M
5. 读写分离与分库分表（TP6 结合）
✅ TP6 读写分离
php
// config/database.php
'read' => [
    ['hostname' => 'slave1'],
    ['hostname' => 'slave2'],
],
'write' => [
    ['hostname' => 'master'],
],
'deploy' => 1,
✅ 分表策略
按时间分表：log_202501, log_202502
按用户 ID 取模：user_0, user_1, ..., user_9
TP6 支持动态切换表：

php
$userId = 123456;
$table = 'user_' . ($userId % 10);
Db::name($table)->where('id', $userId)->find();
6. 慢查询日志分析
开启慢查询日志，找出性能瓶颈。

sql
-- 开启慢查询
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒的查询

-- 查看慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log
7. 使用 EXPLAIN 分析执行计划
sql
EXPLAIN SELECT * FROM user WHERE name = '张三';
关注字段：

type：访问类型（ALL 全表扫描最差，index、range、ref、const 依次变好）
key：实际使用的索引
rows：扫描行数
Extra：额外信息（Using filesort、Using temporary 是性能杀手）