数据库授权，数据索引，慢日志，优化查询及数据库锁

1.mysql创建用户和授权

1.1权限管理

　　权限最高的是root用户，能够进行包括select、update、delete、update、grant等操作。一般只用专门的DBA工程师才有此权限，对于一般用户，工程师会给创建一个账号，设定某些权限进行数据库的相关操作

2.流程

　　root用户下创建账号，对用户账号增删改

# 进入mysql数据库
use mysql

# 进入后一定要进行刷新
flush privileges

# 创建用户
指定ip：192.118.1.1的mjj用户登录
create user 'mjj'@'192.118.1.1' identified by '123';

指定ip：192.118.1.开头的mjj用户登录
create user 'mjj'@'192.118.1.%' identified by '123';

指定任何ip的mjj用户登录
create user 'mjj'@'%' identified by '123';

# 删除用户(记得是在root用户下)
drop user '用户名'@'IP地址';

# 修改用户
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

# 修改密码
set password for '用户名'@'IP地址'=Password('新密码');

　　对当前用户授权处理

# 查看权限
show grants for '用户'@'IP地址'

# 授权mjj用户井队db1.t1文件有查询，插入和更新的操作
grant select,insert,update on db1.t1 to "mjj"@'%';

# mjj用户对db1下的t1文件有任意操作
grant all privileges on db1.t1 to "mjj"@'%';

# mjj用户对所有数据库中文件有任何操作
grant all privileges on *.*  to "mjj"@'%';

# 取消mjj用户对db1的t1文件的任意操作
revoke all on db1.t1 from 'mjj'@"%";  

# 取消来自远程服务器的mjj用户所有数据库的所有的表的权限
revoke all privileges on *.* from 'mjj'@'%';

3.mysql备份命令行操作

# 备份：数据表结构+数据
mysqdump -uroot db1 > db1.sql -p


# 备份：数据表结构
mysqdump -uroot -d db1 > db1.sql -p

#导入现有的数据到某个数据库
#1.先创建一个新的数据库
create database db10;
# 2.将已有的数据库文件导入到db10数据库中
mysqdump -uroot -d db10 < db1.sql -p

表的导入与导出，以及数据库的迁移

2.mysql索引原理

2.1介绍

　　一般应用系统读写比例在10:1左右，因此查询语句的优化相当重要，对于加速查询，这里就需要用到索引

　　在mysql中索引也被称为‘键’，利用索引能够提高查询速度（数据量越大越明显）

　　索引的作用在于约束和加速查找

2.2原理

索引本质：

　　　通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引分类：

　　hash类型索引：查询单挑快，范围查询慢

　　Btree类型索引：b+树，层数越多，数据量指数级增长

 

数据库的索引这里不做介绍，详细解释猛戳这里

3.索引的正确使用

使用中注意

?

1 2 3 4 5 6

1.避免使用select* 2.避免使用count(*)，可以考虑用count(1)代替 3.创建表时尽量使用char代替varchar(char节省了时间，但是消耗空间) 4.尽量使用短索引 5.组合索引代替多个单列索引 6.使用连接（join）代替子查询

4.慢日志查询　　

开启慢日志查询，可以让mysql记录下查询超过指定时间的语句，通过定位分析，从而优化数据库系统性能

(1) 进入MySql 查询是否开了慢查询
         show variables like 'slow_query%';
         参数解释：
             slow_query_log 慢查询开启状态  OFF 未开启 ON 为开启
        slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）

（2）查看慢查询超时时间
       show variables like 'long%';
       ong_query_time 查询超过多少秒才记录   默认10秒 

（3）开启慢日志（1）（是否开启慢查询日志，1表示开启，0表示关闭。）
           set global slow_query_log=1;
（4）再次查看
              show variables like '%slow_query_log%';

（5）开启慢日志（2）：（推荐）
         在my.cnf 文件中
         找到[mysqld]下面添加：
           slow_query_log =1
   　　　　 slow_query_log_file=C:\mysql-5.6.40-winx64\data\localhost-slow.log
    　　　  long_query_time = 1

    参数说明：
        slow_query_log 慢查询开启状态  1 为开启
        slow_query_log_file 慢查询日志存放的位置
        long_query_time 查询超过多少秒才记录   默认10秒 修改为1秒

5.分页性能优化

一般对于分页查询我们采用的方式

第1页：
select * from userinfo limit 0,10;
第2页：
select * from userinfo limit 10,10;
第3页：
select * from userinfo limit 20,10;
……

这种分页查询对于页数较小的情况下影响并不大，但是在页数很多的情况下进行分页，明显会发现查询时间会增加，越往后去查询，它都会进行全文扫描

优化方案

（1）只有上一页和下一页
        做一个记录：记录当前页的最大id或最小id
        下一页：
        select * from userinfo where id>max_id limit 10;

        上一页：
        select * from userinfo where id<min_id order by id desc limit 10;


  (2) 中间有页码的情况
           select * from userinfo where id in(
               select id from (select * from userinfo where id > pre_max_id limit (cur_max_id-pre_max_id)*10) as A order by A.id desc limit 10
           );    

 6.数据库锁

基本使用语法规则：

当这一方还未commit提交的时候，另一方进行数据库操作就会hang住，直到这一方提交才能操作

数据库锁类型还有很多，具体参见