Notes on MySQL optimization

mysql video tutorial column introduces the optimization method of MySQL

Recommended (free): mysql video tutorial

Record study notes and continuously update.

Optimization direction

SQL optimization

1. sql optimization analysis
2. Index optimization

Optimize database objects

1. Optimize the data type of the table
2. Table split (horizontal, vertical)
3. Anti-paradigm
4. Use intermediate table

Optimize mysql server

1. mysql memory management optimization
2. log mechanism And optimization
3. Adjust mysql concurrency parameters

Application optimization

1. Database connection pool
2. Use cache to reduce Pressure
3. Load balancing to establish a cluster
4. Master-master synchronization, master-slave replication

##Mysql optimization problem analysis and positioning

Analyze SQL execution frequency

show status

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

Copy after login

Locate SQl with low execution efficiency

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

Copy after login

Analyze SQL execution plan

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

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

Copy after login

show profile analysis SQL

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

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

Copy after login

Index optimization

Index storage classification

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

Copy after login

Creation and deletion of indexes

添加索引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

Copy after login

Situations where indexes can be used in Mysql

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

Copy after login

Scenarios where indexes cannot be used

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

Copy after login

mysql statement optimization

Optimize the table regularly

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

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

Copy after login

Commonly used optimization

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

Copy after login

Optimize database objects

Optimize table data types

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

Copy after login

Table split

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

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

Copy after login

Anti-Normal

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

Copy after login

Use intermediate table

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

Copy after login

Mysql engine comparison

What engine does mysql have?

Commands about table engine

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

Copy after login

About innodb

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

Copy after login

About MyISAM

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

Copy after login

Adjust parameters to optimize mysql background service

MyISAM memory optimization

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

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

read_buffer 读缓存

write_buffer 写缓存

Copy after login

InnoDB memory optimization

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

innodb_old_blocks_pct LRU算法 决定old sublist的比例

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

Copy after login

mysql concurrency parameters

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

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

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

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

Copy after login

Mysql application optimization introduction

Why do we need to apply application optimization

The importance of data
• mysql service and its own performance bottleneck
• Ensure stable and reliable operation of large systems

Application optimization method

1. Use connection pool

2. Reduce the real connection to mysql

   a. Avoid Repeated execution of the same data (query cache)
   b. Use mysql cache (sql cache)
   

3. Load balancing

   a. LVS distributed
   b. Read and write separation (Master-master replication and master-slave replication ensure data consistency)
   

Database connection pool

Master-slave backup and read-write separation

Master-master backup

Load balancing

Related free learning recommendations: php programming (video)

The above is the detailed content of Notes on MySQL optimization. For more information, please follow other related articles on the PHP Chinese website!