sql优化及原理详解，五分钟读懂sql优化

在我而言这算是一个复习，然后总结出来给大家当个教材吧。

我也是看视频总结出来的笔记，所以说的都很简单和浅薄。有不全面或者偏颇的地方欢迎指出，共同交流进步哈。（因为我当时是看视频总结的笔记，所以可能说的比较杂乱，我尽量写的分明一点，在最后会附上笔记，忽略我字丑）

索引是什么呢？它相当于字典的目录。

索引：index是帮助mysql高效获取数据的数据结构，索引是数据结构（树，默认是B树），hash等。

索引的弊端: 事物都是两面的，有利必然有弊。

索引的优势： 索引有这么多弊端我们还使用的原因是因为优大于劣。

索引的分类：

举个小例子让大家更理解复合索引：如果我把一个表中name，age这两个列做成复合索引（注意顺序很重要）。那么我们形成的目录一级目录是name，二级目录是age。在name相同时才会age再形成目录。因为它本身的排序不是像目录一样一行一行列出来的，所以我们尽量用目录来想像它比较好理解。下面是图解：

有几点注意的事项：

这里说一下，上面说的方法都是原生的sql，比如我现在习惯使用navicat，所以可以直接 *** 作。。爽的不行。

然后删除查询也都是直接可视的，方便的不得了。就不多说了。

mysql做例子，还有个引擎是可以优化的。mysql中引擎分两种：

sql优化等级：

上面说的这些等级在explain中可以看到。

单表优化常用方法：

多表优化常用方法：

因为上面也提到了b树，所以还是单独聊聊吧。其实我也不是很理解。只能说一个浅显的认识而已。这里也就是简单的说一下。

首先，B树不仅可以二叉，还可以三叉，多叉。而只要大于二叉的都叫做BTree。

据说三层BTree可以存放上百万数据。

BTree一般都指B+树，数据全部存放在叶节点中。（这里简单的一个三叉树图）

好了，就写到这里吧，希望日后算法的知识会的更多以后能把B树这个坑填完~~~然后有不同意见或者自己理解的可以留言或者私聊。

全文手打，如果你觉得对你有帮助麻烦点个赞点个关注啥的~~

SQL优化一： sql优化（一）

上片文章已经详细介绍了explain各个字段的含义，以及什么情况应该建立索引，什么情况不需要建立索引以及sql语句性能的判断依据，接下来我介绍下如何合理的建立索引。

sql语句：select id，author_id from article where category_id = 1 and comments>1 order by views desc limit 1

分析：首先我们根据where后面的条件建立符合索引，然后根据order by后面的字段建立索引，因此建立索引idx_article_ccv，即以（category_id，comments，views）数据列建立复合索引，但由于comments是一个范围，按照BTree索引的原理，先排序category_id,如果遇到相同的category_id则再排序comments，如果遇到相同的comments则再排序views，又因为comments字段在复合索引里处于中间位置，而comments>1是一个条件（是一个范围值），在复合索引的一个范围值的数据列后面的索引全部失效，mysql无法利用索引再对后面的views部分进行检索，也就是说views无法按照索引排序，所以explain下此sql语句，type为range，extra使用的是Using filesort，这是比较糟糕的。所以我们放弃comments这个范围字段，建立索引idx_article_cv，即以（category_id，views）数据列建立复合索引，explain 此sql，type变成了ref，extra的using filesort也变成了using index，这就变得好多了。

索引：idx_article_cv，即以（category_id，views）数据列建立复合索引

前段时间做了一个销售精细化项目，是公司crm项目的一个大模块，大致就是为销售人员制定指标，实现销售目标从区域到团到业务员到客户，实时跟踪业务员所负责客户的下单量的情况。这就存在许多关联关系，区域-团，团-业务员，业务员-客户，这使得sql常常需要关联多张表。

sql语句：SELECT

tu.fuserid,

tu.faccount,

tu.fphone,

tu.fcertificationtype,

tu.fcertificatename,

tu.fkeyarea,

tu.fkeyareatext,

DATE_FORMAT(tcr.fupdatetime,'%Y-%m-%d %H:%i:%s') as fupdatetime,

tag.forggroupid,

tag.forggroupname,

tug.forguserid,

tug.fusername,

tug.fuserphone,

tag.fcitycode

FROM t_finedt_user AS tu

LEFT JOIN t_finedt_customer_relation AS tcr

ON tu.fuserid = tcr.fuserid

LEFT JOIN t_finedt_usergroup AS tug

ON tcr.forguserid = tug.forguserid

and tcr.forggroupid = tug.forggroupid

LEFT JOIN t_finedt_areagroup AS tag

ON tug.forggroupid = tag.forggroupid

where tu.fkeyarea=? and tu.fuserid=? and tug.forggroupid = ?

分析：上面的sql是左连接，左边的表一定是全表查询，所以要建立右边表对应关联字段的索引，在表t_finedt_user上建立tu_fuserid_fkeyarea索引，即以(fuserid，fkeyarea)字段建立索引，在表t_finedt_customer_relation 上建立tcr_forguserid_forggroupid索引，即以（forguserid，forggroupid）字段建立索引，在表t_finedt_usergroup 上建立tug_forguserid_forggroupid索引，即以（forguserid,forggroupid)字段建立索引，在表t_finedt_areagroup上建立tag_forggroupid索引，即以（forggroupid)字段建立索引。建立索引后，sql查询速度明显快了很多

索引：tcr_forguserid_forggroupid，tu_fuserid_fkeyarea，tug_forguserid_forggroupid，tag_forggroupid

1、尽可能减少join语句中的NestedLoop的循环次数，永远用小结果集驱动大结果集

2、优先优化NestedLoop的内层循环

3、保证join语句总被驱动表上的join字段已经被索引

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

1、全值匹配我最爱

2、最佳左前缀原则——如果索引了多列，要遵守最左前缀原则，指的是查询从索引的最左前列开始并且不跳过索引中的列

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

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

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

6、mysql在使用不等于（！=、>、<）的时候无法使用索引会导致全表扫描。

7、is null、is not null也无法使用索引。

8、like以通配符开头（"%abc.."），mysql索引失效也会变成全表扫描的 *** 作。

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

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

1、对于单值索引，尽量选择针对当前query过滤性更好的索引

2、在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好

3、在选择组合索引的时候，尽量选择尽可能包含当前query中的where字句中更多字段的索引

4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

全值匹配我最爱，最左前缀要遵守

带头大哥不能死，中间兄弟不能断

索引列上少计算，范围之后全失效

like百分写最右，覆盖索引不写里

不等空值还有or，索引失效要少用

var引号不可丢，sql高级也不难

只有5种吗？我知道十种以上的说。

索引（没我得全表查询了）

改变数据储引擎（MyISAM没事务再也不用担心锁表了）

增加冗余数来减少连表查询数（消耗硬盘空间减少CPU使用）

调整查询顺序减少查询量优先（数量少了连表的笛卡儿积也少了）

全文索引（文字长度有限制，而且IO使用量会大增，但是妥妥的快）

查询尽量不要用函数（函数可是不走索引的哦亲）

查询变量类型要提前对好减少系统负担（我提前改变了系统你就不用检测了）

升级服务器硬件（没什么是氪金解决不了的）

配置好临时表空间，合理理由临时表减少主表查询抢资源（唯我独查）

合理理由函数减少系统的判断（明明都能确认内容不同你用UNION 系统还是傻傻的查一遍是否重复 UNION  ALL则跳过这个步骤同理 inner join 和 left join 也一样 ）

强制走索引（复合索引的情况有时候手动走比系统判断要好哦）

脏读、幻读等（你堵车我绕路）

数据归档，迁移（没用的数据要进仓哦，别占着主表的资源）

表的碎片整理（迁移后碎片整理更健康哦亲）

索引重构（数据都走了索引也应该重构一下才能保证速度哦）

善用存储过程（串N个表（N大于10）的查询千万别一个SQL到底，分布式查询在吧结果集合并吧骚年）

预处理数据（mysql也有job哦，对于经常要子查询的数据可以先弄个明细表根据主表在后台进行补完，查询的时候就更方便了）

懒得说了。。。。。。。。。。。。。。。。。。

---