bo'o 自己在网上学到的：
create database +（数据库名称） 创建一个数据库
use 数据库名称 使用这个数据库
存在疑问 varchar后面那个值是什么意思
一、DDL
RENAME 只能改table
-create ：create table 表名( id int,name varchar(100),phone varchar(20) ); 类似于这种形式
可以用show create table 表名G G的作用是将查到的结构旋转90度变成纵向，加上更好看，按列显示
-drop：删除表 drop table (可以加if exists)表名，(危险，不可回滚，开发一般也没这个权限)，
-rename ：rename table 表名 to 新表名
-truncate：truncate [table] 表名，清空表中的数据(全表数据清空) 保留表结构。不可回滚，比delete快很多
-alter：(感觉像是修改的意思)
1.add 添加(字段，索引)
例：alter table 表名 add column(字段的关键字) city varchar(50);
2.modify/change：对当前字段修改，不能改索引，想改索引只能删了再创建
例：alter table 表名 modify column(字段的关键字) city varchar(30); //将刚刚的city从50变成30
alter table 表名 change column(字段的关键字) city city varchar(30); //change要写两次city
还可以进行语句合并：
alter table 表名 modify column city varchar(40),add city2 varchar(40); 既修改了city的大小又增加了一个字段
3.drop 删除(字段，索引)
例：alter table 表名 drop column(字段的关键字) city;
MySQL：多线程单 进程
二、存储引擎
MyISAM ：不支持事务，速度快一点，表级锁(力度大，并发情况差一些) 在公司里用的少一些
创建后会比InnoDB多两个文件，一个是MYD文件，存储的是表当中的数据文件，MYI存的是索引文件
InnoDB： 支持事务，速度慢一些，行级锁； 在公司里用的比较多，只会将信息存在ibd文件中
三、字段类型
-INT：整形，字段后面的括号数字没有任何意义，可以不写。
TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT 分别对应1，2，3，4，8个字节，对应存储范围xxx
-DECIMAL：定点型，既包括整数数据又包括小数数据，比较精确的值
alter table t2 add column id2 decimal(5,2) 增加一个字段，总共有5位，其中小数位占2位
-字符类型：
1.CHAR/VARCHAR：char是一个不变长度的定点类型,定义是多大就是多大 ，varchar是一个变长类型，如果是空就只有一个字节
大部分都可以用varchar代替char，除非确定长度才用char，varchar会用一个字节记录长度，
比如char(4)那么char永远是四个字节，而varchar(4)如果什么都不存就是一个字节，存四个字符就是5个字节，存四个以上也是5个字节，因为varchar要多一个字节来存长度
超过255要用两个字节存长度，varchar如果定义太大，磁盘空间不会影响，内存会受影响
2.TEXT：能存的内容比varchar更多，长度更长，也是变长的，不能设置NOTNULL和默认值
3.BLOB：记录二进制字符串类型，也是大字段类型。基本不怎么用
TEXT和BLOB只能建立前字段索引，不能建立整个字段值的索引，因为他们太长了，他们还有小字段和更大字段的定义，还可以扩展
-时间日期类：
1.DATE：从 '1000-01-01' to '9999-12-31'只有年份日期相关的数据 ->三个字节
2.DATETIME：从 '1000-01-01 00:00:00' to '9999-12-31 23:59:59' 比DATE增加了时间 ->5.6.4版本后，占用空间变小了
8个字节变成了5个字节，总体来说可以用DATETIME来代替TIMESTAMP
2.TIMESTAMP：从 '1970-01-01 00:00:00' UTC to '2038-01-19 03:14:07' UTC
四、权限管理
一层一层的 user，db，table，column 如果上层有权限下层一定有，如果上层无权限需要到下层继续判断
创建user
五、系统常用命令
-查看帮助信息：help命令 查看MySQL提供的所有帮助信息
help contents 查看server端的帮助信息
-show：
show databases：显示已经创建了多少数据库
show databases like 'test%' ：以test开头的数据库，可以进行模糊匹配
show create database 数据库名G：查看数据库的信息
show tables ： 可以查看一个数据库有几张表
show create table 表名G ：查看表的信息（show columns from 表名）
show table status like 't1'G ：查看t1这个表数据，索引所占空间，平均时长，行数等，这里面一定要加like不然会报错！
show index from 表名 查看一个表的索引信息
show grants for '用户'@'ip' 查看一个用户的权限
show session/global variables like 'XX' 显示会话值，什么也不加默认是session当前会话值
show full processlist ：查看当前连接的用户以及执行SQL，没有full可能因为SQL过长只打印部分内容
-set命令：
set session character_set_client='utf8' 直接set变量名字默认是当前会话，global是全局
某些变量的全局和当前是一样的 比如set global read_only=on,会将全局和当前会话都是on
set names utf8mb4：设置可以防止乱码的产生
六、Qunar数据库使用规范
1.基础规范
(1) 使用INNODB存储引擎并且使用业务不相关自增ID为主键。
(2) 表字符集使用UTF8／UTF8MB4字符集。
(3)所有表、字段都需要添加注释。推荐采用英文标点,避免出现乱码。
(4)禁止在数据库中存储图片、文件等大数据。
(5)每张表数据量建议控制在5000W以内。
(6)禁止在线上做数据库压力测试。
(7)禁止从测试、开发环境直连数据库。
2.索引规范
(1) 单张表中索引数量不超过5个。
(2) 单个索引中的字段数不超过5个。
(3) 索引名必须全部使用小写。
(4) 非唯一索引按照“idx_字段名称[_字段名称]”进行命名。例如idx_age_name。
(5) 唯一索引按照“uniq_字段名称[_字段名称]”进行命名。例如uniq_age_name。
(6) 组合索引建议包含所有字段名,过长的字段名可以采用缩写形式。例如idx_age_name_add。
(7) 表必须有主键,推荐使用UNSIGNED自增列作为主键。
(8) 唯一键由3个以下字段组成,并且字段都是整形时,可使用唯一键作为主键。其他情况下,建议使用自增列或发号器作主键。
(9) 禁止冗余索引。
(10) 禁止重复索引。
(11) 禁止使用外键。
(12) 联表查询时JOIN列的数据类型必须相同,并且要建立索引。
(13) 不在低基数列上建立索引,例如“性别”。
(14) 选择区分度大的列建立索引。组合索引中,区分度大的字段放在最前。
(15) 对字符串使用前缀索引,前缀索引长度建议不超过8个字符，需要根据业务实际需求确定。
(16) 不对过长的VARCHAR字段建立索引。建议优先考虑前缀索引,或添加CRC32或MD5伪列并建立索引。
(17) 合理创建联合索引,(a,b,c) 相当于(a) 、(a,b) 、(a,b,c)。(18) 合理使用覆盖索引减少IO,避免排序。
3.Qunar 字符集规范
(1) 表字符集使用UTF8,必要时可申请使用UTF8MB4字符集a.UTF8字符集存储汉字占用3个字节,存储英文字符占用一个字节。b.UTF8统一而且通用,不会出现转码出现乱码风险。c.如果遇到EMOJ等表情符号的存储需求,可申请使用UTF8MB4字符集。
(2)同一个实例的库表字符集必须一致,JOIN字段字符集必须一致
(3)禁止在字段级别设置字符集
注意：当进行赋值操作或者关联操作的时候，字符集的格式一定要相同，比如join的时候 on a.str = b.str 这两个str得属于同一字符集
alter table t1 charset utf8mb4 与 alter table t1 convert to charset utf8mb4
前者会设置之后写入的数据按照utf8mb4的格式存储，现有数据不变， 后者会使当前已有的和之后写的都会改
线下课：
order by要在group by后面，order by要放在最后 a as b 给a起一个别名b
date 书写类似于字符串
字符串类型一定要加单引号
正确的书写顺序：
5.select 6.distinct
1from
2where
3group by
4Having
7.order by一定要放在最后
慢查询：比如两个表连接后和不连接查询的结果一样就没必要连接再查询了，
在外连接中写成where，左连接和右连接会变成内连接。如果是and才是正常的
delete from a4 where id只要有id就删除
select 写在insert，update，delete中 两边都会上锁，效率特别低。老师写出来过p1故障！！！
只有select语句是快照，不会上锁
读锁别人只能读不能写，写锁别人又不能读又不能写
字段名字前面加idx，加索引的时候
/*--------------------------------------------------------------------------------------------------------------------------------------------*/
一、数据库表字段设计
1.库表设计
(1)将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据
(2)表的默认字符集指定UTF8MB4(特殊需求除外),无须指定排序规则
(3)主键用整数类型,并且字段名称用id,使用AUTO_INCREMENT数据类,并指定UNSIGNED
2.分表策略
(1)推荐使用HASH进行散表,表名后缀使用十进制数,数字必须从0开始
(2)按日期时间分表需符合YYYY[MM][DD][HH]格式,例如2017011601。年份必须用4位数字表示。例如按日散表user_20170209、按月散表user_201702
(3)采用合适的分库分表策略。例如千库十表、十库百表等
3.库表禁止
(1)禁止以非字母开头命名表名及库名
(2)禁止使用分区表
二、索引
1.B+Tree
–叶子结点包含所有数据
–内节点包含了一部分叶子结点的键值
–搜索的时间复杂度比较稳定
–所有记录节点都是按键值的大小顺序存放在同一层的叶子结点上，由叶子节点指针进行连接
2.索引类型
(1)聚集索引(主键索引) 其叶子结点包含(主键列值，事务ID，回滚指针，非主键列值)
直接在Clustered B+Tree上查询
(2)辅助索引(非主键索引) 其叶子结点包含 (索引列值，对应的聚集索引主键列值)
先在Secondary B+Tree上查询到主键，再在Clustered B+Tree上查询
还可分为
(1)单列索引
-由单个字段组成
-Where条件中的字段查询时可以应用到索引
-alter table product add index idx_orderno(orderno);
(2)联合索引：由多个字段组成
-alter table product add index idx_orderno_createtime(orderno,createtime)
就是第一个字段首先有序，然后在第一个字段相同的地方第二个字段也有序
-联合索引字段整体有序，左前缀字段有序
-联合索引可代替左前缀字段的单列索引
-查询条件中联合索引的左前缀为确定值，此联合索引可优化第二个字段排序
如果查询的时候 两个字段都有就会用到联合索引，或者对左前缀字段查询也会用到
(3)覆盖索引：
我们要查询的字段都在二级索引表中，不需要去主键索引去找数据
比如sql中只有联合索引的这两项就会是覆盖索引，或者有id(主键也可以)
说白了就是是否需要通过主键去找其他的·
用不到索引的情况：
–where条件中没有内容
–否定条件：<>(不等于)，not in，not exists
–join中连接字段类型(或字符集)不一致
–扫描内容超过全表的20%，->会自动进行全盘扫描
–where条件的字段存在函数运算 ->不支持函数索引
–like '%name' ->不支持前模糊匹配，可以进行后模糊匹配用前缀索引
–出现隐式字符类型转换
三、Explain:打印一个sql语句的执行计划
–id：查询编号
–select_type
(1)SIMPLE：简单查询
(2)PRIMARY：最外层的select
(3)SUBQUERY：子查询内层查询的第一个select
( 4)DERIVED：子查询派生表的select
–table：表名
–Type
(1)index:全索引扫描
(2)const:通过主键访问
(3)all:全表扫描
(4)range:索引范围扫描
(5)ref: 索引扫描，结果可能有多个匹配值
(6)eq_ref:索引扫描，唯一索引匹配值（唯一）
其中访问效率：const > eq_ref > ref > range > index > ALL
–possible_keys：可能使用到的索引
–key ：最终使用的索引
–key_len：索引长度(使用到的)
–rows：扫描行数，越少说明sql定位越精准，更好
–Extra
效率比较好：(1)Using index 所需要的数据只需要在Index 即可全部获得而不需要再到表中取数据(覆盖索引)
效率比较差：(1)Using filesort Query 中包含ORDER BY ，且无法利用索引完成排序操作的时候，
MySQL Query Optimizer 不得不选择相应的排序算法来实现。（并不一定代表磁盘排序）。
(2)Using temporary 使用临时表时出现，主要常见于GROUP BY 和ORDER BY 等操作中.
如何正确使用索引呢？
1.使用select count(*) from tb;
2.将select * 转换为具体的字段(这样可能就会用到覆盖索引)
3.连接查询的join字段需要添加索引（on两面的字段）
4.where条件的顺序与索引是否使用无关
5.尽量避免更新索引列值
6.在选择性高的字段上添加索引
7.如果能覆盖索引，尽量覆盖索引 就是在select 和 where(group by,order by) 字段取并集，如果都出现在一个索引中，就可以用到覆盖索引
8.order by后面字段尽量也要放到索引之中 where后面是等值条件，然后可以将后面的字段和orderby后面的字段变成一个联合索引
避免bad sql
1.避免使用子查询
2.不要在索引字段上使用函数
SELECT COUNT(*) FROM orders_process_history WHERE 1 =1 AND distribute_type =1 AND DATE( process_time ) = CURDATE( );，这样会让索引失效
3.避免like ‘%xxx%’,使用like ‘xxx%’
4.在业务上想办法去掉类似这样的查询条件：where status = 0；数据量可能太大了，所以可以再跟个条件，比如说时间什么的
四、命名规范
1.库表命名规范：
(1) 库名、表名必须使用小写字母,并采用下划线分割。
(2) 库名、表名禁止超过32个字符。
(3) 库名、表名必须见名知意。命名与业务、产品线等相关联。
(4) 库名、表名禁止使用MySQL保留字。（保留字列表见官方网站）
(5) 临时库、表名必须以tmp为前缀,并以日期为后缀。例如tmp_test01_20130704。
(6) 备份库、表必须以bak为前缀,并以日期为后缀。例如bak_test01_20130704。
2.Qunar 字段命名规范
(1) 字段名必须使用小写字母,并采用下划线分割,禁止驼峰式命名
(2) 字段名禁止超过32个字符。
(3) 字段名必须见名知意。命名与业务、产品线等相关联。
(4) 字段名禁止使用MySQL保留字。（保留字列表见官方网站）
3.Qunar 索引命名规范
1) 索引名必须全部使用小写字母,并采用下划线分割,禁用驼峰式。
(2) 非唯一索引按照“idx_字段名称[_字段名称]”进用行命名。例如idx_age_name。
(3) 唯一索引按照“uniq_字段名称[_字段名称]”进用行命名。例如uniq_age_name。
(4) 组合索引建议包含所有字段名,过长的字段名可以采用缩写形式。例如idx_age_name_add
开发过程中的注意问题：
(1)禁止select *，转化为需要的字段 ：1.无法覆盖索引2.查询时浪费数据库资源3.不利于业务开发维护表，*不能清楚表明该业务使用的字段有哪些
(2)避免出现大事务 1.容易出现死锁2.容易出现锁等待超时3.夹杂RPC的事务，还可能产生主从读数据不一致的情况(千万不要这样)
(3)多个事务写共同表时，保持顺序一致 1.锁交叉导致死锁
(4)事务传播级别为PROPAGATION_REQUIRES_NEW时，要注意子事务不要操作主事务锁住的记录。1.间接死锁，主挂起且有锁，子等主的锁。2.RR隔离级别下，主事务没提交，写的数据对子事务也是不可见的
(5)当开发出的服务提供给外界使用时（不管是给其他同事用还是提供界面用户点击调用），需要提前预估可能打到DB上的并发量。
如果总并发量超过30，则需要用队列或者其他方式做一层防护，不要把压力下放至DB。
(6)增删改查数据量较大时，需要跟自己业务线DBA协商，禁止擅自做主。1.不幸delete一个百十来G的大表会造成机器IO压力上涨，导致MySQL服务下降，甚至无法提供服务。
2.统计信息类sql一定要去离线库跑。
线下课：
B 树的非叶子结点也有数据，范围查询比较麻烦。B+树都在叶子结点，并且有双向的指针，更好的可以进行范围查询
两个range，比如in或者between建立联合索引 索引只能用到前面
两个表连接会先判断where条件
没有where条件是不会走索引的
五星级索引：大海捞针
如果select 在update中会加锁，所以我们可以先运行select 得到值，在调用update。
联合索引，选择性高的放前面
字段可以这样定义–not null default ''
尽量不要使用子查询~~~