mysql优化

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