[MySQL 데이터베이스] 4장 해석: 스키마 및 데이터 유형 최적화(2부)

4.2 MySQL 스키마 설계의 함정

mysql 구현 메커니즘으로 인해 특정 오류가 발생하므로 이를 방지하는 방법은 다음과 같습니다.

1열이 너무 많습니다

MySQL 저장소 엔진 API는 다음과 같습니다. 작업 시 사용되는 서버 계층과 스토리지 엔진 계층은 row buffer 형식을 통해 데이터를 복사한 후, 서버 계층에서 버퍼 내용을 각 열로 디코딩하고, 인코딩된 열을 행 버퍼에서 행 데이터로 변환하는 작업입니다. 비싸요, myisam 고정 길이 행 서버 행 구조와 정확히 일치하며 변환이 필요하지 않습니다. 그러나 InnoDB의 가변 길이 행 구조는 항상 변환이 필요하며 변환 비용은 열 수에 따라 다릅니다.

2. 연관이 너무 많습니다

Entity-Attribute-Value EAV: 잘못된 설계 패턴, mysql은 각 연관 작업을 최대 61개 테이블로 제한하지만 EAV 데이터베이스에는 대략적인 자기 상관 관계가 많이 필요합니다. , 쿼리를 빠르고 좋은 동시성으로 실행하려면

12개의 테이블에 단일 쿼리를 연결하는 것이 가장 좋습니다

3. 열거의 남용 방지

외래 키 사용을 주의하세요. 특정 값을 찾기 위해 사전 테이블이나 조회 테이블에 추가해야 합니다. MySQL에서는

열거 열

테이블에 값을 추가해야 할 때 alter table을 수행해야 합니다. MySQL 5.0 이전 버전에서는 alter tableblocking 작업을 수행해야 합니다. , 5.1 업데이트된 버전에서는 목록 끝에 값을 추가하려면 변경 테이블도 필요합니다 4. 여기서는 발명되지 않은 Null

null을 저장하는 대신 0, 특수 값 또는 빈 문자열을 사용하는 것이 좋습니다. null을 사용하지 마십시오. 그러나 일부 시나리오에서는 null을 사용하는 것이 더 좋습니다.

create table ……（
//全0 （不可能的日期）会导致很多问题
    dt datetime not null default '0000-00-00 00:00:00'
    ……
）

MySQL은 null 값을 인덱스에 저장하고

Oracle은 1을 사용하지 않습니다. 업데이트 작업이 더 빨라집니다2. 데이터가 잘 정규화되면 중복 데이터가 거의 없고 수정해야 하는 데이터도 줄어듭니다

3. 정규화된 테이블은 더 작고 더 잘 처리될 수 있습니다.

4. 중복되는 데이터가 거의 없으며 목록 데이터를 검색할 때 필요한 고유 및 그룹화 문이 적습니다.

단점:

연결이 필요하므로 비용이 많이 들고 인덱스가 무효화될 수 있습니다.

4.3.2 Anti-Normal의 장점과 단점

연관을 피하세요. 메모리보다 큰 데이터는 연관보다 훨씬 빠를 수 있습니다(임의의 I/O 방지) 4.4 캐시 테이블 및 요약 테이블

캐시 테이블:

다음과 같은 경우에 매우 효과적입니다. 검색 및 검색 쿼리문 최적화,

다른 테이블에서 데이터를 쉽게 얻을 수 있는 테이블을 저장합니다(각 획득이 느림)

요약 테이블: Group by 문을 사용하여 집계 저장 데이터 테이블

은 데이터를 유지할지 여부를 결정합니다.

정기적인 재구성

: 자원 절약, 단편화 감소, 순차적으로 구성된 인덱스(효율성)

재구축 시 " 섀도우 테이블을 통해 작업 중에도 데이터의 가용성이 보장됩니다. " 달성하기 위해, 섀도우 테이블: 실제 테이블 뒤에 생성된 테이블, 테이블 생성 작업을 완료한 후 원자적 이름 변경 작업을 통해 섀도우 테이블과 원본 테이블을

전환할 수 있습니다.

4.4.1 구체화된 뷰 디스크에 미리 계산되어 저장된 테이블

. MySQL은 기본적으로 이를 지원하지 않으며 Justin Swanhart 도구를 사용하여 구현할 수 있습니다. flexviews:

flexviews 구성:

데이터를 가져오고 서버 바이너리 로그를 읽고 관련 행의 변경 사항을 구문 분석합니다.

뷰 정의를 생성하고 관리하는 데 도움이 되는 일련의 저장 프로시저

• 변경 사항을 뷰에 적용할 수 있는 몇 가지 도구 데이터베이스의 구체화된 뷰

• flexviews는 소스 테이블의 변경 사항을 추출하여 구체화된 뷰의 내용을

  증분적으로
재
• 재계산

  할 수 있습니다. 원본 데이터를 쿼리할 필요가 없습니다(효율적)

4.4.2 카운터 테이블

카운터 테이블: 사용자 친구 수, 파일 다운로드 시간 등을 캐시합니다. 쿼리 캐시 실패를 방지하려면 카운터를 저장하기 위한 독립 테이블을 생성하는 것이 좋습니다. 트랜잭션 업데이트 및 추가는 순차적으로만 실행할 수 있습니다. 동시성을 높이려면 카운터를 여러 행에 저장할 수 있습니다. 매번 업데이트할 행을 무작위로 선택하고 결과를 계산하려면 집계 쿼리를 사용하세요. 즉, 동일한 카운터가 여러 포인트를 저장하고 그 중 하나를 선택하여 매번 업데이트하고 최종적으로 요약하면 이해하기 쉽지 않은 것 같습니다. 몇 번 읽어보세요)

4.5 Alter Table 속도 향상 작업

mysql의 테이블 구조에 대한 대부분의 수정 사항은 다음과 같습니다. 새 결과가 포함된 빈 테이블 만들기, 이전 테이블에서 모든 데이터를 찾아 새 테이블에 삽입하기 .

mysql5.1及更新包含一些类型的“在线”操作的支持，整个过程不需要全锁表，最新版的InnoDB(MySQL5.5和更新版本中唯一的InnoDB)支持通过排序来建索引，建索引更快且紧凑的布局；

一般而言，大部分alter table导致mysql服务中断，对常见场景，使用的技巧：

1、先在一台不提供服务的机器上执行alter table操作，然后和提取服务的主库进行切换

2、影子拷贝，用要求的表结构创建张和源表无关的新表，通过重命名、删表交换两张表（上有）

不是all的alter table都引起表重建，理论上可跳过创建表的步骤：列默认值实际上存在表的.frm文件中，so可直接修改这个文件不需要改动表本身，但mysql还没有采用这种优化方法，all的modify column将导致表重建；

alter column：通frm文件改变列默认值：alter table容许使用alter column、modify column change column修改列，三种操作不一样;

alter table sakila.film alter column rental_duration set default 5;

4.5.1只修改frm文件

mysql有时在没有必要的时候也重建表，如果愿冒一些风险，可做些其他类型的修改而不用重建表：下面操作可能不能正常工作，先备份数据

下面操作不需要重建表：

     1、移除一个列的auto_increment

     2、增加、移除、更改enum和set常量，如果移除的是被用到的常量、查询返回空字符串

基本技术为想要的表结果创建新的frm文件，然后用它替换掉已经存在的那张表的frm文件：

     1、创建一张有相同结构的空表，进行所需的修改

     2、执行flush tables with read lock：关闭all正在使用的表且禁止任何表被打开

     3、交换frm文件

     4、执行unlock tables释放第2步的读锁

示例略 

4.5.2快速创建myISAM索引

1、为高效地载入数据到MyISAM表，常用技巧：先禁用索引、载入数据、重启索引：因为构建索引的工作延迟到数据载入后，此时可通过排序构建索引，快且使得索引树的碎片更少、更紧凑

但是对唯一索引无效（disable  keys），myisam会在内存中构造唯一索引且为载入的每一行检查唯一性，一旦索引大小超过有效内存、载入操作会越来越慢；

2、在现代版InnoDB中，有个类似技巧：先删除all非唯一索引，然后增加新的列，最后重建删除掉的索引（依赖于innodb快速在线索引创建功能）Percona server可自动完成这些操作；

3、像前alter table 的骇客方法来加速这个操作，但需多做些工作且承担风险，这对从备份中载入数据很有用，如already know all data is effective ，and no need to do the unique check

•     用需要的表结构创建一张表，不包括索引（如用load data file 且载入的表是空的，myisam可排序建索引）

• 载入数据到表中以构建MYD文件

• 按需要的结构创建另外一张空表，这次要包含索引，会创建.frm .MYI文件

• 获读锁并刷新表

• 重命名第二张表的frm文件 MYI，让mysql认为这是第一张表的文件

• 释放读锁

• 使用repair table来重建表的索引，该操作会通过排序来构建all索引、包括唯一索引 

4.6总结

良好的schema设计原则是普通使用的，但mysql有自己的实现细节要注意，概括来说：尽可能保持任何东西小而简单总是好的；mysql喜欢简单（好恰、我也是）

1. 最好避免使用bit

2. 使用小而简单的合适类型；

3. 尽量使用整型定义标识列

4. 매우 복잡한 쿼리나 많은 열로 이어지는 스키마 설계와 같은 과도한 설계를 피해야 합니다. 최대한 피하세요 실제 데이터 모델에서 꼭 필요한 경우가 아니면 null 값을 사용하세요

5. 유사하고 관련된 값을 저장하려면 동일한 유형을 사용해보세요 , 특히 연관 조건에 사용되는 열 🎜#

6. 임시 테이블 사용 및 정렬 시 최대 길이에 따른 비관적인 메모리 할당이 발생할 수 있으므로 가변 길이 문자열에 주의하세요

# 🎜🎜#부동 소수점 숫자의 정밀도나 정수의 표시 너비 지정과 같은 버려진 기능을 사용하지 마세요
Be enum과 set은 사용하기 편리하지만 남용하지 마세요 가끔은 함정이 되기도 합니다
Normal 패러다임도 좋지만 비정규화는 가끔 사전 계산, 캐싱 또는 요약 테이블 생성도 많은 이점을 얻을 수 있습니다. 이점
alter table 대부분의 경우 테이블이 잠기고 전체 테이블이 삭제됩니다. 이 장에서는 대부분의 시나리오에서 좀 더 일반적인 방법을 사용해야 합니다.
관련 기사: #🎜🎜 #

11. [MySQL 데이터베이스] 3장 해석: 서버 성능 분석(1부)

[MySQL 데이터베이스] 3장 해석: 서버 성능 분석(2부)

위 내용은 [MySQL 데이터베이스] 4장 해석: 스키마 및 데이터 유형 최적화(2부)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!