MySql 入门.md

官网下载有两个版本，一个是二进制分发版（.msi 安装文件），一个是免安装版（.zip 压缩文件）。二进制分发版和安装普通软件一样的，在选择需要安装的文件时，如果只是学习 MySql，可以只选择安装 MySQL Server。默认安装路径是：C:\Program Files\MySQL\MySQL Server 5.6。8.x 版本的压缩包版本没有 .ini 文件，和 5.x 版本的不一样，配置方式也不一样，以下内容以 5.x 版本为例。

目录	备注
bin目录	存储可执行文件
data 目录	存储数据文件
include 目录	存储包含的头文件
lib 目录	存储库文件
docs 目录	文档
share 目录	错误信息和字符集文字
my.ini文件	MySQL的配置文件，默认的是 my-default.ini，需要复制并重命名为 my.ini	设置字符集	客户端字符集[mysql]：default-character-set=utf8
			服务器端字符集[masqld]：character-set-server=utf8

运行 MySql 的步骤：

配置环境变量，在 Path 中添加一条数据，即 MySql 的 bin 目录，C:\Program Files\MySQL\MySQL Server 5.6\bin
开启 MySql 服务，安装的时候默认会添加一个名为MySQL56的服务，开启服务有两个方式，一是打开系统的服务，手动开启；二是在控制台输入：net start mysql56，关闭服务是：net stop mysql56
配置编码字符集，避免显示乱码。需要在 my.ini 中添加两行：客户端[mysql] default-character-set=utf8 ，服务器端[mysqld] character-set-server=utf8
连接 MySql，需要在控制台输入：mysql -u root -p

1.Name：表名称
2.Engine：表的存储引擎
3.Version：版本
4.Row_format：行格式
5.Rows：表中的行数
6.Avg_row_length：平均每行包括的字节数
7.Data_length：整个表的数据量（单位：字节）
8.Max_data_length：表可以容纳的最大数据量
9.Index_length：索引占用磁盘的空间大小
10.Data_free：未使用空间
11.Auto_increment：下一个自增长值
12.Create_time：表创建时间
13.Update_time：表最近更新时间
14.Check_time：使用check table或myisamchk工具检查表的最近时间
15.Collation：表的默认字符集和字符排序规则
16.Checksum：启用则对整个表的内容计算时的检验和
17.Create_options：指表创建时的其他所有选项
18.Comment：包含了其他额外信息

表的分区

当数据量过大的时候，需要将一张表的数据划分几张表存储。一些查询可以得到极大的优化，这主要是有助于满足一个给定WHERE语句的数据可以值保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。

查看是否支持分区：SHOW VARIABLES LIKE '%partition%'; yes表示支持分区。

分区的分类：

RANGE分区：基于属于一个特定连续区间的列值，把多行分配给分区
LIST分区：类似与按RANGE分区，区别在于它是基于列值匹配一个离散值集合中的某个值来进行选择
HASH分区：基于用户定义的表达式的返回值来进行选择的分区
KEY分区：：类似与HASH分区，区别在于它只支持计算一列或多列

CREATE TABLE employees(
  id INT NOT NULL,
  fname VARCHAR(30),
  lname VARCHAR(30),
  hired DATE NOT NU;; DEFAULT '1970-01-01',
  separated DATE NOT NULL DEFAULT '9999-12-31',
  job_code INT NOT NULL,
  store_id INT NOT NULL
)

partition BY RANGE(store_id)(
  partition p0 VALUES LESS THAN(6),
  partition p1 VALUES LESS THAN(11),
  partition p2 VALUES LESS THAN(16),
  partition p3 VALUES LESS THAN MAXVALUE
);

-- 查看分区
SHOW CREATE TABLE employees\G

分区的删除：ALTER TABLE 表名 DROP PARTITION 分区名

增加分区：ALTER TABLE 表名 ADD PARTITION(PARTITION 分区名字 VALUES LESS THEN 具体的某个值)

内存优化

myisam内存优化：
1.key_buffer_size设置，决定索引块缓存区的大小，一般的myisam数据库，建议用1/4可用内存分配给key_buffer_size：
key_buffer_size=2G
2.read_buffer_size，经常顺序扫描myisam表，就需要设置，该值是每个session独占的，如果默认值设置太大，就会造成内存浪费。
3.read_rnd_buffer_size，需要做排序的myisam表查询，如带有order by子句的sql，适当增加该值。但需要注意，该值是每个session独占的，设置太大内存浪费。

innodb内存优化：
1.innodb_buffer_pool_size，决定表数据和索引数据的最大缓存区大小
2.innodb_log_buffer_size，决定innodb重做日志缓存的大小，对应有大量更新记录的事务，增加该值大小，可以避免在事务提交前就执行不必要的日志写入磁盘操作。

mysql并发参数：
1.max_connections，提高并发连接
2.thread_cache_size，加快连接数据库的速度，控制mysql缓存客户服务线程的数量
3.innodb_lock_wait_timeout，控制innodb事务等待行锁的时间

应用程序优化

1.访问数据库采用连接池优化，就是连接数据库的客户端放在一个连接池里。

2.采用缓存减少对mysql的访问：

避免对同一数据做重复检索，尽可能选择多个字段
使用查询缓存，比如一条SQL查询除了字段名和表明不一样，其他都一样，mysql就会把SQL语句1缓存，发给SQL2语句
缓存参数的配置
- query_cache_type：是否打开缓存，大小写要一样，以下是可选项
  - OFF ：关闭
  - ON：总是打开
  - DEMAND：只有明确写了SQL_CACHE的查询才会吸入缓存
- query_cache_size：缓存使用的总内存大小空间，单位字节，必须是1024的整数倍，否则MYSQL实际分配可能跟这个数值不同
- query_cache_min_res_unit：分配内存块时的最小单位大小
- query_cache_limit：MYSQL能够缓存的最大结构，如果超出则增加query_not_cached的值，并删除查询结果
- query_cache_wlock_invalidate：如果某个数据表被锁住，是否仍然从缓存中返回数据，默认是OFF，便是仍然返回

3.负载均衡（读写分离）：一个主MYSQL服务器（MASTER）服务器与多个从属MYSQL服务器（STAVE）建立复制（replication）连接，主服务器与从属服务器实现一定程度上的数据同步，多个从属服务器存储相同的数据副本，实现数据冗余，提供容错功能。部署开发应用系统时，对数据库操作代码进行优化，将写操作（如UPDATE、INSERT）定向到主服务器，把大量的查询操作（SELECT）定向到从属服务器，实现集群的负载均衡功能。

如果主服务器发生故障，从属服务器将转换角色成为主服务器，是应用系统为终端用户提供不剪短的网络服务；主服务器恢复运行后，将其转换为从属服务器，存储数据库副本，继续对终端用户提供数据查询检索服务。

账号管理

查看用户：
mysql>USE mysql;
mysql>SELECT host,user,password FROM user;

1.GRANT命令使用

对于ZY用户，赋予SELECT的权限，能查看所有数据库和所有数据表
GRANT SELECT ON . TO ZY@'localhost' IDENTIFIED BY 'PWD' WITH GRANT OPTION;;

对应YZ用户，赋予test下索引数据表的索引权限
GRANT ALL PRIVILEGES ON test.* TO YZ@'localhost' IDENTIFIED BY 'PWD' WITH GRANT OPTION;;

ALL PRIVILEGES ：表示多有权限，你也可以使用SELECT、UPDATE等提到的权限
ON ：用来指定权限针对哪些库和表
test.* ：test数据库下的所有表
TO ：表示将权限赋予给哪个用户
YZ@'localhost' ：表示test用户，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方，但有的版本不包括本地，如果不包括需要再加一个localhost的用户
IDENTIFIED BY ：指定用户的登录密码
WITH GRANT OPTION ：表示该用户可以将自己拥有的权限

2.查看用户的权限：
SHOW GRANTS FOR ‘root’@'localhost'

3.删除用户，不仅仅要删除用户的名称，还要删除用户拥有权限。
使用DELETE删除，并不能删除权限，新建同名用户后会继承以前的权限，正确的做法是使用DROP命令：
DROP USER ‘zy’@'localhost' ;

4.修改密码
SET PASSWORD FOR 'ty'@'localhost'=password('123');

5.对账号权限的资源设置
GRANT SELECT ON employees.* TO test@localhost IDENTIFIED BY 'pwd' WITH max_queries_per_hour 5 max_user_connections 6;

设置test用户对employees数据库的权限，每小时查询的次数小于5次，最多有6个用户进行并发连接

MYSQL监控

随着软件后期的不断升级，mysql的服务器数量越来越多，软硬件故障的发生概率也越来越高。这个时候需要一套监控系统，当主机发生异常时，此时通过监控系统发现和处理。

1.常见监控方式的分类：下面语句没有分号;

自己写程序或者脚本控制
- 监控mysql是否提供正常的服务：mysqladmin -uroot -proot -hlocalhost ping，输出：mysql is alive
- 获取当前的几个状态值：mysqladmin -uroot -proot -hlocalhost status
- 获取数据库当前的连接信息：mysqladmin -uroot -proot -hlocalhost processlist
- 获取当前数据库的连接数：mysql -uroot -proot -BNe "select host,count(host) from processlist group by host;" information_schema
- 检查修复分析优化：mysqlcheck -u root -proot --all-databases
- 在客户端执行以下命令
  - 检查临时表是否过多：SHOW STATUS LIKE 'Created_tmp%'
  - 锁定状态：SHOW STATUS LIKE "%lock%"
  - Inonodb_log_waits反应Innodb Log Buffer空间不足造成等待的次数：SHOW STATUS LIKE 'Innodb_log_waits'
监控采用商业解决方案
监控开源软件

定时维护

mysql 设置定时器，从5.1开始才支持event的。查看版本SELECT VERSION();
1.查看是否开启event与开启event

查看evevt的状态：SHOW VARIABLES LIKE '%sche%'
开启evevt功能：SET GLOBAL event_scheduler=1;

2.创建定时器，创建事件test_event：

DROP EVENT IF EXISTS test_event;
CREATE EVENT test_event
ON schedule every 1 second
ON completion preserve disable
DO CALL p_test_proce();

-- 当为on completion preserve的时候，当event到期了，event会被disable，但该event还会存在

-- 当为on completion not preserve的时候，当event到期时，该event会被自动删除

-- p_test_proce是一个存储过程的名字

开启事件test_event：ALTER EVENT test_event ON completion preserve enable;
关闭事件test-event：ALTER EVENT test_event ON completion preserve disable;

备份与还原

1.备份：

通过mysqldump命令备份：
- 备份单个数据库：mysqldump -u username -p dbname [table1 table2] > backupName.sql
  - dbname 表示数据库的名称，table1 table2表示备份表名称，backupName.sql参数表设计备份文件的名称
- 备份多个数据库：mysqldump -u username -p --databases dbname1 dbname2 >backup.sql
- 备份所有数据库：mysqldump -u username -p -all-databases > bakcupName.sql
直接复制整个数据库目录
使用mysqlhotcopy工具快速备份

2.还原

使用mysql命令还原mysqldump备份的数据库：
- mysql -u root -p [dbname] < backupName.sql

mysql

MySql 入门.md

表的分区

内存优化

应用程序优化

账号管理

MYSQL监控

定时维护

备份与还原

相关

关于MySQL中的事务、提交（commit）、回滚（rollback）

MySQL索引类型

Windows7环境，Dbeaver 安装连接Mysql5.7

linux 恢复mysql 误删除数据

mac 安装mysql workbench8.0.23闪退。

Linux-Mysql 遗忘密码如何解决？

mysql数据库死锁的产生原因及解决办法

linux安装mysqlclient库

【Mysql学习】django.db.utils.OperationalError: (1045:Access denied for user 'roo

mysql改变配置文件后启动报错

mysql binlog恢复命令

MySQL left join、right join和join的区别

标签