1.常见的ddl,dql,dml联合场景
ddl：对于表结构的管理sql语句
dql:常见的sql查询语句
dml:常见的修改数据的update,insert,delete语句
- 将一个表里面的数据插入到另外一个表中的纯sql操作,一般可用于建立临时表使得大表变小表
insert into tbl_quick_msg_group
(wechatapp_id,user_id,group_name,default_type,update_time,create_time)
(select wechatapp_id,user_id,'未分组' group_name,1 default_type,update_time,create_time from tbl_quick_reply group by user_id,wechatapp_id ORDER BY wechatapp_id);
- php配合sql的大表瘦身
public function summary()
{
$sql="DROP TEMPORARY TABLE IF EXISTS tmp_staff_group_info";
LogicUtil::db_run_update($sql);
$sql="DROP TEMPORARY TABLE IF EXISTS tmp_staff_group_rel";
LogicUtil::db_run_update($sql);
$sql="CREATE TEMPORARY TABLE tmp_staff_group_info
SELECT sgi._id, s.user_id
FROM tbl_staff_group_info sgi
INNER JOIN tbl_staff_group_rel sgr ON sgr.group_id = sgi._id
where sgi.group_name LIKE '%白三涉水%' ";
LogicUtil::db_run_update($sql);
$sql="CREATE TEMPORARY TABLE tmp_staff_group_rel
SELECT b._id, a.user_id
FROM tbl_staff_wechatapp_relation AS a
LEFT JOIN tbl_wechatapp AS b ON a.wechatapp_id = b._id
WHERE b.user_id > 0 && b.app_name LIKE '%白三涉水%' ";
LogicUtil::db_run_update($sql);
$sql=" select s.*, 0 group_id
from tbl_staff s
inner join tmp_staff_group_info A on A._id = s.user_id
inner join tmp_staff_group_rel B on B._id = s.user_id
where s.parent_user_id = 16695397
ORDER BY s.user_id ASC LIMIT 0, 20";
$result = LogicUtil::db_run_sql($sql);
$sql="DROP TEMPORARY TABLE IF EXISTS tmp_staff_group_info";
LogicUtil::db_run_update($sql);
$sql="DROP TEMPORARY TABLE IF EXISTS tmp_staff_group_rel";
LogicUtil::db_run_update($sql);
return $result;
}
2.1. mysql的索引优化选择器未必绝对可信，我发现以下2条sql，指定索引执行的效率比mysql索引优化器默认的要快很多，说明不要太依赖优化器，特定场景特事特办(优化器使用order 的 last_chat_time的索引，而我指定where条件中的wechatapp_id索引)
select * from tbl_customer where wechatapp_id in (11739) && kefu_user_id in(798268289) ORDER BY last_chat_time desc limit 0,10； /执行30多秒
select * from tbl_customer FORCE INDEX(`wechatapp_id`) where wechatapp_id in (11739) && kefu_user_id in(798268289) ORDER BY last_chat_time desc limit 0,10; //执行0.4
- 游标场景,缺点内存开销大，容易锁，是一个存储过程
BEGIN
--定义变量
declare testrangeid BIGINT;
declare versionid BIGINT;
declare done int;
--创建游标，并存储数据
declare cur_test CURSOR for
select id as testrangeid,version_id as versionid from tp_testrange;
--游标中的内容执行完后将done设置为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
--打开游标
open cur_test;
--执行循环
posLoop:LOOP
--判断是否结束循环
IF done=1 THEN
LEAVE posLoop;
END IF;
--取游标中的值
FETCH cur_test into testrangeid,versionid;
--执行更新操作
update tp_data_execute set version_id=versionid where testrange_id = testrangeid;
END LOOP posLoop;
--释放游标
CLOSE cur_test;
- sql执行顺序分析:参考文献
第一步：加载from子句的前两个表计算笛卡尔积，生成虚拟表vt1；
第二步：筛选关联表符合on表达式的数据，保留主表，生成虚拟表vt2；
第三步：如果使用的是外连接，执行on的时候，会将主表中不符合on条件的数据也加载进来，做为外部行
第四步：如果from子句中的表数量大于2，则重复第一步到第三步，直至所有的表都加载完毕，更新vt3；
第五步：执行where表达式，筛选掉不符合条件的数据生成vt4；
第六步：执行group by子句。group by 子句执行过后，会对子句组合成唯一值并且对每个唯一值只包含一行，生成vt5,。一旦执行group by，后面的所有步骤只能得到vt5中的列（group by的子句包含的列）和聚合函数。
第七步：执行聚合函数，生成vt6；
第八步：执行having表达式，筛选vt6中的数据。having是唯一一个在分组后的条件筛选，生成vt7;
第九步：从vt7中筛选列，生成vt8；
第十步：执行distinct，对vt8去重，生成vt9。其实执行过group by后就没必要再去执行distinct，因为分组后，每组只会有一条数据，并且每条数据都不相同。
第十一步：对vt9进行排序，此处返回的不是一个虚拟表，而是一个游标，记录了数据的排序顺序，此处可以使用别名；
第十二步：执行limit语句，将结果返回给客户端
4.1 sql绑定参数的那些事：
php在pdo那一层是支持prepare去sql预编译的，参数已bindParam()的是形式绑定进去。之前一直以为参数绑定只是为了防止sql注入，现补充2点：
1.1: 预编译参数绑定执行的sql过程是，php先将sql发送到mysql，mysql执行预编译sql（值得注意的是预编译的过程的sql是开发自己写的sql，并不存在来自客户端用户数据参与的，如果预编译的sql都有注入的问题，那也是开发自己的问题），后面绑定参数走的是不同协议参与到编译的sql中执行的，如此自然不怕其sql注入了
1.2 : 多次执行相同的sql（只是绑定参数不同），mysql只会分析一次，生成执行计划一次，编译一次，减少过程自然效率会有优势（但你需要注意，绑定的参数最好不会对执行计划产生很大影响，mysql这个时候使用的是第一次的执行计划，如果因为你绑定参数使得执行效率很差，一般只有数据不均匀会造成，那你就注意了还有绑定的必要不）
//预处理1--操作数据库
$mysqli = new mysqli('localhost','root','mayi1991','mysqldemo');
$mysqli->query('set names utf8');
//创建预编译对象,这条sql就是先取mysql进行编译处理的，
//我们可以理解成 php与mysql先交互通讯一次，这条sql可是你自己写的，再有sql注入那也是醉了。。。
$mysqli_stmt = $mysqli->prepare("insert account (balance,name) values (?,?)");
//绑定参数
$balance = 122;
$name = '小白';
//TODO 走不同协议去与上一步编译好的sql进行绑定执行的，这里的参数值有可能来自客户端的输入，
//哪怕是用于注入sql的参数值，也没用了，因为之前编译好的sql根本不信任它，只会拿它当参数绑定执行，
//不会因为它去破坏sql的语义。。。
$mysqli_stmt->bind_param("ds",$balance,$name);
//执行 返回boolean值
$mysqli_stmt->execute();
- 数据库大表统计
select
table_schema as '数据库',
table_name as '表明',
table_rows as '记录数',
truncate(data_length/1024/1024/1024,2) as '数据容量(GB)',
truncate(index_length/1024/1024/1024,2) as '索引容量'
from information_schema.tables
where table_schema ='wechatapp_customer_service'
ORDER BY data_length desc,index_length desc;
image.png
- mysql单机储存空间到达极限,删除索引并立即释放空间,再迁移数据后进行索引恢复
ALTER TABLE `wechatapp_customer_service`.`tbl_message202002`
DROP INDEX `openid`;
OPTIMIZE table tbl_message201906; //mysql删除数据后并不会立即腾出空间，执行optimize table 可以快速立即释放
- mysql表设计之三范式思想 MySQL设计之三范式的理解
1NF:字段不可分,就是每个字段属性设计都应该保持原子性(比如地址里面包含省份市级县城,这里就不具备原子性，因为省份，市级，县是可以拆分的)
2NF:有主键，非主键字段依赖主键; （一个表中所有的属性应该只能更主键具备唯一识别的关系，而不能跟非主键，比如用户表中,id与uid，既然你的uid就是可以定位用户信息的，这个时候就不应该再使用id）
3NF:非主键字段不能相互依赖; (A表明明已经存在B表的主键id属性，现在又在A表中建立了B表的其他属性字段，这就不满足第三范式了,因为我们明明可以通过A表中的B的唯一索引推出来B的所有属性，故不能在A表中建立B的非主键属性)
