MySQL使用技巧

1.将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。 有利于有效利用缓存,防⽌止读入无用的冷数据,较少磁盘IO,同时保证热数据常驻内存提⾼高缓存命中率。

2.禁止在数据库中存储明文密码。

采用加密字符串存储密码,并保证密码不可解密,同时采用随机字符串加盐保证密码安全。

3.表必须有主键,推荐使用UNSIGNED自增列作为主键。

表没有主键,INNODB会默认设置隐藏的主键列;没有主键的表在定位数据行的时候非常困难,也会降低基于行复制的效率。

4.禁止冗余索引。

索引是双刃剑,会增加维护负担,增⼤大IO压力。(a,b,c)、(a,b),后者为冗余索引。可以利用前缀索引来达到加速目的,减轻维护负担。

5.禁止重复索引。

primary key a;uniq index a;

重复索引增加维护负担、占用磁盘空间,同时没有任何益处。

6.不在低基数列上建立索引,例如“性别”。

大部分场景下,低基数列上建立索引的精确查找,相对于不建立索引的全表扫描没有任何优势,而且增大了IO负担。

7.合理使用覆盖索引减少IO,避免排序。

 覆盖索引能从索引中获取需要的所有字段,从⽽而避免回表进行二次查找,节省IO。

INNODB

存储引擎中,secondary index(非主键索引,又称为辅助索引、二级索引)没有直接存储行地址,而是存储主键值。

如果用户需要查询secondary index中所不包含的数据列,则需要先通过secondary

index查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次。覆盖索引则可以在⼀一个索引中获取所有需要的数据,因此效率较高。例如SELECT email,uid FROM user_email

WHERE uid=xx,如果uid不是主键,适当时候可以将索引添加为index(uid,email),以获得性能提升。

8.用IN代替OR。SQL语句中IN包含的值不应过多,应少于1000个。 

IN

是范围查找,MySQL内部会对IN的列表值进行排序后查找,比OR效率更高。

9.表字符集使用UTF8,必要时可申请使用UTF8MB4字符集。 

a)UTF8字符集存储汉字占用3个字节,存储英文字符占用一个字节。

b)UTF8统一而且通用,不会出现转码出现乱码风险。

c)如果遇到EMOJ等表情符号的存储需求,可申请使用UTF8MB4字符集。

10.用UNION

ALL代替UNION。

UNION ALL不需要对结果集再进行排序。


11.禁止使用order by rand()。

order by rand()会为表增加一个伪列,然后用rand()函数为每一行数据计算出rand()值,然后基于该行排序,这通常都会生成磁盘上的临时表,因此效率非常低。建议先使用rand()函数获得随机的主键值,然后通过主键获取数据。

12.建议使用合理的分页方式以提高分页效率。

假如有类似下面分页语句:

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10;

这种分页方式会导致大量的io,因为MySQL使用的是提前读取策略。推荐分页方式:

SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC

LIMIT 10.SELECT * FROM table inner JOIN (SELECT id FROM table

ORDER BY TIME LIMIT 10000,10) as tUSING(id)

13.SELECT只获取必要的字段,禁止使用SELECT * 

减少网络带宽消耗;能有效利用覆盖索引;表结构变更对程序基本无影响。

14.SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。

语句级复制场景下,引起主从数据不一致;不确定值的函数,产⽣生的SQL语句无法利用QUERY CACHE。

15.采用合适的分库分表策略。例如千库十表、十库百表等。

采用合适的分库分表策略,有利于业务发展后期快速对数据库进行水平拆分,同时分库可以有效利⽤用MySQL的多线程复制特性。

16.减少与数据库交互次数,尽量采用批量SQL语句。

使用下面的语句来减少和db的交互次数:

a)INSERT ... ON DUPLICATE KEY UPDATEb)REPLACE INTOc)INSERT IGNORE d)INSERT INTOVALUES()

17.拆分复杂SQL为多个小SQL,避免大事务。

简单的SQL容易使⽤用到MySQL的QUERY CACHE;减少锁表时间特别是MyISAM;可以使用多核 CPU。

18.对同一个表的多次alter操作必须合并为一次操作。

mysql对表的修改绝大部分操作都需要锁表并重建表,而锁表则会对线上业务造成影响。为减少这种影响,必须把对表的多次alter操作合并为一次操作。例如,要给表t增加一个字段b,同时给已有的字段aa建立索引,通常的做法分为两步:

alter table t add column b varchar(10);

然后增加索引:

alter table t add index idx_aa(aa);

正确的做法是:

alter table t add column b varchar(10),add index idx_aa(aa);

19.避免使用存储过程、触发器、视图、自定义函数等。

这些高级特性有性能问题,以及未知BUG较多。业务逻辑放到数据库会造成数据库的DDL、SCALE OUT、SHARDING等变得更加困难。

20.禁止有super权限的应用程序账号存在。

安全第一。super权限会导致read only失效,导致较多诡异问题而且很难追踪。

21.不要在MySQL数据库中存放业务逻辑。

 数据库是有状态的服务,变更复杂而且速度慢,如果把业务逻辑放到数据库中,将会限制业务的快速发展。建议把业务逻辑提前,放到前端或中间逻辑层,而把数据库作为存储层,实现逻辑与存储的分离。

小编分类整理了许多java进阶学习材料和BAT面试题,需要资料的请转发此文章后再私聊小编回复【java】就能领取2019年java进阶学习资料和BAT面试题以及《Effective Java》(第3版)电子版书籍。

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 216,843评论 6 502
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 92,538评论 3 392
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 163,187评论 0 353
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 58,264评论 1 292
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 67,289评论 6 390
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 51,231评论 1 299
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 40,116评论 3 418
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,945评论 0 275
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,367评论 1 313
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,581评论 2 333
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,754评论 1 348
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,458评论 5 344
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 41,068评论 3 327
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,692评论 0 22
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,842评论 1 269
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,797评论 2 369
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,654评论 2 354

推荐阅读更多精彩内容