MySQL 최적화에 대한 참고 사항

                                                        频Mysql 비디오 튜토리얼

MySQL 최적화 방법 소개

권장(무료):

Mysql 비디오 튜토리얼 기록 학습 노트, 지속적으로 업데이트됩니다.

최적화 방향

SQL 최적화

sql 최적화 분석

인덱스 최적화

2. 데이터베이스 객체 최적화

테이블의 데이터 유형 최적화

테이블 분할(가로, 세로)

1. 안티 패러다임
2. 중간 테이블 사용
4. mysql 서버 최적화

mysql 메모리 관리 최적화

로그 메커니즘 및 최적화

1. mysql 동시성 매개변수 조정
3. 애플리케이션 최적화

데이터베이스 연결 풀

캐시 사용으로 부담 감소

1. 로드 밸런싱으로 클러스터 설정
2. 마스터-마스터 동기화, 마스터-슬레이브 복제
4. Mysql 최적화 문제 분석 및 위치 지정

SQL 실행 빈도 분석

show status

例如：分析读为主，还是写为主

낮은 SQL을 찾습니다. 실행 효율성

慢查询日志定位-log-slow-queries = xxx（指定文件名）SHOW PROCESSLIST查看当前正在进行的线程，包括线程状态、是否锁表

SQL 실행 계획 분석

explain "your sql"desc "your sql"- 部分参数分析
select_type: SIMPLE 简单表，不使用表连接或子查询PRIMARY 主查询，即外层的查询UNION SUBQUER 子查询的第一个select

type: ALL 全表扫描
index 索引全扫描
range 索引范围扫描
ref 使用非唯一索引或唯一索引的前缀扫描
eq_ref 类似ref，使用的索引是唯一索引const/system 单表中最多有一个匹配行NULL 不用访问表或者索引，直接得到结果

프로필 분석 표시 SQL

select @@have_profiling 是否支持
select @@profiling 是否开启

执行 "your sql"show profiles 
show profile block io for QUERY 17

인덱스 최적화

인덱스 저장 분류

B-TREE索引：常见，大部分都支持HASH索引：只有memory引擎支持R-TREE索引：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型
full-text索引：全文索引，MyISAM的一个特殊索引类型，innodb从5.6开始支持

인덱스 생성 및 삭제

添加索引ALTER Table `table_name` ADD PRIMARY KEY（`column`）ALTER Table `table_name` ADD UNIQUE（`column`）ALTER Table `table_name` ADD INDEX（`column`）ALTER Table `table_name` ADD FULLTEXT（`column`）

删除ALTER Table `table_name` drop index index_name

Mysq 인덱스

匹配全值
匹配值范围查询
匹配最左前缀
仅仅对索引进行查询（覆盖查询）
匹配列前缀 （添加前缀索引）
部分精确+部分范围

인덱스를 사용할 수 없는 시나리오

以%开关的like查询
数据类型出现隐式转换
复合索引查询条件不包含最左部分
使用索引仍比全表扫描慢
用or分割开的条件

mysql 문 최적화

정기적으로 테이블 최적화

optimize table table_name 合并表空间碎片，对MyISAM、BDB、INNODB有效

如果提示不支持，可以用 mysql --skip-new 或者 mysql --safe-mode 来重启，以便让其他引擎支持

공통 최적화

尽量避免全表扫描，对where及orderby的列建立索引
尽量避免where使用 != 或 <>尽量避免where子句用 or 连接条件
乱用%导致全表扫描
尽量避免where子句对字段进行表达式操作
尽量避免where子句对字段进行函数操作
覆盖查询，返回需要的字段
优化嵌套查询，关联查询优于子查询
组合索引或复合索引，最左索引原则
用exist代替in当索引列有大量重复数据时，SQL查询可能不会去利用索引

데이터베이스 개체 최적화

테이블 데이터 유형 최적화

PROCEDURE ANALYSE (16,256) 排除多于16个，大于256字节的ENUM建议"your sql" PROCEDURE ANALYSE ()

테이블 분할

垂直拆分
针对某些列常用、不常用

水平拆分
表很大
表中的数据有独立性，能简单分类
需要在表存放多种介质

anti-paradigm

增加冗余列、增加派生列、重新组表和分割表

중간 테이블 사용

数据查询量大
数据统计、分析场景

Mysql 엔진 비교

엔진의 역할 MySQL은 있나요?

테이블 엔진 명령 정보

show engines; 查看myql所支持的存储引擎
show variables like '%storage_engine'; 查看mysql默认的存储引擎
show create table table_name 查看具体表使用的存储引擎

innodb

1. 提供事务、回滚、系统奔溃修复能力、多版本并发控制事务2. 支持自增列3. 支持外键4. 支持事务以及事务相关联功能5. 支持mvcc的行级锁

MyISAM 정보

1. 不支持事务、不支持行级锁，只支持并发插入的表锁，主要用于高负载的select2. 支持三种不同的存储结构：静态、动态、压缩

매개 변수를 조정하여 mysql 백그라운드 서비스 최적화

MyISAM 메모리 최적화

#修改相应服务器位置的配置文件 my.cnf

key_buffer_size
决定myisam索引块缓存区的大小，直接影响表的存取效率，建议1/4可用内存

read_buffer 读缓存

write_buffer 写缓存

InnoDB 메모리 최적화

innodb_buffer_pool_size 存储引擎表数据和索引数据的最大缓存区大小

innodb_old_blocks_pct LRU算法 决定old sublist的比例

innodb_old_blocks_time LRU算法 数据转移间隔时间

mysql 동시성 매개변수

max_connections 最大连接数，默认151back_log 短时间内处理大量连接，可适当增大

table_open_cache 控制所有SQL执行线程可打开表缓存的数量，受其他参数制约

thread_cache_size 控制缓存客户服务线程数量，加快数据库连接速度，根据threads_created/connections来衡量是否合适

innodb_lock_wait_timeout 控制事务等待行锁时间，默认50ms

Mysql 애플리케이션 최적화 소개

애플리케이션 최적화가 필요한 이유

데이터의 중요성

mysql 서비스와 자체 성능 병목 현상

• 대규모 시스템의 안정성과 신뢰성 보장 실행
• 최적화 방법 적용

연결 풀

1. 을 사용하여 mysql에 대한 실제 연결 감소

   a. )
b. mysql 캐시 사용(sql 캐시)

2. 
   
   로드 밸런싱

   a. LVS 분산
b. 읽기 및 쓰기 분리(마스터-마스터 복제, 마스터-슬레이브 복제로 데이터 일관성 보장)

3. 
   
   

데이터베이스 연결 풀

php-cp 확장명, 이것을 기록하세요. 이 솔루션은 오래되었을 수 있습니다

마스터-슬레이브 백업 및 읽기-쓰기 분리

마스터-마스터 백업

로드 밸런싱

관련 무료 학습 권장 사항: php 프로그래밍(동영상)

위 내용은 MySQL 최적화에 대한 참고 사항의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!