B树索引以及sql优化

　　

上图意思：顶部左边蓝色块是17，右边蓝色块是35 ，现在要查找一个为28的数 ，28是介于17到35之间，那么应该通过中间黄色指针找到中间的数节点，在再中间的节点进行判断寻找；28在26到30之间，那么在往下找

三层Btree可以存放上百万条数据；

 

SQL优化之----执行计划

　　explain select * from teacher t,teacher_card tc,cource c where t.tid = tc.tid and tc.cid = c.cid;

　　

　　1.id 这里如果id相同，那么执行顺序为从上往下执行，多表查询时的顺序时候是按照表的数据多少来决定顺序的；(笛卡尔积 【6*3*2】 【2*3*6】 )；id值不同，id值越大越优先被查询；

　　2.select_type

　　　　PRIMARY：包含子查询sql中的主查询；即【SELECT * FROM tracher t WHERE t.tid = (SELECT tid FROM course c where c.cname = 'java')】; 该查询的最外层

　　　　SUBQUERY：包含子查询的 子查询；（非最外层）

　　　　simple：简单查询（不包含子查询、union）

　　　　derived：衍生查询（查询时候用到了临时表）；在from子查询中如果有table1 union table2，则table1就是衍生表

      3.type

　　　　ALL：全表查询一遍

　　　　range：范围查询（between、<、>=、in）

　　　　index：查询索引【select tid from teacher;】tid为主键索引

　　　　const / system：结果只有一条数据

　　　　eq_ref：结果多条但是每条数据是唯一的；

　　　　ref:：结果多条，单每条数据是0条或者是多条；

　　4.possible_keys :可能用到的索引、

　　5. key实际用到的索引

　　6. key_len ：索引长度；作用：用于判断复合索引是否完全被使用；　　

　　　　比如新建一个索引对应的列是char(20)不可以为null,通过该索引查询的时候索引长度应该是60，如果该字段可以为空那么是61，如果是varchar类型那么该索引长度是63；

　　　　即在mysql中：可以为null字段需药通过一个字符标识，varchar是可长度变字符，需要通过2个字符来标识，  60=20*3

　　　　UTF8:1个字符等于三个字节

　　　　gbk：1个字符等于两个字节

　　　　latin：1个字符等于一个字节

　　7.ref：作用--指明当前表锁参照的字段，

　　　　const:常量 要么是用到的数据表字段

　　8.rows:被优化的查询的个数（实际通过索引查询的个数）

　　9. Extra :

　　　　a)： using filesort:性能消耗大，需要额外一次排序操作（查询）；入下sql:explain SELECT * FROM tracher WHERE tname = "" ORDER BY tcid;    一般常见于order by

　　　　小结：对于单索引字段，如果排序和查找字段是同一个字段则不会出现using filesort；对于单索引字段，如果排序和查找字段不是同一个字段则会出现using filesort；

　　　　　　　　　对于复合索引（不能跨列）【使用复合索引时候，where和order by拼起来的索引不要跨列使用，where中失效索引需要除开】

　　　　　　　　　

 　　　　　　　　　小结：对于复合索引where和order by 按照复合索引的顺序使用，不要跨列或者无序使用；

　　　　

 　　　　b)：suing temporary：性能损耗大，用到了零时表、一般出现在group by语句中。（已经有表了但是不适用需要在开一张表来使用）

　　　　　　　　比如数据库有字段a1和a2，根据a1查询但是根据a2分组，就会出现查出a1数据后在获取a2数据的临时表，根据a2排序后在返回到查询表；

　　　　　　　解决思路：查询什么列就根据什么列分组；

　　　　　　　　　　　sql解析过程： from... on.... join......where.....group by.....having......select dinstinct ....order By  limit .....

　　　　　　　　　　　　i）.explain select * from test03 where a2 =1 and a2 = 4 group by a2，a4；    ------ 没有出现useTemporary

　　　　　　　　　　　　ii）.explain select * from test03 where a2 =1 and a2 = 4 group by a3；           ------ 出现useTemporary

 　　　　c）：using index：性能提升，索引覆盖（说明此sql执行不读取源文件，只需要读取索引文件；不需要回表查询）

　　　　d）：using where： 即需要从索引中查有需要回表查询；

　　　　e）：impossible where：where子句永远为false；

　　　　　　explain select * from test01 where a1 =  1 and a1=2；

　　　　f） ：using join buffer :mysql认为sql写的较差，加了缓存

 

优化：

　　（1）：单表优化

　　　　　　a) :根据sql实际解析顺序调整索引顺序；

　　　　　　b) : where后中有in的，范围查询in可能会导致索引失效，一般将有in的查询放到最后；

　　　　　　c) :索引不能跨列使用（最佳做前缀），保持索引的定义和使用顺序一致性；

　　（2）：两表优化

　　　　　　a): 小表驱动大表；[程序设计原则：对于双循环，一般外循环次数越少，效率越高]

　　　　　　　　eg： where 小表.x=大表.x;

　　　　　　b) : 索引建立在经常使用的字段上；对于左外连接一般左表加索引，右外连接右表加索引；

　　　（3）: 三表优化

　　　　　　a）：多表优化：小表驱动大表

　　　　　　b )  : 索引建立在经常查询的字段　　、

 

避免索引失效原则：

　　　（1）：避免使用in　　

　　　（2）: 复合索引不要跨列或者无序使用（最佳左前缀）

　　　（3）: 尽量使用全索引匹配；即建了复合索引三个字段，尽量全用上；

　　　（4）：不要在索引上进行任何操作（计算，函数，类型转换），否则索引失效；

　　　（5）： 复合索引不能使用不等于（！= <>）或者 is null （is not null），否则自身以及右侧索引全部失效；

　　　（6） ： 尽量不适用类型转换（显示或者隐式），否则会导致索引失效；

　　　（7）: 尽量不使用or 否则会导致索引失效；、

其他优化：

　　（1）exist和in：如果主查询数据大则用in，如果子查询数据大则用exist；

　　exist语法：将主查询的结果放到子查询中进行条件校验，如果符合校验则保留数据；

　　（2）：order by （using filesort有两种算法：双路排序、单路排序【根据io的次数】）

　　　　　　mysql4.1之前默认使用 双路排序；双路：扫描两次磁盘，第一次读取排序字段，第二次取出其他字段

　　　　　　mysql4.1之后默认使用 单路排序；只读取一次（全部字段全部读取），在buffer中进行排序，但是有一定隐患（不一定真的是单次“如果数据量太大可能会一次读取部分数据，分片读取”）；如果数据大可以考虑调大buffer容量的大小：set max_length_for_sort_data = 1024; 单位字节

　　　　执行order by查询策略

　　　　　　a）选择使用单路，双路；调整buffer的容量大小；

　　　　　　b）避免使用 select *

　　　　　　c）复合索引 不要跨列使用，避免using filesort

　　　　　　d） 保证全部的排序字段排序的一致性（都是升序或者降序） 

sql排查--慢查询日志：mysql提供的一种日志记录，用户记录mysql响应超过阈值的sql（long_query = 10s）

　　　　慢查询日志默认是关闭的，建议开发调优时打开，上线是关闭；

　　　　a）检查是否开启了慢查询日志：show variable like ‘%low_query_log%’;   临时开启：set global slow_query_log =1；

　　　　　　　　永久开启：在my.cnf中增加配置；在[mysqld]下面追加：slow_query_log_1 = 1；slow——query_log_file=/var/lib/mysql/slow.log

　　　　　　　　慢查询阈值设置：set_global_query_time = 5；重启生效

　　　　b）查询超过阈值的sql数量：show global status like ‘%slow_queries%’；

　　　　c） 定位慢查询的sql ；通过配置的，慢查询的日志文件定位；通过一些过滤条件快速查找出需要定位的慢sql；

　　　　d）通过mysqldumpslow工具查看慢查询：mysqldumpslow --help

　　　　　　1.s:排序方式

　　　　　　2.r:逆序

　　　　　　3.l:锁定时间

　　　　　　4.g：正则匹配模式

　　　　　　eg：返回记录最多的3个sql:

　　　　　　　　　mysqldumplow  -s r -t 3  /var/lib/mysql/slow.log　

　　　　　　　　获取访问次数最多的3个sql

　　　　　　　　　　mysqldumpslow -s c -t 3  /var/lib/mysql/slow.log

　　　　　　　　　　

　　　　　　　　按照时间顺序，前10条包含 left join的查询语句sql

　　　　　　　　　　mysqldumpslow -s t -t 10 "left join"