MYSQL优化及参数解释

最新推荐文章于 2022-04-29 11:38:39 发布

老王就是我

最新推荐文章于 2022-04-29 11:38:39 发布

阅读量603

点赞数 1

分类专栏：后台文章标签： mysql

本文链接：https://blog.csdn.net/qq_22899047/article/details/118188071

版权

后台专栏收录该内容

19 篇文章 2 订阅

订阅专栏

MYSQL性能监控

参考地址

mysql配置默认打开性能监控，无需手动开启

实时监控

查看最新一条sql语句消耗时间列表

show profile;

结果

starting	0.000075
checking permissions	0.000019
Opening tables	0.000021
init	0.000051
System lock	0.000009
optimizing	0.000006
optimizing	0.000005
statistics	0.000015
preparing	0.000013
statistics	0.000006
preparing	0.000006
executing	0.000009
Sending data	0.000010
executing	0.000005
Sending data	0.001504
end	0.000023
query end	0.000011
closing tables	0.000007
removing tmp table	0.000012
closing tables	0.000010
freeing items	0.000094
cleaning up	0.000016

查看cpu消耗时间

show profile cpu;

结果

Status	Duration	CPU_user	CPU_system
starting	0.000122	0	0.000109
checking permissions	0.000031	0	0.000027
Opening tables	0.000026	0	0.000026
init	0.000083	0	0.000083
System lock	0.000013	0	0.000012
optimizing	0.000008	0	0.000007
optimizing	0.000007	0	0.000008
statistics	0.000021	0	0.000021
preparing	0.000017	0	0.000017
statistics	0.000009	0	0.000008
preparing	0.000008	0	0.000008
executing	0.000014	0	0.000014
Sending data	0.000013	0	0.000012
executing	0.000006	0	0.000007
Sending data	0.001798	0.000803	0.001008
end	0.000046	0	0.000033
query end	0.000013	0	0.000012
closing tables	0.000009	0	0.000009
removing tmp table	0.000016	0	0.000016
closing tables	0.000012	0	0.000012
freeing items	0.000136	0	0.000136
cleaning up	0.000023	0	0.000023

查看性能监控数据库(Performance_Schema)

特征

Performance Schema 提供了一种在运行时检查服务器内部执行的方法。它是使用 PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现的。Performance Schema 主要关注性能数据。这不同于INFORMATION_SCHEMA用于检查元数据的。
Performance Schema 监视服务器事件。一个 “事件”是什么，该服务器确实需要时间和被装备，使时序信息可以收集。通常，事件可以是函数调用、等待操作系统、SQL 语句执行阶段（例如解析或排序），或者整个语句或语句组。事件收集提供对服务器和多个存储引擎的同步调用（例如互斥锁）文件和表 I/O、表锁等信息的访问。
Performance Schema 事件不同于写入服务器二进制日志的事件（描述数据修改）和事件调度程序事件（这是一种存储程序）。
Performance Schema 事件特定于 MySQL 服务器的给定实例。Performance Schema 表被认为是服务器本地的，对它们的更改不会复制或写入二进制日志。
提供当前事件以及事件历史和摘要。这使您能够确定执行了多少次检测活动以及它们花费了多少时间。事件信息可用于显示特定线程的活动，或与特定对象（例如互斥锁或文件）相关联的活动。
该PERFORMANCE_SCHEMA存储引擎使用收集事件数据“检测点”在服务器的源代码。
收集的事件存储在performance_schema数据库的表中。可以SELECT 像其他表一样使用语句来查询这些表。
Performance Schema 配置可以performance_schema 通过 SQL 语句更新数据库中的表来动态修改。配置更改会立即影响数据收集。
Performance Schema 中的表是不使用持久磁盘存储的内存表。内容在服务器启动时重新填充，并在服务器关闭时丢弃。
监控在 MySQL 支持的所有平台上可用。
可能存在一些限制：计时器的类型可能因平台而异。适用于存储引擎的工具可能不会为所有存储引擎实现。每个第三方引擎的仪表是引擎维护者的责任。另请参阅第 25.21 节，“对性能模式的限制”。
数据收集是通过修改服务器源代码添加instrumentation来实现的。与其他功能（如复制或事件调度程序）不同，没有与 Performance Schema 关联的单独线程。
Performance Schema 旨在提供对有关服务器执行的有用信息的访问，同时对服务器性能的影响最小。实现遵循以下设计目标：
激活性能架构不会导致服务器行为发生变化。例如，它不会导致线程调度发生变化，也不会导致查询执行计划（如所示 EXPLAIN）发生变化。
服务器监控以很少的开销持续且不显眼地发生。激活性能架构不会使服务器无法使用。
解析器不变。没有新的关键字或语句。
即使性能模式在内部失败，服务器代码的执行也会正常进行。
当在最初的事件收集期间或稍后的事件检索期间执行处理之间进行选择时，优先考虑使收集更快。这是因为收集是持续进行的，而检索是按需进行的，并且可能永远不会发生。
添加新的检测点很容易。
仪表是版本化的。如果检测实现发生变化，先前检测的代码将继续工作。这有利于第三方插件的开发人员，因为无需升级每个插件即可与最新的 Performance Schema 更改保持同步。

查看性能监控之前要了解的概念

instruments：生产者。用于采集mysql中各种操作产生的信息，在对应配置表中的配置项可以称为监控采集项
counsumers：消费者。对应的消费表用于存储来自instruments的监控采集项监控项采集的数据，在对应的配置表中的配置项可以成为消费存储配置项

查看cpu、io的损耗

show profile block io,cpu for query 1;

查看当前的server正在做什么

SELECT * from events_waits_current

查看服务器启动之后执行的所有事件

SELECT * from events_waits_history

查看登录信息(登陆者ip和连接的数据库)

show PROCESSLIST;

查看最近执行的语句

SELECT
	SCHEMA_NAME,
	DIGEST_TEXT,
	COUNT_STAR,
	sys.format_time ( SUM_TIMER_WAIT ) AS sum_time,
	sys.format_time ( MIN_TIMER_WAIT ) AS min_time,
	sys.format_time ( AVG_TIMER_WAIT ) AS avg_time,
	sys.format_time ( MAX_TIMER_WAIT ) AS max_time,
	sys.format_time ( SUM_LOCK_TIME ) AS sum_lock_time,
	SUM_ROWS_AFFECTED,
	SUM_ROWS_SENT,
	SUM_ROWS_EXAMINED 
FROM
	events_statements_summary_by_digest 
WHERE
	SCHEMA_NAME IS NOT NULL 
ORDER BY
	COUNT_STAR DESC 
	LIMIT 10;

Performance Schema数据库中所有表的作用

events_xxx_current：“当前事件”表存储每个线程的当前监控事件（每个线程一行）
events_xxx_history：“最近历史记录”表存储每个线程结束的最近事件（最多每个线程的最大行数）。
events_xxx_history_long：“ long history ”表存储全局结束的最新事件（跨所有线程，最多每个表的最大行数）。
_history 旨在研究独立于全局服务器负载的单个线程。
对于_history，当表包含给定线程的最大行数时，当为该线程添加新行时，最旧的线程行将被丢弃。
_history_long 旨在全局调查服务器，而不是每个线程。
对于_history_long，当表变满时，添加新行时将丢弃最旧的行，而不管哪个线程生成了任一行。

| 表名 | 描述 | 出现版本号

为null则表示8.0.0开始就有
accounts
`binary_log_transaction_compression_stats`
`clone_progress`
`clone_status`
cond_instances
data_lock_waits
data_locks
error_log
events_errors_summary_by_account_by_error
events_errors_summary_by_host_by_error
events_errors_summary_by_thread_by_error
events_errors_summary_by_user_by_error
events_errors_summary_global_by_error
events_stages_current
events_stages_history
events_stages_history_long
events_stages_summary_by_account_by_event_name
events_stages_summary_by_host_by_event_name
events_stages_summary_by_thread_by_event_name
events_stages_summary_by_user_by_event_name
events_stages_summary_global_by_event_name
events_statements_current
events_statements_histogram_by_digest
events_statements_histogram_global
events_statements_history
events_statements_history_long
events_statements_summary_by_account_by_event_name
events_statements_summary_by_digest
events_statements_summary_by_host_by_event_name
events_statements_summary_by_program
events_statements_summary_by_thread_by_event_name
events_statements_summary_by_user_by_event_name
events_statements_summary_global_by_event_name
events_transactions_current
events_transactions_history
events_transactions_history_long
events_transactions_summary_by_account_by_event_name
events_transactions_summary_by_host_by_event_name
events_transactions_summary_by_thread_by_event_name
events_transactions_summary_by_user_by_event_name
events_transactions_summary_global_by_event_name
events_waits_current
events_waits_history

定线程的最大行数时，
当为该线程添加新行时，
最旧的线程行将被丢弃

Performance Schema参数设置

| --performance-schema[={OFF|ON}] | 此变量的值是ONor OFF以指示是否启用了性能模式。默认情况下，该值为 ON。在服务器启动时，您可以将此变量指定为无值或值为ON或 1 以启用它，或使用值OFF或 0 以禁用它。 |
| — | — |
| --performance-schema-accounts-size=# | accounts表中的行数。如果此变量为 0，则 Performance Schema 不维护accounts表中的连接统计信息或表中的状态变量信息 status_by_account。 |
| --performance-schema-digests-size=# | 表中的最大行数 events_statements_summary_by_digest 。如果超过此最大值以致无法检测摘要，则性能模式会增加 Performance_schema_digest_lost 状态变量。 |
| --performance-schema-events-stages-history-long-size=# | events_stages_history_long表中的行数。 |
| --performance-schema-events-stages-history-size=# | events_stages_history表中每个线程的行数。 |
| --performance-schema-events-statements-history-long-size=# | events_statements_history_long 表中的行数。 |
| --performance-schema-events-statements-history-size=# | events_statements_history表中每个线程的行数。 |
| --performance-schema-events-transactions-history-long-size=# | events_transactions_history_long 表中的行数。 |
| --performance-schema-events-transactions-history-size=# | events_transactions_history 表中每个线程的行数。 |
| --performance-schema-events-waits-history-long-size=# | events_waits_history_long表中的行数。 |
| --performance-schema-events-waits-history-size=# | events_waits_history表中每个线程的行数。 |
| --performance-schema-hosts-size=# | hosts 表中的行数。如果此变量为 0，则 Performance Schema 不维护hosts表中的连接统计信息或表中的状态变量信息status_by_host 。 |
| --performance-schema-max-cond-classes=# | 条件仪器的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-cond-instances=# | 检测条件对象的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-digest-length=# | 每个语句保留的最大内存字节数，用于计算性能模式中的规范化语句摘要值。该变量与 max_digest_length; 请参阅第 5.1.7 节“服务器系统变量”中对该变量的描述。
有关语句摘要的更多信息，包括有关内存使用的注意事项，请参阅第 25.10 节，“性能模式语句摘要”。 |
| --performance-schema-max-file-classes=# | 生产者文件的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-file-handles=# | 打开的文件对象的最大数量。performance_schema_max_file_handles 的值应该大于 open_files_limit 的值。open_files_limit 影响服务器可以支持的最大打开文件句柄数，performance_schema_max_file_handles 影响可以检测这些文件句柄的数量。 |
| --performance-schema-max-file-instances=# | 检测的文件对象的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-index-stat=# | Performance Schema 为其维护统计信息的最大索引数。如果超过此最大值导致索引统计信息丢失，Performance Schema 会增加 Performance_schema_index_stat_lost 状态变量。默认值使用的值自动调整大小 performance_schema_max_table_instances。 |
| --performance-schema-max-memory-classes=# | 内存仪器的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-metadata-locks=# | 元数据锁定工具的最大数量。此值控制metadata_locks表的大小。如果超过此最大值以致无法检测元数据锁，则性能模式会增加 Performance_schema_metadata_lock_lost 状态变量。 |
| --performance-schema-max-mutex-classes=# | 互斥工具的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-mutex-instances=# | 检测的互斥对象的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-prepared-statements-instances=# | 表中的最大行数 prepared_statements_instances 。如果超过此最大值以致无法检测准备好的语句，则性能模式会增加 Performance_schema_prepared_statements_lost 状态变量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。
该变量的默认值是根据max_prepared_stmt_count 系统变量的值自动调整大小。 |
| --performance-schema-max-rwlock-classes=# | rwlock 仪器的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-program-instances=# | Performance Schema 为其维护统计信息的存储程序的最大数量。如果超过此最大值，Performance Schema 会增加 Performance_schema_program_lost 状态变量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-rwlock-instances=# | 检测的 rwlock 对象的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-socket-classes=# | 插座仪器的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-socket-instances=# | 检测的套接字对象的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-sql-text-length=# | 在用于存储SQL语句的最大字节数SQL_TEXT的列 events_statements_current， events_statements_history和 events_statements_history_long 语句事件表。任何超出的字节 performance_schema_max_sql_text_length 都将被丢弃并且不会出现在 SQL_TEXT列中。仅在此列中无法区分许多初始字节之后才不同的语句。
减小该 performance_schema_max_sql_text_length 值会减少内存使用，但如果它们仅在最后不同，则会导致更多语句变得无法区分。增加该值会增加内存使用，但允许区分更长的语句。 |
| --performance-schema-max-stage-classes=# | 生产者阶段的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-statement-classes=# | 报表工具的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。
默认值是在服务器构建时根据客户端/服务器协议中的命令数量和服务器支持的 SQL 语句类型数量计算的。
不应更改此变量，除非将其设置为 0 以禁用所有语句检测并保存与其关联的所有内存。将变量设置为默认值以外的非零值没有任何好处；特别是，大于默认值的值会导致需要分配更多内存。 |
| --performance-schema-max-statement-stack=# | Performance Schema 维护统计信息的嵌套存储程序调用的最大深度。当超过此最大值时，Performance Schema 会Performance_schema_nested_statement_lost 为执行的每个存储的程序语句增加状态变量。 |
| --performance-schema-max-table-handles=# | 打开的表对象的最大数量。此值控制table_handles表的大小。如果超过此最大值以致无法检测表句柄，则性能模式会增加 Performance_schema_table_handles_lost 状态变量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-table-instances=# | 检测的表对象的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-table-lock-stat=# | Performance Schema 为其维护锁统计信息的最大表数。如果超过此最大值导致表锁统计信息丢失，则性能模式会增加 Performance_schema_table_lock_stat_lost 状态变量。 |
| --performance-schema-max-thread-classes=# | 线程生产者的最大数量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。 |
| --performance-schema-max-thread-instances=# | 检测的线程对象的最大数量。该值控制threads 表的大小。如果超过此最大值以致无法检测线程，则性能模式会增加 Performance_schema_thread_instances_lost 状态变量。有关如何设置和使用此变量的信息，请参阅第 25.7 节，“性能模式状态监控”。
该max_connections系统变量会影响多少个线程可以在服务器端运行。 performance_schema_max_thread_instances 影响可以检测这些正在运行的线程的数量。
在variables_by_thread和 status_by_thread表格只包含约前台线程系统状态变量信息。如果不是所有线程都由 Performance Schema 检测，则此表可能会丢失一些行。在这种情况下， Performance_schema_thread_instances_lost 状态变量大于零。 |
| --performance-schema-session-connect-attrs-size=# | setup_actors表中的行数。 |
| --performance-schema-setup-objects-size=# | setup_objects表中的行数。 |
| --performance-schema-users-size=# | users 表中的行数。如果此变量为 0，则 Performance Schema 不维护users表中的连接统计信息或表中的状态变量信息status_by_user 。 |

setup_instruments(性能监控配置表)表结构

NAME	生产者名称
ENABLED	是否启用，值为YES或NO。该值可以修改，但修改后对已创建的生产者无效
TIMED	生产者是否计时，值为YES、NO或NULL。null表示该生产者不支持计时。该值可以修改，但修改后对已创建的
PROPERTIES	生产这属性。

为每个工具设置以下列表中的多个标志：

global_statistics：仪器仅生成全局摘要。更精细级别的摘要不可用，例如每个线程、帐户、用户或主机。例如，大多数记忆工具仅生成全局摘要。
mutable：乐器可以“变异”成更具体的乐器。此属性仅适用于报表工具。
progress：仪器能够报告进度数据。此属性仅适用于舞台乐器。
singleton：仪器只有一个实例。例如，服务器中的大多数全局互斥锁都是单例的，因此相应的仪器也是如此。
user：仪器与用户工作量（相对于系统工作量）直接相关。一种这样的工具是 wait/io/socket/sql/client_connection。

|
| VOLATILITY | 工具的波动。波动率值范围从低到高。
该值对应头文件中的常量PSI_VOLATILITY__xxx_``mysql/psi/psi_base.h
#define PSI_VOLATILITY_UNKNOWN 0
#define PSI_VOLATILITY_PERMANENT 1
#define PSI_VOLATILITY_PROVISIONING 2
#define PSI_VOLATILITY_DDL 3
#define PSI_VOLATILITY_CACHE 4
#define PSI_VOLATILITY_SESSION 5
#define PSI_VOLATILITY_TRANSACTION 6
#define PSI_VOLATILITY_QUERY 7
#define PSI_VOLATILITY_INTRA_QUERY 8
|
| DOCUMENTATION | 描述该生产者的用途 |

为使性能监控完整运行，需要执行以下语句

UPDATE setup_instruments SET ENABLED='YES',TIMED ='YES' WHERE `NAME` LIKE '%wait%'

setup_consumers(消费者表)表结构

NAME	消费者名称
ENABLED	是否使用消费者，YES或NO

为使消费者完整运行，需要执行以下语句

UPDATE setup_consumers SET ENABLED='YES' WHERE `NAME` LIKE '%wait%'

查看数据库当前的所有事件

查询当前事件表(events_waits_current)

THREAD_ID	线程id，与event_id一起组成唯一标识符（），
EVENT_ID	事件id，与thread_id一起组成唯一标识符（组合id），
END_EVENT_ID	此列设置为`NULL`事件开始时，并在事件结束时更新为线程当前事件编号。
SOURCE	产生事件的检测代码的源文件的名称以及检测发生的文件中的行号
TIMER_START	事件的开始时间。单位是皮秒(万亿分之一秒)
TIMER_END	事件的结束时间。单位是皮秒(万亿分之一秒)。如果事件未完成，则此值为当前的时间计数器时间(当前时间)。如果setup_instruments的相应记录的TIMED为NO或者NULL的话，此字段值为NULL
TIMER_WAIT	事件的等待时间。单位是皮秒(万亿分之一秒)。如果setup_instruments的事件相应记录的TIMED为NO或者NULL的话，此字段值为NULL
SPINS	互斥锁的自旋轮数。如果值为 `NULL`，则代码不使用自旋轮或不检测自旋

常用的连接池对比

功能对比

	Druid	BoneCP	DBCP	C3P0	Proxool	JBoss	Tomcat-Jdbc
LRU	是	否	是	否	是	是	?
PSCache	是	是	是	是	否	否	是
PSCache-Oracle-Optimized	是	否	否	否	否	否	否
ExceptionSorter	是	否	否	否	否	是	否
更新维护	是	否	否	否	否	?	是

PSCache

PSCache是数据库连接池的关键指标。在Oracle中，类似SELECT NAME FROM USER WHERE ID = ?这样的SQL，启用PSCache和不启用PSCache的性能可能是相差一个数量级的。Proxool是不支持PSCache的数据库连接池，如果你使用Oracle、SQL Server、DB2、Sybase这样支持游标的数据库，那你就完全不用考虑Proxool。

PSCache-Oracle-Optimized

Oracle 10系列的Driver，如果开启PSCache，会占用大量的内存，必须做特别的处理，启用内部的EnterImplicitCache等方法优化才能够减少内存的占用。这个功能只有DruidDataSource有。如果你使用的是Oracle Jdbc，你应该毫不犹豫采用DruidDataSource。

ExceptionSorter

ExceptionSorter是一个很重要的容错特性，如果一个连接产生了一个不可恢复的错误，必须立刻从连接池中去掉，否则会连续产生大量错误。这个特性，目前只有JBossDataSource和Druid实现。Druid的实现参考自JBossDataSource，经过长期生产反馈补充。

设计数据表的优化

数据类型

占用字节越小的效果越好，尽量使用最小占用的字段类型。因为占用越小代表消耗的计算机资源消耗越少
越简单越好。尽量使用简单的数据类型存储数据，能用数字类型就不要用字符串，能用时间类型就不要用字符串。因为
- 越简单的类型，读写消耗的资源越小。也就是说，越简单的类型，增删查改的速度越快
- 有这样两张表

CREATE TABLE `string_time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `string_time` varbinary(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `test_date_time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

在执行sql的时候的时间对比

SELECT
	* 
FROM
	string_time v
WHERE
	v.string_time = '1950-01-01';
SELECT
	* 
FROM
	test_date_time b
WHERE
	b.test_time = '1950-01-01';

![image.png](https://img-blog.csdnimg.cn/img_convert/072338bf7a6c45baef85602c23c8d691.png#align=left&display=inline&height=874&margin=[object Object]&name=image.png&originHeight=874&originWidth=1666&size=111591&status=done&style=none&width=1666)
原因：识别字符串时，innodb引擎会把该字段的每一个都拿出来字符串作对比，是否符合条件。而数值类型则是整个做对比
尽量避免使用null。使用null会失去索引，并且引擎无法优化。设计数据库时应该把可为null的字段值的默认值设为空串
各种数据类型的应用场景
- char
  - 最大长度：255
  - 存储长度波动不大的数据，例如md5、密码
- varchar
  - 存储长度波荡较大的数据，如文章摘要、用户个性签名、用户名、昵称等
  - 存储多字节字符，如方块字（汉字、日文、韩文等）
  - 字段值很少更新的场景，如毕业院校等
- text、blob
  - 两者都是一个独立的对象，适合存储较大字符长度的数据。例如文章
- datetime
  - 占用8字节
  - 与时区无关，数据库底层的时区配置对datetime无效
  - 精确到年月日时分秒毫秒
  - 保存的时间范围比较大：0000-00-00 00:00:00~9999-12-31 23:59:59
- timestamp
  - 占用4个字节
  - 精确到年月日时分秒
  - 采用整形(int)存储
  - 依赖数据库底层时区设置
  - 可以自动更新timestamp字段的值
  - 保存的时间范围：1970-01-01 00:00:00~2038-01-19 23:59:59
- date
  - 占用空间最小，只有3个字节
  - 依赖数据库底层时区设置
  - 精确到年月日，没有时分秒
  - 保存的时间范围：0000-00-00~9999-12-31
- enum(枚举)
  - 存入的字段的值是可控的(只能存储指定值范围内的数据)

索引

介绍

explain详解

![](https://img-blog.csdnimg.cn/img_convert/d8d9a4008a3aa52e5340f38822a15000.png#align=left&display=inline&height=47&margin=[object Object]&originHeight=47&originWidth=746&size=0&status=done&style=none&width=746)

id	选择标识符
select_type	表示查询的类型。
table	输出结果集的表
partitions	匹配的分区
type	表的连接类型
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
ref	列与索引的比较
rows	扫描出的行数(估算的行数)
filtered	按表条件过滤的行百分比
Extra	执行情况的描述和说明。
Using index————索引覆盖

索引数据结构分类

哈希索引

特性

基于哈希表的实现，只有精确匹配索引所有字段的才生效
只有memory的存储引擎才显示支持哈希索引
哈希索引自身只需存储对应的哈希值，结构非常紧凑，所以该索引的查询速度相当快

使用限制

哈希索引只包含哈希值和行指针，不存储字段值。所以使用该索引不能使用索引中的值(字段值)来避免读取行导致索引失效
哈希索引的数据并不是按照索引值存储(字段值)，无法进行排序
哈希索引不支持部分匹配查找，仅支持全量匹配
还行索引支持等值比较查询，不支持范围查询
哈希索引查询比较快。但哈希冲突时，存储引擎必须遍历链表的所有行指针，进行逐行比较，后找到所有符合条件的记录后返回

B+树索引

索引种类

普通索引(normal)

默认索引，索引只包含一个字段

组合索引(normal)

特性

包含多个字段的普通索引

注意事项

sql语句需要注意顺序，符合最左匹配原则
前一个字段使用了范围索引后，后面跟着的字段不会匹配索引
![](https://img-blog.csdnimg.cn/img_convert/f254fc2b0481d0828c40aafa173dd47f.png#align=left&display=inline&height=430&margin=[object Object]&originHeight=430&originWidth=841&size=0&status=done&style=none&width=841)

聚簇索引和非聚簇索引

聚簇索引

如果存在主键，则主键是聚簇索引。如果不存在主键，则在数据表内部建立一个聚簇索引

非聚簇索引

非主键的字段

唯一索引(unique)

当前索引包含的字段的值全表唯一

全文索引(full text)

假设有这样一条sql语句

select * from test where name like 'dsf%'

数据量比较大的情况下，该语句的效率是比较低的，全文索引就是为了提高检索字符型字段的值的全文内容而提供的解决方案

索引基本原则

最左前缀原则

最左前缀原则指的的是在sql where 字句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序出现、断层都无法利用到多列索引。

举例

上面给出一个多列索引(username,password,last_login)，当三列在where中出现的顺序如(username,password,last_login)、(username,password)、(username)才能用到索引，如下面几个顺序(password,last_login)、(passwrod)、(last_login)—这三者不从username开始，(username,last_login)—断层，少了password，都无法利用到索引。

前缀匹配原则

当查询条件精确匹配左边连续一个或多个列时，索引可以被使用，但只能使用一部分。
有时候需要索引很长的字符串,这会让素引变的大且慢,通常情況下可以使用某个列开始的部分字符串,这样大大的节约素引空间,从而提高素引效率,但这会降低素引的选择性,素引的选择性是指不重复的素引值和数据表记录总数的比值,范围从1/T到1之间。索引的选择性越高则查询效率越高,因为选择性更高的索引可以让mysq在查找的时候过滤掉更多的行。
一般情況下某个列前缀的选择性也是足够高的,足以满足查询的性能,但是对应BLOB, TEXT.VARCHAR类型的列,必须要使用前缀索引,因为mysq不允许素引这些列的完整长度,使用该方法的决窍在于要选择足够长的前缀以保证较高的选择性,通过又不能太长。

全值匹配

和索引中所有的字段进行匹配

匹配范围值

索引包含的字段进行>或者<对比符匹配

只访问索引的查询

查询的时候只访问索引不访问数据行，实际上就是覆盖索引

精准匹配某一列并范围匹配另外一列

使用索引的优化细节

尽量不要在索引字段内使用表达式。如——select * from test where a+1=2;而要改成select * from test where a=1;
尽量使用主键查询，而不是使用其他索引，避免触发回表
利用索引扫描做排序。即：使用包含索引的字段进行order by ，而不是使用非索引包含的字段进行order by

索引优化

需要提前了解的概念

innodb每次读取磁盘，都会读取一页的数据(默认读取4K数据量)

查询慢原因

网络
CPU
IO
上下文切换
系统调用
生成统计信息(类似Performance_Schema)
锁等待时间

注意

并不是每个后缀为"%"都会使用range，优化器会计算出总行数与该sql返回行数的差距后得出使用该索引的代价后悔考虑是否使用索引。
百分号放在尾部的查询条件的结论：这种百分号放在尾部的查询条件在实际执行中不一定会去使用前缀索引，因为在mysql查询优化器中基于成本计算是否使用索引的代价有这样一种方式–我们在用前缀索引也就是辅助索引进行模糊匹配时是有序的，辅助索引也是用b+tree存放的，在该b+tree的叶子节点存放的对应的主键值一般情况下不是有序的，这样可以认为取的每一行记录都需要读取磁盘一次（表的数据在磁盘中划分为块的形式，在内存中划分为页的形式），即test表在磁盘上所占的块数为(B)，执行上述查询语句返回的行数为(T)，当T足够大的时候也就是大致的(T>B)，这时候走全表查询会比走辅助索引到主索引查询更优。当然，如果(T)足够小的时候，走索引查询更优。这个(T)和(B)的关系通常用放回的行数占总行数的比例去衡量，目前来说没有一个固定的临界值，一般的经验值为30%。

举例

在这里插入图片描述

SQL语句优化

执行顺序介绍

原始sql语句

select[distinct]  
from  
join（如left join）  
on  
where  
group by  
having  
union  
order by  
limit

执行顺序

from  
on  
join  
where  
group by  
having  
select  
distinct  
union  
order by  
limit

Order by优化

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

查看max_length_for_sort_data

SHOW VARIABLES LIKE '%max_length_for_sort_data%';

列的总大小

sql语句在limit之前的所有记录数

SQL语句优化

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
1. 尽量避免子查询。因为子查询会创建临时表，查询返回后会清除临时表，这需要较高的io占用
应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20

下面的查询也将导致全表扫描：
select id from t where name like ‘%abc%’
若要提高效率，可以考虑全文检索。
in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)=‘abc’–name以abc开头的id
select id from t where datediff(day,createdate,‘2005-11-30’)=0–'2005-11-30’生成的id
应改为:
select id from t where name like ‘abc%’
select id from t where createdate>=‘2005-11-30’ and createdate<‘2005-12-1’
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)
很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
避免频繁创建和删除临时表，以减少系统表资源的消耗。
临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
尽量避免大事务操作，提高系统并发能力。
尽量不要直接使用limit，而是

select * from test t inner join (select id from test t2 limit 1,2) t3 on t.id=t3.id

查询优化器

查询缓存

开启mysql缓存后，数据没有更新的情况下，相同的查询sql会使用缓存数据返回结果。在数据更新较少，类似查询较多的情况下，使用mysql缓存可以显著提升查询效率。但是，缓存的命中率较低

老王就是我

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MYSQL优化及参数解释

MYSQL性能监控参考地址mysql配置默认打开性能监控，无需手动开启实时监控查看最新一条sql语句消耗时间列表show profile;结果starting0.000075checking permissions0.000019Opening tables0.000021init0.000051System lock0.000009optimizing0.000006optimizing0.000005statistics
复制链接

扫一扫