MariaDB command introduction and MariaDB architecture and Index

MariaDB command introduction and MariaDB architecture and Index

Mariadb> show global variables; global variables affect GLOBAL operations on the server.

Mariadb> SHOW [SESSION] VARIABLES; client variable, effective only for the current SESSION

Note: Some of these parameters can be modified at run time and will take effect immediately. Some parameters are not supported and can only take effect by modifying the configuration file and restarting the server program.

Some parameter scopes are global and cannot be changed. Some parameters can be set separately for each user.

Modify the server variable value

GLOBAL: mariadb> set global system_varname = value;

Mariadb> SET @ global. system_varname = value;

SESSION: mariadb> SET [SESSION] system_varname = value;

Example: set global SQL _mode = strict_trans_tables; Common MODE: TRADITIONAL, STRICT_TRANS_TABLES

SQL _mode is empty by default. It is recommended that developers set this value to the strict mode, so that some problems can be found in the database design and development phase, if such problems are found after running the database in the production environment, the cost of modification will become very huge.

Status variable: a variable used to save statistics in mariadb running.

Mariadb> show global status;

Mariadb> SHOW [SESSION] STATUS;

 

How to obtain the default configuration used by the Program: mysql -- print-defaults (client) mysqld -- print-defaults (server)

Mariadb works in the single-process multi-thread mode. The following figure shows the mariadb architecture.

Mariadb query execution path

1: the client sends a query to the server.

2: The server first checks the query cache. If the cache is hit, the results stored in the cache are immediately returned. Otherwise, enter the next stage.

3: The server performs SQL parsing and preprocessing, and then the optimizer generates the corresponding execution plan.

4: MariaDB calls the storage engine API to execute the query according to the execution plan generated by the optimizer.

5: return the result to the client

The figure above shows that the StuID field in the entire students table has an index called PRIMARY.

Key: PRIMARY the index type: const used in this query indicates to query a row, and directly load a row (rows: 1) in the database. This is the index effect, locate the row to be loaded through the index

There is no index in the age field, type: ALL: The full table scan is performed, 25 rows (rows: 25) are taken ), obtain all rows and use the where condition to filter each row one by one.

(Extra: Using where)

If the data in the table is large, the second query will be slow compared to the first query !!

 

Query cache:

Different query statements may cause cache hits, and cached data is stored in the memory.

Which queries may not be cached: queries that contain udfs, storage functions, User-Defined variables, temporary tables, system tables in mysql databases, or columns containing Permissions

Tables, functions with uncertain values (Now ())

 

Query cache-related server variables: query_cache_min_res_unit: query the minimum allocation unit of memory blocks in the cache.

A smaller value will reduce the waste, but will lead to more frequent Memory Allocation Operations. A larger value will lead to a waste, resulting in excessive fragments.

Query_cache_limit: Maximum query result that can be cached

Query_cache_size: total available memory space for querying the cache. The unit is byte and must be an integer multiple of 1024. The default value is 0, indicating that the cache is not enabled.

Query_cache_wlock_invalidate: If a table is locked by other connections, can it still return results from the query cache? The default value is OFF, indicating that the table can be accessed.

In other connected scenarios, data is returned from the cache; ON indicates that data is not allowed.

The preceding variables can be obtained by running the show global variables like '% query %' command.

View cache hits: show global status like 'qcache % ';

View query times show global status like 'com _ select ';

Evaluate the cache hit rate: Qcache_hits/(Qcache_hits + Com_select)

Linux Tutorial: How to check the MariaDB server version

Implementation of MariaDB Proxy read/write splitting

How to compile and install the MariaDB database in Linux

Install MariaDB database using yum in CentOS

Install MariaDB and MySQL

How to migrate MySQL 5.5 database to MariaDB 10 on Ubuntu

Install MariaDB on the Ubuntu 14.04 (Trusty) Server