MySQL 데이터베이스의 기본 동작 사례 분석

1. MySQL 소개

1. 데이터베이스 관리 소프트웨어의 분류

크게 관계형과 비관계형으로 나뉜다.

관계형 데이터베이스는 테이블 구조가 있어야 하고, 비관계형 데이터베이스는 키-값 데이터를 저장하고 테이블 구조가 없다고 간단히 이해하면 됩니다.

관계 유형: sqllite, db2, oracle, access, sql server, MySQL 등 참고: sql 문은 보편적입니다.

비관계형: mongodb, redis, memcache

2, MySQL

MySQL은 스웨덴 MySQL AB 회사에서 개발한 관계형 데이터베이스 관리 시스템으로 현재 Oracle의 제품입니다.

MySQL은 가장 널리 사용되는 관계형 데이터베이스 관리 시스템 중 하나입니다. 웹 애플리케이션 측면에서 MySQL은 최고의 RDBMS(관계형 데이터베이스 관리 시스템) 애플리케이션 소프트웨어입니다.

SQL 언어는 가장 일반적으로 사용되는 표준화된 언어이며 MySQL 데이터베이스에 액세스하는 데 사용됩니다. MySQL 소프트웨어는 이중 라이센스 정책을 채택하고 있으며 작은 크기, 빠른 속도, 낮은 총 소유 비용, 특히 오픈 소스의 특성으로 인해 커뮤니티 버전과 상용 버전으로 구분됩니다. 중소규모 웹사이트 개발.

MySQL은 Mac, Linux, Windows를 포함한 다양한 운영 체제용 오픈 소스 설치 패키지를 제공합니다.

2. 스토리지 엔진(테이블 유형이라고도 함)

MySQL의 데이터는 다양한 기술을 사용하여 파일(또는 메모리)에 저장됩니다. 다양한 기술은 고유한 저장 메커니즘, 인덱싱 기술 및 잠금 수준을 사용하여 다양한 기능과 기능을 제공합니다. MySQL은 이러한 다양한 기술과 관련 기능을 스토리지 엔진이라고 하며, 이를 테이블 유형이라고도 합니다.

MySQL은 기본적으로 다양한 스토리지 엔진으로 구성되며, 이는 MySQL 서버에서 미리 설정하거나 활성화할 수 있습니다.

1. 일반적인 스토리지 엔진 및 적용 가능한 시나리오

• InnoDB: 외래 키 및 행 수준 잠금을 지원하는 트랜잭션 처리 애플리케이션에 사용됩니다. 애플리케이션이 사물의 무결성에 대한 요구 사항이 상대적으로 높고 동시 조건에서 데이터 일관성이 필요하며 데이터 작업에 삽입 및 쿼리 외에도 많은 업데이트 및 삭제 작업이 포함되는 경우 InnoDB 스토리지 엔진이 더 적합합니다.
  InnoDB는 삭제 및 업데이트로 인한 잠금을 효과적으로 줄이는 것 외에도 트랜잭션의 완전한 제출 및 롤백을 보장할 수 있습니다. 이는 데이터 정확성에 대한 요구 사항이 높은 청구 시스템 또는 금융 시스템과 같은 시스템에 적합한 선택입니다.

• MyISAM: 애플리케이션이 주로 읽기 작업 및 삽입 작업을 기반으로 하고 몇 가지 업데이트 및 삭제 작업만 사용하며 트랜잭션 무결성 및 동시성에 대한 높은 요구 사항이 없는 경우 이 스토리지 엔진을 선택할 수 있습니다.

• 메모리: 모든 데이터를 메모리에 저장하여 기록 및 기타 유사한 데이터를 빠르게 찾아야 하는 환경에서 매우 빠른 액세스를 제공합니다.
  메모리의 단점은 테이블 크기에 제한이 있다는 점입니다. 데이터베이스가 비정상적으로 종료되면 정상적으로 데이터를 복구할 수 있지만, 데이터베이스가 닫히면 메모리에 저장된 데이터가 손실됩니다.

mysql이 지원하는 스토리지 엔진에는 InnoDB, MyISAM, MEMORY, CSV, BLACKHOLE, NDB, FEDERATED, MRG_MYISAM, ARCHIVE, PERFORMANCE_SCHEMA가 포함됩니다.
그 중 NDB와 InnoDB는 트랜잭션에 안전한 테이블을 제공하고, 다른 스토리지 엔진은 트랜잭션에 안전한 테이블을 제공하지 않습니다.

2. mysql

# 查看当前的默认存储引擎:
mysql> show variables like "default_storage_engine";

# 查询当前数据库支持的存储引擎
mysql> show engines \G;

1. 테이블 생성 시 스토리지 엔진을 지정합니다

mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; 
mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;

# 也可以使用alter table语句，修改一个已经存在的表的存储引擎。
mysql> alter table ai engine = innodb;

2. MySQL 테이블 작업

1. 테이블 구조

테이블 구조를 보는 방법에는 두 가지가 있습니다:

desc[테이블 이름] 및 설명[테이블 이름]: 이 두 가지 방법은 동일한 효과를 가지며 현재 테이블 구조를 볼 수 있습니다.
show create table [tablename]: 테이블 정의 외에도 엔진(스토리지 엔진), charset(문자 집합) 등의 정보도 볼 수 있습니다. G 옵션을 사용하면 레코드를 세로로 정렬하여 긴 레코드를 더 쉽게 표시할 수 있습니다. )
예:

# my.ini文件
[mysqld]
default-storage-engine=INNODB

2. 자동 증가 열

제한된 필드는 자동으로 증가하며 동시에 제한되는 필드는 키 기본 키로 제한되어야 합니다

mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.00 sec)

mysql> show create table staff_info\G;
*************************** 1. row ***************************
       Table: staff_info
Create Table: CREATE TABLE `staff_info` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  `phone` bigint(11) DEFAULT NULL,
  `job` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.01 sec)

ERROR: 
No query specified

4.

1. ENUM 및 SET

ENUM 타입의 중국어 이름을 열거형(enumeration type)이라고 하며, 테이블 생성 시 해당 값의 범위를 열거형으로 표시해야 합니다.
• ENUM을 사용하면 한 번에 여러 값이 아닌 값 컬렉션에서 단일 값만 선택할 수 있습니다. 목적: 단일 선택: 성별 선택

  
  

ENUM:

1~255명의 구성원을 열거하려면 1바이트의 저장 공간이 필요합니다.

255~65535명의 구성원의 경우 2바이트의 저장 공간이 필요합니다.
최대 허용 인원은 65535명입니다.

SET은 ENUM과 매우 유사하며 문자열 객체이며 0-64개의 ​​멤버를 포함할 수 있습니다. 회원에 따라 저장공간이 다릅니다.
• 세트 유형을 사용하면 조합할 값 세트에서 1개 이상의 요소를 임의로 선택할 수 있습니다. 범위를 벗어난 내용에 대해서는 주입이 허용되지 않으며, 중복된 값은 자동으로 중복제거됩니다. 사용법: 객관식: 취미 및 성별

SET：
1-8个成员的集合，占1个字节
9-16个成员的集合，占2个字节
17-24个成员的集合，占3个字节
25-32个成员的集合，占4个字节
33-64个成员的集合，占8个字节

2、set/enum示例

mysql> create table t10 (name char(20),gender enum('female','male') );
Query OK, 0 rows affected (0.01 sec)

-- 选择enum('female','male')中的一项作为gender的值，可以正常插入
mysql> insert into t10 values ('nick','male');
Query OK, 1 row affected (0.00 sec)

-- 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值
mysql> insert into t10 values ('nick','male,female');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车') );
Query OK, 0 rows affected (0.01 sec)

-- 可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能
mysql> insert into t11 values ('tank','烫头,喝酒,烫头');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t11;
+------+---------------+
| name | hobby        |
+------+---------------+
| tank | 喝酒,烫头     |
+------+---------------+
row in set (0.00 sec)

-- 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项，
mysql> insert into t11 values ('jason','烫头,翻车,看妹子');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

五、MySQL表查询

1、限制查询的记录数（limit）

示例：

SELECT * FROM employee ORDER BY salary DESC 
    LIMIT 3;    --默认初始位置为0 

SELECT * FROM employee ORDER BY salary DESC
    LIMIT 0 , 5 ; --从第0开始，即先出第一条，然后包含这一条在内往后查5条

SELECT * FROM employee ORDER BY salary DESC
    LIMIT 5 , 5 ; --从第5开始，即先出第6条，然后包含这一条在内往后查5条

2、使用正则表达式查询

小结：对字符串匹配的方式

• WHERE emp_name = 'nick';

• WHERE emp_name LIKE 'sea%';

• WHERE emp_name REGEXP 'on$';

SELECT * FROM employee WHERE emp_name REGEXP '^jas';
SELECT * FROM employee WHERE emp_name REGEXP 'on$';
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';

六、数据备份(命令行)

1、 数据库的逻辑备份

--语法：
mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

--示例：
--单库备份
mysqldump -uroot –p123  mysql > c:\db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

--多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

--备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

2、 数据恢复

--方法一：
[root@nick backup]-- mysql -uroot -p123 < /backup/all.sql

--方法二：
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;   --关闭二进制日志，只对当前session生效
mysql> source /root/db1.sql

七、事务和锁（SQL）

begin;  -- 开启事务
   select * from emp where id = 1 for update;  -- 查询id值，for update添加行锁；
    update emp set salary=10000 where id = 1; -- 完成更新
commit; -- 提交事务

八、执行计划Explain

执行计划：让mysql预估执行操作(一般正确)

Explain语法：

explain select … from … [where …]

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。

具体用法和字段含义可以参考官网explain-output ，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（rows：显示MySQL认为它执行查询时必须检查的行数。）。所以优化语句基本上都是在优化rows。

例如：

explain select * from news;

输出：

+--+-----------+-----+----+-------------+---+-------+---+----+-----+ 
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| 
+--+-----------+-----+----+-------------+---+-------+---+----+----—+

위 내용은 MySQL 데이터베이스의 기본 동작 사례 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!