Mysql database optimization one (storage engine)

Preface

Database optimization is a commonplace question. Novices who are just getting started or have a bald head for N years should be familiar with this problem. If you want to interview a middle-level engineer, then he will be as sticky as "good brothers", and the interviewer will definitely Ask this question, this article we are good with his brother! And this question is a sub-question. The optimization plan of the database is basically the same, and the answer is also fixed. As long as you prepare this question well, you will not be able to stay. You can arrange an interviewer during the interview, otherwise it will be arranged by the interviewer. ! Not much to say, let’s talk about database optimization below!

related articles

Mysql database optimization two (database design)
Mysql database optimization three (SQL optimization)

Storage engine

Introduction

The database storage engine is the underlying component of the database. The database management system uses the data engine to create, query, update, and delete data. Different storage engines provide different storage mechanisms, indexing techniques, locking levels and other functions, and specific functions can also be obtained by using different storage engines. We can use SHOW ENGINES

Insert picture description here

InnoDB storage engine

InnoDB is getting better and better. Since MySQL 5.5, MySQL’s default built-in storage engine is already InnoDB. The main features are

  • Disaster recovery is better
  • Support transaction, the default transaction isolation category is repeatable read
  • The lock granularity used is row lock, which can support higher concurrency
  • Support foreign keys
  • With some hot backup tools can support online hot backup
  • In InnoDB, there is buffer management. Through the buffer pool, all indexes and data are cached to speed up the query.
  • For InnoDB type tables, the physical organization of the data is a clustered table. All data is organized according to the primary key. Sort according to the primary key, the data and the index are put together, and they are all located on the leaf node of the B+ number

MyISAM storage engine

MyISAM engine is the default storage engine of MySQL relational database system (before mysql 5.5.5). This MySQL table storage structure extends many useful functions from the old ISAM code. In the new version of Mysql, the Innodb engine has gradually replaced the Myisam engine due to its advantages in transaction referential integrity and higher concurrency.

  • Does not support transactions
  • Table-level locking
  • Reading and writing block each other: Not only will it block reading when writing, myisam will also block writing when reading, but reading itself does not block other reads.
  • Only cache indexes: Myisam can cache indexes through key_buffer_size to greatly improve access performance and reduce product IO, but this cache area only caches indexes, not data.
  • Reading speed is faster, occupying relatively little resources
  • Does not support foreign key constraints, but supports full-text indexing

MEMORY storage engine

MEMORY is a special kind of storage engine in MySQL. It uses the content stored in memory to create a table, and the data is all stored in memory. These characteristics are very different from the previous two.

  • Support HASH index and BTree index (MyISAM and InnoDB only support BTree index)
  • All fields are fixed length varchar(10)=char(10)
  • Large fields such as BLOB and TEXT are not supported, because its data is stored in memory and should not be too large
  • Memory storage engine uses table-level locks

ARCHIVE storage engine

The ARCHIVE storage engine is suitable for limited scenarios. Because it supports compression, it is mainly used to archive logs, pipelines and other data. The main features are

  • Support Zlib compression, data will be compressed before being inserted into the table
  • Only SELECT and INSERT operations are supported, and the stored data can only be queried, and cannot be modified or deleted;
  • Only supports indexes on auto-increment keys, not other indexes

CSV storage engine

Data transfer trial, the main features are

  • The data format is text in .csv format, which can be edited and saved directly
  • Import and export are more convenient, you can directly export the data in a table as csv, try Excel office software to open

Selection basis

If there is no special requirement, the InnoDB engine can be used by default

MyISAM: Applications that focus on reading and writing, such as blog systems and news portals.

Innodb: update (delete) operation frequency is also high, or to ensure data integrity; high concurrency, support transactions and foreign keys to ensure data integrity. Such as OA automated office system

In addition, it is recommended that friends take an active part in: C Station Evaluation Challenge