MySql

语言

版本

Preface and Legal Notices
General Information
- About This Manual
- Typographical and Syntax Conventions
- Overview of the MySQL Database Management System
- What Is New in MySQL 5.7
- Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 5.7
- MySQL Information Sources
- How to Report Bugs or Problems
- MySQL Standards Compliance
- Credits
Installing and Upgrading MySQL
- General Installation Guidance
- Installing MySQL on Unix/Linux Using Generic Binaries
- Installing MySQL on Microsoft Windows
- Installing MySQL on macOS
- Installing MySQL on Linux
- Installing MySQL Using Unbreakable Linux Network (ULN)
- Installing MySQL on Solaris
  - Installing MySQL on Solaris Using a Solaris PKG
- Installing MySQL on FreeBSD
- Installing MySQL from Source
- Postinstallation Setup and Testing
- Upgrading MySQL
- Downgrading MySQL
- Perl Installation Notes
Tutorial
- Connecting to and Disconnecting from the Server
- Entering Queries
- Creating and Using a Database
- Getting Information About Databases and Tables
- Using mysql in Batch Mode
- Examples of Common Queries
- Using MySQL with Apache
MySQL Programs
- Overview of MySQL Programs
- Using MySQL Programs
- Server and Server-Startup Programs
- Installation-Related Programs
- Client Programs
- Administrative and Utility Programs
- Program Development Utilities
- Miscellaneous Programs
- Environment Variables
- Unix Signal Handling in MySQL
MySQL Server Administration
- The MySQL Server
- The MySQL Data Directory
- The mysql System Database
- MySQL Server Logs
- MySQL Server Plugins
- MySQL Server User-Defined Functions
- Running Multiple MySQL Instances on One Machine
- Tracing mysqld Using DTrace
  - mysqld DTrace Probe Reference
Security
- General Security Issues
- Access Control and Account Management
- Using Encrypted Connections
- Security Plugins
- SELinux
Backup and Recovery
- Backup and Recovery Types
- Database Backup Methods
- Example Backup and Recovery Strategy
- Using mysqldump for Backups
- Point-in-Time (Incremental) Recovery
  - Point-in-Time Recovery Using Binary Log
  - Point-in-Time Recovery Using Event Positions
- MyISAM Table Maintenance and Crash Recovery
Optimization
- Optimization Overview
- Optimizing SQL Statements
- Optimization and Indexes
- Optimizing Database Structure
- Optimizing for InnoDB Tables
- Optimizing for MyISAM Tables
- Optimizing for MEMORY Tables
- Understanding the Query Execution Plan
- Controlling the Query Optimizer
- Buffering and Caching
- Optimizing Locking Operations
- Optimizing the MySQL Server
- Measuring Performance (Benchmarking)
- Examining Server Thread (Process) Information
Language Structure
- Literal Values
- Schema Object Names
- Keywords and Reserved Words
- User-Defined Variables
- Expressions
- Comment Syntax
Character Sets, Collations, Unicode
- Character Sets and Collations in General
- Character Sets and Collations in MySQL
  - Character Set Repertoire
  - UTF-8 for Metadata
- Specifying Character Sets and Collations
- Connection Character Sets and Collations
- Configuring Application Character Set and Collation
- Error Message Character Set
- Column Character Set Conversion
- Collation Issues
- Unicode Support
- Supported Character Sets and Collations
- Restrictions on Character Sets
- Setting the Error Message Language
- Adding a Character Set
- Adding a Collation to a Character Set
- Character Set Configuration
- MySQL Server Locale Support
Data Types
- Numeric Data Types
- Date and Time Data Types
- String Data Types
- Spatial Data Types
- The JSON Data Type
- Data Type Default Values
- Data Type Storage Requirements
- Choosing the Right Type for a Column
- Using Data Types from Other Database Engines
Functions and Operators
- Function and Operator Reference
- Type Conversion in Expression Evaluation
- Operators
  - Operator Precedence
  - Comparison Functions and Operators
  - Logical Operators
  - Assignment Operators
- Control Flow Functions
- Numeric Functions and Operators
  - Arithmetic Operators
  - Mathematical Functions
- Date and Time Functions
- String Functions and Operators
  - String Comparison Functions and Operators
  - Regular Expressions
  - Character Set and Collation of Function Results
- What Calendar Is Used By MySQL?
- Full-Text Search Functions
  - Natural Language Full-Text Searches
  - Boolean Full-Text Searches
  - Full-Text Searches with Query Expansion
  - Full-Text Stopwords
  - Full-Text Restrictions
  - Fine-Tuning MySQL Full-Text Search
  - Adding a Collation for Full-Text Indexing
  - ngram Full-Text Parser
  - MeCab Full-Text Parser Plugin
- Cast Functions and Operators
- XML Functions
- Bit Functions and Operators
- Encryption and Compression Functions
- Locking Functions
- Information Functions
- Spatial Analysis Functions
  - Spatial Function Reference
  - Argument Handling by Spatial Functions
  - Functions That Create Geometry Values from WKT Values
  - Functions That Create Geometry Values from WKB Values
  - MySQL-Specific Functions That Create Geometry Values
  - Geometry Format Conversion Functions
  - Geometry Property Functions
  - Spatial Operator Functions
  - Functions That Test Spatial Relations Between Geometry Objects
    - Spatial Relation Functions That Use Object Shapes
    - Spatial Relation Functions That Use Minimum Bounding Rectangles
  - Spatial Geohash Functions
  - Spatial GeoJSON Functions
  - Spatial Convenience Functions
- JSON Functions
  - JSON Function Reference
  - Functions That Create JSON Values
  - Functions That Search JSON Values
  - Functions That Modify JSON Values
  - Functions That Return JSON Value Attributes
  - JSON Utility Functions
- Functions Used with Global Transaction Identifiers (GTIDs)
- MySQL Enterprise Encryption Functions
  - MySQL Enterprise Encryption Installation
  - MySQL Enterprise Encryption Usage and Examples
  - MySQL Enterprise Encryption Function Reference
  - MySQL Enterprise Encryption Function Descriptions
- Aggregate Functions
  - Aggregate Function Descriptions
  - GROUP BY Modifiers
  - MySQL Handling of GROUP BY
  - Detection of Functional Dependence
- Miscellaneous Functions
- Precision Math
  - Types of Numeric Values
  - DECIMAL Data Type Characteristics
  - Expression Handling
  - Rounding Behavior
  - Precision Math Examples
SQL Statements
- Data Definition Statements
- Data Manipulation Statements
- Transactional and Locking Statements
- Replication Statements
- Prepared Statements
- Compound Statements
- Database Administration Statements
- Utility Statements
The InnoDB Storage Engine
- Introduction to InnoDB
  - Benefits of Using InnoDB Tables
  - Best Practices for InnoDB Tables
  - Verifying that InnoDB is the Default Storage Engine
  - Testing and Benchmarking with InnoDB
  - Turning Off InnoDB
- InnoDB and the ACID Model
- InnoDB Multi-Versioning
- InnoDB Architecture
- InnoDB In-Memory Structures
  - Buffer Pool
  - Change Buffer
  - Adaptive Hash Index
  - Log Buffer
- InnoDB On-Disk Structures
  - Tables
  - Indexes
  - Tablespaces
  - InnoDB Data Dictionary
  - Doublewrite Buffer
  - Redo Log
  - Undo Logs
- InnoDB Locking and Transaction Model
  - InnoDB Locking
  - InnoDB Transaction Model
  - Locks Set by Different SQL Statements in InnoDB
  - Phantom Rows
  - Deadlocks in InnoDB
- InnoDB Configuration
  - InnoDB Startup Configuration
  - Configuring InnoDB for Read-Only Operation
  - InnoDB Buffer Pool Configuration
  - Configuring the Memory Allocator for InnoDB
  - Configuring Thread Concurrency for InnoDB
  - Configuring the Number of Background InnoDB I/O Threads
  - Using Asynchronous I/O on Linux
  - Configuring InnoDB I/O Capacity
  - Configuring Spin Lock Polling
  - Purge Configuration
  - Configuring Optimizer Statistics for InnoDB
  - Configuring the Merge Threshold for Index Pages
- InnoDB Table and Page Compression
  - InnoDB Table Compression
  - InnoDB Page Compression
- InnoDB File-Format Management
  - Enabling File Formats
  - Verifying File Format Compatibility
    - Compatibility Check When InnoDB Is Started
    - Compatibility Check When a Table Is Opened
  - Identifying the File Format in Use
  - Modifying the File Format
- InnoDB Row Formats
- InnoDB Disk I/O and File Space Management
  - InnoDB Disk I/O
  - File Space Management
  - InnoDB Checkpoints
  - Defragmenting a Table
  - Reclaiming Disk Space with TRUNCATE TABLE
- InnoDB and Online DDL
  - Online DDL Operations
  - Online DDL Performance and Concurrency
  - Online DDL Space Requirements
  - Simplifying DDL Statements with Online DDL
  - Online DDL Failure Conditions
  - Online DDL Limitations
- InnoDB Data-at-Rest Encryption
- InnoDB Startup Options and System Variables
- InnoDB INFORMATION_SCHEMA Tables
  - InnoDB INFORMATION_SCHEMA Tables about Compression
  - InnoDB INFORMATION_SCHEMA Transaction and Locking Information
  - InnoDB INFORMATION_SCHEMA System Tables
  - InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables
  - InnoDB INFORMATION_SCHEMA Buffer Pool Tables
  - InnoDB INFORMATION_SCHEMA Metrics Table
  - InnoDB INFORMATION_SCHEMA Temporary Table Info Table
  - Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES
- InnoDB Integration with MySQL Performance Schema
  - Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema
  - Monitoring InnoDB Mutex Waits Using Performance Schema
- InnoDB Monitors
  - InnoDB Monitor Types
  - Enabling InnoDB Monitors
  - InnoDB Standard Monitor and Lock Monitor Output
- InnoDB Backup and Recovery
  - InnoDB Backup
  - InnoDB Recovery
- InnoDB and MySQL Replication
- InnoDB memcached Plugin
  - Benefits of the InnoDB memcached Plugin
  - InnoDB memcached Architecture
  - Setting Up the InnoDB memcached Plugin
  - Security Considerations for the InnoDB memcached Plugin
  - Writing Applications for the InnoDB memcached Plugin
  - The InnoDB memcached Plugin and Replication
  - InnoDB memcached Plugin Internals
  - Troubleshooting the InnoDB memcached Plugin
- InnoDB Troubleshooting
  - Troubleshooting InnoDB I/O Problems
  - Forcing InnoDB Recovery
  - Troubleshooting InnoDB Data Dictionary Operations
  - InnoDB Error Handling
- InnoDB Limits
- InnoDB Restrictions and Limitations
Alternative Storage Engines
- Setting the Storage Engine
- The MyISAM Storage Engine
- The MEMORY Storage Engine
- The CSV Storage Engine
  - Repairing and Checking CSV Tables
  - CSV Limitations
- The ARCHIVE Storage Engine
- The BLACKHOLE Storage Engine
- The MERGE Storage Engine
  - MERGE Table Advantages and Disadvantages
  - MERGE Table Problems
- The FEDERATED Storage Engine
- The EXAMPLE Storage Engine
- Other Storage Engines
- Overview of MySQL Storage Engine Architecture
  - Pluggable Storage Engine Architecture
  - The Common Database Server Layer
Replication
- Configuring Replication
- Replication Implementation
- Replication Solutions
- Replication Notes and Tips
Group Replication
- Group Replication Background
- Getting Started
  - Deploying Group Replication in Single-Primary Mode
  - Deploying Group Replication Locally
- Monitoring Group Replication
- Group Replication Operations
- Group Replication Security
- Group Replication System Variables
- Requirements and Limitations
  - Group Replication Requirements
  - Group Replication Limitations
- Frequently Asked Questions
- Group Replication Technical Details
MySQL Shell
Using MySQL as a Document Store
- Key Concepts
- Setting Up MySQL as a Document Store
  - Installing MySQL Shell
  - Starting MySQL Shell
- Quick-Start Guide: MySQL for Visual Studio
- X Plugin
InnoDB Cluster
- Introducing InnoDB Cluster
- Creating an InnoDB Cluster
- Using MySQL Router with InnoDB Cluster
- Working with InnoDB Cluster
- Known Limitations
MySQL NDB Cluster 7.5 and NDB Cluster 7.6
- NDB Cluster Overview
- NDB Cluster Installation
- Configuration of NDB Cluster
- NDB Cluster Programs
- Management of NDB Cluster
- NDB Cluster Replication
- NDB Cluster Release Notes
Partitioning
- Overview of Partitioning in MySQL
- Partitioning Types
- Partition Management
- Partition Pruning
- Partition Selection
- Restrictions and Limitations on Partitioning
Stored Objects
- Defining Stored Programs
- Using Stored Routines
- Using Triggers
  - Trigger Syntax and Examples
  - Trigger Metadata
- Using the Event Scheduler
- Using Views
- Stored Object Access Control
- Stored Program Binary Logging
- Restrictions on Stored Programs
- Restrictions on Views
INFORMATION_SCHEMA Tables
- Introduction
- The INFORMATION_SCHEMA CHARACTER_SETS Table
- The INFORMATION_SCHEMA COLLATIONS Table
- The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
- The INFORMATION_SCHEMA COLUMNS Table
- The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
- The INFORMATION_SCHEMA ENGINES Table
- The INFORMATION_SCHEMA EVENTS Table
- The INFORMATION_SCHEMA FILES Table
- The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables
- The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables
- The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
- The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table
- The INFORMATION_SCHEMA OPTIMIZER_TRACE Table
- The INFORMATION_SCHEMA PARAMETERS Table
- The INFORMATION_SCHEMA PARTITIONS Table
- The INFORMATION_SCHEMA PLUGINS Table
- The INFORMATION_SCHEMA PROCESSLIST Table
- The INFORMATION_SCHEMA PROFILING Table
- The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
- The INFORMATION_SCHEMA ROUTINES Table
- The INFORMATION_SCHEMA SCHEMATA Table
- The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
- The INFORMATION_SCHEMA STATISTICS Table
- The INFORMATION_SCHEMA TABLES Table
- The INFORMATION_SCHEMA TABLESPACES Table
- The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
- The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
- The INFORMATION_SCHEMA TRIGGERS Table
- The INFORMATION_SCHEMA USER_PRIVILEGES Table
- The INFORMATION_SCHEMA VIEWS Table
- INFORMATION_SCHEMA InnoDB Tables
  - The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
  - The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
  - The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
  - The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
  - The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
  - The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
  - The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
  - The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
  - The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
  - The INFORMATION_SCHEMA INNODB_FT_DELETED Table
  - The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
  - The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
  - The INFORMATION_SCHEMA INNODB_LOCKS Table
  - The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
  - The INFORMATION_SCHEMA INNODB_METRICS Table
  - The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table
  - The INFORMATION_SCHEMA INNODB_SYS_DATAFILES Table
  - The INFORMATION_SCHEMA INNODB_SYS_FIELDS Table
  - The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table
  - The INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS Table
  - The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table
  - The INFORMATION_SCHEMA INNODB_SYS_TABLES Table
  - The INFORMATION_SCHEMA INNODB_SYS_TABLESPACES Table
  - The INFORMATION_SCHEMA INNODB_SYS_TABLESTATS View
  - The INFORMATION_SCHEMA INNODB_SYS_VIRTUAL Table
  - The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table
  - The INFORMATION_SCHEMA INNODB_TRX Table
- INFORMATION_SCHEMA Thread Pool Tables
  - The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table
  - The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table
  - The INFORMATION_SCHEMA TP_THREAD_STATE Table
- INFORMATION_SCHEMA Connection-Control Tables
  - The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table
- Extensions to SHOW Statements
MySQL Performance Schema
- Performance Schema Quick Start
- Performance Schema Build Configuration
- Performance Schema Startup Configuration
- Performance Schema Runtime Configuration
  - Performance Schema Event Timing
  - Performance Schema Event Filtering
  - Event Pre-Filtering
  - Pre-Filtering by Instrument
  - Pre-Filtering by Object
  - Pre-Filtering by Thread
  - Pre-Filtering by Consumer
  - Example Consumer Configurations
  - Naming Instruments or Consumers for Filtering Operations
  - Determining What Is Instrumented
- Performance Schema Queries
- Performance Schema Instrument Naming Conventions
- Performance Schema Status Monitoring
- Performance Schema Atom and Molecule Events
- Performance Schema Tables for Current and Historical Events
- Performance Schema Statement Digests
- Performance Schema General Table Characteristics
- Performance Schema Table Descriptions
  - Performance Schema Table Index
  - Performance Schema Setup Tables
  - Performance Schema Instance Tables
  - Performance Schema Wait Event Tables
  - Performance Schema Stage Event Tables
  - Performance Schema Statement Event Tables
  - Performance Schema Transaction Tables
  - Performance Schema Connection Tables
  - Performance Schema Connection Attribute Tables
    - The session_account_connect_attrs Table
    - The session_connect_attrs Table
  - Performance Schema User-Defined Variable Tables
  - Performance Schema Replication Tables
  - Performance Schema Lock Tables
    - The metadata_locks Table
    - The table_handles Table
  - Performance Schema System Variable Tables
  - Performance Schema Status Variable Tables
  - Performance Schema Summary Tables
  - Performance Schema Miscellaneous Tables
- Performance Schema Option and Variable Reference
- Performance Schema Command Options
- Performance Schema System Variables
- Performance Schema Status Variables
- The Performance Schema Memory-Allocation Model
- Performance Schema and Plugins
- Using the Performance Schema to Diagnose Problems
  - Query Profiling Using Performance Schema
- Migrating to Performance Schema System and Status Variable Tables
- Restrictions on Performance Schema
MySQL sys Schema
- Prerequisites for Using the sys Schema
- Using the sys Schema
- sys Schema Progress Reporting
- sys Schema Object Reference
Connectors and APIs
- MySQL Connector/C++
- MySQL Connector/J
- MySQL Connector/NET
- MySQL Connector/ODBC
- MySQL Connector/Python
- libmysqld, the Embedded MySQL Server Library
- MySQL C API
- MySQL PHP API
- MySQL Perl API
- MySQL Python API
- MySQL Ruby APIs
  - The MySQL/Ruby API
  - The Ruby/MySQL API
- MySQL Tcl API
- MySQL Eiffel Wrapper
Extending MySQL
- MySQL Internals
  - MySQL Threads
  - The MySQL Test Suite
- The MySQL Plugin API
- MySQL Services for Plugins
  - The Locking Service
    - The Locking Service C Interface
    - The Locking Service UDF Interface
  - The Keyring Service
- Adding Functions to MySQL
- Debugging and Porting MySQL
MySQL Enterprise Edition
- MySQL Enterprise Monitor Overview
- MySQL Enterprise Backup Overview
- MySQL Enterprise Security Overview
- MySQL Enterprise Encryption Overview
- MySQL Enterprise Audit Overview
- MySQL Enterprise Firewall Overview
- MySQL Enterprise Thread Pool Overview
- MySQL Enterprise Data Masking and De-Identification Overview
MySQL Workbench
MySQL 5.7 Frequently Asked Questions
- MySQL 5.7 FAQ: General
- MySQL 5.7 FAQ: Storage Engines
- MySQL 5.7 FAQ: Server SQL Mode
- MySQL 5.7 FAQ: Stored Procedures and Functions
- MySQL 5.7 FAQ: Triggers
- MySQL 5.7 FAQ: Views
- MySQL 5.7 FAQ: INFORMATION_SCHEMA
- MySQL 5.7 FAQ: Migration
- MySQL 5.7 FAQ: Security
- MySQL 5.7 FAQ: NDB Cluster
- MySQL 5.7 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
- MySQL 5.7 FAQ: Connectors & APIs
- MySQL 5.7 FAQ: C API, libmysql
- MySQL 5.7 FAQ: Replication
- MySQL 5.7 FAQ: MySQL Enterprise Thread Pool
- MySQL 5.7 FAQ: InnoDB Change Buffer
- MySQL 5.7 FAQ: InnoDB Data-at-Rest Encryption
- MySQL 5.7 FAQ: Virtualization Support
Error Messages and Common Problems
- Error Message Sources and Components
- Error Information Interfaces
- Problems and Common Errors
Indexes
- General Index
- Function Index
- Command Index
- Function Index
- INFORMATION_SCHEMA Index
- Join Types Index
- Operator Index
- Option Index
- Privileges Index
- Modes Index
- Statement/Syntax Index
- Status Variable Index
- System Variable Index
- Transaction Isolation Level Index
MySQL Glossary

14.6 InnoDB On-Disk Structures

14.6.1 Tables
14.6.2 Indexes
14.6.3 Tablespaces
14.6.4 InnoDB Data Dictionary
14.6.5 Doublewrite Buffer
14.6.6 Redo Log
14.6.7 Undo Logs

This section describes InnoDB on-disk structures and related topics.

首页