江苏龙网

  1. 首页 > 资讯 > 科技 > >

运维派官方|面试官邪魅一笑：MySQL千万级别大表，你要如何优化？


当MySQL单表记录数过大时 ， 增删改查性能都会急剧下降 ， 可以参考以下步骤来优化：
单表优化
除非单表数据未来会一直不断上涨 ， 否则不要一开始就考虑拆分 ， 拆分会带来逻辑、部署、运维的各种复杂度 ， 一般以整型值为主的表在千万级以下 ， 字符串为主的表在五百万以下是没有太大问题的 。 而事实上很多时候MySQL单表的性能依然有不少优化空间 ， 甚至能正常支撑千万级以上的数据量：
字段
?尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT ， 如果非负则加上UNSIGNED ?VARCHAR的长度只分配真正需要的空间 ?使用枚举或整数代替字符串类型 ?尽量使用TIMESTAMP而非DATETIME ，?单表不要有太多字段 ， 建议在20以内 ?避免使用NULL字段 ， 很难查询优化且占用额外索引空间 ?用整型来存IP
索引
?索引并不是越多越好 ， 要根据查询有针对性的创建 ， 考虑在WHERE和ORDER BY命令上涉及的列建立索引 ， 可根据EXPLAIN来查看是否用了索引还是全表扫描 ?应尽量避免在WHERE子句中对字段进行NULL值判断 ， 否则将导致引擎放弃使用索引而进行全表扫描 ?值分布很稀少的字段不适合建索引 ， 例如"性别"这种只有两三个值的字段 ?字符字段只建前缀索引 ?字符字段最好不要做主键 ?不用外键 ， 由程序保证约束 ?尽量不用UNIQUE ， 由程序保证约束 ?使用多列索引时主意顺序和查询条件保持一致 ， 同时删除不必要的单列索引
查询SQL
?可通过开启慢查询日志来找出较慢的SQL ?不做列运算：SELECT id WHERE age + 1 = 10 ， 任何对列的操作都将导致表扫描 ， 它包括数据库教程函数、计算表达式等等 ， 查询时要尽可能将操作移至等号右边 ?sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句 ， 减少锁时间；一条大sql可以堵死整个库 ?不用`SELECT *`` ?OR改写成IN：OR的效率是n级别 ， IN的效率是log(n)级别 ， in的个数建议控制在200以内 ?不用函数和触发器 ， 在应用程序实现 ?避免%xxx式查询 ?少用JOIN ?使用同类型进行比较 ， 比如用'123'和'123'比 ， 123和123比 ?尽量避免在WHERE子句中使用!=或
explain partitionsselectcount(1)fromuser_partitionwhereidin(1,2,3,4,5);+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+|1|SIMPLE|user_partition|p1,p4|range|PRIMARY|PRIMARY|8|NULL|5|Usingwhere;Usingindex|+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+1rowinset(0.00sec)
【运维派官方|面试官邪魅一笑：MySQL千万级别大表，你要如何优化？】分区的好处是：
?可以让单表存储更多的数据 ?分区表的数据更容易维护 ， 可以通过清楚整个分区批量删除大量数据 ， 也可以增加新的分区来支持新插入的数据 。 另外 ， 还可以对一个独立分区进行优化、检查、修复等操作 ?部分查询能够从查询条件确定只落在少数分区上 ， 速度会很快 ?分区表的数据还可以分布在不同的物理设备上 ， 从而搞笑利用多个硬件设备 ?可以使用分区表赖避免某些特殊瓶颈 ， 例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争 ?可以备份和恢复单个分区
分区的限制和缺点：
?一个表最多只能有1024个分区 ?如果分区字段中有主键或者唯一索引的列 ， 那么所有主键列和唯一索引列都必须包含进来 ?分区表无法使用外键约束 ?NULL值会使分区过滤无效 ?所有分区必须使用相同的存储引擎


推荐阅读


上一篇：环球Tech|一图看懂微信支付分有啥用

下一篇：太平洋电脑网|生活在于折腾 尼康Z 50另类玩法探索微距世界