Mysql数据库索引优化

    在讲Mysql数据库优化前先讲讲Mysql是怎样执行我们写的sql。

一、Mysql分层

连接层:提供与客户端连接的服务(只是和客户端建立连接)

服务层:第一是提供各种用户使用的接口(select,insert...)。第二是提供SQI优化器(Mysql会自动等价优化我们写的sql)

引擎层:提供了各种存储数据的方式(InnoDB MyISAM等)

InnoDB(默认):事务优先 (适合高并发操作;行锁)每行数据都加个锁。支持外键

MyISAM:性能优先(表锁)

存储层:存储数据

二、SQL解析过程

SQL编写过程:select ....from ...join ...on ...where ...group by ....having ....order by ...limit....

SQL解析过程:from ....on ...join ...where ... group by ...having ...select ...order by ...limit....

三、SQL性能问题(优化索引)

分析SQL执行计划:explain+SQL。可以模拟SQL优化器执行sql,从而知道自己写的sql性能高低。

Mysql查询优化会干扰我们的优化

explain+SQL 参数讲解:

1,id:编号

这是一个三表查询例子:

结论:当id值相同时,由上到下顺序执行,先查询t、tc、c(笛卡尔积,表数据小的先执行,前提编号id相同)

这是一个子查询例子:

结论:id值不同,id值越大越优先执行(本质:在嵌套子查询时,先查内层,再查外层)

这是多表+子查询的例子:

结论:id有相同又有不同时,大的先执行,相同的由上往下顺序执行

2,select_type:查询类型

PRIMARY:包含子查询SQL中的主查询(最外层)

SUBQUERY:包含子查询SQL中的子查询(非最外层)

SIMPLE:简单查询(不包含子查询、union)

DERIVED:衍生查询(使用到了临时表)

UNION:在from子查询中有table1 union table2  table1就是DERIVED,table2  就是UNION

UNION RESULT:告知开发人员哪些表时UNION

3,table:所查询的表名

4,type:类型(该条SQL的查询性能),常见的有system>const>eq_ref>ref>range>index>all 

4.1,system:只有一条数据的衍生表(基本达不到,忽略)

4.2,const:仅仅能查到一条数据的sql,用于Primary key 或 unique索引(基本达不到),与索引类型有关

4.3,eq_ref:唯一性索引(基本达不到):对于每个索引健的查询,返回匹配唯一行数据(有且只有一个,不能多,不能为0)

例如:select ... from student  where name='JAY';查询出来的数据可以有很多条,但是名字叫JAY的只能有一条

4.4,ref:非唯一性索引:对于每个索引健的查询,返回所有行(0,多)

4.5,range:检索指定范围的行,where后面是一个范围chaxun(between  in  <>)

4.6,index:查询全部索引中数据

4.7,all:查询全部表数据

5,possible_keys:可能用到的索引

6,key:真正用到的索引

7,key_len:实际所用索引长度(作用:用于判断复合索引是否被完全使用)

例如:创建了一个3个字段的复合索引(a,b,c),三个字段都是char(20)类型(一个字符等于3个字节),就可以根据key_len来判断用了几个字段的索引了。(字段允许为空的话 会额外用一个字节标识)

a char(20) :是固定长度类型。一个字符等于3个字节,如果a可以为null,额外用一个字节标识

a varchar(20):可变长度类型。一个字符等于3个字节,如果a可以为null,额外用一个字节标识,额外用两个字节标识可变类型

8,ref:表之间的引用。作用:指明当前表 所参照的字段

例如:select ..... from ....where a.c = b.x (b.x可以是常量,如果是常量则值为const)

9,rows:通过索引查到的行数

10,Extra:额外字段

10.1, using filesort:性能消耗大,需要一次额外的排序(查询),需要优化    常见于order by中 

排序前提:先查询

先讲一个单值索引例子:我们创建一个表,有3个单值索引:

create table test02
(
a1 char(3),
a2 char(3),
a3 char(3),
index  idx_a1(a1),
index  idx_a2(a2),
index  idx_a3(a3),
)

explain select  * from test02 where a1 ='' order by a1; 

explain select  * from test02 where a1 ='' order by a2;  --------using filesort

小结:对于单个索引,查询和排序不是同一个字段(要额外再拍一次序),则会出现using filesort

             避免:where哪些字段就order by哪些字段

复合索引例子:
drop index idx_a1(a1) on test02;
drop index idx_a1(a2) on test02;
drop index idx_a1(a3) on test02;
alter table test02 add index idx_a1_a2_a3(a1,a2,a3);

explain select  * from test02 where a1 =' ' order by a2; 

explain select  * from test02 where a1 =' ' order by a3;  --------using filesort

explain select  * from test02 where a2 =' ' order by a3;  --------using filesort

(1)explain select a1 from test02 where a2 =' ' order by a3; --------using filesort 

(2)explain select  a2 from test02 where a1 =' ' order by a3;

(1)为什么会出现using filesort 呢?前面我们讲到SQL解析过程为:

   from ....on ...join ...where ... group by ...having ...select ...order by ...limit....,所以上述SQL应改为(2)

小结:复合索引:不能跨列(最佳左前缀原则)

            避免:where 和 order by 按照复合索引顺序使用,不要跨列或无序使用。也要注意SQL解析顺序

           将含in的范围查询,放到where条件的最后(因为范围查询常常导致索引失效,以免影响后面的索引)

10.2,using temporary:性能消耗大,用到了临时表。常见于 group by

explain select  * from test02 where a1 in ('1' , '2', '3')  order by a1;

explain select  * from test02 where a1 in ('1' , '2', '3')  order by a2; --------using temporary

避免:查询哪些列,就用哪些列group by

10.3,using index:性能提升,覆盖索引。原因:只需要查询索引不需要回表查询

例:age、name是复合索引: (age,name)

select  age,name from test where age='' and name='';

覆盖索引:一个索引覆盖了(包含了)满足查询(select)语句中字段与查询条件(where)中所涉及的字段,可以只查索引,不回表查询

10.4,using where:需要回原表查询

10.5,impossible where:where字句永远为false

10.6,using join buffer:Mysql引擎使用了连接缓存(编写的sql太差,启动缓存)

四、索引优化

4.1 单表优化

主要就是上面讲到的:

复合索引:不能跨列(最佳左前缀原则)

将含in的范围查询,放到where条件的最后(因为范围查询常常导致索引失效,以免影响后面的索引),假如复合索引(a,b,c),最佳左前缀原则,如果a索引失效了,则b、c都失效

4.2 多表优化

select  *  from teacher2 t left join course2 c on t.cid=c.cid where c.name = 'java';

小表驱动大表: t.cid=c.cid   t.表有10条数据,放左边、c表有300条数据,放右边

索引:如果是左连接,索引一般加到右边。右连接索引一般加到左边

索引建立在经常查询的字段

这里顺便附上join常用写法图:

很多索引优化的理解都可以参考上篇文章讲的Mysql索引底层的数据结构来理解。

五、索引使用场景

5.1,适合建立索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段,外键关系建立索引
频繁更新的字段不适合建立索引(因为每次更新不单单是更新了记录还会更新索引)
where条件用不到的字段不创建索引
单键/组合索引 倾向选择组合索引
查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度(order by name,age,height时,建立索引(name,age,height))
查询中统计或者分组字段(group by,分组前提必排序)

5.2,不适合建立索引

表记录太少(300万条数据后,效率下降明显)
经常增删改的表
如果某个数据列包含许多重复且平均分配的内容,为它建立索引就没有太大实际效果(选择性:如果一个表有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性为1980/2000=0.99,越接近1,索引效率越高)

六、常见索引失效场景

6.1,复合索引
不要跨列或无序使用(最佳左前缀)
尽量使用权所有匹配
不能使用(!= <>)或is null is not null,否则自身和右侧索引全部失效
6.2,不要在索引上进行任何操作
SQL优化是概率层面的优化(服务层有SQL优化器,会影响我们的优化),需要用explain去验证Mysql底层是否使用了我们的优化
尽量使用覆盖索引(100%生效)
6.3,like尽量以"常量"开头,不要以%开头,否则索引失效
select * from teacher where name like %aa%  name索引失效
select name from teacher where name like %aa%  如果非要用%开头,使用覆盖索引挽救
6.4,尽量不要使用类型转换,否则索引失效
tname 为字符串类型
select * from teacher where tname = 123//底层将123 ->'123'
6.5,尽量不要使用or,否则索引失效

七,常见优化方法

(1)exist和in
如果主查询的数据集大,则使用in。如果子查询数据大,则使用exist。
exist语法:将主查询的结果,放到子查询结果进行校验,如果符合校验,则保留数据
select tname from teacher where exist (select * from teacher)
(2)order by 优化
using filesort有两种算法:双路排序和单路排序(根据IO的次数)
Mysql4.1之前默认使用双路排序(扫描两次磁盘)
Mysql4.1之前默认使用单路排序(只读取一次全部字段,在buffer中排序,如果buffer容量太小会自动切换到双路排序)
优化:
选择使用单路、双路;调整buffer的容量大小
避免使用select *....
复合索引 不要跨列使用,避免using filesort
保证全部的排序字段,排序的一次性(都是升序或降序)

八,SQL排查

慢查询日志:Mysql提供的一种日志记录,用于记录Mysql中响应时间超过阈值(默认10S)的SQL语句(long_query_time)。慢查询日志默认是关闭的。建议:开发调优时开启,上现网环境关闭

8.1,检查是否开启了慢查询日志:show variables like '%slow_query_log'; 默认关闭

8.2,开启慢查询日志

临时开启慢查询日志:set global slow_query_log =1;

永久开启慢查询日志:etc/my.cnf 中追加配置:

8.3,慢查询阈值

查看慢查询阈值:show variables like '%long_query_time';

设置慢查询阈值:set global long_query_time=3;

执行语句:select sleep(4);该SQL超过我们设置的慢查询阈值3S。

查询超过阈值的SQL:show global status like '%slow_queries%';

具体可以到日志文件中查看。或者可以通过mysqldumpslow(mysql自带)工具查看,具体参数命令查看mysqldumpslow --help

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值