Part I: Basics: Index official introduction index is to Michael Kors Bedford Logo Medium Brown Satchels help MySQL efficient data structure to obtain the data. I understand the index corresponds to a book catalog, through the directory information to know where, do not page by page to find the information access needs. Unique index keyword index --------------------- emphasize unique is that the index value must be unique, keyword unique index create index: 1, create unique index index name on table name (column name); 2, alter table table name add unique index index name (column name); delete the index: 1, drop index index name on table; 2, alter table table name drop index index name; --- ------------------ primary key primary key is a unique indexes, primary keys require built table is specified, the general use auto_increatment columns, keywords are primary key primary key is created: creat table test2 (id int not null primary key auto_increment); --------------------- InnoDB does not support full-text indexing, Myisam support the performance is better, generally Michael Kors Bedford Gusset Medium Blue Crossbody Bags CHAR, VARCHAR or TEXT column created. Create table table name (id int not null primary anto_increment, titlevarchar (100), FULLTEXT (title)) type = myisam ---------- single index and multi-column index Index can be a single index can also be more column index (also known as composite indexes). Create out in the form of the above index is a separate index, now take a look at Michael Kors Bedford Logo Monogram Large Black Totes creating a multi-column index: create table test3 (id int not null primary key auto_increment, uname char (8) not null default '', password char (12) not null , INDEX (uname, password)) type = myisam; Note: INDEX (a, b, c) can be used as a or (a, b) the index to use, but, and b, c or (b, c) of the index This is an optimization method using the most left-prefix, and in the back there will be described in detail, you just know that there are two such concepts --------------------- clustered index An index, the logical order of the index key values determines the physical order of the table in the corresponding row. Clustered index determines the physical order of the data in the table. Mysql in myisam table is not clustered index, innodb there (the primary key is the clustered index), clustered index when innodb structure described below are described in detail. --------------------- See table indexed by the command: Show index from Michael Kors Clutches the table name, such as: mysql Michael Kors Bedford Logo Large Pink Crossbody Bags \u0026 gt; show index from test3; + ------- + ------------ + ---------- Michael Kors Bedford Logo Medium Brown Satchels + + ---------- -------------- --- + ----------- + ------------- + ---------- + -------- + ---- + | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | + ------- Michael Kors Crossbody Bags + ----------- - + ---------- + -------------- + ------------- + -------- --- Michael Kors Outlet + ------------- + ---------- + -------- + ---- + | test3 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | + ------- + ------------ + ---------- + - ------------- + ------------- + ----------- + ---------- --- + ---------- + -------- + ------ + Table: Table name Key_name: What type index (which the primary key) Column_name: index column The field name Cardinality: index bases, a critical parameter, Michael Kors Bedford Signature Large White Totes the average value group = Index base / table rows total, average value group closer to 1, the more Michael Kors Berkley Logo Large Beige Clutches likely use the index Index_type: If the index is a full-text index, it is fulltext, Here is b + tree index, b + tre is one of the key research in this article will not detail other, more: Part Michael Kors Bedford Signature Large White Totes II: MYISAM and INNODB index structure 1, brief B-tree B + tree tree B- tree structure view of an m-order B-tree tree, it has the following properties (1) Ki represents key value, the figure above, k1 \u0026 lt; k2 \u0026 lt; ... \u0026 lt; ki \u0026 lt; k0 \u0026 lt; Michael Kors Accessories Kn (it can be seen, a node left child node key value \u0026 lt; the key value \u0026 lt; right child key value) (2) Pi represents a pointer pointing to child nodes, the left child node pointer pointing to the left, and the right pointer points to Michael Kors Accessories the right child node. That is: p1 [point value] \u0026 lt; k1 \u0026 lt; (This is also the creation myisam and innodb tables must be the primary key) k2 ...... (3) All keywords must be unique values, each node contains; p2 [point value] \u0026 lt a description of how many keywords, as shown above the second row i and n (4) of that node: l m each node can have child nodes most. l the root if not a leaf node, at least two sub-node, sub-node l m up to each non-root, non-leaf nodes of at least [m / 2] sub-sub-tree node or call ([] represents rounding up), up to m child nodes (5) Keyword: Keyword l root number 1 to number of key m-1l non-root non-leaf nodes [m / 2] -1 ~ m-1, as m = 3, then the class node Number Keyword: 2-1 1-2 (6), Michael Kors Bedford Signature Large White Totes and point to the relationship between the keyword number k number of sub-node pointer p: l k + 1 = p, pay attention to the specific needs of data storage, about to have a pointer is empty flag indicates that no B + tree structure diagram is as follows: B + tree is a tree B- variant, it is also a multiple search tree: subtree pointer with the same number of keyword l l non-leaf nodes for all the leaf nodes (arrow red dot sign) to add a chain pointer B- B + tree is the tree of variants, is also a multiple search tree: subtree pointer with the same number of keyword l l non-leaf nodes for all the leaf nodes adding a chain pointer (red dot logo arrow) 2, MyisAM index structure MyisAM B + tree index used to store data, MyisAM index pointer is the key address, which is stored data, as shown below: (1 ) structural explanation: TREES Figure 3, the primary key is Col2, Col value is diverted to the data stored in the physical address, which is a marked in red. l 1 label portion may be confused, not to say in front of the right pointer points to the keyword key 15 is greater than 15, and 15 Michael Kors Bedford Logo Medium Brown Satchels how the following keywords? So since B + tree leaf node contains all keywords and is in ascending order (unique primary key index, secondary index may not be unique), it is equal to the data value of the key in the right subtree l 2 label is the keyword corresponding data storage physical address, one of which is the following note and InnoDB index l 2 different places also marked one of said index and data MyiAM tables are separated, the index stored in the 'table .MYI' file, and the data stored in the ' Table name .MYD 'document, 2 physical address label is the' table .MYD 'physical address within the corresponding data file. (Index and data files InnoDB table together) l secondary index and primary key index is no big difference, the index value secondary indexes can be repeated (but InnoDB secondary index and primary key index has a very clear distinction, where the first to draw attention about) 3, Annode index structure (1) First there is a table, the contents and the structure of the primary key index follows two plans: Col1