mysql数据库优化的一些个人见解

建表原则:

1、定长与变长分离

定长字段如 int char time等类型,变长字段如varchar,text,blob等类型。变长字段适合单放一张表,用主键与核心表关联

2、常用字段和不常用字段分离

需要结合具体的业务来分析,根据字段的查询场景,将查询频度低的自动拆分出来。

3、在1对多需要关联统计的字段上,添加冗余字段

例如论坛需要显示用户的总发帖数,不要每次都去count帖子表,而是在用户表添加一个字段记录总发帖数,当发帖的时候对该字段做+1即可

 

字段类型选择:

1、字段类型优先级排序,整型>date,time>enum,char>varchar>blob,text ,定长类型优于变长类型,整型类型优于字符类型,例如性别字段可以用1、2来区分,而不直接用男、女。

2、字段长度够用就行,大的字段浪费内存,影响速度。例如年龄字段用tinyint unsigned not null可以存储255岁,没必要用int类型浪费字节。

3、尽量避免使用null。null不利于查询,要用特殊的字节来标注。

 

索引优化策略:

mysql索引主要分为B-tree索引和hash索引,而我们常用的InnoDB和MyISAM引擎都使用B-tree索引。

B-tree索引又可分为聚簇索引和非聚簇索引。InnoDB使用的是聚簇索引(所有数据也保存在叶子节点上),MyISAM使用非聚簇索引(只保存索引字段数据)。

建索引的字段一般遵循以下原则:

1、查询频繁

2、区分度高

3、长度小

4、尽量能覆盖常用查询字段

 

区分度高和长度小一定程度上相互影响,一般长度小会造成区分度低,区分度高的一般长度大,所以关于区分度和长度我们要找个比较合适的结合点。如下图的例子,当长度为 4、5、6的时候区分度接近1,是比较合适的点。

对于左前缀区分度不高的情况,例如下图中的link_url 左边基本上都是相同的“http://” 。这样区分度极低的左前缀不利于建立索引。

这里提供两个解决左前缀区分度不高的方法:

1、把列的内容倒叙存储

2、建立伪hash索引,及添加一个新字段,将crc32(link_url)存入新字段,然后在新字段建立索引

对于多列索引的建立,需要根据具体的业务去考虑多列索引字段的顺序

索引不单可以加快查询的速度,还可以加快排序和分值的速度。对于聚簇索引(InnoDB)可以充分利用B-tree遍历的排序特点,让一些查询到的数据本身就排好了顺序。

在长期的数据更改过程中,索引文件和数据文件都将产生空洞形成碎片,我们可以通过执行nop操作(不产生对数据实质影响的操作)来修改表,例如:表的引擎为InnoDB,我们可以执行alter table xxx engine innodb 。

此外我们也可以使用“optimize table 表名”来修复表。但需要注意的是,修复表时会对数据进行重新整理,如果表比较大,修复会比较耗资源,所有修复操作不因太频繁。可以根据表update的操作频率去按周、月或者更长的周期来进行修复。

 

Sql语句优化

sql的优化主要用于提高sql语句的执行时间,而执行时间主要体现在查数据和取数据上面。

如何才能执行的快?

1)查的快,联合索引的顺序、区分度、长度、

2)取得快,索引覆盖

3)传输少,更少的行和列

sql语句的优化思路?

不查–>少查–>高效的查

不查:通过业务逻辑来计算。

少查:尽量去精准数据,少取行和列

高效的查:尽量走索引,利用索引优化

善于利用“explain sql语句” 来分析sql语句执行的情况,然后按实际情况进行优化。

当查询的字段包含text、mediuntext等长文本类型时,排序将发生在磁盘上,执行速度将非常慢,因此我们需避免此类查询。

limit分页优化

在进行limit offset,num 查询的时候,随着offset的不断增大查询速度会明显增大。例如这个查询“select * from test limit 1000000,10;”

1、从业务逻辑上解决,例如分页的时候只显示前100页,因为没什么人会那么无聊去翻100页以后的数据

2、不用offset,用条件查询代替,例如使用这个查询:“select * from test where id>1000000 limit 0,10;”

3、如果1 、2都不能用的情况下,可以利用索引先查出数据的主键再进行连接查询。例如:“select * from test inner join (select id from test limit 1000000,10) as tmp on test.id=tmp.id”