No image

Overview of MySQL 8.0

Let's learn about MySQL

What is MySQL

MySQL is the most widely used relational database management system (RDBMS: Relational DBMS). MySQL is an open source and supports multiple users and multiple threads. In addition, we provide various APIs for various programming languages such as C language, C++, JAVA, PHP, etc. MySQL is available on a variety of operating systems, including UNIX, Linux, and Windows, and is especially frequently used in Web development with PHP. Although MySQL follows an open source license, commercial license must be purchased for commercial use.


Where we use MySQL

1.Government: To reduce the total cost of ownership by over 90%, it is effective to use open source software. Nowadays, governments adopt latest open source technologies and open data policies to make it easier and faster for agencies to develop applications, that deliver high quality services. — Example: US Navy, US Department of Commerce, NASA, open government


2. Education: Educational institutions need variety of database to cover e-learning system, student assessment applications and tuition payment processing systems. Schools also have to solve the budget pressure problem, and have to find a cost-effective solutions. — Example: Scholastic, CERN(The European Organization for Nuclear Research), The College of William & Mary, UDE (The University of Duisburg-Essen)

3. Bank: Even though most of the commercial banks use ‘OracleDB’ to manage customer assets, some of the Internet-specialized banks are using MySQL. One Internet-specialized bank uses MySQL DB on channel system. Channel is a function that links the bank’s internal system with the external system, which is considered as as important as the bank’s core system — the account system. It reduced the cost of using Oracle and tried to provide additional services to customers by solving the synchronization problem of Master and Slave server.


New Features of MySQL 8.0

MySQL 8.0 (the Latest Version): Developed since Sep. 2016 and has been stabilizing the system

1. InnoDB

-Changes to Auto_Increment persistence: Before the update, you had to initialize the auto_increment counter if the MySQL server is restarted because the counter was stored on memory, not disk. However, every time the automatic increment value changes, it is now recorded in the redo log and stored in the system table of the storage engine managing each checkpoint.

-NOWAIT and SKIP LOCKED: Up to MySQL version 5.7, if the application attempts to access the row for which the lock was requested, wait a limited amount of time and exceed it, a timeout occurs and the transaction has to be retried. In MySQL 8.0, these two new features (NOWAIT, SKIP LOCKED) allow timeout to be handled more effectively by implementing sophisticated lock-through scenarios.

2. Indexes

MySQL 5.7 only supported grammar. When you want to create a Descending index, an Ascending index was created that is possible without errors but not in reverse order. However ver8.0 provides ‘Descending Indexes’ to index in reverse order without compromising performance. Also, from MySQL version 8.0, visibility can be controlled using VISIBLE or INVISIBLE keywords as part of the index definition. Lastly the ability to index expression values rather than prefix values in columns has been newly supported.

3. JSON

As the requirement to store unstructured data such as JSON has increased, Document Store Databases like MongoDB have become popular, and are still widely used. In line with this trend, MySQL also supports JSON Data Types, which can store documents from 5.7 versions, and SQL Functions are available to create/change/reference them. MySQL 8.0 adds several features to improve performance, along with changes related to the function for handling JSON-type data. (JSON_PRETTY, JSON_STRORAGE_SIZE..etc)

Also, when you update the JSON data, MySQL 5.7 processed data by deleting and inserting it, but MySQL 8.0 allows only updates to the data that are being updated. Transforming JSON data to relational data: From MySQL 8.0, it is able to show JSON data in RDBMS table form.

4. SQL DDL

MySQL 8.0 adds a new algorithm called Instant for several DDL operations. For the Inplace algorithm used in previous versions, some DDL operations had to internally reconstruct tables and acquire locks in metadata at the start and end of the process. So the larger the table size, the longer it took, and always had to pay attention to the long-transactions that were performed during DDL. In addition, the DDL syntax was passed to the slave server and had to wait for the changed data to be reflected until the job was completed, so the replication delay was necessary. The new Instant algorithm is not only applicable by changing metadata in Data Dictionary (DD) without re-configuring tables, but also by eliminating the need to acquire locks of metadata, enabling faster processing than previous algorithms.


Features of Past MySQL versions

MySQL Server 5.5 is currently widely available (as of December 2010). It has the following performance improvements and features: The primary storage engine is Innodb, which supports transaction and reference integrity limits. / Improved Innodb I/O Subsystem / Enhanced SMP Support / Semi-synchronic replication / SIGNAL and RESIGNAL syntax corresponding to SQL standards / Unicode character sets utf16, utf32, and utf8mb4 / New options for custom partitioning.

MySQL Server 6.0.11-Alpha was announced on May 22, 2009 as the latest edition of the 6.0 line. Subsequent development of MySQL servers will use a new release model. 6.0 will be integrated into the release version in advance.

MySQL 5.6 will be the touchstone for the new release and was introduced at the 2011 MySQL User Conference. The new features allow better performance with data expansion through PERFORMANCE_SCHEMA, which improves performance for query optimizers and Innodb’s high-performance transformation results, new NoSQL-style memory cached APIs, improved partitioning for large tables and queries, and improved replication and performance monitoring.

Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]