MYSQL-四（查询优化）

一、索引分析

单表：查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 article_id。

不建索引：

type :ALL Extra:Using filesort

第一次建索引：create index idx_article_ccv on article(category_id,comments,views);

type:range Extra:Using filesort(优化)

 

原因：因comments > 1 条件是一个范围值(所谓 range)，MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。

第二次建索引：create index idx_article_cv on article(category_id,views);

 type:ref     Extra:Using where 

 两表：

SQL：EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

不建索引：

type:ALL,ALL

 建索引：ALTER TABLE `book` ADD INDEX Y ( `card`); （右表建索引）

type:ALL，ref

建索引：ALTER TABLE `` ADD INDEX Y ( `card`); （左表建索引）

type:inde,ALL 

 有type等级知道ref>index

原因：这是由左连接特性决定的。 LEFT JOIN  条件用于确定如何从右表搜索行 , 左边一定都有 ,

所以右边是我们的关键点 , 一定需要建立索引。

同理。如果是右连接。那么就是右表全有，在左边建立索引。

多表：类似两表同理

总结：

1、小表驱动大表。

例如：A 表1w条数据。B表100w数据。所以两表查询的时候，用左连接的话，B做右表，建索引。

同理：右连接的话就是B做左表。建索引
2、优先优化嵌套循环里的内存循环。

3、Join语句中被驱动表上join条件字段已经被索引。

4、当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太栗色JoinBuffer的设置

二、索引失效

索引失效前提有索引：ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos); 

1、全值匹配我最爱 

例如：创建的复合索引，执行字段查询的顺序有Seq_in_index 排序。值越小，越先执行查询

 查询所有索引列：EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

查询单个列：EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

 查询两个：EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;

2、最佳左前缀法则（where 后的条件 按照 Seq_in_index 添加，且复合索引第一个不能没有） 

索引失效：

SQL1:EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';

 SQL2:EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

SQL3:EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and pos = 'dev';
 pos = 'dev';索引失效

3、不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

 

4、存储引擎不能使用索引中范围条件右边的列(索引列做范围，后面索引列失效)

 

 

5、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

 

 

6、mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

 

 

7、is not null 也无法使用索引,但是is null是可以使用索引的

 

8、like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

问题：解决like '%字符串%'时索引不被使用的方法？？

使用覆盖索引。（复合索引）

9、字符串不加单引号索引失效

10、少用or,用它来连接时会索引失效

 

 

11、