mysql 索引 使用注意细节
在查询时,如果使用到LIKE关键字,就要注意有没有使用到索引;
没有使用索引前:
mysql> explain select * from employees where first_name=‘Georgi‘\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 294382
Extra: Using where
1 row in set (0.00 sec)添加索引: create index first_index on employees (first_name);
索引后:
mysql> explain select * from employees where first_name=‘Georgi‘\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ref
possible_keys: first_index
key: first_index
key_len: 16
ref: const
rows: 253
Extra: Using where
1 row in set (0.00 sec)查询中使用LIKE:
mysql> explain select * from employees where first_name like ‘%Georgi‘\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 294382
Extra: Using where
1 row in set (0.05 sec)mysql> explain select * from employees where first_name like ‘%Georgi%‘\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 294382
Extra: Using where
1 row in set (0.00 sec)mysql> explain select * from employees where first_name like ‘Georgi%‘\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: range
possible_keys: first_index
key: first_index
key_len: 16
ref: NULL
rows: 253
Extra: Using where
1 row in set (0.00 sec)
可以发现只有在查询关键字后面的加%才可以用到索引;
还有使用了is not null,<>都不可以用到索引:
mysql> explain select * from employees where first_name is not null\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: first_index
key: NULL
key_len: NULL
ref: NULL
rows: 294382
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from employees where first_name <> ‘Georgi‘\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: first_index
key: NULL
key_len: NULL
ref: NULL
rows: 294382
Extra: Using where
1 row in set (0.00 sec)
如果在索引的列上面用了函数或运算都不能引用到索引的:
mysql> explain select * from employees where UPPER(first_name)=‘Ult‘\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 294382
Extra: Using where
1 row in set (0.00 sec)
在使用复合索引的时候也要注意第一列有没有在WHERE中;
正确使用索引的:
mysql> explain select * from employees where first_name=‘Georgi‘ and last_name=‘
Facello‘\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ref
possible_keys: name_index
key: name_index
key_len: 34
ref: const,const
rows: 2
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from employees where first_name=‘Georgi‘\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ref
possible_keys: name_index
key: name_index
key_len: 16
ref: const
rows: 253
Extra: Using where
1 row in set (0.00 sec)
没有使用到索引的:
mysql> explain select * from employees where last_name=‘Facello‘\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 300252
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from employees where last_name=‘Facello‘ or first_name=‘
Georgi‘\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: name_index
key: NULL
key_len: NULL
ref: NULL
rows: 300252
Extra: Using where
1 row in set (0.00 sec)
第二个例子因为用了OR所以WHERE中也只是对first_name单独检索;
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。