文章目录
读深入浅出mysql数据库、优化与维护笔记
mysql下载:https://www.mysql.com/downloads/
mysql在线手册:https://dev.mysql.com/doc/
mysqlbug列表:https://bugs.mysql.com/
mysql最新消息:https://www.mysql.com/news-and-events/newsletter/
DDL:数据定义语言,定义不同数据段,数据库,表,列,索引等数据库对象,常用语句关键字create,drop,alter等。
DML:数据操纵语句,用于添加,修改,删除,更新和查询数据库记录,并检查数据完整性,常用包括insert,delete,update和select等。
DCL:数据控制语句,用于控制不同数据段直接的许可和访问级别的语句,这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要语句包括关键字grant、revoke等。
DDL语句(数据库和表操作)
数据库操作
创建数据库
create database test1;
删除数据库
drop database test1;
查看所有数据库
show databases;
使用数据库
use databases;
表操作
use databases之后才可以操作tables
查找数据库中的表
show tables;
创建表
create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));
查看表
desc emp;
查看创建表命令
show create table emp \G;
删除表
drop table emp;
修改表字段
alter table emp modify ename varchar(20)
添加表字段
alter table emp add column age int(3);
删除表字段
alter table emp drop column age;
修改字段名
alter table emp change age age1 int(10);
修改字段排序顺序
alter table emp add birth date after ename;
修改将字段放到最前面
alter table emp modify age int(3) first;
更改表名
alter table emp rename emp1;
DML语句(表数据操作)
1、插入记录
insert into emp(ename,hiredate,sal,deptno) values('zzxl','2000-01-01','2000',1);
或者不用字段名称
insert into emp values('lisa','2003-02-01','3000',2);
一次性插入多条
insert into dept values(5,'dept'),(6,'dept');
2、更新记录
update emp set sal=4000 where ename='lisa';
mysql中同时更新多个表的数据
update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname = a.ename where a.deptno=b.deptno;
多表语法更多用在根据一个表的字段动态的更新另一个表的字段
3、删除记录
删除记录
delete from emp where ename='dony';
同时删除多表数据
delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;
注意不管单表还是多表,不加where会删除全部记录
4、查询记录
select * from emp;
select ename,hiredate from emp;
查询不重复的记录
select distinct deptno from emp;
条件查询
select * from emp where deptno = 1;
多条件查询
select * from emp where deptno = 1 and sal<3000;
排序和条数限制
select * from emp order by deptno;
select * from emp order by deptno,sal desc;
order by 默认升序排列即asc,当为desc的时候为降序;
限制条数(limit)
select * from emp order by sal limit 3;
限制第一条到第三条
select * from emp order by sal limit 1,3;
聚合(统计公司或部门人数)
select count(1) from emp;
统计各个部门人数
select deptno,count(1) from emp group by deptno;
即统计部门人数,又统计总人数
select deptno,count(1) from emp group by deptno with rollup;
统计人数大于一的部门
select deptno,count(1) from emp group by deptno having count(1) > 1;
统计公司员工薪水
select sum(sal),max(sal),min(sal) from emp;
表连接
内连接
select ename,deptname from emp,dept where emp.deptno=dept.deptno;
外连接
分成左连接和右连接
左连接:包含所有左边表记录甚至是右边表没有的记录;
右连接:包含所有右边表的记录甚至是左边表所没有的记录;
查询emp中所有用户名和所在部门名称
select ename,deptname from emp left join dept on emp.deptno = dept.deptno;
或者
select ename,deptname from dept right join emp on emp.deptno = dept.deptno;
子查询
需要另一个条件的结果
关键字有in、not in、=、!=、exists、not exists等
select * from emp where deptno in(select deptno from dept);
注意:mysql4.1以前版本不支持子查询,需要用表连接实现功能。
表连接很多情况下用于优化子查询
记录联合(纵向联合非横向)
UNION和UNION ALL
区别 UNION将UNION ALL查询数据进行distinct去重复
select deptno from emp
union all
select deptno from dept;
去重复
select deptno from emp
union
select deptno from dept;
DCL语句(权限操作)
管理用户权限
创建一个数据用户zl,具有对sakila数据库中所有表的SELECT/INSERT权限
grant select,insert on sakila.* to 'zl@localhost' identified by '123';
exit;
收回insert权限
revoke insert on sakila.* from 'zl@localhost';
exit;
mysql数据库类型
分成数值类型,字符串类型、日期和时间类型
数值类型
用alter table t1 modify id1 int zerofill;
可以让0填满整个int类型
比如1,使用zerofill后为,00001
但是使用zerofill后会为字段自动添加UNSIGNED属性
UNSIGNED属性的作用:比如int类型时-127-126,范围会编程0-255。
数值自增长创建语句
CREATE TABLE AI(
ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
NAME VARCHAR(100) DEFAULT NULL);
float double decimal的使用
float double是浮点数,decimal定点数(适合精确度较高比如货币)
都不可以使用如float(M,D) M整数位,D小数位。如123.22 中 M=123 D=2
不指定精度
float 和 double由操作系统决定
decimal默认是10
注意指定精度为非标准用法,最好不要用于数据库迁移
bit用于存储二进制字段
创建
CREATE TABLE t2(
id2 BIT(2) DEFAULT NULL;
)
注意:BIT(1~64位)
二进制格式查询使用
select bin(id),hax(id) from t2;
因为插入使用的是二进制,所以需要足够长度
日期类型
CREATE TABLE t(d date,t time,dt datetime);
INSERT INTO t VALUES(now(),now(),now());
SELECT * FROM t;
TIMESTRAMP特性
范围比datetime较小(1970-01-01 08:00:01 - 2038某一天)
每个表只能有一列会有CURRENT_TIMESTRAMP
插入时间会根据时区变化而变化
YEAR类型用于记录年份
比使用date更节省空间
采用不同格式插入日期
CREATE TABLE t6(dt datetime);
INSERT INTO t6 VALUES('2007-9-3 12:10:10');
INSERT INTO t6 VALUES('2007/9/3 12+10+10');
INSERT INTO t6 VALUES('20070903121010');
INSERT INTO t6 VALUES(20070903121010);
SELECT * FROM t6;
字符串类型
char和varchar区别
长度上面差距,varchar会保留字符串末尾的空格,char会删除末尾的空格
binary和varbinary类型
这两个类型包含二进制字符串
CREATE TABLE t(C BINARY(3));
INSERT INTO t SET c='a';
SELECT * ,hex(c),c='a',c='a\0',c='a\0\0' from t;
enum枚举类型(1~65535个成员变量)
1~255一个字节存储
255~65535两个字节存储
CREATE TABLE t(gender enum('M','F'));
INSERT INTO t VALUES('M');
INSERT INTO t VALUES('1');
INSERT INTO t VALUES('f');
INSERT INTO t VALUES('2');
INSERT INTO t VALUES(NULL);
SELECT * FROM t;
特性,不区分大小写,只能取其中一个。
SET类型(1~64)和enum相似
可以存储多个集合
CREATE TABLE t(
c SET ('a','b','c','d')
);
INSERT INTO t VALUES('a,b');
INSERT INTO t VALUES('a,b,a');
INSERT INTO t VALUES('a,d');
INSERT INTO t VALUES('a,c'),('a');
SELECT * FROM t;
MYsql运算符
算术运算符
普通加减乘除和取模
select 0.1+0.3333,0.1-0.3333,0.1*0.3333,1/2,1%2;
select 1/0,1%0;
select 3%2,mod(3,2);
比较运算符
用于比较数字,字符和表达式
SELECT 1=0,1=1,NULL=NULL;
结果:0 , 1 , null
SELECT 1<>0,1<>1,NULL<>NULL;
结果:1 , 0 , null
SELECT 1<=>1,2<=>0,0<=>0,NULL<=>NULL;
结果:1 , 0 , 1
SELECT 'a'<'b','a'<'a','a'<'c',1<2;
结果:1 , 0 , 1
SELECT 'bdf'<='b','b'<='b',0<1;
结果:0 , 1 , 1
SELECT 'a'>'b','abc'>'a',1>0;
结果:0 , 1 , 1
SELECT 'a'>='b','abc'>='a',1>=0;
结果:0 , 1 , 1
SELECT 10 BETWEEN 10 AND 20,9 BETWEEN 10 AND 20;
结果:1 , 0
SELECT 1 IN (1,2,3) , 't' IN ('t','a','b','1','e'),0 in (1,2);
结果:1 , 1 , 0
SELECT 0 IS NULL,NULL IS NULL;
结果:0,1
SELECT 0 IS NOT NULL,NULL IS NOT NULL;
结果:1 , 0
SELECT 123456 LIKE '123%',123456 LIKE '%123%', 123456 LIKE '%321%';
结果:1 , 1 , 0
SELECT 'abcdef' REGEXP 'ab','abcdefg' REGEXP 'k';
结果:1 , 0
逻辑运算符
与,或,非,异或
SELECT (1 AND 1) , (0 AND 1) , (3 AND 1) , (1 AND NULL);
非空和非0为1
结果:1,0,1,NULL
SELECT (1 OR 0), (0 OR 0) , (1 OR NULL),(1 OR 1),(NULL OR NULL);
一个不为0为1,
结果:1,0,1,1,null
SELECT NOT 0,NOT 1,NOT NULL;
结果数为0 返回1
结果:1,0,null
SELECT 1 XOR 1,0 XOR 0, 1 XOR 0 , 0 XOR 1 , NULL XOR 1;
相同为0,不同为1,有NULL为空
结果:0,0,1,1,null
位运算符
select 2&3; 10 & 11 = 10
2
select 2|3; 10 | 11 = 11
3
select 2^3; 10 ^ 11 = 01
1
select ~1; 18446744073709551614
二进制63个1和0;
select 100>>3; 12
0001100100 0000001100
select 100<<3; 800
0001100100 1100100000
运算符的优先级
常用函数
字符串函数
函数 | 功能 |
---|---|
CONCAT(S1,S2,…Sn) | 连接S1到Sn为字符串 |
INSERT(str,x,y,instr) | 将字符串str从第x个位置开始,y个字符长字串替换成instr字串 |
LOWER(str) | 将str所有字符变成小写 |
UPPER(str) | 将str所有字符变成大写 |
LEFT(str,x) | 返回str从左边开始中的第x个字符 |
RIGHT(str,x) | 返回str从右边开始第x个字符 |
LOWER(str) | 将str所有字符变成小写 |
LPAD(str,n,pad) | 用字符串pad对str最左边进行填充,直到n个字符长度为止 |
RPAD(str,n,pad) | 用字符串pad对str最右边进行填充,直到n个字符长度为止 |
LTRIM(str) | 去除字符串左边的空格 |
RTRIM(str) | 去除字符串右边的空格 |
REPEAT(str,x) | 返回重复x次的结果 |
REPLACE(str,a,b) | 用字符串b替换str中所有出现的字符串a |
STRCMP(s1,s2) | 比较字符串s1和s2 |
TRIM(str) | 去掉头尾空格 |
SUBSTRING(str,a,b) | 返回从a位置到b位置的字符串 |
INSERT(str,x,y,instr)
select INSERT('beijing2008you',12,3,'me');
结果 beijing2008me
LEFT(str,x)
select LEFT('beijing2008',7);
结果:beijing
LPAD(str,n,pad)
SELECT LPAD('2008',20,'beijing');
结果:beijingbeijingbe2008
REPEAT(str,x)
SELECT REPEAT('mysql',3);
结果:mysql mysql mysql
STRCMP(s1,s2)
比较ASCII编码大小
SELECT STRCMP('a','b'),STRCMP('b','b'),STRCMP('c','b');
-1 0 1
数值函数
函数 | 功能 |
---|---|
ABS(x) | 返回x的绝对值 |
CEIL(x) | 返回大于x的最小整数 |
FLOOR(x) | 返回小于x的最小整数 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0~1内的随机数 |
ROUND(x,y) | 返回参数x的四舍五入保留y位小数 |
TRUNCATE(x,y) | 返回数字x截断为y位的小数结果 |
SELECT ROUND(1.235,2),TRUNCATE(1.235,2)
结果:1.24 1.23
日期和时间函数
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前时间和日期 |
UNIX_TIMESTAMP(date) | 返回日期date的UNIX的当前时间戳 |
FROM_UNIXTIME() | 返回UNIX时间戳的日期值 |
WEEK(date) | 返回日期date为一年的第几周 |
YEAR(date) | 返回日期date的年份 |
HOUR(time) | 返回time的小时值 |
MINUTE(time) | 返回time的分钟值 |
MONTHNAME(date) | 返回date的月份名 |
DATE_FORMAT(date,fmt) | 返回按字符串fmt格式化日期date值 |
DATE_ADD(date,INTERVAL expr type) | 返回一个日期或时间值加上一个时间间隔的时间值 |
DATEDIFF(expr,expr2) | 返回起始时间expr和结束时间expr2之间的次数 |
SELECT UNIX_TIMESTAMP(now());
1567436137
SELECT FROM_UNIXTIME(1567436073);
2019-09-02 22:54:33
SELECT DATE_FORMAT(now(),'%M,%D,%Y');
September,2nd,2019
SELECT DATEDIFF('2008-08-08',NOW());
-4042
SELECT NOW() current,DATE_ADD(now(),INTERVAL 31 DAY) after31days,DATE_ADD(now(),INTERVAL '1_2' YEAR_MONTH) after_oneyear_twomonth;
结果:
fmt格式(DATE_FORMAT使用)
日期间隔类型(DATE_ADD函数使用)
流程函数
函数 | 功能 |
---|---|
IF(value,t,f) | 如果value是真,返回t;否则返回f |
IFNULL(value1,value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN [value1] THEN[result1] … ELSE [default] END | 如果value1是真返回result1,否则返回default |
CASE [expr] WHEN [value1] THEN[result1] … ELSE [default] END | 如果expr等于value1返回result1,否则返回default |
CREATE TABLE salary (userid int,salary decimal(9,2));
INSERT INTO salary VALUES(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
SELECT IF(salary>2000,'high','low') FROM salary;
显示high和low
SELECT IFNULL(salary,0) FROM salary;
替换null为0
SELECT CASE WHEN salary <= 2000 THEN 'low' ELSE 'high' END FROM salary;
显示high和low
SELECT CASE WHEN salary <= 2000 THEN 'low' WHEN salary >2000 AND salary <=4000 THEN 'mid' WHEN salary > 5000 THEN 'high' END FROM salary;
其他常用函数
函数 | 功能 |
---|---|
DATABASE() | 返回当前数据库名 |
VERSION() | 返回当前数据库版本 |
USER() | 返回当前登录用户 |
INET_ATON(ip) | 返回ip地址的数字表示 |
INET_NTOA(num) | 返回数字代表的ip地址 |
PASSWORD(str) | 返回字符串str的加密版本 |
MD5(str) | 返回字符串str的MD5加密 |
SELECT INET_ATON('192.168.0.1');
3232235521
SELECT INET_NTOA(3232235521);
192.168.0.1
这样可以更方便进行ip或者网段比较
CREATE TABLE t1(
ip varchar(20) DEFAULT NULL
);
INSERT INTO t1 VALUES('192.168.1.1'),('192.168.1.3'),('192.168.1.6'),('192.168.1.10'),('192.168.1.20'),('192.168.1.30');
SELECT * FROM t WHERE INET_ATON(ip)>=INET_ATON('192.168.1.3') AND INET_ATON(ip) <= INET_ATON('192.168.1.20');
SELECT MD5('123456');
SELECT PASSWORD9('123456');
比较网段结果
表类型(存储引擎的选择)
创建表的时候创建存储引擎
5.5前默认MYisam,之后默认InnoDB
show variables like 'table_type';
CREATE TABLE ai (
i bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(i)
) ENGINE=MyISAM DEFAULT CHARSET = gbk;
CREATE TABLE country(
contry_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
contry VARCHAR(50) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (contry_id)
)ENGINE=InnoDB DEFAULT CHARSET=gbk;
ALTER TABLE ai ENGINE = InnoDB;
SHOW CREATE TABLE ai \G
存储引擎区别
MyISAM引擎
不支持事务和外键,优势访问速度快,对事务完整性没有要求,或者以SELECT、INSERT语句为主
MyISAM引擎分为三个文件
文件名.frm(存储表定义)
文件名.MYD(MYData,存储数据)
文件名.MYI(MYIndex,存储索引)
创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定,也就是说不同MYISAM的索引文件和数据文件放置位置不同,文件路径是绝对路径并且具有访问权限。
MYSIAM表文件可能损坏,用CHECK TABLE语句检查健康,并且用REPAIR TABLE 语句修复一个MYSIAM表的健康。
MYSIAM分为三种存储方式,静态表,动态表,压缩表。
静态表,非变长字符串,存储迅速,容易缓存,出现故障容易修复,缺点占用空间比动态表多,静态表多余的会按照空格补齐,但是获取会去掉后面对的空格。
CREATE TABLE Myisam_char(name char(10)) ENGINE = MYISAM;
INSERT INTO Myisam_char VALUES('abcde'),('abcde '),(' abcde'),(' abcde ');
SELECT name,LENGTH(name) FROM Myisam_char;
动态表
变长字符串,记录不是固定长度,存储优点占用空间较少,但是频繁更新和删除会产生碎片,需要定期执行OPTIMIZE TABLE 语句或mysiamchk -r 命令改善性能,出现故障时回复比较难。
压缩表
myisampack工具创建,占用非常小的磁盘空间,因为每个记录都是单独压缩的。所以只有非常小的访问开支。
InnoDB引擎
具有提交、回滚、崩溃恢复的事务安全。但是对比MYISAM,InnoDB写的效率会更低,并且会占用更多磁盘空间保留数据。
不同于其他引擎的地方。
1、自动增长列
当插入值为0或者空的时候,实际插入自动增长后的值,
SELECT LAST_INSERT_ID();
获取最后一此插入的自增长值
CREATE TABLE autoincre_demo(
i smallint not null auto_increment,
name varchar(10) , primary key(i)
)engine=innodb;
insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3');
select * from autoincre_demo;
insert into autoincre_demo values(4,'4');
select LAST_INSERT_ID();
结果:2
INSERT INTO autoincre_demo(name) values('5'),('6'),('7');
SELECT LAST_INSERT_ID();
结果:5
InnoDB自动增长列必须是索引,如果组合索引,必须是组合索引的第一列。
MYSIAM组合索引可以是其他列,这样插入时按照组合索引前面几列进行排序递增的。
外键约束
MYSQL支持外键约束的表只有InnoDB,创建外键的时候,附表必须有对应的索引,子表创建外键的时候也会创建索引。
DROP TABLE country;
CREATE TABLE country(
country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
country VARCHAR(50) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE city;
CREATE TABLE city(
city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
city VARCHAR(50) NOT NULL,
country_id SMALLINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(city_id),
KEY idx_fk_country_id(country_id),
FOREIGN KEY(country_id) REFERENCES country(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
创建外键约束指定ON DELETE RESTRICT ON UPDATE CASCADE表示在删除的时候子表有对应记录就不删除,修改的时候,对应子表记录也修改。
当某个表被其他表外键关联的时候,主键或对应索引禁止删除
导入多个表的时候,如果忽略表的导入顺序,可以通过暂时关闭索引,处理,关闭命令时,SET FOREIGE_KEY_CHECKS=0 执行完 SET FOREIGE_KEY_CHECKS=1 回复
3、存储方式
使用共享表空间存储:这种方式存储的表结果保存在.frm中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中。
使用多表空间存储:
单表备份回复目标到数据库需要借助mysqldump和mysqlimport
MEMORY存储引擎
使用存在于内存的内容创建表。每个memory实际对应一个磁盘文件,格式.frm。MEMORY引擎访问速度非常快,因为默认放在内存中,而且默认HASH索引,但是一旦关闭服务,数据就会丢失。
CREATE TABLE tab_memory ENGINE=MEMORY
SELECT city_id,city,country_id
FROM city GROUP BY city_id;
SELECT count(*) from tab_memory;
create index mem_hash USING HASH on tab_memory(city_id);
MERGE存储引擎
适用于对多个表进行查询和更新操作,缺点不能只能插入对应表格。
不能使用分区表时候使用
DROP TABLE IF EXISTS payment_2006;
CREATE TABLE payment_2006(
country_id smallint,
payment_date datetime,
amount DECIMAL(15,2),
KEY idx_fk_country_id(country_id)
)ENGINE=MYISAM;
DROP TABLE IF EXISTS payment_2007;
CREATE TABLE payment_2007(
country_id smallint,
payment_date datetime,
amount DECIMAL(15,2),
KEY idx_fk_country_id(country_id)
)ENGINE=MYISAM;
DROP TABLE IF EXISTS payment_all;
CREATE TABLE payment_all(
country_id smallint,
payment_date datetime,
amount DECIMAL(15,2),
KEY idx_fk_country_id(country_id)
)ENGINE=MERGE union=(payment_2006,payment_2007) INSERT_METHOD=LAST;
insert into payment_2006 values(1,'2006-05-01',100000),(2,'2006-08-15',150000);
insert into payment_2007 values(1,'2007-05-01',350000),(2,'2007-07-15',220000);
select * from payment_2006;
select * from payment_2007;
select * from payment_all;
TokuDB
如何选择合适的存储引擎
MYISAM:默认的MYSQL插件式存储引擎,如果应用以读操作和插入操作为主,只有很少用到更新和删除的操作,对事物完整性和并发性要求不是很高,那么选择这个存储引擎非常合适,MYISAM实在WEB、数据仓库和其他应用环境最常用的存储引擎之一。
InnoDB:用于事务处理应用程序、支持外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了操作插入和查询以外,还包括很多更新和删除操作。inndb比较合适。对于类似计费系统或者财务系统等对数据准确性要求比较高的系统,InnDB都合适
MEMORY:将数据保存在RAM中,需要快速定位和其他类似数据的环境下,可以提供极快的访问速度。MEMORY的缺陷是对表的大小限制,太大的表无法缓存在内存当中,数据库异常数据可以恢复。
MERGE:用于将一系列MYSIAM表按照逻辑的方式组合在一起,并作为引用对象,优点在于可以突破对单个MYISAM表达大小的限制,并且通过将不同的表分不到多个磁盘上,有效改善MERGE访问效率。
选择合适的数据类型
char和varchar存储选择
注意varchar(4)存5个字节在非严格模式下面会发生,但是严格模式下面则超出长度的字节不会存储
对于变化不大并且查询速度有要求的用char
不同引擎使用
MYISAM:建议使用char代替可变长度的数据列
MEMORY存储引擎:目前使用固定长度的数据行存储,无论char或varchar都没有关系。
InnoDB存储引擎:建议使用varchar,因为内部存储长度没有区分固定长度和变长长度;
TEXT和BLOB
一般保存少量文本的时候使用char或varchar,保存大量文本的时候使用TEXT和BLOB
BLOB能够保存二进制格式比如招聘,TEXT只能保存字符数据文章或日志
因为BLOB和TEXT会发生性能问题,删除的时候会留下"空洞"
提高性能建议定期使用OPTIME TABLE功能对类表进行碎片整理
消除text的空洞空间
drop table if EXISTS t;
CREATE TABLE t(id varchar(100),context text);
insert into t values(1,repeat('haha',100));
insert into t values(2,repeat('haha',100));
insert into t values(3,repeat('haha',100));
insert into t select * from t;
insert into t select * from t;
exit;
du -sh t.*;
OPTIMIZE TABLE t
du -sh t.*;
创建散列索引加快text和blob等大文本的查询
合成索引就是根据大文本字段的内容简历索引值,并把这个值单独存在索引列。这种技术只能用于精确匹配,可以使用MD5(),SHAI(),CRC32()或者自己的应用程序计算散列值。
精确查询
drop table if EXISTS t;
create table t(id varchar(100),context blob,hash_value varchar(40));
insert into t values(1,repeat('beijing',2),md5(context));
insert into t values(2,repeat('beijing',2),md5(context));
insert into t values(3,repeat('beijing 2008',2),md5(context));
select * from t;
select * from t where hash_value=md5(repeat('beijing 2008',2));
模糊查询
为字段的前n列创建索引
create index idx_blob on t(context(100));
select * from t where context like 'beijing%';
不必要避免检索text和blob数据
把blob或text分离到其他表当中,既可以避免select * 查询所有,把原来数据列改成固定数据。又可以减少主表中的碎片。
浮点数与定点数
浮点数和定点数区别
插入大于精度的时候,浮点数插入会进行四舍五入。
定点数在sqlmode默认情况下,会报错并且会插入,如果sqlmode在TRADITIONAL(传统模式),系统会直接报错。
create table test(c1 float(10,2),c2 decimal(10,2));
insert into test values(131072.32,131072.32);
select * from test;
从这里可以看出,浮点型数据会产生误差,而定点数不会产生误差。
注意:
关于浮点数和定点数的应用。
1、浮点数存在误差问题。
2、对货币等对精度敏感的数据使用定点数,。
3、编程中注意浮点数误差,并且尽量避免进行比较
4、注意浮点数中一些特殊值的处理。
日期类型选择
date time datetime timestramp
1、根据满足应用最小存储日期类型,如果应用只需要年份,那么用1字节存储year,不需要四字节date,
2、如果记录时分秒并且年代久远使用datetime,不适用timestramp。
3、如果记录不同时区使用,最好使用timestramp,和实际时区对应。
对于字符类型根据存储引擎选择
对于精度要求较高,用decimal,
对含有text和blob的应用,如果经常删除和修改记录要定时执行optimize table进行碎片整理
日期类型根据实际需要选择满足应用的最小存储的日期类型。
字符集
CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
如何选择合适的字符集
1、应用要处理各种各样的文字,或者将发布到使用不同国家或地区,使用Unicode字符集,mysql目前是utf-8
2、如果数据库涉及已有数据的导入,就充分考虑已有数据的兼容性,假如已有数据是GBK文字,选择GB2312-80为数据库字符集很可能出现文字无法正确导入。
3、如果数据库支持一般中文,且数据量很大,性能要求很高,选择双字节定长编码的中文符集,比如GBK。
而UTF-8汉字编码需要3个字节。
4、如果数据库需要大量字符运算使用定长字符集,因为定长字符集处理速度比边长字符集要快
5、如果所有客户端都支持相同字符集,则优先算则该字符集作为数据库字符集。
Mysql字符集和校对规则,4个级别的默认设置:服务器级、数据库级、表级和字段级。