1:like的前导模糊查询不能使用索引

2、union、in、or都能够命中索引，建议使用in

2.1. union能够命中索引，并且MySQL耗费的CPU最少。

select from doc where status=1union allselect from doc where status=2;

2.2 in能够命中索引，查询优化耗费的CPU比union all 多，但可以忽略不计，一般情况下建议使用

select * from doc where status in ( 1, 2);

2.3. or新版的 MySQL能够命中索引，查询优化耗费的CPU比 in多，不建议频繁用or。

select * from doc where status = 1 or status = 2

3、负向条件查询不能使用索引

·负向条件有:!=、<>、not in、not exists、not like 等。·例如下面SQL语句:
select from doc where status != 1 and status != 2;
·可以优化为in查询:
select from doc where status in (0,3,4);

4、联合索引最左前缀原则

·如果在(a,b,c)三个字段上建立联合索引，那么他会自动建立 al| (a,b) | (a,b,c)组索引。·登录业务需求,SQL语句如下:
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顺序要和联合索引一致。
·下面的 SQL语句也可以命中 (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不能用。

5、不能使用索引中范围条件右边的列(范围列可以用到索引 )，范围列之后列的索引全失效

·范围条件有:、>=、between等。
·索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。
假如有联合索引(empno、title、fromdate)，那么下面的SQL中emp_no可以用到索引，而title和from_date则使用不到索引。
select * from employees.titles where emp_no

6、不要在索引列上面做任何操作(计算、函数)，否则会导致索引失效而转向全表扫描·例如下面的 SQL语句，即使date 上建立了索引，也会全表扫描:

select from doc where YEAR(create_time)<= ‘2016’;
。可优化为值计算，如下:
select from doc where create_time <= ‘2016-01-01”;

7、强制类型转换会全表扫描

字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。·如果phone字段是 varchar类型，则下面的 SQL不能命中索引。
select from user where phone=138**
·可以优化为:
select from user where phone=’138**‘;

8、更新十分频繁、数据区分度不高的列不宜建立索引

·更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。
。“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。。一般区分度在80%以上的时候就可以建立索引，区分度可以使用count(distinct(列名))/count(*)来计算。

9、利用覆盖索引来进行查询操作，避免回表，减少select *的使用

·覆盖索引:查询的列和所建立的索引的列个数相同，字段相同。
被查询的列，数据能从索引中取得，而不用通过行定位符row-locator再到 row上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。
例如登录业务需求,SQL语句如下。
Select uid, login _time from user where login_name=? and passwd=?
可以建立(login_name, passwd, login_time)的联合索引，由于login_time 已经建立在索引中了，被查询的uid和login_time 就不用去row 上获取数据了，从而加速查询。

10、索引不会包含有NULL值的列

。只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时，尽量使用not 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、使用短索引(前缀索引)

    对列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果该列在前10个或20个字符内，既使得前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省磁盘I/O操作，减少索引文件的维护开销。可以使用count(distinct leftlndex(列名,索引长度)/count()来计算前缀索引的区分度·但缺点是不能用于ORDER BY和GROUP BY操作，也不能用于覆盖索引。
    。不过很多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

    14、利用延迟关联或者子查询优化超多分页场景

    ·MySQL并不是跳过 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 ;

    15、如果明确知道只有一条结果返回， limit 1能够提高效率

    . 比如如下SQL语句:
    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最好
    . consts:单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。e ref:使用普通的索引(Normal lndex)。
    . range:对索引进行范围检索。
    ·当type=index时，索引物理文件全扫，速度非常慢。

    19、业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引

    。不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外，即使在应用层做了非常完善的校验控制,只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

    20.创建索引时避免以下错误观念

    1．索引越多越好，认为需要一个查询就建一个索引。
    2．宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度。
    3.抵制惟一索引，认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。4．过早优化,在不了解系统的情况下就开始优化。