导图社区 MySql性能优化知识结构详解
- 34
- 1
- 0
- 举报
MySql性能优化知识结构详解
MySql性能优化知识结构详解，包括系统级查询语句、测试mysql性能、MySql架构与存储引擎、业务设计等等。
编辑于2022-05-10 21:58:55- 代码
- MySql性…
- MySql性能
- 相似推荐
- 大纲
MySql性能优化知识结构详解
4.系统级查询语句
查询系统支持的存储引擎
SHOW ENGINES;
查询默认的存储引擎
SHOW VARIABLES LIKE storage_engine
最大连接数
show VARIABLES like max_connections
设置最大连接数
set GLOBAL max_connections = 200
是否开启缓存
SHOW VARIABLES LIKE query_cache_type
设置缓存大小
SET GLOBAL query_cache_size = 4000
查询是否开启innodb独立表空间
show VARIABLES like innodb_file_per_table
设置独立表空间开启关闭
set global innodb_file_per_table=off
查询innodb的redo日志缓存大小
show VARIABLES like innodb_log_buffer_size
查询innodb日志存放多少个
show VARIABLES like innodb_log_files_in_group
查询innodb日志每个大小最大多少
SHOW VARIABLES LIKE innodb_log_file_size
显示memory引擎的存储大小
show VARIABLES like max_heap_table_size
5.测试mysql性能
官方工具mysqlslap
测试内容
创建数据库、表、测试数据
运行负载测试，并发测试
测试环境清理，删除创建数据、表、断开连接等
测试参数
--create-schema=name
数据库名
--engine=name
引擎名，可指定多个值
--concurrency=N
模拟多个客户端执行，多个值逗号
--number-of-queries=N
测试查询次数，如果是100，客户端10，则每个10
--iterations=N
迭代执行测试次数
--commit=N
执行N条DML后提交一次
--auto-generate-sql
自动生成sql脚本测试
--atuo-generate-sql-load-type=name
测试sql的类型，read/write/key/update/mixed,默认混合
--auto-generate-sql-add-auto-increment
自增列
--number-int-cols=name
测试生成的列包含int类型的列的个数
--number-char-cols=name
测试生成的列包含char类型的列的个数
--debug-info
打印内存和CPU信息
1 MySql架构与存储引擎
Mysql逻辑架构介绍
Connectors连接层
连接处理、授权认证、安全等功能均在这一层处理。比如jdbc、.NET等
1.收到用户连接请求
2.授权认证
服务层
MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
1.查询缓存
* 缓存怎样存储的？ > MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。 * 使用缓存的过程？ > 在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。 * 缓存命中的条件？ > 1. 两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。 2. 如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。 * 既然是缓存，就会失效，那查询缓存何时失效呢？ > MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。 * 使用查询缓存一定好吗？ > 不一定，在使用缓存的情况下。任何写操作MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外： >> 1. 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存 2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗。 > 基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如： >> 1. 多个小表代替一个大表，注意不要过度设计 2. 批量插入代替循环单条插入 3. 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适 4. 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存。 > 最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。
2.解析sql
MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。
1.解析器
2.解析树
3.预处理
4.新解析树
3.查询优化器
优化后转换为执行计划，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。
比如优化where1=1，则优化为没有where查询
优化不为空的列查询，也没有where语句
4.执行计划
5.查询执行引擎
在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。
6.API接口查询
存储引擎
负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。
MyISAM 5.5前默认
* MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有： > 1. 大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持 2. 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成 3. 每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16 4. 最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上 5. BLOB和TEXT列可以被索引 6. NULL被允许在索引的列中，这个值占每个键的0~1个字节 7. 所有数字键值以高字节优先被存储以允许一个更高的索引压缩 8. 每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快 9. 可以把数据文件和索引文件放在不同目录 10. 每个字符列可以有不同的字符集 11. 有VARCHAR的表可以固定或动态记录长度 12. VARCHAR和CHAR列可以多达64KB 使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）
存储结构
1.frm文件，存储表结构
2.myd文件，存储表数据
3.myi文件，存储表索引
特性
并发性
表级别锁
全文检索
支持数据压缩
相关命令
数据压缩： .\myisampack.exe -b -f .MYI
数据修复 : .\myisamchk.exe -b -f .MYI
sql检查表：CHECK table product_info
sql修复表：REPAIR table product_info
适应场景
非事务应用（数据仓库，报表，日志）
只读应用
空间应用gis，空间函数，坐标等
Innodb 5.5后默认
* InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键,主要特性有: > 1. InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合 2. InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的 3. InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上 4. InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键 5. InnoDB被用在众多需要高性能的大型数据库站点上 InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件
存储结构
1.自动在mysqldata下创建ibdata1为10M的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件，系统表空间
2..frm文件，存储表结构
3. .ibd文件，存储索引及表数据，独立表空间
特性
有独立表空间和系统表空间的切换
系统表空间无法简单压缩，且会有IO瓶颈
独立表空间可以同时向多个表刷新数据，OPTIMIZE TABLE xx可以压缩数据
有事务
支持事务的ACID特性
Redolog 和 UndoLog
行级锁定，并发高
使用场景
适用于大多的OLTP应用
CSV
存储结构
1. .CSV文件存储内容
2. .CSM文件存储表的元数据，如表状态和数据量
3. frm表结构
特性
以CSV格式存储，所有列不能为null
不支持索引，不适合在线处理，适合存放数据
可以对数据文件直接编辑，执行完后flush tables;
Archive
存储结构
1. frm表结构
2. ARZ数据存储
特性
只支持插入和查询
只允许在自增上加索引
用zlib对表数据压缩，磁盘IO更少
使用场景
日志和数据采集应用
Memory
* MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有： > 1. MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度 2. MEMORY存储引擎执行HASH和BTREE缩影 3. 可以在一个MEMORY表中有非唯一键值 4. MEMORY表使用一个固定的记录长度格式 5. MEMORY不支持BLOB或TEXT列 6. MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引 7. MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表） 8. MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享 9. 当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）
存储结构
1. frm表结构
2,.数据在内存中
特性
最大大小由max_heap_table_size设置，默认16MB
表锁
不支持Blog和Text大字段
字段长度固定 varchar(10)=char(10)
支持HASH索引和Btree索引
也成HEAP存储引擎，存储在内存
使用场景
hash索引用于查找或者是映射表，邮编和地区的对应表
保存数据分析产生的中间表
缓存周期性的聚合数据的结果表
创建方式：create temporary table
Federated
存储结构
1. frm表结构
2.数据在远程服务器相同结构的表上
特性
提供了访问远程mysql的方法
本地不存储数据，数据都放在服务器上
本地存储表结构和远程服务器的链接信息
使用场景
偶尔的统计分析和手工查询
如何使用
默认禁止，开启需要输出
mysql://user_name[:password]@hostname[:port]/db_name/tablename
innodb和myisam比较
<table> <tr> <td> <b>对比项</b> </td> <td> <b>MyiSam</b> </td> <td> <b>InnoDb</b> </td> </tr> <tr> <td> 主外键 </td> <td> 不支持 </td> <td> 支持 </td> </tr> <tr> <td> 事务 </td> <td> 不支持 </td> <td> 支持 </td> </tr> <tr> <td> 行表索 </td> <td> 表索 </td> <td> 行锁 </td> </tr> <tr> <td> 缓存 </td> <td> 只缓存索引，不缓存数据 </td> <td> 都缓存，对内存要求高，内存大小对性能有影响 </td> </tr> <tr> <td> 表空间 </td> <td> 小 </td> <td> 大 </td> </tr> <tr> <td> 关注度 </td> <td> 性能 </td> <td> 事务 </td> </tr> </table>
存储层
就是计算机的磁盘
2.业务设计
锁
什么是锁
锁的种类
表锁
开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行锁
开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁
开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
存储引擎
InnoDB
行锁
行锁实现方式
> * InnoDB行锁是通过索引上的索引项来实现的，这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！
排它锁X（写锁）
允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。 另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
共享锁S（读锁）
允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
意向共享锁（IS）
事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁（IX）
事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
InnoDB行锁模式兼容性列表
<table style="width: 587px;" border="0" cellspacing="0" cellpadding="0"><colgroup><col width="161"><col width="120"><col width="115"><col width="101"><col width="90"></colgroup> <tbody> <tr> <td class="xl65" width="161" height="65">当前锁模式/是否兼容/请求锁模式</td> <td class="xl65" width="120">X</td> <td class="xl65" width="115">IX</td> <td class="xl65" width="101">S</td> <td class="xl65" width="90">IS</td> </tr> <tr> <td class="xl66" height="80">X</td> <td class="xl64" width="120">冲突</td> <td class="xl63">冲突</td> <td class="xl63">冲突</td> <td class="xl63">冲突</td> </tr> <tr> <td class="xl66" height="58">IX</td> <td class="xl64" width="120">冲突</td> <td class="xl63">兼容</td> <td class="xl63">冲突</td> <td class="xl63">兼容</td> </tr> <tr> <td class="xl65" width="161" height="65">S</td> <td class="xl64" width="120">冲突</td> <td class="xl63">冲突</td> <td class="xl63">兼容</td> <td class="xl63">兼容</td> </tr> <tr> <td class="xl65" width="161" height="75">IS</td> <td class="xl64" width="120">冲突</td> <td class="xl63">兼容</td> <td class="xl63">兼容</td> <td class="xl63">兼容</td> </tr> </tbody> </table> > * 如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。 * 意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及及数据集加排他锁（Ｘ）；对于普通SELECT语句，InnoDB会自动给涉及数据集加排他锁（Ｘ）；对于普通SELECT语句，InnoDB不会任何锁；事务可以通过以下语句显示给记录集加共享锁或排锁。 * 共享锁（Ｓ）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE * 排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE * 用SELECT .. IN SHARE MODE获得共享锁，主要用在需要数据依存关系时确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。 * 但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT ... FOR UPDATE方式获取排他锁。
间隙锁（Next-Key锁）
> * 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制不是所谓的间隙锁（Next-Key锁）。 * 举例来说，假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101，下面的SQL： * SELECT * FROM emp WHERE empid > 100 FOR UPDATE * 是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。 * InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况。 * 很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
表锁
使用表锁的情况
> 对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个另特殊事务中，也可以考虑使用表级锁。 * 事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。 * 事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。 > 当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ表。
表锁开启的条件
> * 使用LOCK TALBES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，ＭySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。 * 在用LOCAK TABLES对InnoDB锁时要注意，要将AUTOCOMMIT设为0，否则ＭySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK产不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句。
MyISAM
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
表共享锁（Table Read Lock）
* 当表为read lock的时候，只允许当前session操作读表，不允许操作其他表。 * 当为read loacal时，满足MyISAM表并发插入条件的情况下，允许其他用户在表尾插入记录。 其他session相应的也可以的读该表。 * 可以同时锁定多个表 * 执行代码如下： session1 ``` -- 锁表，读锁，开始 lock table my_myisam read; -- 尝试查询成功 select * from my_myisam; -- 尝试修改失败 update my_myisam set name = 1.1 where id = 1; -- 尝试删除失败 delete from my_myisam where id = 1; -- 尝试查询其他表失败 select * from my_innodb; -- 尝试修改其他表失败 update my_innodb set name = 1.1 where id = 1; ``` session2 ``` -- 查询被读锁，加锁的表成功 select * from my_myisam; -- 尝试更新阻塞，锁释放执行 update my_myisam set name = 1.1 where id = 1; -- 尝试删除阻塞，锁释放执行 delete from my_myisam where id = 1; ```
表独占写锁（Table Write Lock）
* 当表为write lock的时候，只允许当前session操作表增删改查，不允许操作其他表 其他session不能对此表做任何操作，会发生阻塞，等当前session释放了锁以后，才能继续执行。 * 可以同时锁定多个表。 执行代码如下: session1 ``` -- 锁表，写锁，开始 lock table my_myisam write; -- 尝试查询成功 select * from my_myisam; -- 尝试修改成功 update my_myisam set name = 1.1 where id = 1; -- 尝试查询其他表失败 select * from my_innodb; UNLOCK tables; ``` session2 ``` -- 查询被写锁，加锁的表，阻塞 select * from my_myisam; -- 尝试更新阻塞，锁释放执行 update my_myisam set name = 1.1 where id = 1; -- 尝试删除阻塞，锁释放执行 delete from my_myisam where id = 1; ```
并发锁
* 在一定条件下，MyISAM也支持查询和操作的并发进行。 * MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。 * 当concurrent_insert设置为0时，不允许并发插入。 * 当concurrent_insert设置为1时，如果MyISAM允许在一个读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾插入记录，都允许在表尾并发插入记录。 * 可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入锁争用。例如，将concurrent_insert系统变量为2，总是允许并发插入；同时，通过定期在系统空闲时段执行OPTIONMIZE TABLE语句来整理空间碎片，收到因删除记录而产生的中间空洞。
逻辑结构修改
锁表
解决办法
解决思路
pt-online-schema-change
事务
什么叫事务ACID
特性
原子性（Actomicity）
事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）
在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
隔离性（Isolation）
数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性（Durable）
事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
事务带来的问题
更新丢失（Lost Update）
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题
脏读（Dirty Reads）
一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
不可重复读（Non-Repeatable Reads）
一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。
幻读（Phantom Reads）
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
隔离性级别
<table style="width: 607px; height: 340px;" border="0" cellspacing="0" cellpadding="0"><colgroup><col width="331"><col width="201"><col width="155"><col width="146"><col width="72"></colgroup> <tbody> <tr> <td class="xl65" width="331" height="65">隔离级别/读数据一致性及允许的并发副作用</td> <td class="xl65" width="201">读数据一致性</td> <td class="xl65" width="155">脏读</td> <td class="xl65" width="146">不可重复读</td> <td class="xl65" width="72">幻读</td> </tr> <tr> <td class="xl63" height="80"> <div>未提交读（Read uncommitted）</div> </td> <td class="xl64" width="201">最低级别，只能保证不读取物理上损坏的数据</td> <td class="xl63">是</td> <td class="xl63">是</td> <td class="xl63">是</td> </tr> <tr> <td class="xl63" height="58">已提交度（Read committed）</td> <td class="xl63">语句级</td> <td class="xl63">否</td> <td class="xl63">是</td> <td class="xl63">是</td> </tr> <tr> <td class="xl63" height="65">可重复读（Repeatable read）</td> <td class="xl63">事务级</td> <td class="xl63">否</td> <td class="xl63">否</td> <td class="xl63">是</td> </tr> <tr> <td class="xl63" height="75">可序列化（Serializable）</td> <td class="xl63">最高级别，事务级</td> <td class="xl63">否</td> <td class="xl63">否</td> <td class="xl63">否</td> </tr> </tbody> </table>
未提交读
以提交读
可重复读
可串行化
逻辑设计
范式设计
反范式设计
物理设计
命名规范
存储引擎选择
数据类型选择
3.查询以及索引
慢查询
慢查询配置
slow_query_log
启动停止慢查询日志
slow_query_log_file
指定慢查询日志得存储路径及文件（默认和数据文件放一起）
long_query_time
指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
log_queries_not_using_indexes
是否记录未使用索引的SQL
log_output
日志存放的地方【TABLE】【FILE】【FILE,TABLE】
慢查询分析工具
mysqldumpslow自带
mysqldumpslow -s r -t 10 slow-mysql.log
pt-query-digest
perl .\pt-query-digest --explain h=127.0.0.1,u=root,p=root1234% D:\DESKTOP-2EKGEE5-slow.log
慢查询记录哪些sql
查询语句
数据修改语句
已经回滚得SQL
慢查询结果分析
#User@Host: root[root] @localhost [127.0.0.1] Id:10
用户名 、用户的IP信息、线程ID号
# Query_time:0.0005
执行花费的时间【单位：毫秒】
#Lock_Time:0.1212
执行获得锁的时间
#Rows_sent:2
获得的结果行数
#Rows_examined:2
扫描的数据行数
SET timestamp=1535462721
这SQL执行的具体时间
Select * from table
具体的SQL语句
索引
索引是什么
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。 可以得到索引的本质：索引是数据结构。
索引的作用
BTree与B+Tree
优缺点
分类
单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引
索引列的值必须唯一，但允许有空值
复合索引
即一个索引包含多个列
聚簇索引(聚集索引)
并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
非聚簇索引
不是聚簇索引，就是非聚簇索引
语法
创建
CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length)); ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
删除
DROP INDEX [indexName] ON mytable;
查看
SHOW INDEX FROM table_name\G
执行计划
什么是执行计划
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
具体作用
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
语法
字段详解
id
> 描述select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序 根据ID的数值结果可以分成一下三种情况 1. id相同：执行顺序由上至下 2. id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行 3. id相同不同：同时存在
select_type
* 查询的类型，用于区别普通查询、联合查询、子查询等的复杂查询，有以下几种类型： > 1. SIMPLE 简单的select查询，查询中不包含子查询或者union查询 2. PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为此 3. SUBQUERY 在SELECT或者WHERE列表中包含了子查询，那子查询标记为此 4. DERIVED 在FROM列表中包含的子查询被标记为DERIVERD(衍生)，MYSQL会递归执行这些子查询，把结果集放在临时表里。也就是查询的时候临时组合成的表，起别名使用继续查询 5. UNION 若第二个SELECT出现在UNION之后，则被标记为UNION,若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED 6. UNION RESULT 从UNION表获取结果的SELECT，也就是多个union后，从这些表中获取的数据。
table
显示这一行的数据是关于哪张表的
type
* type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是： > 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 一般来说，得保证查询至少达到range级别，最好能达到ref。
System
const类型的特殊情况
const
eq_ref
Ref
Range
Index
ALL
possible_keys
key
key_len
ref
rows
Extra
Sql优化
策略
尽量全值匹配
最佳左前缀法则
不在索引列上做任何操作
范围条件放最后
覆盖索引尽量用
不等于要甚用
Null/Not 有影响
Like查询要当心
字符类型加引号
OR改UNION效率高
总结
批量导入