MySQL theory combined with the actual article (i)

In the last two days, the requirement is to use a fixed table for the temporary tables used in the refund and return statements.

When you build your own table, such as (just for example):

CREATE TABLE Tasks (task_id int UNSIGNED not NULL auto_increment, parent_id INT UNSIGNED not null DEFAULT 0, Task VARCHAR (+) Not NULL, test_id INT UNSIGNED not null DEFAULT 0, date_added TIMESTAMP not NULL, date_completed TIMESTAMP, PRIMARY Key (task_id), Key parent_id (parent_id), key test_id (test_id)) Engine=innodb;

Like parent_id and test_id, when querying in a table, multiple occurrences are in the where condition, so they are indexed.

In the process, I thought of a few questions: Why do we use key instead of index? What are the characteristics of temporary tables, which are stored in memory?

question 1. What is the difference between index and key in MySQL?

---this is a common problem in StackOverflow (http://stackoverflow.com/questions/1401572/ WHAT-ARE-DIFFERENCES-BETWEEN-INDEX-V-S-KEY-IN-MYSQL)---

Key is the physical structure of the database, at the model level, it contains two levels of meaning, one is the constraint (emphasis on constraints and standardize the structure of the database integrity), and the second is the index (auxiliary query). Includes primary key, unique key, foreign key, and more.

-PRIMARY Key

Two functions, one is the binding function (constraint), used to standardize a storage primary key and uniqueness, but also on this key set up an index;

Unique Key

Two functions, one is the constraint (constraint), the uniqueness of the specification data, but also on the key set up an index;

-FOREIGN Key

Two functions, one is the binding function (constraint), the canonical data referential integrity, but also on this key set up an index;

In summary:the key in MySQL is both constraint and index .

MySQL requires every Key also is indexed, that's an implementation detail specific to MySQL to improve performance.

index is the physical structure of the database, at the implementation level, it is only a secondary query , it is created in a separate table space (MySQL InnoDB table space) in a similar directory of the structure of the storage. Index to classify, divided into prefix index, full-text index, etc., so the index is just an index, it does not constrain the behavior of indexed fields (that is what key to do).

MySQL Common indexes are: Primary key index, unique index, normal index, full-text index, combined index.

question 2. What is the difference between a memory table and a temp table in MySQL?

Memory table:

1. Parameter control: max_heap_table_size=1024m. 2. When the error is reached on the line, sufficient memory is required at the same time. 3. The table definition is saved on disk, and the data and index are stored in memory. 4. Cannot include fields such as Text,blob. ------------------------------------------5. Multiple sessions, the name of the creation table cannot be the same as 6. After a session is created, it is also visible to the other session 7. In the data directory, only tmp_memory.frm, the table structure is placed on disk, and it is placed in memory 8. Data in the memory table is lost when MySQL restarts or shuts down, but the table structure still exists 9. You can create indexes, delete indexes, and support unique index 10. Does not affect the main preparation, the main library inserted data, the library can also find 11. Show tables See table

Use the memory table to be aware of:

Memory table requires its own delete data or drop table, which requires drop permission, which is more dangerous

The table structure of the memory table is saved on disk, if multiple sessions use the same table name, there is a conflict, if you do not need to use the table name, if you need to create a table structure, there will be a lot of small files exist, not conducive to the maintenance of the DB, DBA cleanup table is also risky;

Temp table:

1. Parameter control: tmp_table_size=1024m. 2. Create files on disk when you arrive on line. 3. Table definitions and data are in memory. 4. You can include fields such as text, blobs, and so on. ----------------------5. The name of the created table can be the same as 6. The table structure and data are placed in memory 7. The Session vanishing table structure and data all disappear by 8. You can create an index and delete index 9. The main library created the table, the repository is not found, 10. Show tables can't see the table

In addition: the temporary table and the memory table engine is different, the temporary table default is MyISAM, and the memory table is

I think the previous use of temporary table is mainly the following two features:

Temporary tables are session-level, and do not affect each other even if the table names are created by multiple sessions

the session disappears and all disappears, which is very detrimental to application troubleshooting.

No matter the memory table or the temporary table will need to consume additional memory space, although the db end can endure, but not very controllable;

The->db side also has this parameter: Max_tmp_tables the maximum number of temporary tables a customer can keep open at the same time, which defaults to 32, which can be adjusted as needed

Reference article: MySQL memory tables and temporary tables

Question 3. MySQL single-column index and federated index

A colleague small partner says that you can consider a federated index, depending on your needs or not.

"If you don't understand the index enough, a common mistake is to wear a separate multi-tone for each column, or create a multicolumn index in the wrong order. ”

When it comes to indexing strategies, many of the vague suggestions such as "index the columns in the where" are very wrong. The best thing about this approach is only the "one Star" index,

To achieve a truly better index, you need to optimize the order of indexed columns, or create a fully-covered index.

---Note: The evaluation of indexes is mentioned in the third edition of "High Performance MySQL": Samsung Evaluation (Three-star system)

The index places the related records together to obtain a single star;

If the order of the data in the index and the order in the lookup are consistent, two stars are obtained;

If the column in the index contains all the columns needed in the query, get Samsung;

In recent days to see "high-performance MySQL" Or harvest is very large, diligent reading or is very necessary, at the same time and practice together!

Follow up to add, if I understand the wrong place, I hope someone can raise!

MySQL theory combined with the actual article (i)