MySQL 5.1 In-Depth | © MySQL AB 2007 | Константин Осипов Software Developer, MySQL AB Moscow MUG, Август 2007 Новые возможности MySQL 5.1

Copyright 2007 MySQL ABThe Worlds Most Popular Open Source Database Фундамент: возможности 5.0 хранимые процедуры, функции триггеры представления (views) ANSI режим INFORMATION_SCHEMA.* таблицы

Copyright 2007 MySQL ABThe Worlds Most Popular Open Source Database Основные направления разработки 5.1 удобство администрирования высокая доступность большие объёмы данных производительность

Copyright 2007 MySQL ABThe Worlds Most Popular Open Source Database Новые возмжоности управления данными: разбиения (Partitioning) улучшение полнотекстового поиска XML: поддержка XPath

Copyright 2007 MySQL ABThe Worlds Most Popular Open Source Database Безотказность (High Availability): NDB кластер: данные на диске NDB клсатер: репликация новый формат репликации: row-based

Copyright 2007 MySQL ABThe Worlds Most Popular Open Source Database Удобство администрирования: диспетчер событий (Events) Производительность: MyISAM может использовать mmap ALTER TABLE, ADD/DROP INDEX mysqlimport –use-threads, mysqlslap

MySQL 5.1 In-Depth | © MySQL AB 2007 | Partitioning: данные и индексы предназначен для управления большими объемами данных повышение скорости доступа к данным простота управления дисковым пространством поддержка range, hash, key, list и составных способов разбиения все storage engines Business Intelligence/ Data Warehousing

MySQL 5.1 In-Depth | © MySQL AB 2007 | Partitioning: примеры разбиение по диапазону – подходит для хранения исторической информации или других слабо связанных данных CREATE TABLE RANGE_BY_DATE( CUSTOMER_NUMBER int NOT NULL, CUSTOMER_ORDER VARCHAR(50) NOT NULL, CUSTOMER_ORDER_DATE DATETIME NOT NULL) PARTITION BY RANGE(YEAR(CUSTOMER_ORDER_DATE)) ( PARTITION P1 VALUES LESS THAN (2000), PARTITION P2 VALUES LESS THAN (2003), PARTITION P3 VALUES LESS THAN (2005), PARTITION P4 VALUES LESS THAN MAXVALUE );

MySQL 5.1 In-Depth | © MySQL AB 2007 | Partitioning: примеры по значению хэш-функции – для равномерного распределения данных среди нескольких физический устройств и повышения эффективности ввода-вывода. Хэш функция должан быть выбрана верно! CREATE TABLE HASH_EXAMPLE (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH(col1) ( PARTITION P1 DATA DIRECTORY = '/.../mysql51/data', PARTITION P2 DATA DIRECTORY = '/.../mysql51/data2', PARTITION P3 DATA DIRECTORY = '/.../mysql51/data3', PARTITION P4 DATA DIRECTORY = '/.../mysql51/data4' );

MySQL 5.1 In-Depth | © MySQL AB 2007 | Partitioning: примеры по первичному ключу – используется если данные ключа распределены равномерно. CREATE TABLE HASH_EXAMPLE (col1 INT primary key, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col1) ( PARTITION P1 DATA DIRECTORY = '/.../mysql51/data', PARTITION P2 DATA DIRECTORY = '/.../mysql51/data2', PARTITION P3 DATA DIRECTORY = '/.../mysql51/data3', PARTITION P4 DATA DIRECTORY = '/.../mysql51/data4' ) ;

MySQL 5.1 In-Depth | © MySQL AB 2007 | Partitioning: примеры по списку значений – позволяет с точностью указать распределение данных для каждого значения CREATE TABLE LIST_BY_AREA ( STORE_NUMBER int NOT NULL, STORE_LOCATION int NOT NULL, ROLLUP_DATE DATE NOT NULL, STORE_RECEIPTS DECIMAL(10,2) NOT NULL) PARTITION BY LIST(STORE_LOCATION) ( PARTITION P1 VALUES IN (1,2), PARTITION P2 VALUES IN (3), PARTITION P3 VALUES IN (4,5) ) ;

MySQL 5.1 In-Depth | © MySQL AB 2007 | Partitioning: примеры возможность создания вложенных разбиений CREATE TABLE SUB_EXAMPLE ( CUSTOMER_NUMBER INT NOT NULL, CUSTOMER_ORDER VARCHAR(50) NOT NULL, CUSTOMER_ORDER_DATE DATETIME NOT NULL, CUSTOMER_SERVICE_REGION INT NOT NULL) PARTITION BY RANGE(YEAR(CUSTOMER_ORDER_DATE)) SUBPARTITION BY HASH(CUSTOMER_SERVICE_REGION) ( PARTITION P1 VALUES LESS THAN (2000) (SUBPARTITION S0,SUBPARTITION S1), PARTITION P2 VALUES LESS THAN (2003) (SUBPARTITION S2,SUBPARTITION S3), PARTITION P3 VALUES LESS THAN (2005) (SUBPARTITION S4,SUBPARTITION S5), PARTITION P4 VALUES LESS THAN MAXVALUE (SUBPARTITION S6,SUBPARTITION S7) ) ;

MySQL 5.1 In-Depth | © MySQL AB 2007 | Partitioning: мета-информация mysql> desc partitions; | Field | Type | Null | Key | Default | Extra | | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | PARTITION_NAME | varchar(64) | YES | | NULL | | | SUBPARTITION_NAME | varchar(64) | YES | | NULL | | | PARTITION_ORDINAL_POSITION | bigint(21) | YES | | NULL | | | SUBPARTITION_ORDINAL_POSITION | bigint(21) | YES | | NULL | | | PARTITION_METHOD | varchar(12) | YES | | NULL | | | SUBPARTITION_METHOD | varchar(5) | YES | | NULL | | | PARTITION_EXPRESSION | longtext | YES | | NULL | | | SUBPARTITION_EXPRESSION | longtext | YES | | NULL | | | PARTITION_DESCRIPTION | longtext | YES | | NULL | | | TABLE_ROWS | bigint(21) | NO | | 0 | | | AVG_ROW_LENGTH | bigint(21) | NO | | 0 | | | DATA_LENGTH | bigint(21) | NO | | 0 | | | MAX_DATA_LENGTH | bigint(21) | YES | | NULL | | | INDEX_LENGTH | bigint(21) | NO | | 0 | | | DATA_FREE | bigint(21) | NO | | 0 | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | CHECKSUM | bigint(21) | YES | | NULL | | | PARTITION_COMMENT | varchar(80) | NO | | | | | NODEGROUP | bigint(21) | NO | | 0 | | | TABLESPACE_NAME | varchar(64) | NO | | | |

MySQL 5.1 In-Depth | © MySQL AB 2007 | Partitioning: мета-информация mysql> insert into test.RANGE_BY_DATE VALUES(1,'TEST',NOW()); mysql> insert into test.RANGE_BY_DATE VALUES(2,'TEST',NOW()); mysql> insert into test.RANGE_BY_DATE VALUES(3,'TEST',NOW()); mysql> select * from partitions where table_name = 'RANGE_BY_DATE'\G ********************** 4. row ******************** TABLE_CATALOG: NULL TABLE_SCHEMA: test TABLE_NAME: RANGE_BY_DATE PARTITION_NAME: P4 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 4 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: RANGE SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: YEAR(CUSTOMER_ORDER_DATE) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: MAXVALUE TABLE_ROWS: 3 AVG_ROW_LENGTH: 24 DATA_LENGTH: 72 MAX_DATA_LENGTH: INDEX_LENGTH: 1024 DATA_FREE: 0 CREATE_TIME: :12:10 UPDATE_TIME: :23:36 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: default NODEGROUP: 0 TABLESPACE_NAME: default

MySQL 5.1 In-Depth | © MySQL AB 2007 | Partitioning: производительность mysql> CREATE TABLE part_tab -> ( c1 int,c2 varchar(30),c3 date ) -> PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995), -> PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (1997), -> PARTITION p3 VALUES LESS THAN (1998), PARTITION p4 VALUES LESS THAN (1999), -> PARTITION p5 VALUES LESS THAN (2000), PARTITION p6 VALUES LESS THAN (2001), -> PARTITION p7 VALUES LESS THAN (2002), PARTITION p8 VALUES LESS THAN (2003), -> PARTITION p9 VALUES LESS THAN (2004), PARTITION p10 VALUES LESS THAN (2010), -> PARTITION p11 VALUES LESS THAN MAXVALUE ); mysql> create table no_part_tab (c1 int,c2 varchar(30),c3 date); *** Load 8 million rows of data into each table *** mysql> select count(*) from no_part_tab where c3 > date ' ' and c3 < date ' '; | count(*) | | | row in set (38.30 sec) mysql> select count(*) from part_tab where c3 > date ' ' and c3 < date ' '; | count(*) | | | row in set (3.88 sec) 10x ускорение!

MySQL 5.1 In-Depth | © MySQL AB 2007 | пример PLUG-IN парсера включён в main tree поддержка списков стоп-слов возможность игнорировать регистр возможность влиять на релевантность режим BOOLEAN теперь по умолчанию SHOW PLUGIN, INSTALL/UNINSTALL PLUGIN --plugin_dir=path, plugin.h документация Полнотекстовый поиск: plug-in архитектура Business Intelligence/ Data Warehousing

MySQL 5.1 In-Depth | © MySQL AB 2007 | XML документ доступен пользователю в виде дерева узлов возможность доступа к значениям без пересылки на клиент extractValue(), updateXML() Поддержка XML Xpath XML Business Intelligence/ Data Warehousing

MySQL 5.1 In-Depth | © MySQL AB 2007 | XPath: пример mysql> SELECT extractValue(doc,'/book/author/initial') FROM x; | extractValue(doc,'/book/author/initial') | | CJ | | J | rows in set (0.01 sec) mysql> SELECT extractValue(doc,'/book/child::*') FROM x; | extractValue(doc,'/book/child::*') | | A guide to the SQL standard | | SQL:1999 | rows in set (0.00 sec)

MySQL 5.1 In-Depth | © MySQL AB 2007 | Пример работы c RSS: извлечение заголовков mysql> select ExtractValue(raw_xml, "/*/channel/title") from sites_log LIMIT 7; | ExtractValue(raw_xml, "/*/channel/ title") | | The Motley Fool | | Nanodot: Nanodot: Nanotechnology News and Discussion of Emerging Technologies | | Slashdot: Science | | Slashdot | | Slashdot: Book Reviews | | Slashdot: Features | | Slashdot: Interviews | rows in set (0.00 sec) mysql> INSERT into titles select ExtractValue(raw_xml, "/*/channel/title") from sites_log;

MySQL 5.1 In-Depth | © MySQL AB 2006 | таблицы могут храниться на диске управление дисковым пространством при помощи tablespaces индексы хранятся в памяти Кластер: данные на диске High Availability

MySQL 5.1 In-Depth | © MySQL AB 2007 | Кластер: определение табличных пространств CREATE TABLESPACE ts1 ADD DATAFILE 'datafile.dat' USE LOGFILE GROUP lg1 INITIAL_SIZE 12M ENGINE NDB; CREATE TABLE t1 (pk1 INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL ) TABLESPACE ts1 STORAGE DISK ENGINE NDB;

MySQL 5.1 In-Depth | © MySQL AB 2007 | High Availability Новый формат репликации: row-based опционален – сохранён старый формат любые сценарии репликации наиболее надёжный распространён в большинстве коммерческих СУБД возможен смешанный режим, сочетающий преимущества двух форматов

MySQL 5.1 In-Depth | © MySQL AB 2007 | Кластер: репликация возможность репликации данных из одного кластера в другой ограничение: нет поддержки CREATE/ALTER/DROP TABLE High Availability

MySQL 5.1 In-Depth | © MySQL AB 2007 | Диспетчер событий новый тип объекта – событие, EVENT позволяет создавать единоразовые или повторяющиеся задачи позволяет выполнить запрос, блок SQL или хранимую процедуру использует нити для выполнения имеется возможность остановить (kill) выполняющуюся задачу реплицируется только в новом формате Manageability

MySQL 5.1 In-Depth | © MySQL AB 2007 | Events: пример реорганизация таблиц каждое воскресенье в 6 утра DELIMITER // CREATE EVENT OPTIMIZE_TABLES ON SCHEDULE EVERY 1 WEEK STARTS ' :00:00' ON COMPLETION PRESERVE DO BEGIN OPTIMIZE TABLE test.table1; OPTIMIZE TABLE test.table2; END //

MySQL 5.1 In-Depth | © MySQL AB 2007 | Events: пример DBA решает что с 2007 года таблица более не нужна. DELIMITER // CREATE EVENT DROP_TABLES ON SCHEDULE AT TIMESTAMP ' :01:00' ON COMPLETION NOT PRESERVE DO BEGIN DROP TABLE test.table2; END //

MySQL 5.1 In-Depth | © MySQL AB 2007 | FAQ по Events Может ли диспетчер пропустить выполнение события? Могут ли два события выполняться одновременно? Что происходит при переводе часов? Можно ли перегрузить сервер? Можно ли ограничить число событий?

MySQL 5.1 In-Depth | © MySQL AB 2007 | Новые возможности аудита таблица PROCESSLIST таблица general_log, slow_log CSV или MyISAM формат *_log таблиц SQL Manageability

MySQL 5.1 In-Depth | © MySQL AB 2007 | Таблица INFORMATION_SCHEMA.PROCESSLIST mysql> desc processlist; | Field | Type | Null | Key | Default | Extra | | ID | bigint(4) | NO | | 0 | | | USER | varchar(16) | NO | | | | | HOST | varchar(64) | NO | | | | | DB | varchar(64) | YES | | NULL | | | COMMAND | varchar(16) | NO | | | | | TIME | bigint(4) | NO | | 0 | | | STATE | varchar(30) | YES | | NULL | | | INFO | varchar(100) | YES | | NULL | |

MySQL 5.1 In-Depth | © MySQL AB 2007 | Лог-таблицы: структура mysql> desc slow_log; | Field | Type | Null | Key | Default | Extra | | start_time | timestamp | YES | | CURRENT_TIMESTAMP | | | user_host | mediumtext | NO | | | | | query_time | time | NO | | | | | lock_time | time | NO | | | | | rows_sent | int(11) | NO | | | | | rows_examined | int(11) | NO | | | | | db | varchar(512) | YES | | NULL | | | last_insert_id | int(11) | YES | | NULL | | | insert_id | int(11) | YES | | NULL | | | server_id | int(11) | YES | | NULL | | | sql_text | mediumtext | NO | | | | mysql> desc general_log; | Field | Type | Null | Key | Default | Extra | | event_time | timestamp | YES | | CURRENT_TIMESTAMP | | | user_host | mediumtext | YES | | NULL | | | thread_id | int(11) | YES | | NULL | | | server_id | int(11) | YES | | NULL | | | command_type | varchar(64) | YES | | NULL | | | argument | mediumtext | YES | | NULL | |

MySQL 5.1 In-Depth | © MySQL AB 2007 | Лог-таблицы: примеры mysql> select query_time, rows_examined, sql_text -> from slow_log -> order by query_time desc -> limit 1\G *************************** 1. row *************************** query_time: 00:00:49 rows_examined: 9935 sql_text: SELECT c_custkey, c_name, SUM(l_extendedprice * (1 - l_discount)) AS REVENUE, c_acctbal, n_name, c_address, c_phone, c_comment FROM dss_customer, dss_order, dss_lineitem, dss_nation WHERE c_custkey = o_custkey AND l_or derkey = o_orderkey AND o_orderdate >= ' ' AND o_orderdate < ' ' AND l_returnflag = 'R AND c_nationkey = n_nationkey GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment ORDER BY REVENUE DESC 1 row in set (0.00 sec)

MySQL 5.1 In-Depth | © MySQL AB 2007 | Производительность MyISAM новая переменная myisam_use_mmap позволяет использовать mmap для MyISAM таблиц эффект зависит от сценария использования наши оценки показывают прирост в 7-40% для SELECT запросов Performance

MySQL 5.1 In-Depth | © MySQL AB 2007 | Спасибо!