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”