Mysql数据库学习备忘

eumnq8

一、推荐命名规范
1. 数据库命名
一般游戏开发会有两个数据库：主数据库和日志数据库，这部分运维会直接创建好。
主数据库：  存放游戏的所有操作数据。
命名规则：服务前缀+游戏ID+db
日志数据库：存放游戏中产生的日志数据。
命名规则：服务前缀+游戏ID+logdb
2. 其他命名规则
Mysql不区分大小写，所以命名规则一般建议小写，下划线风格
表名tbl_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx，存储过程usp_xxx, 函数 ufn_XXX, 长度不超过 30 个字符
字段命名如user_id、create_date
存储过程参数名命名建议p_XXX，注意不要与字段名相同

二、设计规范
1. 遵守 3NF 标准 3NF 规定
第一范式(1NF)：字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式);
例如：姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段;

第二范式(2NF)：一个表必须有主键,即每行数据都能被唯一的区分;
备注：必须先满足第一范式;

第三范式(3NF)：一个表中不能包含其他相关表中非关键字段的信息,即数据表不能有冗余字段;
备注：必须先满足第二范式;

备注：往往我们在设计表中不能遵守第三范式,因为合理的冗余字段将会给我们减少join的查询;
例如：相册表中会添加图片的点击数字段,在相册图片表中也会添加图片的点击数字段;

2. 小心保留字
要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突。避免使用如 order，这类的关键字做为字段名，保留字参考附录四

3. 字段类型与索引注意点
        3.1 尽量采用数字类型作为字段类型
        因为数字类型相对其他字段类型，在查询性能和存储效率上更具优势。如 create_date 就可以采用类似 20180319 这样的 int 型的数据进行存储。
         
        3.2尽量不要使用TEXT、BLOB类型
        会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

        3.3常见类型（int，bigint，varchar，timestamp）参考附录五，数据库的编码使用utf8mb4

        3.4字段定义为NOT NULL并且提供默认值

        3.5游戏开发中禁止使用小数存储玩家数据
        小数在多次计算后然后失去精度，容易导致数据对不上，引起玩家反馈

        3.6建议使用varchar(20)存储手机号

        3.7 任何sql语句都要注意增加相应的索引，索引的创建可以参考附录二，在一个功能上线时，要做下模拟外网的数据量的测试，来执行explain参看sql相关性能

三、设计技巧
1、核心原则
不在数据库做运算，cpu计算务必移至业务层;
控制列数量(字段少而精,字段数建议在20以内);
平衡范式与冗余(效率优先；往往牺牲范式)
拒绝3B(拒绝大sql语句：big sql、拒绝大事务：big transaction、拒绝大批量：big batch);

2、字段类原则
用好数值类型(用合适的字段类型节约空间);
字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);
避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);
少用text类型(尽量使用varchar代替text字段);

游客，如果您要查看本帖隐藏内容请回复

       
六、Mysql保留字
ADD        ALL        ALTER
ANALYZE        AND        AS
ASC        ASENSITIVE        BEFORE
BETWEEN        BIGINT        BINARY
BLOB        BOTH        BY
CALL        CASCADE        CASE
CHANGE        CHAR        CHARACTER
CHECK        COLLATE        COLUMN
CONDITION        CONNECTION        CONSTRAINT
CONTINUE        CONVERT        CREATE
CROSS        CURRENT_DATE        CURRENT_TIME
CURRENT_TIMESTAMP        CURRENT_USER        CURSOR
DATABASE        DATABASES        DAY_HOUR
DAY_MICROSECOND        DAY_MINUTE        DAY_SECOND
DEC        DECIMAL        DECLARE
DEFAULT        DELAYED        DELETE
DESC        DESCRIBE        DETERMINISTIC
DISTINCT        DISTINCTROW        DIV
DOUBLE        DROP        DUAL
EACH        ELSE        ELSEIF
ENCLOSED        ESCAPED        EXISTS
EXIT        EXPLAIN        FALSE
FETCH        FLOAT        FLOAT4
FLOAT8        FOR        FORCE
FOREIGN        FROM        FULLTEXT
GOTO        GRANT        GROUP
HAVING        HIGH_PRIORITY        HOUR_MICROSECOND
HOUR_MINUTE        HOUR_SECOND        IF
IGNORE        IN        INDEX
INFILE        INNER        INOUT
INSENSITIVE        INSERT        INT
INT1        INT2        INT3
INT4        INT8        INTEGER
INTERVAL        INTO        IS
ITERATE        JOIN        KEY
KEYS        KILL        LABEL
LEADING        LEAVE        LEFT
LIKE        LIMIT        LINEAR
LINES        LOAD        LOCALTIME
LOCALTIMESTAMP        LOCK        LONG
LONGBLOB        LONGTEXT        LOOP
LOW_PRIORITY        MATCH        MEDIUMBLOB
MEDIUMINT        MEDIUMTEXT        MIDDLEINT
MINUTE_MICROSECOND        MINUTE_SECOND        MOD
MODIFIES        NATURAL        NOT
NO_WRITE_TO_BINLOG        NULL        NUMERIC
ON        OPTIMIZE        OPTION
OPTIONALLY        OR        ORDER
OUT        OUTER        OUTFILE
PRECISION        PRIMARY        PROCEDURE
PURGE        RAID0        RANGE
READ        READS        REAL
REFERENCES        REGEXP        RELEASE
RENAME        REPEAT        REPLACE
REQUIRE        RESTRICT        RETURN
REVOKE        RIGHT        RLIKE
SCHEMA        SCHEMAS        SECOND_MICROSECOND
SELECT        SENSITIVE        SEPARATOR
SET        SHOW        SMALLINT
SPATIAL        SPECIFIC        SQL
SQLEXCEPTION        SQLSTATE        SQLWARNING
SQL_BIG_RESULT        SQL_CALC_FOUND_ROWS        SQL_SMALL_RESULT
SSL        STARTING        STRAIGHT_JOIN
TABLE        TERMINATED        THEN
TINYBLOB        TINYINT        TINYTEXT
TO        TRAILING        TRIGGER
TRUE        UNDO        UNION
UNIQUE        UNLOCK        UNSIGNED
UPDATE        USAGE        USE
USING        UTC_DATE        UTC_TIME
UTC_TIMESTAMP        VALUES        VARBINARY
VARCHAR        VARCHARACTER        VARYING
WHEN        WHERE        WHILE
WITH        WRITE        X509
XOR        YEAR_MONTH        ZEROFILL

七、MYSQL常用类型
类型            默认值        取值范围
int                0            有符号值：-2147683648 到2147683647（- 2^31 到2^31- 1）
无符号值：  0       到4294967295（0 到2^32 – 1）
bigint            0            有符号值：-9223372036854775808 到9223373036854775807（- 2^63到2^63-1）
无符号值：  0       到18446744073709551615（0到2^64 – 1）
varchar            ""            可变长度，最多不超过255字节，如在创建时指定VARCHAR（n），则可存储0~n个字符的变长串
timestamp        NOW()        YYYYMMDD HHMMSS  混合日期和时间，可作时间戳