文档章节

mysql数据库SQL优化

王小念和木子田博客
 王小念和木子田博客
发布于 2016/09/25 13:14
字数 1822
阅读 219
收藏 16

1.mysql主要存储引擎 MyISAM 和 InnoDB

    MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到更新操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的,如果只作为查询效果很好。 

   InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。他是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。最主要是支持事务。

2.mysql常用命令

 

   SELECT `ID`, `USER`, `HOST`, `DB`, `COMMAND`, `TIME`, `STATE`, LEFT(`INFO`, 51200) AS `Info`      FROM `information_schema`.`PROCESSLIST`  //显示当前进程   

    show full processlist;                                             //显示当前进程   
    SHOW VARIABLES LIKE 'max_connections';   //查看最大连接数
    SHOW STATUS LIKE 'Threads_connected';    //查看当前线程连接数
    SHOW STATUS LIKE 'Threads_running';        //查看当前线程运行连接数
    set global max_connections=1000;              //可以通过命令直接修改 
   
   

3.连接池配置
     spring.datasource.initialSize=5                        //初始化连接数
     spring.datasource.minIdle=5                            //最小连接数
     spring.datasource.maxActive=20                         //最大连接数
     spring.datasource.maxWait=60000                        //配置获取连接等待超时的时间,druid配置了maxWait之后,启用公平锁,并发效率会有所下降,一般不配置
     spring.datasource.timeBetweenEvictionRunsMillis=60000  //配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
     spring.datasource.minEvictableIdleTimeMillis=3000      //配置一个连接在池中最小生存的时间,单位是毫秒
     spring.datasource.validationQuery=SELECT 1             //配置检查执行的sql语句
     spring.datasource.testWhileIdle=true                   //是否空闲时检查      
     spring.datasource.testOnBorrow=false                   //是否从连接池中获取对象时检查      
     spring.datasource.testOnReturn=false                   //是否放回到连接池中时检查

 4.sql解析顺序
    1.FROM 子句 组装来自不同数据源的数据
    2.WHERE 子句 基于指定的条件对记录进行筛选
    3.GROUP BY 子句 将数据划分为多个分组
    4.使用聚合函数进行计算
    5.使用HAVING子句筛选分组
    6.计算所有的表达式
    7.使用ORDER BY对结果集进行排序


5.索引
      不是每个字段都是可以加索引的,比如文本太长的,mysql InnoDB最大长度是767字节,不支持全文索引。MyISAM才支持全文索引

      经常使用explain查看sql执行计划

      单表查询
      explain SELECT * from test_user  where user_name ="哈哈2017-02-15 14:39:09" 没加索引之前 231毫秒
      CREATE index index_name on test_user(user_name)   加索引之后  2毫秒  

      两表查询
      explain SELECT * from test_user user join test_teacher tracher on  user.foreign_key=tracher.id limit 10,10;  106毫秒
      CREATE index index_foreign_key on test_user(foreign_key) 加索引之后  1毫秒 


      放弃索引的几种情况

      1.like 全模糊的时候           like "%key%"
      2.使用mysql函数               CONCAT(加索引字段,"abc")
      3.where对字段进行NULL值判断   key is null
      4.where使用了or               key=1 or key1=2
      5.where使用不等号             key!=1  key<>1
      6.where使用not in             key  not in (1,2,3)
      7.全表扫描快于索引扫描

 6.常用sql的优化

     1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。


     2.字段最好不要给数据库留NULL,尽可能的使用NOT NULL填充数据库。

        优化方案:每个字段设置默认值

     3.应尽量避免在 where 子句中使用 != 或 <> 操作符

        比如 explain SELECT * from test_user  where user_name !="哈哈2017-02-15 14:39:09" 

        优化方案:
            explain  SELECT * from test_user  where user_name ="哈哈2017-02-15 14:39:09" or  user_name ="哈哈2017-02-15 14:39:10"


     4.应尽量避免在 where 子句中使用 or 来连接条件

          优化方案:业务需求没办法,必须确保or的字段都有索引,如果其中有一个没有加,都会放弃索引走全表扫描。

     5.应尽量避免在 where 子句中使用 not in 

         explain SELECT * from test_user  where user_name  not in ("哈哈2017-02-15 14:39:09","哈哈2017-02-15 14:39:10","哈哈12017-02-15 14:39:10")

         优化方案:看业务,not in应该用的很少,换其他方式或者用in。

     6.对于连续的数值,能用between就不要用in了,能用>和<也不用in

     7.尽量使用exists代替 in

     8.应尽量避免在 where 子句中对字段进行表达式操作,也就是函数或者运算  key-1=1 ,oracle是支持函数索引,貌似mysql5.7之后也会支持。
        explain SELECT * from test_user  where CONCAT(user_name,"1") ="哈哈2017-02-15 14:39:09" 

        优化方案 
        explain SELECT * from test_user  where user_name=CONCAT("哈哈2017-02-15 14:39:09","1")
    
     9.Update语句,如果只更改1,2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,还会更新索引,同时带来大量日志。、
          使用hibernate以及jpa  save和update操作 就会全量更新

          优化方案
           互联网高并发项目最好不用hibernate那种笨重的持久化框架,换mybatis手写sql的方式(这需要考验程序员的sql编写能力)
           如果用hibernate建议手写update更新,反对for循环出来一个个update并全量更新的代码

     10.limit越到后面越慢,数据越大越慢
           SELECT * from test_user  limit 1,10       1毫秒
           SELECT * from test_user  limit 500000,10  141毫秒
           
          优化方案
              根据业务需求  拆库拆表

     11.SELECT COUNT(*) from test_user 最好使用 count(id)

     12.索引并不是越多越好,索引虽然可以提高查询的效率,但同时也降低了插入及更新的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。但是外键必须要加上 比如loginId

     13.只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

     14.必须使用varchar/nvarchar 代替 char/nchar,  mysql5.5之后 默认字符就是Unicode编码,所以没有nvarchar类型

     15.尽量避免使用 select * ,应该用具体的字段列表代替*,不要返回用不到的任何字段。

     16.返回条数多的时候尽量使用limit分页

     17.避免使用 SELECT * from test_user 查询之后 list.size() 获取总条数

     18.join关联的时候,单表的where条件越多,联合查询越快

     19.最好使用 mysql5.7原生json类型存储json字符串

     20.只要一行数据时使用LIMIT 1

     21.每张表都必须要有一个自增长int的id主键,使用VARCHAR类型来当主键会使用得性能下降,主键的性能和设置变得非常重要,比如 集群拆表

     22.目前项目中最大瓶颈就数据库,必要时用memcached/redis缓存来降低数据库的负载。

  附 规范

     数据库boolean.....


   

© 著作权归作者所有

共有 人打赏支持
王小念和木子田博客
粉丝 179
博文 109
码字总数 81897
作品 0
浦东
程序员
私信 提问
“2017年度十大MVP”入选者干货好文回顾

“2017年度十大MVP评选”开始征集大众投票啦!为了方便大家回顾各位MVP入选者在这一年来的精彩技术贡献,投出你最宝贵的一票,小编在此整理了一份原创文章合集,点击文章标题即可阅读全文! ...

2017/11/10
0
0
你曾错过的2017十大MVP精彩好文,今天Repo回来!

“2017年度十大MVP评选”已圆满落幕,再次感谢这10位获奖专家及团队为知识传播的努力、对技术分享的热忱、为行业发展的贡献,在此将他们这一年来分享过的干货好文遴选出来,以便大家温故知新...

DBAplus社群
2017/11/20
0
0
MySQL数据库的性能的影响分析及其优化

MySQL数据库的性能的影响 一. 服务器的硬件的限制 二. 服务器所使用的操作系统 三. 服务器的所配置的参数设置不同 四. 数据库存储引擎的选择 五. 数据库的参数配置的不同 六. (重点)数据库的...

Panda_Jerry
2017/11/04
0
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
小蚂蚁学习mysql性能优化(1)--SQL以及索引优化

性能优化之mysql优化 可以从几个方面进行优化 硬件 系统配置 数据库表结构 SQL索引 成本从高到底,效果从低到高。 如何发现有问题的SQL? 使用mysql慢查询日志对有效率问题的sql进行监控。 ...

嗜学如命的小蚂蚁
2015/09/28
115
0

没有更多内容

加载失败,请刷新页面

加载更多

商品详情页上拉查看详情

商品详情页上拉查看详情 目录介绍 01.该库介绍 02.效果展示 03.如何使用 04.注意要点 05.优化问题 06.部分代码逻辑 07.参考案例 01.该库介绍 模仿淘宝、京东、考拉等商品详情页分页加载的UI效...

潇湘剑雨
19分钟前
0
0
Netty内存池之PoolArena详解

PoolArena是Netty内存池中的一个核心容器,它的主要作用是对创建的一系列的PoolChunk和PoolSubpage进行管理,根据申请的不同内存大小将最终的申请动作委托给这两个子容器进行管理。整体上,P...

爱宝贝丶
24分钟前
1
0
Django使用Channels实现WebSocket--下篇

希望通过对这两篇文章的学习,能够对Channels有更加深入的了解,使用起来得心应手游刃有余 通过上一篇《Django使用Channels实现WebSocket--上篇》的学习应该对Channels的各种概念有了清晰的认...

运维咖啡吧
31分钟前
2
0
linux下设置定时执行shell脚本的示例

很多时候我们有希望服务器定时去运行一个脚本来触发一个操作,比如说定时去备份服务器数据、数据库数据等 不适合人工经常做的一些操作这里简单说下 shell Shell俗称壳,类似于DOS下的command...

阿锋zxf
34分钟前
3
0
介绍Kubernetes监控Heapster

什么是Heapster? Heapster是容器集群监控和性能分析工具,天然的支持Kubernetes和CoreOS,Kubernetes有个出名的监控agent—cAdvisor。在每个kubernetes Node上都会运行cAdvisor,它会收集本机...

xiangyunyan
35分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部