MySQLの最適化に関する注意事項

                                                       

#mysql ビデオ チュートリアル コラムでは MySQL の最適化方法を紹介しています

推奨 (無料): mysql ビデオ チュートリアル

学習メモを記録し、継続的に更新します。

#最適化の方向

SQL 最適化

SQL 最適化分析

1. インデックス最適化

#データベース オブジェクトの最適化

#テーブルのデータ型の最適化

#テーブルの分割 (水平、垂直)

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

#Mysqlでインデックスが使用できる状況

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

インデックスを使用できないシナリオ

以%开关的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 ()

テーブル分割

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

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

アンチノーマル

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

中間テーブルを使用

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

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 サービスとそれ自体のパフォーマンスのボトルネック

大規模システムの安定した信頼性の高い運用を確保する

• アプリケーションの最適化方法
• 接続プールを使用する

mysql への実際の接続を減らす

a. 同じデータの繰り返し実行を避ける (クエリ キャッシュ)

b. mysql キャッシュを使用する (SQL キャッシュ)
ロード バランシング
2. a. LVS 分散

   b. 読み取りと書き込みの分離 (マスター/マスター レプリケーションおよびマスター/スレーブ レプリケーションによりデータの一貫性が保証されます)
   
   

3. データベース接続プール
   
   php -cp 拡張子、これを記録してください。このソリューションは古い可能性があります

   #マスター/スレーブ バックアップと読み取り/書き込み分離

   マスター/マスター バックアップ

   負荷分散

   関連する無料学習の推奨事項:

   php プログラミング (ビデオ)
   

以上がMySQLの最適化に関する注意事項の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。