MySQL Development Basic Series 15 index design and use

I. Overview

All MySQL column types can be indexed and are the best way to improve the performance of select queries. Depending on the storage engine, you can define the maximum number of indexes and the maximum index length for each table, and each engine supports at least 16 indexes per table with a total index length of at least 256 bytes.
MyISAM and InnoDB engine tables are btree indexed by default, support prefix index, prefix index length is related to the storage engine, for the MyISAM engine, the length can be up to 1000 bytes long, for InnoDB length up to 767 bytes, Consider when specifying a prefix length for a column that uses multibyte character sets.
Support for full-text indexing (fulltext), only MyISAM engine support, limited to char,varchar,text columns. The default memory engine uses a hash index and also supports the Tbree index.

1. For example, to create a 10-byte prefix index for the city table, the syntax is:

-- Create a 10-byte prefix index for the CityName field of the city table CREATE INDEX  on the City (CityName (ten)); --   Index View SELECT*fromWHERE cityname=' ' ;

2. Deleting an index

DROP INDEX   on  City

Two. Principles for designing indexes:

1. The index uses the column after the where, rather than the column selected by select.
2. The larger the cardinality of the indexed column, the better the index effect.
3. Using a short index, if the string is indexed, you should specify a prefix length. If the majority value is unique within the first 10 or 20 characters, do not index the entire column. This will save the index space.
4. Do not overuse indexes. Reduced write performance due to disk space consumption.
5. The normal index of the InnoDB table holds the key value of the primary key, so the key value of the primary key selects the shorter type as much as possible.
6. With the leftmost prefix, when creating an N-column index, you actually create n indexes that MySQL can use, and a multicolumn index can serve several indexes because the leftmost Lie in the index can be used to match rows.

--Create a multi-column composite index of the city tableCREATE INDEXIx1 onCity (CityName (Ten), citycode);--go to the indexed statementEXPLAINSELECT *  fromCityWHERECityName="'  andCitycode="'; EXPLAINSELECT *  fromCityWHERECityName="' --do not go indexEXPLAINSELECT *  fromCityWHERECitycode="'

Three. Btree Index and Hash index

Memory engine can choose to use Btree or hash index, two different types of indexes each have their own different scope of application, hash index use need to note:
1. Use only for equality comparisons using the = or <=> operator.
2. The optimizer cannot use a hash index to speed up the order by operation.
3. MySQL cannot determine the approximate number of rows between two values. If you change a MyISAM table to a memory table with a hash index, it will affect the efficiency of the execution of some queries.
4. You can search only one line using the entire keyword.

Below is a demonstration of:

 --  add city_memory table on city table  create  table  city_memory select  *  from   city  --  alter  table  city_memory add  key   idx_fk_country_id (country_id);  --  add primary key  alter  table  city_memory add  primary  key  (city_id); 

-- City 's Btree Walk index SELECT *  from WHERE > 1  and < Ten

--City_memory The hash does not go indexEXPLAINSELECT *  fromCity_memoryWHEREcountry_id> 1  andcountry_id< TenEXPLAINSELECT *  fromCity_memoryWHEREcountry_id> 1EXPLAINSELECT *  fromCity_memoryWHEREcountry_id< Ten

-- City_memory's Hash Walk index SELECT *  from where country_id=tenSELECT*fromWHERE   in (ten,one);

Summary: Most MySQL indexes (such as primary key, unique index, index, fulltext index) are stored in btree, only the spatial column type index uses Rtree, and the memory table supports the hash index

MySQL Development Basic Series 15 index design and use