1、like语句的前导模糊查询不能使用索引
select * from doc where title like '%XX'; --不能使用索引 select * from doc where title like 'XX%'; --非前导模糊查询,可以使用索引
2、union、in、or 都能够命中索引,建议使用 in
1.union能够命中索引,并且MySQL 耗费的 CPU 最少。
select * from doc where status=1 union all select * from doc where status=2;
2.in能够命中索引,查询优化耗费的 CPU 比 union all 多,但可以忽略不计,一般情况下建议使用 in。
select * from doc where status in (1, 2);
3.or 新版的 MySQL 能够命中索引,查询优化耗费的 CPU 比 in多,不建议频繁用or。
select * from doc where status = 1 or status = 2
4.补充:有些地方说在where条件中使用or,索引会失效,造成全表扫描,这是个误区:
3、负向条件查询不能使用索引
select * from doc where status != 1 and status != 2;
select * from doc where status in (0,3,4);
4、联合索引最左前缀原则
select uid, login_time from user where login_name=? andpasswd=?
1.建立联合索引的时候,区分度最高的字段在最左边
2.存在非等号和等号混合判断条件时,在建立索引时,把等号条件的列前置。如 where a>? and b=?,那么即使a 的区分度更高,也必须把 b 放在索引的最前列。
3.最左前缀查询时,并不是指SQL语句的where顺序要和联合索引一致。
select uid, login_time from user where passwd=? andlogin_name=?
4.假如index(a,b,c), where a=3 and b like ‘abc%’ and c=4,a能用,b能用,c不能用。
5、不能使用索引中范围条件右边的列(范围列可以用到索引),范围列之后列的索引全失效
select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'
6、不要在索引列上面做任何操作(计算、函数),否则会导致索引失效而转向全表扫描
select * from doc where YEAR(create_time) <= '2016';
可优化为值计算,如下:
select * from doc where create_time <= '2016-01-01';
比如下面的 SQL 语句:
select * from order where date < = CURDATE();
可以优化为:
select * from order where date < = '2018-01-2412:00:00';
7、强制类型转换会全表扫描
select * from user where phone=13800001234
可以优化为:
select * from user where phone='13800001234';
8、更新十分频繁、数据区分度不高的列不宜建立索引
9、利用覆盖索引来进行查询操作,避免回表,减少select * 的使用
覆盖索引:查询的列和所建立的索引的列个数相同,字段相同。
被查询的列,数据能从索引中取得,而不用通过行定位符 row-locator 再到 row
上获取,即“被查询列要被所建的索引覆盖”,这能够加速查询速度。
例如登录业务需求,SQL语句如下。
Select uid, login_time from user where login_name=? and passwd=?
10、索引不会包含有NULL值的列
11、is null, is not null无法使用索引
12、如果有order by、group by的场景,请注意利用索引的有序性
1.order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort 的情况,影响查询性能。
例如对于语句 where a=? and b=? order by c,可以建立联合索引(a,b,c)。
2.如果索引中有范围查找,那么索引有序性无法利用,如WHERE a>10 ORDER BY b;,索引(a,b)无法排序。
13、使用短索引(前缀索引)
14、利用延迟关联或者子查询优化超多分页场景
selecta.* from 表1 a,(select id from 表1 where 条件 limit100000,20 ) b where a.id=b.id;
15、如果明确知道只有一条结果返回,limit 1 能够提高效率
select * from user where login_name=?;
select * from user where login_name=? limit 1
16、超过三个表最好不要 join
需要 join 的字段,数据类型必须一致,多表关联查询时,保证被关联的字段需要有索引。
例如:left join是由左边决定的,左边的数据一定都有,所以右边是我们的关键点,建立索引要建右边的。当然如果索引在左边,可以用right join。
17、单表索引建议控制在5个以内
18、SQL 性能优化 explain 中的 type:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好
19、业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引
20.创建索引时避免以下错误观念
Index Selectivity = Cardinality / #T
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles; +-------------+ | Selectivity | +-------------+ | 0.0000 | +-------------+
EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido'; +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 300024 | Using where | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees; +-------------+ | Selectivity | +-------------+ | 0.0042 | +-------------+ SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees; +-------------+ | Selectivity | +-------------+ | 0.9313 | +-------------+
SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees; +-------------+ | Selectivity | +-------------+ | 0.7879 | +-------------+
SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees; +-------------+ | Selectivity | +-------------+ | 0.9007 | +-------------+
ALTER TABLE employees.employees ADD INDEX `first_name_last_name4` (first_name, last_name(4));
SHOW PROFILES; +----------+------------+---------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------------------------------------------------------+ | 87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' | | 90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' | +----------+------------+---------------------------------------------------------------------------------+