MySQL Index Introduction

Today Qi number shares with you what is an index. In fact index: index is equivalent to the book directory

Index Introduction

In the official words,

An index is a decentralized storage structure created to speed up the retrieval of rows of data in a table. Indexes are built on tables and are made up of index pages other than data pages, and the rows in each index page contain logical pointers to speed up the retrieval of physical data.

A lot of crap, in fact, the index is: the index is equivalent to the specified column to sort, sort favor the query of the column, can greatly increase the query efficiency, and the index is actually stored in the file
And indexing also consumes system resources, so indexing reduces the efficiency of write operations.

Index Storage Path

Either the Wamp or the XAMPP path is typically
mysql/data/Database user Name

MySQL storage engine What is a storage engine

Data in MySQL is stored in files (or memory) in a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different capabilities and capabilities. By selecting different technologies, you can gain additional speed or functionality to improve the overall functionality of your application.

MySQL commonly used storage engines are generally innodb and MyISAM
You little white in the process of the interview, the interviewer often ask InnoDB and myisam difference, some small white one encounter will not be the problem on the forehead fever, feet start shaking, and answer the question is donkey head not horse mouth, very general.

This with the girlfriend and male votes say belly ache, male votes if only will say drink more hot water, early to rest such words estimated girlfriend want to kill male ticket heart all have, answer not to point on, drink more hot water really is a word omnipotent words, below QI number can together with everyone to resolve this problem.

InnoDB: Support transactions, foreign key security MyISAM not supported
InnoDB Clustered index: Table data and index in the same file

MyISAM: High speed, fast stability, high efficiency, easy to manage, relatively insecure
MyISAM: Table's data and index are in different files

InnoDB has two files
. FRM table Structure
. IDB table Data + table Index

MyISAM has three files
. FRM table Structure
. MYD table Data
. MYI Table Index

1. High efficiency
MyISAM indexes and data are separate, and the indexes are compressed, increasing the memory
The usage rate, can load more indexes.
The InnoDB is that the index and data are tightly bound, and no compression is used which can cause
InnoDB is larger than MyISAM size.
2. Fast speed
Most of the projects hosted on the platform are read-write-less projects, while MyISAM read performance
is a lot stronger than InnoDB.
3. Easy to manage
If you back up the datasheet, MyISAM is handy, just send them the frm that corresponds to that watch. myd,myi files, let them in the corresponding version of the database to start the line, and InnoDB need to export xxx.sql, because the light to other people's files, by the dictionary data file, the other side is not available.
4. If and MyISAM than insert write operation, InnoDB also can not reach MyISAM write performance, if is for index-based update operation, although MyISAM may be inferior innodb, but so high concurrency of write, from the library can chase is also a problem, It might as well be solved by a multi-instance sub-Library table architecture.
5. If using MyISAM, the merge engine can greatly speed up the development of the application, as long as they do some select count (*) operations on the merge table, which is ideal for business tables of a certain type (such as logs, survey statistics) of a large project with a total of about hundreds of millions of.
6. Of course InnoDB is not absolutely not used, the project with the business is used InnoDB. In addition, someone might say that you myisam not be able to write too much, but you can make up for it through architecture.

Junior programmers can be described in terms of high efficiency, fast, easy to manage these three points is similar, and then can say how the index works, back to the data, so that the interviewer feel at least a bit of ink.

Index type

1. MyISAM Index

B-tree (binary tree) commonly used

B-tree (Multi-Path search tree) is a common data structure. Using the B-TREE structure can significantly reduce the intermediate process that is experienced when locating records, thus speeding up the access speed. According to translation, B is generally regarded as the abbreviation of balance. This data structure is generally used to index the database, the overall efficiency is high.

Index the file in MyISAM. Myi store is actually the data address, the real coordinates, the database quickly find this coordinate and go to locate. Data in the MYD, the process professional noun is called the return line,
Repeat it again.
The database finds the location in the index where the specified data resides, and locates the data in memory on the disk.

Hash (hashed)

The data coordinates of the hash insert are ordered, and the data addresses that are stored are unordered

2. InnoDB Index
　　B-tree (binary tree) commonly used

index files in InnoDB. IDB files are stored in fact the data address and data, the database quickly find the coordinates and also found the desired data, do not need to go back to find the data in other files

Here Qi number here to emphasize, not MyISAM query speed must be faster than InnoDB, there is a word said, all things are amorphous, pull a bit far away ha, in fact, it is for different database environment, different storage engine execution efficiency is not the same, But in general speaking MyISAM query speed is greater than innodb;

This sentence you can think about, or go online to collect some information;

Categories of indexes

General index optimization query sorting grouping
Create an index
CREATE index in_name on T1 (name);
ALTER TABLE T1 add index in_name (name);
View Index
Show index from T1;
Delete Index
Drop index in_name on T1;
ALTER TABLE T1 DROP INDEX in_name;
Unique index Special Normal index cannot appear duplicate value
Create an index
Create unique index un_name on T1 (name);
ALTER TABLE T1 add unique index un_name (name);
View Index
Show index from T1;
Delete Index
Drop index un_name on T1;
ALTER TABLE T1 DROP INDEX un_name;
Primary key Index
Delete a self-increment type constraint
ALTER TABLE t1 modify ID int (4);
Delete primary key index
ALTER TABLE T1 drop PRIMARY key;

Add primary Key
ALTER TABLE T1 ADD primary key (ID);
Add constraint
ALTER TABLE t1 modify ID int (4) auto_increment;

Well, the summary of the index is here, after the QI number if there is a new sentiment will continue to update the article, some small partners have different views can also comment, a lot of suggestions ~ ~

At the end of the QI, we share an interview with a Super bull:

Title: There is a mathematical sequence called Fibonacci series: 0,1,1,2,3,5,8,13,21,34,55,89,144 in these series, the first number is 0, the second number is 1, and from the third number, each number happens to be the first two values and

Ask to write a function method, enter a value in the method, and finally output is less than this value before all the numbers;

Today, Qi shares a word in the workplace:

Find the problem must be solved in time, all the reasons in the eyes of the leadership is an excuse.

MySQL Index Introduction