MySQL structure and indexing principles

A few days ago the interview was asked the database index problem, did not answer. Back hurriedly check the next, check the time only to find some knowledge about the database has been almost forgotten, and then follow the words do not understand, but the more search found that they do not understand the more ...

First, the purpose of indexing is to improve the database query efficiency, however, this must pay some cost, one is that the index table itself needs to occupy a portion of space, and then the write operation is more expensive than no index, because to maintain the data structure of the index. In general, the implementation of the index is B-tree and + + tree, that is, for example, I set an index on a column of a table, the database system will automatically sort this column and then create a B + tree, each time the search follows the B + tree, instead of the first row of data to find the target row, which greatly reduces the search time

So why not use the same high-efficiency sorting tree or the upgraded version of the red-black tree, because this and the memory of the access principle is a bit of a relationship. In General, the index itself is large and cannot be stored in memory, so the index is often stored as an index file on the disk. In this way, the index lookup process will generate disk I/O consumption, relative to memory access, I/O access to the consumption of a few orders of magnitude, so the evaluation of a data structure as an index is the most important indicator of the number of disk I/O operations in the process of incremental complexity. In other words, the structural organization of the index minimizes the number of disk I/O accesses during the lookup process. One advantage of the B + tree is that each node can hold many groups of data, that is, the out-of-date , usually more than 100, so the tree height is very small, usually no more than 3, so the number of access to the disk less. Red black tree Words tree height is relatively larger, so access to disk relatively more points, of course, there are some other reasons, specifically in the http://blog.jobbole.com/24006/have said.

Then the first layer of MySQL implementation index is in MyISAM or InnoDB. What are these two things? And then it's time to get the picture:

(hereinafter quoted from http://www.cnblogs.com/yjf512/archive/2012/02/06/2339496.html)

This thing MySQLServer architecture, MySQL is composed of SQL interface, parser, optimizer, cache, storage

1 connectors refers to the interaction of SQL in different languages

2 Management serveices & Utilities: System Management and control tools

3 Connection Pool: Connection pooling.

Manage buffered user connections, threading, and other needs for caching

4 SQL Interface:sql interface.

Accepts the user's SQL command and returns the result that the user needs to query. For example, select from is called SQL Interface

5 Parser: Parser.

When the SQL command is passed to the parser, it is validated and parsed by the parser. The parser is implemented by Lex and YACC and is a very long script.

Main functions:

A. The SQL statement is decomposed into a data structure, and the structure is passed to the next step, after which the SQL statements are passed and processed based on this structure

B. If an error is encountered in the decomposition composition, then it is not reasonable to indicate that the SQL statement

6 Optimizer: Query optimizer.

SQL statements use the query optimizer to optimize queries before they are queried. He uses the Select-drop-join strategy for querying.

As an example, you can understand: Select Uid,name from user where gender = 1;

This select query is selected based on the where statement instead of querying all of the tables before gender filtering

This select query first attributes the property based on the UID and name, instead of removing the attributes from the filter

Join these two query criteria to generate the final query result.

7 Cache and Buffer: query caching.

If the query cache has hit query results, the query can go directly to the query cache to fetch the data.

This caching mechanism is made up of a series of small caches. such as table cache, record cache, key cache, permission cache, etc.

8 Engine: Storage engine.

The storage engine is the specific subsystem in MySQL that deals with files. It is also one of MySQL's most distinctive places.

The MySQL storage engine is plug-in. It customizes a file access mechanism based on an abstract interface of the file access layer provided by MySQL AB (the access mechanism is called the storage engine)

Now there are many kinds of storage engines, the advantages of each storage engine are different, the most common myisam,innodb,bdb

By default MySQL is using the MyISAM engine, it queries fast, has better index optimization and data compression technology. However, it does not support transactions.

InnoDB supports transactions and provides row-level locking, and the application is quite extensive.

MySQL also supports its own custom storage engine, and even the different tables in a library use different storage engines, which are allowed.

MySQL structure and indexing principles