查询时指定列
在select 是禁止使用select *from table
，尽量指定自己需要使用的列，下面测试一下指定列和不指定的执行时间
准备表和数据：
create table user_test.user_info
(
id bigint auto_increment primary key,
username varchar(11) null,
gender varchar(2) null,
password varchar(100) null)
engine = MyISAM
charset = utf8;
复制
创建存储过程，插入1千万条数据：
create procedure myproc() begindeclare num int;set num=1;while num <= 10000000 doinsert into user_info(username,gender,password) values(num,'保密',PASSWORD(num));set num=num+1;end while;
end;
复制
执行存储过程：
call myproc()；
复制
查看查询执行结果：
sql> select * from user_info
[2018-05-30 16:05:24] 1000000 rows retrieved starting from 1 in 1 m 9 s 103 ms (execution: 65 ms, fetching: 1 m 9 s 38 ms)
sql> select username from user_info
[2018-05-30 16:05:39] 1000000 rows retrieved starting from 1 in 11 s 862 ms (execution: 38 ms, fetching: 11 s 824 ms)
复制
结果显而易见。
创建索引
索引是可以对记录集合的字段进行排序的方法。在一张表中给一个字段创建一个索引，将创建另外一个数据结构，这个数据结构包含该字段的数值以及指向这一列的指针，然后创建完之后会对这个索引进行排序。如下图：
索引创建原则
性能分析
创建索引与不创建索引的查询比较：
在user_info
中创建字段username
为索引
复制
sql> select id,username,gender,password from user_info where username='9000000'[2018-05-31 11:34:06] 1 row retrieved starting from 1 in 3 s 182 ms (execution: 3 s 82 ms, fetching: 100 ms)
复制
耗时：3 s 182 ms
sql> select id,username,gender,password from user_info where username='9000000'[2018-05-31 11:37:31] 1 row retrieved starting from 1 in 66 ms (execution: 40 ms, fetching: 26 ms)
复制
耗时：66 ms
创建聚合索引原则
新建一个表test ，创建一个聚合索引 a,b,c,d
create table user_test.test
(
id int not null
primary key,
a int not null,
b int not null,
c int not null,
d int not null,
e int not null);create index test_a_b_c_d_index on user_test.test (a, b, c, d);
复制
分析下面的sql哪些使用索引的情况
select a,b,c,d from test where c=1 and a =1 and b=1 and d=1;
explain select a,b,c,d from test where c=1 and a =1 and b=1 and d=1;
复制
执行结果：
mysql> explain select a,b,c,d from test where c=1 and a =1 and b=1 and d=1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test type: ref
possible_keys: test_a_b_c_d_index
key: test_a_b_c_d_index
key_len: 16
ref: const,const,const,const
rows: 1
Extra: Using index1 row in set (0.00 sec)
复制
Extra: Using index
说明此处有命中索引；
此处的key_len 是16 ， 因为int是4个字节，此处命中的索引是a,b,c,d
,所以是16。此处的c=1 and a =1 and b=1 and d=1
等价于a =1 and b=1 and c=1 and d=1
。
select a,b,c,d from test where a =1 and b = 1 group by c;
mysql> explain select a,b,c,d from test where a =1 and b = 1 group by c \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test type: ref
possible_keys: test_a_b_c_d_index
key: test_a_b_c_d_index
key_len: 8
ref: const,const
rows: 1
Extra: Using where; Using index1 row in set (0.00 sec)
复制
Extra: Using where; Using index
： 表示有命中索引；
key_len: 8
:次数只有连个字段命中索引，即a,b
select a,b,c,d from test where a =1 and b = 1 and d= 1 group by c;
mysql> explain select a,b,c,d from test where a =1 and b = 1 and d= 1 group by c \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test type: ref
possible_keys: test_a_b_c_d_index
key: test_a_b_c_d_index
key_len: 8
ref: const,const
rows: 1
Extra: Using where; Using index1 row in set (0.00 sec)
复制
Extra: Using where; Using index
： 表示有命中索引；
key_len: 8
:只有两个字段命中索引，还是a,b，因为遇到group by
的时候，后面的索引就不生效了。
select a,b,c,d from test where c=1 and a =1 and b>1;
mysql> explain select a,b,c,d from test where c=1 and a =1 and b>1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test type: ref
possible_keys: test_a_b_c_d_index
key: test_a_b_c_d_index
key_len: 4
ref: const
rows: 1
Extra: Using where; Using index1 row in set (0.00 sec)
复制
Extra: Using where; Using index
:表示有命中索引；
key_len: 4
:只有一个字段命中索引，此处只有a命中索引，根据最左原则，会从最左开始匹配，a匹配完会找b，但是b是返回查找，不能使用索引，所以此时就只有a命中索引。
select a,b,c,d from test where c=1 and b =1 and d=1;
复制
总结：
最左前缀原则
最左前缀原则指的的是在sql where 字句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序出现、断层都无法利用到多列索引。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配.
Explain使用分析
参考：https://segmentfault.com/a/1190000008131735
如果觉得本文对你有帮助，请打赏以回报我的劳动
