Innodb Index concept

The innodb index concept summarizes the index concepts of innodb. For more information, see innodb index classification: clustered index (clustered index) 1. Create a clustered index based on the primary key when a primary key is available. 2) if there is no primary key, a unique and not empty index column will be used as the primary key to become the clustered index of this table. 3) if neither of the above conditions is met, innodb creates a virtual secondary index (secondary index). Non-clustered indexes are secondary indexes, myisam indexes, such as composite indexes, prefix indexes, and unique indexes: Because myisam indexes and data are stored separately, myisam caches indexes to the memory through key_buffer, when you need to access data (access data through indexes), you can directly search for indexes in the memory and then find the corresponding data on the disk through indexes. That is why the index does not hit the key buffer, the reason for the slow speed: innodb Index: the innodb data and index are put together, and when the index is found, the data adaptive hash index is found: innodb will monitor Index usage on a table. If we observe that the creation of a hash index can increase the speed, we will create a hash index, which is self-adapted to the hash index constructed through the B + tree of the buffer pool, as a result, the creation speed is very fast. You do not need to create a hash index for the entire table. the InnoDB Storage engine automatically creates a hash index for some pages based on the Access frequency and mode. The adaptive hash index does not need to store disks. When the database is stopped, the content will be lost, and the database will be created by itself and the index will be maintained slowly. Clustered index: MySQL InnoDB will certainly create a clustered index to store the actual data rows and related key values in one partition. This also determines that a table can have only one clustered index, that is, MySQL does not store data rows in two places at a time. 1) InnoDB generally performs clustering based on the primary key value (primary key). 2. If no primary key is created, a unique and non-null index column is used as the primary key, become the clustered index of this table. 3) If none of the above two conditions is met, InnoDB will create a virtual clustered index. Advantages: the advantage of clustered index is to improve data access performance. Clustered indexes store both indexes and data in the same B + tree data structure, and store the index columns and related data rows together. This means that when you access different rows of records on the same data page, the page has been loaded into the Buffer. When you access the page again, the access will be completed in the memory without accessing the disk. Different from the MyISAM engine, the index and data are not stored in one block and stored in different physical files. The index file is cached in key_buffer, and the index corresponds to the disk location, you have to access disk data through the disk location. Disadvantages: 1) It is very expensive to maintain indexes, especially when a new row is inserted or the primary key is updated to the page split. We recommend that you use optimize table to OPTIMIZE the TABLE when a large number of new rows are inserted, because the row data that must be moved may cause fragmentation. The use of exclusive tablespace can weaken fragments. 2) The table uses UUId as the primary key to make data storage sparse, which may cause the cluster index to be slower than the full table scan, therefore, we recommend that you use auto_increment of int as the primary key. 3) if the primary key is large, the secondary index will be larger, because the leaf of the secondary index stores the primary key value; if it is too long, as a result, non-leaf nodes occupy more physical space. Secondary indexes created on the clustered indexes are called secondary indexes, and secondary indexes always need to be searched for data. Secondary index leaf nodes store primary key values instead of physical locations of rows. The secondary index first finds the primary key value, then finds the data leaf of the Data row through the primary key value, and then finds the data row through the Page Directory in the Data leaf. A composite index is an index created by multiple columns. A composite index uses alter table 'test' only when the index's leading column must appear in the where clause '. 'users' add index 'idx _ users_id_name '('name' (10) ASC, 'id' ASC); prefix INDEX when the INDEX string column is large, the created index will become very large. To reduce the index volume and increase the index scanning speed, we will use the first part of the index's string index, which will greatly reduce the space occupied by the index, in addition, the index selection will not be much lower. In addition, if you want to index BLOB and TEXT columns or VARCHAR columns that are very long, you must use a prefix index because MySQL does not allow indexing all their lengths. Usage: it is important to select the length of the column prefix, which also saves the index space and ensures that the prefix index selectivity is close to the full length of the index. The unique index of a unique index is easy to understand, that is, the index value must be unique. This index is optional because the best primary key index is the unique index, however, the primary key index is created when the table is created, and the unique index can be created at any time. It indicates the difference between the primary key and the unique index. 1) The primary key is the primary key constraint + the unique index. 2) The primary key must contain a unique index, but the unique index is not the primary key. 3) the unique index column allows null values, however, the primary key column does not allow null values. 4) A table can only have one primary key, but multiple unique index scanning methods are available: compact index scanning (dense index): At the beginning, to locate the data, you need to perform a right table scan. To increase the scanning speed, you need to separately store the index key value in a separate data block, in addition, each key value has a pointer to the original data block. Because the index is relatively small, the index scanning speed is faster than scanning the entire table, this method of scanning all the key values is called compact index scanning loose index scanning (sparse index): to improve the efficiency of compact index scanning, by sorting indexes and searching algorithms (B + trre), we can determine the location or direction of the next data block by matching the key value of the first row of each data block, therefore, valid data is the first row of data in each data block. If you create an index for the first row of data in each data block, perform a half-lookup on the newly created index, data location will be faster. This index scan method is called loose index scan. Covering index: an index that contains all the data that meets the query needs is called a overwriting index. That is, the index is used to return fields in the select list, instead of having to re-read the data file index based on the index. Common commands: 1) CREATE the primary key 'Primary key _ tab2' ('id' int (11) not null AUTO_INCREMENT, 'a1' varchar (45) default null, primary key ('id') ENGINE = InnoDB default charset = utf8; 2) create unique index indexname on tablename (columnname); alter table tablename add unique index indexname (columnname); 3) create a single column general index create index indexname on tablename (columnname ); alter table tablename add index indexname (columnname); 4) create a single column prefix index create index indexname on tablename (columnname (10 )); // create a prefix index for the first 10 characters in a single column alter table tablename add index indexname (columnname (10); // create a prefix index for the first 10 characters in a single column 5) create index indexname on tablename (columnname1, columnname2); // create index indexname on tablename (columnname1, columnname2 (10 )); // alter table tablename add index indexname (columnname1, columnname2) of a composite index containing a prefix for multiple columns; // alter table tablename add index indexname (columnname1, columnname (10); // composite index with prefix for multiple columns 6) Delete index drop index indexname on tablename; alter table tablename drop index indexname; 7) view the index show index from tablename; show create table pk_tab2; -------- end --------