Shulou(Shulou.com)06/01 Report--

The following mainly brings you a detailed introduction of MySql knowledge. I hope these contents can bring you practical use, which is also the main purpose of this article that I edit a detailed introduction of MySql knowledge. All right, don't talk too much nonsense, let's just read the following.

MySQL is an open source relational database management system (RDBMS), which uses the most commonly used database management language-structured query language (SQL) for database management.

MySQL is open source, so anyone can download it under the license of General Public License and modify it according to their individual needs.

MySQL has attracted much attention because of its speed, reliability and adaptability. Most people agree that MySQL is the best choice for managing content without the need for transactional processing.

Recommended tutorial: mysql getting started Video tutorial

Introduction to MySQL

MySQL is an open source relational database management system (RDBMS). The MySQL database system uses the most commonly used database management language-structured query language (SQL) for database management.

Because MySQL is open source, anyone can download it under the license of General Public License and modify it according to individual needs. MySQL has attracted much attention because of its speed, reliability and adaptability. Most people agree that MySQL is the best choice for managing content without the need for transactional processing.

The origin of the name MySQL is not clear. A more influential theory is that basic guidelines and a large number of libraries and tools have been prefixed with "my" for more than 10 years, and in any case, the daughter of Monty Widenius, one of the founders of MySQL AB, is also called My. Which of these two gave the name MySQL is still a mystery, including developers.

MySQL's dolphin logo, named "sakila", was selected by the founder of MySQL AB from a large list of names suggested by users in the "dolphin naming" competition. The winning name was provided by Ambrose Twebaze, an open source software developer from Eswatini in Africa. According to Ambrose, Sakila comes from an Eswatini dialect called SiSwati, which is also the name of a small town in Arusha, Tanzania, near Ambrose's hometown of Uganda.

MySQL, although the function may not be very powerful, but because of its open source and wide spread, many people know about this database. Its history is also legendary.

Database optimization

Select InnoDB as the storage engine

Databases of large products have higher requirements for reliability and concurrency. InnoDB, as the default MySQL storage engine, is a better choice than MyISAM.

Optimize database structure

Organize the schema, tables, and fields of the database to reduce the cost of iCandle O, keep related items together, and plan ahead so that performance can be maintained at a high level as the amount of data grows.

The design of the data table should minimize the space it takes up and the primary key of the table should be as short as possible. For InnoDB tables, the column in which the primary key resides is replicable in each secondary index entry, so if there are many secondary indexes, a short primary key can save a lot of space.

Create only indexes that you need to improve query performance. The index facilitates retrieval, but increases the execution time of insert and update operations.

ChangeBuffering characteristics of InnoDB

InnoDB provides a configuration of changebuffering to reduce the disk Icano required to maintain the secondary index. Large-scale databases may encounter a large number of table operations and a large number of I hand O to keep secondary indexes up-to-date. When the relevant page is not in the buffer pool, the changebuffer of InnoDB will change the cache to the secondary index entry, thus avoiding the time-consuming IUnio operation caused by not being able to read the page from disk immediately. When the page is loaded into the buffer pool, the buffered changes are merged and the updated page is then flushed to disk. This improves performance and is suitable for MySQL5.5 and later versions.

InnoDB page compression

InnoDB supports page-level compression of tables. When a data page is written, there is a specific compression algorithm to compress it. The compressed data is written to disk, and the punching mechanism releases the empty blocks at the end of the page. If compression fails, the data is written as is. Both tables and indexes are compressed, because indexes are usually a large part of the total database size, and compression can significantly save memory, Imax O, or processing time, thus improving performance and scalability. It also reduces the amount of data transferred between memory and disk. This feature is supported by MySQL5.1 and later.

Note that page compression does not support tables in shared tablespaces. Shared table spaces include system table spaces, temporary table spaces, and regular table spaces.

Use bulk data Import

Importing bulk data using sorted data sources on the primary key speeds up the data insertion process. Otherwise, you may need to insert rows between other rows to maintain sorting, which can cause disk Imax O to become higher, which in turn affects performance and increases page splitting. Turning off the autocommit mode is also beneficial because it flushes the log to disk for each insert. Temporary transfer of unique keys and foreign key checks during bulk insertion can also significantly reduce disk Imax O. For newly created tables, it is best to create foreign key / unique key constraints after bulk import.

Once your data has reached a stable size, or the growing table has increased by tens or hundreds of megabytes, you should consider using the OPTIMIZETABLEstatement to reorganize the table and compress the wasted space. A full table scan of the reorganized table requires less I _ hand O.

Optimize InnoDB disk iDUBO

Increasing the size of the InnoDB buffer pool allows queries to be accessed from the buffer pool rather than through the disk Ihop O. The index of clearing buffer is adjusted to reach the best level by adjusting the system variable innodb_flush_method.

Memory allocation of MySQL

Before allocating enough memory for MySQL, consider the memory requirements for MySQL in different areas. The key area to consider is concurrent connections-for a large number of concurrent connections, sorting and temporary tables will require a lot of memory. At the time of this writing, 16GB to 32GB RAM is sufficient for databases that handle 3000 + concurrent connections.

Memory fragmentation can consume about 10% or more of memory. Caches and buffers such as innodb_buffer_pool_size, key_buffer_size, and query_cache_size consume about 80% of the allocated memory.

Daily maintenance

Periodically check the slow query log and optimize the query mechanism to effectively use the cache to reduce disk Imax O. Optimize them to scan the minimum number of rows instead of doing a full table scan.

Other logs that can help DBA check and analyze performance include error logs, regular query logs, binary logs, and DDL logs (metadata logs).

Periodically flush caches and buffers to reduce fragmentation. Use the OPTIMIZETABLEstatement to reorganize the table and compress any space that may be wasted. [1]

For the above detailed introduction of MySql knowledge, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.