select * from doc where title like '%XX'； --不能使用索引
select * from doc where title like 'XX%'； --非前导模糊查询，可以使用索引
union能够命中索引，并且MySQL 耗费的 CPU 最少。select * from doc where status=1
union all
select * from doc where status=2;
in能够命中索引，查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in。select * from doc where status in (1, 2);
or 新版的 MySQL 能够命中索引，查询优化耗费的 CPU 比 in多，不建议频繁用or。select * from doc where status = 1 or status = 2
where条件中使用or，索引会失效，造成全表扫描，这是个误区：where子句使用的所有字段，都必须建立索引；5.0以上，且查询优化器开启了index_merge_union=on, 也就是变量optimizer_switch里存在index_merge_union且为on。!=、<>、not in、not exists、not like 等。select * from doc where status != 1 and status != 2;
select * from doc where status in (0,3,4);
(a,b,c)三个字段上建立联合索引，那么他会自动建立 a| (a,b) | (a,b,c)组索引。select uid, login_time from user where login_name=? andpasswd=?
(login_name, passwd)的联合索引。因为业务上几乎没有passwd 的单条件查询需求，而有很多login_name 的单条件查询需求，所以可以建立(login_name, passwd)的联合索引，而不是(passwd, login_name)。
- 建立联合索引的时候，区分度最高的字段在最左边
- 存在非等号和等号混合判断条件时，在建立索引时，把等号条件的列前置。如
where a>? and b=?，那么即使a的区分度更高，也必须把b放在索引的最前列。
- 最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致。
(login_name, passwd) 这个联合索引：select uid, login_time from user where passwd=? andlogin_name=?
where 后的顺序和联合索引一致，养成好习惯。
- 假如
index(a,b,c),where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用。
<、<=、>、>=、between等。(empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 则使用不到索引。select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'
date 上建立了索引，也会全表扫描：select * from doc where YEAR(create_time) <= '2016';
select * from doc where create_time <= '2016-01-01';
select * from order where date < = CURDATE()；
select * from order where date < = '2018-01-2412:00:00';
phone 字段是 varchar 类型，则下面的 SQL 不能命中索引。select * from user where phone=13800001234
select * from user where phone='13800001234';
count(distinct(列名))/count(*) 来计算。Select uid, login_time from user where login_name=? and passwd=?
(login_name, passwd, login_time)的联合索引，由于 login_time 已经建立在索引中了，被查询的 uid 和 login_time 就不用去 row 上获取数据了，从而加速查询。NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时，尽量使用not null 约束以及默认值。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort 的情况，影响查询性能。where a=? and b=? order by c，可以建立联合索引(a,b,c)。WHERE a>10 ORDER BY b;，索引(a,b)无法排序。CHAR(255)的列，如果该列在前10个或20个字符内，可以做到既使得前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作，减少索引文件的维护开销。可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度。ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引。offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。id段，然后再关联:selecta.* from 表1 a,(select id from 表1 where 条件 limit100000,20 ) b where a.id=b.id；
select * from user where login_name=?;
select * from user where login_name=? limit 1
left join是由左边决定的，左边的数据一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用right join。consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。ref：使用普通的索引（Normal Index）。range：对索引进行范围检索。type=index 时，索引物理文件全扫，速度非常慢。insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。Index Selectivity = Cardinality / #T
(0, 1]``，选择性越高的索引价值越大，这是由B+Tree的性质决定的。例如，employees.titles表，如果title`字段经常被单独查询，是否需要建索引，我们看一下它的选择性：SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
| 0.0000 |
+-------------+
title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。employees.employees表为例介绍前缀索引的选择和使用。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 |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
<first_name>或<first_name, last_name>，看下两个索引的选择性：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 |
+-------------+
<first_name>显然选择性太低，``<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>`，看看其选择性：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 |
+-------------+
18，比<first_name, last_name>短了接近一半，我们把这个前缀索引建上：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' |
+----------+------------+---------------------------------------------------------------------------------+
ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。引自 码猿技术专栏 ，作者不才陈某