MySQL架构课程学习笔记（一）

最新推荐文章于 2024-04-18 23:26:43 发布

法号轻尘

最新推荐文章于 2024-04-18 23:26:43 发布

阅读量147

点赞数

分类专栏： # 网课笔记文章标签：数据库 python linux mysql java

本文链接：https://blog.csdn.net/qq_36381800/article/details/119536765

版权

网课笔记专栏收录该内容

52 篇文章 0 订阅

订阅专栏

SQL执行原理

1. MySQL架构

2. 连接器

负责管理连接，做权限校验
连接时间超8小时，则会自动断开
连接分为长连接与短连接，推荐使用长连接，避免频繁建立和断开连接

3. 分析器

负责词法分析，语法分析等
识别SQL中每个部分代表的含义
解析语法是否符合语法规则

4. 优化器

负责对SQL进行执行计划分析、索引选择等SQL优化
SQL不同执行方式对执行效率影响很大，因此优化器的作用很关键
当一张表中存在多个索引时，决定用哪个效率高，多表关联时，决定哪种连接顺序效率高

5. 执行器

调用存储引擎，执行SQL，处理返回结果

6. 查询缓存

SQL查询以key-value形式存储在缓存中
相同两条SQL查询，如果命中缓存，则直接返回结果，不走后续阶段
但查询缓存在表数据有更新操作时，则会失效，缓存就会清空，因此一般命中率较低

SQL性能监控

1. show profiles

用于分析当前会话中的SQL执行资源消耗情况
默认关闭，通过set profiling=on;进行开启

使用方式：

show profiles; -- 查看最近15调SQL执行情况

Duration 为执行耗时时间

show profile 可查看详情

支持的参数： ①ALL：显示所有的开销信息。 ②BLOCK IO：显示块IO开销。 ③CONTEXT SWITCHES：上下文切换开销。 ④CPU：显示CPU开销信息。 ⑤IPC：显示发送和JIE收开销信息。 ⑥MEMORY：显示内存开销信息。 ⑦PAGE FAULTS：显示页面错误开销信息。 ⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。 ⑨SWAPS：显示交换次数开销信息。

2. performace schema

5.5版本开始默认提供的一个数据库
用于收集数据库服务器性能相关指标数据
默认为关闭状态，需使用 performance_schema=ON 开启
里面共计默认内置87张张表，包含事件相关，内存相关等信息

mysql> show tables;
+------------------------------------------------------+
| Tables_in_performance_schema                         |
+------------------------------------------------------+
| accounts                                             |
| cond_instances                                       |
| 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_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                                 |
| events_waits_history_long                            |
| events_waits_summary_by_account_by_event_name        |
| events_waits_summary_by_host_by_event_name           |
| events_waits_summary_by_instance                     |
| events_waits_summary_by_thread_by_event_name         |
| events_waits_summary_by_user_by_event_name           |
| events_waits_summary_global_by_event_name            |
| file_instances                                       |
| file_summary_by_event_name                           |
| file_summary_by_instance                             |
| global_status                                        |
| global_variables                                     |
| host_cache                                           |
| hosts                                                |
| memory_summary_by_account_by_event_name              |
| memory_summary_by_host_by_event_name                 |
| memory_summary_by_thread_by_event_name               |
| memory_summary_by_user_by_event_name                 |
| memory_summary_global_by_event_name                  |
| metadata_locks                                       |
| mutex_instances                                      |
| objects_summary_global_by_type                       |
| performance_timers                                   |
| prepared_statements_instances                        |
| replication_applier_configuration                    |
| replication_applier_status                           |
| replication_applier_status_by_coordinator            |
| replication_applier_status_by_worker                 |
| replication_connection_configuration                 |
| replication_connection_status                        |
| replication_group_member_stats                       |
| replication_group_members                            |
| rwlock_instances                                     |
| session_account_connect_attrs                        |
| session_connect_attrs                                |
| session_status                                       |
| session_variables                                    |
| setup_actors                                         |
| setup_consumers                                      |
| setup_instruments                                    |
| setup_objects                                        |
| setup_timers                                         |
| socket_instances                                     |
| socket_summary_by_event_name                         |
| socket_summary_by_instance                           |
| status_by_account                                    |
| status_by_host                                       |
| status_by_thread                                     |
| status_by_user                                       |
| table_handles                                        |
| table_io_waits_summary_by_index_usage                |
| table_io_waits_summary_by_table                      |
| table_lock_waits_summary_by_table                    |
| threads                                              |
| user_variables_by_thread                             |
| users                                                |
| variables_by_thread                                  |

3. show processlist

用于显示客户端正在运行中的线程
Id: 就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。前面我们说了show processlist 显示的信息时来自information_schema.processlist 表，所以这个Id就是这个表的主键。
User: 就是指启动这个线程的用户
Host: 记录了发送请求的客户端的 IP 和端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求
DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL
Command: 是指此刻该线程正在执行的命令。这个很复杂，下面单独解释
Time: 表示该线程处于当前状态的时间。
State: 线程的状态，和 Command 对应
Info: 一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。

command状态

sleep：线程正在等待客户端发送新的请求
query：线程正在执行查询或正在将结果发送给客户端
locked：在mysql的服务层，该线程正在等待表锁
analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
Copying to tmp table：线程正在执行查询，并且将其结果集都复制到一个临时表中
sorting result：线程正在对结果集进行排序
sending data：线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据

数据类型选择

1. 总体原则

满足存储数据需求的前提下，尽量使用占用空间更小
满足需求的前提下，尽量选用数据类型更简单的类型，如能用int就不用varchar
尽量设置字段都不为null，特别是索引列，因为可为null，会使得索引统计和比较的计算更为复杂

2. 重点细则

各种整型类型的比较：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空，尽量选用满足需求最小即可
char与varchar类型：char固定长度，最大长度255，读写速度比varchar高，适合长度较为固定的业务场景，如md5码、其他短字符串；varchar在小于255长度时，使用额外一个字节保存长度，大于后使用2个字节保存；varchar(50)和varchar(255)保存相同内容时，占用磁盘空间相同，但占用内存空间不同
blob与text类型：MySQL将其都当做一个独立对象处理，为较长内容存储而设计，分别采用二进制和字符方式存储数据；
datetime与timestamp类型：dt占用8个字节，tt占用4个，dt可精确到毫秒，保存时间范围大，tt为70年到38年左右，可精确到秒，且依赖数据库时区设置，date类型只需3个字节，比使用字符串、dt、int存储都要小，可存储1000年到9999年之前日期
枚举类型：枚举相比字符串占用空间更小，但一般不常用
其他特殊数据类型：多思考数据的本质，例如IP地址，本质是32位无符号整数，且有inet_aton函数和inet_ntoa函数互转