这两天看了享学课题的公开课,挺好的,总结一下
针对面试题:当服务器运行慢了,你对MYSQL采取什么优化措施?
1、硬件方面:服务器硬件:机械硬盘效率相比固态硬盘约10倍差距,尽量采取固态硬盘。
2、软件方面: MySQL服务器优化,Linux(稳定),Windows相对不稳定,运行时可能产生垃圾,所有尽量安装在Linux上。
3、程序员的对SQL语句的优化:
(1)首先,我们要定位究竟是哪里慢了?开启慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S以上的语句。默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。
,这里我针对当前数据库:设置slow_query_log的值来开启(当前数据库开启,如果MySQL重启后则会失效)
show variables like '%slow_query_log%'; 查看慢查询是否开启 OFF / ON set global slow_query_log=1; 开启慢查询 show variables like 'long_query_time'; 查看查询限制时间,默认10秒 set global long_query_time=4; 设置限制时间为4秒,大于4秒的SQL才会记录到慢查询日志
慢查询的参数slow_query_log_file ,它指定慢查询日志文件的存放路径,系统默认会给一个缺省的文件host_name-slow.log
在MySQL里面执行SQL语句,然后我们去检查对应的慢查询日志。
日志分析工具mysqldumpslow
在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具mysqldumpslow
得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 +日志文件路径
(2)发现了最慢的SQL,进行优化
1、表格设计方面:反范式化设计
1.1 范式化设计
范式化设计的优点:减少冗余。对新增删除修改友好,对查询不友好,需要很多的拼接。
有时候我们稍微违反下范式化设计,改善我们的查询。以空间换时间,适当冗余。
2、可以把子查询改为联结查询
3、索引优化
(1)首先明白什么是索引?
索引(Index)是帮助MySQL高效获取数据的数据结构,举例书目录(树形)
(2)分类:
普通索引:即一个索引只包含单个列,一个表可以有多个单列索引
唯一索引:索引列的值必须唯一,但允许有空值
复合索引:即一个索引包含多个列
问题一:我们都知道索引能加快查询,但真的能加快查询吗?
关键看两个:1、建立了索引是否能被用到?2、使用充分地使用到了索引?----执行计划:EXPLAIN + SQL
执行计划是什么
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
主要看三点:
key :代表是否有用到索引
如果没使用索引,值为NULL,使用了覆盖索引,则该索引与查询的select字段重叠。
key_len : 可以计算索引是否被是否充分
如果是衡量索引是否充分使用,越长越好,如果是来检验表的物理设计,越短越好。
type : type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是 system>const>eq_ref>ref>range>index>ALL
- system:表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计
- const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快
- eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- ref:非唯一性索引扫描,返回匹配某个单独值的所有行.本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
- range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引,一般就是在你的where语句中出现了between、<、>、in等的查询,这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。
- all:Full Table Scan,将遍历全表以找到匹配的行
建立索引十个策略:(索引顺序的策略)
策略1.尽量全值匹配 :建立了复合索引 name, age, pos
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
策略2.最佳左前缀法则:以火车为例子理解
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
一定有火车头,如果中间车厢断了,后面的车厢也就用不了了。
策略3.不在索引列上做任何操作
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
策略4.范围条件放最后
存储引擎不能使用索引中范围条件右边的列
策略5.覆盖索引尽量用
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
策略6.不等于要甚用
mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
策略7.Null/Not 有影响
注意null/not null对索引的可能影响
策略8.Like查询要当心
like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
策略9.字符类型加引号
字符串不加单引号索引失效
策略10.OR改UNION效率高
最后总结: