Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you restrictions on how to understand the MySQL InnoDB table. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

In < 3.23.50 version of InnoDB, you cannot use ALTER TABLE or CREATE INDEX to modify a table that already has or references a foreign key constraint. Use DROP TABLE and CREATE TABLE to replace it.

You cannot convert MySQL system tables, such as' user' or 'host', to type InnoDB. The system table must always be of type MyISAM.

Full-text search (fulltext search) is not supported in InnoDB tables.

MySQL performs replication (replication) in autocommit mode (autocommit mode). So the consistent reads in slave may look like you've partially handled the transaction, so this kind of read is not really consistent in slave. This limit no longer exists at 3.23.52.

InnoDB does not keep the total number of table records internally, which is a bit complicated to implement because of multiversioning. In response to a query SELECT COUNT (*) FROM T, InnoDB has to scan an index of the table, which takes some time if the table is not fully in the buffer pool. In order to get a faster count, you have to create your own count table and let your application update it as it is inserted and deleted. One way to eliminate bottlenecks caused by lock waiting is to create an overall set of counters. Applications can randomly select one at a time. To get the count, simply sum the counters: SELECT SUM (counter_column) FROM your_counter_table.

Anyone with an auto-increment column in the table must define a key for it, and the key must contain only this auto-increment column. InnoDB does not support the use of AUTO_INCREMENT=... in a CREATE TABLE statement. The purpose of this clause is to set the first value for an auto-increment column (the default is 1). Workspace (Workaround): inserts a specified value into the self-increment column as the first value. From then on, InnoDB will increase from this value.

SHOW TABLE STATUS cannot give precise statistics for the InnoDB table, except for the physical size retained by the table. The number of rows recorded can only be roughly estimated by an optimized SQL.

In replication in MySQL, load table from master still does not work in the InnoDB table. Set up a workaround in the main (master) server to convert the table to MyISAM, then load, and then change the table back to InnoDB in master.

If you index with the front part of a column:

CREATE TABLE T (A CHAR (20), B INT, INDEX T_IND (A (5) TYPE = InnoDB

InnoDB will inherently build an index on the entire column, not just the first part of the set.

The InnoDB table does not support INSERT DELAYED.

MySQL's LOCK TABLES operation cannot know that a SQL statement has finished locking rows on InnoDB: this means that even if another user's transaction has set a row lock on the same table, you will still lock the table. So your actions on this table conflict with the locking of other users and have to wait. Deadlocks are also possible. In any case, transactional integrity (transaction integrity) is not dangerous because row-level locks set by InnoDB usually take care of integrity (integrity). Similarly, a table-level lock can prevent other transactions from acquiring more row-level locks on the table (locking patterns are inconsistent).

The index cannot be set on BLOB or TEXT fields.

A table cannot have more than 1000 fields.

DELETE FROM TABLE does not rebuild the table except for deleting all record rows, deleting one after another, which is not that fast. TRUNCATE can be used in future versions of MySQL, which is fairly fast.

It no longer exists in the version = 3.23.44.

The default database page size for InnoDB is 16 kB. It can be set to 8 kB to 64 kB by recompiling the source code. You must update UNIV_PAGE_SIZE and UNIV_PAGE_SIZE_SHIFT in univ.i. In the version

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.