Mysql优化

 2020-02-07 11:05:58  mysql  军哥
 (53)  (0)

Mysql优化

一、优化的方面

  1. sql语句,通常注意一些查询会导致索引失效,然后进行全表扫描
  2. 索引创建,哪些字段设置
  3. 表结构
  4. 慢查询日志分析&explan
  5. mysql查询缓存设置

二、mysql系统命令

show index from table 查看某张表中的索引
show processlist查看所有的进程
show engines 查看所支持的引擎
kill session_id 杀掉处理进程

三、sql语句优化

避免字段设置默认值为null,应该设置为not null,select * from table where c_1 is null会放弃这个字段上的索引,采用全表扫描

避免在where对字段进行!=或<>操作,这样会导致全表扫描,只能用<、>、<=、>=、between这些才会应用索引,select .. from .. where sal != 3000 改为select .. from .. where sal < 3000 or sal > 3000;

in和not in也会导致全表扫描,可以采用between and 来替代,或者用exists来替代,select num from a where num in(select num from b).用下面的语句替换: select num from a where exists(select 1 from b where num=a.num)

like会导致全表扫描,如果该字段用了全文索引,那么可以用like 'xxx%'可以应用索引
如果该字段用了全文索引,那么可以用like '%xxx%'不可以应用索引,而是全表扫描

select * from tab,这样会查询出一些没有必要的字段,这样会增加io的浪费

避免在where的字段中用表达式,会全表扫描,select id from t where num/2=100应改为:select id from t where num=100*2

避免在where子句中对字段进行函数操作,会全表扫描select id from t where substring(name,1,3)=’abc’ ,name以abc开头的id应改为:select id from t where name like ‘abc%’

使用union代替or
SELECT LOC_ID ,LOC_DESC ,REGION FROM LOCATION WHERE LOC_ID=10 OR REGION ='MELBOURNE'
替换为
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = 'MELBOURNE'

四、索引优化

可以在经常where过滤的字段和order by的字段上进行添加索引,一般数据上了百万,那么索引的效果就很凸显了
索引的种类有
普通索引、唯一索引、全文索引、主键索引、组合索引

五、表结构优化

尽可能的使用 varchar/nvarchar 代替 char/nchar , 因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
避免默认值用null,
尽量用占用空间小的数据类型

存储引擎:
myisam
innodb 5.5

六、慢查询日志&explan执行计划

开启mysql的慢查询日期,在my.ini中配置
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=5

mysql5.6取消了log-slow-queries
slow_query_log = on
slow-query-log-file = /home/db/madb/log/slow-query.log
long_query_time = 1

然后查看日期文件,然后根据里面记录的执行慢的sql再做具体的调整

explan select * from table
system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery >
index_subquery > range > index > ALL



打赏, 我要小额赞助,鼓励作者写出更好的文章
>> 下一篇:搭建直播服务器

评论列表



您还没有登录,登录后才能发表评论,请【登录】