mysql 复合索引b树_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"