MySQL database storage engine and database optimization

Storage Engine

(1) MySQL can store data in a file (memory) with different techniques, which becomes the storage engine.

Each memory engine uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different functions.

(2) Different types of tables can also be used for different storage engines

(3) MySQL-supported storage engine

• 1. MyISAM
• 1. InnoDB
• 1. Memory
• 1. Csv
• 1. Archive

To view the creation statement for a data table:

SHOW CREATE TABLE 表名

Related Concepts :
(1). concurrency control : One person reads the data and the other person deletes the data.

Ensure data consistency and integrity when multiple connections modify records. The system uses a lock system to solve this concurrency control, which is divided into:

1). shared Lock (read lock)-At the same time, multiple users can read the same resource without any changes to the data during the read.

2). Exclusive (write-lock)-only one user can write to the resource at any time, and when a write lock is made, it blocks other read or write locks.

3. The force of the lock (also called the particle of the lock)

Lock particles (units when locked)

- table lock , which is a locking strategy with minimal overhead. Get a write lock on a data table

- row lock , which is the most expensive lock policy. Maximum parallelism

The cost of table locks is minimal, because the number of locks is minimized, and the cost of row locks is the largest because more locks may be used.

Concurrency of

is to ensure the integrity and consistency of the data when multiple links operate on the same data.

Transaction characteristics--"transfer business: Subtract 100 from one person, plus 100 for another person.

Transactions (which contain a sequence of operations, a transaction (Transaction) is a unit of work performed on a database) to protect the integrity of the data. Several processes are restored to the original data as a whole, i.e. every process error occurs

1. atomicity (atomicity): ensures that all operations within the work unit are completed successfully, otherwise the transaction terminates in the event of a failure, and the previous operation is rolled back to its previous state.

2. consistency (consistency): Ensure that the database correctly changes state on a successfully committed transaction.

3. Isolation (Isolation): Make transactional operations independent and transparent.

4. Persistence (Durability): Ensure that the result or effect of a committed transaction persists in the event of a system failure.

ACID;

Foreign keys and indexes

1, FOREIGN key: the strategy of ensuring data consistency
2, Index: Similar to the table of contents, is a column or columns of data in the list of the values of a structure, convenient to quickly find data

Index: Normal index, unique index, full-text index, btree index, hash index ...

Features of various storage engines

The most used: Myisam,innodb.

MyISAM: It is suitable for the processing of transaction, which supports data compression and large capacity;
InnoDB: It is suitable for more transaction processing and requires foreign key support.

CSV storage Engine: comma-delimited, index not supported;
Blackhole: Black hole engine, write data will disappear, generally used to do data replication relay;

Storage Engine:
MyISAM: Storage limit up to 256TB, support index, table level lock, data compression
InnoDB: Storage limit is 64TB, transactions and indexes are supported, lock grain is row lock.

Setting up the storage engine
(1) by modifying the MySQL configuration file implementation

default-storage-engine = engine

(2) by creating a data Table command to implement

CREATE TABLE table_name(...) ENGINE = engine;

For example:

CREATE TABLE tp1(s1 VARCHAR(10)) ENGINE = MyISAM;SHOW CREATE TABLE tp1; // 查看数据表的结构

(3) By modifying the data Table command to achieve

ALTER TABLE table_name ENGINE [=] engine_name;

For example:

ALTER TABLE tp1 ENGINE = InnoDB;

MySQL Database optimization

1. Maintenance of data dictionary

Maintain Data dictionary:

1. Third-party tools: for different DBMS
2. Use the Memo field of the database itself: Add a Memo field to the table and column, for example

3. Export Data dictionary (very common) but note: When you change the table notes, you only need to change the table notes, which
The properties of some of his columns (column length, width, non-null) must remain the same

2. Maintenance Index

Indexed columns:

• 1. Columns appearing in where, group by, and ORDER BY clauses
• 2. Optionally high columns are placed before the index (the Condition column order does not require the same order as the index column)
• 3, index column data not too long, (such as text for MD5 processing)
  Note: 1, the index is not the more the better (too many indexes also reduce the efficiency of reading: Multiple index selection process)

2. Periodic maintenance of index fragmentation
3, (MySQL) do not use the force index keyword in sql

3. Maintain (modify) Table structure

Precautions
1, MySQL5.5 will be locked before the table, the use of third-party tools; 5.6 after itself support online table structure changes
2. Maintain data dictionary at the same time
3, control the width and size of the table

The right operation

1, batch operations (in the database) by operation (in the application)
2. Use "SELECT *" as few queries as possible
3, control the use of user-defined functions (using functions, the index does not work)
4, do not use full-text index (Chinese support is not good, need to build index file)

4. Horizontal split and vertical split of data table

Vertical split: To control the width of the table

Horizontal split: To control the amount of data in a table

Indicates two-dimensional is a plane, the above situation is very easy to think about, the crux of the problem is to rely on a certain principle!
The goal is constant: for efficiency, for maintainability, for faster and easier!

SQL query Statement optimization

explain analyze the SQL execution plan and find out where SQL needs to be optimized

Explain select Customer_id,first_name,last_name from customer;
+--+ ————-+ ———-+--+ ————— +--+ ——— +--+--+ ——-+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+--+ ————-+ ———-+--+ ————— +--+ ——— +--+--+ ——-+
| 1 | Simple | Customer | All | NULL | NULL | NULL | NULL | 599 | NULL |
+--+ ————-+ ———-+--+ ————— +--+ ——— +--+--+ ——-+

• Table: List name;
• Type: Types of connections, const, EQ_REG, ref, range, index, and All;const: Primary key, Index, Eq_reg: Primary key, index range lookup, ref: Connection lookup (join),
• Range: The scope of the index to find; index: the scan of indexes;
• Possible_keys: The index that may be used;
• Key: the actual index used;
• Key_len: The length of the index, the shorter the better;
• Ref: Which column of the index is used and the constant is good;
• Rows:mysql the number of rows that must be checked to return the requested data;
• Extra:using Filesort, using temporary (often appearing when using order by) need to be optimized.

Optimizations for Max () and COUNT ()

1. For the max () query, you can create an index for the table, create index index_name on table_name(column_name 规定需要索引的列) which is indexed by the date of payment, and then in the query.

If there is no index, the query may continue to the last row.

2.count () Queries for multiple keywords, such as detecting the number of movies in 2006 and 2007 simultaneously in a single SQL statement:

select count(release_year=‘2006‘ or null) as ‘2006年电影数量‘,count(release_year=‘2007‘ or null) as ‘2007年电影数量‘from film;

COUNT (*) contains null for this column, and the notation for count (ID) does not contain a null column.

3. Optimization of sub-queries

The handle query changes to the left connection query, but if two tables exist in one-to-many cases, the left JOIN query results will appear, so use distinct to remove duplicate records

select * from table1 where table1.column1 in (select table2.column2 from table2);select distinct table1.column1 from table1 join table2 on table1.column1=table2.column2;

4.order by Statement optimization
The group by may have temporary tables (using temporary), file ordering (using Filesort), etc., affecting efficiency.
You can save IO by correlating subqueries to avoid temporary table and file sorting
Before rewriting

select actor.first_name,actor.last_name,count(*)from sakila.film_actorinner join sakila.actor using(actor_id)group by film_actor.actor_id;

After rewriting

select actor.first_name,actor.last_name,c.cntfrom sakila.actor inner join(select actor_id,count(*) as cnt from sakila.film_actor group byactor_id)as c using(actor_id);

5.limit Statement Optimization

Limit is often used for paging, often accompanied by an ORDER BY clause, so most of the time using filesorts will cause a lot of IO problems

1. Use an indexed column or primary key for an order by operation

2. Record the last returned primary key and use primary key filtering at the next query
There is a restriction that the primary key must be sequential and sequential, and if the primary key appears to be empty, it may result in less than 5 listings on the final page, and the workaround is to append a column to ensure that the column is self-incremented and the index is increased.

6. Select the appropriate index column

1. Columns appearing in the Where,group by,order by,on clause

2. The smaller the index field, the better (because the storage unit of the database is the page, the more data you can save in a page, the better)

3. High dispersion is placed in front of the federated Index

select count(distinct customer_id), count(distinct staff_id) from payment;

View the degree of dispersion by counting the different column values to achieve a higher count of discretization

Too many indexes affect not only writing but also query, the more indexes, the slower the analysis
How to find duplicate and redundant indexes, the primary key is already indexed, so Primay Key's primary key does not have to set a unique unique index anymore

Redundant index refers to the same prefix column for multiple indexes, and InnoDB automatically adds primary key information after each index


Redundant index query tool
Pt-duplicate-key-checker

Because of business changes some of the original used indexes are not used now also need to clear, this is an aspect of index optimization! Some indexes are used in a very low frequency, even if they are useless.
Note: The author again emphasizes that SQL and index optimization is very important to the optimization of the database, if the optimization of this layer is done, other optimizations can play a role otherwise the optimization can play a negligible role, this layer of optimization is the lowest cost effect of the best layer, So the best place to focus on database optimization is on this layer.

1. optimization of configuration files;

#重要，缓冲池的大小 推荐总内存量的75%，越大越好。innodb_buffer_pool_size#默认只有一个缓冲池，如果一个缓冲池中并发量过大，容易阻塞，此时可以分为多个缓冲池；innodb_buffer_pool_instances#log缓冲的大小，一般最常1s就会刷新一次，故不用太大；innodb_log_buffer_size#重要，对io效率影响较大。0:1s刷新一次到磁盘；1：每次提交都会刷新到磁盘；2：每次提交刷新到缓冲区，1s刷新到磁盘；默认为1。innodb_flush_log_at_trx_commit#读写的io进程数量，默认为4innodb_read_io_threadsinnodb_write_io_threads#重要，控制每个表使用独立的表空间，默认为OFF，即所有表建立在一个共享的表空间中。innodb_file_per_table#mysql在什么情况下会刷新表的统计信息，一般为OFF。innodb_stats_on_metadata

MySQL database storage engine and database optimization