MySQL实战详解

GYVHV • 2025-04-24 06:40 • 编程

一、存储引擎

MySQL的存储引擎决定了数据如何被存储，不同的存储引擎适用于不同类型的应用场景。MySQL支持多种存储引擎，包括InnoDB、MyISAM、MEMORY等。

1、InnoDB

InnoDB是MySQL的默认存储引擎，支持ACID事务、行锁定和外键等特性，适用于高并发写入的应用。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2、MyISAM

MyISAM是MySQL的旧有存储引擎，不支持ACID事务和行锁定，并发读写性能较好，适用于读频繁的应用。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

3、MEMORY

MEMORY也称为HEAP，将数据存储在内存中而不是硬盘中，访问速度非常快，但是内存有限制，数据容易丢失。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

二、索引

索引可以加速数据的查找、过滤和排序，提高查询性能。MySQL支持多种类型的索引，包括B-tree、HASH和FULLTEXT等。

1、B-tree索引

B-tree是一种常见的索引类型，适用于范围查找和排序。MySQL支持普通索引、唯一索引和主键索引。

CREATE INDEX idx_name ON user(name);
CREATE UNIQUE INDEX idx_id ON user(id);
ALTER TABLE user ADD PRIMARY KEY (id);

2、HASH索引

HASH索引适用于等值查询，它使用哈希函数将数据分散到不同的桶中，哈希冲突的数据存储在同一个桶内。

CREATE INDEX idx_name ON user(name) USING HASH;

3、FULLTEXT索引

FULLTEXT索引适用于全文检索，它可以在文本中查找关键词。

CREATE FULLTEXT INDEX idx_name ON user(name);

三、优化

MySQL的优化可以提高数据库性能，包括优化查询、优化表结构、优化服务器设置等。

1、优化查询

优化查询可以通过索引、优化SQL语句、使用缓存等方式提高查询性能。

SELECT * FROM user WHERE age >= 18 ORDER BY id DESC LIMIT 10;

2、优化表结构

优化表结构可以通过限制字段长度、避免使用NULL值、使用ENUM代替VARCHAR等方式减小表的体积，提高查询性能。

CREATE TABLE `user` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `name` VARCHAR(50) NOT NULL COMMENT '用户名',
  `gender` ENUM('male', 'female') NOT NULL COMMENT '性别',
  `age` TINYINT(3) UNSIGNED NOT NULL COMMENT '年龄',
  `city` VARCHAR(50) NOT NULL COMMENT '城市',
  PRIMARY KEY (`id`),
  INDEX `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

3、优化服务器设置

优化服务器设置可以通过修改缓存大小、优化innodb_buffer_pool_size、调整max_connections等方式提高服务器性能。

innodb_buffer_pool_size=4G
max_connections=1000

四、数据备份与恢复

数据备份与恢复是数据库管理的重要方面，可以在数据意外丢失时快速恢复数据。

1、物理备份

物理备份可以将整个数据库备份到一个文件中，包括数据文件、二进制日志文件、配置文件等。

mysqldump -uroot -p dbname > backup.sql

2、逻辑备份

逻辑备份可以将数据库中的数据导出为SQL语句，适用于跨数据库备份和导入数据。

mysqldump -uroot -p dbname > backup.sql

3、数据恢复

数据恢复可以通过source命令、MySQL Workbench、命令行等方式恢复数据。

mysql -uroot -p dbname < backup.sql

原创文章，作者：GYVHV，如若转载，请注明出处：https://www.506064.com/n/372439.html