mysql复习2

LIJUN1456

已于 2024-07-17 14:48:32 修改

阅读量693

点赞数 25

分类专栏：基础复习文章标签： mysql 数据库

于 2024-07-16 23:48:18 首次发布

本文链接：https://blog.csdn.net/LIJUN1456/article/details/140476715

版权

基础复习专栏收录该内容

5 篇文章 0 订阅

订阅专栏

联合索引

联合索引的使用场景：如果where条件中使用到多个字段，并且需要对多个字段建立索引，此时就可以考虑采用复合索引（联合索引）。比如查询地址时需要输入省、市，那么在省、市上建立索引，当数据量大时会明显提高查询速度。

联合索引的优点：

减少查询开销：建立复合索引（c1,c2,c3），实际上相当于建立了（c1）,（c1,c2）,（c1,c2,c3）三个索引。对于大表来说，可以极大减少开销。
覆盖索引：MySQL可以直接通过遍历索引取得数据，而无需回表，减少了很多的随机io操作。
效率高：索引列越多，通过索引筛选出来的数据就越少，从而提升查询效率。

复合索引使用建议：在多条件下，建议尽量使用联合索引（每个表最多2~3个），一个sql，就算有多个单子索引，起作用的索引只能有一个，并且，多个单子索引消耗的资源明显要大于一个联合索引，最后，联合索引查询效率要高于单个索引

常见sql深入优化：

Order by与Group by优化

联合索引：

using filesort 为表外排序，利用临时表重新排序，排序慢

using index 为利用索引进行直接排序，排序更快

问题一：

分析：Extra结果为index,利用了索引进行排序

问题二：

分析二：没有age,不服合最左前缀原则，导致排序无法利用联合索引

问题三：

分析三：不服和最左前缀原则，age要和position互换位置才行，肯定是using filesort

问题四：

分析：因为前面age为18，所以后面排序的age就没用了，所以肯定是using index，利用索引排序

问题五：

分析：联合索引都是升序排列的，所以现在降序和升序无法使用索引排序

问题六：

分析：查询了全部字段，这就产生了一个问题，即使通过索引查出来，也只知道索引加主键按，其他字段无法知道，还要回表，所以这个就没走索引字段

修改的话，不用select*,用select name,age,postion from这样形成索引覆盖，就会走索引了

优化总结：

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。

2、order by满足两种情况会使用Using index。 1) order by语句使用索引最左前列。 2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了

索引设计原则（重点）：

1、代码先行，索引后上

所有主业务完成，相关sql也完成,在考虑索引优化

2、联合索引尽量覆盖条件

一个表可以设计一个或者两三个联合索引(尽量少建单值索引)，除非唯一索引（这里的唯一指的是业务上的唯一标识），让每一个联合索引都尽量去包含sql语句里的 where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则

3、不要在区分度小字段上建立索引

一般建立索引，尽量使用那些区分度比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。

4、长字符串我们可以采用前缀索引

对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)，这个的前提是前20个字符串有一定的区分度，并且此时不能用order by,否则直接会走filesort

5、where与order by冲突时优先where

where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？优先走where,因为where的赛选会使数据量变小

6、基于慢sql查询做优化，相关的mysql的设置

slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭。

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。

long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志。

log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。