SQL

SQL的必知必会 - Part Two

SQL知识点

Posted by Claire on November 10, 2020

SQL的必知必会

1.覆盖索引

  • 大型查询或频繁执行的查询带来显著性能提升
  • 覆盖索引需要包含where order_by group_by的每一列
  • 覆盖索引对于那些使用了很多较小长度的主码和外检约束的大型规范化模式来说是理想的优化方式

2.局部索引-对索引字段指定大小

  • 如果查询需要获取大量行中的更多列数据，创建更小行宽度的小型索引会更加高效

2.内存相关配置

  • MySQL内促进系统变量能够影响全局内存的使用，也能够影响到多线程的单一MySQL进程中会话的内存使用情况。很多配置是动态的，可以通过set命令更改
  • 全局内存缓冲区变量：key_buffer_size,innodb_buffer_pool_size,innodb_additional_mem_pool_size,query_cache_size
  • 全局/会话内存缓冲区：max_heap_table_size,tmp_table_size
  • 会话缓冲区：join_buffer_size,sort_buffer_size,read_buffer_size,read_md_buffer_size
  • 以上每个字段都有一定的取值范围，超出其大小，可能适得其反

(1)key_buffer_size

  • 只存储MyISAM索引信息的全局内存缓冲区。从对应.MYI中的索引数据从磁盘上被读取出来然后存入这个缓冲区
  • 需要调整key_buffer_size,简单统计一下所有MyISAM表中总索引的大小，进行设置，并不断随着数据累计修改即可，使用LRU算法覆盖旧数据
  • 即使是InnoDB模式，仍需要定义一个索引码缓冲区

(2)命名码缓冲区

  • 除了MyISAM码缓冲区，MySQL还支持其他命名码缓冲区
  • MyISAM能够为索引定义专用的内存池

(3)innodb_buffer_pool_size

  • 用来存储所有InnoDB数据和索引的全局内存缓冲区
  • 针对于完全使用InnoDB引擎的模式来说，这是非常重要参数，需要正确分配
  • 总共有超过50个innodb相关的系统变量

(4)innodb_additional_mem_pool_size

  • 为InnoDB特定数据字典信息定义了内存池

(5)query_cache_size

  • 用来存储经常缓存过得查询全局内存缓冲区
  • 当启用这个变量后，select查询会被自动缓存，可以同SQL_CAHCE/SQL_NO_CACHE进一步确定哪些语句需要被缓存

(6)max_heap_table_size

  • 定义MySQL memory存储引擎表的最大容量

(7)tmp_table_size

  • max_heap_table_size 和 tmp_table_size中的最小值定义了内部临时表的最大容量，内部临时表用于存储在内存中的查询执行过程
  • 如果explain Extra中出现Using Temporary 则这个查询使用了内部临时表，一个SQL可能使用多个内部临时表
  • MySQL通过memory引擎来支持这些内部临时表，如果内部临时表的容量超过max_heap_table_size和tmp_table_size的最小值时，MySQL会临时位置创建一个基于MyISAM磁盘的表
  • 查看是否创建临时表/基于磁盘的临时表，show session status like ‘Create%tables’

(8)join_buffer_size

  • 定义每个线程的内存缓冲区，当查询必须连接两个表的数据集并且不能使用索引时，缓冲区会被用到
  • explain 中Extra 提示Using join buffer证明使用了这个缓冲区
  • 不建议调大，保持默认即可

(9)sort_buffer_size

  • 定义每个线程用于结果集排除的缓冲区
  • explain中Extra 提示Using file-sort证明使用了这个缓冲区
  • 不建议调大，保持默认即可

(10)read_buffer_size

  • 当SQL查询执行连续的表数据扫描时，可以使用这个缓冲区

(11)read_md_buffer_size

  • 用来存储作为排序操作的结果被读取的数据
  • 只有在执行大型orderby 语句时，才推荐调大

3. 基础工具变量

(1) slow_query_log

  • 可以启用执行缓慢的查询日志功能，报告所有执行时间超过long_query_time变量值的查询

(2) slow_query_log_file

  • 定义当慢查询日志功能开启后，保存所有被记录的查询文件的文件名

(3) general_log

  • 用来接六每条查询执行情况的全面查询日志

(4)general_log_file

  • 记录当全面日志启用后，所有SQL查询的文件名

(5)long_query_time

  • 只定了一个查询执行时间的限制，当慢查询日志启用后，执行时间超过这个限制的查询记录会被记录下来

(6)log_output

  • 定义了慢查询日志和全查询日志的输出位置，类型：FILE/TABLE/NONE

(7)profiling

  • 激活细节语句分析，并且提供SQL精确到毫秒级别的执行时间

4.SQL生命周期

(1)截取SQL语句

  • 可以通过以下获取SQL语句：全面查询日志、慢查询日志、二进制日志、进程列表、引擎状态、MySQL连接器、应用程序代码、INFORMATION_SCHEMA、PERFORMANCE_SCHEMA、SQL语句统计信息插件、MYSQL代理、TCP/IP

全面查询日志

  • 适合提供所有连续的SQL查询语句顺序，并不提供执行时间
  • 禁止在生产中启用这个功能

慢查询日志

  • 记录执行时间超过配置限制的查询语句

二进制日志

  • 涵盖所有非select语句，包含DDL/DML。可以提供表级别力度的语句分析

进程列表

  • show full processlist/select * from INFORMATION_SCHEMA.PROCESSLIST

引擎状态

  • show engine status 可提供一些额外SQL信息
  • show engine innodb status 可以提供SQL语句的详细信息

MySQL连接器

应用程序代码

INFORMATION_SCHEMA

PERFORMANCE_SCHEMA

SQL语句统计插件

  • sqlstat

MySQL Proxy

TCP/IP

  • tcpdump 截取后人工解读

5.识别有问题的语句

  • 找到执行频率最高、执行时间最长的语句，是最需要优化的语句
  • mysqldumpslow path….
  • mysqlbinlog xxxx grep
  • maatkit的mk-query-digest工具，能够定位高频率SQL，以及最大最小以及平均执行时间

慢日志分析

TCP/IP分析

6.确认语句执行

7.语句分析

8.语句优化

9.结果验证

10.索引管理优化

(1) 整合DDL语句

  • 多条alter 语句进行合并成一条SQL，可以大幅度提升管理任务的性能

(2)去除重复索引

  • 重复索引会使得DML语句变慢，并且占用更多磁盘空间
  • maatkit mk-duplicate-index-checker

(3)删除不用的索引

  • show index_statistic，分析不常用的索引，进行整理和删除

(4)监控无效的索引

(5)索引列的改进

  • 修改数据类型：bigint(8个字节) -> int（4个字节）,如果可以修改，就不要将字段类型设计过大

  • 修改数据类型：datetime（8个字节，包含日期和时间） -> timstamp （4个字节，默认值0，不支持null）

  • enum 适合存储静态代码值

  • null 和 not null :如果可以设置字段为not null尽量非null,索引可以占用更小空间，不需要使用Null条件筛选

  • 隐含的变换，表连接选择一个索引数据类型时，需要确保这个数据类型是相同的

  • IP（IPV4）地址：可以定义为 int unsigned占用4个字节，INET_ATON()/INET_NTOA()可以进行IP地址与字符串转换，不需要将IP地址字段设置为varchar(15)类型，占用较多空间。IPV6 :binary(16)

  • md5: 可使用char(32)存储，无需使用varchar(32),可以使用UNHEX()/HEX()函数存储在binary(16)数据类型中，仅需占用16字节

  • 减少SQL语句：删除内容重复的SQL,删除重复执行的SQL，删除不必要的SQL语句，缓存SQL结果，应用程序缓存，简化SQL语句（列数，连接操作，子查询，视图）

  • 使用MySQL复制功能，扩展slave节点，增强读可扩展性

11.explain

(1)explain结果字段

id
select_type
table
partitions
possible_keys
key
ref
rows
filtered
Extra
  • key：列出优化器选择使用的索引
  • rows: 分析所有存在于累计结果集中的估计行数
  • possible_keys： 指出优化器为查询选定的索引
  • key_len: 用于SQL语句的连接条件的键的长度
  • table: 一个单独行的唯一标识符（可能是表名、别名、临时表，子查询、派生表）
  • select_type: 标识table列引用的使用方式的类型，有SIMPLE\PRIMARY\DERIVED\UNION等
SIMPLE:不包含子查询和其他复杂语法的简单查询

PRIMARY:可以在DERIVED\UNION类型混合时使用

DERIVED：当表不是一个物理表（临时表、派生表等）

DEPENENT SUBQUERY:使用子查询时定义

UNION： union是一个SQL元素

UNION RESULT :
  • partitions :explain partitions 时出现
  • Extra :提供优化器路径的一系列额外信息
Using where : 使用where语句处理结果

Using temporary:

Using index:

Using file sort:

Using join buffer:

Impossible where 

Select tables potimized away:

Distinct:

Index merges:

  • id

  • ref:

  • filtered:

  • type:

Catalog
    Featured Tags
    工具类 XML JAVA Java Mybatis 反射 代理模式 日志 工厂方法模式 装饰器模式 结果集映射 Web RESTful WebAPI规范 SQL Redis Jira SynapseRT 有限状态机 State Machine statemachine SpringBoot IDEA Tomcat Vue Docker Springboot Apache Ignite 项目发布与调试 白帽子讲web安全 黑名单 通用化 企业中台实践 MacOS docker minio springboot 分布式存储 Postgres 数据备份 数据恢复 K8S 抖音小程序 开发实践 SpringCloud Skywalking Nacos Kubernetes Secret ConfigMap Git GitOps 云原生 RocketMQ ActiveMQ-Artemis ClickHouse Kamailio sipp Homer heplify heplify-server HEP