CREATE TABLE `staffs`(
id int primary key auto_increment,
name varchar(24) not null default "" comment'姓名',
age int not null default 0 comment '年齡',
pos varchar(20) not null default "" comment'職位',
add_time timestamp not null default current_timestamp comment '入職時間'
)charset utf8 comment '員工記錄表';
insert into staffs(name,age,pos,add_time) values('z3',22,'manage',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());
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev'
當建立了索引列後，能在wherel條件中使用索引的盡量所用。
如果索引了多列，要遵守最左字首法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev'
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev'
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'
不在索引列上做任何操作（計算、函數、(自動or手動)類型轉換），會導緻索引失效而轉向全表掃描
EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' ;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age =22;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age =22 and pos='manager'
中間有範圍查詢會導緻後面的索引列全部失效
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age >22 and pos='manager'
盡量使用覆寫索引(隻通路索引的查詢(索引列和查詢列一緻))，減少select *
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = 'July' and age =22 and pos='manager'
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = 'July' and age >22 and pos='manager'
mysql 在使用不等于(!= 或者<>)的時候無法使用索引會導緻全表掃描
EXPLAIN SELECT * FROM staffs WHERE NAME != 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME <> 'July';
如果定要需要使用不等于,請用覆寫索引
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME != 'July';
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME <> 'July';
注意null/not null對索引的可能影響
EXPLAIN select * from staffs where name is null
EXPLAIN select * from staffs where name is not null
在字段為not null的情況下，使用is null 或 is not null 會導緻索引失效
解決方式：覆寫索引
EXPLAIN select name,age,pos from staffs where name is not null
EXPLAIN select * from staffs2 where name is null
EXPLAIN select * from staffs2 where name is not null
Is not null 的情況會導緻索引失效
like以通配符開頭('%abc...')mysql索引失效會變成全表掃描的操作
EXPLAIN select * from staffs where name ='july'
EXPLAIN select * from staffs where name like '%july%'
EXPLAIN select * from staffs where name like '%july'
EXPLAIN select * from staffs where name like 'july%'
EXPLAIN select name,age,pos from staffs where name like '%july%'
字元串不加單引号索引失效
EXPLAIN select * from staffs where name = 917
EXPLAIN select name,age,pos from staffs where name = 917
解決方式：請加引号
EXPLAIN
select * from staffs where name='July' or name = 'z3'
select * from staffs where name='July'
UNION
select * from staffs where name = 'z3'
select name,age from staffs where name='July' or name = 'z3'
答案：
記憶總結：
l 全職比對我最愛，最左字首要遵守；
l 帶頭大哥不能死，中間兄弟不能斷；
l 索引列上少計算，範圍之後全失效；
l LIKE百分寫最右，覆寫索引不寫*；
l 不等空值還有OR，索引影響要注意；
l VAR引号不可丢， SQL優化有訣竅。
l 送出前關閉自動送出
l 盡量使用批量insert語句
l 可以使用MyISAM存儲引擎
LOAD DATA INFLIE；
使用LOAD DATA INFLIE ,比一般的insert語句快20倍
select * into OUTFILE 'D:\\product.txt' from product_info
load data INFILE 'D:\\product.txt' into table product_info
load data INFILE '/soft/product3.txt' into table product_info
檢視目前資料庫是否支援load data方式導入導出：
show VARIABLES like 'secure_file_priv'
解釋：
l secure_file_priv 為 NULL 時，表示限制mysqld不允許導入或導出。
l secure_file_priv 為 /tmp 時，表示限制mysqld隻能在/tmp目錄中執行導入導出，其他目錄不能執行。
l secure_file_priv 沒有值時，表示不限制mysqld在任意目錄的導入導出。
配置目前資料庫支援load data方式導入導出：
在my.ini檔案中添加：secure_file_priv=''