PrinceLei' s Blog - mysql
type: channel
link: https://www.princelei.club/category/mysql
items: mysql的锁机制
items: mysql调优(六)
items: mysql调优(五)
items: mysql调优(四)
items: mysql调优(三)
items: mysql调优(二)
items: mysql调优(一)

mysql的锁机制
type: item
date: 2020-05-09T08:39:00+00:00
description: # mysql的锁机制 ## MySQL锁的基本介绍 **锁是计算机协调多个进程或线程并发访问某一资源的机制。**在数据库中，除传统的 计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一 个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。 相对其他数据库而言，MySQL的锁机制比较简单，其最 显著的特点是不同的**存储引擎**支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。 - **表级锁**：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 - **行级锁**：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。 ## MyISAM表锁 MySQL的表级锁有两种模式：**表共享读锁（Table Read Lock）**和**表独占写锁（Table Write Lock）**。 对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！ 建表语句： ```sql CREATE TABLE `mylock` ( `id` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `mylock` (`id`, `NAME`) VALUES ('1', 'a'); INSERT INTO `mylock` (`id`, `NAME`) VALUES ('2', 'b'); INSERT INTO `mylock` (`id`, `NAME`) VALUES ('3', 'c'); INSERT INTO `mylock` (`id`, `NAME`) VALUES ('4', 'd'); ``` ### MyISAM写锁阻塞读的案例 当一个线程获得对一个表的写锁之后，只有持有锁的线程可以对表进行更新操作。其他线程的读写操作都会等待，直到锁释放为止。 | **session1** | session2 | | :-: | :-: | | 获取表的write锁定`lock table mylock write;` | | | 当前session对表的查询，插入，更新操作都可以执行`select * from mylock;``insert into mylock values(5,'e');` | 当前session对表的查询会被阻塞`select * from mylock;` | | 释放锁：`unlock tables;` | 当前session能够立刻执行，并返回对应结果 | ### MyISAM读阻塞写的案例 一个session使用lock table给表加读锁，这个session可以锁定表中的记录，但更新和访问其他表都会提示错误，同时，另一个session可以查询表中的记录，但更新就会出现锁等待。 | **session1** | session2 | | :-: | :-: | | 获得表的read锁定`lock table mylock read;` | | | 当前session可以查询该表记录：`select * from mylock;` | 当前session可以查询该表记录：`select * from mylock;` | | 当前session不能查询没有锁定的表`select * from person`Table 'person' was not locked with LOCK TABLES | 当前session可以查询或者更新未锁定的表`select * from mylock``insert into person values(1,'zhangsan');` | |…

mysql调优(六)
type: item
date: 2020-05-08T10:08:00+00:00
description: # mysql调优(六) ## mysql日志 mysql日志一般分为三种，redo log、undo log、bin log，其中redo与undo归属于innodb存储引擎，bin log归属于mysql server。 事务具有4个特征ACID，A表示原子性，通过undo log实现，I为隔离性，通过锁来实现，D为持久性，通过redo log实现，A、I、D共同保证C，即一致性。 ![log buffer.png][1] ![redo commit.png][2] 当执行一个写操作，mysql会将记录放入log buffer中，log buffer再将其放入OS buffer，最后OS buffer通过fsync系统调用将其溢写到磁盘中。 该过程有三种实现方式(如图)，可以通过参数来配置,其中第2种最安全，但是效率低，建议使用第三种。 redo日志 - 当发生数据修改的时候，innodb引擎会先将记录写到redo log中，并更新内存，此时更新就算是完成了，同时innodb引擎会在合适的时机将记录操作到磁盘中 - redo log是固定大小的，是循环写的过程 - 有了redo log之后，innodb就可以保证即使数据库发生异常重启，之前的记录也不会丢失，这叫做crash-safe undo日志 - undo log是为了实现事务的原子性，在mysql数据库InnoDB存储引擎中，还用undo log来实现多版本并发控制(简称: MVCC) - 在操作任何数据之前，首先将数据备份到一个地方(这个存储数据备份的地方称为undo log)。然后进行数据修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用undo log中的备份将数据恢复到事务开始之前的状态 - 注意: undo log是逻辑日志，可以理解为: - 当delete一条记录时，undo log中会记录一条对应的insert记录 - 当insert一条记录时，undo log中会记录一条对应的delete记录 - 当update一条记录时，undo log中会记录一条对应的相反的update记录 bin log——服务端的日志文件 - bin log是server层的日志，主要做mysql功能层面的事情 - 与redo log的区别: 1. redo是innodb独有的，binlog是所有引擎都可以使用的 2. redo是物理日志，记录的是在某个数据页上做了什么修改，bin log是逻辑日志，记录的是这个语句的原始逻辑 3. redo是循环写的，空间会用完，bin log是可以追加写的，不会覆盖之前的日志信息 - bin log中会记录所有的逻辑，并且采用追加写的方式 - 一般在企业中数据库会有备份系统，可以定期执行备份，备份的周期可以自己设置 - 恢复数据的过程: 1. 找到最近一次的全量备份数据 2. 从备份的时间点开始，将备份的bin log取出来，重放到要恢复的那个时刻 数据更新的流程: 1. 执行器先从引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回 2. 执行器拿到数据之后会先修改数据，然后调用引擎接口重新写入数据 3. 引擎将数据更新到内存，同时写数据到redo中，此时处于prepare阶段，并通知执行器执行完成，随时可以操作 4. 执行器生成这个操作的bin log 5. 执行器调用引擎的事务提交接口，引擎把刚刚写完的redo改成commit状态，更新完成 redo log的两阶段提交 - 先写redo log后写bin log: 假设在redo log写完，bin log还没有写完的时候，mysql进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。但是由于bin log没写完就crash了，这时候bin log里面就没有记录这个语句。因此，之后备份日志的时候，存起来的bin log里面就没有这条语句。然后你会发现，如果需要用这个bin log来恢复临时库的话，恢复出来的这一行c的值就是0，与原库的值不同 - 先写bin log后写redo log: 如果在bin log写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0.但是bin log里面已经记录了“把c从0改成1”这个日志。所以，在之后用bin log来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同 因此，需要prepare和commit进行两阶段提交，确保redo log与bin log内容一致 ![redo_log两阶段提交.jpg][3] prepare: redolog写入log buffer，并fsync…

mysql调优(五)
type: item
date: 2020-05-06T12:11:00+00:00
description: # mysql调优(五) ## 分区表 ### 分区表的应用场景 - 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据 - 分区表的数据更容易维护 - 批量删除大量数据可以使用清除整个分区的方式 - 对一个独立分区进行优化、检查、修复等操作 - 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备 - 可以使用分区表来避免某些特殊的瓶颈 - innodb的单个索引的互斥访问 - ext3文件系统的inode锁竞争 - 可以备份和恢复独立的分区 ### 分区表的限制 - 一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区 - 在早期的mysql中，分区表达式必须是整数或者是返回整数的表达式，在mysql5.5中，某些场景可以直接使用列来进行分区 - 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来 - 分区表无法使用外键约束 ### 分区表的原理 分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。 分区表的操作按照以下的操作逻辑进行： **select查询** 当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据 **insert操作** 当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表 **delete操作** 当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作 **update操作** 当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对底层表进行写入操作，并对源数据所在的底层表进行删除操作 有些操作是支持过滤的，例如，当删除一条记录时，MySQL需要先找到这条记录，如果where条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对update同样有效。如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉。mysql先确定这条记录属于哪个分区，再将记录写入对应的分区表，无须对任何其他分区进行操作 虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，例如innodb，则会在分区层释放对应表锁。 ### 分区表的类型 #### 范围分区 根据列值在给定范围内将行分配给分区 范围分区表的分区方式是：每个分区都包含行数据且分区的表达式在给定的范围内，分区的范围应该是连续的且不能重叠，可以使用values less than运算符来定义。 1、创建普通的表 ``` CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ); ``` 2、创建带分区的表，下面建表的语句是按照store_id来进行分区的，指定了4个分区 ``` CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21) ); -…

mysql调优(四)
type: item
date: 2020-05-06T03:41:00+00:00
description: # mysql调优(四) ## 查询优化 在编写快速的查询之前，需要清楚一点，真正重要的是响应时间，而且要知道在整个SQL语句的执行过程中每个步骤都花费了多长时间，要知道哪些步骤是拖垮执行效率的关键步骤，想要做到这点，必须要知道查询的生命周期，然后进行优化，不同的应用场景有不同的优化方式，不要一概而论，具体情况具体分析。 ### 查询慢的原因 - 网络 - CPU - IO - 上下文切换 - 系统调用 - 生成统计信息 - 锁等待时间 ### 优化数据访问 查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化 - 确认应用程序是否在检索大量超过需要的数据(执行计划，rows) - 确认mysql服务器层是否在分析大量超过需要的数据行 是否向数据库请求了不需要的数据 **查询不需要的记录** 我们常常会误以为mysql会只返回需要的数据，实际上mysql却是先返回全部结果再进行计算，在日常的开发习惯中，经常是先用select语句查询大量的结果，然后获取前面的N行后关闭结果集。优化方式是在查询后面添加limit。 **多表关联时返回全部列** ``` select * from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title='Academy Dinosaur'; ``` 不要使用上面的这种`select *`的方式，要写出所需要的查询列。 **总是取出全部列** 如上，在查询的时候，尽量少使用`select *` **重复查询相同的数据** 如果需要不断的重复执行相同的查询，且每次返回完全相同的数据，因此，基于这样的应用场景，我们可以将这部分数据缓存起来，这样的话能够提高查询效率 ### 执行过程的优化 #### 查询缓存 在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据，如果查询恰好命中了查询缓存，那么会在返回结果之前会检查用户权限，如果权限没有问题，那么mysql会跳过所有的阶段，就直接从缓存中拿到结果并返回给客户端 #### 查询优化处理 1、语法解析器和预处理 mysql通过关键字将SQL语句进行解析，并生成一颗解析树，mysql解析器将使用mysql语法规则验证和解析查询，例如验证是否使用了错误的关键字或者顺序是否正确等等，预处理器会进一步检查解析树是否合法，例如表名和列名是否存在，是否有歧义，还会验证权限等等 2、查询优化器 当语法树没有问题之后，相应的要由优化器将其转成执行计划，一条查询语句可以使用非常多的执行方式，最后都可以得到对应的结果，但是不同的执行方式带来的效率是不同的，优化器的最主要目的就是要选择最有效的执行计划。 mysql使用的是基于成本的优化器，在优化的时候会尝试预测一个查询使用某种查询计划时候的成本，并选择其中成本最小的一个 ``` select count(*) from film_actor; show status like 'last_query_cost'; ``` 可以看到这条查询语句大概需要做1104个数据页才能找到对应的数据，这是经过一系列的统计信息计算来的,通过每个表或者索引的页面个数、索引的基数，索引和数据行的长度，索引的分布情况等参数计算。 在很多情况下mysql会选择错误的执行计划，原因如下： - 统计信息不准确 - InnoDB因为其mvcc的架构，并不能维护一个数据表的行数的精确统计信息 - 执行计划的成本估算不等同于实际执行的成本 - 有时候某个执行计划虽然需要读取更多的页面，但是他的成本却更小，因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很小，mysql层面并不知道哪些页面在内存中，哪些在磁盘，所以查询之际执行过程中到底需要多少次IO是无法得知的 - mysql的最优可能跟你想的不一样 - mysql的优化是基于成本模型的优化，但是有可能不是最快的优化 - mysql不考虑其他并发执行的查询 - mysql不会考虑不受其控制的操作成本 - 执行存储过程或者用户自定义函数的成本 优化器的优化策略 - 静态优化: 直接对解析树进行分析，并完成优化 - 动态优化: 动态优化与查询的上下文有关，也可能跟取值、索引对应的行数有关 - mysql对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重新评估 优化器的优化类型 - 重新定义关联表的顺序 - 数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序是优化器很重要的功能 - 将外连接转化成内连接，内连接的效率要高于外连接 -…

mysql调优(三)
type: item
date: 2020-04-27T13:00:00+00:00
description: # mysql调优(三) ## 通过索引进行优化 ### 索引的基本知识 索引的优点 1. 大大减少了服务器需要扫描的数据量 2. 帮助服务器避免排序和临时表 3. 将随机io变成顺序io 索引的用处 1. 快速查找匹配WHERE子句的行 2. 从consideration中消除行,如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引 3. 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行 4. 当有表连接的时候，从其他表检索行数据 5. 查找特定索引列的min或max值 6. 如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组 7. 在某些情况下，可以优化查询以检索值而无需查询数据行 索引的分类 - 主键索引 - 唯一索引 - 普通索引 - 全文索引 - 组合索引 索引采用的数据结构 大多数mysql的索引(主键、唯一、普通、全文)采用的是B+树，空间数据类型用的是R树，Memory存储引擎还支持哈希索引。 哈希表： 采用哈希表存储有如下缺点 1. 需要将所有的数据文件添加到内存，比较耗费内存空间 2. 如果所有的查询都是等值查询，那么哈希确实很快，但是在企业或者实际工作环境中，范围查找的数据更多，因此哈希就不合适了 B+数: 为什么要用B+树？因为无论二叉树还是红黑树，都会因为树的深度过深(每个节点至多有两个子节点)而造成io次数变多，影响数据读取的速率。因此，可以采用B树，即每个节点中可以包含多个key，与指向子节点的指针，可以拥有多个子节点，缺点是每个节点都有key，同时包含data，虽然查询时，命中当前的key就不需要继续向下遍历了，但是每个页存储空间有限，如果data比较大会造成每个节点存储key的数量变小，当存储量很大时，仍然会造成树的深度过深。所以，采用B+树，它与B树的区别是，普通节点不会存储data，所有data都存在叶子节点上，这样每个页存储的key数量增多，降低了树的高度，并将数据范围变为多个区间，区间越多，数据检索越快，理论上三层的B+树就可以存储千万级别的数据。叶子节点两两指针相互连接(符合磁盘的预读特性)，顺序查询性能更高。 ![Bplustrees.png][1] 在B+树上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点(即数据节点)之间是一种链式环结构，因此可以对B+树进行两种查找运算，一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。 ### 聚簇索引与非聚簇索引 聚簇索引 不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起 优点： 1. 可以把相关数据保存在一起 2. 数据访问更快，因为索引和数据保存在同一个树中 3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值 缺点: 1. 聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势 2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式 3. 更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置 4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题 5. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候 聚簇索引因为索引与数据在一个文件中，每次插入和删除操作都很能存在页分裂和页合并问题，即向某个页中插入新的key，超过了该页的存储上限时，这个页会分出一半数据,分裂成两个页。同样的，如果某两个页数据被删除，导致key减少，也会合并成一个页，造成io上的开销。 非聚簇索引 数据文件跟索引文件分开存放 InnoDB采用的是聚簇索引，默认对主键创建索引，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6位的row_id来作为主键,主键索引叶子节点存储的是该行的记录。如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的记录，叫做回表。 MyISAM采用的是非聚簇索引，叶子节点中保存的是该条记录的地址，然后再通过地址去数据文件中找到对应的记录。 ### 技术名词 - 回表 在普通列创建索引时，叶子节点存储的并不是整行数据，而是对应的主键，如果需要取出该行的其他字段，需要再次通过主键索引获取该条记录，这个过程叫做回表 - 覆盖索引 在普通列创建索引时，比如，为name字段添加了索引，通过`SELECT id,name FROM table_name`来查询，因为id和name都可以通过该索引直接拿到，也就是不需要回表了，回表过程没有了，就叫做覆盖索引 - 最左匹配 在组合索引中，比如，我为name和age创建组合索引，此时na…

mysql调优(二)
type: item
date: 2020-04-24T13:27:00+00:00
description: # mysql调优(二) ## schema与数据类型优化 ### 合理使用范式和反范式 范式 优点： - 范式化的更新通常比反范式要快 - 当数据较好的范式化后，很少或者没有重复的数据 - 范式化的数据比较小，可以放在内存中，操作比较快 缺点: - 通常需要进行关联 反范式 优点: - 所有的数据都在同一张表中，可以避免关联 - 可以设计有效的索引 缺点: - 表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失 在企业中很少能做到严格意义上的范式或者反范式，一般需要混合使用 - 在一个网站实例中，这个网站，允许用户发送消息，并且一些用户是付费用户。现在想查看付费用户最近的10条信息。 在user表和message表中都存储用户类型(account_type)而不用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的时候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大，有利于高效地获取数据。 - 另一个从父表冗余一些数据到子表的理由是排序的需要。 - 缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（类似论坛的），可以每次执行一个昂贵的自查询来计算并显示它；也可以在user表中建一个num_messages列，每当用户发新消息时更新这个值。 案例 ![范式设计1.png][1] ![反范式设计2.png][2] ### 主键的选择 - 代理主键: 与业务无关的，无意义的数字序列 - 自然主键: 事物属性中的自然唯一标识 推荐使用代理主键 - 它们不与业务耦合，因此更容易维护 - 一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本 ### 字符集的选择 字符集直接决定了数据在MySQL中的存储编码方式，由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。 1. 纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。 2. 如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。 3. MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。 ### 存储引擎的选择 ![存储引擎的对比.png][3] ### 适当的数据冗余 1. 被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。 2. 这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。 ### 适当拆分 当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。 ## mysql执行计划 在企业的应用场景中，为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。 官网地址： https://dev.mysql.com/doc/refman/5.5/en/explain-output.html ### 执行计划中包含的信息 :-: | :-: **Column** | **Meaning** id | The `SELECT` identifier select_type | The `SELECT` type table | The table for the output row partitions | The matching partitions type | The join type possible_keys | The possible indexes to choose key | The index actually chosen key_len | The length of the chosen key ref | The columns compared to the index rows | Estimate of …

mysql调优(一)
type: item
date: 2020-04-22T10:30:00+00:00
description: # mysql调优(一) ## mysql架构 ![mysql架构.jpg][1] ## 性能监控 ### show profile 使用show profile查询剖析工具，可以指定具体的type。此工具默认是禁用的，可以通过`set profiling=1;`命令开启，开启之后，服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。通常在mysql命令行模式下，只能显示两位小数的时间，开启show profile之后，可以通过`show profiles;`命令查看更具体的执行时间，`show profile for query 1;`可以查看详细的每个步骤的执行时间，`for query 1`为`show profiles;`中某条记录具体的ID，不加的话会默认显示最后一次执行的操作。 type - all: 显示所有性能信息`show profile all for query n` - block io: 显示io操作的次数`show profile block io for query n` - context switches: 显示上下文切换次数,被动和主动`show profile context switches for query n` - cpu: 显示用户cpu时间，系统cpu时间`show profile cpu for query n` - IPC: 显示发送和接收的消息数量`show profile ipc for query n` - Memory: 暂未实现 - page faults: 显示页错误数量`show profile page faults for query n` - source: 显示源码中的函数名称与位置`show profile source for query n` - swaps: 显示swap的次数`show profile swaps for query n` show profile使用起来简单方便，但mysql官方表示该功能可能会在未来的版本中被废弃，当然，低版本的使用不存在问题，mysql官方建议使用更强大的performance schema。 ### performance schema MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况，特点如下： 1. 提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息 2. performance_schema通过监视server的事件来实现监视server内部运行情况， “事件”就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里？一般来说，事件可以是函数调用、操作系统的等待、SQL语句执行的阶段（如sql语句执行过程中的parsing 或 sorting阶段）或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。 3. performance_schema中的事件与写入二进制日志中的事件（描述数据修改的events）、事件计划调度程序（这是一种存储程序）的事件不同。performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。 ​4. performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。 5. 当前活跃事件、历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数、使用时长。进而可用于分析某个特定线程、特定对象（如mutex或file）相关联的活动。 6. PERFORMANCE_SCHEMA存储引擎使用server源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同 7. 收集的事件数据存储在performance_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema…