MySQL分区_MySQL-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

MySQL分区_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:11 PM

分区主要分为垂直分区和水平分区两种：

垂直分表将一个N1+N2个字段的表Tab拆分成N1字段的子表Tab1和(N2+1)字段的子表Tab2；

其中子表Tab2包含了关于子表Tab1的主键信息，否则两个表的关联关系就会丢失。

当然垂直分表会带来程序端SQL的修改，若是应用程序已经应用很长的一段时间，然后程序的升级将是耗时而且易出错的，即升级的代价将会很大。

水平分区技术将一个表拆成多个表，比较常用的方式是将表中的记录按照某种Hash算法进行拆分，

简单的拆分方法如取模方式。

同样，这种分区方法也必须对前端的应用程序中的SQL进行修改方可使用。

而且对于一个SQL，它可能会修改两个表，那么你必须得写成2个SQL语句从而可以完成一个逻辑的事务，使得程序的判断逻辑越来越复杂，这样也会导致程序的维护代价高，也就失去了采用数据库的优势。

因此，分区技术可以有力地避免如上的弊端，成为解决海量数据存储的有力方法。

SHOW VARIABLES命令来确定MySQL是否支持分区

show variables like "%partition%" ; <br /><br />+-------------------+-------+<br />| Variable_name     | Value |<br />+-------------------+-------+<br />| have_partitioning | YES   | <br />+-------------------+-------+<br />

MySQL 分区引擎在一个单独的层中运行，并且可以和任何这样的层进行相互作用

同一个分区表的所有分区必须使用同一个存储引擎

为某个分区表配置一个专门的存储引擎，必须且只能使用[STORAGE] ENGINE 选项。

[STORAGE] ENGINE必须列在用在CREATE TABLE语句中的其他任何分区选项之前

可以通过使用用来创建分区表的CREATE TABLE语句的PARTITION子句的DATA DIRECTORY（数据路径）和INDEX DIRECTORY（索引路径）选项，为每个分区的数据和索引指定特定的路径。

MAX_ROWS和MIN_ROWS选项可以用来设定最大和最小的行数，它们可以各自保存在每个分区里。

分区类型：

Range分区	基于属于一个给定连续区间的列值
List分区	基于列值匹配一个离散值集合中的某个值来进行选择
Hash分区	用户定义表达式的返回值来进行选的分区
Key分区	KEY分区只支持计算一列或多列

分区总是在创建时就自动的顺序编号，且从0开始记录

当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区

参考文献：

http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles