【原创】MySQL优化建议

仅个人总结,欢迎一起交流!

MySQL优化的本质:

1. 结合MySQL数据库的主要特性(数据存储与数据查询),使得数据存储占用空间更小,更新、查询速度更快,并发程度更高,使得服务器资源利用率更高。

2. 尤其是对于大数据量、高并发的业务场景,避免因不合理的设计拖垮数据库,造成数据库宕机、数据丢失、业务无法正常进行等问题,保证服务的可用性。

以下为本人总结的部分优化建议:

选择InnoDB引擎。InnoDB引擎支持事务、行级锁、并发性能更好,CPU及内存缓存页优化使得资源利用率更高。

使用UTF8字符集,UTF-8MB4。无需转码,不存在乱码的风险,更节省空间。

减少存储过程、视图、触发器等使用,尽量将计算逻辑设计运用至业务代码层面,避免因耗时计算影响到数据库的并发,严重时拖垮数据库。

使用自增主键。Innodb是基于B+树的索引组织表,使用自增主键,数据行写入可以提高插入性能,可以避免页分裂,减少表碎片提升空间和内存的使用。

禁止使用外键,外键导致表和表之间的耦合。更新和删除操作涉及关联的表,极大地影响sql的性能,甚至可能造成死锁。

禁止使用TEXT、BLOB类型。更多的磁盘和内存空间将被浪费,且大字段查询将会清理掉热点数据的缓存,导致缓存命中率急剧下降并影响数据库性能。如有必要,创建另一个表记录,将热点数据与大字段数据分离。

适当的建立索引,避免全表扫描。

不要过多的建立索引,建议控制在5个以内。索引的本质是对数据顺序的记录,占用存储空间,不宜过多。可使用EXPLAIN来查看了解索引命中情况。

索引的建立原则:为常用于where或orderby语句后的字段建立索引,但对于数据值变化少(区分度不大)的情况不适用,如“性别”字段,一般值为“男/女/未知”等,建立这种索引并不能加快查询速度,却浪费了磁盘空间。

更新十分频繁的字段上不宜建立索引。更新操作会变更B+树,重建索引,这个过程十分耗性能。

联合索引的建立要满足“最左匹配”原则,将命中率更高的字段建立在最前面。

列值设置默认值,禁止null值,以防破坏索引,影响查询效率。

不在where语句后使用函数或者表达式计算,会造成索引失效。

不使用NOT IN和<>操作,%开头的模糊查询,避免全表扫描;

避免使用属性隐式转换。如phone='123456789’为字符串不写成phone=123456789。

尽量建立覆盖索引,避免回表查询。

唯一/普通索引的选择。唯一索引需要唯一性检查,不能利用 change buffer特性。需要频繁从磁盘读取数据,涉及随机 IO 的访问,效率变低。所以能使用普通索引的场景尽量不使用唯一索引。

更新条件列要使用索引,避免无法通过索引键加锁而升级为表级锁。

varchar字段过长时考虑使用前缀索引。

禁止对大型表使用JOIN查询或子查询。会生成临时表,消耗内存和CPU,极大地影响数据库性能。关联查询可在业务代码层拆分为多次单表查询。

避免存储大文件。大文件可存至专门的文件系统,如OSS。

根据业务需要合理地确定字段类型与长度,避免空间浪费。

大事务尽量拆成多个小事务,减少锁记录和时间。

最长事务代码块尽量放在程序的最后执行。

尽量减少基于范围的数据检索过滤条件更新,避免间隙锁带来的影响而锁定了不该锁定的记录。

注意间隙锁,批量更新&删除操作尽量避免并发,以免造成意外的死锁。

数据量大的业务系统,考虑分库分表,按业务系统的划分垂直分库,单表数据量过大考虑分表(水平/垂直分表)

最后,送大家一份免费MySQL学习资料,立即前往>>>学无止境,愿大家都能够少走弯路。

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

推荐阅读更多精彩内容