分类： mysql数据库

sql执行顺序

基础版，未考虑性能优化而做出的改变
sql执行顺序
(1)from
(2) on
(3) join
(4) where
(5)group by
(6) avg,sum....
(7)having
(8) select
(9) distinct
(10) order by

整个流程

  • 1 from：

    • from子句中的前两个表执行一个笛卡尔乘积，此时生成虚拟表table_1

    Table A Table B
    ID ID
    1 3
    2 4
    笛卡尔乘积后:
    AID BID
    1 3
    1 4
    2 3
    2 4

  • 2 on筛选器：

    • on中的逻辑表达式将应用到 vt1 中的各个行，筛选出满足on逻辑表达式的行，生成虚拟表 table_2

  • 3 join:

    • 根据join语句中多个表之间字段的联系,生成一个新的表table_3
    几种用法：
    INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。
    LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。
    RIGHT JOIN（右连接）：与 LEFT JOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。

    Table A Table B
    ID Name ID Name
    1 aa 1 bb
    2 bb 2 cc
    3 cc 3 dd
    4 dd 4 ee

    inner join: select * from A inner join B on A.name = B.name;

    ID Name ID Name
    2 bb 1 bb
    3 cc 2 cc
    4 dd 3 dd

    left join: select * from A left join B on A.name = B.name;

    ID Name ID Name
    1 aa Null Null
    2 bb 1 bb
    3 cc 2 cc
    4 dd 3 dd

    right join: select * from A right join B on A.name = B.name;

    ID Name ID Name
    2 bb 1 bb
    3 cc 2 cc
    4 dd 3 dd
    Null Null 4 ee

  • 4 重复1-3步骤

    • from子句中的表数目多余两个表，那么就将table_3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3的步骤，最终得到一个新的虚拟表 table_3。

  • 5 where筛选器

    • 上一步生产的虚拟表引用where筛选器，生成虚拟表table_4
    在含有join操作时：on和where的一点区别：
    现有两张表A和B：都只有一个字段ID，A数据有4，B的数据有3个

    Table A Table B
    ID ID
    1 1
    2 2
    3 3
    4

    1 select A.ID as AID, B.ID as BID from A left join B ON A.ID = B.ID WHERE B.ID<3

    AID BID
    1 1
    2 2

    2 select A.ID as AID, B.ID as BID from A left join B ON A.ID = B.ID AND B.ID<3

    AID BID
    1 1
    2 2
    3 Null
    4 Null

    原因： 优先级： on > left join > where
    第一条语句的顺序：
    a)执行leftjoin A left join B结果：
    AID BID
    1 1
    2 2
    3 Null
    4 Null

    b)执行where B.ID < 2结果： AID BID 1 1 2 2 第二条语句的顺序： a)先执行 A.ID = B.ID and B.ID < 2，得到结果 ID 1 2 b)再执行left join，得到结果 AID BID 1 1 2 2 3 Null 4 Null

  • 6 group by

    • group by 子句将中的唯一的值组合成为一组，得到虚拟表table_5。
    根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。
    Table A
    ID Level
    1 A
    2 A
    3 B
    4 B
    5 B
    6 B
    7 C

    Select count(ID) as Num ,Level from table group by Level

    table
    Num Level
    2 A
    4 B
    1 C

  • 7 having

    • 应用having筛选器，生成table_6。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。
    如在6中针对筛选的结果需要进行Num>2的筛选：只能通过having，而不能通过where
    Where的执行在group by之前，不能够针对group 不要生成的table进行操作，因此，需要对group by后生成的table进行操作可以使用having对数据进行筛选

  • 8 select

    • 处理select子句。将table_6中的在select中出现的列筛选出来。生成table_7
    注意：由此可以看出，select是在from之后被执行的，所以在select中使用的别名在where中是不可以被使用的。

  • 9 distinct

    • 应用distinct子句，table_7中移除相同的行，生成table_8。事实上如果应用了group by子句那么distinct是多余的，
    原因同样在于，分组的时候是将列中唯一的值分成一组，同时只为每一组返回一行记录，那么所以的记录都将是不相同的。

  • 10 order by

    • 应用order by子句。按照order by 的条件去排序table_8

  • 11 返回结果

Mysql默认值

编    写：袁    亮
时    间：2014-11-17
说    明：Mysql默认值

1、默认值default设置
1.1 有设置：默认值
1.2 未设置：无

2、是否允许NULL值设置：
2.1 允许有(NULL)
default有值则为设置的默认值
default无值，则为NULL
2.2 不允许(NOT NULL)
default有值，而且不为NULL，则默认值为设置的值
default无值，则为对应字段类型的默认值(字符串的空字符串，整型的0)

3、自增长设置：AUTO_INCREMENT
3.1 不受default和是否允许NULL影响

4、时间戳：timestamp
4.1 默认为当前时间

内存不足导致sql执行过慢

编    写：袁    亮
时    间：2014-05-08
说    明：内存不足导致sql执行过慢

一、问题描述
1、某条sql执行了37秒
2、看慢日志里，被锁的时间在万分之几秒，因此不是因为被锁从而导致执行慢

二、问题分析
1、经与其他人沟通，可能是因为内存不足，导致索引没有加载到内存里，所以比较慢
mysql的所有会部分加载到内存里，如果内存不足的时候，会根据一定的策略将部分数据去除，重新读入数据到内存中
该内存大小设置可通过：show variables like '%key%';
然后设置set global key_buffer_size=2048000000;(2G)将该值改大
2、另外一种可能是在那个时间点，磁盘io有问题，从而导致读取数据变慢
但是如果是磁盘io卡住的话，那么那个时间点的其他sql应该也会卡，而看慢日志里，附近时间点并没有其他的慢查询
查看那个时间点的io，正常

三、总结
1、mysql启动或者重启的时候，因为要把索引加载到内存中，刚重启的时候，可能会导致服务器的io等负载过高，重启就挂
2、mysql一般来说，不能直接关，关掉之后，会需要修复表
3、有些时候，会执行的很慢，很有可能就是内存给的不够，一般4个G，机器较好的话，可以给4G

关于mysql截断内容问题

袁亮，2014-03-14，关于mysql截断内容问题解决

 

  • 问题描述：
  • 当用户发表有某些特殊字符的内容时，存入mysql数据库的内容会被截断

2、数据库、程序文件等都是utf-8编码

3、确认sql正常

 

  • 原因：
  • mysql中utf8编码最多只能是3个字节（5.3版本后，有utf8mb4类型可支持4个字节的utf8）
  • utf8是一种1-4个字节的可变字符编码（英文1个字符，汉字3个字符）
  • 某些特殊字符(emoji表情符号等)是4字节的utf8编码
  • Mysql在遇到超过最大字节范围的字符时，会忽略其后面的字符串，从而导致内容丢失

 

  • 解决思路：
  • 升级mysql到5.3版本以上，并将字段的编码设置为utf8mb4类型
  • 通过程序，将字符串中4字节的utf8字符替换或者删除即可

 

  • 剔除utf8字符串中4字节的字符方法：

[code]

function removeByte4($str){

return preg_replace('/[\xF0-\xF7].../s','', $str);

}

[/code]

性能：台式机，4.6W字符，0.006s，对程序性能影响基本可以忽略

 

附：（其他的一些相关知识）

 

  • Utf8 4字节各系统的支持情况：

1、windows xp: xp系统不支持4字节utf8字符, 浏览器用占位符显示

2、windows 7: 支持4字节utf8字符

3、mac os x: 支持4字节utf8字符

4、iPhone/iPad: 支持4字节utf8字符

 

1、将特殊的4字节字符用相应的图片表情代替

2、替换的时候，注意不要堵塞用户的处理流程，当文本内容较长时，这是一个很耗性能的处理，建议先忽略4字节字符，然后将内容存到文件或者其他地方，后续使用脚本对这些内容进行替换处理等操作。

3、新浪微博等有做相应处理

4、特殊符合以及对应表情可参考网站：http://www.charbase.com/

  • 四种不同剔除utf8字符串中4字节字符的性能比对：

测试机：普通pc 测试文本：4.6W字节的中英文、特殊字符等混合

  • 极快，推荐使用：006s

function removeByte4($str){

return preg_replace('/[\xF0-\xF7].../s','', $str);

}

 

  • 较慢，参考用：2s

function removeByte4_2($str){

return preg_replace('/[\x{10000}-\x{10FFFF}]/u','', $str);

}

  • 慢，但方便对特殊字符做不同替换：3s

function removeByte4_1($str){//移除utf-8编码中4字节及以上的字符

mb_internal_encoding("UTF-8");

$len = mb_strlen($str);

$res = '';

for($i=0;$i<$len;$i++){

$t = mb_substr($str,$i,1);

$res .= strlen($t)<=3?$t:'';

}

return $res;

}

  • 极慢，不具使用价值，仅参考：9s

function remove_4_byte($string) {

$char_array = preg_split('/(?<!^)(?!$)/u', $string );

$len = count($char_array);

for($x=0;$x<$len;$x++) {

if(strlen($char_array[$x])>3) {

$char_array[$x] = "";

}

}

return implode($char_array, "");

}

Mysql使用过程中的一些规范


编	写：袁	亮
时	间：2014-10-30
说	明：Mysql使用过程中的一些规范，tech4内部试行版

一.说明
	1.1 不管是新入职的实习生，还是工作多年的老油条，都需要遵守
	1.2 里面有些可能不是那么合理，如果觉得有疑问，可以随时找我沟通讨论
	1.3 所有的规定都是基于某些原因考虑的，只是这边没有说的太仔细
	1.4 过早优化、过渡优化都是一种错误，不要在一个小项目里，过渡的使用各种优化

二.数据库设置选择
	2.1 统一字符集：utf8
		2.1.1 校对规则：utf8_general_ci
		2.1.2 建库和建表的时候必须选，建表的时候选是因为有些老库没有设置该字符集
		2.1.3 乱码：SET NAMES UTF8 (这个是因为有很多老坑，否则这个是应该去掉，很影响性能)，程序也要设置utf8
		2.1.4 如果不想在程序段处理emoj等表情，可以使用UTF8MB4字符集做兼容(5.5.3版本以后)
	2.2 mysql版本最好是5.1 或者5.5以后的版本
		2.2.1 我们新的都已经是5之后了，只有几个非常老的项目，还是4.几的版本
	2.3 引擎的选择
		2.3.1 大大小小10几种引擎，但我们常用的就Myisam和InnoDB
		2.3.2 mysql 5以后的版本，默认引擎用InnoDB
		2.3.3 InnoDB的效率，根据我看网上各家的评测结果，应该是全面占优了，之前一直理解是Myisam读性能强于InnoDB
		2.3.4 我们的老项目，基本上都是Myisam，新项目尽量使用InnoDB
	2.4 程序连接数据库的账号权限
		2.4.1 指定ip连接(内网或者指定ip)
		2.4.2 主库给增、删、改、查权限
		2.4.3 从库只需要查的权限即可，禁止给全部权限

三.字段介绍选择
	3.1 常用字段：
		3.1.1 tinyint:0-255，枚举类型，性别等等，均以tinyint来
		3.1.2 int:一般的整型均用该类型，int不要设置长度，设不设置，实际存储都一样
		3.1.3 decimal:存储确定精度的浮点数时，不要用float和double，M是所有数字的个数，D是小数点后面的个数
		3.1.4 char:定长字符串，性能较高，存储md5后的值之类的非常适合
		3.1.5 varchar:变长字符串，非常常用，设置适合长度的(过长不会影响使用空间，但会影响内存使用)
		3.1.6 date:存储日期，生日之类的
		3.1.7 datetime:具体时间，精确到秒，不推荐timestamp，查看太麻烦了，性能还可以
		3.1.8 text:文本类型，最好分单独的表
		3.1.9 longtext:长文本，存储富文本编辑器中的内容，单独分表存储
	3.1 常用类型：
		3.2.1 主键：id		int		不要设置长度
		3.2.2 用户id:user_id		int		
		3.2.3 用户名：username	varchar(30)	根据用户名规则长度
		3.2.4 昵称：nickname 		varchar(30)	根据注册规则定
		3.2.5 Ip地址：ip		varchar(15)
		3.2.6 记录时间：dated		datetime	
		3.2.7 修改时间：update_dated	datetime	
		3.2.8 生日：birthday		date	
		3.2.9 手机号：mobile		char(11)	如果要兼容国内外的和座机，可以用varchar
		3.2.10 链接地址：url		varchar(255)
		3.2.11 标题：title		varchar(255)
		3.2.12 图片地址：pic		varchar(255)
		3.2.13 描述：describe		varchar(255)
		3.2.14 内容：content		text		分到单独的表中
		3.2.15 状态：state		tinyint		每个值对应什么含义，在数据字典里标明
		3.2.16 类型：types		tinyint		同状态	
		3.2.17 是否显示：is_show	tinyint		0:不线上，1:显示，不要反过来	
	3.1 字段设计的几点禁忌
		3.3.1 每张表，必须加两个字段，一个id，自增长主键，一个dated，这条记录的生成时间，不管你有没有用到，必须加
		3.3.2 不要使用枚举类型或者set，使用tinyint来代替，否则数据量上去之后，想改就是个悲剧
		3.3.3 所有字段必须是not null，并且默认值不要给null
		3.3.4 text或者longtext的拆分到单独的表中存储，不要跟主逻辑表混一起
		3.3.5 图片等不允许用blob等存储图片内容，只存图片地址
		3.3.6 密码不允许明文存储，MD5(密码+定义的字符串密钥)
		3.3.7 单张表，字段数不要超过15个，超过的话，考虑拆分成多张表，例如：
			主表：posts:id,user_id,title,....,dated
			附属表，不常用字段 post_details:id,post_id,describe,...,dated
			大文本内容： post_contents:id,post_id,content,dated
			通过post_id来做关联，将最常用的字段放到主表里，不怎么用的字段放到detail表，长文本content单独一张表

四.命名规则
	4.1 库、表、字段、索引名等：只允许小写字母、_ 、数字组成，命名不要超过20个字符
	4.2 索引命名：idx_字段名_字段名 (太长的时候，对字段名采用简写)
	4.3 避免保留字命名
	4.4 表名最后加s，看表名，字段名，需要知道这张表是干什么的，不要起一些奇奇怪怪的名字
	4.5 同一个功能模块，采用统一前缀，比如特卖购物中，订单会有多个表：
		trade_totals:
		trade_alis:
		trade_details:
		trade_express:
		不要是下面这种命名
		total_trades:
		ali_trades:
		express:
		所有订单模块涉及的表，它的表前缀必然都是trade_
	4.6 数据字典维护，以及表注释维护
	4.7 常用表名

五.数据库规模控制
	5.1 除非是一些不重要的库，否则一个库最好是一个数据库实例(同一台服务器同一个端口)
	5.2 单个数据库，表的个数不超过300，总体大小，最好在100G以内
	5.3 数据表规模：
		5.3.1 log表等，只根据索引查询的，可以大点，超过4G，也不会有什么大的性能影响
		5.3.2 核心表，比如博客的文章表等，会有很多复杂查询等，最好不要超过1G，否则性能下降会非常严重(垂直分表)
		5.3.3 单张表，字段数不超过15个(log表等，不超过30个)
		5.3.4 学会预估表的大小，预估5年时间的数据量，可以在本地插入1000条左右的正常记录，看表的大小，然后预估
	5.4 表结构设计的时候，降低数据库规模
		5.4.1 只在数据库在可预期的时间里，可能会超大的时候才优化
		5.4.2 一个字段，如果只有表中的少量记录有值，拆分到一个新的表里，空间占用将会减少很多，比如tags
		5.4.3 log表等，可以定时备份清零

六.禁止以下写法，性能考虑
	6.1 【重要】order by rand 
	6.2 【重要】子查询
	6.3 【重要】联表查询
	6.4 【重要】不带limit的sql(除了insert)
	6.5 【重要】like '%username%' 这种前缀%的查询
	6.6 or的改写(尽量不要用or，实在没办法的时候，采用以下方法优化)：
		6.6.1 同一字段的不同条件，请采用in来优化，in的个数不要超过200个，否则性能下降很严重
		6.6.2 不同字段上的条件，使用union all，不要用union(union会去重，严重影响性能)
	6.7 不要出现select *，在一些大表中，这个会浪费各种字段(内存、带宽、cpu等等)，会导致一堆问题
	6.8 不要在sql中，使用mysql的内置函数，比如md5等等(绝对不能在索引列上使用)
	6.9 避免not , != , <> , !< 、!> 、NOT EXISTS 、NOT IN 、NOT LIKE 等语法
	6.10 少写大sql，或者让人看起来不易懂的sql
	6.11 不要用存储过程、触发器等，mysql做的并不好
	6.12 分页考虑大页码时候的优化(redis的有序集合非常擅长做这个)
	6.13 group by的时候，可以采用order by null 来去除排序(group by 本身会进行排序)

七.索引
	7.1 自增长id主键必须有
	7.2 字符串上建索引定长，不要全部索引(一般前8或者前10即可)
	7.3 不允许在索引列上，用mysql做运算(其他字段除非实在没办法，否则也不允许)
	7.4 不要使用外键(高并发，锁竞争会死的很惨，而且调bug很不方便，程序约束)
	7.5 explain必须要经常用，看懂基本的
	7.6 字段上越分散，索引效果越好(比如性别上单独建索引，性能提升基本没有，甚至出现性能下降)
	7.7 联合索引只能使用最左前缀
	7.8 覆盖索引速度最快
	7.9 不要出现重复索引(比如user_id上加了个索引，又在user_id上建个唯一索引)

八.sql规范
	8.1 sql语句采用小写
	8.2 字段名、表名必须加``包含起来，因为你不知道什么时候就会碰到mysql关键字
	8.3 容易导致条件不明显的加()区分先后顺序，谁要考验我逻辑运算先后顺序，我就会去考验他加班的耐性...
	8.4 尽量使用等值条件
	8.5 不要对字段使用函数，比如where abs(`num`)>3这种的
	8.6 union all代替union
	8.7 数据库配置统一
	8.8 A项目需要使用B项目中的数据库，不要直接连，采用B项目的接口形式
	8.9 insert的时候，必须指明插入哪些字段，否则改下表结构，就废了
	8.10 select的时候，取字段名数据，不要以数字下标，理由同insert

九.其他操作
	9.1 导入大批量数据
		9.1.1 使用load data比insert快20倍
		9.1.2 先不要索引，导入完之后，再建立索引会很快很多
		9.1.3 如果只用insert的话，一次insert多条，不要每次insert一条
		9.1.4 大批量的话，尽量不要在高峰期间执行
	9.2 insert select 可能导致复制异常
	9.3 较大项目的时候，读写分离
	9.4 不允许程序端对mysql加锁
	9.5 平时上phpmyadmin的话，上从库的，不要在主库在做操作
	9.6 对于不清楚性能的sql，在前面讲explain先看下查询计划，没问题之后再执行，要是不清楚的话，可以找其他人问下再确定是否执行
	9.7 执行大规模统计等的时候，严禁在浏览器中访问统计脚本
		9.7.1 见过最极端的是一个apache进程跑了差不多20000秒，6个小时
		9.7.2 浏览器里，执行的时候，卡住，很多人直接关浏览器，导致进程一直堵塞
		9.7.3 浏览器上如果有百度插件，你访问了一个连接，百度马上就会去爬下这个链接，也可能会死的很惨
		9.7.4 在服务器上，直接php static.php这样来执行，发现卡住，ctrl+c可以终止进程
		9.7.5 该统计脚本执行完之后，删除或者在最上面die掉，不允许一直留着
	9.8 搜索
		9.8.1 精确搜索，通过索引等解决
		9.8.2 非模糊前缀的，可以同上，通过索引解决
		9.8.3 模糊搜索，通过sphinx来建立索引，不要直接用mysql的
	9.9 定时脚本,禁止通过http访问
		if(isset($_SERVER['HTTP_HOST'])){
			die();
		}
	9.10 log表过大，备份，清空
		9.10.1 导出原表结构，并记录下来
		9.10.2 对原表进行rename
		9.10.3 根据原表结构，进行建表

附录：
1.不同类型数据比较
	字符列与数值型
	数值列与字符串
2.数值溢出，导致转换失败，从而不能使用索引，性能大降
	$sql = "update `users` set `name`='hello' where id='2147483648' limit 1";
	因为id是有符合整型，最大值是2147483647，而sql中的值超过上限，转换失败，update的时候，因为没有转成整型，索引使用失败，全表扫描
	没有超过上限的话，加不加单引号，效率基本一致
3.emoji 表情符，MYSQL版本不支持的话，需要预先转换再存入数据库中，否则会被截断，可以参考文档
	mysql内容截断原因.wps
	手机聊天表情
4.php中的mysql_query
	mysl_query执行之后，其实跟数据库打交道的事情就已经解决了，后面mysql_fetch_rows等的时候，只是把内存中的数据读出来
	所以不要抱着侥幸心理，我只是query了一下，没取出来，所以对数据库没影响