数据库 mysql 优化一

  • Post author:
  • Post category:mysql

最近遇到了一个问题,我们测试时的用例库只有一万条数据,但是查询已经肉眼可见的很慢了,于是想着或许可以优化一下查询。

然后今天让男朋友讲了一下数据库优化的知识,虽然自己也学习过,但一直也没在实战中用到过。男朋友做过数据库优化的内容,所以今天就让他借禅道的数据库讲了一下。

比较散装,但是也很口语化非常容易理解,而且是项目中经常用到的。

前言:做数据库优化的前提是,我们的表已经存在了,我们在不改变表结构的前提下,如何利用索引、修改查询语句、修改查询方式等方法进行优化。

目录

一、确定索引

1、索引解释及索引查询

2、问题及解答

二、查询语句

情况一:

情况二:

三、引擎


一、确定索引

1、索引解释及索引查询

索引:给某个字段建一个属于它的目录。

打开某张表,如图,UNIQUE KEY 就是一个索引,且是唯一索引,且是组合索引。

它这个索引是`owner`,`module`,`section`,`key` 这四个字段组成的一个索引,这四个字段组合可以确定唯一的数据。

所以:UNIQUE KEY `unique` (`owner`,`module`,`section`,`key`) 即是四个字段组合成了一个唯一组合索引。

我们在查询的时候,如果遇到查询慢,第一步就是确定这个表有没有索引,第二步确定查询语句有没有用到索引,是否是全文检索数据,如果是全文检索,我们优化时就可以做成用索引查询。

网上的图书目录就是一个很好的例子,用目录(索引)查询比一页一页翻书查询要快的多。

索引查询:即在where 后面跟上索引字段的条件,例如:

2、问题及解答

a、索引有单个索引和组合索引等,对于我们上面的组合索引UNIQUE KEY `unique` (`owner`,`module`,`section`,`key`) ,是否建立四个单个索引,查询效率一样?

答:否,虽然此时有了四个索引,但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。所以和四个字段组合成一个索引的效率是不一样的。

b、组合索引的最左原则:where后面一定要有组合索引最左边的字段,这个索引才能生效,否则索引不生效(即,一定要有owner字段,否则即使用索引里面的section 、key等字段放在where后面,也不会用到它的索引,还是会全文检索)。

例子:(1)不用owner索引就不生效:

(2)用了owner索引才生效:(owner和section 前后顺序没关系,似乎是mysql优化了,因为之前的版本owner必须在前,索引才能生效。)

但是,如果一个表中所有的字段都建立了索引,那不管怎么查,都会用到索引,因为它相当于给整个表建立了索引,所以这种情况不存在最左原则。

比如这张表:一共3个字段,3个字段都建立了索引,所以where后面不管怎么查,都是用到了索引:

有最左,用到了索引:

无最左,也用到了索引:

c、当用到的索引字段,它的值在表中数据大部分都一样时,用这个索引字段查询,索引仍旧不生效:

例如:索引字段owner,它有两个值,“system”占了很多,“admin”只有两个。那么我用 where owner=”system”时,索引就不生效,而用where owner=”admin”时,索引就生效,这也很好理解,因为你要查的数据值占了很大篇幅,我直接全文检索就可以了,你查的值只占了很小的篇幅,我再用索引就会明显快。

where owner=”system”时,索引就不生效:

where owner=”admin”时,索引就生效:

有些说法“当索引字段查询条件有 > = < + – * % is null ,is not null时,索引不生效 ”,但其实我执行的时候,都生效,所以mysql不同版本应该有不同要求,至于要完全弄清,大概要花费非常多的时间,是DBA的必修吧。

但对于我们来说,每个人职业规划不同,所以术业专攻。

 

二、查询语句

讲完索引,我们看语句。

下面只讲两种情况:

情况一:

sql1: 

select * from zt_grouppriv t1 left join zt_group t2
on t1.`group` = t2.id
where t1.module = "bug"

这种查询,第一步会先生成一个虚拟表,这个虚拟表里有t1的所有数据,如果t1非常大,那么就会生成一个非常大的虚拟表;

第二步才会用where t1.module = “bug” 做筛选。

所以优化的思路就是:1、先过滤t1的数据,可以用索引过滤。2、在过滤之后的t1小表和t1做联合查询。

语句即是:

sql2:

select * from (select * from zt_grouppriv where module="bug") t1 left join zt_group t2
on t1.`group` = t2.id

这里有个问题,如果module不是索引或者不是最左索引怎么办,那select * from zt_grouppriv where module=”bug” 是全文检索,所以虽然比sql1快,但也没有”极致”优化。

此时,我们可以1.如果module不是索引,给module设置为单个索引;2.如果module不是最左索引,给select * from zt_grouppriv where module=”bug” 加上最左索引查询,比如最左是group字段:

select * from zt_grouppriv where module=”bug” and `group` is not null,即sql变成:

select * from (select * from zt_grouppriv where module="bug" and `group` is not null ) t1 left join zt_group t2
on t1.`group` = t2.id 

关于sql执行顺序可以查看:https://blog.csdn.net/u011630575/article/details/51002126

情况二:

对于联合查询,如果我们只用到某张表的某一个字段,那么其实我们可以不用写联合查询语句也可以实现联合查询功能。

思路:第一步:查t1表符合要求的数据(可以用到索引);第二步:t1表的数据放到一个集合里,在代码里用for循环这个集合,写sql语句查询t2表,条件是t1返回的结果,查询字段是我们需要的t2的某一个字段。

例如:t1表要查 module=”bug”的数据;t2表要查t2的name字段值,条件是t2的id等于t1的group字段。联合查询:

select t1.*,t2.name from zt_grouppriv t1 left join zt_group t2
on t1.`group` = t2.id
where t1.module = "bug"

按我们的思路修改之后:

1、查询 zt_grouppriv 符合条件的,比如我要查module=”bug”的数据,至于用不用索引,要看具体实现是否使用索引更快速。

2、

for(zt_grouppriv 返回的数据集合,比如是list){
            select name from zt_group t2 where t2.id = list.getId();
            grouppriv.setName(name);
}

grouppriv是我们代码里的类,它的属性就是t1的某些字段和t2的name

具体如何修改,是不是要用索引,是不是要组合查询等等等等,一定要按实际情况来,因为索引不是一定可以优化的,索引也会增加开销。

数据库优化是一个非常大的领域,没办法出几个固定公式说套用就可以,

所以一定要按实际情况为主,做完优化之后,要测试完全,适用的才是好用的。

三、引擎

最后我们补充一下数据库引擎,它一开始是在建表的时候就确定的,所以一般我们做优化,不会涉及修改引擎,除非要新建一个表,那么我们就要关注,用什么引擎,对你的crud更友好。

mysql有InnoDB和MyISAM 两种引擎。

MyISAM 不支持事务,所以它主要应用于查询插入比较多,修改比较少,或者对数据修改要求不高的库。比如文章报道这种库,或者我们例子中用的禅道。

InnoDB支持事务,但是它查询不如MyISAM。

MyISAM是表级锁,即我们做修改的时候,哪怕修改一条数据,它也会锁住整个表,别人就不能修改了:

InnoDB是行级锁,只锁你正在修改的数据:

表锁行锁也说明了,MyISAM应用于查询插入比较多,修改比较少的库。InnoDB适用用修改比较多,对事务,对数据修改要求比较高的库,比如涉及交易、金钱这种的库。

但同样的InnoDB查询不如MyISAM快,我们用实例来说明:

zentao库的zt_grouppriv 和 zt_group 两个表和 xzs库的zt_grouppriv 和 zt_group 两个表字段数据完全一样,

但是zentao库的zt_grouppriv 和 zt_group用MyISAM引擎,

xzs库的zt_grouppriv 和 zt_group用InnoDB引擎。

我们分别看下查询速度:

zentao库,zt_grouppriv 和 zt_group用MyISAM引擎:

xzs库,zt_grouppriv 和 zt_group用InnoDB引擎:

对比还是非常明显的,可以了解到MyISAM引擎 相比较于InnoDB引擎,查询速度更快。

至于更底层的数据结构,需要花更多的时间精力学习,还是一句话:看是否符合你的职业规划~~

毕竟人生最重要的必修课就是知道自己所往


版权声明:本文为weixin_40459875原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。