MySQL:索引

SQL语法

创建索引

drop table if exists lck_tst;
create table lck_tst(
id int not null,
name varchar(200) not null,
number varchar(20) not null, 
type1 char(1),
type2 char(2),
content varchar(200),
remark varchar(200)
) ENGINE=MyISAM;
alter table lck_tst add primary key (id);
alter table lck_tst add unique idx_number(number);
alter table lck_tst add index idx_name(name);
alter table lck_tst add index idx_type(type1, type2);
alter table lck_tst add fulltext(content);

insert into lck_tst(id, name, number, type1, type2, content, remark)
values
(1, 'lee', '0001', 'M', 'S', 'Stray birds of summer come to my window to sing and fly away.And yellow leaves
 of autumn, which have no songs, flutter and fall there with a sign.', '140Kg, 25Y/O, like music'),
(2, 'zhang', '0002', 'F', 'Q', 'O Troupe of little vagrants of the world, leave your footprints in my 
words.', '150Kg, 26Y/O, like sport'),
(3, '刘德华', '0003', 'M', 'S', 'The world puts off its mask of vastness to its lover.', '180Kg, 25Y/O, like
 music'),
(4, '侯宝林', '0004', 'M', 'Q', 'It is the tears of the earth that keep here smiles in bloom.', '14Kg, 
26Y/O, like sport'),
(5, '梅葆玖', '0005', 'M', 'W', 'The mighty desert is burning for the love of a bladeof grass who shakes her
 head and laughs and flies away.', 'wahaha a wahaha'),
(6, 'Riguz', 'Ab01', 'F', 'R', 'If you shed tears when you miss the sun, you also miss the stars.',
 'kasjdslkhah asjdhjksa xhjawhejqw xjhjask'),
(7, 'Linus', 'BB-2', 'F', 'R', 'The sands in your way beg for your song and your movement, dancing water. 
Will you carry the burden of their lameness?', '180Kg'),
(8, '列夫托尔斯泰', '30x9', 'E', 'S', '跳舞着的流水呀，在你途中的泥沙，要求你的歌声，你的流动呢。你肯挟瘸足
的泥沙而俱下么？', 'shdiuowa weqwehoxz xckahwejk asjdhkjas'),
(9, '马克思', '9845', 'M', 'E', '她的热切的脸，如夜雨似的，搅扰着我的梦魂', '018989yhahhhjashd'),
(10, '买买提 铁木耳', '9863', 'S', 'E', '忧思在我的心里平静下去，正如暮色降临在寂静的山林中。　　', '123sser');

全文索引

SELECT * FROM lck_tst WHERE MATCH(content) AGAINST('tears earth birds');

InnoDB 索引实现

索引类型

Clustered Indexes

Each InnoDB table has a special index called the clustered index that stores row data. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, it is important to understand how InnoDB uses the clustered index to optimize the common lookup and DML operations.

1. When you define a PRIMARY KEY on a table, InnoDB uses it as the clustered index.
2. If you do not define a PRIMARY KEY for a table, InnoDB uses the first UNIQUE index with all key columns defined as NOT NULL as the clustered index.
3. If a table has no PRIMARY KEY or suitable UNIQUE index, InnoDB generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column that contains row ID values. The rows are ordered by the row ID that InnoDB assigns. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in order of insertion.

Accessing a row through the clustered index is fast because the index search leads directly to the page that contains the row data. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record.

Secondary Indexes

Indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.