MySQL 8.0
0 likes47 views
Overview of some of the improvements which has been made in MySQL version 8.0 the newest MySQL release! 際際滷s in Russian
![Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. |
丕仍亠仆亳亠 仂仗舒 舒亰舒弍仂亳从仂于 JSON
亳舒仗舒亰仂仆 于 于舒亢亠仆亳 JSON Path:
仗亳仄亳亰舒仂 仗仂亟亟亠亢亳于舒亠 仂弍仆仂于仍亠仆亳 仆舒 仄亠亠
丕仍亠仆亳亠 仗仂亳亰于仂亟亳亠仍仆仂亳
20
MySQL 8.0
SELECT doc->>"$[last]" AS last from t1;
+------+
| last |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
+----------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
+----------------------------------------------+
| [2, 3, 4] |
+----------------------------------------------+
1 row in set (0.00 sec)](https://image.slidesharecdn.com/08mysql8-180530090311/85/MySQL-8-0-20-320.jpg)
![Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. |
亳仄亠 JSON_TABLE
SELECT id,amenity_type, distance FROM seats,
JSON_TABLE(doc, "$.properties.amenities[*]" COLUMNS (
id for ordinality, amenity_type VARCHAR(100) PATH "$.type",
distance float PATH '$.distance_in_meters')
) AS amenities
WHERE seats.id = 28100 AND amenities.amenity_type IN ('snacks', 'bar');
+--------+------------------+----------+
| id | amenity_type | distance |
+------+--------------------+----------+
| 2 | bar | 100.538 |
| 3 | snacks | 136.647 |
+--------+------------------+----------+
21
MySQL 8.0](https://image.slidesharecdn.com/08mysql8-180530090311/85/MySQL-8-0-21-320.jpg)
More Related Content
What's hot (20)
Similar to MySQL 8.0 (20)
More from Vittorio Cioe (6)
MySQL 8.0
- 1. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 8.0 Vittorio Cioe MySQL Sr. Sales Consultant vittorio.cioe@oracle.com
- 2. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracles products remains at the sole discretion of Oracle. 2
- 3. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 8.0.11 亠仗亠 亟仂仗亠仆! 2 亞仂亟舒 于 舒亰舒弍仂从亠 400+ 舒弍仂亳 亢仆舒仍仂于 5000+ 亳仗舒于仍亠仆 仂亳弍从亳 500 仆仂于 亠仂于 3
- 4. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | Transactional (InnoDB) Data Dictionary Persistent runtime configuration changes (SET PERSIST) INVISIBLE indexes Common Table Expressions Windows Functions New default: utf8mb4 (UTF 9) Expanded GIS Support Expanded Query Hints Support Improved Scan Query Performance Improved BLOB Storage Improved InnoDB Memcached Interface Descending indexes Scalability Improvements Parser Refactoring New! Document Store Improvements to Temporary Tables C++11 and Toolchain Improvements Replication Applier Thread Progress Reports GTID_PURGED always settable Auto-increment counter value will now persist across server restarts! Indexing of PS tables SQL roles (collection of privileges) MySQL privilege tables are now InnoDB 4 MySQL 8.0: 亠 仂 亳 仄仆仂亞仂亠 亟亞仂亠 ....
- 5. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | 丱仂仂亳亠 仆仂于仂亳 亟仍 舒亟仄亳仆亳舒仂仂于 Copyright 息 2015, Oracle and/or its affiliates. All rights reserved.
- 6. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | 6 舒弍仂 亳仄于仂仍仂于 亟仍 仆亠仆亠舒 MySQL 8.0: UTF-8 仂于仂! 仂亟亟亠亢从舒 仆仂于亠亶亠亞仂 Unicode 9.0 舒于亳仍舒 仂亳仂于从亳 (collation) 亰舒于亳 仂 舒仆 丼于于亳亠仍仆仂 从 舒从亠仆 丼于于亳亠仍仆仂 从 亠亞亳 亳仄于仂仍仂于 UTF8MB4 从舒从 仆舒弍仂 亳仄于仂仍仂于 仗仂 仄仂仍舒仆亳 仂亠从 亰舒仗亠仆 亢亠 于 MySQL 5.7 仆仂亞亳亠 仍亠仆亳 亟仍 仆亳亢亠仆亳 于仂亰亟亠亶于亳 仆舒 仗仂亳亰于仂亟亳亠仍仆仂
- 7. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | UTF-8 UTF-8 磦仍磳 亟仂仄亳仆亳ﾑ亳仄 仆舒弍仂仂仄 亳仄于仂仍仂于 于 仂于亠仄亠仆仆 仗亳仍仂亢亠仆亳 丐亠弍亠 1-4 弍舒亶舒 亟仍 舒仆亠仆亳 亳仄于仂仍仂于 仂亳亠从仂 仍仂亢亳于亳亶 仗仂弍仍亠仄舒 仗仂亳亰于仂亟亳亠仍仆仂 亠亟亳亶 仆舒弍仂 亳仄于仂仍仂于 于 仆亠仆亠亠 7 https://en.wikipedia.org/wiki/UTF-8
- 8. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 8.0 vs MySQL 5.7 utf8mb4 8 0 45000 90000 135000 180000 8 16 64 128 512 1024 SELECT DISTINCT_RANGES 0 10000 20000 30000 40000 8 16 64 128 512 1024 OLTP RO 0 7500 15000 22500 30000 8 16 64 128 512 1024 OLTP RW +300-350% in OLTP RO +176-233% in OLTP RW +1500-1800% in SELECT DISTINCT_RANGES
- 9. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | 仂于亠仆仆舒 仆舒亟亠亢仆仂 仂仂亠仆仂 仆舒 InnoDB 亠 舒亶仍仂于 FRM 亠 舒亶仍仂于DB.OPT 亠 舒亶仍仂于 TRG 亠 舒亶仍仂于 TRN 亠 舒亶仍仂于 PAR MyISAM 亢亠 仆亠 亠弍亠 仂仄仆亶 DDL 舒 亠仗仍亳从舒亳 丐舒仆亰舒从亳仂仆仆仂c DML 亟仍 于仆亠仆亳 从仍ﾑ亠亶 API 亟仍 亞亳弍从亳 仄亠舒亟舒仆仆 仂仂亶 仗 从 亟仂弍舒于仍亠仆亳 仆仂于 仆从亳亶 丕仍亠仆仆亶 从仂- 仗仍舒仂仄亠仆仆亶 仂仗 亳从舒从亳 亰舒于亳亳仄仂亠亶 仂 亠仄舒仆亳从亳 舒亶仍仂于仂亶 亳亠仄 9 MySQL 8.0: 仂亠仆仆亶 仍仂于舒 亟舒仆仆
- 10. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | 10 MySQL 8.0: 仂仄舒仆仂 于 仗亳于亳仍亠亞亳 丐舒弍仍亳 仗亳于亳仍亠亞亳亶 亠仗亠 100% 仆舒 InnoDB 仂仄仆亠 DDL 亟仍 仗舒于仍亠仆亳 仗仂仍亰仂于舒亠仍礆亳 CREATE USER ALTER USER RENAME USER DROP USER GRANT REVOKE
- 11. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 8.0: 仂仍亳 弍亠亰仂仗舒仆仂亳 仂仍仆仂 仆从亳仂仆舒仍仆亠, 亞亳弍从亳亠, 仗舒于亳仍仆仂 仆舒仂亠仆仆亠 仂仍亳 弌仂亰亟舒仆亳亠 亳 亟舒仍亠仆亳亠 仂仍亠亶, 于亟舒于舒仆亳亠 仂仍亠亶 亟舒于舒仆亳亠 仂仍亳 仂仍礆, 于亟舒于舒仆亳亠 仂仍亳 仗仂仍亰仂于舒亠仍礆 亞舒仆亳亳 仂, 从仂仂亠 仄仂亞 亳仗仂仍亰仂于舒 仂仍亳, 仗亠亟亠仍亳 仂仍亳 仗仂 仄仂仍舒仆亳 亟舒亢亠 于亳亰舒仍亳亰亳仂于舒 仂仍亳 仗仂仄仂 仆从亳亳 SQL ROLES_GRAPHML ()
- 12. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | 12 丕仍亠仆仂, 仂弍 舒仄仂亠 于仂亰仄仂亢仆仂 亳仗仂仍亰仂于舒仆亳 仗仍舒 弍亠仂于 仂亟亠仍 亰舒舒 仗亳仄亳亰舒仂舒 Feature Request from DBAs SELECT * FROM Country WHERE population > 20000000; 仂亟亠仍 亟仍 从舒仆亳仂于舒仆亳 舒弍仍亳: # pages in table * (IO_BLOCK_READ_COST | MEMORY_BLOCK_READ_COST) # records * ROW_EVALUATE_COST = 25.4 100% in memory = 29.9 100% on disk 仂亟亠仍 亟仍 从舒仆亳仂于舒仆亳 于 亟亳舒仗舒亰仂仆亠: # records_in_range * (IO_BLOCK_READ_COST | MEMORY_BLOCK_READ_COS T) # records_in_range * ROW_EVALUATE_COST + # records_in_range * ROW_EVALUATE_COST = 22.5 100% in memory = 60 100% on disk 仂 亳 仗仂 MySQL 于亠亞亟舒 仗亠亟仗仂仍舒亞舒仍舒, 仂 亟舒仆仆亠 弍仍亳 舒亰仄亠亠仆 仆舒 亟亳从亠 仗亳 仂亠仆从亠 (亳仍亳 仄仂亟亠仍亳仂于舒仆亳亳) 仂亳仄仂亳 亰舒仗仂舒. MySQL 8.0 亳于舒亠, 从舒从舒 舒 亳仆亟亠从舒 仆舒仂亟亳 于 仗舒仄亳, 亳 亟亳仆舒仄亳亠从亳 仆亳亢舒亠 仂亳仄仂 仗仍舒仆舒.
- 13. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | 13 MySQL 8.0: 亠于亳亟亳仄亠 亳仆亟亠从 仆亟亠从 束从損 亟仍 仂仗亳仄亳亰舒仂舒 MySQL 亠 仂 亢亠 舒仄仂亠, 仂 束仂从仍ﾑ亠仆仆亠 亳仆亟亠从損 弌仂亟亠亢亳仄仂亠 仗仂仍仆仂 仂弍仆仂于仍亠仆仂 亳 仗仂亟亟亠亢亳于舒亠 DML 于舒 于舒亳舒仆舒 亳仗仂仍亰仂于舒仆亳: 磪从仂亠 亟舒仍亠仆亳亠 (从仂亰亳仆舒) 仍舒仆亳亠仄亶 于仗从 Feature Request from DBAs
- 14. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 8.0: 弌仂于仄亠亳仄仂 仂弍仍舒从仂仄 亳 从仂仆亠亶仆亠舒仄亳 仂于舒 仗亠亠仄亠仆仆舒 innodb_dedicated_server 亰舒于亳亳仄仂亳 仂 仂弍亠仄舒 仗舒仄亳, 仂弍仆舒亢亠仆仆仂亶 仆舒 亠于亠亠, 舒于仂仄舒亳亠从亳 仆舒舒亳于舒ﾑ innodb_buffer_pool_size, innodb_log_file_size, innodb_flush_method 仂仂礌仆仂亠 亳亰仄亠仆亠仆亳亠 从仂仆亳亞舒亳亳 于仂 于亠仄 于仗仂仍仆亠仆亳 SET PERSIST variable_name = value; ﾐ頴委 仗亠亠仄亠仆仆舒 从仂仆亳亞舒亳亳 束GLOBAL DYNAMIC損 仄仂亢亠 弍 亳亰仄亠仆亠仆舒 舒仂亶从亳 弍亟 仂舒仆 于 舒亶仍亠 亳仄亠仆亠仄 mysqld-auto.cnf (仂仄舒 JSON) 仂于舒 舒弍仍亳舒 performance_schema.variables_info 14
- 15. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | 丱仂仂亳亠 仆仂于仂亳 亟仍 舒亰舒弍仂亳从仂于 Copyright 息 2015, Oracle and/or its affiliates. All rights reserved.
- 16. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 8.0: 亠从亳于仆亶 CTE 16 Feature Request from Developers
- 17. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 8.0: 个仆从亳亳 仂从仆舒 17 Feature Request from Developers 仗仂仍亰亶亠 仂弍仆亠 舒亞亠亞舒仆亠 仆从亳亳 亳仍亳 ... RANK(), DENSE_RANK(), PERCENT_RANK() CUME_DIST() NTILE() ROW_NUMBER() FIRST_VALUE(), LAST_VALUE(), NTH_VALUE() LEAD(), LAG()
- 18. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | 丕仍亠仆亳 于 弌 仂亟亟亠亢从舒 亞亠仂亞舒亳亳 仂亟亟亠亢从舒 仗仂舒仆于亠仆仆 仂仗仂仆 亳亠仄 (SRS) 亠亟舒于仍亠仆亳 亠仄 亳仆仂仄舒亳亳 SQL/MM 弌舒仆亟舒仆仂亠 仂亞仍舒仂于舒仆亳亠 仂亳 于 仆从亳亳 亳仄仗仂舒 / 从仗仂舒 仗仂仄仂亞舒亠仍仆亠 仆从亳亳 亟仍 仂弍舒弍仂从亳 亳 仗亠仂弍舒亰仂于舒仆亳 亟舒仆仆: st_y(geom, y) st_srid(geom, srid) 18
- 19. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | 个仆从亳亳 JSON 19 MySQL 5.7 亳 8.0 JSON_ARRAY_APPEND() JSON_ARRAY_INSERT() JSON_ARRAY() JSON_CONTAINS_PATH() JSON_CONTAINS() JSON_DEPTH() JSON_EXTRACT() JSON_INSERT() JSON_KEYS() JSON_LENGTH() JSON_MERGE() JSON_OBJECT() JSON_QUOTE() JSON_REMOVE() JSON_REPLACE() JSON_SEARCH() JSON_SET() JSON_TYPE() JSON_UNQUOTE() JSON_VALID() JSON_PRETTY() JSON_STORAGE_SIZE() JSON_STORAGE_FREE() JSON_ARRAYAGG() JSON_OBJECTAGG()
- 20. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | 丕仍亠仆亳亠 仂仗舒 舒亰舒弍仂亳从仂于 JSON 亳舒仗舒亰仂仆 于 于舒亢亠仆亳 JSON Path: 仗亳仄亳亰舒仂 仗仂亟亟亠亢亳于舒亠 仂弍仆仂于仍亠仆亳 仆舒 仄亠亠 丕仍亠仆亳亠 仗仂亳亰于仂亟亳亠仍仆仂亳 20 MySQL 8.0 SELECT doc->>"$[last]" AS last from t1; +------+ | last | +------+ | 5 | +------+ 1 row in set (0.00 sec) SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]'); +----------------------------------------------+ | JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') | +----------------------------------------------+ | [2, 3, 4] | +----------------------------------------------+ 1 row in set (0.00 sec)
- 21. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | 亳仄亠 JSON_TABLE SELECT id,amenity_type, distance FROM seats, JSON_TABLE(doc, "$.properties.amenities[*]" COLUMNS ( id for ordinality, amenity_type VARCHAR(100) PATH "$.type", distance float PATH '$.distance_in_meters') ) AS amenities WHERE seats.id = 28100 AND amenities.amenity_type IN ('snacks', 'bar'); +--------+------------------+----------+ | id | amenity_type | distance | +------+--------------------+----------+ | 2 | bar | 100.538 | | 3 | snacks | 136.647 | +--------+------------------+----------+ 21 MySQL 8.0
- 22. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | 仂于仂! MySQL InnoDB Cluster X-Dev API 弌仂于亠仄亠仆仆亶: 亞亳弍从亳亶 API, 亠仗仂从舒 仄亠仂亟仂于 弌亠舒仆 弍亠亰 亠舒 仂仂礌亳 仂弍亠仗亠亳于舒ﾑ 仗仂亰舒仆仂亠 仄舒舒弍亳仂于舒仆亳亠 于仂 仄仆仂亞仂-亠于亠仆 亠亟舒 仂亟亟亠亢从舒 SQL CRUD 亟仍 从仂仍仍亠从亳亶 亟仂从仄亠仆仂于 亳 舒弍仍亳 仂从仄亠仆 从舒从 仗仂亠 亳 弍舒亰仂于亠 仂弍亠从 亟仂仄亠仆舒 舒亢亠仆亳 仗仂亳从舒 仂仂于亠于ﾑ 于舒亢亠仆亳礆 SQL SELECT 亠舒仍亳亰仂于舒仆仂 于 仂弍仂仍仂从亠 MySQL 亳 从仂仆仆亠从仂舒 MySQL 仂于仂! MySQL 仂仆仆亠从仂/node.js MySQL 仂仆仆亠从仂/J MySQL 仂仆仆亠从仂/Net 22
- 23. Copyright 息 2015, Oracle and/or its affiliates. All rights reserved. | MySQL 从舒从 舒仆亳仍亳亠 亟仂从仄亠仆仂于 1. 丕仍亠仆仆舒 仗仂亟亟亠亢从舒 亳仗舒 亟舒仆仆 JSON 2. 仗亠舒亳亳 CRUD 3. X-Plugin 亳 X Protocol 4. 丕仍亠仆仆舒 仂弍仂仍仂从舒 MySQL
- 24. Copyright 息 2017, Oracle and/or its affiliates. All rights reserved. | 弌仍亠亟ﾑ亳亠 舒亞亳 丕亰仆舒 弍仂仍亠 https://dev.mysql.com/doc/relnotes/mysql/8.0/en https://dev.mysql.com/doc/refman/8.0/en/ https://dev.mysql.com/doc/refman/8.0/en/document-store.html 仂仗仂弍仂于舒 dev.mysql.com/downloads/ https://labs.mysql.com/ 弌仂仂弍亳亠 仆舒仄 于仂亠 仄仆亠仆亳亠 http://mysqlhighavailability.com/ http://forums.mysql.com/list.php?25 9th April 2015 Copyright 2015, oracle and/or its affiliates. All rights reserved 24