MySql

来自随意问技术百科
跳转至: 导航搜索

SQL

  • where 1=1 性能低效
  • group by ... having ...

function

  • CONCAT_WS 第一个字段可以指定字符串分隔符
  • CEIL() 舍入到最大整数
  • FLOOR() 舍入到最小整数
  • ROUND() 四舍五入
  • SIGN() 求符号,>0返回1,=0返回0,<0返回-1
  • MOD() 求整除余数
  • LEFT()、RIGHT()、SUBSTRING() 依次为从左、右、任意位置截取字符串
  • ASCII() 得到字符的ASCII码
  • CHAR() 得到一个ASCII码数字对应的字符
  • NOW()、SYSDATE()、CURRENT_TIMESTAMP、CURDATE()、CURTIME() 获取系统时间
  • DATE_ADD(date, INTERVAL expr type)时间的加法运算
  • DATE_SUB(date, INTERVAL expr type)时间的减法运算
  • CAST(expression AS type)、CONVERT(expression, type) 类型转换
  • COALESCE(expression,value1,value2...,valuen)、IFNULL(expression,value) 空值处理
  • NULLIF(expression1,expression2) 等价返回NULL,不等价返回expression1
  • CASE 控制流程函数
用法1 case expression
when value1 then returnvalue1
when vlaue2 then returnvalue2
...
else defaultreturnvalue
end
用法2 case 
when condition1 then returnvalue1
when condition2 then returnvalue2
...
else defaultreturnvalue
end
  • CONV(N,from_base,to_base)、BIN(N)、OCT(N)、HEX(N) 数字进制转换
  • LPAD(str,len,padstr)、RPAD(str,len,padstr) 填充函数
  • REPEAT(str,count) 字符串str重复次数count
  • REVERSE(str) 字符串颠倒
  • ELT(N,str1,str2,str3,...)、FIELD(str,str1,str2,str3,...)、FIND_IN_SET(str,strlist) 字符串集合操作
  • GREATEST()、LEAST() 计算集合中的最大、最小值
  • DATABASE() 返回当前数据库名
  • VERSION() 返回MySQL服务器的版本
  • USER()(SYSTEM_USER、SESSION_USER) 返回当前MySQL用户名
  • ENCODE(str,pass_str) 、DECODE(str,pass_str)、MD5(str)、SHA1(str) 加密解密函数
  • UUID() 生成全局唯一字符串

索引和约束

http://blog.csdn.net/zhaohuabing/article/details/1447721

  • 索引
创建索引
CREATE INDEX 索引名 ON 表名(字段1,字段2...,字段n)
删除索引
DROP INDEX 索引名 ON 表名
  • 约束 非空约束、唯一约束、CHECK约束(MySql目前未支持)、主键约束、外键约束
创建约束
ALTER TABLE 表名 ADD CONSTRAINT 唯一约束名 UNIQUE(字段1,字段2...,字段n) 
在Mysql中其实也是索引,等价于
CREATE UNIQUE INDEX 索引名 ON 表名(字段1,字段2...,字段n)

删除约束
ALTER TABLE DROP INDEX 索引名

连接

  • 内连接(inner join) 默认连接方式
select * from table a JOIN table b ON a.xx=b.xx
  • 不等值连接 笛卡尔乘积
select * from table a JION table b ON a.xx!=b.xx
  • 交叉连接
隐式 select * from table a ,table b
显式 select * from table a CROSS JOIN table b
  • 自连接
表与自身相连接  
  • 外部连接
OUTER JOIN(MySql不支持), LEFT JOIN, RIGHT JOIN

子查询

  • 单值子查询
返回一行记录,又称标量子查询,可以看做是函数
  • 列值子查询
返回多行记录,又称表子查询,可以看做是一个临时的表

主流数据库的SQL语法差异解决方案

  • 差异
  1. 数据类型的差异
  2. 运算符的差异
  3. 函数的差异
  4. 常用SQL的差异
  5. 取元数据信息的差异
  • 消除差异性方案
  1. 为每种数据库编写不同的SQL语句
  2. 使用语法交集
  3. 使用SQL实体对象、适配翻译器
  4. 使用ORM工具 Hibernate的HSQL、EJB的EJB-SQL
  5. 使用SQL翻译器 SwisSQL、LDBC(开源)、CowNewSQL(开源、建议使用)

SQL优化

  1. 创建必要的索引
  2. 使用预编译查询
  3. 调整WHERE子句中的连接顺序
  4. SELECT语句中避免使用*
  5. 尽量将多条SQL压缩到一句SQL中
  6. 用WHERE子句替换HAVING子句
  7. 使用表的别名
  8. 用EXISTS替换IN
  9. 用表连接替换EXISTS
  10. 避免在索引列上使用计算
  11. 用UNION ALL替换UNION
  12. 避免隐式类型转换造成的全表扫描
  13. 防止检索范围过宽
IS NOT NULL或者不等于判断
LIKE a%使用索引,a%c、%a使用全表扫描

事务

  • ACID
原子性(automicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)
  • 事务隔离级别
READ_UNCOMMITED 存在脏读、不可重复读、幻读问题
READ_COMMITED 存在不可重复读、幻读问题
REPEATABLE_READ 存在幻读问题
SERIALIZABLE 最严格的隔离级别,解决了上述三个问题

在innodb默认的事务隔离级别下,普通的SELECT是不需要加行锁的,但LOCK IN SHARE MODE、FOR UPDATE及高串行化级别中的SELECT都要加锁。

死锁

向innodb数据表中写数据死锁。搜集资料如下

  • 形成死锁的四个必要条件
  1. 互斥条件:一个资源每次只能被一个进程使用。
  2. 请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放。
  3. 不剥夺条件:进程已获得的资源,在末使用完之前,不能强行剥夺。
  4. 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。即A在等待B占有的资源、B在等待C占有资源。。。Z在等待A占有的资源。

破坏上面任一条件都可避免死锁。

  • 发生死锁后的解决办法:
  1. 撤消陷于死锁的全部进程;
  2. 逐个撤消陷于死锁的进程,直到死锁不存在;
  3. 从陷于死锁的进程中逐个强迫放弃所占用的资源,直至死锁消失。
  4. 从另外一些进程那里强行剥夺足够数量的资源分配给死锁进程,以解除死锁状态
可直接在mysql命令行执行:show engine innodb status\G; 
这里显示了 Innodb 最后检测到事务引发的死锁,包括发生死锁时的状态,加了什么锁,在等待什么锁释放,以及 Innodb 决定哪个事务会被回滚。注意,innodb只显示了事务持有锁的相关简单信息。
并且只显示了每个事务最后执行的语句,发生死锁的记录就是由于这些语句引起的。查看复杂的死锁信息还需要查看日志文件,才能找到真正引发冲突的语句。
大部分情况下,SHOW INNODB STATUS 显示的信息基本足够了。
查看造成死锁的sql语句,分析索引情况,然后优化sql 
然后 
show processlist; 
kill processid; 


explain

之前的MySQL版本,explain 只支持select,但在最新的5.6版本中,支持explain update/delete

slowlog

  • show variables like '%slow%';
  • show global status like '%slow%';