mysql笔记

group_concat separator | 1 对多 多列合并到一列 并用 separator 后的符号 分割
linux 开发：尽量 不要有中文 不要用空格
mysql 易学难精
内核
优化，索引，my.conf
安装，rpm 安装mysql5.5
1：检查是否安装 rpm -qa | grep -i mysql
2: rpm -ivh **.rpm
查看用户 cat /etc/passwd|grep mysql
查看用户组 cat /etc/group|grep mysql
查看版本 mysqladmin --version

安装后设置密码：/usr/bin/mysqladmin -u root password 123456
设置自启动 chkconfig mysqld on
查看运行等级下的启动状态 chkconfig --list |grep mysql
查看etc配置文件 cat /etc/inittab
查看系统服务启动状态。 （*）ntsysv:集中管理系统不同的运行等级下的系统服务启动状态。

路径					解释						备注

/var/lib/mysql mysql 数据库文件的存放位置 /var.lib/myysql/atguigu.cloud.pid
/usr/share/mysql 配置文件目录 mysql.server命令及配置文件
/usr/bin 相关命令目录 mysqladmin,mysqldump等命令
/etc/init.d/mysql 启停相关脚本

mysql原本的配置文件不动，将配置文件考到/etc/目录下，修改此目录。
cp /usr/share/mysql/my-huge.cnf /etc/my.cnf (mysql 5.5 )

查看字符集：show variables like ‘character%’;
show variables like ‘%char%’
修改配置文件：
修改字符集：修改配置文件
[clint]
default-character-set=utf8
[mysqld]
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8
默认表名区分大小写：0：区分大小写 1：不区分
lower_case_tale_names = 1
设置最大链接数 默认151 最大16384
max_connections=1000

frm 文件  存放表结构
myd 文件  数据
myi 文件  索引

插件式的存储引擎架构将查询处理和其他的系统任务以及数据存储提取相分离
逻辑架构介绍 : 查看 '逻辑结构.png/逻辑结构详情.png'
		连接层
	php  python ...
		服务层，业务逻辑处理层
	1:管理服务和工具组件(Management Services &Utilities)
	2:线程连接池
	3:关键词(存储过程，视图触发器)  判断是什么操作 select/update/...
	4:解析转换（查询的事务，对象的权限） 去掉注释，转换成mysql自己能理解的。重组过滤，从from开始解析
	5:mysql优化器 按照mysql优化器认为最优的方式运行
	6:缓存，缓冲
		存储引擎
	7:存储引擎，myisam innodb(默认，支持行锁，事务) ...
		存储
	8:文件存储 file system  files&logs

存储引擎
	show engines；显示所有的存储引擎
	show variables like '%storage_engine%';显示默认和当前的存储引擎

二：索引优化

	执行时间长		查询语句写的烂
					索引失效
	等待时间长		关联太多 太多的join
					服务器调优，各个参数设置（缓冲，线程数）
				
	create index idx_user_name on user(name);
	create index idx_user_nameEmail on user(name,email);
	
join查询  "sqljoin-1.png" "sqljoin-2.png"
	sql的执行顺序 查看‘sql解析.png’
		手写：select distinct from left join  on  where  group by  having order by limit
		机读：先读from ， from left join on where group by having   select distinct order by limit 

***	mysql 中没有 full outer join  达到相同效果 使用union  合并并去重
		select * from tablea a left join tableb b on a.id=b.id
		union
		select * from tablea a right join tableb b on a.id=b.id

索引
定义：（官方）是帮助MYSQL高效获取数据的数据结构。（自己）排好序的快速查找数据结构。
目的：提高查找效率
索引会影响到where后的查找，order by 后的排序

删除操作，在底层是更新操作，将数据从激活状态改为非激活状态，目的：1，大数据，云计算，进行数据分析。2，为了索引，

单值索引：索引包含单列，一个表中可以有多个单列索引
唯一索引：索引列的值必须唯一，单允许有空值
复合索引：一个索引包含多个列

语法：
	创建：create [unique] index idx_table_colname on table(columnname(lenght))
		  alert table add index [unique] idx_table_colname on (columnname(lenght))
	删除：drop index idx_table_colname on table
		  alert table talbe_name drop index idx_table_colname
	查看：show index from table\G(不用分号)
那些		需要创索引									那些不需要
	主键，频繁查找的，外键，统计，排序，  		频繁更新，where条件中用不到的，记录少（300万），
常见瓶颈：
	cpu,在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
	IO，磁盘I/O瓶颈发生在装入数据大于内存容量的时候
	服务器硬件性能瓶颈，top,free，iostat,vmstat来查看系统的性能状态

*****explain:模拟优化器执行sql查询
能做什么：
1，表的读取顺序
2，数据读取操作的类型
3，哪些索引可以使用，哪些索引被实际使用，
4，表之间的引用
5，每张表有多少行被优化器查询

表头：
***	id:	select 查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序
		三种情况：
			1，id相同，执行顺序由上至下
			2，id不同，如果是子查询，id的序号会增加，id越大优先级越高，越先被执行
			3，id相同不同，同时存在，数字大优先，相同顺序执行
	select_type:查询类型是什么
		六中类型
			simple:简单select查询，查询中不包含子查询或者union
			primary:查询中包含任何复杂的子部分，最外层的查询被标记为primary
			subquery:在select或where列表中包含了子查询
			derived:在from列表中包含的子查询被标记为derived（衍生），mysql会递归执行这些子查询，把结果放到临时表中
			union:若第二个select出现在union之后则标记为union。若union包含在from字句的子查询中，外层select将被标记为derived
			union result:从nuion表获取的结果的select	
	table ：表
***	type:访问类型
		八种值：从最好到最差排序：system>const>eq_ref>ref>range>index>all
			all:全表扫描
			index:索引扫描
			range:检索给定范围的行，使用一个索引来选择行。一般是在where雨中中出现 between,<,>,in. ***此时会导致范围后的索引失效，在Extra中出现全文扫描（using filesort）
			ref:非唯一性索引扫描，返回匹配某个单独值的所有行
			eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
			const:常量，表示通过索引一次就查到，用于比较primary key 或者unique 索引。只匹配一条记录
			system:表只有一条记录，const类型的特例。
			null:
			index_merge:索引合并
	possible_keys:可能应用在表中的索引，一个或多个

*** keys:实际用到的索引，如果是null则没有使用索引，失效或没建
查询中若使用了覆盖索引，则该索引金出现在key列表中
索引覆盖：select 字段和索引字段的顺序和个数相匹配
key_len:(理论长度)表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精度的情况下长度越短越好。
ref:显示索引的那一列被使用，如果可能的话，是一个常数(const)，哪些列或常量被用于查询索引列上的值。
*** row:大致查询的行数
*** extra:额外扩展展示的信息，十分重要的额外信息
(bad)using filesort:说明mysql对数据使用了一个外部索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序操作称为"文件排序"
(bad)using temporary:使用临时表保存中间结果，mysql在对查询结果排序是使用了临时表，常见于排序order by和分组 group by
(good)using index:正好使用了覆盖索引，既查询的值还有顺序和索引相同
using where:
using join buffer:调大配置文件buffer的值。inner join 三个表的时候会出现buffer
impossible where:错误
select tables optimized away:
distinct:找到第一个就停止

索引优化
	单表：where条件后的字段，跟产品经理提需求，尽量等于（常量），不要用范围。
		**  where中有表示范围的条件：tmp>1,范围以后的索引会导致失效
			所以建立索引时不将范围的的条件写入索引。
	两表：有主外键，
		左外连接的特性决定，左侧必须有，条件确定如何从右表搜索行，故左连接加在右表！
		右外连接的特性决定，右侧必须有，条件确定如何从左表搜索行，故右连接加在左表！
	三表：		
		后两个表建立索引。
		尽量减少join语句中的nestedloop的循环总次数：永远用小的结果集驱动大的结果集
		优先优化nestedloop的内层循环
		保证join语句中被驱动表上的join条件字段已经被索引
		当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝啬joinBuffer的设置
		
索引失效：避免！
	1：全值匹配是最好，个数加顺序！(索引覆盖)
	2：最佳左前缀法则,从索引的最左前列开始，中间列不能跳过。
	3：不在索引列（where条件中）上做任何操作（计算，函数，[自动或手动]类型转换，会导致索引失效从而转向全表扫描）
	4：存储引擎不能使用索引中/*范围条件右边*/的列
	5：尽量使用覆盖索引（只访问索引的查询[索引列和查询列一致]）尽量少用 select *
	6：mysql在使用不等于（!=或者<>）的时候，无法使用索引，导致全表扫描。
	7：is null is not null  也无法使用索引  可以使用default值，避免空值。
	8：like 以通配符开头（like "%char"）索引失效，全表扫描。like 'char%'(tpe为range)可以,使用覆盖索引来解决！

*** 9：字符串不加单引号索引失效 必须加引号！！！！
10：少用OR 用它来连接是索引失效
口诀：
全值排配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上无计算，范围之后全失效
like百分加右边，覆盖索引不写*
不等空值还有OR，索引失效要少用
字符引号不能丢，SQL 高级也不难

？定值，范围，还是排序。一般order by 是给个范围
？分组之前必排序，会有临时表产生。
一般性建议：
	对于单值索引，尽量选择针对当前query过滤性更好的索引
	在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序，位置越靠前越好
	在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
	尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

查询截取分析
	查询优化：
		小表驱动大表
	order by 的排序优化
		order by字句，尽量使用index方式排序，避免使用filesort方式排序
		两种情况是index排序，
			1：order by语句使用索引最左前列
			2：使用where字句和orderby 字句条件列组合满足索引最左前列
		提高orderby 速度
			1：不要用select* 
			2：增大sort_buffer_size
			3：提高max_lenght_for_sort_data

		为排序使用索引 '查看排序使用索引.png'
	groupby 与orderby 相似 由于where 高于having 故能在where中的条件不要写在having中				
		
开启慢查询日志，如果不调优，建议关闭
	1：查看：show variables like '%slow_query_log%';
	2: 设置slow_query_log的值开启：set global slow_query_log=1; 重启后失效
	3：永久生效(不建议)：在[mysqld]中
				slow_query_log=1
				slow_query_log_file=/var/lib/mysql/主机-slow.log
				long_query_time=3
				log_output=FILE
	4：查看默认慢查询时间 ：show variables like '%long_query_time%',;
		设置long_query_time时间：set global long_query_time=3
	5：show global status like '%Slow_queries%';	
	6:MYSQL 提供日志分析工具：
		mysqldumpslow 帮助信息查看'mysqldumpslow日志分析帮助.png'

showprofile	
	1:show variables like'profiling' 查看是否支持	
	2:set profiling=on;
	3:show profiles; 查看所有执行的sql
	4:show profile cpu,block io for query 3(上一个查询出来的id);  sql诊断
		1):converting heap to myisam 查询结果太大，内存不够用王磁盘上搬
		2):creating tmp table 创建临时表  1.拷贝数据到临时表 2.用完在再删除
		3):copying to tmp talbe on disk  内存中的临时表复制到磁盘 危险！！！
		4):locked 
		
全局查询日志 只能在测试环境用 不要在正式环境中使用		
	set global generral_log=1
	set global log_output='table_name';
	将会记录到mysql库中的general_log表 可以用 select * from mysql.general_log;查看 	 		
		
分析
	1：观察，至少跑一天，查看生产的mjsql情况
	2：开启慢查询日志，设置阈值，比如超过5秒钟就是慢sql并抓取出来
	3：explain+慢SQL分析
	4：show profile 
	5：运维经理或DBA 进行服务器的参数调优
总结：
	1：慢查询的开启并捕获
	2：explain+慢SQL分析
	3：showprofile 查询SQL在mysql服务器中的执行细节和生命周期情况
	4：SQL数据库服务器的参数调优

****where 和 order by 联合使用复合索引！！！！
group by 一定要使用索引 否则会有 文件排序，或临时表
like ‘char%’ 只有在开始没有’%'的时候使用索引

锁机制
	对数据操作的类型分类
		读锁（共享锁）：
		写锁（排它锁）：
	对数据操作的粒度分类
		表锁：
		行锁：

手动增加表锁：
	lock table table_name read(write), table_name2 read(write),其他；
解锁：
	unlock tables;
查看锁的命令
	show open tables;

表锁：(偏读)

*** myisam表： 读锁会阻塞写，不会堵塞读，写锁会把读写都阻塞
读锁： session1 读锁book表 book表能读但不能更新，插入。（栈没有清空）session1不能读别的表。
session2 book表能读，且更新修改时阻塞 可以查询更新未锁定的表

		写锁：  session1  写锁book表    可以读，改 book表，（栈未清空）不能读其他表
				session2  读，更，插 book表 阻塞

*** 表锁分析：show status like ‘table%’; 查看lock_waits的条数

行锁：(偏写)
	innoDB表：支持事务，
		关闭自动提交：set autocommit=0  试验用
		读己之所写，
		间隙锁 ：当用范围条件而不是相等条件检索数据，并请求共享或排它锁时，innodb会给符合条件的已有数据的索引项加锁，对于键值在条件范围内胆不存在的记录，叫‘间隙’；

*** 对于公司来说数据都是重要的，删除操作到底层一般都是更新操作。
1 3 4 5 6 没有2 会出现间隙，此时另一个程序操作2 时就会出现间隙锁 导致阻塞
*** 行锁会变表锁在 索引失效的时候。
锁定某一行 select * from table where id= 1 for update; 提交前 其他人的操作都是阻塞。

	行锁分析：show status like 'innodb_row_lock%';  查看lock_waits的条数

**** 事务：
ACID：Atomicity 原子性：事务是一个原子操作单元，其对数据的修改要么全执行，要么全不执行
Consistent 一致性：事务开始和完成时，数据必须保持一致状态，
这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性，
事务结束时，所有的内部数据结构（B树索引或双向链表）也都必须是正确的
Isolation 隔离性：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行
这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
Durable 持久性：事务完成之后，他对数据的修改时永久性的，即使出现系统故障也能够保持
并发带来的问题：
更新丢失：
脏读：
不可重复读
幻读：
脏读 读到修改
幻读 读到新增
事务的四种隔离级别：
未提交读：
已提交读：
**** 可重复读：
可序列化：
查看默认隔离级别： show variables like ‘tx_isolation%’;

	CAP?????  AP 重要
	
	
主从复制：
	复制有三步：
		1：master将改变记录到二进制日志(binary log),这些记录过程叫做二进制日志事件，banary log event;
		2:slave 将master的binary log events拷贝到他的中继日志(relay log)
		3：slave重做中继日志中的事件，将改变应用到自己的数据库中，mysql复制是异步的且串行化
	配置：
		版本相同，机器同一网段，
		主机
		[mysqld]中
	(必须)	1:主服务器唯一：server-id=1	 
	(必须)	2:启用二进制日志：log-bin=本地路径/mysqlbin   
			3:启用错误日志：log_error=本地路径/myslqerr
			4:根目录： basedir="本地路径"
			5:临时目录: tmpdir="本地路径"
			6:数据目录：datadir="本地路径/Data"
			7:read-only=0
			8:binlog-ignore-db=mysql  设置不要复制的数据库
			9:binlog-do-bd=db_name  设置需要复制的数据库
		从机 
			1:server-id = 1 注释掉
			2:server-id = 2  解开注释
			3:从服务器日志开启 不要动
		主从都需要关闭防火墙！
		主机 	1:授权用户 grant 	
				2:show master status;查看状态 找到slave中需要的'文件名'和'行数'
		从机  1:change master to master_host='ip' ,
				master_user='name' 
				master_password='pass' 
				master_log_file='文件名',master_log_pos=行数
			  2:start slave;
			  3:show slave status \G
					必须同时看到： slave_to-runing :yes
								   slave_sql_runing:yes
		停止主从： stop slave