PHP face question sharing and answers

Since each of the previous issues is a relatively large knowledge point, the author hopes to be as far as possible one by one detailed answers, if there are insufficient places to welcome you to add and modify, while learning from the cow people write MySQL algorithm implementation and memory principle, btree structure.

1:mysql database storage engine has MyISAM and InnoDB, how are these two types of indexes stored?

Now ask the question has not asked MySQL has what storage engine, also do not ask MyISAM and InnoDB difference? Just start asking how does the index store it?

This also needs to start with why MySQL needs to build the index, the index can improve the efficiency of data retrieval, reduce the IO cost and the sorting cost of the database, add index to the field in the database, the biggest benefit is that the field as a search condition can greatly improve our retrieval efficiency, speed up the retrieval time, Reduce the amount of data that needs to be scanned during the retrieval process, but we cannot add indexes to the fields as soon as we encounter query.

Index is to set you as the index of the field a stored in the independent interval s, which only the contents of this field, in the search for this field and field a, only need to find in the interval s, rather than go to the database to find the matching criteria for the field, in the database to find a corresponding field of the physical address, And then read the corresponding data. If you do not add an index, you may find the entire database there will be a lot of extra field lookup, irrelevant fields, the database in the process of judging irrelevant fields wasted time and efficiency. Not as many indexes as possible, each of your indexes is built in a separate interval s, and the larger the independent interval, the more your resources will be occupied by the search.

For more information, please see: http://blog.sina.com.cn/s/blog_8efa3c4f0102wqgs.html

The difference between Ps:myisam and InnoDB:

First, INNODB support affairs, MyISAM not support, this is very important. Transactions are an advanced way of handling, such as in some column additions and deletions, as long as the error can be rolled back to restore, and MyISAM will not be.

Second, MyISAM suitable for query and insert-based applications, InnoDB suitable for frequent modification and related to high-security applications

Third, INNODB support foreign key, MyISAM not support

Four, MyISAM is the default engine, InnoDB need to specify

V. INNODB does not support indexes of fulltext types

Six, InnoDB does not save the number of rows in the table, such as SELECT COUNT (*) from table, InnoDB needs to scan through the entire table to calculate how many rows, but MyISAM simply read out the number of rows saved.

Note that when the COUNT (*) statement contains a where condition, MyISAM also needs to scan the entire table

Vii. for self-growing fields, InnoDB must contain only the index of the field, but you can establish a federated index with other fields in the MyISAM table

Eight, when emptying the entire table, InnoDB is a row of deletes, the efficiency is very slow. MyISAM will rebuild the table.

Ix. InnoDB supports row locks (or, in some cases, lock whole tables, such as Update table set a=1 where user like '%lee% '

2: What are indexes divided into? How do I create an index? How is the index optimized? A,b,c How can a federated index hit an index?

MySQL index is primary key index, unique index, normal index, full-text index and composite index, etc.

Create PRIMARY KEY index: ALTER TABLE user Add primary key (' A ');

Create a unique index: ALTER TABLE ' user ' Add unique (' A ');

Create a normal index: ALTER TABLE ' user ' Add index index_name (' A ');

Create full-text index: ALTER TABLE ' user ' Add fulltext (' A ');

Create a federated index: ALTER TABLE ' user ' Add index index_name (' A ', ' B ', ' C ');

Note that the index uses a left-of-the-box principle, that is, the first index to use must exclude the most options, for the ABC Federated Index, in the process of using as long as there is a and a row in the first place, that is, ab,ac,abc such an index used to hit the index, A is a federated index of the boot column.

Ps:

The primary key is not necessarily self-increasing, but the self-increment key must be the primary key.

View SQL using the explain command to view indexes and SQL efficiency

There are many things to index, and proper use of indexes requires developers to depend on their business.

Full-text indexes are fulltext indexed in MySQL and can only be created on columns of type Char,varchar or text.

Above the MySQL5 version, 20 in Varcahr (20) represents 20 characters, can store 20 characters, letters, numbers, etc., storing Chinese characters is not related to character encoding.

Varcahr, char (20) cannot be inserted into the database after more than the specified character.

SQL statement commands need to be memorized by heart.

PHP face question sharing and answers