MySQL使用总结

2014-04-04 约 3348 字预计阅读 7 分钟次阅读

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

mysqldump -uusername -ppassword --all-databases>all.sql

导入所有库
1

mysql>source all.sql;

导出某些库

1

mysqldump -uusername -ppassword --databases db1 db2 >db1db2.sql

导入某些库
1

mysql>source db1db2.sql

导入某个库

1

mysql -uusername -ppassword db1<db1.sql;  mysql>source db1.sql;

导出某个库

1

mysqldump -uusername -ppassword db1 table1 table2>tb1tb2.sql

导入某些数据表

1
2
3


mysql -uusername -ppassword db1<tb1tb2.sql  
mysql>user db1;  
mysql>source tb1tb2.sql; 

mysqldump字符集设置

1

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

Mysql用户管理

创建用户

1
2


mysql> insert into mysql.user(Host,User,Password) values("localhost","phplamp",password("1234"));  
mysql>grant all privileges on *.* to jee@localhost identified by ‘123′；  

修改用户密码

1
2


mysql> UPDATE mysql.user SET password=PASSWORD(’新密码’) WHERE User=’root’;  
mysql> FLUSH PRIVILEGES;  

显示当前用户
1

mysql>select USER();

第一次启用用户进入到/etc/mysql/debian.cnf查看用户名和密码（）后临时登录到mysql shell，运行一下命令，设置root用的密码为123456。

1
2
3
4
5
6


 use mysql;
 // 下面这句命令有点长，请注意。
 update mysql.user set authentication_string=password('root') where user='root' and Host ='localhost';
 update user set plugin="mysql_native_password"; 
 flush privileges;
 quit;

mysql大小写敏感配置

查看

1

show global variables like '%lower_case%'

配置

1
2
3


#0-大小写敏感，1-不敏感（先把表名转为小写，再执行操作）。
lower_case_table_names = 0 或lower_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 username@'deniedhost' identified by 'password';  
GRANT ALL PRIVILEGES ON *.* TO user_name@'%' 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依赖于主键的情况.

查询优化

jvm调优，内存结构
内存碎片问题
MajorGC是清理老年代 MinorGC是从年轻代空间（包括Eden和Survivor区域）回收内存。 FullGC是清理整个堆空间包括年轻代和老年代。
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

目录

MySQL使用总结

Mysql创建数据库

Mysql导入导出

Mysql用户管理

mysql大小写敏感配置

Mysql启用远程

Mysql 主从复制

测试MySQL复制

Mysql 创建视图

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

on,where,having的区别

查询优化