***** ## 键表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 |