一、数据库的分类；
关系型数据库： sql数据库
非关系型数据库： nosql数据库，not only sql数据库
关系型数据库：数据拥有固定的存储结构，通过库--表--行--列的方式存储，存储时会有表的结构化关系，过程如下：解析sql语句--连接层--磁盘存取--结构化成表，；
优势：1.容易理解，二维表的结构非常贴近现实世界，二维表格，容易理解；
2.使用方便，通用的sql语句使得操作关系型数据库非常方便；
3.易于维护，数据库的ACID属性，大大降低了数据冗余和数据不一致的概率；
瓶颈：1.海量数据的读写效率低，对于网站的并发量高，往往达到每秒上万次的请求，对于传统关系型数据库来说，硬盘I/O是一个很大的挑战。
2.高扩展性和可用性，在基于web的结构中，数据库是最难以横向拓展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库没有办法像web Server那样简单的通过添加更多的硬件和服务节点来拓展性能和负载能力。
非关系型数据库，就是为了加快客户端的访问速度才出现的，因为所有的非关系型数据库都是尽可能的将数据放到内存当中；非关系型数据库是以key:value的形式存储的；
非关系型数据库：NoSQL主要指那些非关系型的、分布式的，主要代表MongoDB，Redis、CouchDB。NoSQL提出了另一种理念，以键值来存储，且结构不稳定，每一个元组都可以有不一样的字段，这种就不会局限于固定的结构，可以减少一些时间和空间的开销。
二、Mysql下载安装
1.连接池：最上层负责和客户端进行连接，比如jdbc，odbj这样的数据库连接的API，在这一层有连接池的概念，类似于线程池，连接池可以同时处理很多个数据库请求。同时这一层有SSL的安全概念，可以确保连接是安全的；
2.SQL接口：当SQL语句进入MySQL后，会先到SQL接口中，这一层是封装层，将传过来的SQL语句拆散，将底层的结果封装成SQL的数据格式；
3.解析器：这一层负责将SQL语句进行拆分，验证，如果语句有问题那么就返回错误，如果没问题就继续向下执行；
4.优化器：对SQL查询的结果优化处理，产生多种执行计划，最终数据库会选择最优化的方案去执行，尽快返会结果。比如select a,b from c where d。在这里会先查询c表中符合d的数据并将他们的ab项进行投影，返回结果，并不会直接把整张表查出来；
5.缓存：对要查询的SQL语句进行hash后缓存，如果下一次是相同的查询语句，则在SQL接口之后直接返回结果；
6.存储引擎：MySQL有很多种存储引擎，每一种存储引擎有不同的特性，他们负责组织文件的存放形式，位置，访问文件的方法等等。比较常用的有innoDB，MyISAM，MEMORY等；
7.文件系统：真正存放物理文件的单位；
二
[root@my ~]# wget http://dev.mysql.com/Downloads/MySQL-5.7/mysql-5.7.12.tar.gz
[root@my ~]# wget https://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz/download ##需要使用windows下载，不能使用linux下载
[root@my ~]# yum -y remove mysql-* boost-*
[root@my ~]# rpm -qa mysql
[root@my ~]# rpm -qa boost
[root@my ~]# yum -y install gcc gcc-c++ ncurses bison libgcrypt perl cmake ncurses-devel
[root@my ~]# tar -zxvf boost_1_59_0.tar.gz
[root@my ~]# mv boost_1_59_0 /usr/local/boost/
[root@my ~]# groupadd mysql
[root@my ~]# useradd -r -g mysql mysql
[root@my ~]# tar zxvf mysql-5.7.12.tar.gz -C /usr/src/
[root@my ~]# cd /usr/src/mysql-5.7.12/ ##编译多次时，需要删除CMAKE的缓存，rm -rf /usr/src/mysql-5.7.12/CMakeCache.txt
[root@my mysql-5.7.12]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DMYSQL_TCP_PORT=3306 -DMYSQL_USER=mysql -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DENABLE_DOWNLOADS=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost -DSYSCONFDIR=/etc
注释：
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql [MySQL安装的根目录]
-DMYSQL_DATADIR=/mydata/mysql/data [MySQL数据库文件存放目录]
-DDEFAULT_CHARSET=utf8 [设置默认字符集为utf8]
-DDEFAULT_COLLATION=utf8_general_ci [设置默认字符校对]
-DMYSQL_TCP_PORT=3306 [MySQL的监听端口]
-DMYSQL_USER=mysql [MySQL用户名]
-DWITH_MYISAM_STORAGE_ENGINE=1 [安装MySQL的myisam数据库引擎]
-DWITH_INNOBASE_STORAGE_ENGINE=1 [安装MySQL的innodb数据库引擎]
-DWITH_ARCHIVE_STORAGE_ENGINE=1 [安装MySQL的archive数据库引擎]
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 [安装MySQL的blackhole数据库引擎]
-DWITH_MEMORY_STORAGE_ENGINE=1 [安装MySQL的memory数据库引擎]
-DENABLE_DOWNLOADS=1 [编译时允许自主下载相关文件]
-DDOWNLOAD_BOOST=1 [允许下载BOOST]
-DWITH_BOOST=/usr/local/boost [指定系统中存在的BOOST]
-DSYSCONFDIR=/etc [MySQL配置文件所在目录]
-DWITH_READLINE=1 [MySQL的readline library]
-DMYSQL_UNIX_ADDR=/var/run/mysql/mysql.sock [MySQL的通讯目录]
-DENABLED_LOCAL_INFILE=1 [启用加载本地数据]
-DWITH_PARTITION_STORAGE_ENGINE=1 [启动mysql的分区存储结构]
-DEXTRA_CHARSETS=all [使MySQL支持所有的扩展字符]
-DWITH_DEBUG=0 [禁用调试模式]
-DMYSQL_MAINTAINER_MODE=0
-DWITH_SSL:STRING=bundled [通讯时支持ssl协议]
-DWITH_ZLIB:STRING=bundled [允许使用zlib library]
[root@my ~]# make -j `cat /proc/cpuinfo | grep processor| wc -l`
[root@my ~]# make install
[root@my ~]# ls /usr/local/mysql
bin COPYING data docs include lib logs man mysql-test README share support-files
- 优化调整Mysql程序；
[root@my ~]# chown -R mysql:mysql /usr/local/mysql
[root@my ~]# cp /usr/src/mysql-5.7.12/support-files/my-default.cnf /etc/my.cnf
[root@my ~]# cp /usr/src/mysql-5.7.12/support-files/mysql.server /etc/init.d/
[root@my ~]# chmod +x /etc/init.d/mysql.server
[root@my ~]# cat <<END >>/usr/lib/systemd/system/mysqld.service ##编写服务控制脚本
[Unit]
Description=mysqldapi
After=network.target
[Service]
Type=forking
PIDFile=/usr/local/mysql/logs/mysqld.pid
ExecStart=/etc/init.d/mysql.server start
ExecReload=/etc/init.d/mysql.server restart
ExecStop=/etc/init.d/mysql.server stop
PrivateTmp=Flase
[Install]
WantedBy=multi-user.target
END
注解：
PrivateTmp=Flase ##此配置必须关闭，不然mysql连接文件mysql.sock文件会默认生成在以下位置/tmp/systemd-private-83bba738e8ff4837b5ae657eff983821-mysqld.service-BPxWpJ/tmp/mysql.sock，导致数据库无法连接，将此配置项关闭后，则文件正常生成在/tmp/mysql.sock
[root@my ~]# echo "export PATH=$PATH:/usr/local/mysql/bin/" >>/etc/profile
[root@my ~]# source /etc/profile
- 初始化Mysql数据库服务；
[root@my ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
注解：
--initialize-insecure ##禁用mysql的密码策略（密码复杂性等），--initializeaize是开启密码策略，自动生成密码在mysqld.log文件中
--user=mysql ##运行的账户
--basedir=/usr/local/mysql ##mysql的安装位置
--datadir=/usr/local/mysql/data ##mysql数据库服务数据的物理存放路径
[root@my ~]# cat <<END >/etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character_set_server=utf8
init_connect='SET NAMES utf8'
log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/logs/mysqld.pid
skip-name-resolve
END
注解：
[mysqld] ##声明区域
basedir = /usr/local/mysql ##mysql的安装位置
datadir = /usr/local/mysql/data ##mysql的物理文件存放位置
port = 3306 ##mysql服务监听的端口
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ##mysql的模式
character_set_server=utf8 ##字符集
init_connect='SET NAMES utf8'
log-error=/usr/local/mysql/logs/mysqld.log ##指定日志文件位置
pid-file=/usr/local/mysql/logs/mysqld.pid ##指定运行服务所产生的pid文件位置
skip-name-resolve ##跳过mysql的域名反向解析
[root@my ~]# mkdir /usr/local/mysql/logs
[root@my ~]# chown mysql:mysql /usr/local/mysql/logs/
[root@my ~]# systemctl start mysqld
[root@my ~]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@my ~]# netstat -utpln |grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2885/mysqld
- 测试连接访问数据库；
[root@my ~]# mysqladmin -u root -p password "123123" ##登录mysql设置密码
[root@my ~]# mysql -uroot -p123123
mysql> exit
六、Sql语句操作之增删改查；
Sql语句分类：
- DDL：数据定义语言，用来建立数据库，数据对象和定义其列，如create、alter、drop；
- DML：数据操纵语言，用来查询、插入、删除、修改数据库中的数据，如select、insert、update、delete；
- DCL：数据控制语言，用来控制数据库组件的存取许可，存取权限等，如commit、rollback、grant、revoke；
打开客户端并登陆mysql
linux下在任何位置直接打开终端即可
windows下使用各种客户端打开
登陆mysql命令 直接在终端下执行以下代码 然后输入密码
mysql -uroot -p
退出：
exit;
SQL语句规范
- 以;(分号)结尾
- 关键字之间有空格 通常一个空格 但是有多个也没问题
- sql语句中可以添加换行
- SQL 不区分大小写
数据库相关SQL
- 查看所有数据库命令：
show databases;- 创建数据库 通常一个项目只对应1个数据库 格式：create database 数据库名称;
create database db1;
指定字符集的创建格式 ：create database 数据库名称 CHARACTER SET utf8;
create database db2 character set gbk;
- 查看数据库详情 -格式：show create database 数据库名称;
show create database db1;- 删除数据库 -格式：drop database 数据库名称;
drop database db2;- 选中数据库
use db2;表相关SQL
什么是表
-表是数据库中的数据组成单位 类似于Java中的对象 表中的字段 对应对象中的属性
创建表
格式： create table 表名 (字段1名 字段类型，字段2名 字段类型);
create table person (name varchar(10),age int);
创建表的原理：
-在客户端中写完创建表的sql语句后客户端会把sql语句交给DBMS（MySql）DBMS解析后 会在数据库中创建语句中的表 和表中的字段
查看所有表
show tables;查看表结构
-格式：show create table 表名;
show create table person;
-格式： desc 表名
desc person
表的引擎
InnoDB：支持数据库的高级处理包括 事物 外键等 默认是innodb
Myisam：只支持数据的基本存储
创建表时指定表的字符集 和 引擎
-格式
create table person (id int,name varchar(10)) engine=myisam charset=utf8;练习：创建5个数据库 在每个数据库里面创建1个表 然后删除数据库练习
创建学生表
create table student (
id int,
name varchar(10),
chinese int,
math int,
english int
);对已经创建好的表进行修改
- 修改表名称 -格式： rename table 原名 to 新名;
rename table student to t_student;
- 修改表的属性 -格式： alter table 表名 engine=myisam charset=gbk;
alter table t_student engine=myisam charset=gbk;
- 添加表的字段 -在最后添加 -格式：alter table 表名 add age int;
alter table student add age int;
-在最前面添加 -格式：alter table 表名 add fatherAge int first;
alter table student add fatherAge int first;
-在某个字段之后添加 -格式：alter table 表名 add fatherName varchar(10) after fatherAge;
alter table student add fatherName varchar(10) after fatherAge;
- 删除表字段 -格式： alter table 表名 drop 字段名;
alter table student drop fatherAge;
- 修改字段名和类型 -格式： alter table 表名 change 原字段名 新字段名 新字段类型;
alter table student change age fatherAge varchar(10);
- 修改类型和顺序 -格式1： alter table 表名 modify 字段名称 新字段类型 after 字段名 -格式2： alter table 表名 modify 字段名称 新字段类型 first
alter table student modify fatherAge int first;
alter table student modify fatherAge int after id;
alter table dgf modify 姓名 varchar(20);
删除表
-格式： drop table 表名
drop table student;
练习：
1.创建一个hero表 有 id name type 三个字段
create table hero (id int,name varchar(10),type varchar(10));
- 修改hero表的属性 引擎为myisam 编码属性为utf8
alter table hero engine=myisam charset=utf8;
- 给hero表添加一个money int 字段 添加在name字段的后面
alter table hero add money int after name;
- 修改type字段 名称修改为 hero_type varchar(30)
alter table hero change type hero_type varchar(30);
- 修改hero表名为heros
rename table hero to heros;
- 修改name字段到最后面
alter table heros modify name varchar(10) after hero_type;
- 把money字段删除
alter table heros drop money;
- 删除hero表
drop table heros;
数据相关SQL
create table student(
id int,
name varchar(20),
chinese int,
math int,
english int
);
- 插入语句 -全表插入格式：insert into 表名 values(值1,值2,值3....); 全表插入values后面的值的数量和顺序必须和表中字段的数量和顺序一致
insert into student values(1,'lisi',18,26,88);
-指定字段插入格式：insert into 表名 (字段名1，字段名2)values(值1,值2);
insert into student (name,math)values('张三',90);
-批量插入
-全表批量 insert into student values(10,'刘备',10,20,30),(11,'关羽',12,34,22),(12,'张飞',3,4,2); -指定字段批量
insert into student (id,name,english)values(20,'孙悟空',0),(21,'猪八戒',90),(22,'沙师弟',5);
- 查询语句 -格式: select 字段1, 字段2,字段3... from 表名 -如果字段位置写* 代表查询所有的字段
select * from student;
select id,name,math from student;
- 更新(修改)语句 -修改全表格式：update 表名 set math=100;
update student set math=100;
-添加条件修改格式： update 表名 set math=50 where id=10;
update student set math=50 where id=10;
- 删除语句 -删除指定条件的数据格式：delete from 表名 where id=10;
delete from student where id=10;
delete from student where chinese is null;
-删除表中所有数据
delete from student;
Mysql5.7安装完成之后，默认4个数据库
information_schema：information_schema是一个信息数据库，它保存着关于MySQL服务器所维护的所有其他数据库的信息。(如数据库名，数据库的表，表栏的数据类型与访问权 限等。)
Mysql：主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息
performance_schema：主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表
Sys：Sys库所有的数据源来自：performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。
八、Mysql基本优化操作；
1.忘记密码：
[root@my ~]# vi /etc/my.cnf
[mysqld]
skip-grant-tables ##添加该行，跳过密码验证
:wq
[root@my ~]# systemctl restart mysqld
[root@my ~]# mysql ##登录后操作
mysql> use mysql;
Database changed
mysql> update user set authentication_string=password('123456') where user="root";
mysql> select Host,user,authentication_string from user;
+-----------+-----------+-------------------------------------------+
| Host | user | authentication_string（原mysql 5.5的password字段） |
+-----------+-----------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+
2 rows in set (0.01 sec)
[root@my ~]# vi /etc/my.cnf
[mysqld]
#skip-grant-tables ##注释该行
:wq
[root@my ~]# systemctl restart mysqld
2.解决mysql乱码问题：
[root@my ~]# vi /etc/my.cnf
[client]
default-character-set=utf8
[root@my ~]# systemctl restart mysqld
九、Mysql存储引擎的应用；
1.查看数据库可以配置的存储引擎类
2.查看表正在使用的存储引擎：
3. 创建表并且指定存储引擎；
4.修改已经存在表的存储引擎：
5.修改Mysql服务的默认存储引擎：
[root@my ~]# vi /etc/my.cnf ##新添配置项
[mysqld]
default-storage-engine=MyISAM
[root@my ~]# systemctl restart mysqld
[root@my ~]# mysql -uroot -p123123
十、Mysql配置文件详解：
[root@my ~]# vi /etc/my.cnf
[mysqld]
########basic settings########
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character_set_server=utf8
init_connect='SET NAMES utf8'
log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/logs/mysqld.pid
user = mysql
symbolic-links=0
server-id = 11
#bind_address = 192.168.100.101
autocommit = 1
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 100
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 128M
tmp_table_size = 128M
tmpdir = /dev/shm
max_allowed_packet = 16M
interactive_timeout = 60
wait_timeout = 60
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
########log settings########
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/logs/mysql-slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 1
min_examined_row_limit = 100
########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
log_bin = /usr/local/mysql/logs/mysql-bin
#sync_binlog = 4
gtid_mode = on
enforce_gtid_consistency = 1
#log_slave_updates
binlog_format = row
#relay_log = /usr/local/mysql/logs/mysql-relay.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors
########innodb settings########
innodb_page_size = 16K
innodb_buffer_pool_size = 4G
#innodb_buffer_pool_instances = 8
#innodb_buffer_pool_load_at_startup = 1
#innodb_buffer_pool_dump_at_shutdown = 1
#innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000
#innodb_flush_method = O_DIRECT
#innodb_log_group_home_dir = /usr/local/mysql/logs/redolog/
#innodb_undo_directory = /usr/local/mysql/logs/undolog/
#innodb_undo_logs = 128
#innodb_undo_tablespaces = 0
#innodb_flush_neighbors = 1
#innodb_log_file_size = 4G
#innodb_log_buffer_size = 16M
#innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
#innodb_print_all_deadlocks = 1
#innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
########semi sync replication settings########
#plugin_dir=/usr/local/mysql/lib/plugin
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#loose_rpl_semi_sync_master_enabled = 1
#loose_rpl_semi_sync_slave_enabled = 1
#loose_rpl_semi_sync_master_timeout = 5000
[mysqld-5.7]
#innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
#innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 2G
#innodb_purge_rseg_truncate_frequency = 128
#binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
#show_compatibility_56=on
注解：
[mysqld]
mysql以什么用户运行
mysql运行在哪个端口
datadir = /usr/loca/mysql/data/
mysql的数据目录
mysql以socket方式运行的sock文件位置
是否支持符号链接，即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录，为0不开启
########basic settings########
mysql的服务器分配id，在启用主从和集群的时候必须指定，每个节点必须不同
mysql监听的ip地址，如果是127.0.0.1，表示仅本机访问
数据修改是否自动提交，为0不自动提交
服务器使用的字符集
禁用DNS主机名查找，启用以后用内网地址向mysqlslap请求响应快了一半
mysql最大连接数
某台host连接错误次数等于max_connect_errors（默认10） ，主机'host_name'再次尝试时被屏蔽。可有效反的防止dos攻击
transaction_isolation = READ-COMMITTED
数据库事务隔离级别
1.READ-UNCOMMITTED(读取未提交内容)级别
2. READ-COMMITTED（读取提交内容）
3. REPEATABLE-READ(可重读)
4.SERIERLIZED(可串行化)
默认级别REPEATABLE-READexplicit_defaults_for_timestamp = 1
mysql中TIMESTAMP类型和其他的类型有点不一样(在没有设置explicit_defaults_for_timestamp=1的情况下）
当我们的join是ALL,index,rang或者Index_merge的时候使用的buffer。 实际上这种join被称为FULL JOIN
规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下
保存临时文件的目录
mysql最大接受的数据包大小
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
sql_mode 模式，定义了你MySQL应该支持的sql语法，对数据的校验等等，限制一些所谓的‘不合法’的操作
服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端
服务器关闭非交互连接之前等待活动的秒数，在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)
读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区
随机读缓冲区大小，当按任意顺序读取行时（列如按照排序顺序）将分配一个随机读取缓冲区，进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度
是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存
########log settings########
#log_error = /data/local/mysql-5.7.19/log/mysql-error.log
错误日志位置
是否开启慢查询日志收集
slow_query_log_file = /data/local/mysql-5.7.19/log/mysql-slow.log
慢查询日志位置
log_queries_not_using_indexes = 1
是否记录未使用索引的语句
慢查询也记录那些慢的optimize table，analyze table和alter table语句
记录由Slave所产生的慢查询
log_throttle_queries_not_using_indexes = 10
设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间
日志自动过期清理天数
设置记录慢查询超时时间
查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
########replication settings########
#master_info_repository = TABLE
从机保存主节点信息方式，设成file时 会生成master.info 和 relay-log.info2个文件，设成table，信息就会存在mysql.master_slave_info表中。不管是设置的哪种值，都不要移动或者编辑相关的文件和表
#relay_log_info_repository = TABLE
用于保存slave读取relay log的位置信息，可选值为“FILE”、“TABLE”，以便crash重启后继续恢复
log_bin = /data/local/mysql-5.7.19/log/mysql-bin
binlog的保存位置，不能指定确定的文件名如mysql-bin.log，只能指定位置和前缀，会生成以前缀为开头的一系列文件
这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
启用gtid类型，否则就是普通的复制架构
强制GTID的一致性
slave更新是否记入日志，在做双主架构时异常重要，影响到双主架构是否能互相同步
binlog日志格式，可选值“MIXED”、“ROW”、“STATEMENT”，在5.6版本之前默认为“STATEMENT”，5.6之后默认为“MIXED”；因为“STATEMENT”方式在处理一些“不确定”性的方法时会造成数据不一致问题，我们建议使用“MIXED”或者“ROW”
#relay_log = /data/local/mysql-5.7.19/log/mysql-relay.log
从机保存同步中继日志的位置
当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性
#binlog_gtid_simple_recovery = 1
这个参数控制了当mysql启动或重启时，mysql在搜寻GTIDs时是如何迭代使用binlog文件的。 这个选项设置为真，会提升mysql执行恢复的性能。因为这样mysql-server启动和binlog日志清理更快
#slave_skip_errors = ddl_exist_errors
跳过指定error no类型的错误，设成all 跳过所有错误
########innodb settings########
innodb每个数据页大小，这个参数在一开始初始化时就要加入my.cnf里，如果已经创建了表，再修改，启动MySQL会报错
缓存innodb表的索引，数据，插入数据时的缓冲，专用mysql服务器设置的大小： 操作系统内存的70%-80%最佳
#innodb_buffer_pool_instances = 8
可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写
#innodb_buffer_pool_load_at_startup = 1
默认为关闭OFF。如果开启该参数，启动MySQL服务时，MySQL将本地热数据加载到InnoDB缓冲池中
#innodb_buffer_pool_dump_at_shutdown = 1
默认为关闭OFF。如果开启该参数，停止MySQL服务时，InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘
根据 官方文档 描述，它会影响page cleaner线程每次刷脏页的数量， 这是一个每1秒 loop一次的线程
事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败；参数的时间单位是秒
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000这两个设置会影响InnoDB每秒在后台执行多少操作. 大多数写IO(除了写InnoDB日志)是后台操作的. 如果你深度了解硬件性能(如每秒可以执行多少次IO操作),则使用这些功能是很可取的,而不是让它闲着
#innodb_flush_method = O_DIRECT
默认值为 fdatasync. 如果使用 硬件RAID磁盘控制器, 可能需要设置为 O_DIRECT. 这在读取InnoDB缓冲池时可防止“双缓冲(double buffering)”效应,否则会在文件系统缓存与InnoDB缓存间形成2个副本(copy). 如果不使用硬件RAID控制器,或者使用SAN存储时, O_DIRECT 可能会导致性能下降
#innodb_log_group_home_dir = /data/local/mysql-5.7.19/log/redolog/
innodb重做日志保存目录
#innodb_undo_directory = /data/local/mysql-5.7.19/log/undolog/
innodb回滚日志保存目录
undo回滚段的数量， 至少大于等于35，默认128
用于设定创建的undo表空间的个数，在mysql_install_db时初始化后，就再也不能被改动了；默认值为0，表示不独立设置undo的tablespace，默认记录到ibdata中；否则，则在undo目录下创建这么多个undo文件，例如假定设置该值为4，那么就会创建命名为undo001~undo004的undo tablespace文件，每个文件的默认大小为10M。修改该值会导致Innodb无法完成初始化，数据库无法启动，但是另两个参数可以修改
InnoDB存储引擎在刷新一个脏页时，会检测该页所在区(extent)的所有页，如果是脏页，那么一起刷新。这样做的好处是通过AIO可以将多个IO写操作合并为一个IO操作。对于传统机械硬盘建议使用，而对于固态硬盘可以关闭。
这个值定义了日志文件的大小，innodb日志文件的作用是用来保存redo日志。一个事务对于数据或索引的修改往往对应到表空间中的随机的位置，因此当刷新这些修改到磁盘中就会引起随机的I/O，而随机的I/O往往比顺序的I/O更加昂贵的开销，因为随机的I/O需要更多的开销来定位到指定的位置。innodb使用日志来将随机的I/O转为顺序的I/O，只要日志文件是安全的，那么事务就是永久的，尽管这些改变还没有写到数据文件中，如果出现了当机或服务器断电的情况，那么innodb也可以通过日志文件来恢复以及提交的事务。但是日志文件是有一定的大小的，所以必须要把日志文件记录的改变写到数据文件中，innodb对于日志文件的操作是循环的，即当日志文件写满后，会将指针重新移动到文件开始的地方重新写，但是它不会覆盖那些还没有写到数据文件中的日志，因为这是唯一记录了事务持久化的记录
如果对 Innodb 数据表有大量的写入操作，那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要。然而设置太大了，就会增加恢复的时间，因此在MySQL崩溃或者突然断电等情况会令MySQL服务器花很长时间来恢复
事务在内存中的缓冲。 分配原 则：控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次
控制是否使用，使用几个独立purge线程（清除二进制日志）
mysql在5.6之前一直都是单列索引限制767，起因是256×3-1。这个3是字符最大占用空间（utf8）。但是在5.6以后，开始支持4个字节的uutf8。255×4>767, 于是增加了这个参数。这个参数默认值是OFF。当改为ON时，允许列索引最大达到3072
innodb_thread_concurrency = 64
InnoDB kernel并发最大的线程数。 1) 最少设置为(num_disks+num_cpus)*2。 2) 可以通过设置成1000来禁止这个限制
#innodb_print_all_deadlocks = 1
是否将死锁相关信息保存到MySQL 错误日志中
开启InnoDB严格检查模式，尤其采用了页数据压缩功能后，最好是开启该功能。开启此功能后，当创建表（CREATE TABLE）、更改表（ALTER TABLE）和创建索引（CREATE INDEX）语句时，如果写法有错误，不会有警告信息，而是直接抛出错误，这样就可直接将问题扼杀在摇篮里
ORDER BY 或者GROUP BY 操作的buffer缓存大小
########semi sync replication settings#########plugin_dir=/data/local/mysql-5.7.19/lib/plugin
指定mysql的插件目录
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
指定载入哪些插件
#loose_rpl_semi_sync_master_enabled = 1
控制主库上是否开启semisync
#loose_rpl_semi_sync_slave_enabled = 1
控制备库是否开启semisync
#loose_rpl_semi_sync_master_timeout = 5000
单位毫秒，防止半同步复制在没有收到确认的情况下，发送堵塞。master在超时之前没有收到确认，将恢复到异步复制，继续执行半同步没有进行的操作
[mysqld-5.7]
#innodb_buffer_pool_dump_pct = 40
表示转储每个bp instance LRU上最热的page的百分比。通过设置该参数可以减少转储的page数
为了提升扩展性和刷脏效率，在5.7.4版本里引入了多个page cleaner线程。从而达到并行刷脏的效果
在该版本中，Page cleaner并未和buffer pool绑定，其模型为一个协调线程 + 多个工作线程，协调线程本身也是工作线程。因此如果innodb_page_cleaners设置为8，那么就是一个协调线程，加7个工作线程
是否开启在线回收（收缩）undo log日志文件，支持动态设置
#innodb_max_undo_log_size = 2G
当超过这个阀值（默认是1G），会触发truncate回收（收缩）动作，truncate后空间缩小到10M
#innodb_purge_rseg_truncate_frequency = 128
控制回收（收缩）undo log的频率。undo log空间在它的回滚段没有得到释放之前不会收缩， 想要增加释放回滚区间的频率，就得降低设定值
#binlog_gtid_simple_recovery=1
这个参数控制了当mysql启动或重启时，mysql在搜寻GTIDs时是如何迭代使用binlog文件的。 这个选项设置为真，会提升mysql执行恢复的性能。因为这样mysql-server启动和binlog日志清理更快。该参数为真时，mysql-server只需打开最老的和最新的这2个binlog文件
在MySQL 5.7.2 新增了 log_timestamps 这个参数，该参数主要是控制 error log、genera log，等等记录日志的显示时间参数。 在 5.7.2 之后改参数为默认 UTC 这样会导致日志中记录的时间比中国这边的慢，导致查看日志不方便。修改为 SYSTEM 就能解决问题
#transaction_write_set_extraction=MURMUR32
这个神奇的参数5.7.6版本引入，用于定义一个记录事务的算法，这个算法使用hash标识来记录事务。如果使用MGR，那么这个hash值需要用于分布式冲突检测何处理，在64位的系统，官网建议设置该参数使用 XXHASH64 算法。如果线上并没有使用该功能，应该设为off
从mysql5.7.6开始information_schema.global_status已经开始被舍弃，为了兼容性，此时需要打开 show_compatibility_56
Sql语句练习：
数据库相关
- 连接数据库的命令
mysql -uroot -p
- 创建数据库
create database db2;
- 查询所有的数据库
show databases;
- 查询单个数据库的信息
show create database db2;
- 删除数据库
drop database db2;
- 创建数据库时指定字符集
create database db2 character set utf8;
- 使用数据库
use db2;
表相关的SQL
创建表student
create table student (id int, name varchar(20));
查看所有的表
show tables;
查看表属性 数据库表的引擎 和 编码
show create table student;
查看表结构
desc student;
创建表 指定引擎和编码
create table teacher (id int, name varchar(10)) engine=innodb charset=utf8;
修改表的名字 rename table student to stu;
给表添加字段
-在最后
alter table stu add age int;
-在最前端添加
alter table stu add age int first;
-在某个字段的后面
alter table stu add age int after id;
删除表字段
alter table stu drop age;
修改表的属性
alter table stu engine=myisam charset=utf8;
修改字段的名称和类型
alter table stu change age fatherAge int;
修改字段的类型和位置
alter table stu modify fatherAge double after name;
删除表
drop table stu;
数据相关
-创建商品表：
create table t_item(
id int,
title varchar(40),
num int,
price double,
category varchar(20),
createDate date
);
- 插入数据 -全表插入 insert into t_item values(1,'毛巾',89,4.5,'日用品','1985-06-23'); -指定字段插入
insert into t_item (title,price,category)values
('香皂',1.5,'日用品');
-批量插入数据
insert into t_item values
(3,'鼠标',200,20,'电脑配件','2008-12-21'),
(4,'键盘',260,35,'电脑配件','2018-11-25'),
(3,'写字板',10,120,'电脑配件','2006-10-28');
- 修改数据
update t_item set title='双飞燕鼠标' where title='鼠标';
-修改多条数据
update t_item set title='鼠标',price=38 where title='双飞燕鼠标';
- 删除数据
delete from t_item where id is null;
delete from t_item where title='键盘';
查询数据
-查询sql 后面也可以添加where 条件
select title from t_item where category='日用品';
练习：
1.创建newdb数据库创建表emp（员工表）字段有：id name、salary（工资）、dept（部门名称）、joinDate（入职日期）
create table emp( id int, name varchar(10), salary double, dept varchar(10), joinDate date );
- 插入刘关张和唐僧四人组 7个人 刘关张部门为：三国部 唐僧它们部门为取经部
insert into emp values(1,'刘备',1800,'三国部','2001-12-10'),(2,'关羽',800,'三国部','2002-12-10'),(3,'张飞',5800,'三国部','2003-12-10'),(4,'唐僧',11800,'取经部','2004-12-10'),(5,'悟空',2800,'取经部','2005-12-10'),(6,'八戒',1800,'取经部','2006-12-10'),(7,'悟净',800,'取经部','2007-12-10')
- 修改刘备的工资为2000
update emp set salary=2000 where id=1;
- 修改唐僧名称为唐长老
update emp set name='唐长老' where id=4;
- 给表添加一个年龄字段 在name字段的后面
alter table emp add age int after name;
- 修改三国部的年龄为45
update emp set age=45 where dept='三国部';
- 修改取经部的日期为今天日期
update emp set joinDate='2018-2-26' where dept='取经部';
- 修改工资小于5000的年龄为18
update emp set age=18 where salary<5000;
- 删除三国部门所有员工
delete from emp where dept='三国部';
- 删除全表
delete from emp;
drop table emp;
1436
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
