Schema optimization and indexing-high-performance indexing strategy

Preface

Creating the right indexes and using them correctly is the key to getting good query performance. We've covered different kinds of indexes and explored their pros and cons. Now let's look at how to use powerful indexes in depth.

There are many efficient ways to select and use indexes, because there are many special cases of optimization and some specialized behaviors. Deciding when to use and evaluate the potential impact of your choices on performance is also a skill we will learn. The next department will teach you how to use indexes efficiently, but don't forget to benchmark them.

Quarantine Columns

If you do not isolate indexed columns in a query, MySQL generally does not use the index on this column unless you isolate the column in the query. The "Quarantine" column means that the column cannot make part of an expression or be placed inside a function.

For example, in this query, you cannot use the index on the actor_id.

Mysql> SELECT actor_id from Sakila.actor WHERE actor_id + 1 = 5;

It's easy to know that the actor_id+1=5 and actor_id=4 are the same, but MySQL can't handle this actor_id+1=5 equation. It depends on what you do. You should simplify your where condition. Therefore, you should separate the index columns at one end of the comparison symbol.

The following example is another common mistake we make.

Mysql> SELECT ... WHERE to_days (current_date)-To_days (Date_col) <= 10;

This query will look for all rows in the last 10 days, but this statement will not use the index because the To_days function is used. Here's the better way

Mysql> SELECT ... WHERE date_col >= date_sub (current_date, INTERVAL Day);

This query uses an index without problems, but you can improve performance in another way. Current_date references can cause caching to fail. You can replace it with a specific date to solve the problem.

Mysql> SELECT ... WHERE date_col >= date_sub (' 2008-01-17 ', INTERVAL Day);

In the future, we will talk about the query cache.

prefix index and index selectivity

Sometimes you might want to have columns with a very high number of indexed characters. This will make your index very large and slow. One strategy is to simulate a hash index. The previous part has already said. But sometimes this method is not very good. What will be done.

You can generally use some of the characters in the first part of the index to save space and get good performance. This allows you to use less space for your index, but this reduces selectivity. The selectivity of indexes (index selectivity) is a ratio of the number of index values and the number of rows in the table (#T). Range is 1/#T到1. The higher the selectivity of the index, the better, because MySQL will filter out more rows when it matches. Selectivity of a unique index 1, which is the best.

The index of a prefix for good performance, its selectivity is sufficient. If you index a BLOB and text column, or a very long varchar column, you must define the prefix index because MySQL does not allow you to index all their lengths.

The trick is to select a column prefix, and the length of the prefix has a good selectivity, but it may save more space. The indexing effect of the index prefix should be as close as possible to the overall length of the index.

To know the length of a good prefix, find the most common values and compare them to the most common prefixes. Look at the following examples

CREATE TABLE Sakila.city_demo (city VARCHAR is not NULL);
INSERT into Sakila.city_demo (city) SELECT City from Sakila.city;
--Repeat The Next statement five times:
INSERT into Sakila.city_demo (city) SELECT City from Sakila.city_demo;
--Now randomize the distribution (inefficiently but conveniently):
UPDATE Sakila.city_demo
SET City = (SELECT the city from Sakila.city Order by RAND () LIMIT 1);

Now we have a sample dataset. The results are not really distributed, and we use RAND (), so the results are variable, but there is no effect on this exercise. First we find the cities with the highest frequency. Clustered Index (Clustered Indexes)

The clustered index is not a separate index type. They are exactly one way to store the data. There are some details of changes between implementations, but the InnoDB clustered index actually stores the B-tree index and the rows together in the same structure.

When a table has a clustered index, its rows are actually stored in the leaf pages of the index (leaf pages). "Clustered" means that the adjacent key values of a row are stored very close to each other. You may only have one clustered index per table, because you cannot store these rows in two places at the same time.

Because the storage engine is responsible for implementing indexes, not all storage engines support clustered indexes. For now, SOLIDDB and InnoDB support. This section focuses on InnoDB, but it is at least partially true for those engines that have implemented or are about to implement a clustered index.

The following figure shows how the records are distributed on a clustered index. Note that the leaf page (leaf pages) contains the entire row, but the node's page contains only the indexed columns. In this example, the indexed column contains an integer value.

Some database servers allow you to choose which index can be clustered, but MySQL's current storage engine cannot do that. InnoDB a primary key to centralize data. That is to say, the index column of the above diagram is the primary key column.

If you do not define a primary key, InnoDB selects a unique, non-empty index to override the primary key index. Without such an index, INNODB will define a hidden primary key. InnoDB aggregates data only on a single page. The adjacent key values that the page accompanies may be away from each other.

A clustered primary key index is useful for performance, but can also cause serious performance problems. So you should think carefully about clustering, especially when you change a table's storage engine from InnoDB to other engines. Overlay Index (covering Indexes)

Indexes are a way to efficiently find rows, but MySQL can also use indexes to find data for a column, so it does not have to read the entire row. After all, index leaf nodes store the data they index; When you can get the data you want by reading the index, you don't need to read the rows. An index that contains (or overwrites) data that satisfies the query's results is called the Overlay Index (covering Indexex)

Overwriting an index is a very powerful tool and can greatly improve performance. Consider the benefits of reading only indexes:

The entities that are indexed tend to be smaller than the entire row size. If MySQL reads only the index, it means there is very little data to access. This is useful for caching work, so that the corresponding time is basically from replicated data. Also useful for IO limits, because indexes are smaller and easier to write into memory than data. (This is especially useful for MyISAM, which compresses the index so that the index becomes smaller).

Indexes are sorted based on index values, so access to IO limits is relatively less than the IO required from a random hard disk location. For some storage engines, such as MyISAM, you can even use the Optimize table to get all sorts of indexes. This enables a simple scope query to use Access to a fully contiguous index.

Most storage engine cache indexes are better than data. Some storage engines, such as MyISAM, cache indexes only. Because the operating system caches MyISAM data, accessing the data requires a system call. This can lead to very serious performance problems. Especially for caching, system invocation is the most important part of data access consumption.

The overlay index has some special utility with the InnoDB table. Because InnoDB is a clustered index. InnoDB secondary indexes hold the primary key of the row in their leaf nodes. Therefore, overrides of secondary indexes can avoid the lookup of another index on the primary key.

In these scenarios, it is much lower to satisfy a query consumption from the index than the query row.

Overwriting an index does not apply to any index type, and the index must store the value of the column. Hash, spatial, and Full-text indexes do not store values, so MySQL can only use B-tree. and the different storage Engine implementation Overlay Index is different. Not all storage engines support them. use an index scan to sort

MySQL has two methods for generating sequential results: Using file sorting or sequentially scanning indexes. You can use explain to see if the Type column is index to find out whether MySQL plans to scan the index.

Scanning the index itself is very fast because it simply needs to move from one index entity to the next. However, if MySQL does not overwrite the query with an index, it looks for each row found in the index. This is a random IO process, so reading data from an indexed order is much slower than a continuous-table scan.

MySQL can use the same index to sort and find rows. If possible, it would be a good thing to meet both tasks.

Sorting by index is useful when the order of the indexes is the same as the order by condition and all columns are in the same direction (ascending or descending). If a query has more than one table associated with it, you can use an index sort if the column must be the first table after all order by criteria. The order by condition also has the same limit as the query: it needs to form an index of the left-side prefix. In other cases MySQL uses the file sort.

In one case, the condition after order by is not the leftmost index prefix, and if the Where condition or a join condition gives the values of those missing indexes, the index can still be used to sort.

For example, the rental table has an index (rental_date, inventory_id, customer_id). 、

CREATE TABLE Rental (
...
PRIMARY KEY (rental_id),
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
...
);

MySQL uses the rental_date index to sort the following queries

Mysql> EXPLAIN SELECT rental_id, staff_id from Sakila.rental
-> WHERE rental_date = ' 2005-05-25 '
-> ORDER by inventory_id, CUSTOMER_IDG
1. Row ***************************
Type:ref
Possible_keys:rental_date
Key:rental_date
Rows:1
Extra:using where

This can be sorted using an index, even if the order by condition is not the leftmost index prefix. That's because we've specified the value of the first column index after the Where condition.

The following are some of the queries that can be sorted using indexes. The following one can use the index because the query already uses the first indexed column and specifies the second indexed column with order by. To look at, is a leftmost index prefix.

... WHERE rental_date = ' 2005-05-25 ' ORDER by inventory_id DESC;

The following statement is also possible, because the condition after the order is also the leftmost index prefix.

... WHERE rental_date > ' 2005-05-25 ' ORDER by rental_date, inventory_id;

The following are statements that cannot be sorted using the index:

This query uses different orientations for the sort, but the indexed column is ascending.

... WHERE rental_date = ' 2005-05-25 ' ORDER by inventory_id DESC, customer_id ASC;

The following statement, which is not an index, is the column after the order by.

... WHERE rental_date = ' 2005-05-25 ' ORDER by inventory_id, staff_id;

The following statement is not the leftmost index prefix.

... WHERE rental_date = ' 2005-05-25 ' ORDER by customer_id;

This statement uses a range query for the first field, so MySQL will not use the remaining indexes.

... WHERE rental_date > ' 2005-05-25 ' ORDER by inventory_id, customer_id;

There are many identical conditions for the inventory_id column. For this sort, and query a range.

... WHERE rental_date = ' 2005-05-25 ' and inventory_id in (1,2) order by customer_id;

The following statement, on the surface, can be sorted using an index, but in fact it is not, because the statement optimizer places the Film_actor table in the second position. Let's talk about it later.

Mysql> EXPLAIN SELECT actor_id, title from Sakila.film_actor
-> INNER JOIN sakila.film USING (film_id) Order by ACTOR_IDG
+------------+----------------------------------------------+
| Table | Extra |
+------------+----------------------------------------------+
| Film | Using index; Using temporary; Using Filesort |
| Film_actor | Using Index |
+------------+----------------------------------------------+

Use indexes to sort the most important thing is that a query has order by and limit. This will be said in detail later. Compressed index (Packed Indexes)

MyISAM uses prefix compression to reduce the size of the index, so that more indexes can be put into memory and in some cases performance can be greatly improved. The default is a compressed string, but you can specify it to compress the integer value.

The MyISAM compresses each index block by storing the first value of the block, and then stores each additional value in the block by recording the byte number of the same prefix, plus the actual data of the different suffixes. For example, if the first value is "perform" and the second value is "performance", the second value is stored as "7,ance". MyISAM also prefix compression of adjacent row pointers.

Compressed blocks use less space, but they slow down the main operation. Because the compression prefix for each value depends on its previous value, MyISAM cannot use a binary search in the block to find the desired item and must scan the entire block from scratch. Sequential scans may perform well, but reverse scans-such as order by desc--are not very good. To find a separate line operation in the middle of the block, you need to scan the average, about half a block.

Our benchmark tests show CPU limitations (Cpu-bound), and the compressed key in the MyISAM table slows index lookups because the scans require random lookups. The key to reverse scan compression is even slower. There is a trade-off between CPU resources, one of the memory resources, and hard disk resources.

The compressed index is one-tenth of the original, and if you have an IO limit (io-bound) workload, they can reduce the cost of the primary operation. redundant and duplicate indexes

MySQL can create multiple indexes on one column; this does not alert and prevent errors. MySQL must maintain each duplicate index separately, and it will refer to these indexes when the statement optimizer optimizes the statement. This can have an impact on performance.

Duplicate indexes are the same set of indexes that have the same type, in the same order. You should avoid creating them and if you find them to be deleted as soon as possible.

Sometimes you can create a duplicate index without knowing it. For example, the following code

CREATE TABLE Test (
ID INT not NULL PRIMARY KEY,
UNIQUE (ID),
INDEX (ID)
);

An inexperienced user might think of making a column a primary key, plus a unique constraint, and adding an index. In fact, MySQL implements a unique constraint and primary key index. So in fact, three indexes have been created on a single column. There is no need to do this unless you want to create different types of indexes on the same columns to satisfy different types of queries.

Redundant indexes are different from duplicate indexes. If there is an index on (a,b), the other index on (A) is redundant. Because it is the prefix of the first index. The index on (a,b) can also be used separately as an index on (A). However, the index on one (B,A) is not redundant, and the index on (B) is not redundant because it is not the prefix of the (a,b) index. Further, different index types are not redundant for B-tree indexes, regardless of the columns they cover.

When people add indexes, redundant indexes are often present. For example, some people have added an index (A,B) to replace the extension of an existing (A) index.

In most cases, you don't want to have redundant indexes, and to avoid them, you should extend existing indexes instead of adding a new one. There are also times when you may need to use a redundant index for performance reasons. The main reason for using redundant indexes is that when you extend an existing index, this redundant index will make it larger.

For example, if you have an index on an integral column and you extend it to this integer column and an index on a varchar column, it slows down. If your query overwrites the index, or if the table is MyISAM and needs a range scan (because MyISAM uses prefix compression), the slow situation can occur.

Consider the UserInfo table. As mentioned in the previous tutorial. This table has 1,000,000 lines and each state_id has 20,000 records. There is an index on the state_id. This is useful for the following queries. The Q1 statement is as follows:

Mysql> SELECT Count (*) from userinfo WHERE state_id=5;

A simple benchmark shows an execution rate of 115 per second (QPS). We are looking at the relevant query Q2.

Mysql> SELECT state_id, City, address from UserInfo WHERE state_id=5;

For this query, the result is less than 10QPS. The simple way to improve performance is to extend the index (state_id, city, address). Therefore, the index overwrites the query.

mysql> ALTER TABLE userinfo DROP KEY state_id,
-> ADD KEY state_id_2 (state_id, city, address);

After modifying the index, the Q2 is faster and the Q1 is slow. If we want two queries to be quick, we should leave both indexes, even if that individual index is redundant. The following table shows the performance of both indexes in MyISAM and InnoDB. Note that when using the State_id2 index, the InnoDB Q1 does not degrade much because InnoDB does not have a key compression.

state_id only State_id_2 only Both state_id and State_id_2
MyISAM, Q1 114.96 25.40 112.19
MyISAM, Q2 9.97 16.34 16.37
InnoDB, Q1 108.55 100.33 107.97
InnoDB, Q2 12.12 28.04 28.06

The disadvantage of using two indexes at the same time is a large maintenance consumption. The following data is inserted 1 million rows of performance.

state_id only Both state_id and State_id_2
InnoDB, enough memory for both indexes-seconds
MyISAM, enough memory for only one index, seconds 470 seconds

As you can see, the performance of inserting data is poor. In general, the following is true: Adding an index can have a significant impact on the performance of insert,update,delete operations, especially if the new index reaches memory limits. Indexes and Locks

In InnoDB, the role of the index is very important. Because they can allow statements to lock fewer rows. This is a matter to consider, because the lock is not released until a thing in MySQL5.0 InnoDB is ahead of schedule.

If the query statement does not retrieve rows that they do not need. They will lock in fewer rows. And for performance improvement, there are two reasons: first, even if the InnoDB row lock is very efficient and use less memory, but the row lock will also consume a certain amount of resources. Second, locking a lot of rows increases the lock's competition and reduces concurrency.

Locks are innodb only when the rows are accessed, and an index reduces the rows to be accessed by InnoDB, thereby reducing the lock. However, this applies only to the storage engine level, where InnoDB filters the rows that are not expected. If the index does not allow InnoDB to do so, the MySQL server applies the Where condition after InnoDB takes these values and returns to the service layer. In this case, it is too late to avoid locking the rows: InnoDB has locked them and the server is impossible to unlock.

To better understand our example, we still use the previous database Sakila

Mysql> SET autocommit=0;
Mysql> BEGIN;
Mysql> SELECT actor_id from Sakila.actor WHERE actor_id < 5
-> and actor_id <> 1 for UPDATE;
+----------+
| actor_id |
+----------+
| 2 |
| 3 |
| 4 |
+----------+

This query returns 2 to 4 rows, but in fact it has obtained an exclusive lock of 1 to 4 rows. InnoDB locked the first line because the statement is read as an index range:

Mysql> EXPLAIN SELECT actor_id from Sakila.actor
-> WHERE actor_id < 5 and actor_id <> 1 for UPDATE;
+----+-------------+-------+-------+---------+--------------------------+
| ID | Select_type | Table | Type | Key | Extra |
+----+-------------+-------+-------+---------+--------------------------+
| 1 | Simple | Actor | Range | PRIMARY | The Using where; Using Index |
+----+-------------+-------+-------+---------+--------------------------+

In other words, this low-level storage engine operation is "start from the index and get all the rows until Actor_id<5 is false." The server is not going to tell Innodb,where actor_id <>1 conditions. Look at the using where of the extra column. This means that after the storage engine returns rows, the MySQL server uses a where to filter.

The following statement proves that the first row has been locked, even if it does not appear in the first query result. Drop the first connection and start the second connection to execute the following statement.

Mysql> SET autocommit=0;
Mysql> BEGIN;
Mysql> SELECT actor_id from sakila.actor WHERE actor_id = 1 for UPDATE;

This query will be suspended. Waiting for the first thing to be the first line of the lock. This behavior is to ensure that the replication based on the statement level is correct. (In the Copy section.) ）

As this example shows, even if an index is used, InnoDB still locks it out of the line that is not really needed. This is even more problematic when you do not use an index to find and lock rows: If this statement is not indexed, MySQL scans the entire table and locks each row, regardless of the need.

Here are some little-known details of InnoDB, indexing and locking: InnoDB places shared locks on secondary indexes, but exclusive locks require access to primary keys. This reduces the likelihood of using a overwrite index and causes the select for UPDATE to be slower than the lock in SHARE MODE or the query without locks.

Learn an index example

Using examples to understand the concept of indexing is a simple way to do it. So we look at an example of an index.

If we were going to make an online dating site. The user's data will have many columns, such as country, state/region, city, sex, age, eye color, and so on. This site must support a combination of these attributes to query user data. It is also necessary to enable users to sort and limit results through the user's recent online time, other member ratings, and so on. How do we design indexes for such a complex requirement?

Oddly enough, the first thing to decide is whether we must use index sorting or whether a file sort is acceptable. Indexed sorting limits how indexes and statement creation are made. For example, the case where we cannot use an index is where the age of the condition is between 18-25, regardless of whether the statement uses an index to sort.

If MySQL uses an indexed range query, it cannot be sorted using another index. If this is a more common query, we know that a lot of queries are used to sort files.

Supports multiple types of filtering

Now we need to know the number of unique values for the column and the columns that often appear after the Where condition. It is highly selective to create indexes on columns with many unique values. Because MySQL will efficiently filter out unwanted values.

Country is not sure if it is selective, but it may appear in most query statements. The sex column is not selective, but it basically appears in each query statement. Based on the above ideas, we can use a prefix (sex,country) to create an index of a series of different combinations.

In traditional ways, adding indexes to a field with low selectivity has little effect. But why do we put a low selectivity column at the beginning of each index? The idea was wrong.

We have two reasons to use this. The first reason is that every query will use sex. We can even design to allow users to search through only one sex. Importantly, adding this column will not have any drawbacks, as we still have some tricks in it. maintenance of indexes and tables

When you've created a table, have the right data type, and add an index, your work isn't over yet: you also need to maintain your tables and indexes to make them work better. Table maintenance has three main goals: discovering and resolving table corruption, maintaining accurate index statistics, and reducing storage fragmentation.

Locate and repair a damaged table

The worst thing is that the table has been damaged. For MyISAM, most of them are caused by the machine. However, all storage engines will be corrupted by the index due to hardware problems or MySQL internal bugs or operating system causes.

Corrupted indexes can cause queries to return incorrect results, errors that throw duplicate key values when no duplicate values appear, or cause query deadlocks and machines. If you have a strange behavior-such as an unexpected mistake-check table to see if it is corrupted. Check table can generally check for damage to most tables and indexes.

You can use repair table to fix it. But not all engines support this command. This allows you to use ALTER commands, such as modifying the same storage engine as the table.

mysql> ALTER TABLE innodb_tbl engine=innodb;

You can also use offline repair tools for the storage engine. such as MYISAMCHK or delete data and reload. However, if the change occurs in the system, or if the "row data" in the table replaces the index, there is nothing you can do about it. In this case, you can only recover the table from the backup or recover the data from the corrupted file. Will say in detail later.

Update STATISTICS for indexes

The MySQL query optimizer uses two APIs to learn from the storage engine how the index is distributed when deciding how to use the index. The first one is the Records_in_range call. It passes into the endpoint range and returns the value of the record for the range. The second is info (), which returns different types of data, including the cardinality of the index (how much data is available for each key value).

When the storage engine does not provide the optimizer with accurate information about the number of query rows, the optimizer uses the statistical information of the index. This information you can use analyze table to estimate the number of downlink. The MySQL optimizer is cost-based, and the main consumption factor is how much data the query accesses. If this statistic is not generated, or if they expire, the optimizer may have a bad decision. The scenario is to use the Analyze table to generate the statistical data.

Paradigm and non-paradigm

There are many ways to present the given data. From the complete paradigm to the complete non-paradigm and between the two. In a database that conforms to the paradigm, each fact is presented once and only once. Conversely, in a database that is not normal, information is duplicated or stored in many places.

If you are unfamiliar with the paradigm, you should strengthen your study. On the paradigm, you can learn from some books and online resources. Here, we mainly introduce the knowledge that you should understand in this chapter. Let's take a look at the classic example, that is employee,departments, and department heads.

EMPLOYEE DEPARTMENT Head
Jones Accounting Jones.
Smith Engineering Smith
Brown Accounting Jones
Green Engineering Smith

The problem with this design is that when the data changes, the data model becomes dysfunctional. If Brown takes over the accounting department, we must update multiple statements to reflect this change, and these updates may also make the state of the data inconsistent. If Jones's head is different from Brown's head, there's no way to know if the head is right. As the old saying goes: A man has two watches, and he doesn't know the exact time. Further, it is not possible to show department when there are no employees. If we delete all the employees, the department information is deleted as well. To avoid this problem, we divide the table into two entities of employee and department. The result is two sheets:

Employee_Name DEPARTMENT
Jones Accounting
Smith Engineering
Brown Accounting
Green Engineering



DEPARTMENT Head
Accounting Jones
Engineering Smith

accelerated alter TABLE

The performance problem arises when ALTER TABLE is performed on a large table. MySQL Most of the changes are as follows: Create an empty table based on the new table structure, remove the data from the old table and insert it into the new table, delete the old table. This is a very long process. Many people have had to wait 1 hours or 1 days for painful experiences after alter table.

MySQL AB has started to improve performance in this area. Some of the upcoming features are support for "online" operations without locking the table. InnoDB developers are also actively trying to develop a sort to create an index. MyISAM has supported this feature, with the result that indexes are faster and the index layout is compressed.

Not all alter tables will rebuild the table. For example, you can change or delete column defaults (one fast, one slow) in two ways. If you want to change a film lease term for the original 3 days to the present 5 days. The method is as follows:

mysql> ALTER TABLE Sakila.film
-> MODIFY COLUMN rental_duration TINYINT (3) not NULL DEFAULT 5;

Using show status to monitor this statement, it did 1000 reads and 1000 inserts. In other words, copy a table into a new table. Even if the type of the column, whether the size, or not, is null has not changed.

In principle, MySQL can skip creating new tables. This default value is actually stored in the. frm file. So you can change it without the need to contact the table. MySQL does not do optimizations, however any modify column will cause the table to be rebuilt.

You can use ALTER column to modify:

mysql> ALTER TABLE Sakila.film
-> ALTER COLUMN rental_duration SET DEFAULT 5;

This statement modifies the. frm file without having to manipulate the table. The result is very fast.

Just modify the. frm file

We found that modifying the marked. frm file is very fast and when it can't do that, MySQL sometimes rebuilds the table. If you are willing to take on a part of the risk, you can tell MySQL to do some type of modification without rebuilding the table.

Simple records about the storage engine

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.

Row lock (Row-level locks)

Lock at the level of a row. A select that does not have an enlarged and non-blocking select-standard does not set any locks. This concurrency is better.

Multiple Versions (multiversioning)

InnoDB uses multiple versions of concurrency control, so your select will read robust data by default. In fact, the MVCC architecture adds a lot of complexity and possible surprises. You should read the InnoDB manual carefully.

Cluster by primary key (clustering by primary key)

All InnoDB tables are clustered by a primary key. This can be used for a higher level of data model design.

All indexes contain primary key columns (all indexes contain the primary key columns)

The cable refers to the primary key to refer to the row. If your primary key is very long, the index will grow particularly fast.

Cache optimization (optimized caching)

In the buffer pool, the INNODB caches data and memory. It also automatically creates a hash index to expedite the fetching of rows.

Non-compressed index (unpacked indexes)

Index does not have prefix compression. They are therefore larger than the index of the MyISAM table.

Slow data read (slow-load)

Mysql5.0,innodb does not optimize the operation of reading data. It creates an index for one row at a time. Instead of ordering them to create them. This causes the data to be read slowly.

Blocked auto_increment (Blocking auto_increment)

In earlier versions of MySQL5.1, InnoDB uses table-level locks to generate auto_increment values.

The value of Count (*) will not be cached

Unlike MyISAM tables or memory tables, InnoDB tables do not store the number of rows in the table. That is, COUNT (*) without a Where condition is not optimized and requires a scan of the entire table. The next chapter is about optimizing the count () statement.