Download as PDF, PPTX
![• PITR on the fly • Create a versioned table MariaDB [test]> alter table employees ADD SYSTEM VERSIONING; Query OK, 300024 rows affected (1.060 sec) Records: 300024 Duplicates: 0 Warnings: 0 System-Versioned Tables 11](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-30-2048.jpg)
![• PITR on the fly • Check table options MariaDB [test]> show create table employeesG *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE ‘employees‘ ( ‘emp_no‘ int(11) NOT NULL, ‘birth_date‘ date NOT NULL, ‘first_name‘ varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL, ‘last_name‘ varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL, ‘gender‘ enum(’M’,’F’) COLLATE utf8mb4_unicode_ci NOT NULL, ‘hire_date‘ date NOT NULL, PRIMARY KEY (‘emp_no‘) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci WITH SYSTEM VERSIONING 1 row in set (0.001 sec) System-Versioned Tables 11](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-31-2048.jpg)
![• PITR on the fly • Perform activities MariaDB [test]> select count(*) from employees where hire_date < ’1986-01-01’; +––––––––––+ | count(*) | +––––––––––+ | 35316 | +––––––––––+ 1 row in set (0.188 sec) MariaDB [test]> delete from employees where hire_date < ’1986-01-01’; Query OK, 35316 rows affected (0.837 sec) System-Versioned Tables 11](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-32-2048.jpg)
![• PITR on the fly • Lost rows MariaDB [test]> select count(*) from employees where hire_date < ’1986-01-01’; +––––––––––+ | count(*) | +––––––––––+ | 0 | +––––––––––+ 1 row in set (0.103 sec) System-Versioned Tables 11](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-33-2048.jpg)
![• PITR on the fly • Restore deleted rows MariaDB [test]> insert into employees select * FROM employees -> FOR SYSTEM_TIME between (now() - interval 1 hour) and now() -> where hire_date < ’1986-01-01’; Query OK, 35316 rows affected (0.721 sec) Records: 35316 Duplicates: 0 Warnings: 0 MariaDB [test]> select count(*) from employees where hire_date < ’1986-01-01’; +––––––––––+ | count(*) | +––––––––––+ | 35316 | +––––––––––+ 1 row in set (0.216 sec) System-Versioned Tables 11](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-34-2048.jpg)
![• Columns, not visible for SELECT * MariaDB [test]> alter table employees add column address JSON invisible; Query OK, 0 rows affected (0.005 sec) Records: 0 Duplicates: 0 Warnings: 0 – Update address for some employees MariaDB [test]> select * from employees limit 3; +––––––––+––––––––––––+––––––––––––+–––––––––––+––––––––+––––––––––––+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +––––––––+––––––––––––+––––––––––––+–––––––––––+––––––––+––––––––––––+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | +––––––––+––––––––––––+––––––––––––+–––––––––––+––––––––+––––––––––––+ 3 rows in set (0.001 sec) Invisible columns 12](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-36-2048.jpg)
![• Columns, not visible for SELECT * • Accessible for the direct query MariaDB [test]> select first_name, last_name, json_extract(address, "$.City") -> from employees where address is not null; +––––––––––––+–––––––––––+–––––––––––––––––––––––––––––––––+ | first_name | last_name | json_extract(address, "$.City") | +––––––––––––+–––––––––––+–––––––––––––––––––––––––––––––––+ | Tianruo | Jenevein | "Espoo" | | Dulce | Kolinko | "Espoo" | | Masasuke | Gill | "Espoo" | | Toshimi | Karner | "Espoo" | | Danco | Yetto | "Espoo" | +––––––––––––+–––––––––––+–––––––––––––––––––––––––––––––––+ 5 rows in set (0.173 sec) Invisible columns 12](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-37-2048.jpg)
![MariaDB [test]> create sequence odds start with 1 increment by 2; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> create sequence evens start with 2 increment by 2; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> create table numbers( -> id int not null auto_increment primary key, -> odd int default next value for odds, -> even int default next value for evens -> ); Query OK, 0 rows affected (0.004 sec) Sequences 13](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-38-2048.jpg)
![MariaDB [test]> insert into numbers values (), (), (), (), (); Query OK, 5 rows affected (0.002 sec) Records: 5 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from numbers; +––––+––––––+––––––+ | id | odd | even | +––––+––––––+––––––+ | 1 | 1 | 2 | | 2 | 3 | 4 | | 3 | 5 | 6 | | 4 | 7 | 8 | | 5 | 9 | 10 | +––––+––––––+––––––+ 5 rows in set (0.001 sec) Sequences 13](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-39-2048.jpg)
![• Native IPv6 support MariaDB [test]> create table inet6_test(ip inet6); Query OK, 0 rows affected (0.001 sec) MariaDB [test]> insert into inet6_test values(’fe80::e43b:a1ff:fe32:cb0b’); – IPv6 Query OK, 1 row affected (0.009 sec) MariaDB [test]> insert into inet6_test values(’::192.168.60.100’); – IPv4 compatible Query OK, 1 row affected (0.001 sec) MariaDB [test]> insert into inet6_test values(’::ffff:192.168.60.100’); – IPv4 mapped Query OK, 1 row affected (0.001 sec) MariaDB [test]> SELECT ip, if(IS_IPV4_COMPAT(ip), ’deprecated’, ’OK’) FROM inet6_test; +–––––––––––––––––––––––––––+––––––––––––––––––––––––––––––––––––––––––––+ | ip | if(IS_IPV4_COMPAT(ip), ’deprecated’, ’OK’) | +–––––––––––––––––––––––––––+––––––––––––––––––––––––––––––––––––––––––––+ | fe80::e43b:a1ff:fe32:cb0b | OK | | ::192.168.60.100 | deprecated | | ::ffff:192.168.60.100 | OK | +–––––––––––––––––––––––––––+––––––––––––––––––––––––––––––––––––––––––––+ 3 rows in set (0.002 sec) INET6 data type 14](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-40-2048.jpg)
![• Normal table scan 1 2 3 4 5 Locked 1 2 Wait [innodb_]lock_wait_timeout seconds WAIT and NOWAIT 15](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-41-2048.jpg)
![• Your own aggregate functions MariaDB [test]> create aggregate function count_positive(val int) returns int -> begin -> declare result int unsigned default 0; -> declare exit handler for not found return result; -> main_loop: -> loop -> fetch group next row; -> if sign(val) = 1 then -> set result := result + 1; -> end if; -> end loop; -> end -> | Query OK, 0 rows affected (0.001 sec) Aggregate stored functions 17](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-46-2048.jpg)
![• Your own aggregate functions MariaDB [test]> select color, count_positive(num) from numbers group by color; +–––––––+–––––––––––––––––––––+ | color | count_positive(num) | +–––––––+–––––––––––––––––––––+ | NULL | 1 | | blue | 0 | | green | 2 | | red | 2 | +–––––––+–––––––––––––––––––––+ 4 rows in set (0.001 sec) MariaDB [test]> select group_concat(num) from numbers; +–––––––––––––––––––––––––+ | group_concat(num) | +–––––––––––––––––––––––––+ | 1,-2,3,-4,5,-6,7,-8,9,0 | +–––––––––––––––––––––––––+ 1 row in set (0.001 sec) Aggregate stored functions 17](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-47-2048.jpg)
![Based on MySQL bug #78651 • EXPLAIN is telling lies MariaDB [test]> explain select * from ol -> where thread_id=10432 and site_id != 9939 order by id limit 3G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ol type: index possible_keys: thread_id key: PRIMARY key_len: 4 ref: NULL rows: 33 Extra: Using where ANALYZE 22](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-52-2048.jpg)
![Based on MySQL bug #78651 • ANALYZE executes the statement MariaDB [test]> analyze select * from ol -> where thread_id=10432 and site_id != 9939 order by id limit 3G *************************** 1. row *************************** ... type: index possible_keys: thread_id key: PRIMARY key_len: 4 ref: const rows: 100000 r_rows: 100000.00 filtered: 8.96 r_filtered: 0.00 Extra: Using where ANALYZE 22](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-53-2048.jpg)
![• CTEs • Not recursive MariaDB [employees]> with -> dept_data as -> (select emp_no, dept_name from dept_emp join departments using (dept_no) -> select first_name, last_name, dept_name -> from employees join dept_data using(emp_no) -> order by hire_date desc limit 3; +––––––––––––+–––––––––––+––––––––––––––––––––+ | first_name | last_name | dept_name | +––––––––––––+–––––––––––+––––––––––––––––––––+ | Bikash | Covnot | Quality Management | | Yucai | Gerlach | Production | | Hideyuki | Delgrande | Development | +––––––––––––+–––––––––––+––––––––––––––––––––+ 3 rows in set (0.00 sec) SQL DML 23](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-54-2048.jpg)
![• CTEs • Recursive MariaDB [employees]> with recursive rand_generator(id, rand_value) as -> (select 1, rand() -> union all select id+1, rand() -> from rand_generator where id < 5) -> select * from rand_generator; +––––––+–––––––––––––––––––––+ | id | rand_value | +––––––+–––––––––––––––––––––+ | 1 | 0.5599308382346582 | | 2 | 0.2151867702744778 | | 3 | 0.39614136740205935 | | 4 | 0.33514655692050843 | | 5 | 0.4873087131300091 | +––––––+–––––––––––––––––––––+ 5 rows in set (0.00 sec) SQL DML 23](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-55-2048.jpg)
![• Window functions MariaDB [employees]> select -> row_number() over win as id, dept_no, dept_name from departments -> window win -> as (order by dept_no); +––––+–––––––––+––––––––––––––––––––+ | id | dept_no | dept_name | +––––+–––––––––+––––––––––––––––––––+ | 1 | d001 | Marketing | | 2 | d002 | Finance | | 3 | d003 | Human Resources | | 4 | d004 | Production | | 5 | d005 | Development | | 6 | d006 | Quality Management | | 7 | d007 | Sales | | 8 | d008 | Research | | 9 | d009 | Customer Service | +––––+–––––––––+––––––––––––––––––––+ SQL DML 23](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-56-2048.jpg)
![• Custom rules validation MariaDB [test]> create table even (even_value int check(even_value % 2 = 0)) engine=innodb; Query OK, 0 rows affected (0.004 sec) MariaDB [test]> insert into even value(2); Query OK, 1 row affected (0.003 sec) MariaDB [test]> insert into even value(1); ERROR 4025 (23000): CONSTRAINT ‘even.even_value‘ failed for ‘test‘.‘even‘ CHECK Constraint 24](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-58-2048.jpg)
![• No need to repeat GRANT MariaDB [test]> create role read_only, admin; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> grant select on *.* to read_only; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> grant super on *.* to admin; Query OK, 0 rows affected (0.001 sec) Roles 25](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-59-2048.jpg)
![• No need to repeat GRANT MariaDB [test]> create user sveta; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> create user kaj; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> create user privileged; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> grant read_only to sveta; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> grant read_only to kaj; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> grant admin to privileged; Query OK, 0 rows affected (0.001 sec) Roles 25](https://image.slidesharecdn.com/newfeatures105-201017130129/75/Modern-solutions-for-modern-database-load-improvements-in-the-latest-MariaDB-versions-60-2048.jpg)
Uploaded bySveta Smirnova
Modern solutions for modern database load: improvements in the latest MariaDB versions
The document discusses improvements and features of Mariadb, detailing advancements in storage, optimization, and SQL performance. Key highlights include the introduction of system-versioned tables, support for invisible columns, and native IPv6 support along with user-defined aggregate functions. The document also outlines various storage engines and features ported from MySQL, emphasizing Mariadb's independence and additional functionalities.
Modern solutions for modern database load: improvements in the latest MariaDB versions
- 1. MariaDB Improvements in theLatest Versions September 14-16, 2020 Sveta Smirnova
- 2. • MySQL SupportEngineer • Author of • MySQL Troubleshooting • JSON UDF functions • FILTER clause for MySQL • Speaker • Percona Live, OOW, Fosdem, DevConf, HighLoad... Sveta Smirnova 2
- 3. •Storage Improvements •Optimizer andSQL Improvements Table of Contents 3
- 4. 1995 Initial Releaseby MySQL AB MySQL 4
- 5. 1995 Initial Releaseby MySQL AB 2006 Percona Server for MySQL MySQL 4
- 6. 1995 Initial Releaseby MySQL AB 2006 Percona Server for MySQL 2008 Sun owns MySQL MySQL 4
- 7. 1995 Initial Releaseby MySQL AB 2006 Percona Server for MySQL 2008 Sun owns MySQL 2010 Oracle owns Sun and MySQL MySQL 4
- 8. 1995 Initial Releaseby MySQL AB 2006 Percona Server for MySQL 2008 Sun owns MySQL 2010 Oracle owns Sun and MySQL 2010 MariaDB MySQL 4
- 9. • Started asfork of MySQL MariaDB 5
- 10. • Started asfork of MySQL • Independent product MariaDB 5
- 11. • Started asfork of MySQL • Independent product • Hundreds of unique features MariaDB 5
- 12. • Started asfork of MySQL • Independent product • Hundreds of unique features • Important improvements are backported • from MySQL MariaDB 5
- 13. • Features, portedfrom MySQL 8.0 This Talk 6
- 14. • Features, portedfrom MySQL 8.0 • Another talk MySQL 8.0 and Percona Improvements This Talk 6
- 15. • Features, portedfrom MySQL 8.0 • Another talk MySQL 8.0 and Percona Improvements • MariaDB Unique Features • Storage Improvements Alternative storage engines Others • Optimizer Improvements Advanced SQL Performance and diagnostics This Talk 6
- 16.
- 17.
- 18. • Storage Engine •Data stored on Amazon S3 S3 8
- 19. • Storage Engine •Data stored on Amazon S3 • Any original engine ALTER TABLE my_table ENGINE=S3; ALTER TABLE my_table ENGINE=INNODB; ALTER TABLE my_table ENGINE=S3; S3 8
- 20. • Storage Engine •Data stored on Amazon S3 • Any original engine • Underlying engine is Aria S3 8
- 21. • Storage Engine •Data stored on Amazon S3 • Any original engine • Underlying engine is Aria • Read-only tables S3 8
- 22. • Storage Engine •Data stored on Amazon S3 • Any original engine • Underlying engine is Aria • Read-only tables • Shared and separated storage for replication S3 8
- 23. • Storage Engine •Data stored on Amazon S3 • Any original engine • Underlying engine is Aria • Read-only tables • Shared and separated storage for replication • Own layer to connect to S3: libmarias3 S3 8
- 24. • Storage Engine • Availablesince 2017 as a plugin • Part of 10.5 Community Server release ColumnStore 9
- 25. • Columnar Storage Engine •Based on InfiniDB ColumnStore 9
- 26. • Designed for parallelprocessing • SQL interface • JOINs with SQL engines (InnoDB etc.) • Easy integration with existent SQL setup • All advantages of the column storage ColumnStore 9
- 27.
- 28. • Change history System-VersionedTables 11
- 29. • PITR onthe fly System-Versioned Tables 11
- 30. • PITR onthe fly • Create a versioned table MariaDB [test]> alter table employees ADD SYSTEM VERSIONING; Query OK, 300024 rows affected (1.060 sec) Records: 300024 Duplicates: 0 Warnings: 0 System-Versioned Tables 11
- 31. • PITR onthe fly • Check table options MariaDB [test]> show create table employeesG *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE ‘employees‘ ( ‘emp_no‘ int(11) NOT NULL, ‘birth_date‘ date NOT NULL, ‘first_name‘ varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL, ‘last_name‘ varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL, ‘gender‘ enum(’M’,’F’) COLLATE utf8mb4_unicode_ci NOT NULL, ‘hire_date‘ date NOT NULL, PRIMARY KEY (‘emp_no‘) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci WITH SYSTEM VERSIONING 1 row in set (0.001 sec) System-Versioned Tables 11
- 32. • PITR onthe fly • Perform activities MariaDB [test]> select count(*) from employees where hire_date < ’1986-01-01’; +––––––––––+ | count(*) | +––––––––––+ | 35316 | +––––––––––+ 1 row in set (0.188 sec) MariaDB [test]> delete from employees where hire_date < ’1986-01-01’; Query OK, 35316 rows affected (0.837 sec) System-Versioned Tables 11
- 33. • PITR onthe fly • Lost rows MariaDB [test]> select count(*) from employees where hire_date < ’1986-01-01’; +––––––––––+ | count(*) | +––––––––––+ | 0 | +––––––––––+ 1 row in set (0.103 sec) System-Versioned Tables 11
- 34. • PITR onthe fly • Restore deleted rows MariaDB [test]> insert into employees select * FROM employees -> FOR SYSTEM_TIME between (now() - interval 1 hour) and now() -> where hire_date < ’1986-01-01’; Query OK, 35316 rows affected (0.721 sec) Records: 35316 Duplicates: 0 Warnings: 0 MariaDB [test]> select count(*) from employees where hire_date < ’1986-01-01’; +––––––––––+ | count(*) | +––––––––––+ | 35316 | +––––––––––+ 1 row in set (0.216 sec) System-Versioned Tables 11
- 35. • Change history •PITR on the fly • Tunable Time Transaction ID With History, stored separately Paritioned With columns, excluded from versioning With time periods: application history With both system and application period levels System-Versioned Tables 11
- 36. • Columns, notvisible for SELECT * MariaDB [test]> alter table employees add column address JSON invisible; Query OK, 0 rows affected (0.005 sec) Records: 0 Duplicates: 0 Warnings: 0 – Update address for some employees MariaDB [test]> select * from employees limit 3; +––––––––+––––––––––––+––––––––––––+–––––––––––+––––––––+––––––––––––+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +––––––––+––––––––––––+––––––––––––+–––––––––––+––––––––+––––––––––––+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | +––––––––+––––––––––––+––––––––––––+–––––––––––+––––––––+––––––––––––+ 3 rows in set (0.001 sec) Invisible columns 12
- 37. • Columns, notvisible for SELECT * • Accessible for the direct query MariaDB [test]> select first_name, last_name, json_extract(address, "$.City") -> from employees where address is not null; +––––––––––––+–––––––––––+–––––––––––––––––––––––––––––––––+ | first_name | last_name | json_extract(address, "$.City") | +––––––––––––+–––––––––––+–––––––––––––––––––––––––––––––––+ | Tianruo | Jenevein | "Espoo" | | Dulce | Kolinko | "Espoo" | | Masasuke | Gill | "Espoo" | | Toshimi | Karner | "Espoo" | | Danco | Yetto | "Espoo" | +––––––––––––+–––––––––––+–––––––––––––––––––––––––––––––––+ 5 rows in set (0.173 sec) Invisible columns 12
- 38. MariaDB [test]> createsequence odds start with 1 increment by 2; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> create sequence evens start with 2 increment by 2; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> create table numbers( -> id int not null auto_increment primary key, -> odd int default next value for odds, -> even int default next value for evens -> ); Query OK, 0 rows affected (0.004 sec) Sequences 13
- 39. MariaDB [test]> insertinto numbers values (), (), (), (), (); Query OK, 5 rows affected (0.002 sec) Records: 5 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from numbers; +––––+––––––+––––––+ | id | odd | even | +––––+––––––+––––––+ | 1 | 1 | 2 | | 2 | 3 | 4 | | 3 | 5 | 6 | | 4 | 7 | 8 | | 5 | 9 | 10 | +––––+––––––+––––––+ 5 rows in set (0.001 sec) Sequences 13
- 40. • Native IPv6 support MariaDB[test]> create table inet6_test(ip inet6); Query OK, 0 rows affected (0.001 sec) MariaDB [test]> insert into inet6_test values(’fe80::e43b:a1ff:fe32:cb0b’); – IPv6 Query OK, 1 row affected (0.009 sec) MariaDB [test]> insert into inet6_test values(’::192.168.60.100’); – IPv4 compatible Query OK, 1 row affected (0.001 sec) MariaDB [test]> insert into inet6_test values(’::ffff:192.168.60.100’); – IPv4 mapped Query OK, 1 row affected (0.001 sec) MariaDB [test]> SELECT ip, if(IS_IPV4_COMPAT(ip), ’deprecated’, ’OK’) FROM inet6_test; +–––––––––––––––––––––––––––+––––––––––––––––––––––––––––––––––––––––––––+ | ip | if(IS_IPV4_COMPAT(ip), ’deprecated’, ’OK’) | +–––––––––––––––––––––––––––+––––––––––––––––––––––––––––––––––––––––––––+ | fe80::e43b:a1ff:fe32:cb0b | OK | | ::192.168.60.100 | deprecated | | ::ffff:192.168.60.100 | OK | +–––––––––––––––––––––––––––+––––––––––––––––––––––––––––––––––––––––––––+ 3 rows in set (0.002 sec) INET6 data type 14
- 41. • Normal tablescan 1 2 3 4 5 Locked 1 2 Wait [innodb_]lock_wait_timeout seconds WAIT and NOWAIT 15
- 42. • WAIT n 1 2 3 4 5 Locked 1 2 Wait nseconds WAIT and NOWAIT 15
- 43. • NOWAIT orWAIT 0 1 2 3 4 5 Locked 1 2 Error WAIT and NOWAIT 15
- 44. Optimizer and SQLImprovements
- 45. • Your ownaggregate functions Aggregate stored functions 17
- 46. • Your ownaggregate functions MariaDB [test]> create aggregate function count_positive(val int) returns int -> begin -> declare result int unsigned default 0; -> declare exit handler for not found return result; -> main_loop: -> loop -> fetch group next row; -> if sign(val) = 1 then -> set result := result + 1; -> end if; -> end loop; -> end -> | Query OK, 0 rows affected (0.001 sec) Aggregate stored functions 17
- 47. • Your ownaggregate functions MariaDB [test]> select color, count_positive(num) from numbers group by color; +–––––––+–––––––––––––––––––––+ | color | count_positive(num) | +–––––––+–––––––––––––––––––––+ | NULL | 1 | | blue | 0 | | green | 2 | | red | 2 | +–––––––+–––––––––––––––––––––+ 4 rows in set (0.001 sec) MariaDB [test]> select group_concat(num) from numbers; +–––––––––––––––––––––––––+ | group_concat(num) | +–––––––––––––––––––––––––+ | 1,-2,3,-4,5,-6,7,-8,9,0 | +–––––––––––––––––––––––––+ 1 row in set (0.001 sec) Aggregate stored functions 17
- 48. • Histogram-based statistics • AndMariaDB-only independent table statistics • ANALYZE • CTEs and WITH statement • Window functions • CHECK constraint • Roles MariaDB 10.0+ Features, Announced in MySQL 8.0 18
- 49. 1 2 34 5 6 7 8 9 10 0 200 400 600 800 Data Distribution 19
- 50. 1 2 34 5 6 7 8 9 10 0 200 400 600 800 Indexes: Cardinality 20
- 51. 1 2 34 5 6 7 8 9 10 0 0.2 0.4 0.6 0.8 1 Same Data in Histograms 21
- 52. Based on MySQLbug #78651 • EXPLAIN is telling lies MariaDB [test]> explain select * from ol -> where thread_id=10432 and site_id != 9939 order by id limit 3G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ol type: index possible_keys: thread_id key: PRIMARY key_len: 4 ref: NULL rows: 33 Extra: Using where ANALYZE 22
- 53. Based on MySQLbug #78651 • ANALYZE executes the statement MariaDB [test]> analyze select * from ol -> where thread_id=10432 and site_id != 9939 order by id limit 3G *************************** 1. row *************************** ... type: index possible_keys: thread_id key: PRIMARY key_len: 4 ref: const rows: 100000 r_rows: 100000.00 filtered: 8.96 r_filtered: 0.00 Extra: Using where ANALYZE 22
- 54. • CTEs • Not recursive MariaDB[employees]> with -> dept_data as -> (select emp_no, dept_name from dept_emp join departments using (dept_no) -> select first_name, last_name, dept_name -> from employees join dept_data using(emp_no) -> order by hire_date desc limit 3; +––––––––––––+–––––––––––+––––––––––––––––––––+ | first_name | last_name | dept_name | +––––––––––––+–––––––––––+––––––––––––––––––––+ | Bikash | Covnot | Quality Management | | Yucai | Gerlach | Production | | Hideyuki | Delgrande | Development | +––––––––––––+–––––––––––+––––––––––––––––––––+ 3 rows in set (0.00 sec) SQL DML 23
- 55. • CTEs • Recursive MariaDB[employees]> with recursive rand_generator(id, rand_value) as -> (select 1, rand() -> union all select id+1, rand() -> from rand_generator where id < 5) -> select * from rand_generator; +––––––+–––––––––––––––––––––+ | id | rand_value | +––––––+–––––––––––––––––––––+ | 1 | 0.5599308382346582 | | 2 | 0.2151867702744778 | | 3 | 0.39614136740205935 | | 4 | 0.33514655692050843 | | 5 | 0.4873087131300091 | +––––––+–––––––––––––––––––––+ 5 rows in set (0.00 sec) SQL DML 23
- 56. • Window functions MariaDB[employees]> select -> row_number() over win as id, dept_no, dept_name from departments -> window win -> as (order by dept_no); +––––+–––––––––+––––––––––––––––––––+ | id | dept_no | dept_name | +––––+–––––––––+––––––––––––––––––––+ | 1 | d001 | Marketing | | 2 | d002 | Finance | | 3 | d003 | Human Resources | | 4 | d004 | Production | | 5 | d005 | Development | | 6 | d006 | Quality Management | | 7 | d007 | Sales | | 8 | d008 | Research | | 9 | d009 | Customer Service | +––––+–––––––––+––––––––––––––––––––+ SQL DML 23
- 57. • Custom rulesvalidation CHECK Constraint 24
- 58. • Custom rulesvalidation MariaDB [test]> create table even (even_value int check(even_value % 2 = 0)) engine=innodb; Query OK, 0 rows affected (0.004 sec) MariaDB [test]> insert into even value(2); Query OK, 1 row affected (0.003 sec) MariaDB [test]> insert into even value(1); ERROR 4025 (23000): CONSTRAINT ‘even.even_value‘ failed for ‘test‘.‘even‘ CHECK Constraint 24
- 59. • No needto repeat GRANT MariaDB [test]> create role read_only, admin; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> grant select on *.* to read_only; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> grant super on *.* to admin; Query OK, 0 rows affected (0.001 sec) Roles 25
- 60. • No needto repeat GRANT MariaDB [test]> create user sveta; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> create user kaj; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> create user privileged; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> grant read_only to sveta; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> grant read_only to kaj; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> grant admin to privileged; Query OK, 0 rows affected (0.001 sec) Roles 25
- 61. • No needto repeat GRANT • DEFAULT roles Roles 25
- 62. • New versionshave new features • MariaDB implements advanced features early • Upgrade to new version • Explore all MariaDB advantages! Conclusions 26
- 63. • The S3 StorageEngine MariaDB ColumnStore Rewinding time with System Versioned Tables Invisible Columns Sequences INET6 Data Type WAIT and NOWAIT More Details 27
- 64. • Stored Aggregate Functions Histogram-BasedStatistics Engine Independent Statistics ANALYZE Statement Common Table Expressions Window Functions More Details 27
- 65.
- 66.
- 67.