MySQL engine, indexing, and optimization (Ali)

First, the storage engine

Storage engine, data in MySQL is stored in files (or memory) in a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different capabilities and capabilities. By selecting different technologies, you can gain additional speed or functionality to improve the overall functionality of your application. The InnoDB storage engine is the default database for MySQL after version 5.5, the preferred engine for transactional databases, which supports acid transactions and supports row-level locking. There is also a common MyISAM storage engine, which has high insertion, query speed, but does not support transactions. Therefore, it is obvious: the insertion is infrequent, the query is very frequent, there is no transaction, with MyISAM, high reliability requirements, the table is updated frequently, the transaction is many, with InnoDB.

 //  #查看本机MySQL提供的什么存储引擎  // show ENGINES;  // //  #查看Mysql当前默认的存储引擎  // show variables like '%storage_engine% ';  // //  #查看当前表用什么存储引擎 (DDL last)  // show create TABLE Idc_work_order_main; 
 
//#修改当前表的存储引擎 
//alter TABLE idc_work_order_main ENGINE = ' MyISAM '  

MySQL is officially explaining to InnoDB that InnoDB provides MySQL with a transaction-safe storage engine with commit, rollback, and crash resiliency. InnoDB is designed for maximum performance when dealing with huge amounts of data, and its CPU efficiency can be unmatched by any other disk-based relational database engine. The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory.

　　If you use the InnoDB storage engine, we know that the main feature of the engine is transactional and row lock (row-level lock). By no means there will be no table lock, but in fact, there will be a lock table of the situation, it will be more serious, the following is mainly to discuss the problem. Viewing the MySQL document will find that although InnoDB uses row lock (row-level lock), a special table lock is used when working with tables with the AUTO increment field: Auto-inc. In short, InnoDB will keep a counter in memory to record the value of auto_increment, and when inserting data, a table lock is used to lock the counter until the insertion is complete. A single insertion problem is small, but if high concurrency is inserted, it can cause SQL blocking.

　　solution : 1. Do not use the Auto increment field to maintain primary key generation. It is important to choose the primary key generation strategy in this method, and to consider the simple and efficiency problems synthetically. Assuming that the UUID is used, though simple, it can cause the table's primary key to be inefficient (InnoDB's primary key is a special index, and the other index references the primary key). 2. Upgrade to the latest version 5.2.

//#MySQL5. Prior to version 1.22, this approach is characterized by "table-level locking" and poor concurrency//innodb_autoinc_lock_mode = 0 ("traditional" lock mode: all use of table lock)////#推荐使用 "Consecutive", which is relatively high in concurrency, is to ensure that the newly inserted auto_increment ID in the same INSERT statement is continuous//Innodb_autoinc_lock_mode = 1 ("Consecutive" lock mode)////#这种模式是来一个分配一个, instead of locking the table, only locks the * process * of the allocation ID, and the difference between Innodb_autoinc_lock_mode = 1 is that//#不会预分配多个, this method is the most concurrency. But in replication, when Binlog_format is statement-based,//# (SBR statement-based replication) There is a problem, because it is to assign one, so that when concurrent execution,//# "Bulk inserts" is assigned to other inserts at the same time, and the master-slave inconsistency occurs (different from the result of the library execution and the main library execution result) because Binlog only records the start Insert ID. //Innodb_autoinc_lock_mode = 2 ("Interleaved" lock mode: All in new mode, unsafe, not suitable for replication)

Second, the index

An index is a data structure that helps MySQL to get data efficiently. This paper introduces the index structure and index principle of MySQL, and then learns the optimization of indexes. MySQL's index structure includes: B-tree index, tree Index, hash index (hash), bitmap index (BITMAP), skip table.

MySQL engine, indexing, and optimization (Ali)