Mysql高级
安装完成mysql之后需要给mysql 设置密码: /usr/bin/mysqladmin –u rootpassword 123123
那么我们登陆的时候 需要执行 mysql –u root -p
设置开机自启动mysql :chkconfig mysql on
Frm文件存放表结构
Myd 文件存放数据
Myi 文件存放索引
索引
定义: 索引就是一个排好序的数据结构 使用合并算法快速查找的一个数据结构
索引一般也很大,所以索引一般以文件的形式存储在磁盘上
我们平常所说的索引没有特别指明的情况下一般是Btree 索引
索引的优势: 提高数据 的查询效率,降低了数据库的IO成本
通过索引对队数据进行排序,降低了数据排序的成本,降低了CPU消耗
索引的劣势: 降低DML操作的速度; 并且占用一定空间
创建索引的时机:
主键自动自动建立索引
频繁作为查询条件的字段建立索引
外键建立缩索引
更新频繁不适合建立索引
Where不用到的字段不用建索引
查询到的排序字段,排序字段如果通过索引将大大提高排序速度;
查询中如果的统计字段或者分组字段\
那些情况不需要建索引:
1 表记录太少
2 经常做增删改的 操作
3 数据重复 且分布均匀的字段
性能分析 Explain (报告单)
执行计划 explain
使用explain关键字可以模拟优化器执行sql查询语句,从而知道mysql是如何处理你的sql的.分析你的查询语句或者是表结构的性能瓶颈;
格式 : explain +sql语句可以检查sql执行过程
explain能干嘛
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表之间有多少行被优化器查询
名词字段解释
当用explain +sql 得到计划解释后
Id :
select 查询的序列号包含一组数字.表示查询中执行select的字句或者操作表的顺序
三种情况: id 相同, 执行顺序由上至下
Id不同: 如果是子查询 id的序号会递增,id值越大优先级越高,越被先执行
Id相同不同,同时存在: 先执行数字(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 从union表获取结果的select
type 和sql是否优化息息相关
访问类型排列依次如下:
从最好到最次依次如下 :
System > const > eq_ref > ref >range > index > all
System表只有一条记录(等于系统表),这是const类型的特例,平时不会出现这个也可以忽略不计
Const表示通过索引一次就找到了,const用于比较primary key 或者unique索引,以为只匹配一行数据,所以很快.如 将主键置于where列表中,mysql就能将该查询转换为一个常量
Eq_ref唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配.常见于主键或者唯一索引扫描
Ref:非唯一性索引扫描.返回匹配某个单独值的所有航, 本质上也是一种索引访问,他返回所有匹配某个单独值的行,然后,.它可能会找到多个符合条件的行,所以他应该属于初八债和扫描的混合体
Range(范围) 只检索给定范围的行,使用一个索引来选择行,key列显示使用了那个索引,一般就是在你的where语句中出现了bean,<,>,in等的查询,这种范围扫描索引扫描比全表扫描要好,以为它值需要开始于索引的某一点,而结束与另一点,不用扫描全部索引.
Indexfull index scan, index与all区别为index类型只遍历索引树,这通常比all快,以为索引文件通常比数据文件小. (也就是说虽然all和index都是读全表,但index是从索引(内存)中读取的,而all是从硬盘上读取的)
All将遍历全表以找到匹配的行
(备注: 一般来说,得保证查询至少达到range级别,最好能达到ref)
Possible_keys 和key
1 功能: 判断是否使用到了索引,判断索引是否会失效
Possible_keys: 显示可能应用在这张表中的索引,一个或多个.
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
Key:最后实际应用到的索引
如果为null则没有使用到索引
查询中如果使用了覆盖索引,则该索引只出现在key列表中
Ket_len
表索引中使用的字节数,可通过该列计算查询中是哟领的索引的长度,在不损失精确性的情况下,长度越短越好.
显示的值是索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得到的,不是通过表内检索出的.(使用越少越好)
Ref : 显示索引的那一列被使用了,如果可能的话,是一个常数,那些列或者常量被用于查询索引列上的值
Rows根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
Extra: 包含不适合在其他咧显示但十分重要的额外信息 它包含:
1using filesort 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取 ,mysql中无法利用索引完成的排序操作成为”文件排序 (拖慢)
2 using temporary : 使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表常见于排序order by 和分组查询group by(拖慢)”
3 using index : 表示select 操作中使用了覆盖索引(covering index) ,避免访问了标的数据行,效率不错! 如果同时出现using where 表明索引被用来执行索引键值的查找;
如果没有同时出现using where ,表明索引用来读取数据而非执行查找动作
连接查询建立索引位置
不管是Left join 还是 right join 索引建立在右边表上
避免索引失效注意
1 全值匹配我最爱;
2 最佳左前缀法则 (如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不挑过索引中的列.)
带头大哥不能死,中间兄弟不能断
3 不在索引列上做任何操作(计算.函数.(自动or手动)类型转换),会导致索引失效而转向全表扫描
4 存储引擎不能使用索引中范围条件右边的列
5 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
6 mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描;
7 is null,is not null 也无法使用到索引
8 like以通配符开头 mysql索引失效会变成全表扫描的操作
9 字符串不加单引号索引失效
10 少用or 用它来连接时会索引失效
一般order by 如果按索引建立的顺序来排序,并且最左边的已经是常量 那么会使用到索引
如果不是按索引建立的顺序排序 , 那么会出现using filesort自己排序
Group by 基本上都需要进行排序会有临时表产生,出现usingtemporary
一般性建议:
1 对于单键索引, 尽量选择针对当前query过滤性更好的索引
2 在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好
3 在选择组合索引的时候尽量选择能够包含当前query中的where字句中的更多字段的索引,
4 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
口诀
全值匹配我最爱,最左前缀要遵循;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
like百分写最右,覆盖索引不能*;
不等空值还有or,索引失效要少用;
var引号不可丢,sql高级也不难;
查询截取分析
Sql 优化:
1观察,至少跑一天看看生产的慢sql情况
2开启慢查询日志,设置阙值,比如超过5秒的就是慢sql,并将它抓取出来
3explain+慢sql分析
4show profile
5运维经理或者dba进行sql数据库服务器的参数调优;
==总结
1 慢查询的开启并捕获
2 explain + 慢sql 分析
3 show profile查询sql在数据库里面的执行细节和生命周期情况
4 sql数据库服务器参数的调优
查询优化
1 优化原则: 永远小表驱动大表 ,即小的数据集驱动大的数据集
2 exists 和 in的功能类似
Exists语法可以理解为: 将主查询的数据,放到子查询中做条件验证,根据验证结果(true 或者false)来决定查询的数据结果是否得以保留;
Exists只返回true或者false,因此子查询中的select* 也可以是select 1 或者select x,官方说法是实际执行的时候会忽略select 清单,因此,没有区别
最终到底用in还是exist取决于那张表小,永远用小表驱动大表
Order By 优化
尽量使用usingindex 避免使用file sorte方式排序
索引最左前列原则
尽可能在索引列上完成排序操作,遵照索引列的最佳左前缀
Order by 满足两种情况会使用index方式排序: 1 orderby 使用索引最左前列
2 使用where字句与order by字句条件列组合满足最左前列
如果不在索引列上排序:
Filesort 有两种算法: 双路排序,和单路排序;
双路排序:
两次磁盘扫描 最终得到数据
取一批数据,要对磁盘进行了两次扫描,众所周知,I\O是很耗时的所以在mysql4.1
读取行指针和order by 的列,对他们进行排序,然后扫描已经排好序的列表,按照列表中的值重新从列表中读取对应的数据输出.
单路排序
从磁盘读取所需要查询的所有列,按照order by 列在buffer对他们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据,并且把随机I\O变成了顺序I\O,但是它会使用更多的空间,因为它把每一行都保存在了内存中了
Group by
实质:先排序后分组.遵照索引建的最佳左前缀
当无法使用索引列,增大max_length_for_dort_data参数,增大sort_buffer_size参数设置
Where高于having,能写在where限定条件就不要去having限定了
慢查询
慢查询在mysql中默认是关闭的
查看是否开启慢查询: show variables like ‘%slow_quert_log%’,
开启: set gloable slow_query_log=1
这个由参数long_query_time控制,默认情况下long_query_time的值为10秒,
命令: showvariables like 'long_query_time%'
也可以使用命令修改,在my.cnf参数里面修改
只有超过十秒的才会被记录, 大于十秒,不是大于等于十秒
设置 慢的阙值时间: set global long_query_time=3;(修改过后需要重新开一个会话才能看到修改)
查看日志记录条数: showglobal status like '%slow_queries%'
日志分析工具 mysqldumpslow
在/var/bin/mysql/主机名-slow.log 这个文件里边可以看到是那条sql查询慢,查询时间
mysqldumpslow的帮助信息:
s:是表示按何种方式排序
c:访问次数;
l:锁定时间
r:返回记录
-t查询时间
al: 平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条数据
g:放后边搭配一个正则匹配模式,大小写不敏感的;
得到返回记录集最多的10个sql
Mysqldumpslow–s r –t 10 /var/lib/mysql/主机名-slow.log
得到访问次数最多的10个sql
Mysqldumpslow–s c –t 10 /var/lib/mysql/主机名-slow.log
得到按照时间排序的钱10条里面含有做链接的查询语句
Mysqldumpslow–s t –t 10 –g ‘left join” /var/lib/mysql/主机名-slow.log
另外建议在使用这些命令的时候结合 | 和more 使用,否则有可能出现爆屏情况
批量插入数据脚本
批量数据脚本
1 创建数据库—创建表
2 穿件函数,加入报错:thisfunction has none of determinestic (这个函数不是静态的)
由于我们开启过慢查询日志,因为我们开启了 bin-log,我们就必须为我们的function指定一个参数(二进制的一个东西)
Showvariables like “”log_bin_trus_function_creators”;
Setglobal log_bin_trust_function_creators=1;
这样添加了参数以后,如果mysql重启,上述参数又会消失.永久方法:
Window下my.ini[mysql]加上log_bin_trust_function_creators=1
Linux下 /etc/my.cnf下 my.cnf[mysql加上log_bin_trust_function_creators=1;
3 创建函数,保证每条数据都不同
(1随机产生字符串,当做员工名字
函数: Delimiter$$ 开始,end $$ 结束,declare 声明函数,create function 建函数
Delimiter$$
Createfunction rand_string(n int) returns varchar2(255)
Begin
Declarechars-str varchar(100) default ‘abcdefghijklmnoparstuvwxyzABCDEF…’
Declarereturn-str varchar(255) default ‘’;
DeclareI int defaulr 0;
WhileI < n DO
Setreturn_str = concat(return_str,substring(chars_str,floor(1+rand()*52,1));
SetI = i+1;
Endwhile;
Returnreturn_str;
End $$
(2 随机产生部门编号
Delimiter$$
Createfunction rand_nun()
Returensint (5)
Begin
DeclareI int default 0 ;
SetI = floor (100+rand()*10);
ReturnI;
End$$
3) 创建存储过程
Delimiter$$
Creatrprocedure insert_emp(in start int(10),int max_num int(10))
Begin
DeclareI int default 0;
Setautocommit = 0;
Repeat
SetI = i+1;
Insertinto emp (emp no,ename,job,mgr,hiredate,sal,comm,deptno) values ((tart+i),,rand_string(6),’salesman’,0001,curd
UntilI = max_num
Endrepeat
commit
End$$
4 调用存储过程:
Delimiter:
Callinsert_dept(100,10)
Show profile性能分析脚本
它是 mysql视同的可以用来分析当前会话中语句执行的资源消耗情况,可以用于sql的调优的测量
默认情况下,参数处于关闭窗台,并保存最近15次的运行结果
分析步骤:
查看:w variables like “profiling 默认是关闭的使用需要开启;
开启 : setpfofiling = on;
运行: showprofiles;
诊断sql : showprofile cpu,block io for query 数字id ;问题sql数字号码
日常开发需要注意的结论: 1converting heap to mysam 查询结果太大,内存不够用了,搬到磁盘上
2creating tmp table 创建临时表:拷贝数据到临时表,用完再删除;
3copying to tmp table on disk 把内存中临时表复制到磁盘,危险!!!
4locked
全局查询日志
配置启用:
编码启用:
Setglobal general_log = 1;
Setglobal log_output=’’table’
此后写的sql,将会记录到mysql库里边的general_log表中,
查看: select * from mysql.general_log;
永远不要在生产环境开启这个功能
Mysql锁机制
表锁(共享锁)一份数据,多个操作可以同时进行而不会互相影响
写锁(排他锁): 当前写操作没有完成前,它会铸锻其他写锁和读锁
在企业中应用:
表锁: 偏向读的方向
手动增加表锁: lock table 表名字read(write),表名字2read(write),其他
查看表有没有被锁: show open tables
释放锁: unlock tables
加了读锁后 只能读自己, 不能读其他,不能写自己,其他session读不影响,写此表会等待
写锁: 给自己加写锁,自己不能读其他,其他不能读被锁的表,写也不能
所以: MyISAM 在执行查询语句前,会自动给涉及的所有表加读锁,在执行增删改之前会自动给所涉及的表加写锁;
1对MyISAM表的读操作(加读锁),不会注阻塞其他进程对同一表的读请求,但是会阻塞对同一表的写请求,,只有当读锁释放后才会执行其他进程的写操作
2对MyISAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读写操作,只有当写锁释放后,才会执行其他进程的读写操作,
简而言之,就是读锁会阻塞写,但是不会阻塞读,写锁会把读和写都阻塞;
分析表锁定:
Showstatus like 'table%'
出现两个变量说明表锁定的 情况:
1table_locks_immediate: 产生表级锁定的次所,表示可以立即获取锁的查询次数,每立即获取锁值加1
2table_locks_waited: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值越高说明存在着较为严重的表级锁争用的情况;
此外,myisam的读写锁调度是写优先,这要是myisam不适合做写为主表的引擎,因为写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成永远阻塞;
行锁: 偏向写的方向;
偏向innodb 开销大,加锁慢,会出现死锁,锁定颗粒度最小,发生锁冲突的概率很低,并发度也最高
innodb和myisam的最大不同有两点,一是支持事物,二是使用行锁,
事物的ACID特性:
原子性: 事物是一个原子操作单元,对其数据的修改,要么全执行,要么全不执行
一致性: 在事务开始和完成时,数据都必须保持一直状态,这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性,事务结束时,所有的内部数据结构(如Btree索引和双向链表)也都必须是正确的.
隔离性: 数据库系统提供一定的隔离机制,保证事务在不受外部外部并发操作影响的"独立"环境执行,这以为这事务处理过程中的中间状态对外部是不可见的,反之亦然
持久性: 事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持.
并发事务带来的问题:
更新丢失:类似于svn代码冲突
脏读: 事务A读取到了事务b已经修改,但是还未提交的数据
不可重复读: 事务A读到了事务b已经修改,并且提交的数据,不符合隔离性
幻读:事务A读到了事务b里面的新增数据,不符合隔离性
索引失效会导致行锁变成表锁;
间隙锁带来的危害:
行锁结论:
行锁分析: show status like 'innodb_row_lock%'
页锁: 结余两者之间
优化建议
1尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁.
2 合理设计索引,尽量缩小锁的外围
3 尽可能较少检索条件,避免间隙锁
4 尽量控制事务大小,减少锁定资源量和时间长度
5 尽可能低级别事务隔离
主从复制
复制的基本原理
Slave会从master读取binlog来进行数据同步
步骤:
复制的规则: 每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个master能有多个slave
复制的最大问题: 网络上有延迟;
一主一从常见配置:
1mysql版本一致,且后台以服务运行,并且主从机器都在同一网段,能ping通
2主从都配置在[mysqld]节点下,都是小写
3主机修改my.ini文件
1>server-id=1([必须] 主服务器唯一ID)
2>log-bin= 自己本地的路径/mysqlbin (在data文件夹中[必须] 启用二进制文件)
3>log-err=自己本地的路径/mysqlerr (在data文件夹中[可选]启用错误日志)
4>basedir=''自己本地路径" ([可选] 根目录)
5>tmpdir="自己本地路径" ([可选]临时目录)
6>datadir="自己本地路径/data/" ([可选]数据目录)
7>read-only=0 主机,读写都可以
8>binlog-ignore-db=mysql([可选] 设置不要复制的数据库)
9>binlog-do-db=需要复制的主数据库的名字 ([可选]设置需要复制的数据库)
4从机修改my.cnf配置文件:
1[必须] 从服务器唯一ID
2[可选] 启用二进制日志
5重新启动主从机mysql服务;
6主机从机都关闭防火墙 (虚拟机: service iptables stop)
7在windows 主机上简历账户并授权给slave(在cmd窗口进入mysql的bin目录下)
Grantreplication slave on *.* to 'zhazngshan'@'从机器数据库ip' identified by '123456'
Flushprivileges (刷新)
Showmaster status ; (查询master的状态) 记录下来file和position的值
8在linux从机上配置需要复制的主机
直接连接mysql窗口执行
主机新建库,新建表,insert记录,从机复制;
Changemaster to master_host='主机IP',
master_user='zhangshan',
master_password=’123456’,
master_log_file=’mysqlbin.名字’,
master_log_pos=position数字;
配置完上面的内容后执行: start slave;
执行: Show slave status; 当看到slave_IO_running:yes"
slave_sql_running: yes
这两个全部为yes时候才成功
如果不想要主机的数据: stop slave