SQL的索引失效以及优化

 1：索引失效：

• 全值匹配

• 最佳左前缀原则

                从索引的最左边开始找查询语句中出现的列，一直到未出现

• 计算、函数、类型转换(自动或手动)导致索引失效

                例1：

                        用LIKE进行模糊查询，索引生效，用left进行查询，索引失效

• 类型转换导致索引失效

                # 未使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE                 name=123;

•     范围条件右边的列索引失效         

•     不等于(!= 或者<>)索引失效

•     is null可以使用索引，is not null无法使用索引    

•     like以通配符%开头索引失效      

•      OR 前后存在非索引的列，索引失效 

2：索引优化

• 关联查询优化：

1. 左外连接

                LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有，所以 右边是我们的关键点,一定需要建立索引 。

1. join语句原理

join语句的执 行流程是这样的：

         1. 从表t1中读入一行数据 R；

        2. 从数据行R中，取出a字段到表t2里去查找；

         3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；

        4. 重复执行步骤1到3，直到表t1的末尾循环结束。

两个结论： 1. 使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；

                     2. 如果使用join语句的话，需要让小表做驱动表。

小结 ：

  保证被驱动表的JOIN字段已经创建了索引 需要JOIN 的字段，

数据类型保持绝对一致。

LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。 INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。

能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数) 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。

衍生表建不了索引

        排序优化（order by）

                        优化建议：

                                        1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫 描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排 序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

                                         2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列； 如果不同就使用联合索引。

                                               3. 无法使用 Index 时，需要对 FileSort 方式进行调优。

                        索引实效的案例：

                                        过程一：

                                                未添加索引

                                        过程二：

                                                Oder by时没有limit，因为数据量较大时还需要回表，索引优化器默认优化，如果不需要回表则不需要。

                                        过程三：order by 顺序错误

                                                  最左前缀原则

                                                   方向不一致（DESC,ASC）

•  优化分页查询

                          在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

                          该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询

• 覆盖索引

                ：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列 （即建索引的字段正好是覆盖查询条件中所涉及的字段）。 简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。  

• 索引下推