MySQL性能优化

指定测试的数据库名称，默认是mysqlslap

创建测试表所使用的存储引擎，可指定多个，用“，”隔开

模拟N个客户端并发执行，可以分成多次，用“，”隔开

总的测试查询次数（并发客户端*每客户查询次数），比如并发是10，总次数是100，那么10个客户端各执行10个

迭代执行的次数，即重复的次数（相同的测试执行N次，求一个平均值），指的是整个步骤的重复次数，包括准备数据，测试load、清理

执行N条DML后提交一次

自动生成测试表和数据，表示用mysqlslap工具自己生成的sql脚本来测试并发压力

测试语句的类型，代表要测试的环境是读操作还是写操作还是两者混合的

取值包括read（scan tables），write（insert into tables），key（read primary keys），update（update primary keys）
或者是mixed（half inserts、half scanning selects），默认值是mixed

对生成的表自动添加到auto_increment列

自动生成的测试表中包含N个字符类型的列，默认1

自动生成的测试表中包含N个数字类型的列，默认1

打印内存和CPU信息

参数

show variables like '%query_cache_type%';

show variables like '%query_cache_size%';

SELECT DISTINCT
        < select_list >
FROM
        < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
        < where_condition >
GROUP BY
        < group_by_list >
HAVING
        < having_condition >
ORDER BY
        < order_by_condition >
LIMIT < limit_number >

MySQL查询解析顺序图

压缩数据

修复数据

show variables like 'innodb_file_per_table';

系统表空间 all in one 不利于管理

系统表空间会产生IO瓶颈

独立表空间可以同时向多个文件刷新数据

独立表空间可以通过optimize table回收存储空间

.CSV文件存储内容

.CSM文件存储表的元数据，如表状态和数据量

以csv格式进行数据存储

所有列都不能为null

不支持索引（不适合大表，不适合在线处理）

可以对数据文件直接编辑（保存文本文件内容）

以zlib对表数据进行压缩，磁盘IO更少

数据存储在ARZ为后缀的文件中

只支持insert和select操作

只允许在自增ID列上加索引

支持Hash索引和BTree索引

所有字段都是固定长度，即varchar(10)=char(10)

不支持Blog和Text等大字段

Memory存储引擎使用表级锁

设置max_heap_table_size控制内存表大小（字节）

设置tmp_table_size设置内存临时表最大值（字节）

提供了访问远程MySQL服务器上表的方法

本地不存储数据，数据全部放到远程服务器上

本地需要保存表结构和远程服务器的连接信息

偶尔的统计分析及手工查询

MySQL 数据库默认不开启Federated存储引擎

在配置文件中添加federated=1

共享锁（读锁）

排他锁（写锁）

MySQL不同的存储引擎支持不同的锁机制，所有的存储引擎都以自
己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现

默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令

不同粒度锁的比较

通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利

通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低

通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级

给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会

lock table 表名 read;

lock table 表名 as 别名 read;

unlock tables;

lock table 表名 write;

lock table 表名 as 别名 write;

unlock tables;

select * from 表名 where 条件 lock in share mode;

select * from 表名 where 条件 for update;

1. 下载安装perl环境

2. 下载percona-toolkit工具集合

3. ppm install DBI依赖

4. ppm install DBD::mysql 安装MySQL驱动依赖

pt-online-schema-change h=127.0.0.1,u=root,D=mysqldemo,t=product_info,--alter
"modify product_name varchar(150) not null default '' " --execute

会产生不可重复读

会产生不可重复读

不会产生不可重复读

不会产生不可重复读

存储空间：1字节

存储空间：2字节

存储空间：3字节

存储空间：4字节

存储空间：8字节

存储空间：4字节

存储空间：8字节

存储空间：每4个字节存9个数字，小数点占一个字节

VARCHAR变体长度，根据实际内容保存数据

1. 使用最小的符合需求的长度

2. VARCHAR(255)以下使用额外一个字节保存长度

3. VARCHAR(255)以上使用额外两个字节保存长度

4. VARCHAR(5)与VARCHAR(200)内存占用不同

5. VARCHAR变更长度时会出现锁表

1. VARCHAR适合存储长度波动大的数据，（如博客文章）

2. 字符串很少被更新的场景

3. VARCHAR适合保存多字节字符

CHAR属于定长数据

1. CHAR最大长度255

2. CHAR类型会自动删除末尾的空格

3. CHAR检索效率比VARCHAR高

1. CHAR适合存储长度波动不大的数据，如MD5摘要

2. CHAR适合存储短字符串

3. CHAR类型适合存储经常更新的字符串

汇总除查询条件外其他完全相同的sql，并将分析结果按照参数中所指定的顺序输出

mysqldumpslow -s r -t 10 slow-mysql.log

-s

-t

-g

pt_query_digest --explain h=127.0.0.1,u=root,p=password slow-mysql.log

--create-review-table

--create-history-table

--filter

--limit

--host

--user

--password

--history

--output

--since

--until

--explain

即一个索引只包含单个列，一个表可以有多个单列索引

索引列的值必须唯一，但允许有空值

即一个索引包含多个列

数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同，通俗来说，就是数据与索引存储在一起

数据库表行中数据的物理顺序与键值的逻辑（索引）顺序不同，通俗来说，就是数据与索引分开存储

id相同

id不同

id相同又不同（两种情况同时存在）

SIMPLE

PRIMARY

SUBQUERY

DERIVED

UNION

UNION RESULT

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

 system

const

eq_ref

ref

range

index

ALL

varchar(10)变长字段且允许NULL = 10 * (character set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchar(10)变长字段且不允许NULL = 10 *(character set:utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL = 10 * (character set:utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL = 10 * (character set:utf8=3,gbk=2,latin1=1)

变长字段需要额外2字节（varchar值保存时只保存需要的字符数，另加1个字节来记录长度，如果列声明的长度超过255，
则使用2个字节，所以varchar索引长度计算时要加2），固定长度字段不需要额外的字节

NULL需要1个字节的额外空间，所以索引字段最好不要为NULL，因为NULL让统计更加复杂并且需要额外的存储空间

符合索引有最左前缀（最左匹配）的特性，如果符合索引能全部使用上，则key_len大小为符合字段的索引长度之和，这也
可以用来判定符合索引是否部分使用，还是全部使用

Using filesort

Using temporary

Using index

Using where

Using join buffer

Impossible WHERE

select tables optimized away

distinct