优质文章收集
- MySQL索引原理及慢查询优化
B+树、最左匹配、区分度的计算
DDL
DDL=data definition language,主要是对数据库表结构的操作语言,包括create、alter、drop
创建表
一般要带有自增序列的ID、记录数据插入时间的CTIME、记录数据修改时间的UTIME:
--参考语句 create table TABLE_NAME ( ID int not null auto_increment, OTHER_COLUMN varchar(80), CTIME datetime not null default now(), UTIME datetime not null default now() on update now(), primary key (id)); --指定编码 create table analytics.tmp(id int) default charset=utf8; --修改编码 alter table analytics.tmp convert to character set utf8; |
NewCaseFolder.MedicalRecord_Affix表和Event_Attach_R表,在3月28日的改版中,就遗漏了servercreatetime和serverupdatetime。
修改列属性
alter table TABLE_NAME change column COLUMN_NAME NEW_COLUMN_NAME int; -- 举例,增加ctime,utime列 alter table NewCaseFolder.Chart_Group add updatetime datetime default now() on update now(); alter table NewCaseFolder.Chart_Group add createtime datetime default now(); -- 举例,为表中的系统时间增加默认值 alter table NewCaseFolder.Chart_Group change column ServerCreateTime ServerCreateTime datetime default now(); alter table NewCaseFolder.Chart_Group change column ServerUpdateTime ServerUpdateTime datetime default now() on update now(); |
结束死锁SQL
有时候,低效的
show processlist;
kill 7314;
创建触发器
对于新插入的数据,分配随机、唯一的邀请码。
DELIMITER // create trigger tr_invite_code before insert on xsl_mada.d_business_admin for each row begin declare v_icode int; declare v_duplicate int; repeat set v_icode = ROUND(ROUND(RAND(),5)*100000); select count(*) into v_duplicate from xsl_mada.d_business_admin where invite_code=v_icode; until v_icode>10000 and v_duplicate=0 end repeat; set NEW.invite_code=v_icode; end// delimiter ; |
DML
DML=data manipulation language,主要是对表数据的操作,包括select、insert、update、delete
常见日期计算
更多函数,参见日期和时间函数大全。
常用查询计算列举如下:
--本周周一 select DATE_SUB(CURDATE(),INTERVAL WEEKDAY(curdate()) DAY); --上月月初 select DATE_SUB(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),INTERVAL DAY(CURDATE())-1 DAY); --本月月初 select DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY); --下月月初 select date_add(last_day(CURDATE()),interval 1 day); --根据生日计算年龄的两种方法 select fld_PatientBirthday,TIMESTAMPDIFF(YEAR,pa.fld_PatientBirthday,curdate()) age from db_Hdf.tab_Patient where fld_PatientBirthday!='' limit 100; |
字符串操作
- concat
- left、right
- substring、substring_index
- instr
- replace
去回车换行
REPLACE(REPLACE(field, CHAR(10),''), CHAR(13),'')
查列名
向一个表insert插入数据的时候,一定要标记列名:
--错误的做法,当TABLE_NAME表新增一列的时候,此SQL将报错 insert into TABLE_NAME values('aa','bb'); --正确的做法,当TABLE_NAME表新增一列的时候,不会影响此SQL的执行 insert into TABLE_NAME (COLUMN1, COLUMN2) values('aa','bb'); |
当表的列过多时,逐个输入列名会很麻烦,可以使用如下SQL:
select group_concat(column_name) from information_schema.columns where table_name='the_name'; |
批量插入
如果是使用程序插入大量数据,尽量绑定变量。如果是执行SQL批量插入,尽量将多条SQL合并一条。例如:
--方法1 insert into TABLE_NAME (ID,OTHER_COLUMN) values (1,'aaa'); insert into TABLE_NAME (ID,OTHER_COLUMN) values (1,'aaa'); --方法2 insert into TABLE_NAME (ID,OTHER_COLUMN) values (1,'aaa'),(1,'aaa'); |
上面两种方法,方法2比方法1的效率要搞10+倍。
方法1,每个SQL执行,都需要连接数据库、建立事务、记录日志、执行、关闭事务、关闭数据库连接。对于方法2,只是执行一次流程。
更多的描述参考:MySQL批量SQL插入性能优化
顺序编号
为记录添加1,2,3这样的顺序编号:
select @rowNum:=IFNULL(@rowNum,0)+1 as rowNo,fmt.upload_record from analytics.f_medicalrecord_total fmt,(Select (@rowNum :=0) ) b limit 100; |
通过表名来查所在库
select table_schema from information_schema.tables where table_name ='你要查询的表名(注意要使用大写字母)'; |
只复制表结构到新表
CREATE TABLE 新表SELECT * FROM 旧表WHERE 1=2 |
修改已有ID为自增
alter table 表 change ID ID bigint not null auto_increment primary key; |
获取表字段
select COLUMN_NAME from information_schema.COLUMNS where table_name = 'your_table_name' and table_schema = 'your_db_name'; |
存储过程传递参数中文类型设置
declare v varchar(100) character set utf8; |
中文字段拼音排序
select * from TABLE_NAME order by CONVERT(chineseColumnName USING gbk); |
更多中文拼音排序可参考:点此进入
Exists和in的性能比较和使用
in 是把外表和内表作hash join,而exists是对外表作loop循环,每次loop循环再对内表进行查询。(关于hash join可参考Merge join vs. Hash join vs. Nested loop )
如果查询的两个表大小相当,那么用in和exists差别不大;如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
例如:表A(小表),表B(大表)
1:
select * from A where cc in (select cc from B) 效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc) 效率高,用到了B表上cc列的索引。
2(与1相反):
select * from B where cc in (select cc from A) 效率高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc) 效率低,用到了A表上cc列的索引。
not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao'); 与 select name from student where name='zhang' or name='li' or name='wang' or name='zhao' 的结果是相同的。
#内表较大情况使用exists select userid from analytics.d_user_basic dub where exists(select 1 from analytics.f_business_cml fbc where dub.userid=fbc.userid); #内表较小情况使用in select userid from analytics.d_user_basic dub where dub.userid in(select userid from analytics.f_business_cml); |
结果中取随机值
- order by rand()
-
使用方便,但效率不高,会多次遍历全表排序
select tt.userid 用户ID, dub.truename 姓名, dub.speciality 科室, dub.standard_hospital 医院, tt.phone 电话, dub.Registerdate 注册时间, tt.epocket_last_time 最后使用时间, dup.epocket_upgrade_time 最后一次升级时间, dup.epocket_cur_version 目前版本, tt.last_action_time 最后一次行为时间, last_action 最后一次行为,tt.action_qty '8-9月行为次数' from analytics.tmp_epocket_action tt join analytics.d_user_basic dub on tt.userid=dub.userid join analytics.d_user_product dup on tt.userid=dup.userid where tt.epocket_last_time<=date_sub(curdate(),interval 30 day) and push_history is null order by rand() limit 4500;
-
- round(max(id)*rand())
-
- 如果id列不连续将造成随机结果比预期的要小,越不连续越不可靠
- 多个表相连时,取其中最能决定结果行数的表
-
对于order by rand()速度慢的情况,可以使用中间表,建立一列自增id列,用来取随机值
select tt.userid 用户ID, dub.truename 姓名, dub.speciality 科室, dub.standard_hospital 医院, tt.phone 电话, dub.Registerdate 注册时间, tt.epocket_last_time 最后使用时间, dup.epocket_upgrade_time 最后一次升级时间, dup.epocket_cur_version 目前版本, tt.last_action_time 最后一次行为时间, last_action 最后一次行为,tt.action_qty '8-9月行为次数' from analytics.tmp_epocket_action tt join (select round(61024*rand()) id from ( select * from analytics.tmp_epocket_action tt where tt.epocket_last_time<=date_sub(curdate(),interval 30 day) and push_history is null) tt limit 4600)a on tt.id=a.id join analytics.d_user_basic dub on tt.userid=dub.userid join analytics.d_user_product dup on tt.userid=dup.userid