前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >mysql数据库SQL优化

mysql数据库SQL优化

作者头像
王念博客
发布2019-07-24 10:34:25
2.4K0
发布2019-07-24 10:34:25
举报
文章被收录于专栏:王念博客

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.....

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档