深入理解MySQL内部机制

2星需积分: 16 127 浏览量更新于2024-07-20 收藏 1.01MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

"MySQL Internals Manual，英文版，适合学习收藏" 《MySQL Internals Manual》是MySQL数据库系统的一份深入技术文档，主要面向开发者、DBA和技术爱好者，它详细揭示了MySQL内部的工作机制和实现原理。这份手册的创建日期为2006年10月18日，修订版本为3666，由MySQL AB公司编著，并保留所有权利。在该手册中，读者可以了解到以下关键知识点： 1. **存储引擎**：MySQL支持多种存储引擎，如InnoDB、MyISAM、Memory等。手册会详细解析每个引擎的特性、数据存储方式以及事务处理机制，帮助读者理解如何选择和优化不同的存储引擎。 2. **查询优化**：手册会介绍MySQL如何解析SQL语句，包括查询优化器的工作原理，如查询计划的选择、索引的使用、JOIN操作的优化等，这对于提升数据库性能至关重要。 3. **并发控制与事务处理**：对于InnoDB引擎，手册会详细阐述行级锁定机制、MVCC（多版本并发控制）以及回滚段等概念，这些都是高并发环境下保证数据一致性的基础。 4. **内存管理**：MySQL如何管理内存资源，包括缓冲池（Buffer Pool）、线程池等，以及如何调整这些参数以优化性能。 5. **复制技术**：MySQL的主从复制是其扩展性和高可用性的重要组成部分，手册会详细讲解复制的工作流程、配置方法和故障恢复策略。 6. **日志系统**：包括redo log（重做日志）和binlog（二进制日志），它们在事务持久化和复制中的作用，以及如何进行日志管理和优化。 7. **锁机制**：MySQL的各种锁类型（如表锁、行锁、元数据锁等）及其应用，有助于理解和解决并发问题。 8. **物理存储**：表结构、分区、索引的物理组织形式，包括数据页、B+树等，这些对理解查询性能至关重要。 9. **性能监控和调优**：手册可能会提供监控工具和指标的介绍，帮助读者诊断性能瓶颈并进行调优。 10. **系统架构**：MySQL服务器的整体架构，包括客户端/服务器通信协议、插件式架构等，有助于理解MySQL的可扩展性和模块化设计。由于这是一份内部手册，它通常包含更深入的技术细节，适合有经验的MySQL使用者或希望深入了解MySQL工作原理的人阅读。虽然内容为英文，但对熟悉数据库原理和技术人员来说，是不可多得的学习资料。在遵循MySQL AB的许可条款下，可以转换文档格式以便个人学习和使用，但不得未经许可进行公开发布或商业利用。

资源详情

资源推荐

Row handling programs on mysql-5.0/myisam:

size name comment

---- ---- -------

29064 mi_delete.c for deleting

2562 mi_delete_all.c for deleting all

6797 mi_update.c for updating

32613 mi_write.c for inserting

+ more mi_*.c programs

Drilling down a bit, you'll also find programs in the myisam directory that handle deleting, updating, and

inserting of rows. The only one that's a little hard to find is the program for inserting rows, which we've

called mi_write.c instead of mi_insert.c.

Key handling programs on mysql-5.0/myisam:

size name comment

---- ---- -------

4668 mi_rkey.c for random key searches

3646 mi_rnext.c for next-key searches

15440 mi_key.c for managing keys

+ more mi_*.c programs

The final notable group of files in the myisam directory is the group that handles keys in indexes.

To sum up: (1) The myisam directory is where you'll find programs for handling files, rows, and keys.

You won't find programs for handling columns — we'll get to them a bit later. (2) The myisam directory

is just one of the handler directories. The programs in the other storage engine directories fulfill about

the same functions.

1.2.5. Major Directories: mysys

The next major directory is labelled mysys, which stands for MySQL System Library. This is the tool-

box directory, for example it has low level routines for file access. The .c files in mysys have procedures

and functions that are handy for calling by main programs, for example by the programs in the myisam

directory. There are 115 .c files in mysys, so we only can note a sampling.

Sampling of programs on mysql-5.0/mysys

size name comment

---- ---- -------

17684 charset.c character sets

6165 mf_qsort.c quicksort

5609 mf__tempfile.c temporary files

+ 112 more *.c programs

Example one: with charset.c routines, you can change the character set.

Example two: mf_qsort.c contains our quicksort package.

Example three: mf_tempfile.c has what's needed for maintaining MySQL's temporary files.

You can see from these examples that mysys is a hodgepodge. That's why we went to the trouble of pro-

ducing extra documentation in this document to help you analyze mysys's contents.

1.2.6. Major Directories: sql

The next major directory is mysql-5.0/sql. If you remember your manual, you know that you must pro-

nounce this: ess queue ell.

The "parser" programs on mysql-5.0/sql:

A Guided Tour Of The MySQL Source Code

size name comment

---- ---- -------

51326 sql_lex.cc lexer

230026 sql_yacc.yy parser

+ many more *.cc programs

This is where we keep the parser. In other words, programs like sql_lex.cc and sql_yacc.yy are respons-

ible for figuring out what's in an SQL command, and deciding what to do about it.

The "handler" programs on mysql-5.0/sql:

size name comment

---- ---- -------

79798 ha_berkeley.cc bdb

56687 ha_federated.cc federated (sql/med)

61033 ha_heap.cc heap (memory)

214046 ha_innodb.cc innodb

47361 ha_myisam.cc myisam

14727 ha_myisammrg.cc merge

215091 ha_ndbcluster.cc ndb

This is also where we keep the handler programs. Now, you'll recall that the storage engine itself, for ex-

ample myisam, is a separate directory. But here in the sql directory, we have programs which are re-

sponsible for determining which handler to call, formatting appropriate arguments, and checking results.

In other words, the programs that begin with the letters ha are the handler interface programs, and there's

one for each storage engine.

The "statement" routines in mysql-5.0/sql:

size name comment

---- ---- -------

24212 sql_delete.cc 'delete ...' statement

1217 sql_do.cc 'do ...'

22362 sql_help.cc 'help ...'

75331 sql_insert.cc 'insert ...'

430486 sql_select.cc 'select ...'

130861 sql_show.cc 'show ...'

42346 sql_update.cc 'update ...'

+ many more sql_*.cc programs

Also in the sql directory, you'll find individual programs for handling each of the syntactical compon-

ents of an SQL statement. These programs tend to have names beginning with sql_. So for the SELECT

statement, check out sql_select.cc.

Thus, there are "statement" routines like sql_delete.c, sql_load.c, and sql_help.c, which take care of the

DELETE, LOAD DATA, and HELP statements. The file names are hints about the SQL statements in-

volved.

The "statement function" routines in mysql-5.0/sql:

size name comment

---- ---- -------

19906 sql_string.cc strings

6152 sql_olap.cc olap (rollup)

14241 sql_udf.cc user-defined functions

17669 sql_union.cc unions

Then there are the routines for components of statements, such as strings, or online analytical processing

which at this moment just means ROLLUP, or user-defined functions, or the UNION operator.

1.2.7. Major Directories: vio

The final major directory that we'll highlight is labelled vio, for "virtual I/O".

A Guided Tour Of The MySQL Source Code

The vio routines are wrappers for the various network I/O calls that happen with different protocols. The

idea is that in the main modules one won't have to write separate bits of code for each protocol. Thus

vio's purpose is somewhat like the purpose of Microsoft's winsock library.

That wraps up our quick look at the seven major directories. Just one summary chart remains to do.

1.3. The Flow

This is a diagram of the flow.

User enters "INSERT" statement /* client */

Message goes over TCP/IP line /* vio, various */

Server parses statement /* sql */

Server calls low-level functions /* myisam */

Handler stores in file /* mysys */

The diagram is very simplified — it's a caricature that distorts important things, but remember that we've

only discussed seven major directories so far: Docs, BUILD, and the five that you see here.

The flow works like this:

First, the client routines get an SQL statement from a user, allowing edit, performing initial checks, and

so on.

Then, via the vio routines, the somewhat-massaged statement goes off to the server.

Next, the sql routines handle the parsing and call what's necessary for each individual part of the state-

ment. Along the way, the sql routines will be calling the low level mysys routines frequently.

Finally, one of the ha (handler) programs in the sql directory will dispatch to an appropriate handler for

storage. In this case we've assumed, as before, that the handler is myisam — so a myisam-directory pro-

gram is involved. Specifically, that program is mi_write.c, as we mentioned earlier.

Simple, eh?

1.4. The Open-source Directories

We're now getting into the directories which aren't "major". Starting with:

dbug

pstack

regex

strings

zlib

Now it's time to reveal a startling fact, which is — we didn't write all of the source code in all of the

source code directories all by ourselves. This list is, in a sense, a tribute to the idea of open source.

There's dbug, which is Fred Fish's debug library.

There's pstack, which displays the process stack.

There's regex, which is what we use for our regular expressions function.

A Guided Tour Of The MySQL Source Code

There's strings, the meaning of which is obvious.

There's zlib, which is for Zempel-Liv compression.

All of the programs in these directories were supplied by others, as open source code. We didn't just take

them, of course. MySQL has checked and changed what's in these directories. But we acknowledge with

thanks that they're the products of other projects, and other people's labor, and we only regret that we

won't have time to note all the contributed or publicly available components of MySQL, in this manual.

1.5. The Internal and External Storage Engine Director-

ies

Continuing with our extract from the directory list ...

bdb /* external */

heap

innobase /* external */

myisam

myisammrg

ndb

Let's go through the idea of storage engines once more, this time with a list of all the storage engines,

both the ones that we produce, and the ones that others produce. We've already mentioned the internal

ones — so now we'll remark on the directories of the two common external storage engines — BDB and

innobase.

The BDB, or Berkeley Database, handler, is strictly the product of Sleepycat software. Sleepycat has a

web page at sleepycat.com, which contains, among other things, documentation for their product. So

you can download Sleepycat's own documentation of the source code in the BDB directory.

As for the innobase handler, which many of you probably use, you'll be happy to know that the com-

ments in the files are reasonably clear (the InnoBase Oy people are pretty strict about comments). There

are two chapters about it in this document.

1.6. The "OS Specific" Directories

netware

NEW-RPMS

os2

VC++Files

A few words are in order about the directories that contain files which relate to a particular environment

that MySQL can run in.

The netware directory contains a set of files for interfacing with netware, and anyone who has an in-

volvement with NetWare knows that we're allied with them, and so this is one of the directories that rep-

resents the joint enterprise.

The NEW-RPMS directory (empty at time of writing) is for Linux, and the os2 directory is for OS/2.

Finally, the VC++Files directory is for Windows. We've found that the majority of Windows program-

mers who download and build MySQL from source use Microsoft Visual C. In the VC++Files directory

you will find a nearly complete replication of what's in all the other directories that we've discussed, ex-

cept that the .c files are modified to account for the quirks of Microsoft tools.

Without endorsing by particular names, we should note that other compilers from other manufacturers

also work.

A Guided Tour Of The MySQL Source Code

剩余250页未读，继续阅读

xczzmn

粉丝: 15
资源: 68

深入理解MySQL内部机制

MySQL Manual

MySQL Internals Manual.pdf

mysql 推荐书籍

有没有关于gdb调试mysql源码的官方文档，有的话，麻烦提供下地址，要可以访问的地址

mysql的caching_sha2_password登录，相关网络包的官方文档，要详细一点。

ModuleNotFoundError: No module named 'cartopy.internals'

understanding linux network internals pdf

ocean base 数据库书籍推荐

unix internals pdf

java flink mysql binlog 代码 带pom.xml

chrome://webrtc-internals 怎么打开

windows internals 7th part2

windows internals 7th

翻译 internals

chrome://webrtc-internals/是什么

java flink mysql binlog 代码

运行wpf窗体后点击button按钮显示"未加载System.ServiceModel.Internals.pdb"是什么原因

rome://net-internals/#hsts

chrome://net-internals/#hsts

使用chrome://webrtc-internals/ 发现很多webrtc未关闭

最新资源

java flink mysql binlog 代码带pom.xml