MySQL优化建议

最新推荐文章于 2022-05-05 21:19:11 发布

jazon@

最新推荐文章于 2022-05-05 21:19:11 发布

阅读量256

点赞数 1

分类专栏： MySQL 文章标签： mysql sql 数据库

版权

27 篇文章 1 订阅

订阅专栏

优先选择符合存储需要的最小的数据类型，具体，将IP地址转换成整形数据，对于非负型的数据，优先使用无符号整型来存储，对于非负数据，优先使用无符号整型来存储
避免使用TEXT,BLOB数据类型，最常见的TEXT类型可以存储64k的数据，如果要用，建议把BLOB或TEXT列分离到单独的扩展表中。因为MySQL内存临时表不支持TEXT，BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表。
避免使用ENUM类型，因为枚举类型order by效率低
尽可能把所有列定义为NOT NULL，索引NULL需要额外的空间来保存，所以占用更多的空间。
使用TIMESTAMP或DATETIME来存储时间
同财务相关的金额类数据必须使用decimal类型

限制每张表的上的索引数量，建议单张表索引不超过5个
禁止给表中的每一列都建立单独的索引
每个Innodb表必须有个主键，Innodb如果没有设置主键，那么就会有第一个唯一索引作为主键，如果还没有，就会自动生成一个不可见的row_id作为主键。表是按照主键索引的顺序来组织，所以不要使用UUID等非递增的主键(会不断重新调整页，俗称页分裂)，不要使用更新频繁的列作为主键(意味着要重新调整页)。
常见索引列建议，出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列，包含在ORDER BY、GROUP BY、DISTINCT中的字段，多表join的关联列。
选择性高的放在最左侧，字段长度小的列放在联合索引的最左侧，使用最频繁的列放在联合索引的左侧。
避免冗余索引和重复索引，增加了查询优化器生成执行计划的时间
避免使用数据库外键约束，应该由程序控制，原因:1.每次修改数据，数据库都要去检查另一个表的数据，获取额外的锁，容易死锁。2.性能问题，每次插入数据都需要判断另一张是否存在数据，由程序控制可以省略一些判断。3.扩展性问题，分库分表方便，更换数据库方便，比如从Mysql到Oracle。4.技术问题，DBA往往小公司都没有，减少数据库性能问题，极为重要。

避免数据类型的隐式转换，比如

select name,phone from customer wehre id = '111';
这个语句从字符串转换为数字的话,索引会失效的

充分利用表上已经存在的索引
禁止使用SELECT * 必须使用 SELECT 字段列表查询，
禁止使用不含字段列表的INSERT语句
避免使用子查询，可以把子查询优化为join操作，通常子查询在in子句中，且子查询中为简单SQL时，才可以把子查询转化为关联查询进行优化。子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。子查询产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量慢查询。
避免使用JOIN关联太多的表，MySQL是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。如果join_buffer_size设置不合理，就会造成服务器内存溢出，影响服务器数据库性能的稳定性。一般不建议超过5个。
减少同数据库的交互次数
对应同一列进行or判断时，使用in代替or,or大多数不能利用索引
WHERE从句中禁止对列进行函数转换和计算
在明显不会重复是使用UNION ALL而不是UNION，因为UNION有一个去重操作。
拆分复杂的大SQL为多个小SQL

超100万行的批量写操作，要分批次进行，大批量引起严重的主从延迟，binlog日志为row格式时会产生大量日志，避免大事务操作，大事务会造成表中大批量数据进行锁定，从而导致大量阻塞，阻塞会对MySQL的性能产生非常大的影响，长时间的阻塞，会占满数据库所有的可用连接。
对于大表使用pt-online-schema-change修改表结构，原理是新建一个与原结构相同的新表，然后复制。

关注

专栏目录