每日归档： 2017年4月21日

控制开发者权限只给DELETE, INSERT,UPDADE,SELECT，

excute, create routine

不让开发瞎鸡巴搞（我是开发）

不给开发更改存储过程权限

线上最小权限

任何变更是dba需要知道

load date in file 权限生产环境应该避免掉，包括自己的账号，

为什么要用mysql一个无效的用户来跑mysqld呢，

因为早些年出现好多人利用load data把/etc/passwd加载到数据库拿到password 暴力破解

现在不存在了 

现在password放到好像是shadow里拿不到 

有这个权限也很悲剧

可以通过select into 一个 file 创建一个shell 给系统创建一个账号

所以需要注意

drop权限也要注意 避免被sql入侵把表drop掉

lock tables 一般情况下不建议开发在程序中使用 酌情考虑

GRANT OPTION 

给了一个给别人授权的权限

show grants for 'root'@'localhost';

PROCESS

show processlist; 显示有哪些线程在运行

RELOAD

数据库重新加载

flush tables; flush logs; flush开头的

replication client VS replication slave  （虽然这里我还不懂）

replicaation client 

show master status;

show slave status;

replication slave复制传输用的

SHUTDOWN

mysql_multi里面配置的user 需要给配置这个

SUPER

当所有连接占完了会给SUPER权限保留连接

ALL

all 是除了grant option 之外的权限

USAGE 只能连上 啥也干不了

mysql 5.6 引入了一个proxy user

但要买官方的插件

select user,host,password from mysql.user;

mysql.db

我们测试库里mysql.columns_priv里数据貌似是空的

keepalived 密码是８个字符

mysql 用户名是１６个字符以下

超过了会造成内存溢出

'leokim'@'192.168.11．％'

mysql -S /path/mysql.sock -uwubx

mysql -h192.168.11.20 -uwubx -p

client：192.168.11.100

server 192.168.11.20

mysql -h192.168.11.20 -uwubx -p 这个时候用哪个密码呢？

匹配原则按最精确的情况匹配（wubxwubx）

drop user 'wubx'@'192.168.11.%';

云类的数据库对资源限制

每小时允许做多少次query，update等

max_user_connections

max_connections=1100

max_user_connections=500

控制连接数

忘记root密码怎么处理

核心是让mysqld重新加载一下权限表

kill -SIGHUP

mysql 5.6可以让一个密码过期 

mysql　5.6以后的客户端

可以让密码过期 直接登录 修改密码

show grants for 'wubx'@'localhost';

mysql手册第5，6章很值得看

可以用iptables

在数据库里

grant usage on *.* to 'admin'@'192.168.10.15' identified by 'wubxwubx';

information_schema.processlist

找到ip干掉

索引就是目录检索系统

IOT : index cluster table

表要聚集索引排序后存储

sql语句中带where条件的都会用到索引

idx_abc(a,b,c)—–》(a), (a,b), (a,b,c)

select * from tb where a=xxx; 可以用到索引

select * from tb where b=xxx; 不能用到索引

加速join操作

where a.c_id = b.d_id 要求2个数据类型是一样的

如果不一样也用不到索引比如c_id mediumint，d_id int 2个去join不能进行转换成为临时表

char和varchar 大小一样的情况下可以用到索引 但是只要size定义不一样则不能用到索引

join的场景要求两个类型一样的同时要求size一样才能用到索引

排序可以用到索引

idx_abc(a,b,c)

select a,b,c from tb where a=xxx;

索引包含（Innodb的B+tree，索引内包含数据）

一个表有多列索引怎么选择

select * from tb where c1=xxxx and c2=xxx;

c1 varchar(10)

c2 int(10)

idx_c1(c1)

idx_c2(c2)

查询优化里有一个原则：基于成本优化

统计视图

假设成查询成本是１

c1 string 　0.9

c2 int 0.6

mysql认为这个计划都符合要求，就会从上到下取第一个，而我们这里c1比c2靠前所以优先用c1

这就是mysql的 成本优化器

5.7这块可能有变化

trace

《sql server 查询》

基于成本优化内容

R-Tree只在MyISAM里面的地理数据里支持

主建和唯一索引有什么区别吗

主键不允许null，唯一索引允许null

普通索引对null没有要求

使用上会有区别

where col1  =xxx ;

普通索引不能保证数据不重复还会往下面继续查

所以普通索引会比主键和唯一索引多一点io开销 

核心类和高并发尽可能用主键或者唯一索引

（tokudb可以有多个主键多个class index）

Innodb只允许有一个主建

数据是以主键为顺序排序存储的

Innodb表以主建排序存储，如果没有主建了怎么办？

如果没有主建，会选择第一个（创建的顺序）唯一索引，升级为主建

如果这个表连唯一索引也没有怎么办？

自动创建一个6个字节长度的主键

6个字节是很长的成本比较高

idx_

udx_

fdx_

前缀索引貌似是mysql的一个特色

email(64) 用20位就已经可以区别出来了就可以创建

create index idx_email on tb(email(20));

让索引使用最少的空间来减少io

联合索引

(c3,c4)

update tb set c6=xxx where c3=xxx and c4=xxx;

如果创建索引了 就不要再这个列上经常更新

update set email

改索引后需要先改字段然后进行索引排序然后再把数据弄过去，可能涉及到拆页分页

所以要避免更新索引列

全文索引

目前（5.6）不支持中文

5.7后貌似支持中文了

http://www.actionsky.com/docs/archives/163

CJK亚洲文字

create fulltext index idx_c5 on yw(c5);

MySQL 5.6 innodb增加了全文搜索

唯一索引（=）效过最好 ，但是也支持区间（>,between, in）

主键定义删除创建智能用lalter table一般都是在表创建的时候去定义，这个动作很大需要copy表空间，生产环境中基本不去动主键这个事情。

isopen

type

flag

这些列不太适合做索引，基数太少了

比如说type就3个值那也就是30%的比例

mysql扫描不低于30%（打比方的值）都不会走索引

这个索引是无效的

如果标志达到4个或者5个以上索引可能还是可以用到的

选择基数比较大的列做索引，或者重复比较少的。

我们目前的status_id呀，type_id呀,action_id呀之类的这些标志我得找个时间去测试一下效果如何

只有基于唯一索引或主键上等于类的操作才能做到行级锁，这部分非常复杂

gap

next key

idx_ab(a,b)

idx_a(a)

后面会有工具来帮助排查

《基于Innodb最佳实践》

无序主键 rand出来

写入速度很慢 1s只能一两百

顺序索引/s能上万

索引无序写入会造成写入速度降低

update 尽可能不要update索引列

5.7对这块update做了一些合并的维护

字符型的索引，是怎么查找的？

字符串比较btree的字符串比较定位到值在哪

逻辑结构

1. 客户端：php，java…应用程序

2. 通过drive连接到mysql，mysql线程池

3. sql形成MD5/hash的key去查缓存（query cache）是否有这个结果

4. sql分析器，进行优化找到最优执行路径

5. 到存储引擎拿数据

服务器进程

1. 连接层：连接接入，认证

2. SQL层：语法校验

3. 存储层：DBA应该最关注的地方

数据库优化的本质: 

减少IO

把随机IO想办法转成顺序IO

连接层

通信协议

线程

验证

OneSQL就是在连接层把thread-pool做了一个优化，官方的thread-pool也差不多

SQL层：SQL解析，授权，优化器，查询执行，查询日志等等

SQL语句处理过程

连接mysql后查询query cache

query cache是没有经过语法解析的

select * from tb1 where id=1; 和select * from tb1 where Id=1; 

实际生产中是禁掉query cache

query cache是mysql一个性能瓶颈形成全局锁

整体模块结构

Oracle官方有一个去IOE团队推ndb cluster 哈哈哈

日志

1. 错误日志

2. 慢日志（slow_query_log）

3. 二进制日志（binary log）

4. 一般日志（general log）

slow_query_log_file 

set global general_log=1; 

select sleep(3); 

set global general_log=0;

MySQL基于存储引擎的一个解决方案

官方的存储引擎

不支持事物：MyISAM, Memory, Archive(只写，不允许修改删除，日志审计) 

锁的粒度：Innodb,NDB 行级锁

是否支持压缩

innodb 5.1.37引入innodb plugin 这个开支持压缩

myisam 5.6开始支持英语全文搜索

Innodb 5.6 开始支持英语全文搜索

批量写的速度

MyISAM,Memory的批量写入比较快

Innodb的Bulk insert (load data) 在5.7之前没有MyISAM,Memory快

原因是因为不支持批量更新索引

5.7之后改善

恢复到某个点 binlog

TokuDB

带压缩支持高速写入的一个引擎

Innodb/Tokudb压缩比是10:1

innodb表每秒2万

Tokudb可以达到4W

tokudb的insert特别快

percona enterpise

Percona MySQL 5.6 包含了TokuDB

MariaDB 中也含的有Tokudb

engine=Tokudb;

Percona Server TokuDB做为他的一个默认引擎

缺点

Tokudb不适合update多的场景

tokudb表20G

update col2=xxx ;

表由原来的20G表变成40G了

多引擎混用注意join

会形成大量移植表不推荐2种不同引擎join

MySQL不适合做OLAP?

Infrobright/InfiniDB

MySQL 5.1的时间都出现了

group ,order ,count之类聚集函数

OLAP 里的表,很多都是宽表

统计类的库（OLAP）一张表好几百列

针对业务去选择引擎

常用推荐：InnoDB

如果有大数据写入批量读取的操作Tokudb

针对OLAD可以考虑使用InfiniDB/Infrobright

推荐研究: InfiniDB，完全开源

infrobright 分企业版和社区版，社区版只支持load data操作

如果对数据量小，要求速度，无持久化要求：Memory

拒绝选用MyISAM

1. 只能用单个cpu

2. 内存只能用到4G

3. 内存里只有索引（B-tree）

4. 并发能力差