Mysql index -- (mysql 2) _ MySQL

Mysql index -- (mysql 2) bitsCN.com

Mysql index -- (mysql 2)

Index type:

Normal index

Primary key

Foreign key

Unique index

Non-unique

Professional index:

-- B-tree balanced number index, which is different from binary tree. the height of a binary tree may be very high. The Balance tree may not be very high.

B + tree B-tree is the logical name, and B + tree is the technical implementation. some of them are stored on disks when the memory is insufficient (innodb, MyISAM, Memery, etc)

R-tree spatial index (MyISAM)

Full text index. (MyISAM)

Hash index (Memery)

Purpose:

Reducing I/O will provide query speed and affect dml speed.

Optional: percentage of returned rows to the entire record

Index type: prefix index, composite index, function index flexibility (implemented by adding columns and triggers), full-text index

Composite index: oracle has an index skip algorithm to enable the index of a non-bootstrap column to be used. mysql must use composite index in the defined order.

Full-text index: mainly used to query words .... where match (column) aginst ('characters' in mode ). there are 3 medium-mode boolean (operator expressions supported), natural language, and extended natural language.

Select title from books where mathc (title) against ('Prince ')

Select title, author from books where match (title) against ('green + Anne 'in boolean mode); -- in natural language mode/with query expansion

-- View the execution plan

Explain select * from t where year (d)> 1994/G

Select_type: subquery (use subquery), dependent subquery (associate subquery), derived (use subquery as from, embedded view ),

Simple (simple query)

Union (union is used)

View the index of a table:

Show index from [tb_name]/G

BitsCN.com