目录

MySQL使用总结

MySQL使用总结

Mysql创建数据库

1
2
create database sina default character set utf8mb4 collate utf8mb4_unicode_ci;   
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;  

使用utf8mb4而不是utf8 ,应为utf8的长度不确定,utf8编码占用3个字节,utf8mb4能保证长度的够用, 5.5.3 版本以后的mysql版本开始支持utf8mb4

Mysql导入导出

  1. 导出所有库

    1
    
    mysqldump -uusername -ppassword --all-databases>all.sql  
    
  2. 导入所有库

    1
    
    mysql>source all.sql;
    
  3. 导出某些库

    1
    
    mysqldump -uusername -ppassword --databases db1 db2 >db1db2.sql 
    
  4. 导入某些库

    1
    
    mysql>source db1db2.sql 
    
  5. 导入某个库

    1
    
    mysql -uusername -ppassword db1<db1.sql;  mysql>source db1.sql;  
    
  6. 导出某个库

    1
    
    mysqldump -uusername -ppassword db1 table1 table2>tb1tb2.sql  
    
  7. 导入某些数据表

    1
    2
    3
    
    mysql -uusername -ppassword db1<tb1tb2.sql  
    mysql>user db1;  
    mysql>source tb1tb2.sql; 
    
  8. mysqldump字符集设置

    1
    
    $mysqldump -uusername -ppassword --default-character-set=utf-8 db1 table1>tb1.sql  
    

Mysql用户管理

  1. 创建用户
    1
    2
    
    mysql> insert into mysql.user(Host,User,Password) values("localhost","phplamp",password("1234"));  
    mysql>grant all privileges on *.* to [email protected] identified by ‘123′;  
    
  2. 修改用户密码
    1
    2
    
    mysql> UPDATE mysql.user SET password=PASSWORD(’新密码’) WHERE User=’root’;  
    mysql> FLUSH PRIVILEGES;  
    
  3. 显示当前用户
    1
    
    mysql>select USER(); 
    

mysql大小写敏感配置

  1. 查看
    1
    
    show global variables like '%lower_case%'
    
  2. 配置
    1
    2
    3
    
    #0-大小写敏感,1-不敏感(先把表名转为小写,再执行操作)。
    low_case_table_names = 0 或lower_low_case_table_names_low_case_table_names =1 #my.cnf文件下
      
    

Mysql启用远程

1
2
3
4
5
6
7
cd /etc/mysql  
vi my.cnf  #注释bind-address  
mysql -uusername -ppassword #进入到mysql  
grant all privileges on *.* to [email protected]'deniedhost' identified by 'password';  
GRANT ALL PRIVILEGES ON *.* TO [email protected]'%' IDENTIFIED BY ‘pass_word’;  #if dynamic ip  
flush privileges;  
quit;  

Mysql 主从复制

  • mysql复制的用途:
  • 读取扩展
  • 主备份服务器
  • 故障转移服务器
  • 地理空间冗余
  • 数据仓库
  • 基准测试
  • 软件更新
  • 复制配置(my.cnf):
  • 主服务器配置(Alpha)
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
[mysqld]   
log-bin=binary-log #log-bin参数启动二进制日志.二进制日志在MySQL环境中提供了多种用途.它为MySQL复制提供了DDL和DML语句流,能够重新应用于复制的从服务器.  
server-id=1   
重新启动MySQL后 ,使用SQL命令show master status;进行验证.(对于配置复制的从服务器是必要的)   
从服务器配置(Beta)   
[mysqld]   
server-id=2   
read_only=TRUE   
show slave status;   
change master to  
MASTER_HOST='192.168.100.1',#主服务器上的用户名和密码   
MASTER_USER='rep1',   
MASTER_PASSWORD='rep12009';   

change master to   
MASTER_LOG_FILE='binary-log.00001',#主服务器上运行show 
 master status命令所检索到的日志文件名称和位置.       
MASTER_LOG_POS=106;   

slave start;#启动从服务器验证其操作   
show slave status;#Slave_IO_Running和Slave_SQL_Running的值为Yes,就证明"主服务器-从服务器"式的MySQL拓扑已经正常运行.   

测试MySQL复制

在alpha上测试下面的代码:

1
2
PROMPT alpha>; 
alpha>   

确认从服务器上什么都不存在:

1
beta> show schemas;   

Mysql 创建视图

1
2
3
create view v as select * from table;  
create view v as select id,name,age from table;  
create view v[vid,vname,vage] as select id,name,age from table;  

ubuntu mysql5.7解决不用密码也能登陆:

1
2
3
4
5
6
7
8
登录mysql    mysql -u root -p 或 mysql
use mysql;
update user set authentication_string=PASSWORD("密码") where user='root';
update user set plugin="mysql_native_password";
flush privileges;
quit;
/etc/init.d/mysql restart;
mysql -u root -p 密码;

on,where,having的区别

on,where,having这三个都可以加条件的子句中,on是最先执行,where次之,having最后.有时候如果这先后顺序不影响中间结果的话,那最终结果是相同的.但因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的. 根据上面的分析,可以知道where应该比having快一点,应为它过滤数据后才进行sum,所以having是最慢的.但也不是说having没用,因为有时在步骤三还没有出来都不知道那个记录才符合要求时,就要用having了. left join加上where 条件
用where是先连接然后再筛选
用and是先筛选再连接
数据库范式:
第一范式:强调的是列的原子性.
第二范式:首先使1NF,另外包含两部分内容,一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分.
第三范式:首先是2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖.即不能存在:非主键列A依赖于非主键列B,非主键列B依赖于主键的情况.

查询优化

  1. jvm调优,内存结构
  2. 内存碎片问题
    MajorGC是清理老年代 MinorGC是从年轻代空间(包括Eden和Survivor区域)回收内存。 FullGC是清理整个堆空间包括年轻代和老年代。
  3. mysql执行计划,查询慢
  • MySQL查询优化:
  • 查询性能衡量标准
    • 查询时间
    • 慢查询(几乎所有大道秒级别的查询都可以认为比较慢)
    • 执行计划
      • type查询的方式
      • key使用的索引
      • Rows结果集大小
      • Extra提示信息
  • 影响查询性能的因素
    • SQL解析时间#使用prepared stmt语句减少重复SQL的解析
    • 查询优化算法
      • 执行计划分析
      • 索引优化
        • 表优化(建主键unsigned int)
        • 表优化(字段尽量使用NOT NULL)
        • 表优化(能使用enum的尽量不要使用varchar)
        • 表优化(ip字段使用unsigned int并使用INET_NTOA和INET_ATON)
        • 索引优化(为频繁搜索的字段建立索引)
        • 索引优化(为varchar text建立全文索引,避免使用 like)
        • 索引优化(避免使用blob字段,该字段只能建立前缀索引)
        • 索引优化(最多匹配原则和最高区分度原则)
      • 查询优化
        • 索引字段不参与计算,否则索引失效
        • 避免使用 select*,select count(*)等
        • 知道结果数量的使用,使用limit,尽早结果过程。
  • Query Cache的使用#关了吧
    • 磁盘IO次数#IO优化,增大buffer pool,开取MRR,避免select *
    • 事务键锁的影响#锁优化:避免使用大事务,RC比RR好,不适用GAP锁,避免使用select *,可能会锁权标
  • join优化#利用index nexted-loop join算法,在没有索引的情况下,合理设置join_buffer_size.
  • 走索引OR全表扫描
  • 结果集大小与运算过程
  • 查询计划分析
  • 查询优化技巧
    MySQL执行计划
1
2
3
4
5
6
7
  语法:
     EXPLAIN select ...
  变体:
     1.将执行计划“反翻译”成SELECT 语句,运行show warnings 可得到MySQL优化器优化后的查询语句
      EXPLAIN EXTENDED select ...
    2.用于分区表的EXPLAIN
      EXPLAIN PARTITIONS SELECT ...

执行计划包含的信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
    id:包含一组数字,标识查询语句中执行SELECT子句或者操作表的顺序。id相同,执行顺序由上至下。若是子查询,id序号递增,id值越大优先级越高,越会被执行  
    select_type:表示每个select子句的类型(简单或复杂)  
    SIMPLE:查询中不包含子查询或者UNION  
    PRIMARY:查询中包含任何复杂的子部分,最外层查询则被标记为该类型   
    SUBQUERY:在SELECT或where列表中包含了子查询,该子查询就被标记为SUBQUERY   
    DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)   
    UNION:若第二个SELECT出现在UNION之后,则被标记为UNION    
    UNION RESULT:从UNION表获取结果的SELECT被标记为UNION RESULT   
    type:标识MySQL在表中找到所需行的方式,又称为“访问类型”   
    这列很重要,显示了连接使用了哪种类别,有无使用索引.   

从最好到最差的连接类型为const、eq_reg、ref、range、index和AL mysql explain用法和结果的含义:https://www.cnblogs.com/yycc/p/7338894.html MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE

不走索引情况

1
2
3
4
5
6
7
8
select * tb1 where name like ‘%comnn’ %再在前不走索引
select * tb1 where reverse(name) = ‘xu’ 条件出现函数运算不走索引
使用 or 不走索引     特别的: 当or条件中有 未 建立索引的列,会走索引
数据类型不一致   select * tb1 where name = 0999
!=      特别的: 如果是主键 索引生效
 >        特别的: 如果是主键 或者索引是int类型 索引生效
 order by
 选择的映射是索引才生效    特别的: 如果是主键 索引生效

参考
无法启动mongodb的解决方法
启用远程mysql