10 New Features of MySQL Server 5.7, MySQL 5.7 Hosting

MySQL 5.7 introduced a native JSON data type, allowing for efficient storage and manipulation of JSON (JavaScript Object Notation) documents. It includes a set of functions for querying and modifying JSON data. The JSON data type is commonly used in scenarios where the data structure is dynamic or varies across records. It is suitable for storing configurations, application settings, complex objects, and other scenarios where a flexible and nested data structure is beneficial. The introduction of the JSON data type in MySQL provides developers with a powerful tool for handling flexible, semi-structured data, offering benefits such as a flexible schema, ease of integration with web applications, improved readability, and efficient querying capabilities. It aligns with modern application development practices, especially in scenarios where data structures are dynamic and evolving.

MySQL 5.7 introduced a NoSQL-style JSON document store using the X Plugin, allowing developers to work with both SQL and NoSQL paradigms within the same database. This provides flexibility in application development, allowing developers to choose the data model that best suits their requirements.

MySQL 5.7 enhanced security features, including automatic password expiration and account locking after multiple failed login attempts. These strengthened security measures will help reduce the risk of unauthorized access.

MySQL 5.7 enhanced the efficiency of the InnoDB storage engine with improvements to the buffer pool, resulting in better overall performance. InnoDB is a storage engine for MySQL that is well-suited for various workloads, particularly those that prioritize data integrity, concurrency, and transactional consistency.

MySQL Server 5.7 also enabled parallel execution of replication threads, enhancing the utilization of hardware resources and improving replication performance.

MySQL 5.7 introduces optimizer enhancements for better query performance by improving the cost model for query execution. The query optimizer is a crucial component of a relational database management system (RDBMS) that determines the most efficient way to execute a given SQL query. The cost model is a key aspect of the optimizer that assigns costs to different execution plans and helps the optimizer choose the plan with the lowest estimated cost. Some of the key cost model improvements includes Improved handling of Index Condition Pushdown (ICP), histograms for the InnoDB storage engine, batched key access, and Extension of Index Condition Pushdown to cover derived tables.

MySQL 5.7 improved spatial extensions by enhancing support for spatial data types and functions, catering to applications involving Geographic Information Systems (GIS), mapping, and location-based services. For example, MySQL 5.7 introduced spatial data types, including POINT, LINESTRING, POLYGON, GEOMETRY, and others, which allow you to store geometric objects representing points, lines, and polygons in the database. It also provides support for spatial indexing, enabling faster retrieval of spatial data based on location. Meanwhile, MySQL 5.7 comes with a set of spatial functions and operators that allow you to perform various operations on spatial data. These operations include distance calculations, intersection testing, area calculations, and more.

MySQL 5.7 introduced full-text search capabilities for the InnoDB storage engine, allowing efficient text searches on data stored in InnoDB tables. InnoDB Full-text Search provides a native, built-in solution for conducting full-text searches directly within the InnoDB storage engine. This eliminates the need for external full-text search engines or plugins. Besides this, it supports a "natural language" mode for search queries. In this mode, users can input simple phrases, and the search engine will attempt to find matches based on the natural language of the query. Additionally, InnoDB Full-text Search provides a "boolean" mode for more complex searches. Users can use boolean operators (AND, OR, NOT) and other advanced syntax to construct intricate search queries. It is also integrated into the SQL syntax, making it easy to incorporate full-text search queries into standard SQL statements. This seamless integration simplifies the process of combining text searches with other types of queries.

MySQL 5.7 improved support for online schema changes, allowing ALTER TABLE operations without locking the table for an extended period. This helps minimize downtime during schema modifications, enhancing the availability of the database.

Traditional ALTER TABLE operations often involve taking a lock on the entire table, which can lead to downtime and impact the availability of the database. Online ALTER TABLE in MySQL 5.7 aims to minimize table locking, allowing some types of schema changes to be performed without blocking concurrent read and write operations. Online ALTER TABLE supports concurrent read and write operations on the table being altered. While the alteration is in progress, new transactions can continue to read and write data to the table without being blocked. In some cases, Online ALTER TABLE may take advantage of parallel processing, allowing the alteration to be performed more efficiently. However, the level of parallelism depends on the specific alteration and the MySQL version. While Online ALTER TABLE provides benefits for certain operations, not all alterations can be performed online. Some alterations may still require exclusive locks and result in downtime.

MySQL 5.7 introduced support for generated columns, allowing column values to be computed based on an expression or function. These columns are known as "generated" because their values are computed or generated based on the specified expressions during INSERT or UPDATE operations, rather than being explicitly provided by the user.

When creating a table, you can define a column as a generated column by specifying a generation expression. The syntax for creating a generated column is as follows:

column_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY] | [PRIMARY] KEY]

In this code, GENERATED ALWAYS specifies that the column is always generated and cannot be explicitly set by the user. AS (expression) specifies the expression used to compute the column's value. VIRTUAL or STORED determines whether the column's values are computed on-the-fly (VIRTUAL) or stored in the table (STORED).

Computed or derived columns are useful for scenarios where certain values in a table can be derived from other columns or expressions. This can simplify data management and ensure consistency.

MySQL 5.7 introduced the SYS schema, a set of objects providing insights into server internals, aiding administrators in monitoring and troubleshooting.

The SYS schema refers to a set of views, procedures, and functions provided by MySQL that collectively form a schema named "SYS." It simplifies the process of querying and understanding server-related information. It introduces a standardized set of views and procedures, making it easier for users to access and interpret the data they need. To use the SYS schema, you typically need to have the necessary privileges to access the relevant views and procedures. Queries against SYS schema views can provide valuable information for monitoring and optimizing the performance of the MySQL server.

Here's a simple example of querying the sys.processlist view in the SYS schema:

SELECT * FROM sys.processlist;

This query retrieves information about the current threads and processes running on the MySQL server.