bitsCN.com
MySQL分区概述:
允许根据可以设置为任意大小的规则,跨文件系统分配单个表的多个部分。实际上,表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数,这在MySQL中它可以是模数,或者是简单的匹配一个连续的数值区间或数值列表,或者是一个内部HASH函数,或一个线性HASH函数。函数根据用户指定的分区类型来选择,把用户提供的表达式的值作为参数。该表达式可以是一个整数列值,或一个作用在一个或多个列值上并返回一个整数的函数。[z1] 。这个表达式的值传递给分区函数,分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数,也不能是任意数。它不能包含任何查询,但是实际上可以使用MySQL 中任何可用的SQL表达式,只要该表达式返回一个小于MAXVALUE(最大可能的正整数)的正数值。
由于MySQL无全局索引的概念,只有本地分区索引,基于此种原因,一个表中如果有2个或2个以上的唯一索引,此表无法分区。分区函数应用的数据库的列必须是MySQL的主键,否则不能分区。[z2]
对于创建了分区的表,可以使用你的MySQL 服务器所支持的任何存储引擎。在MySQL 5.1版中,同一个分区表的所有分区必须使用同一个存储引擎;例如,不能对一个分区使用MyISAM,而对另一个使用InnoDB。但是,这并不妨碍在同一个 MySQL 服务器中,甚至在同一个数据库中,对于不同的分区表使用不同的存储引擎。
MySQL分区的建立
MySQL可以建立四种分区类型的分区:
RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。详情参见18.2.1节,“RANGE分区”。
· LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。详情参见18.2.2节,“LIST分区”。
· HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。详情参见18.2.3节,“HASH分区”。
· KEY 分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。详情参照:18.2.4. KEY分区。
子分区:子分区是分区表中每个分区的再次分割。书写格式参照:18.2.5. 子分区
(1)关于子分区应注意的地方: 每个分区必须有相同数量的子分区。
· (2)如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区,那么就必须定义所有的子分区。
在建立分区的时候可以指定分区的数据存储位置和索引位置,这样可以跨磁盘或者文件系统保存不同的数据。数据分磁盘存储可以一定程度上增加数据读取速度,因为采用多磁盘后,每个磁盘的I/O操作会降低。而且采用指定分区存储位置能够增大存储量。
无论使用何种类型的分区,分区总是在创建时就自动的顺序编号,且从0开始记录,记住这一点非常重要。当有一新行插入到一个分区表中时,就是使用这些分区编号来识别正确的分区。例如,如果你的表使用4个分区,那么这些分区就编号为0, 1, 2, 和3。对于RANGE和LIST分区类型,确认每个分区编号都定义了一个分区,很有必要。对HASH分区,使用的用户函数必须返回一个大于0的整数值。对于KEY分区,这个问题通过MySQL服务器内部使用的 哈希函数自动进行处理。注意:分区的名字是不区分大小写的,且对于RANGE分区和LIST分区,分区的名称是不能重复的。这几种可根据不同的需求来选择,比较常用的是RANGE分区。
常用的MySQL的分区管理:
RANGE 和LIST分区管理
分区对于程序来说是透明的,而且只有删除能在分区层面上操作,其他如查询、修改、增加都不能指定分区。
ALTER TABLE …DROPPARTITION ….(删除分区)
ALTER TABLE … ADD PARTITION (PARTITION p3 VALUESLESS THAN (…));[z3] 增加分区
ALTER TABLE ... REORGANIZE PARTITION …,… INTO (
PARTITION p0 VALUES LESS THAN (…)
);[z4] 合并拆分分区。
HASH和KEY分区管理
添加分区和RANGE、LIST分区方式相同,对于修改分区,不能使用与从按照RANGE或LIST分区的表中删除分区相同的方式,来从HASH或KEY分区的表中删除分区。但是,可以使用“ALTERTABLE ... COALESCE PARTITION”命令来合并HASH或KEY分区。
如果要查看分区的信息,可以通过sql语句来查询
SELECT * FROM INFORMATION_SCHEMA.partitions WHERETABLE_SCHEMA = schema() AND TABLE_NAME='xxx’
分区表效率比较
MySQL分区表实验
分区采用红色,不分区采用蓝色
测试环境:CentOS 虚拟机,1G内存,20G硬盘
实验数据库:test 不分区(内有1张表RPT_MALEVENTS)、test2(与test一样)
背景数据:
mysql> SELECT COUNT(*)FROM RPT_MALEVENTS;
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (10.84 sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS;
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (14.63sec)
数据分布:2011/8/4~2011/8/17
分区表结构:
CREATETABLE `RPT_MALEVENTS` (
`RECORD_DATE` date NOT NULL,
`RECORD_HOUR` tinyint(2) NOT NULL,
`RECORD_MINUTE` tinyint(2) NOT NULL,
`RECORD_DATETIME` datetime NOT NULL,
`MC_IP` int(10) unsigned NOT NULL,
`PC_IP` int(10) unsigned NOT NULL,
`NETOBJECT_GROUP_ID` smallint(5) DEFAULTNULL,
`ALERT_TYPE` tinyint(3) NOT NULL,
`SUB_TYPE` smallint(5) NOT NULL,
`SHOW_TYPE` smallint(5) NOT NULL,
`ALERT_ID` tinyint(3) NOT NULL,
`EVENT_COUNT` int(10) unsigned DEFAULT NULL,
PRIMARY KEY(`RECORD_DATE`,`RECORD_HOUR`,`RECORD_MINUTE`,`MC_IP`,`PC_IP`,`ALERT_TYPE`,`SUB_TYPE`,`ALERT_ID`),
KEY `RECORD_DATETIME` (`RECORD_DATETIME`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci /*!50100 PARTITIONBY RANGE (TO_DAYS(RECORD_DATE)[z1] ) (PARTITION p2011 VALUES LESS THAN (734503)ENGINE = InnoDB, PARTITION p20110809 VALUES LESS THAN (734724) ENGINE = InnoDB,PARTITION p20110810 VALUES LESS THAN (734725) ENGINE = InnoDB, PARTITIONp20110811 VALUES LESS THAN (734726) ENGINE = InnoDB, PARTITION p20110812 VALUESLESS THAN (734727) ENGINE = InnoDB, PARTITION p20110813 VALUES LESS THAN(734728) ENGINE = InnoDB, PARTITION p20110814 VALUES LESS THAN (734729) ENGINE= InnoDB, PARTITION p20110815 VALUES LESS THAN (734730) ENGINE = InnoDB,PARTITION p20110816 VALUES LESS THAN (734731) ENGINE = InnoDB, PARTITIONp20110817 VALUES LESS THAN (734732) ENGINE = InnoDB, PARTITION p20110818 VALUESLESS THAN (734733) ENGINE = InnoDB, PARTITION pMax VALUES LESS THAN MAXVALUE[z2] ENGINE = InnoDB)
分区表的物理存储如下,当前用的是innodB的存储引擎,采用分表结构
分析如下
(条件查询查询全部数据)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-01' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (21.62sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-01' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (29.20sec)
(查询部分数据,不使用分区函数使用的列)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-02' ANDRECORD_DATETIME +----------+
| COUNT(*) |
+----------+
| 5083194 |
+----------+
1 row in set (2.83sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-02' AND RECORD_DATETIME +----------+
| COUNT(*) |
+----------+
| 5083194 |
+----------+
1 row in set (5.60sec)
(使用其他条件查询部分数据)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE ALERT_TYPE = 1;
+----------+
| COUNT(*) |
+----------+
| 88739 |
+----------+
1 row in set (8.49sec)
SELECT COUNT(*) FROMRPT_MALEVENTS WHERE ALERT_TYPE = 1;
+----------+
| COUNT(*) |
+----------+
| 88739 |
+----------+
1 row in set (12.88sec)
(小范围查询,在一个分区内查询)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-13' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 2116249 |
+----------+
1 row in set (1.85sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-13' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 2116249 |
+----------+
1 row in set (3.10sec)
分析SQL语句的执行过程
rows表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数。
mysql>EXPLAIN PARTITIONS SELECT * FROMRPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-12' AND RECORD_DATETIME ***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p2011,p20110809,p20110810,p20110811,p20110812,p20110813,p20110814,p20110815,p2011[z3] 0816,p20110817,p20110818,pMax
type: range
possible_keys:RECORD_DATETIME
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 355911[z4]
Extra: Using where
1 row in set (0.00sec)
mysql> EXPLAIN SELECT * FROM RPT_MALEVENTS WHERERECORD_DATETIME > '2011-08-12' AND RECORD_DATETIME ***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
type: range
possible_keys:RECORD_DATETIME
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 1002288[z5]
Extra: Using where
1 row in set (0.00sec)
与分区函数使用列无关的查询条件
mysql>EXPLAIN PARTITIONS SELECT COUNT(*) FROMRPT_MALEVENTS WHERE ALERT_TYPE = 1/G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p2011,p20110809,p20110810,p20110811,p20110812,p20110813,p20110814,p20110815,p20110816,p20110817,p20110818,pMax[z6]
type: index
possible_keys: NULL
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 17084274[z7]
Extra: Using where; Using index
1 row in set (0.00sec)
mysql> EXPLAINSELECT COUNT(*) FROM RPT_MALEVENTS WHERE ALERT_TYPE = 1/G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
type: index
possible_keys: NULL
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 17082459
Extra: Using where; Using index
1 row in set (0.00sec)
采用分区函数使用的列
mysql> EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE >'2011-08-09' AND RECORD_DATE ***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p20110810,p20110811,p20110812,p20110813,p20110814,p20110815[z8]
type: range
possible_keys:PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 3767081[z9]
Extra: Using where; Using index
1 row in set (0.08sec)
mysql> EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE >'2011-08-09' AND RECORD_DATE ***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: NULL
type: range
possible_keys:PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 8541229[z10]
Extra: Using where; Using index
1 row in set (0.00sec)
删除数据,如果删除1整天的数据,由于我们采用按天分区,
mysql> ALTER TABLERPT_MALEVENTS DROP PARTITION p20110809;[z11]
Query OK, 0 rowsaffected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除后包含索引的和数据的RPT_MALEVENTS#P#p20110809.ibd被删除了
如果采用传统的不分区的方式删除。
mysql> DELETE FROMRPT_MALEVENTS WHERE RECORD_DATE Query OK, 3929328rows affected (1 min 29.68 sec)
由此可见,删除整个分区内的数据还是很快的,
如果分区表采用传统的方式删除:
mysql> DELETEFROM RPT_MALEVENTS WHERE RECORD_DATE Query OK, 1153866rows affected (19.72 sec)
mysql> DELETE FROMRPT_MALEVENTS WHERE RECORD_DATE Query OK, 1153866rows affected (18.75 sec)
采用传统的方式删除一天的数据,用的时间都差不多。
只删除数据后,数据分区配p20110810还在,而且大小不变。可以用ALTER TABLE t1 OPTIMIZE PARTITION来进行回收,但是MySQL5.1.22还没有实现。
跨分区删除。
DELETE FROMRPT_MALEVENTS WHERE ALERT_TYPE =1;
Query OK, 63969 rowsaffected (55.20 sec)
DELETE FROMRPT_MALEVENTS WHERE ALERT_TYPE =1;
Query OK, 63969 rowsaffected (50.26 sec)
分区表删除比不分区的略慢
[z1]分区函数
[z2]分区信息,从2011-08-09开始
[z3]没有用分区函数使用的列会扫描所有分区
[z4]数据量为681311,分区后扫描行数为355911,虽然查询条件没有分区函数的列,但是mysql的查询优化器会将其对应于时间分区,这样可以减少扫描行数
[z5]数据量为681311,分区后扫描行数为1002288
[z6]查找所有分区
[z7]无关分区函数的字段,会遍历几乎所有行。
[z8]扫描部分分区
[z9]扫描行数随之减少
[z10]估计扫描的行数
[z11]这个分区的数据是所有2011-8-10之前的所有数据,共3929328。
总结:
分区表是在MySQL5.1中新增的的功能,截止到MySQL5.1.22-rc,分区技术并不很成熟,很多分区的维护和管理功能未实现。如,分区内数据存储空间的回收、分区的修复、分区的优化等,MySQL的分区可以用在可以按分区删除的表中,且对数据库的修改操作不大,且频繁按照分区字段进行查询的表中(如恶意代码中的统计表按天分区,经常按照时间进行查询、分组等,且可以按天删除分区)。此外,由于MySQL无全局索引只有分区索引,当一张有2个唯一索引[z5] 的时候,不能将此表分区,分区列中必须包含主键。否则MySQL会报错。
总之,MySQL对于分区的限制很多,且个人认为hash和key的分区实际意义不是太大。
分区引入了一种新的优化查询的方式(当然,也有相应的缺点)。优化器可以使用分区函数修整分区,或者把分区从查询中完全移除掉。它通过推断是否可以在特定的分区上找到数据来达成这种优化。因此在最好的情况下,修整可以让查询访问更少的数据。重要的是要在WHERE子句中定义分区键,即使它看上去像是多余的。通过分区键,优化器就可以去掉不用的分区,否则的话,执行引擎就会像合并表那样访问表的所有分区,这在大表上会非常慢。分区数据比非分区数据更好维护,并且可以通过删除分区来移除老的数据。分区数据可以被分布到不同的物理位置,这样服务器可以更有效地使用多个硬盘驱动器。
[z1]分区函数的返回值必须是整数,新增分区的分区函数返回值应大于任何一个现有分区的分区函数的返回值。
[z2]对于有主键的表错误提示:#1503
A PRIMARY KEY MUST INCLUDE ALL COLUMNS INTHE TABLE'S PARTITIONING FUNCTION,没有主键的则无此约束
[z3]注意:对于通过RANGE分区的表,只可以使用ADD PARTITION添加新的分区到分区列表的高端。即不能添加比这个分区的范围小的分区。
[z4] 对于按照RANGE分区的表,只能重新组织相邻的分区;不能跳过RANGE分区。不能使用REORGANIZEPARTITION来改变表的分区类型;也就是说,例如,不能把RANGE分区变为HASH分区,反之亦然。也不能使用该命令来改变分区表达式或列。
[z5]注意主键和唯一索引的区别
作者“深巷明朝卖杏花”

MySQLはオープンソースのリレーショナルデータベース管理システムであり、主にデータを迅速かつ確実に保存および取得するために使用されます。その実用的な原則には、クライアントリクエスト、クエリ解像度、クエリの実行、返品結果が含まれます。使用法の例には、テーブルの作成、データの挿入とクエリ、および参加操作などの高度な機能が含まれます。一般的なエラーには、SQL構文、データ型、およびアクセス許可、および最適化の提案には、インデックスの使用、最適化されたクエリ、およびテーブルの分割が含まれます。

MySQLは、データストレージ、管理、クエリ、セキュリティに適したオープンソースのリレーショナルデータベース管理システムです。 1.さまざまなオペレーティングシステムをサポートし、Webアプリケーションやその他のフィールドで広く使用されています。 2。クライアントサーバーアーキテクチャとさまざまなストレージエンジンを通じて、MySQLはデータを効率的に処理します。 3.基本的な使用には、データベースとテーブルの作成、挿入、クエリ、データの更新が含まれます。 4.高度な使用には、複雑なクエリとストアドプロシージャが含まれます。 5.一般的なエラーは、説明ステートメントを介してデバッグできます。 6.パフォーマンスの最適化には、インデックスの合理的な使用と最適化されたクエリステートメントが含まれます。

MySQLは、そのパフォーマンス、信頼性、使いやすさ、コミュニティサポートに選択されています。 1.MYSQLは、複数のデータ型と高度なクエリ操作をサポートし、効率的なデータストレージおよび検索機能を提供します。 2.クライアントサーバーアーキテクチャと複数のストレージエンジンを採用して、トランザクションとクエリの最適化をサポートします。 3.使いやすく、さまざまなオペレーティングシステムとプログラミング言語をサポートしています。 4.強力なコミュニティサポートを提供し、豊富なリソースとソリューションを提供します。

INNODBのロックメカニズムには、共有ロック、排他的ロック、意図ロック、レコードロック、ギャップロック、次のキーロックが含まれます。 1.共有ロックにより、トランザクションは他のトランザクションが読み取らないようにデータを読み取ることができます。 2.排他的ロックは、他のトランザクションがデータの読み取りと変更を防ぎます。 3.意図ロックは、ロック効率を最適化します。 4。ロックロックインデックスのレコードを記録します。 5。ギャップロックロックインデックス記録ギャップ。 6.次のキーロックは、データの一貫性を確保するためのレコードロックとギャップロックの組み合わせです。

MySQLクエリのパフォーマンスが低いことの主な理由には、インデックスの使用、クエリオプティマイザーによる誤った実行計画の選択、不合理なテーブルデザイン、過剰なデータボリューム、ロック競争などがあります。 1.インデックスがゆっくりとクエリを引き起こし、インデックスを追加するとパフォーマンスが大幅に向上する可能性があります。 2。説明コマンドを使用してクエリ計画を分析し、オプティマイザーエラーを見つけます。 3.テーブル構造の再構築と結合条件を最適化すると、テーブルの設計上の問題が改善されます。 4.データボリュームが大きい場合、パーティション化とテーブル分割戦略が採用されます。 5.高い並行性環境では、トランザクションの最適化とロック戦略は、ロック競争を減らすことができます。

データベースの最適化では、クエリ要件に従ってインデックス作成戦略を選択する必要があります。1。クエリに複数の列が含まれ、条件の順序が固定されている場合、複合インデックスを使用します。 2。クエリに複数の列が含まれているが、条件の順序が修正されていない場合、複数の単一列インデックスを使用します。複合インデックスは、マルチコラムクエリの最適化に適していますが、単一列インデックスは単一列クエリに適しています。

MySQLスロークエリを最適化するには、slowquerylogとperformance_schemaを使用する必要があります。1。LowerQueryLogを有効にし、しきい値を設定して、スロークエリを記録します。 2。performance_schemaを使用してクエリの実行の詳細を分析し、パフォーマンスのボトルネックを見つけて最適化します。

MySQLとSQLは、開発者にとって不可欠なスキルです。 1.MYSQLはオープンソースのリレーショナルデータベース管理システムであり、SQLはデータベースの管理と操作に使用される標準言語です。 2.MYSQLは、効率的なデータストレージと検索機能を介して複数のストレージエンジンをサポートし、SQLは簡単なステートメントを通じて複雑なデータ操作を完了します。 3.使用の例には、条件によるフィルタリングやソートなどの基本的なクエリと高度なクエリが含まれます。 4.一般的なエラーには、SQLステートメントをチェックして説明コマンドを使用することで最適化できる構文エラーとパフォーマンスの問題が含まれます。 5.パフォーマンス最適化手法には、インデックスの使用、フルテーブルスキャンの回避、参加操作の最適化、コードの読み取り可能性の向上が含まれます。


ホットAIツール

Undresser.AI Undress
リアルなヌード写真を作成する AI 搭載アプリ

AI Clothes Remover
写真から衣服を削除するオンライン AI ツール。

Undress AI Tool
脱衣画像を無料で

Clothoff.io
AI衣類リムーバー

AI Hentai Generator
AIヘンタイを無料で生成します。

人気の記事

ホットツール

SublimeText3 Mac版
神レベルのコード編集ソフト(SublimeText3)

DVWA
Damn Vulnerable Web App (DVWA) は、非常に脆弱な PHP/MySQL Web アプリケーションです。その主な目的は、セキュリティ専門家が法的環境でスキルとツールをテストするのに役立ち、Web 開発者が Web アプリケーションを保護するプロセスをより深く理解できるようにし、教師/生徒が教室環境で Web アプリケーションを教え/学習できるようにすることです。安全。 DVWA の目標は、シンプルでわかりやすいインターフェイスを通じて、さまざまな難易度で最も一般的な Web 脆弱性のいくつかを実践することです。このソフトウェアは、

SublimeText3 中国語版
中国語版、とても使いやすい

mPDF
mPDF は、UTF-8 でエンコードされた HTML から PDF ファイルを生成できる PHP ライブラリです。オリジナルの作者である Ian Back は、Web サイトから「オンザフライ」で PDF ファイルを出力し、さまざまな言語を処理するために mPDF を作成しました。 HTML2FPDF などのオリジナルのスクリプトよりも遅く、Unicode フォントを使用すると生成されるファイルが大きくなりますが、CSS スタイルなどをサポートし、多くの機能強化が施されています。 RTL (アラビア語とヘブライ語) や CJK (中国語、日本語、韓国語) を含むほぼすべての言語をサポートします。ネストされたブロックレベル要素 (P、DIV など) をサポートします。

EditPlus 中国語クラック版
サイズが小さく、構文の強調表示、コード プロンプト機能はサポートされていません
