mysql数据库SQL优化

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