tiankafei - java相关技术栈

# MySQL调优

命令行连接mysql命令：

# mysql -u用户名 -p密码 -h主机 -P端口
mysql -uroot -ptiankafei -hsoftware -P3306

# MySQL中innodb的主键

# 设计原则

  1. 一定要显式定义主键
  2. 采用与业务无关的单独列
  3. 采用自增列
  4. 数据类型采用int，并尽可能小，能用tinyint就不用int，能用int就不用bigint

# 这样设计的原因

  1. 在innodb引擎中只能有一个聚集索引，我们知道，聚集索引的叶子节点上直接存有行数据，所以聚集索引列尽量不要更改，而innodb表在有主键时会自动将主键设为聚集索引，如果不显式定义主键，会选第一个没有null值的唯一索引作为聚集索引，唯一索引涉及到的列内容难免被修改引发存储碎片且可能不是递增关系，存取效率低，所以最好显式定义主键且采用与业务无关的列以避免修改；如果这个条件也不符合，就会自动添加一个不可见不可引用的6byte大小的rowid作为聚集索引
  2. 需采用自增列来使数据顺序插入，新增数据顺序插入到当前索引的后面，符合叶子节点的分裂顺序，性能较高；若不用自增列，数据的插入近似于随机，插入时需要插入到现在索引页的某个中间位置，需要移动数据，造成大量的数据碎片，索引结构松散，性能很差
  3. 在主键插入时，会判断是否有重复值，所以尽量采用较小的数据类型，以减小比对长度提高性能，且可以减小存储需求，磁盘占用小，进而减少磁盘IO和内存占用；而且主键存储占用小，普通索引的占用也相应较小，减少占用，减少IO，且存储索引的页中能包含较多的数据，减少页的分裂，提高效率

# MySQL中各数据类型的取值范围

字段 最小值 最大值
tinyint -128 127
tinyint unsigned 0 255
smallint -32768 32,767
smallint unsigned 0 65,535
mediumint -8388608 8,388,607
mediumint unsigned 0 16,777,215
int -2147483648 2,147,483,647
int unsigned 0 4,294,967,295
bigint -9223372036854775808 9,223,372,036,854,775,807
bigint unsigned 0 18,446,744,073,709,551,615

# 优化种类

# RBO：基于规则的优化

# CBO：基于成本的优化

# 数据文件类型

# InnoDB

  • *.frm 当前存储表的表结构文件
  • *.ibd 当前存储表的数据文件

# MyISAM

  • *.frm 当前存储表的表结构文件
  • *.MYD 当前存储表的数据文件
  • *.MYI 当前存储表的索引文件

# 性能监控

# 使用show profile查询剖析工具，可以指定具体的type

-- 可以查看执行sql的Query_ID
show profiles;

  1. all：显示所有性能信息

    show profile all for query Query_ID

  2. block io：显示块io操作的次数

    show profile block io for query Query_ID

  3. context switches：显示上下文切换次数，被动和主动

    show profile context switches for query Query_ID

  4. cpu：显示用户cpu时间、系统cpu时间

    show profile cpu for query Query_ID

  5. IPC：显示发送和接受的消息数量

    show profile ipc for query Query_ID

  6. page faults：显示页错误数量

    show profile page faults for query Query_ID

  7. source：显示源码中的函数名称与位置

    show profile source for query Query_ID

  8. swaps：显示swap的次数

    show profile swaps for query Query_ID

# 使用performance schema来更加容易的监控mysql

# performance_schema的介绍

背景： MySQL 5.5开始新增一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。MySQL5.5默认是关闭的，需要手动开启，在配置文件里添加：

[mysqld]
performance_schema=ON

查看是否开启:

show variables like 'performance_schema';

从MySQL5.6开始，默认打开，本文就从MySQL5.6来说明，在数据库使用当中PERFORMANCE_SCHEMA的一些比较常用的功能。具体的信息可以查看官方文档 (opens new window)。

参考地址：https://www.cnblogs.com/zhoujinyi/p/5236705.html (opens new window)

特点如下：

  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数据库中的表记录（如动态修改performance_schema的setup_*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集）。
  8. performance_schema的表中的数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失（包括配置表在内的整个performance_schema下的所有数据）。
  9. MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所差异。

# performance schema入门

在mysql的5.7版本中，性能模式是默认开启的，如果想要显式的关闭的话需要修改配置文件，不能直接进行修改，会报错Variable 'performance_schema' is a read only variable。

  • instruments: 生产者，用于采集mysql中各种各样的操作产生的事件信息，对应配置表中的配置项我们可以称为监控采集配置项。
  • consumers:消费者，对应的消费者表用于存储来自instruments采集的数据，对应配置表中的配置项我们可以称为消费存储配置项。
--查看performance_schema的属性
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

--在配置文件中修改performance_schema的属性值，on表示开启，off表示关闭
[mysqld]
performance_schema=ON

--切换数据库
use performance_schema;

--查看当前数据库下的所有表,会看到有很多表存储着相关的信息
show tables;

--可以通过show create table tablename来查看创建表的时候的表结构
mysql> show create table setup_consumers;
+-----------------+---------------------------------
| Table           | Create Table                    
+-----------------+---------------------------------
| setup_consumers | CREATE TABLE `setup_consumers` (
  `NAME` varchar(64) NOT NULL,                      
  `ENABLED` enum('YES','NO') NOT NULL               
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |  
+-----------------+---------------------------------
1 row in set (0.00 sec)

# performance_schema表的分类

performance_schema库下的表可以按照监视不同的维度就行分组。

--语句事件记录表，这些表记录了语句事件信息，当前语句事件表events_statements_current、历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分)
show tables like '%statement%';

--等待事件记录表，与语句事件类型的相关记录表类似：
show tables like '%wait%';

--阶段事件记录表，记录语句执行的阶段事件的表
show tables like '%stage%';

--事务事件记录表，记录事务相关的事件的表
show tables like '%transaction%';

--监控文件系统层调用的表
show tables like '%file%';

--监视内存使用的表
show tables like '%memory%';

--动态对performance_schema进行配置的配置表
show tables like '%setup%';

# performance_schema的简单配置与使用

数据库刚刚初始化并启动时，并非所有instruments(事件采集项，在采集项的配置表中每一项都有一个开关字段，或为YES，或为NO)和consumers(与采集项类似，也有一个对应的事件类型保存表配置项，为YES就表示对应的表保存性能数据，为NO就表示对应的表不保存性能数据)都启用了，所以默认不会收集所有的事件，可能你需要检测的事件并没有打开，需要进行设置，可以使用如下两个语句打开对应的instruments和consumers（行计数可能会因MySQL版本而异)。

--打开等待事件的采集器配置项开关，需要修改setup_instruments配置表中对应的采集器配置项
UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'where name like 'wait%';

--打开等待事件的保存表配置开关，修改setup_consumers配置表中对应的配置项
UPDATE setup_consumers SET ENABLED = 'YES'where name like '%wait%';

--当配置完成之后可以查看当前server正在做什么，可以通过查询events_waits_current表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件
select * from events_waits_current\G
*************************** 1. row ***************************
            THREAD_ID: 11
             EVENT_ID: 570
         END_EVENT_ID: 570
           EVENT_NAME: wait/synch/mutex/innodb/buf_dblwr_mutex
               SOURCE: 
          TIMER_START: 4508505105239280
            TIMER_END: 4508505105270160
           TIMER_WAIT: 30880
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
           INDEX_NAME: NULL
          OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 67918392
     NESTING_EVENT_ID: NULL
   NESTING_EVENT_TYPE: NULL
            OPERATION: lock
      NUMBER_OF_BYTES: NULL
                FLAGS: NULL
/*该信息表示线程id为11的线程正在等待buf_dblwr_mutex锁，等待事件为30880
属性说明：
	id:事件来自哪个线程，事件编号是多少
	event_name:表示检测到的具体的内容
	source:表示这个检测代码在哪个源文件中以及行号
	timer_start:表示该事件的开始时间
	timer_end:表示该事件的结束时间
	timer_wait:表示该事件总的花费时间
注意：_current表中每个线程只保留一条记录，一旦线程完成工作，该表中不会再记录该线程的事件信息
*/

/*
_history表中记录每个线程应该执行完成的事件信息，但每个线程的事件信息只会记录10条，再多就会被覆盖，*_history_long表中记录所有线程的事件信息，但总记录数量是10000，超过就会被覆盖掉
*/
select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21;

/*
summary表提供所有事件的汇总信息，该组中的表以不同的方式汇总事件数据（如：按用户，按主机，按线程等等）。例如：要查看哪些instruments占用最多的时间，可以通过对events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列进行查询（这两列是对事件的记录数执行COUNT（*）、事件记录的TIMER_WAIT列执行SUM（TIMER_WAIT）统计而来）
*/
SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name  ORDER BY COUNT_STAR DESC LIMIT 10;

/*
instance表记录了哪些类型的对象会被检测。这些对象在被server使用时，在该表中将会产生一条事件记录，例如，file_instances表列出了文件I/O操作及其关联文件名
*/
select * from file_instances limit 20;

# 常用配置项的参数说明

  • 启动选项

    performance_schema_consumer_events_statements_current=TRUE
-- 是否在mysql server启动时就开启events_statements_current表的记录功能(该表记录当前的语句事件信息)，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新setup_consumers配置表中的events_statements_current配置项，默认值为TRUE

performance_schema_consumer_events_statements_history=TRUE
-- 与performance_schema_consumer_events_statements_current选项类似，但该选项是用于配置是否记录语句事件短历史信息，默认为TRUE

performance_schema_consumer_events_stages_history_long=FALSE
-- 与performance_schema_consumer_events_statements_current选项类似，但该选项是用于配置是否记录语句事件长历史信息，默认为FALSE

performance_schema_consumer_global_instrumentation=TRUE
-- 是否在MySQL Server启动时就开启全局表（如：mutex_instances、rwlock_instances、cond_instances、file_instances、users、hostsaccounts、socket_summary_by_event_name、file_summary_by_instance等大部分的全局对象计数统计和事件汇总统计信息表 ）的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新全局配置项：默认值为TRUE

performance_schema_consumer_statements_digest=TRUE
-- 是否在MySQL Server启动时就开启events_statements_summary_by_digest 表的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新digest配置项：默认值为TRUE

performance_schema_consumer_thread_instrumentation=TRUE
-- 是否在MySQL Server启动时就开启events_xxx_summary_by_yyy_by_event_name表的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新线程配置项：默认值为TRUE

performance_schema_instrument[=name]
-- 是否在MySQL Server启动时就启用某些采集器，由于instruments配置项多达数千个，所以该配置项支持key-value模式，还支持%号进行通配等，如下:
-- [=name]可以指定为具体的Instruments名称（但是这样如果有多个需要指定的时候，就需要使用该选项多次），也可以使用通配符，可以指定instruments相同的前缀+通配符，也可以使用%代表所有的instruments

-- 指定开启单个instruments
performance-schema-instrument= 'instrument_name=value'
-- 使用通配符指定开启多个instruments
performance-schema-instrument= 'wait/synch/cond/%=COUNTED'
-- 开关所有的instruments
performance-schema-instrument= '%=ON'
performance-schema-instrument= '%=OFF'
-- 注意，这些启动选项要生效的前提是，需要设置performance_schema=ON。另外，这些启动选项虽然无法使用show variables语句查看，但我们可以通过setup_instruments和setup_consumers表查询这些选项指定的值。

  • 系统变量

    show variables like '%performance_schema%';
--重要的属性解释
performance_schema=ON
/*
控制performance_schema功能的开关，要使用MySQL的performance_schema，需要在mysqld启动时启用，以启用事件收集功能
该参数在5.7.x之前支持performance_schema的版本中默认关闭，5.7.x版本开始默认开启
注意：如果mysqld在初始化performance_schema时发现无法分配任何相关的内部缓冲区，则performance_schema将自动禁用，并将performance_schema设置为OFF
*/

performance_schema_digests_size=10000
/*
控制events_statements_summary_by_digest表中的最大行数。如果产生的语句摘要信息超过此最大值，便无法继续存入该表，此时performance_schema会增加状态变量
*/
performance_schema_events_statements_history_long_size=10000
/*
控制events_statements_history_long表中的最大行数，该参数控制所有会话在events_statements_history_long表中能够存放的总事件记录数，超过这个限制之后，最早的记录将被覆盖
全局变量，只读变量，整型值，5.6.3版本引入 * 5.6.x版本中，5.6.5及其之前的版本默认为10000，5.6.6及其之后的版本默认值为-1，通常情况下，自动计算的值都是10000 * 5.7.x版本中，默认值为-1，通常情况下，自动计算的值都是10000
*/
performance_schema_events_statements_history_size=10
/*
控制events_statements_history表中单个线程（会话）的最大行数，该参数控制单个会话在events_statements_history表中能够存放的事件记录数，超过这个限制之后，单个会话最早的记录将被覆盖
全局变量，只读变量，整型值，5.6.3版本引入 * 5.6.x版本中，5.6.5及其之前的版本默认为10，5.6.6及其之后的版本默认值为-1，通常情况下，自动计算的值都是10 * 5.7.x版本中，默认值为-1，通常情况下，自动计算的值都是10
除了statement(语句)事件之外，wait(等待)事件、state(阶段)事件、transaction(事务)事件，他们与statement事件一样都有三个参数分别进行存储限制配置，有兴趣的同学自行研究，这里不再赘述
*/
performance_schema_max_digest_length=1024
/*
用于控制标准化形式的SQL语句文本在存入performance_schema时的限制长度，该变量与max_digest_length变量相关(max_digest_length变量含义请自行查阅相关资料)
全局变量，只读变量，默认值1024字节，整型值，取值范围0~1048576
*/
performance_schema_max_sql_text_length=1024
/*
控制存入events_statements_current，events_statements_history和events_statements_history_long语句事件表中的SQL_TEXT列的最大SQL长度字节数。 超出系统变量performance_schema_max_sql_text_length的部分将被丢弃，不会记录，一般情况下不需要调整该参数，除非被截断的部分与其他SQL比起来有很大差异
全局变量，只读变量，整型值，默认值为1024字节，取值范围为0~1048576，5.7.6版本引入
降低系统变量performance_schema_max_sql_text_length值可以减少内存使用，但如果汇总的SQL中，被截断部分有较大差异，会导致没有办法再对这些有较大差异的SQL进行区分。 增加该系统变量值会增加内存使用，但对于汇总SQL来讲可以更精准地区分不同的部分。
*/

# 重要配置表的相关说明

注意：在performance_schema库中还包含了很多其他的库和表，能对数据库的性能做完整的监控，大家需要参考官网详细了解。

/*
performance_timers表中记录了server中有哪些可用的事件计时器
字段解释：
	timer_name:表示可用计时器名称，CYCLE是基于CPU周期计数器的定时器
	timer_frequency:表示每秒钟对应的计时器单位的数量,CYCLE计时器的换算值与CPU的频率相关、
	timer_resolution:计时器精度值，表示在每个计时器被调用时额外增加的值
	timer_overhead:表示在使用定时器获取事件时开销的最小周期值
*/
select * from performance_timers;

/*
setup_timers表中记录当前使用的事件计时器信息
字段解释：
	name:计时器类型，对应某个事件类别
	timer_name:计时器类型名称
*/
select * from setup_timers;

/*
setup_consumers表中列出了consumers可配置列表项
字段解释：
	NAME：consumers配置名称
	ENABLED：consumers是否启用，有效值为YES或NO，此列可以使用UPDATE语句修改。
*/
select * from setup_consumers;

/*
setup_instruments 表列出了instruments 列表配置项，即代表了哪些事件支持被收集：
字段解释：
	NAME：instruments名称，instruments名称可能具有多个部分并形成层次结构
	ENABLED：instrumetns是否启用，有效值为YES或NO，此列可以使用UPDATE语句修改。如果设置为NO，则这个instruments不会被执行，不会产生任何的事件信息
	TIMED：instruments是否收集时间信息，有效值为YES或NO，此列可以使用UPDATE语句修改，如果设置为NO，则这个instruments不会收集时间信息
*/
SELECT * FROM setup_instruments;

/*
setup_actors表的初始内容是匹配任何用户和主机，因此对于所有前台线程，默认情况下启用监视和历史事件收集功能
字段解释：
	HOST：与grant语句类似的主机名，一个具体的字符串名字，或使用“％”表示“任何主机”
	USER：一个具体的字符串名称，或使用“％”表示“任何用户”
	ROLE：当前未使用，MySQL 8.0中才启用角色功能
	ENABLED：是否启用与HOST，USER，ROLE匹配的前台线程的监控功能，有效值为：YES或NO
	HISTORY：是否启用与HOST， USER，ROLE匹配的前台线程的历史事件记录功能，有效值为：YES或NO
*/
SELECT * FROM setup_actors;

/*
setup_objects表控制performance_schema是否监视特定对象。默认情况下，此表的最大行数为100行。
字段解释：
	OBJECT_TYPE：instruments类型，有效值为：“EVENT”（事件调度器事件）、“FUNCTION”（存储函数）、“PROCEDURE”（存储过程）、“TABLE”（基表）、“TRIGGER”（触发器），TABLE对象类型的配置会影响表I/O事件（wait/io/table/sql/handler instrument）和表锁事件（wait/lock/table/sql/handler instrument）的收集
	OBJECT_SCHEMA：某个监视类型对象涵盖的数据库名称，一个字符串名称，或“％”(表示“任何数据库”)
	OBJECT_NAME：某个监视类型对象涵盖的表名，一个字符串名称，或“％”(表示“任何数据库内的对象”)
	ENABLED：是否开启对某个类型对象的监视功能，有效值为：YES或NO。此列可以修改
	TIMED：是否开启对某个类型对象的时间收集功能，有效值为：YES或NO，此列可以修改
*/
SELECT * FROM setup_objects;

/*
threads表对于每个server线程生成一行包含线程相关的信息，
字段解释：
	THREAD_ID：线程的唯一标识符（ID）
	NAME：与server中的线程检测代码相关联的名称(注意，这里不是instruments名称)
	TYPE：线程类型，有效值为：FOREGROUND、BACKGROUND。分别表示前台线程和后台线程
	PROCESSLIST_ID：对应INFORMATION_SCHEMA.PROCESSLIST表中的ID列。
	PROCESSLIST_USER：与前台线程相关联的用户名，对于后台线程为NULL。
	PROCESSLIST_HOST：与前台线程关联的客户端的主机名，对于后台线程为NULL。
	PROCESSLIST_DB：线程的默认数据库，如果没有，则为NULL。
	PROCESSLIST_COMMAND：对于前台线程，该值代表着当前客户端正在执行的command类型，如果是sleep则表示当前会话处于空闲状态
	PROCESSLIST_TIME：当前线程已处于当前线程状态的持续时间（秒）
	PROCESSLIST_STATE：表示线程正在做什么事情。
	PROCESSLIST_INFO：线程正在执行的语句，如果没有执行任何语句，则为NULL。
	PARENT_THREAD_ID：如果这个线程是一个子线程（由另一个线程生成），那么该字段显示其父线程ID
	ROLE：暂未使用
	INSTRUMENTED：线程执行的事件是否被检测。有效值：YES、NO 
	HISTORY：是否记录线程的历史事件。有效值：YES、NO * 
	THREAD_OS_ID：由操作系统层定义的线程或任务标识符（ID）：
*/
select * from threads

# performance_schema实践操作

基本了解了表的相关信息之后，可以通过这些表进行实际的查询操作来进行实际的分析。

--1、哪类的SQL执行最多？
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--2、哪类SQL的平均响应时间最多？
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--3、哪类SQL排序记录数最多？
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--4、哪类SQL扫描记录数最多？
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--5、哪类SQL使用临时表最多？
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--6、哪类SQL返回结果集最多？
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--7、哪个表物理IO最多？
SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
--8、哪个表逻辑IO最多？
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
--9、哪个索引访问最多？
SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
--10、哪个索引从来没有用过？
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
--11、哪个等待事件消耗时间最多？
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
--12-1、剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
--12-2、查看每个阶段的时间消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
--12-3、查看每个阶段的锁等待情况
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;

# 使用 show processlist 查看连接的线程个数

可以观察是否有大量线程处于不正常的状态或者其他不正常的特征

show-processlist

属性说明：

  • id表示session id
  • user表示操作的用户
  • host表示操作的主机
  • db表示操作的数据库
  • command表示当前状态
    • sleep：线程正在等待客户端发送新的请求
    • query：线程正在执行查询或正在将结果发送给客户端
    • locked：在mysql的服务层，该线程正在等待表锁
    • analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
    • Copying to tmp table：线程正在执行查询，并且将其结果集都复制到一个临时表中
    • sorting result：线程正在对结果集进行排序
    • sending data：线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据
  • time表示相应命令执行时间
  • state表示命令执行状态
  • info表示详细的sql语句

# schema与数据类型优化

# 数据类型的优化

# 更小的通常更好

应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型 案例：设计两张表，设计不同的数据类型，查看表的容量

# 简单就好

简单数据类型的操作通常需要更少的CPU周期，例如， 1、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂， 2、使用mysql自建类型而不是字符串来存储日期和时间 3、用整型存储IP地址 案例：创建两张相同的表，改变日期的数据类型，查看SQL语句执行的速度

# 尽量避免null

如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂，坦白来说，通常情况下null的列改为not null带来的性能提升比较小，所有没有必要将所有的表的schema进行修改，但是应该尽量避免设计成可为null的列

# 实际细则

# 整数类型

可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。 尽量使用满足需求的最小数据类型

# 字符和字符串类型

1、char长度固定，即每条数据占用等长字节空间；最大长度是255个字符，适合用在身份证号、手机号等定长字符串 2、varchar可变程度，可以设置最大长度；最大空间是65535个字节，适合用在长度可变的属性 3、text不设置长度，当不知道属性的最大长度时，适合用text 按照查询速度：char>varchar>text

  • varchar根据实际内容长度保存数据
    1. 使用最小的符合需求的长度。
    2. varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。
    3. varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小 。
    4. varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时时，都会导致锁表。
    5. 应用场景
      • 存储长度波动较大的数据，如：文章，有的会很短有的会很长
      • 字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度
      • 适合保存多字节字符，如：汉字，特殊字符等
  • char固定长度的字符串
    1. 最大长度：255
    2. 会自动删除末尾的空格
    3. 检索效率、写效率 会比varchar高，以空间换时间
    4. 应用场景
      • 存储长度波动不大的数据，如：md5摘要
      • 存储短字符串、经常更新的字符串
# BLOB和TEXT类型

MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。 两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。

# datetime和timestamp

1、不要使用字符串类型来存储日期时间数据 2、日期时间类型通常比字符串占用的存储空间小 3、日期时间类型在进行查找过滤时可以利用日期来进行比对 4、日期时间类型还有着丰富的处理函数，可以方便的对时间类型进行日期计算 5、使用int存储日期时间不如使用timestamp类型

  1. datetime
    • 占用8个字节
    • 与时区无关，数据库底层时区配置，对datetime无效
    • 可保存到毫秒
    • 可保存时间范围大
    • 不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性
  2. timestamp
    • 占用4个字节
    • 时间范围：1970-01-01到2038-01-19
    • 精确到秒
    • 采用整形存储
    • 依赖数据库设置的时区
    • 自动更新timestamp列的值
  3. date
    • 占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节
    • 使用date类型还可以利用日期时间函数进行日期之间的计算
    • date类型用于保存1000-01-01到9999-12-31之间的日期
# 使用枚举代替字符串类型

有时可以使用枚举类代替常用的字符串类型，mysql存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或两个字节中，mysql在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的查找表 create table enum_test(e enum('fish','apple','dog') not null); insert into enum_test(e) values('fish'),('dog'),('apple'); select e+0 from enum_test;

# 特殊类型数据

人们经常使用varchar(15)来存储ip地址，然而，它的本质是32位无符号整数不是字符串，可以使用INET_ATON()和INET_NTOA函数在这两种表示方法之间转换 案例： select inet_aton('1.1.1.1') select inet_ntoa(16843009)

# 合理使用范式和反范式

# 范式

# 优点：
  • 范式化的更新通常比反范式要快
  • 当数据较好的范式化后，很少或者没有重复的数据
  • 范式化的数据比较小，可以放在内存中，操作比较快
# 缺点：
  • 通常需要进行关联

# 反范式

# 优点：
  • 所有的数据都在同一张表中，可以避免关联
  • 可以设计有效的索引；
# 缺点：
  • 表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失

# 需要注意的是：

在企业中很好能做到严格意义上的范式或者反范式，一般需要混合使用

  • 在一个网站实例中，这个网站，允许用户发送消息，并且一些用户是付费用户。现在想查看付费用户最近的10条信息。 在user表和message表中都存储用户类型(account_type)而不用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的时候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大，有利于高效地获取数据。
  • 另一个从父表冗余一些数据到子表的理由是排序的需要。
  • 缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（类似论坛的），可以每次执行一个昂贵的自查询来计算并显示它；也可以在user表中建一个num_messages列，每当用户发新消息时更新这个值。

# 主键的选择

# 代理主键

与业务无关的，无意义的数字序列

# 自然主键

事物属性中的自然唯一标识

# 推荐使用代理主键

  1. 它们不与业务耦合，因此更容易维护
  2. 一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本

# 字符集的选择

字符集直接决定了数据在MySQL中的存储编码方式，由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。

  1. 纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。
  2. 如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。
  3. MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。

# 存储引擎的选择

区别在于：数据文件的组织形式

聚簇索引：数据和索引存放在一起

非聚簇索引：数据和文件没有放在一起

MySQL引擎区别

# InnoDB

# MyISAM

# 适当的数据冗余

  1. 被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。
  2. 这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。

# 适当拆分

当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。

# 执行计划

在企业的应用场景中，为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。

官网地址： https://dev.mysql.com/doc/refman/5.5/en/explain-output.html (opens new window)

执行计划中包含的信息：

执行计划

属性解释：

# id

select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序

id号分为三种情况：

  1. 如果id相同，那么执行顺序从上到下

    explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;

  2. 如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

    explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');

  3. d相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行

    explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');

# select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

--sample:简单的查询，不包含子查询和union
explain select * from emp;

--primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--union:若第二个select出现在union之后，则被标记为union
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--dependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)

--union result:从union表获取结果的select
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--subquery:在select或者where列表中包含子查询
explain select * from emp where sal > (select avg(sal) from emp) ;

--dependent subquery:subquery的子查询要受到外部表查询的影响
explain select * from emp e where e.deptno in (select distinct deptno from dept);

--DERIVED: from子句中出现的子查询，也叫做派生类，
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存
 explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
 
--uncacheable union:表示union的查询结果不能被缓存：sql语句未验证

# table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集

  1. 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
  2. 表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
  3. 当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id

# partitions

使用的分区

# type

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：

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

一般情况下，得保证查询至少达到range级别，最好能达到ref

--all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
explain select * from emp;

--index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
explain  select empno from emp;

--range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
explain select * from emp where empno between 7000 and 7500;

--index_subquery：利用索引来关联子查询，不再扫描全表
explain select * from emp where emp.job in (select job from t_job);

--unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引
 explain select * from emp e where e.deptno in (select distinct deptno from dept);
 
--index_merge：在查询过程中需要多个索引组合使用，没有模拟出来

--ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式
explain select * from emp e where  e.mgr is null or e.mgr=7369;

--ref：使用了非唯一性索引进行数据的查找
 create index idx_3 on emp(deptno);
 explain select * from emp e,dept d where e.deptno =d.deptno;

--eq_ref ：使用唯一性索引进行数据查找
explain select * from emp,emp2 where emp.empno = emp2.empno;

--const：这个表至多有一个匹配行，
explain select * from emp where empno = 7369;
 
--system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

# possible_keys

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

# key

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

# key_len

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

# ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

# rows

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

explain select * from emp;

# extra

包含额外的信息。

--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
explain select * from emp order by sal;

--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;

--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;

--using where:使用where进行条件过滤
explain select * from t_user where id = 1;

--using join buffer:使用连接缓存，情况没有模拟出来

--impossible where：where语句的结果总是false
explain select * from emp where empno = 7469;

# 通过索引进行优化

# 索引基本知识

# 索引的优点

  1. 大大减少了服务器需要扫描的数据量，加快数据的检索速度
  2. 帮助服务器避免排序和临时表
  3. 索引可以将随机I/O变为顺序I/O

# 索引的用处

  1. 快速查找匹配WHERE子句的行
  2. 从consideration中消除行，如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引
  3. 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
  4. 当有表连接的时候，从其他表检索行数据
  5. 查找特定索引列的min或max值
  6. 如果排序或分组时在可用索引的最左前缀上完成的，再对表进行排序和分组
  7. 在某些情况下，可以优化查询以检索值而无需查询数据行

# 索引的分类

  1. 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引
  2. 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  3. 普通索引：最基本的索引，它没有任何限制，用于加速查询
  4. 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引配合match against操作使用，而不是一般的where语句加like。目前只有char、varchar，text 列上可以创建全文索引。MyISAM是全文索引，InnoDB5.6以后支持全文索引。
  5. 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

# 面试技术名词

# 回表

先根据普通索引定位主键值（普通索引的B+树叶子节点存储的主键而不是数据行记录），然后再根据主键值定位行记录，需要遍历两颗B+树。

# 覆盖索引

索引覆盖是一种避免回表查询的优化策略。具体的做法就是将要查询的数据作为索引列建立普通索引（可以是单列索引，也可以是联合索引），这样的话就可以直接返回索引中的的数据，不需要再通过主键索引去定位行记录，避免了回表的情况发生。

# 最左匹配

按照索引的字段顺序，先匹配左边的字段

# 索引下推

索引下推简而言之就是在复合索引由于某些条件(比如 like %aa)失效的情况下，当存在失效的过滤字段在索引覆盖范围内，使用比较的方式在不回表的情况下进一步缩小查询的范围。其实就是对索引失效的进一步修复，属于最左前缀索引原则的一个意外情况。

索引下推触发的条件

  1. 查询条件是复合索引
  2. 失效条件的字段在索引覆盖的范围内
  3. 失效条件是可以通过数据进行比较的简单对比

索引下推可以使用的场景：

  1. like查询
  2. 函数在左的查询,比如CHAR_LENGTH(colName)=5

# 索引采用的数据结构

  1. 哈希表（存储引擎是Memory支持）
  2. B+树（存储引擎是InnoDB和MyISAM支持）

# 索引匹配方式

# 全值匹配

全值匹配指的是和索引中的所有列进行匹配

explain select * from staffs where name = 'July' and age = '23' and pos = 'dev';
# 匹配最左前缀

只匹配前面的几列

explain select * from staffs where name = 'July' and age = '23';
explain select * from staffs where name = 'July';
# 匹配列前缀

可以匹配某一列的值的开头部分

-- 正确
explain select * from staffs where name like 'J%';
-- 错误
explain select * from staffs where name like '%y';
# 匹配范围值

可以查找某一个范围的数据

explain select * from staffs where name > 'Mary';
# 精确匹配某一列并范围匹配另外一列

可以查询第一列的全部和第二列的部分

explain select * from staffs where name = 'July' and age > 25;
# 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';

# 哈希索引

  1. 基于哈希表的实现，只有精确匹配索引所有列的查询才有效

  2. 在mysql中，只有memory的存储引擎显式支持哈希索引

  3. 哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快

  4. 哈希索引的限制

    • 哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行
    • 哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
    • 哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值
    • 哈希索引支持等值比较查询，也不支持任何范围查询
    • 访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行
    • 哈希冲突比较多的话，维护的代价也会很高

  5. 案例

    当需要存储大量的URL，并且根据URL进行搜索查找，如果使用B+树，存储的内容就会很大 select id from url where url="" 也可以利用将url使用CRC32做哈希，可以使用以下查询方式： select id fom url where url="" and url_crc=CRC32("") 此查询性能较高原因是使用体积很小的索引来完成查找

# 组合索引

当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要

# 聚簇索引与非聚簇索引

# 聚簇索引

不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起

# 优点
  1. 可以把相关数据保存在一起
  2. 数据访问更快，因为索引和数据保存在同一个树中
  3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
# 缺点
  1. 聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
  2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
  3. 更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
  4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
  5. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

# 非聚簇索引

数据文件跟索引文件分开存放

# 覆盖索引

# 基本介绍

  1. 如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引
  2. 不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值
  3. 不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引

# 优势

  1. 索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql就会极大的较少数据访问量
  2. 因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多
  3. 一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题
  4. 由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用

# 案例演示

  1. 当发起一个被索引覆盖的查询时，在explain的extra列可以看到using index的信息，此时就使用了覆盖索引

    mysql> explain select store_id,film_id from inventory\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_store_id_film_id
      key_len: 3
          ref: NULL
         rows: 4581
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

  2. 在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过，可以进一步的进行优化，可以使用innodb的二级索引来覆盖查询。

    二级索引又称辅助索引、非聚集索引(no-clustered index)。b＋tree树结构。然而二级索引的叶子节点不保存记录中的所有列，其叶子节点保存的是<健值，(记录)地址>。好似聚集索引中非叶子节点保存的信息，不同的是二级索引保存的是记录地址，而聚集索引保存的是下一层节点地址。记录的地址一般可以保存两种形式。

    1. 记录的物理地址，页号：槽号：偏移量
    2. 记录的主键值

    例如：actor使用innodb存储引擎，并在last_name字段又二级索引，虽然该索引的列不包括主键actor_id，但也能够用于对actor_id做覆盖查询

    mysql> explain select actor_id,last_name from actor where last_name='HOPPER'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

    innodb读操作：

    通过二级索引查询记录仅能得到主键值，要查询完整的记录还需要再通过一次聚集索引查询,这种查询方式为书签查找（bookmark lookup）。

    innodb写操作：

    仅当主键发生改变时，才会更新二级索引。

    二级索引的非叶子节点存放的记录格式为<键值，主键值，地址>，二级索引的非叶子节点依然存在主键信息。二级索引节点的记录不保存隐藏列xid和roll ptr。聚集索引的非叶子节点保存的是下一层节点地址。 由于二级索引不包含记录的完整信息，在innodb存储引擎中二级索引的树高度比聚簇索引的树高度小，二级索引效率低。

# 优化小细节

# 当使用索引列进行查询时尽量不要使用表达式

-- 把计算放到业务层而不是数据库层
-- 正确
select actor_id from actor where actor_id=4;
-- 错误
select actor_id from actor where actor_id+1=5;

# 尽量使用主键查询，而不是其他索引

因为主键查询不会触发回表查询

# 使用前缀索引

​ 有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

​ 一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

--创建数据表
create table citydemo(city varchar(50) not null);
insert into citydemo(city) select city from city;

--重复执行5次下面的sql语句
insert into citydemo(city) select city from citydemo;

--更新城市表的名称
update citydemo set city=(select city from city order by rand() limit 1);

--查找最常见的城市列表，发现每个值都出现45-65次，
select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;

--查找最频繁出现的城市前缀，先从3个前缀字母开始，发现比原来出现的次数更多，可以分别截取多个字符查看城市出现的次数
select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;
select count(*) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;
--此时前缀的选择性接近于完整列的选择性

--还可以通过另外一种方式来计算完整列的选择性，可以看到当前缀长度到达7之后，再增加前缀长度，选择性提升的幅度已经很小了
select count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4,
count(distinct left(city,5))/count(*) as sel5,
count(distinct left(city,6))/count(*) as sel6,
count(distinct left(city,7))/count(*) as sel7,
count(distinct left(city,8))/count(*) as sel8 
from citydemo;

--计算完成之后可以创建前缀索引
alter table citydemo add key(city(7));

--注意：前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和 group by。

# 使用索引扫描来排序

mysql有两种方式可以生成有序的结果：通过排序操作或者按照索引顺序扫描，如果explain出来的type列的值为index，则说明mysql使用了索引扫描来做的排序。

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机IO，因此按照索引顺序读取数据的速度通常要比顺序地全表扫描要慢。

mysql可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索引时应该尽可能地同时满足这两种要求。

只有当索引的列顺序和order by 子句的顺序完全一致，并且所有列的排序方式都一样时，mysql才能使用索引来对结果进行排序，如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一张表时，才能使用索引做排序。order by 子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql都需要执行顺序操作，而无法利用索引排序。

当Extra中有Using filesort时说明没有使用索引排序（使用了文件排序），当没有这个时说明使用了索引排序

  1. order by 子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为索引的第一列被指定为一个常数

    使用索引排序01

  2. 该查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两个列组合在一起，就形成了索引的最左前缀

    使用索引排序01

  3. 这个查询不用利用索引（当索引中第一个字段是范围查询找时，后面的字段不会用到索引）

    使用索引排序03

  4. 该查询使用了两个不同的排序方向，但索引列都是正序排序的

    使用索引排序04

    使用索引排序05

  5. 查询使用两个相同的排序方向，要么全是正序，要么全是倒序（因为索引是有序的）

    使用索引排序06

    使用索引排序07

  6. 当排序字段中有不包含索引的字段，则不会使用索引排序

    使用索引排序08

    使用索引排序09

# union all,in,or都能够使用索引，但是推荐使用in

union-in-or执行计划

explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id =2;

# 范围列可以用到索引

  1. 范围条件是：<、<=、>、>=、between
  2. 范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列

# 强制类型转换会全表扫描

create table user(id int,name varchar(10),phone varchar(11));

alter table user add index idx_1(phone);

-- 不会触发索引
explain select * from user where phone=13800001234;
-- 触发索引
explain select * from user where phone='13800001234';

# 更新频繁，数据区分度不高的字段不宜建立索引

  1. 更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能
  2. 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据，
  3. 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算
  4. 索引页分裂、索引页合并

# 创建索引的列，不允许为null

创建索引的列，不允许为null，可能会得到不符合预期的结果

# 表JOIN最好不要超过三张表

当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致

join实现的三种方式：

# 完全匹配方式嵌套循环

join-简单嵌套循环

# 索引嵌套循环

join-索引嵌套循环

# 内存嵌套循环

内存大小限制参数：join_buffer_size=265K

join-内存嵌套循环

# 能使用limit的时候尽量使用limit

根据主键或者唯一索引查询数据时，可以不用加limit 1；使用其他条件时，如果明确知道只有1条记录时，可以加上limit 1来提高查询效率。

# 单表索引的个数建议控制在5个以内

# 单个索引字段数不允许超过5个（组合索引）

# 创建索引的时候应该避免以下错误概念

  1. 索引越多越好
  2. 过早优化，在不了解系统的情况下进行优化

# 索引监控

show status like 'Handler_read%';

索引监控

参数解释

  1. Handler_read_first：读取索引第一个条目的次数
  2. Handler_read_key：通过index获取数据的次数
  3. Handler_read_last：读取索引最后一个条目的次数
  4. Handler_read_next：通过索引读取下一条数据的次数
  5. Handler_read_prev：通过索引读取上一条数据的次数
  6. Handler_read_rnd：从固定位置读取数据的次数
  7. Handler_read_rnd_next：从数据节点读取下一条数据的次数

# 简单案例

# 预先准备好数据

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `itdragon_order_list`;
CREATE TABLE `itdragon_order_list` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id，默认自增长',
  `transaction_id` varchar(150) DEFAULT NULL COMMENT '交易号',
  `gross` double DEFAULT NULL COMMENT '毛收入(RMB)',
  `net` double DEFAULT NULL COMMENT '净收入(RMB)',
  `stock_id` int(11) DEFAULT NULL COMMENT '发货仓库',
  `order_status` int(11) DEFAULT NULL COMMENT '订单状态',
  `descript` varchar(255) DEFAULT NULL COMMENT '客服备注',
  `finance_descript` varchar(255) DEFAULT NULL COMMENT '财务备注',
  `create_type` varchar(100) DEFAULT NULL COMMENT '创建类型',
  `order_level` int(11) DEFAULT NULL COMMENT '订单级别',
  `input_user` varchar(20) DEFAULT NULL COMMENT '录入人',
  `input_date` varchar(20) DEFAULT NULL COMMENT '录入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;

INSERT INTO itdragon_order_list VALUES ('10000', '81X97310V32236260E', '6.6', '6.13', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-28 17:01:49');
INSERT INTO itdragon_order_list VALUES ('10001', '61525478BB371361Q', '18.88', '18.79', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-18 17:01:50');
INSERT INTO itdragon_order_list VALUES ('10002', '5RT64180WE555861V', '20.18', '20.17', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-09-08 17:01:49');

# 第一个案例：

select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
--通过查看执行计划发现type=all,需要进行全表扫描
explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

--优化一、为transaction_id创建唯一索引
 create unique index idx_order_transaID on itdragon_order_list (transaction_id);
--当创建索引之后，唯一索引对应的type是const，通过索引一次就可以找到结果，普通索引对应的type是ref，表示非唯一性索引赛秒，找到值还要进行扫描，直到将索引文件扫描完为止，显而易见，const的性能要高于ref
 explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 
 --优化二、使用覆盖索引，查询的结果变成 transaction_id,当extra出现using index,表示使用了覆盖索引
 explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";

# 第二个案例：

--创建复合索引
create index idx_order_levelDate on itdragon_order_list (order_level,input_date);

--创建索引之后发现跟没有创建索引一样，都是全表扫描，都是文件排序
explain select * from itdragon_order_list order by order_level,input_date;

--可以使用force index强制指定索引
explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
--其实给订单排序意义不大，给订单级别添加索引意义也不大，因此可以先确定order_level的值，然后再给input_date排序
explain select * from itdragon_order_list where order_level=3 order by input_date;

# 查询优化

# 查询慢的原因

  1. 网络
  2. CPU
  3. IO
  4. 内存不足
  5. 上下文切换
  6. 系统调用
  7. 生成统计信息
  8. 锁等待时间

# 优化数据访问

# 访问的数据太多

查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化（分页，一次只查询部分数据）

  1. 确认应用程序是否在检索大量超过需要的数据

  2. 确认mysql服务器层是否在分析大量超过需要的数据行

    查询不必要的数据-sql优化01

大数据量的索引排序问题：

  1. 当查询的结果集数据量比较大的时候，可能不会使用索引来排序（也不是绝对）

    索引出大量数据不能按照索引排序的问题

  2. 当查询结果集数据量比较的时候，确使用了索引排序

    索引出大量数据按照索引排序的问题

# 是否向数据库请求了不需要的数据

# 查询不需要的记录

我们常常会误以为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 actor.* from actor...;

多表关联时，建议给表增加表别名

# 总是取出全部列

在公司的企业需求中，禁止使用select *,虽然这种方式能够简化开发，但是会影响查询的性能，所以尽量不要使用

# 重复查询相同的数据

如果需要不断的重复执行相同的查询，且每次返回完全相同的数据，因此，基于这样的应用场景，我们可以将这部分数据缓存起来，这样的话能够提高查询效率

# 执行过程的优化

# 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据，如果查询恰好命中了查询缓存，那么会在返回结果之前会检查用户权限，如果权限没有问题，那么mysql会跳过所有的阶段，就直接从缓存中拿到结果并返回给客户端。

查询缓存在MySQL8中移除了，因为当数据频繁发生变化时，就需要频繁的往缓存中插入和更新；当内存满了之后，还需要内存淘汰策略（LRU，LFU）

# 查询优化处理

mysql查询完缓存之后会经过以下几个步骤：解析SQL、预处理、优化SQL执行计划，这几个步骤出现任何的错误，都可能会终止查询

# 语法解析器和预处理

mysql通过关键字将SQL语句进行解析，并生成一颗解析树，mysql解析器将使用mysql语法规则验证和解析查询，例如验证使用使用了错误的关键字或者顺序是否正确等等，预处理器会进一步检查解析树是否合法，例如表名和列名是否存在，是否有歧义，还会验证权限等等

# 查询优化器

当语法树没有问题之后，相应的要由优化器将其转成执行计划，一条查询语句可以使用非常多的执行方式，最后都可以得到对应的结果，但是不同的执行方式带来的效率是不同的，优化器的最主要目的就是要选择最有效的执行计划

mysql使用的是基于成本的优化器，在优化的时候会尝试预测一个查询使用某种查询计划时候的成本，并选择其中成本最小的一个

# 查询sql分析
select count(*) from film_actor;
show status like 'last_query_cost';

可以看到这条查询语句大概需要做1104个数据页才能找到对应的数据，这是经过一系列的统计信息计算来的

  1. 每个表或者索引的页面个数
  2. 索引的基数
  3. 索引和数据行的长度
  4. 索引的分布情况
# mysql可能会选择错误的执行计划

在很多情况下mysql会选择错误的执行计划，原因如下：

  1. 统计信息不准确：InnoDB因为其mvcc的架构，并不能维护一个数据表的行数的精确统计信息
  2. 执行计划的成本估算不等同于实际执行的成本：有时候某个执行计划虽然需要读取更多的页面，但是他的成本却更小，因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很小，mysql层面并不知道哪些页面在内存中，哪些在磁盘，所以查询之际执行过程中到底需要多少次IO是无法得知的
  3. mysql的最优可能跟你想的不一样：mysql的优化是基于成本模型的优化，但是有可能不是最快的优化
  4. mysql不考虑其他并发执行的查询
  5. mysql不会考虑不受其控制的操作成本：执行存储过程或者用户自定义函数的成本
# 优化器的优化策略
  1. 静态优化：直接对解析树进行分析，并完成优化
  2. 动态优化：动态优化与查询的上下文有关，也可能跟取值、索引对应的行数有关
  3. mysql对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重新评估
# 优化器的优化类型

  1. 重新定义关联表的顺序：数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序时优化器很重要的功能

  2. 将外连接转化成内连接，内连接的效率要高于外连接

  3. 使用等价变换规则，mysql可以使用一些等价变化来简化并规划表达式

  4. 优化count(),min(),max()：索引和列是否可以为空通常可以帮助mysql优化这类表达式：例如，要找到某一列的最小值，只需要查询索引的最左端的记录即可，不需要全文扫描比较

  5. 预估并转化为常数表达式，当mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行处理

    explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1

  6. 索引覆盖扫描，当索引中的列包含所有查询中需要使用的列的时候，可以使用覆盖索引

  7. 子查询优化：mysql在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问，例如将经常查询的数据放入到缓存中

  8. 等值传播

    -- 如果两个列的值通过等式关联，那么mysql能够把其中一个列的where条件传递到另一个上：
explain select film.film_id from film inner join film_actor using(film_id
) where film.film_id > 500;
-- 这里使用film_id字段进行等值关联，film_id这个列不仅适用于film表而且适用于film_actor表
explain select film.film_id from film inner join film_actor using(film_id
) where film.film_id > 500 and film_actor.film_id > 500;
# 关联查询

  1. join的实现方式原理

    • Simple Nested-Loop Join

    • Index Nested-Loop Join

    • Block Nested-Loop Join

      -- （1）Join Buffer会缓存所有参与查询的列而不是只有Join的列。
-- （2）可以通过调整join_buffer_size缓存大小
-- （3）join_buffer_size的默认值是256K，join_buffer_size的最大值在MySQL 5.1.22版本前是4G-1，而之后的版本才能在64位操作系统下申请大于4G的Join Buffer空间。
-- （4）使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。

show variables like '%optimizer_switch%'

  2. 案例演示

    -- 查看不同的顺序执行方式对查询性能的影响：
explain select film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join f
ilm_actor using(film_id) inner join actor using(actor_id);
-- 查看执行的成本：
show status like 'last_query_cost'; 
-- 按照自己预想的规定顺序执行：
explain select straight_join film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from fil
m inner join film_actor using(film_id) inner join actor using(actor_id);
-- 查看执行的成本：
show status like 'last_query_cost';
# 排序算法优化

  1. 两次传输排序：

    第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排好序的结果按照需要去读取数据行。
这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高
两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作

  2. 单次传输排序

    先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据

  3. 当需要排序的列的总大小加上orderby的列大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式

# 优化特定类型的查询

# 优化count()查询

  1. 总有人认为myisam的count函数比较快，这是有前提条件的，只有没有任何where条件的count(*)才是比较快的
  2. 使用近似值：在某些应用场景中，不需要完全精确的值，可以参考使用近似值来代替，比如可以使用explain来获取近似的值。其实在很多OLAP的应用中，需要计算某一个列值的基数，有一个计算近似值的算法叫hyperloglog。
  3. 更复杂的优化：一般情况下，count()需要扫描大量的行才能获取精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统。

# 优化关联查询

  1. 确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序
  2. 确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程

# 优化子查询

子查询的优化最重要的优化建议是尽可能使用关联查询代替

# 优化limit分页

优化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列

-- 优化前
explain select film_id,description from film order by title limit 50,5
-- 优化后
explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);

# 优化union查询

除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高

# 推荐使用用户自定义变量

# 自定义变量的使用
  1. set @one :=1
  2. set @min_actor :=(select min(actor_id) from actor)
  3. set @last_week :=current_date-interval 1 week;
# 自定义变量的限制
  1. 无法使用查询缓存
  2. 不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名或者limit子句
  3. 用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信
  4. 不能显式地声明自定义变量地类型
  5. mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想地方式运行
  6. 赋值符号：=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号
  7. 使用未定义变量不会产生任何语法错误
# 自定义变量的使用案例

  1. 优化排名语句

    • 在给一个变量赋值的同时使用这个变量

      select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;

    • 查询获取演过最多电影的前10名演员，然后根据出演电影次数做一个排名

      select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10;

  2. 避免重新查询刚刚更新的数据：当需要高效的更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么

    update t1 set lastUpdated=now() where id =1;
select lastUpdated from t1 where id =1;

update t1 set lastupdated = now() where id = 1 and @now:=now();
select @now;

  3. 确定取值的顺序：在赋值和读取变量的时候可能是在查询的不同阶段

    set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;
-- 因为where和select在查询的不同阶段执行，所以看到查询到两条记录，这不符合预期

set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name
-- 当引入了orde;r by之后，发现打印出了全部结果，这是因为order by引入了文件排序，而where条件是在文件排序操作之前取值的

-- 解决这个问题的关键在于让变量的赋值和取值发生在执行查询的同一阶段：
set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;

# 分区表

# 分区表的应用场景

  1. 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
  2. 分区表的数据更容易维护
    • 批量删除大量数据可以使用清除整个分区的方式
    • 对一个独立分区进行优化、检查、修复等操作
  3. 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
  4. 可以使用分区表来避免某些特殊的瓶颈
    • innodb的单个索引的互斥访问
    • ext3文件系统的inode锁竞争
  5. 可以备份和恢复独立的分区

# 分区表的限制

  1. 一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区
  2. 在早期的mysql中，分区表达式必须是整数或者是返回整数的表达式，在mysql5.5中，某些场景可以直接使用列来进行分区
  3. 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
  4. 分区表无法使用外键约束

# 分区表的原理

分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引知识在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

分区表的操作按照以下的操作逻辑进行：

# select查询

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

# insert操作

当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表

# delete操作

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

# update操作

当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对底层表进行写入操作，并对源数据所在的底层表进行删除操作

# 总结

​ 当更新一条记录时，分区层先打开并锁住所有的底层表，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)
);
--在当前的建表语句中可以看到，store_id的值在1-5的在p0分区，6-10的在p1分区，11-15的在p3分区，16-20的在p4分区，但是如果插入超过20的值就会报错，因为mysql不知道将数据放在哪个分区

  3. 可以使用less than maxvalue来避免此种情况

    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 MAXVALUE
);
--maxvalue表示始终大于等于最大可能整数值的整数值

  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 (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

  5. 可以使用date类型进行分区：如虚妄根据每个员工离开公司的年份进行划分，如year(separated)

    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,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

  6. 可以使用函数根据range的值来对表进行分区，如timestampunix_timestamp()

    CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
--timestamp不允许使用任何其他涉及值的表达式

    基于时间间隔的分区方案，在mysql5.7中，可以基于范围或事件间隔实现分区方案，有两种选择

    1. 基于范围的分区，对于分区表达式，可以使用操作函数基于date、time、或者datatime列来返回一个整数值

      CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

    2. 基于范围列的分区，使用date或者datatime列作为分区列

      CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

    3. 
#不分区的表
CREATE TABLE no_part_tab
(id INT DEFAULT NULL,
remark VARCHAR(50) DEFAULT NULL,
d_date DATE DEFAULT NULL
)ENGINE=MYISAM;
#分区的表
CREATE TABLE part_tab
(id INT DEFAULT NULL,
remark VARCHAR(50) DEFAULT NULL,
d_date DATE DEFAULT NULL
)ENGINE=MYISAM
PARTITION BY RANGE(YEAR(d_date))(
PARTITION p0 VALUES LESS THAN(1995),
PARTITION p1 VALUES LESS THAN(1996),
PARTITION p2 VALUES LESS THAN(1997),
PARTITION p3 VALUES LESS THAN(1998),
PARTITION p4 VALUES LESS THAN(1999),
PARTITION p5 VALUES LESS THAN(2000),
PARTITION p6 VALUES LESS THAN(2001),
PARTITION p7 VALUES LESS THAN(2002),
PARTITION p8 VALUES LESS THAN(2003),
PARTITION p9 VALUES LESS THAN(2004),
PARTITION p10 VALUES LESS THAN maxvalue);
#插入未分区表记录
DROP PROCEDURE IF EXISTS no_load_part;
 

DELIMITER//
CREATE PROCEDURE no_load_part()
BEGIN
    DECLARE i INT;
    SET i =1;
    WHILE i<80001
    DO
    INSERT INTO no_part_tab VALUES(i,'no',ADDDATE('1995-01-01',(RAND(i)*36520) MOD 3652));
    SET i=i+1;
    END WHILE;
END//
DELIMITER ;
 
CALL no_load_part;
#插入分区表记录
DROP PROCEDURE IF EXISTS load_part;
 
DELIMITER&& 
CREATE PROCEDURE load_part()
BEGIN
    DECLARE i INT;
    SET i=1;
    WHILE i<80001
    DO
    INSERT INTO part_tab VALUES(i,'partition',ADDDATE('1995-01-01',(RAND(i)*36520) MOD 3652));
    SET i=i+1;
    END WHILE;
END&&
DELIMITER ;
 
CALL load_part;

# 列表分区

类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择

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,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

# 列分区

mysql从5.5开始支持column分区，可以认为i是range和list的升级版，在5.5之后，可以使用column分区替代range和list，但是column分区只接受普通列不接受表达式

CREATE TABLE `list_c` (
 `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(c1)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */

 CREATE TABLE `list_c` (
 `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL,
 `c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(c1,c3)
(PARTITION p0 VALUES LESS THAN (5,'aaa') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10,'bbb') ENGINE = InnoDB) */

 CREATE TABLE `list_c` (
 `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL,
 `c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY LIST COLUMNS(c3)
(PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB,
 PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB) */

# hash分区

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含myql中有效的、产生非负整数值的任何表达式。

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,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 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,
    store_id INT
)
PARTITION BY LINEAR HASH(YEAR(hired))
PARTITIONS 4;

# key分区

类似于hash分区，区别在于key分区只支持一列或多列，且mysql服务器提供其自身的哈希函数，必须有一列或多列包含整数值

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

# 子分区

在分区的基础之上，再进行分区后存储

CREATE TABLE `t_partition_by_subpart`
(
  `id` INT AUTO_INCREMENT,
  `sName` VARCHAR(10) NOT NULL,
  `sAge` INT(2) UNSIGNED ZEROFILL NOT NULL,
  `sAddr` VARCHAR(20) DEFAULT NULL,
  `sGrade` INT(2) NOT NULL,
  `sStuId` INT(8) DEFAULT NULL,
  `sSex` INT(1) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`, `sGrade`)
)  ENGINE = INNODB
PARTITION BY RANGE(id)
SUBPARTITION BY HASH(sGrade) SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN(5),
PARTITION p1 VALUES LESS THAN(10),
PARTITION p2 VALUES LESS THAN(15)
);

# 如何使用分区表

如果需要从非常大的表中查询出某一段时间的记录，而这张表中包含很多年的历史数据，数据是按照时间排序的，此时应该如何查询数据呢？ 因为数据量巨大，肯定不能在每次查询的时候都扫描全表。考虑到索引在空间和维护上的消耗，也不希望使用索引，即使使用索引，会发现会产生大量的碎片，还会产生大量的随机IO，但是当数据量超大的时候，索引也就无法起作用了，此时可以考虑使用分区来进行解决

# 全量扫描数据，不要任何索引

使用简单的分区方式存放表，不要任何索引，根据分区规则大致定位需要的数据为止，通过使用where条件将需要的数据限制在少数分区中，这种策略适用于以正常的方式访问大量数据

# 索引数据，并分离热点

如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中，这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存

# 在使用分区表的时候需要注意的问题

  1. null值会使分区过滤无效
  2. 分区列和索引列不匹配，会导致查询无法进行分区过滤
  3. 选择分区的成本可能很高
  4. 打开并锁住所有底层表的成本可能很高
  5. 维护分区的成本可能很高

# 服务器参数设置

# general：通用配置

# 数据文件存放的目录
datadir=/var/lib/mysql
# mysql.socket表示server和client在同一台服务器，并且使用localhost进行连接，就会使用socket进行连接
socket=/var/lib/mysql/mysql.sock
# 存储mysql的pid
pid_file=/var/lib/mysql/mysql.pid
# mysql服务的端口号
port=3306
# mysql存储引擎
default_storage_engine=InnoDB
# 当忘记mysql的用户名密码的时候，可以在mysql配置文件中配置该参数，跳过权限表验证，不需要密码即可登录mysql
skip-grant-tables

# character：字符集

# 客户端数据的字符集
character_set_client = 
# mysql处理客户端发来的信息时，会把这些数据转换成连接的字符集格式
character_set_connection = 
# mysql发送给客户端的结果集所用的字符集
character_set_results = 
# 数据库默认的字符集
character_set_database = 
# mysql server的默认字符集
character_set_server =

# connection：连接 配置

# mysql的最大连接数，如果数据库的并发连接请求比较大，应该调高该值
max_connections = 
# 限制每个用户的连接个数
max_user_connections = 
# mysql能够暂存的连接数量，当mysql的线程在一个很短时间内得到非常多的连接请求时，就会起作用，如果mysql的连接数量达到max_connections时，新的请求会被存储在堆栈中，以等待某一个连接释放资源，如果等待连接的数量超过back_log,则不再接受连接资源
back_log = 
# mysql在关闭一个非交互的连接之前需要等待的时长
wait_timeout = 
# 关闭一个交互连接之前需要等待的秒数
interactive_timeout =

# log：日志配置

# 指定错误日志文件名称，用于记录当mysqld启动和停止时，以及服务器在运行中发生任何严重错误时的相关信息
log_error = 
# 指定二进制日志文件名称，用于记录对数据造成更改的所有查询语句
log_bin = 
# 指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将忽略，不记录在日志中
binlog_do_db = 
# 指定不将更新记录到二进制日志的数据库
binlog_ignore_db = 
# 指定多少次写日志后同步磁盘
sync_binlog = 
# 是否开启查询日志记录
general_log = 
# 指定查询日志文件名，用于记录所有的查询语句
general_log_file = 
# 是否开启慢查询日志记录
slow_query_log = 
# 指定慢查询日志文件名称，用于记录耗时比较长的查询语句
slow_query_log_file = 
# 设置慢查询的时间，超过这个时间的查询语句才会记录日志
long_query_time = 
# 是否将管理语句写入慢查询日志
log_slow_admin_statements

# cache：缓存

# 索引缓存去的大小（只对myisam表起作用）
key_buffer_size = 
# query cache
# 查询缓存的大小，未来版本被删除
query_cache_size = 

# 查看缓存的相关属性
show status like '%Qcache%';
# 缓存中相邻内存块的个数，如果值比较大，那么查询缓存中碎片比较多
Qcache_free_blocks = 
# 查询缓存中剩余的内存大小
Qcache_free_memory = 
# 表示有多少此命中缓存
Qcache_hits = 
# 表示多少次未命中而插入
Qcache_inserts = 
# 多少条query因为内存不足而被移除cache
Qcache_lowmen_prunes = 
# 当前cache中缓存的query数量
Qcache_queries_in_cache = 
# 当前cache中block的数量
Qcache_total_blocks = 

# 超出此大小的查询将不被缓存
query_cache_limit = 
# 缓存块最小大小
query_cache_min_res_unit = 
# 缓存类型，决定缓存什么样的查询：
# 0表示禁用；
# 1表示将缓存所有结果，除非sql语句中使用sql_no_cache金庸查询缓存；
# 2表示只缓存select语句中通过sql_cache指定需要缓存的查询
query_cache_type = 

# 每个需要排序的线程分派该大小的缓冲区
sort_buffer_size = 
# 限制server接受的数据包大小
max_allowed_packet=32M
# 表示关联缓存的大小
join_buffer_size=2M
# Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程
# Threads_connected：代表当前已简历连接的数量
# Threads_created：代表最近一次服务启动，已创建现成的数量，如果该值比较大，那么服务器会一直再创建线程
# Threads_running：代表当前激活的线程数
thread_cache_size =

# innodb

# innodb_buffer_pool_size

  • 该参数指定大小的内存来缓冲数据和索引，最大可以设置为物理内存的80%

# innodb_flush_log_at_trx_commit

  • 主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，控制如何将日志缓冲区的内容写入磁盘并刷新到磁盘。值分别为0，1，2
  • 0：Innodb 中的Log Thread 没隔1 秒钟会将log buffer中的数据写入到文件，同时还会通知文件系统进行文件同步的flush操作，保证数据确实已经写入到磁盘上面的物理文件。但是，每次事务的结束（commit 或者是rollback）并不会触发Log Thread将log buffer中的数据写入文件。所以，当设置为0 的时候，MySQL Crash和OS Crash 或者主机断电之后，最极端的情况是丢失1 秒时间的数据变更。
  • 1：这也是Innodb的默认设置。我们每次事务的结束都会触发Log Thread将log buffer 中的数据写入文件并通知文件系统同步文件。这个设置是最安全的设置，能够保证不论是MySQL Crash 还是OS Crash或者是主机断电都不会丢失任何已经提交的数据。
  • 2：当我们设置为2 的时候，Log Thread会在我们每次事务结束的时候将数据写入事务日志，但是这里的写入仅仅是调用了文件系统的文件写入操作。而我们的文件系统都是有缓存机制的，所以Log Thread的这个写入并不能保证内容真的已经写入到物理磁盘上面完成持久化的动作。文件系统什么时候会将缓存中的这个数据同步到物理磁盘文件Log Thread就完全不知道了。所以，当设置为2 的时候，MySQL Crash并不会造成数据的丢失，但是OS Crash 或者是主机断电后可能丢失的数据量就完全控制在文件系统上了。各种文件系统对于自己缓存的刷新机制各不一样，大家可以自行参阅相关的手册。

# innodb_thread_concurrency

  • 设置innodb线程的并发数，默认为0表示不受限制，如果要设置建议跟服务器的cpu核心数一致或者是cpu核心数的两倍

# innodb_log_buffer_size

  • 此参数确定日志文件所用的内存大小，以M为单位

# innodb_log_file_size

  • 日志组中每个日志文件的字节大小。日志文件的组合大小（(innodb_log_file_size * innodb_log_files_in_group) ）不能超过略小于512GB的最大值。
  • 通常，日志文件的组合大小应该足够大，以便服务器能够消除工作负载活动的高峰和低谷，这通常意味着有足够的重做日志空间来处理一个多小时的写活动。值越大，缓冲池中所需的检查点刷新活动就越少，从而节省磁盘I/O。日志文件越大，崩溃恢复速度也越慢。在MySQL 5.7.11中，(innodb_log_file_size 的最小值从1MB增加到4MB。
  • Innodb_log_file_size 一般设置128M，实际不够可根据如下规则调整。
    • 具体依据如下：我经常设置为 64-512MB
    • 一般来说，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。
    • 首先在业务高峰期，计算出1分钟写入的redo量，然后评估出一个小时的redo量
  • innodb_log_file_size 设置太小，当一个日志文件写满后，innodb会自动切换到另外一个日志文件，而且会触发数据库的检查点（checkpoint），这会导致innodb缓存脏页的批量刷新，会明显降低innodb的性能。
  • innodb_log_file_size 设置太大，减少了checkpoint，并且由于redo log是顺序I/O，大大提高了I/O性能。但是如果数据库意外出现了问题，比如意外宕机，那么需要重放日志并且恢复已经提交的事务。如果日志很大，那么将会导致恢复时间很长，甚至到我们不能接受的程度。

# innodb_log_files_in_group

  • 日志文件中日志组：InnoDB以循环方式写入文件。默认值（推荐值）为2，也就是两个ib_logfile文件。

# read_buffer_size

  • mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区，read_buffer_size仅仅用于MyISAM引擎

# read_rnd_buffer_size

  • mysql随机读的缓冲区大小

# innodb_file_per_table

  • 此参数确定为每张表分配一个新的文件

# bulk_insert_buffer_size

# mysql集群

# 主从复制

# 为什么需要主从复制

  1. 在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
  2. 做数据的热备
  3. 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

# 什么是mysql的主从复制

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

# mysql复制原理

# 原理
  1. master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中。
  2. slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O Thread请求master二进制事件。
  3. 同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/O Thread和SQLThread将进入睡眠状态，等待下一次被唤醒。
# 也就是说
  1. 从库会生成两个线程,一个I/O线程,一个SQL线程;
  2. I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
  3. 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
  4. SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;
# 需要注意
  1. master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。
  2. slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。
  3. Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
  4. Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）。
  5. master和slave两节点间时间需同步。
# 具体步骤
  1. 从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave
  2. 从库的IO线程和主库的dump线程建立连接。
  3. 从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。
  4. 主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。
  5. 从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到master.info中
  6. 从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge

# mysql主从形式

# 一主一从

MySQL一主一从

# 主主复制

MySQL主主复制

# 一主多从

MySQL一主多从

# 多主一从

MySQL多主一从

# 联级复制

MySQL联机复制

# mysql主从同步延时分析

mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高，slave的sql thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随机的，不是顺序，所以成本要高很多，另一方面，由于sql thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL thread所能处理的速度，或者当slave中有大型query语句产生了锁等待，那么延时就产生了。

# 解决方案
  1. 业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。
  2. 单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。
  3. 服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。
  4. 不同业务的mysql物理上放在不同机器，分散压力。
  5. 使用比主库更好的硬件设备作为slave，mysql压力小，延迟自然会变小。
  6. 使用更加强劲的硬件设备
# MySQL5.6 基于schema的并行复制
# 即可有4个SQL Thread（coordinator线程）来进行并行复制，其状态为：Waiting for an evant from Coordinator
slave_parallel_workers = 4

MySQL从5.6开始有了SQL Thread多个的概念，可以并发还原数据，即并行复制技术。其并行只是基于Schema的，也就是基于库的。如果数据库实例中存在多个Schema，这样设置对于Slave复制的速度可以有比较大的提升。通常情况下单库多表是更常见的一种情形，

那基于库的并发就没有卵用。其核心思想是：不同 schema 下的表并发提交时的数据不会相互影响，即 slave 节点可以用对 relay log 中不同的 schema 各分配一个类似 SQL 功能的线程，来重放 relay log 中主库已经提交的事务，保持数据与主库一致。

# MySQL5.7 基于group commit的并行复制
# 即可有4个SQL Thread（coordinator线程）来进行并行复制
slave_parallel_workers=4
# 变量slave-parallel-type可以有两个值：DATABASE 默认值，基于库的并行复制方式；LOGICAL_CLOCK：基于组提交的并行复制方式
slave_parallel_type=LOGICAL_CLOCK

一个组提交的事务都是可以并行回放，因为这些事务都已进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）。

为了兼容MySQL 5.6基于库的并行复制，5.7引入了新的变量slave-parallel-type，其可以配置的值有：

  1. DATABASE：默认值，基于库的并行复制方式
  2. LOGICAL_CLOCK：基于组提交的并行复制方式

由于MTS机制基于组提交实现，简单来说在主上是怎样并行执行的，从服务器上就怎么回放。这里存在一个可能，即若主服务器的并行度不够，则从机的并行机制效果就会大打折扣。

# MySQL8.0 基于write-set的并行复制
# 用于控制如何决定事务的依赖关系。该值有三个选项：默认的 COMMIT_ORDERE 表示继续使用5.7中的基于组提交的方式决定事务的依赖关系；WRITESET 表示使用写集合来决定事务的依赖关系；还有一个选项 WRITESET_SESSION 表示使用 WriteSet 来决定事务的依赖关系，但是同一个Session内的事务不会有相同的 last_committed 值。
binlog_transaction_depandency_tracking = COMMIT_ORDERE
# 取值范围为 1-1000000 ，初始默认值为 25000
binlog_transaction_dependency_history_size = 
# 控制检测事务依赖关系时采用的HASH算法，有三个取值 OFF| XXHASH64 | MURMUR32
# 如果 binlog_transaction_depandency_tracking 取值为 WRITESET 或 WRITESET_SESSION, 那么该值取值不能为OFF，且不能变更。
transaction_write_set_extraction =

​ WriteSet 是通过检测两个事务是否更新了相同的记录来判断事务能否并行回放的，因此需要在运行时保存已经提交的事务信息以记录历史事务更新了哪些行。记录历史事务的参数为 binlog_transaction_dependency_history_size. 该值越大可以记录更多的已经提交的事务信息，不过需要注意的是，这个值并非指事务大小，而是指追踪的事务更新信息的数量。在开启了 WRITESET 或 WRITESET_SESSION 后，MySQL 按以下的方式标识并记录事务的更新。

  • 如果事务当前更新的行有主键（Primary Key），则将 HASH(DB名，TABLE名，KEY名称，KEY_VALUE1, KEY_VALUE2,.....) 加入到当前事务的 vector write_set 中。
  • 如果事务当前更新的行有非空的唯一键 （Unique Key Not NULL）， 同样将 HASH(DB名, TABLE名，KEY名, KEY_VALUE1, ....)加入到当前事务的 write_set 中。
  • 如果事务更新的行有外键约束( FOREIGN KEY )且不为空，则将该 外键信息与VALUE 的HASH加到当前事务的 write_set 中
  • 如果事务当前更新的表的主键是其他某个表的外键，并设置当前事务 has_related_foreign_key = true
  • 如果事务更新了某一行且没有任何数据被加入到 write_set 中，则标记当前事务 has_missing_key = true

​ 在执行冲突检测的时候，先会检查 has_related_foreign_key 和 has_missing_key ， 如果为true， 则退到 COMMIT_ORDER 模式。否则，会依照事务的 write_set 中的HASH值与已提交的事务的 write_set 进行比对，如果没有冲突，则当前事务与最后一个已提交的事务共享相同的 last_commited, 否则将从全局已提交的 write_set 中删除那个冲突的事务之前提交的所有write_set，并退化到 COMMIT_ORDER 计算last_committed 。 每次计算完事务的 last_committed 值以后，检测当前全局已提交事务的 write_set 是否已经超过了 binlog_transaction_dependency_history_size 设置的值，如果超过，则清空已提交事务的全局 write_set。

​ 从检测条件上看，该特性依赖于 主键和唯一索引，如果事务涉及的表中没有主键且没有唯一非空索引，那么将无法从此特性中获得性能的提升。除此之外，还需要将 Binlog 格式设置为 Row 格式。

# 总结

​ 从 MySQL Hight Availability 的测试中可以看到，开启了基于 WriteSet 的事务依赖后，对Slave上RelayLog回放速度提升显著。Slave上的 RelayLog 回放速度将不再依赖于 Master 上提交时的并行程度，使得Slave上可以发挥其最大的吞吐能力， 这个特性在Slave上复制停止一段时间后恢复复制时尤其有效。

​ 这个特性使得 Slave 上可能拥有比 Master 上更大的吞吐量，同时可能在保证事务依赖关系的情况下，在 Slave 上产生 Master 上没有产生过的提交场景，事务的提交顺序可能会在 Slave 上发生改变。 虽然在5.7 的并行复制中就可能发生这种情况，不过在8.0中由于 Slave 上更高的并发能力，会使该场景更加常见。 通常情况下这不是什么大问题，不过如果在 Slave 上做基于 Binlog 的增量备份，可能就需要保证在 Slave 上与Master 上一致的提交顺序，这种情况下可以开启 slave_preserve_commit_order 这是一个 5.7 就引入的参数，可以保证 Slave 上并行回放的线程按 RelayLog 中写入的顺序 Commit。

# 读写分离

MySQL读写分离基本原理是让master数据库处理写操作，slave数据库处理读操作。master将写操作的变更同步到各个slave节点。

# MySQL读写分离能提高系统性能的原因

  1. 物理服务器增加，机器处理能力提升。拿硬件换性能。
  2. 主从只负责各自的读和写，极大程度缓解X锁和S锁争用。
  3. slave可以配置myiasm引擎，提升查询性能以及节约系统开销。
  4. master直接写是并发的，slave通过主库发送来的binlog恢复数据是异步。
  5. slave可以单独设置一些参数来提升其读的性能。
  6. 增加冗余，提高可用性。

# MySQL读写分离中间代理层

  1. mysql-proxy
  2. Amoeba
  3. MyCat

# MySQL事务

# 事务的处理

  1. 正常的事务提交(commit)或者回滚(rollback)
  2. 自动提交，但是一般情况下要将自动提交进行关闭，影响效率
  3. 用户关闭会话，会自动提交事务
  4. 系统崩溃或者断电的时候，自动回滚事务
  5. 当一次执行多条sql（同时包含DML语句和DDL语句）时，当在执行DDL语句之前，会自动提交事务，所以当遇到这种情况时，应当把DDL语句放在最后执行。
  6. sql分类：
    1. DML 数据操控:如select,insert,update,delete
    2. DCL 数据控制:如权限控制，grant,revoke
    3. DDL 数据定义:如create,alter,drop等表定义语句

# 事务的基本特性

事务的基本特性ACID：

  1. 原子性（Atomicity）：一个原子事务要么完整执行，要么干脆不执行。这意味着，工作单元中的每项任务都必须正确执行。如果有任一任务执行失败，则整个工作单元或事务就会被终止。即此前对数据所作的任何修改都将被撤销。如果所有任务都被成功执行，事务就会被提交，即对数据所作的修改将会是永久性的。
  2. 一致性（Consistency）：一致性代表了底层数据存储的完整性。它必须由事务系统和应用开发人员共同来保证。事务系统通过保证事务的原子性，隔离性和持久性来满足这一要求; 应用开发人员则需要保证数据库有适当的约束(主键，引用完整性等)，并且工作单元中所实现的业务逻辑不会导致数据的不一致(即，数据预期所表达的现实业务情况不相一致)。例如，在一次转账过程中，从某一账户中扣除的金额必须与另一账户中存入的金额相等。支付宝账号100 你读到余额要取，有人向你转100 但是事物没提交（这时候你读到的余额应该是100，而不是200） 这种就是一致性。
  3. 隔离性（Isolation）：隔离性意味着事务必须在不干扰其他进程或事务的前提下独立执行。换言之，在事务或工作单元执行完毕之前，其所访问的数据不能受系统其他部分的影响。
  4. 持久性（Durability）：持久性表示在某个事务的执行过程中，对数据所作的所有改动都必须在事务成功结束前保存至某种物理存储设备。这样可以保证，所作的修改在任何系统瘫痪时不至于丢失。

# 隔离级别

  1. 脏读

所谓脏读，就是指事务A读到了事务B还没有提交的数据，比如银行取钱，事务A开启事务，此时切换到事务B，事务B开启事务-->取走100元，此时切换回事务A，事务A读取的肯定是数据库里面的原始数据，因为事务B取走了100块钱，并没有提交，数据库里面的账务余额肯定还是原始余额，这就是脏读。

  1. 不可重复读

所谓不可重复读，就是指在一个事务里面读取了两次某个数据，读出来的数据不一致。还是以银行取钱为例，事务A开启事务-->查出银行卡余额为1000元，此时切换到事务B事务B开启事务-->事务B取走100元-->提交，数据库里面余额变为900元，此时切换回事务A，事务A再查一次查出账户余额为900元，这样对事务A而言，在同一个事务内两次读取账户余额数据不一致，这就是不可重复读。

  1. 幻读

所谓幻读，就是指在一个事务里面的操作中发现了未被操作的数据。比如学生信息，事务A开启事务-->修改所有学生当天签到状况为false，此时切换到事务B，事务B开启事务-->事务B插入了一条学生数据，此时切换回事务A，事务A提交的时候发现了一条自己没有修改过的数据，这就是幻读，就好像发生了幻觉一样。幻读出现的前提是并发的事务中有事务发生了插入、删除操作。

MySQL的事务隔离级别：默认可重复读 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
读已提交（read-committed） 否 是 是
可重复读（repeatable-read） 否 否 是
序列化（serializable） 否 否 否
Oracle的事务隔离级别:默认读已提交 脏读 不可重复读 幻读
读已提交（read-committed） 否 是 是
只读（read only） 否 否 是
序列化（serializable） 否 否 否
Last Updated: 6/23/2021, 11:40:46 AM

mysql主从复制安装配置 →