MySQL小技巧

简介篇

存储引擎

MyISAM是MySQL5.1及之前的版本的默认存储引擎。MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但是MyISAM不支持事务和行级锁,而且有一个毫无疑问的缺陷就是崩溃后无法安全回复。

MyISAM会将表存储在两个文件中:数据文件和索引文件,分别以.MYD和.MYI为扩展名。

InnoDB表是基于聚簇索引建立的。

推荐InnoDB存储引擎

数据类型

MySQL支持的数据类型非常多,总体上分为:数字、字符、日期、JSON。选择正确的数据类型对于获得高性能至关重要。不管存储那种类型的数据,下面几个简单的原则都有助于作出更好的选择。

1)更小的通常更好

一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为他们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。

2)简单就好

简单的数据类型的操作通常需要更少的CPU周期。例如,整型比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。

3)尽量避免NULL

很多表都包含可为NULL的列,即使应用程序并不需要保存NULL也是如此,这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。

用NULL会浪费存储空间,因为InnoDB需要一个额外的字节存储。

NULL字段的复合索引无效。

如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更负责。当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。

日期类型

DATE类型表示日期的年月日部分,范围在1000-01-01到9999-12-31之间。

DATETIME类型表示日期的年月日时分秒部分,范围在1000-01-01 00:00:00到9999-12-31 23:59:59之间。

TIMESTAMP类型表示日期的年月日时分秒部分,范围在1970-01-01 00:00:01UTC到2038-01-19 03:14:07UTC之间。

可以让DATETIME和TIMESTAMP自动初始化并且随其他字段的修改而自动修改。

例如:

CREATE TABLE aaa (

id bigint(11) NOT NULL AUTO_INCREMENT,

ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

name varchar(20) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

主键

整数通常是主键的最好选择,因为他们很快并且可以使用AUTO_INCREMENT。

如果可能,应该避免使用字符串类型作为主键,因为他们很消耗空间,并且通常比数字类型慢。尤其在MyISAM表里使用字符串作为主键要特别小心。MyISAM默认对字符串使用压缩索引,这会导致查询慢很多。

对于完全“随机”的字符串也许多加注意,例如MD5()、SHA1()、或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内,这回导致INSERT以及一些SELECT语句变得很慢:

因为插入值会随机地写到索引的不通位置,所以是的INSERT语句更慢。这回导致页分裂、磁盘随机访问,以及对于聚簇存储引擎产生聚簇索引碎片。

SELECT语句会变得更慢,因为逻辑上相邻的行会分布在磁盘和内存的不同地方。

随机值导致缓存对所有类型的查询语句效果都很差,因为会使得缓存赖以工作的访问局部性原理失效。如果整个数据集都一样的“热”,那么缓存任何一部分特定数据到内存都没有好处;如果工作集比内存大,缓存将会有很多刷新和不命中。

如果存储UUID值,则应该移除“-”符号;或者更好的做法是,用UNHEX()函数转换UUID值为16字节的数字,并且存储在一个BINARY(16)列中。检索时可以通过HEX()函数来格式化为十六进制格式。

UUID()生成的值与加密散列函数例如SHA1()生成的值有不同的特征:UUID值虽然分布也不均匀,但还是有一定顺序的。尽管如此,还是不如递增的整数好用。

索引

B-Tree索引

B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索。

B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入内存,再在内存中进行查找,最终得到要查找的数据。

限制:

如果不是按照索引的最左列开始查找,则无法使用索引。

不能跳过索引中的列。

如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找。

Hash索引

高性能索引策略:

分区表

分区功能并不是在存储引擎层完成的。分区有水平分区和垂直分区两种,但是MySQL不支持垂直分区。

大多数DBA会有这样一个误区:只要启用了分区,数据库就会变得更快。这个结论是存在很多问题的。其实,分区对于某些SQL语句性能可能会带来提高,但是分区主要用语高可用性,利于数据库的管理。在OLTP应用中,对于分区的使用应该非常小心。如果只是一味使用分区,而不理解分区是如何工作的,也不清楚你的应用如是使用分区,那么分许极有可能只会对性能产生负面的影响。

MySQL数据库支持以下几种分区类型:

RANGE分区:

LIST分区:

HASH分区:

KEY分区:

不论创建哪种类型的分区,如果表中存在主键或者是唯一索引时,分区列必须是唯一索引的一个组成部分。

唯一索引可以是允许NULL值的,并且分区列只要是唯一索引的一个组成部分,不需要整个唯一索引列都是分区列。

当建表时没有指定主键和唯一索引时,可以指定任何一列为分区列。

规范篇

规范存在意义

保证线上数据库schema规范

减少出问题概率

方便自动化管理

规范需要长期坚持,对开发和DBA是一个双赢的事情

核心规范

不在数据库做运算:cpu计算务必移至业务层

控制单表数据量:单表记录控制在1000w

控制列数量:字段数控制在20以内

平衡范式与冗余:为提高效率牺牲范式设计,冗余数据

拒绝3B:拒绝大sql,大事物,大批量

基本命名和约束规范

表字符集选择UTF8 ,如果需要存储emoj表情,需要使用UTF8mb4(MySQL 5.5.3以后支持)

存储引擎使用InnoDB

变长字符串尽量使用varchar varbinary

不在数据库中存储图片、文件等

单表数据量控制在1000w以下

库名、表名、字段名不使用保留字

库名、表名、字段名、索引名使用小写字母,以下划线分割 ,需要见名知意

库表名不要设计过长,尽可能用最少的字符表达出表的用途

索引规范

单个索引字段数不超过5,单表索引数量不超过5,索引设计遵循B+ Tree索引最左前缀匹配原则

选择区分度高的列作为索引

建立的索引能覆盖80%主要的查询,不求全,解决问题的主要矛盾

DML和order by和group by字段要建立合适的索引

不在索引做列运算

避免索引的隐式转换

避免冗余索引

不要用外键

关于索引规范,一定要记住索引这个东西是一把双刃剑,在加速读的同时也引入了很多额外的写入和锁,降低写入能力,这也是为什么要控制索引数原因。之前看到过不少人给表里每个字段都建了索引,其实对查询可能起不到什么作用。

冗余索引例子

idx_abc(a,b,c)

idx_a(a) 冗余

idx_ab(a,b) 冗余

隐式转换例子

字段:remark varchar(50) NOT Null

MySQL>SELECT id, gift_code FROM gift WHERE deal_id = 640 AND remark=115127; 1 row in set (0.14 sec)

MySQL>SELECT id, gift_code FROM pool_gift WHEREdeal_id = 640 AND remark=‘115127’; 1 row in set (0.005 sec)

字段定义为varchar,但传入的值是个int,就会导致全表扫描,要求程序端要做好类型检查

字段规范

所有字段均定义为NOT NULL ,除非你真的想存Null

字段类型在满足需求条件下越小越好,使用UNSIGNED存储非负整数 ,实际使用时候存储负数场景不多

使用datetime存储时间

使用varchar存储变长字符串 ,当然要注意varchar(M)里的M指的是字符数不是字节数;使用UNSIGNED INT存储IPv4 地址而不是CHAR(15) ,这种方式只能存储IPv4,存储不了IPv6

使用DECIMAL存储精确浮点数,用float有的时候会有问题

少用blob text

SQL规范

尽量不使用存储过程、触发器、函数等

避免使用大表的JOIN,MySQL优化器对join优化策略过于简单

避免在数据库中进行数学运算和其他大量计算任务

SQL合并,主要是指的DML时候多个value合并,减少和数据库交互

UPDATE、DELETE语句不使用LIMIT,容易造成主从不一致

不用select *

慎用count(*)

避免负向%

请使用同类型比较

sql语句尽可能简单

一条sql只能在一个cpu运算

大语句拆小语句以减少锁时间

一条大sql可以堵死整个库

简单的事务

事务时间尽可能短

bad case:

上传图片事务

OR改写为IN()

or的效率是n级别

in的消息时log(n)级别

in的个数建议控制在200以内

select id from t where phone=’159′ or phone=’136′;

=>

select id from t where phone in (’159′, ’136′);

OR改写为UNION

mysql的索引合并很弱智

select id from t where phone = ’159′ or name = ‘john’;

=>

select id from t where phone=’159′

union

select id from t where name=’jonh’

limit高效分页

limit越大,效率越低

select id from t limit 10000, 10;

=>

select id from t where id > 10000 limit 10;

使用union all替代union

union有去重开销

使用load data导数据

load data比insert快约20倍;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值