Basic theory of MySQL database optimization

• Insert buffer
• Double write
• Adaptive Hash index (AHI)
• Pre-reading (read ahead)

If there are no specific requirements, use the default Innodb.

MyISAM: Read-write and insert-oriented applications, such as blogging systems, news portals.

Innodb: Updates (deletes) frequently, or to ensure data integrity; High concurrency, support for transactions and foreign keys. For example, OA office automation system.

What is an index?

Indexes are special files (indexes on InnoDB tables are part of the table space) that contain Pointers to all the records in the table.

An index is a data structure. A database index is a sorted data structure in a database management system to help query and update data in a database table quickly. Indexes are usually implemented using B trees and their variant B+ trees.

More generally, an index is a table of contents. In order to facilitate the search of the contents of the book, through the content of the index to form a catalog. An index is a file that occupies physical space.

What are the advantages and disadvantages of indexes?

Advantages of indexes

• It can greatly speed up the retrieval of data, which is the main reason for creating indexes.
• By using indexes, you can improve the performance of the system by using optimization hiders during the query process.

Disadvantages of indexes

• Time: It takes time to create and maintain indexes. To be specific, indexes need to be maintained dynamically when data in a table is added, deleted, or modified, which reduces the efficiency of adding, changing, or deleting.
• Spatial: Indexes need to occupy physical space.

What are the types of indexes?

Primary key index: Data columns cannot duplicate or be NULL, and a table can have only one primary key.

Unique index: Data columns are not allowed to duplicate and NULL values are allowed. A table allows multiple columns to create unique indexes.

• Can be achieved byALTER TABLE table_name ADD UNIQUE (column);Create a unique index
• Can be achieved byALTER TABLE table_name ADD UNIQUE (column1,column2);Create a unique composite index

Plain index: A basic index type that has no restrictions on uniqueness and allows NULL values.

• Can be achieved byALTER TABLE table_name ADD INDEX index_name (column);Creating a normal index
• Can be achieved byALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);Create composite indexes

Full-text indexing is a key technology used by search engines at present.

• Can be achieved byALTER TABLE table_name ADD FULLTEXT (column);Creating a full-text index

Index data structure (B-tree, hash)

The data structure of index is related to the implementation of specific storage engine. Indexes used in MySQL include Hash index, B+ tree index, etc. The default index of InnoDB storage engine we often use is B+ tree index. For hash index, the underlying data structure is hash table, so in the vast majority of requirements for a single record query, you can choose hash index, query performance is the fastest; In most scenarios, you are advised to select the BTree index.

The fundamentals of indexing

Indexes are used to quickly find records that have specific values. If there is no index, the query will generally traverse the entire table.

The principle of indexing is simple: it turns unordered data into ordered queries

1. Sort the contents of columns that have been indexed
2. Generate an inversion list of sorted results
3. Spell the data address chain on the inverted list contents
4. In the query, first get inverted list content, and then take out the data address chain, so as to get specific data

Principles of index design?

1. The columns that are suitable for indexing are those that appear in the WHERE clause or are specified in the join clause
2. Classes with a small cardinality are poorly indexed and there is no need to index this column
3. Use short indexes. If you index long string columns, you should specify a prefix length to save a lot of index space
4. Don’t over-index. Indexes require additional disk space and reduce write performance. When table contents are modified, the index is updated or even reconstructed, and the more index columns, the longer this takes. So keep only the indexes you need to help the query.

Principles for index creation (top priority)

Indexing is good, but it is not unlimited. It is best to comply with the following principles

Mysql will keep matching to the right until it hits a range query (>, <, between, like). A = 1 and b = 2 and c > 3 and d = 4 a = 1 and b = 2 and c > 3 and d = 4 a = 1 and b = 2 and C > 3 and d = 4

2) create an index for a field that is frequently used as a query condition

3) Frequently updated fields are not suitable for creating indexes

4) If the column cannot effectively distinguish data, it is not suitable for index column (such as gender, male and female unknown, there are only three at most, the distinction is too low).

5) Expand indexes as much as possible, do not create new indexes. For example, if you want to add (a,b) to a table that already has an index of A, you only need to modify the original index.

6) foreign key columns must be indexed.

7) For those columns that are rarely involved in the query, do not create indexes for those columns with a high number of duplicate values.

8) Do not index columns of data types defined as text, image, and bit.

There are three ways to create an index: drop an index

The first method: CREATE an index when executing CREATE TABLE

Second: use the ALTER TABLE command to add an index

Third method: Run the CREATE INDEX command to CREATE the INDEX

CREATE INDEX index_name ON table_name (column_list);
Copy the code

Remove the index

Drop a normal, unique, or full-text index by index name: ALTER TABLE table name drop KEY index name

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;
Copy the code

What should I pay attention to when creating an index?

• Non-empty fields: Columns should be specified NOT NULL unless you want to store NULL. Columns with null values are difficult to query optimize in mysql because they complicate indexes, index statistics, and comparison operations. You should replace null values with 0, a special value, or an empty string;
• The columns of fields with large value dispersion (the difference between values of variables) are placed before the joint index. You can view the difference value of the field by using the count() function. The larger the returned value is, the more unique values of the field the higher the dispersion degree of the field is.
• The smaller the index field, the better: Database data is stored in pages. More data is stored on a page. More data is obtained in one I/O operation, the more efficient it is.

What are database transactions?

Transaction is an indivisible sequence of database operations and the basic unit of database concurrency control. The result of its execution must make the database change from one consistency state to another. A transaction is a logical set of operations that either all or none of them execute.

The most classic and often cited example of a transaction is the transfer of money.

If Xiao Ming wants to transfer 1000 yuan to Xiao Hong, the transfer will involve two key operations: reducing Xiao Ming’s balance by 1000 yuan and increasing Xiao Hong’s balance by 1000 yuan. If something goes wrong between these two operations like the banking system crashes, and Ming’s balance goes down and Red’s balance doesn’t go up, that’s not right. A transaction is a guarantee that both of these critical operations will either succeed or fail.

What are the four properties of ACID?

Relational databases must follow the ACID rule, which reads as follows:

1. Atomicity: Transactions are the smallest unit of execution and do not allow splitting. The atomicity of the transaction ensures that the action either completes completely or does not work at all;
2. Consistency: Data is consistent before and after a transaction is executed. Multiple transactions read the same data with the same result.
3. Isolation: when accessing the database concurrently, a user’s transaction is not disturbed by other transactions, and the database is independent between the concurrent transactions.
4. Persistence: After a transaction is committed. Its changes to the data in the database are persistent and should not be affected if the database fails.

What is dirty reading? Phantom read? Unrepeatable?

• Drity Read: a transaction has updated a copy of data, and another transaction has Read the same copy of data. For some reason, the first transaction has rolled back, and the data Read by the second transaction is incorrect.
• Non-repeatable read: Data inconsistency between two queries of a transaction. This may be because the original data updated by a transaction was inserted between the two queries.
• Phantom Read: a transaction where the number of pens is inconsistent between two queries. For example, if one transaction queries for rows and another inserts new columns, the previous transaction will find columns that it did not have before on subsequent queries.

In order to achieve the four characteristics of transaction, the database defines four different transaction isolation levels, which are Read uncommitted, Read committed, Repeatable Read, Serializable. The four levels solve the problems of dirty reads, unrepeatable reads, and phantom reads one by one.

MySQL InnoDB engine row lock how to implement?

A: InnoDB does row locking based on indexes

Select * from tab_with_index where id = 1 for update;

For UPDATE can perform row locks based on conditions, and ids are columns with index keys. If id is not an index key InnoDB will complete table locks and concurrency will not be possible

What are optimistic and pessimistic locks for databases? How do you do that?

The task of concurrency control in a database management system (DBMS) is to ensure that the isolation and unity of transactions and the unity of the database are not broken when multiple transactions simultaneously access the same data in the database. Optimistic concurrency control (optimistic locking) and pessimistic concurrency control (pessimistic locking) are the main techniques used in concurrency control.

Pessimistic locking: Shielding all operations that might violate data integrity, assuming concurrency conflicts. The transaction is locked after the data is queried until the transaction is committed. Implementation: use the locking mechanism in the database

Optimistic locking: Data integrity violations are checked only at commit time, assuming no concurrency conflicts will occur. The transaction is locked while the data is being modified, using version locking. Implementation: Music will generally use the version number mechanism or CAS algorithm implementation.

Two types of lock usage scenarios

From the introduction of the two kinds of lock, we know that the two kinds of lock have their own advantages and disadvantages, can not be considered better than the other kind, for example, optimistic lock is suitable for the situation of less write (multi-read scenario), that is, conflict is really rare, this can save the lock overhead, increase the overall throughput of the system.

However, in the case of overwrite, conflicts often arise, which can cause the upper application to be repeatedly retry, thus reducing performance. Pessimistic locking is suitable for overwrite scenarios.

Why use views? What is a view?

To improve the reusability of complex SQL statements and the security of table operations, the MySQL database management system provides the view feature. A view is essentially a virtual table that does not physically exist and contains a list of named columns and rows similar to a real table. However, views do not exist in the database as stored data values. The row and column data comes from the base table referenced by the query that defines the view and is generated dynamically when the view is specifically referenced.

Views improve the security of data in the database by allowing developers to focus only on specific data they are interested in and specific tasks they are responsible for, and only see the data defined in the view rather than the data in the tables referenced by the view.

What is a trigger? What are the use scenarios for triggers?

Triggers are special event-driven stored procedures defined by users on relational tables. A trigger is a piece of code that is automatically executed when an event is triggered.

Usage scenarios

• Changes can be cascaded through related tables in the database.
• Monitor changes to a field in a table in real time and need to be processed accordingly.
• For example, some service numbers can be generated.
• Do not abuse it; otherwise, database and application maintenance will be difficult.
• Keep the basics in mind, but understand the difference between the data type CHAR and VARCHAR, and the difference between the table storage engine InnoDB and MyISAM.

There are six types of triggers in MySQL database:

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

What kinds of SQL constraints are there?

• NOT NULL: The contents of the control field must NOT be NULL.
• UNIQUE: The content of the control field cannot be repeated. A table can have multiple UNIQUE constraints.
• PRIMARY KEY: Also used for control field contents cannot be repeated, but it is allowed only one in a table.
• FOREIGN KEY: Action used to prevent breaking connections between tables and to prevent illegal data from being inserted into a FOREIGN KEY column, since it must be one of the values in the table to which it points.
• CHECK: Used to control the value range of the field.