1 字段类型设计
- 表字段避免null值出现,null值很难查询优化且占用额外的索引空间,推荐默认数字0代替null。
- 尽量使用INT而非BIGINT,如果非负则加上UNSIGNED(这样数值容量会扩大一倍),当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
- 使用枚举或整数代替字符串类型
- 尽量使用TIMESTAMP而非DATETIME
- 单表不要有太多字段,建议在20以内
- 用整型来存IP
- 使用可存下数据的最小的数据类型,整型 < date,time < char,varchar < blob
- 使用简单的数据类型,整型比字符处理开销更小,因为字符串的比较更复杂。如,int类型存储时间类型,bigint类型转ip函数
- 使用合理的字段属性长度,固定长度的表会更快。使用enum、char而不是varchar
- 尽可能使用not null定义字段
- 尽量少用text,非用不可最好分表
2 索引设计
索引并不是越多越好,要根据查询有针对性的创建,考虑在
WHERE
和
ORDER BY
命令上涉及的列建立索引,可根据
EXPLAIN来查看是否用了索引还是全表扫描
应尽量避免在
WHERE子句中对字段进行
NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描
值分布很稀少的字段不适合建索引,例如
"性别"这种只有两三个值的字段
字符字段只建前缀索引
字符字段最好不要做主键
不用外键,由程序保证约束
尽量不用
UNIQUE,由程序保证约束
使用多列索引时主意顺序和查询条件保持一致,同时删除不必要的单列索引
- 查询频繁的列,在where,group by,order by,on从句中出现的列
- where条件中<,<=,=,>,>=,between,in,以及like 字符串+通配符(%)出现的列
- 长度小的列,索引字段越小越好,因为数据库的存储单位是页,一页中能存下的数据越多越好
- 离散度大(不同的值多)的列,放在联合索引前面。查看离散度,通过统计不同的列值来实现,count越大,离散程度越高
3 SQL语句编写
使用
limit
对查询结果的记录进行限定
避免
select*
,将需要查找的字段列出来
使用连接(
join)来代替子查询
拆分大的
delete或
insert语句
可通过开启慢查询日志来找出较慢的
SQL不做列运算:
SELECTage +
1=
10,任何对列的操作都将导致表扫描,它包括数据库教程函数、计算表达式等等,查询时要尽可能将操作移至等号右边
- sql
语句尽可能简单:一条
sql只能在一个
cpu
运算;大语句拆小语句,减少锁时间;一条大
sql可以堵死整个库
- OR
改写成
IN:
OR的效率是
n
级别,
IN的效率是
log(n)
级别,
in的个数建议控制在
200以内
不用函数和触发器,在应用程序实现
避免
%xxx
式查询
少用
JOIN使用同类型进行比较,比如用
'123'和
'123'比,
123和
123比
尽量避免在
WHERE子句中使用
!=
或
<>
操作符,否则将引擎放弃使用索引而进行全表扫描
对于连续数值,使用
BETWEEN不用
IN:
SELECTt
WHERE列表数据不要拿全表,要使用
LIMIT来分页,每页数量也不要太大
4 MySQL数据库引擎
4.1 数据库引擎MyISAM
MyISAM引擎是
MySQL 5
.1及之前版本的默认引擎,它的特点是:
不支持行锁,读取时对需要读到的所有表加锁,写入时则对表加排它锁
不支持事务
不支持外键
不支持崩溃后的安全恢复
在表有读取查询的同时,支持往表中插入新纪录
支持
BLOB和
TEXT的前
500
个字符索引,支持全文索引
支持延迟更新索引,极大提升写入性能
对于不会进行修改的表,支持压缩表,极大减少磁盘空间占用
4.2 数据库引擎InnoDB
InnoDB在
MySQL 5
.5后成为默认索引,它的特点是:
支持行锁,采用
MVCC来支持高并发
支持事务
支持外键
支持崩溃后的安全恢复
不支持全文索引
5 MySQL数据库表分区
MySQL在5.1版引入的分区是一种简单的水平拆分,用户需要在建表的时候加上分区参数,对应用是透明的无需修改代码。
对用户来说,分区表是一个独立的逻辑表,但是底层由多个物理子表组成,实现分区的代码实际上是通过对一组底层表的对象封装,但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义,没有全局索引。
用户的SQL语句是需要针对分区表做优化,SQL条件中要带上分区条件的列,从而使查询定位到少量的分区上,否则就会扫描全部分区,可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上,从而进行SQL优化。
5.1 优势
可以让单表存储更多的数据
分区表的数据更容易维护,可以通过清楚整个分区批量删除大量数据,也可以增加新的分区来支持新插入的数据。另外,还可以对一个独立分区进行优化、检查、修复等操作
部分查询能够从查询条件确定只落在少数分区上,速度会很快
分区表的数据还可以分布在不同的物理设备上,从而搞笑利用多个硬件设备
可以使用分区表赖避免某些特殊瓶颈,例如
InnoDB
单个索引的互斥访问、
ext3
文件系统的
inode
锁竞争
可以备份和恢复单个分区
5.2 缺点和限制
一个表最多只能有
1024个分区
如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来
分区表无法使用外键约束
- NULL
值会使分区过滤无效
所有分区必须使用相同的存储引擎
5.3 分区类型
- RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区
- LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
- HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
- KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值
6 其他兼容MySQL数据库类型
6.1 开源数据库
tiDB
https://github.com/pingcap/tidb
Cubrid
https://www.cubrid.org/
开源数据库会带来大量的运维成本且其工业品质和MySQL尚有差距,有很多坑要踩,如果你公司要求必须自建数据库,那么选择该类型产品。
6.2 云数据库
6.2.1 阿里云POLARDB
https://www.aliyun.com/product/polardb?spm=a2c4g.11174283.cloudEssentials.47.7a984b5cS7h4wH
官方介绍语:POLARDB 是阿里云自研的下一代关系型分布式云原生数据库,
100%
兼容MySQL,存储容量最高可达
100T
,性能最高提升至 MySQL 的
6
倍。POLARDB 既融合了商业数据库稳定、可靠、高性能的特征,又具有开源数据库简单、可扩展、持续迭代的优势,而成本只需商用数据库的
1/
10。
6.2.2 阿里云OcenanBase
公测中
6.2.3 阿里云HybridDB for MySQL (原PetaData)
https://www.aliyun.com/product/petadata?spm=a2c4g.11174283.cloudEssentials.54.7a984b5cS7h4wH
官方介绍:云数据库HybridDB
for MySQL
(原名PetaData)是同时支持海量数据在线事务(OLTP)和在线分析(OLAP)的HTAP(Hybrid Transaction/Analytical Processing)关系型数据库。
6.2.4 腾讯云DCDB
https://cloud.tencent.com/product/dcdb_for_tdsql
官方介绍:DCDB又名TDSQL,一种兼容MySQL协议和语法,支持自动水平拆分的高性能分布式数据库——即业务显示为完整的逻辑表,数据却均匀的拆分到多个分片中;每个分片默认采用主备架构,提供灾备、恢复、监控、不停机扩容等全套解决方案,适用于TB或PB级的海量数据场景。