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，easy造成主从不一致

不用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倍。