Schema optimization and indexing

At the end of this chapter, let's take a look at the choice of the storage engine for the design model that you should keep in mind. We don't fully introduce the storage engine, and the goal is to list some of the key factors that affect the design of the data model.

MyISAM Storage Engine

Table Lock (Locks)

MyISAM is a table-level lock. The little heart is this will not become a bottleneck.

No automatic data recovery (no automated recovery)

If the MySQL server hangs or the power is off. You should fix the MyISAM table before using the table. If you have a large table, this process may last for several hours.

Do not support things (no transactions)

MyISAM does not support things. In fact, the MyISAM table does not guarantee that a single statement will execute. If an error occurs in more than one update, some rows are updated and the other rows are not updated.

Only indexes will be cached (only indexes are cached in memory)

Within the MySQL process, the MyISAM only caches the index in the critical buffer. The operating system caches the table's data, so in MySQL5.0, you need an operating system call to get the data. This process consumes a large part.

Compressed storage (Compact storage)

Each row is next to each other, so the required hard disk will be small and the full table scan will be quick.

Memory Storage Engine

Table Lock (Locks)

Like MyISAM tables, memory tables support table locks. This is not a problem, because statements are executed in memory. Very fast.

No active rows (no dynamic rows)

Memory tables do not support dynamic (for example, length of variable) rows, so they do not support blob,text fields. A varchar (5000) is converted to char (5000)-A large amount of memory is wasted if most of the data is small.

The hash index is the default index type (hash indexes are the "default index type")

Unlike other storage engines, its default index type is hash.

No indexed statistics (no index statistics)

The memory table does not support index statistics, so it may not be ideal for complex queries.

Content lost after reboot (content is lost on restart)

Memory does not persist any data. So after the server restarts, the data will be lost, but the table still exists.

InnoDB Storage Engine

Things (transactional)

InnoDB supports things and supports 4 levels of isolation.

FOREIGN key (Foreign keys)

Mysql5.0,innodb is the only storage engine that supports foreign keys. Other storage engines can create foreign keys while creating a table, but they are not constrained. Some third-party engines, such as SOLIDDB and PBXT, support them at the storage engine level. MySQL AB prepares to support foreign keys at the server level.