经过第一天的成功给leader的问题都给回答上来，我的leader苦思冥想一晚上，各种百度，各种搜，各种查，终于搜到了法子对付我。。。害，我估计我离走是不远了

leader：小伙子啊，看你昨天回答的不错，今天我还有一些问题，看你能不能回答出来了

我：老大，我错了。我真的不会别的了！

leader：别别别，您这么牛，怎么能不会呢，废话少说，如果回答不出来，哼哼，你耗子尾汁

leader：听着，你昨天不是说出来覆盖索引了么，那我今天在问问你，最左前缀法则是什么你知道么？

我：（心里：就这？看来昨天老大这百度没百度出来啥东西呀）我想想奥。。。

最左前缀法则

说到最左前缀法则，就要想到复合索引，复合索引是遵循最左前缀法则的，顾名思义，就是查询的时候以最左侧的为例开始使用索引，如果从索引的第二列或者之后开始，索引就会失效

这里select name，age，sex from user where name='xx'这个是走索引的 where name='xx' and age='22' 这个也是走索引的，where name='xx' and age='22' and sex='男'这个也是走索引的

select name，age，sex from user where age='22' 这个是不走索引的，where age='22' and sex='男' 这个也是不走索引的，where name='xx' and sex='男'这个是走索引的，但是只会走name，走不到sex，所以，大家明白了，他这个是完全从左到右的来执行，如果中间断了，那么断之前是走索引的，断之后是不走的，如果不是从最左开始判断，那就都不会走索引。

leader：别得意，这只是前戏，那你说一下，如何sql优化吧。记得要说全点，不然你走着瞧。

我：。。。。这。。。。。

SQL优化

提到SQL优化，一定就得提到一个工具，Explain，它可以对select语句进行分析，并输出select执行的详细信息，供开发人员有针对性的优化

还是昨天的语句

EXPLAIN select userID,userName,age from user where userName='张三'

简单来分析一下：

select_type：表示查询的类型，常用的值如下：

SIMPLE：表示查询语句不包含子查询或union
PRIMARY：表示此查询是最外层查询
UNION：表示此查询是UNION的第二个或后续的查询
DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
UNION RESULT：UNION的结果
SUBQUERY：SELECT子查询语句
DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果最常见的查询类型就是SIMPLE，表示我们的查询没有子查询也没有用到UNION查询

table：这一列表示正在访问哪个表。

type：表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用的属性值如下，从上至下效率依次增强。

ALL：表示全表扫描，性能最差
index：表示基于索引的全表扫描，先扫描索引再扫描全表数据
range：表示使用索引范围查询。使用>,>=,<,<=,in等等
ref：表示使用非唯一索引进行单值查询
eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
const：表示使用主键或唯一索引做等值查询，常量查询
null：表示不用访问表，速度最快。

possible_keys：表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称

key：表示查询时真正使用到的索引，显示的是索引名称

rows：MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录，原则上rows是越少效率越高，可以直观的了解到SQL效率高低

key_len：表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引 key_len的计算规则如下：

字符串类型
- 字符串长度跟字符集有关：latin1=1，gbk=2，utf8=3，utf8mb4=4； char(n)：n字符集长度 varchar(n)：n字符集长度+2字节
数值类型
- TINYINT：1个字节
- SMALLINT：2个字节
- MEDIUMINT：3个字节
- INT,FLOAT：4个字节
- BIGINT，DOUBLE：8个字节
时间类型
- DATE：3个字节
- TIMESTAMP：4个字节
- DATETIME：8个字节
字段属性
- Null属性占用1个字节，如果一个字段设置了Not NULL，则没有此项

ref：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（列：film.id）

Extra

Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：

Using where
- 表示查询需要通过索引回表查询数据
Using index
- 表示查询需要通过索引，索引就可以满足所需数据
Using filesort
- 表示查询出来的结果需要额外的排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化
Using temprorary
- 查询使用到了临时表，一般出现于去重，分组等操作。

说到这里，相信大家做查询的时候，一定是不少使用like进行模糊查询吧？那用like查询的时候，索引能起到作用么？

MySQL在使用like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。还是之前的表

ALTER TABLE user ADD INDEX u (userName,age);
EXPLAIN select userID,userName,age from user where userName like'%三%'

首先看一下type类型是ALL，说明是全表扫描，在看一下key是空，说明没有用到索引

EXPLAIN select userID,userName,age from user where userName like '三%'

大家看到区别了吧，这里是走到了u这个索引，而这个u的索引是上面创建的userName和age的组合索引。

这里聪明的人应该就想到了，为什么%写前面不走索引，上一篇也说了MySQL索引底层是B+树，前面写百分号就相当于要把所有的都搜索一遍，肯定不走索引呀，对吧。

在MySQL中，查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或者磁盘进行排序操作，效率低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。

filesort有两种排序算法：双路排序和单路排序

双路排序：需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序；第二次去读取其他字段数据。

单路排序：从磁盘查询所需的列数据，然后在内存排序将结果返回。如果查询数据超出缓存sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。解决方案：少使用select * ；增加sort_buffer_size容量和max_length_for_sort_data容量

如果我们Explain分析SQL，结果中Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。如果Extra属性显示Using index时，表示覆盖索引，也表示所有操作在索引上完成，也可以使用index排序方式，建议大家尽可能采用覆盖索引。

以下几种情况，会使用index方式的排序

Order by子句索引列组合满足索引最左前列

explain select id from user order by id; //对应(id)、(id,name)索引有效

WHERE子句+ORDER BY子句索引列组合满足索引最左前列

explain select id from user where age=18 order by name; //对应 (age,name)索引

以下几种情况，会使用filesort方式的排序

对索引列同时使用ASC和DESC

explain select id from user order by age asc,name desc; //对应 (age,name)索引

WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询(例如>、<、in 等)

explain select id from user where age>10 order by name; //对应 (age,name)索引

ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列

explain select id from user order by name; //对应(age,name)索引

使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引

explain select id from user order by name,age; //对应(name)、(age)两个索 引

WHERE子句与ORDER BY子句，使用了不同的索引

explain select id from user where name='tom' order by age; //对应 (name)、(age)索引

WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式

explain select id from user order by abs(age); //对应(age)索引

下面再给大家举例几种常见的不走索引的情况：

在索引列上做任何操作（计算，函数，（自动or手动）类型转换），会导致索引失效
使用!=或者<>，not in，not exists的时候无法使用索，<小于，>大于，<=，>=这些，mysql内部优化器会根据索引比例，表大小等多个因素整体评估是否使用索引
is null，is not null一般情况下也无法使用索引
字符串不加单引号索引失效(这里是很坑的，之前有一次我们项目上也出现了这个小失误，有人写sql字符串上没加单引号，就比如phone设置的varchar类型，但是查询的时候，直接phone=123这样查询，而不是phone='123'这样查询，由于数据量非常的大，导致整个集群压力都很大，服务卡死。排查了半天才发现这个问题，这也是大部分人很容易马虎的地方
少用or或in，用它查询的时候，mysql不一定使用索引，mysql内部优化器会根据检索比例，表大小等多个因素整体评估是否使用索引。
如果是范围查询，范围比较大的情况下，可能也是不走索引的，建议把范围拆开，拆成两个小范围这样，是可能会走索引的

之前还有一次我们在查询比较慢的接口的时候，最后找到sql的问题，因为这个表一开始压测的时候是没什么问题的，因为数据量比较小，但是数据量大起来的时候，就发现问题了，全是无效索引的。就很难受

其实数据库优化，也不仅仅是在sql层面，硬件啊，表结构设计啊，商业需求都有关系，有些时候，sql再优化，还是会很慢，那这个就可以看看这个需求是否合理了，就比如，你几十亿的数据，人家就要全都看，各种条件查询，那你觉得这个合理么，我觉得需求层面就不太合理，大家在写代码的时候，不要只是为了完成业务，更多的是要思考性能方面的问题，业务不管是简单还是难，只能说对你的提升是有帮助，但是如果想去好的互联网公司，性能优化这里是必须的，还有系统的整体架构都要摸清楚。这才是最锻炼人的。

leader：行啊，小伙子，最后来这么长句感言，不过你说的还是不太细啊，这具体的sql优化

我：老大啊，我这要说细了，我不得说一天啊，如果这个文章点赞可以破五十，我之后单门出一篇文章具体实战一下sql优化！

leader：这是你说的，小伙子，今天我还是没压住你，我再想想mysql还有什么题能考考你奥。。。

我：。。。。。

leader：有了！那你简单来给我说说mysql的事务和锁这块吧？

我：。。。。老年人你是真不讲武德，我大意了，没用闪。。。我这里还没学呢！不是说索引这里么！！！

我：。。。溜了溜了。回家好好看看这里下次再来回答你！

被leader怼对MySQL索引一无所知，我再次打了leader的脸，最后leader不讲武德，我没闪大意了

最左前缀法则

SQL优化