***** ## 鍵表SQL ~~~ create table staffs( id int primary key auto_increment, name varchar(24) not null default "", age int not null default 0, pos varchar(20) not null default "", add_time timestamp not null default CURRENT_TIMESTAMP )charset utf8; ~~~ ~~~ create table user( id int not null auto_increment primary key, name varchar(20) default null, age int default null, email varchar(20) default null ) engine=innodb default charset=utf8; ~~~ 插入數據 ~~~ insert into staffs(`name`,`age`,`pos`,`add_time`) values('z3',22,'manager',now()); insert into staffs(`name`,`age`,`pos`,`add_time`) values('July',23,'dev',now()); insert into staffs(`name`,`age`,`pos`,`add_time`) values('2000',23,'dev',now()); ~~~ ~~~ insert into user(name,age,email) values('1aa1',21,'b@163.com'); insert into user(name,age,email) values('2aa2',22,'a@163.com'); insert into user(name,age,email) values('3aa3',23,'c@163.com'); insert into user(name,age,email) values('4aa4',25,'d@163.com'); ~~~ 建立復合索引 ~~~ create index idx_staffs_nameAgePos on staffs(name,age,pos); ~~~ ### 口訣 全值匹配我最愛,最左前綴要遵守 帶頭大哥不能死,中間兄弟不能斷 索引列上少計算,范圍之后全失效 like百分寫最右,覆蓋索引不寫星 不等空值還有or,索引失效要少用 varchar引號不可丟,SQL高級也不難 ### 練習 假設index(a,b,c) | where語句 | 索引是否被使用到 | | --- | --- | | where a = 3 | Y,使用到a | | where a = 3 and b = 5 | Y,使用到a,b | | where a = 3 and b = 5 and c = 4 | Y,使用到a,b,c | | where b = 3 或 where b = 3 and c = 4 或 where c = 4 | N | | where a = 3 and c = 5 | 使用到a,c沒有被使用,b中間斷了 | | where a = 3 and b > 4 and c = 5 | 使用到了a,b | | where a = 3 and b like 'kk%' and c = 4 | 使用到了a,b,c | | where a = 3 and b like '%kk' and c = 4 | 使用到了a | | where a = 3 and b like '%kk%' and c = 4 | 使用到了a |