12-mysql简单优化
1、运行情况查看
- 根据情况选择合适的存储引擎
# 查看所有运行情况，加global为运行至今
show status;
# 查看经历多少查询
show global status like '%Com_select%';
# 查看经历多少插入
show global status like '%Com_insert%';
- 根据服务器情况，设置系统最大支持多少个连接
# 查询服务器运行多长时间
show global status like '%update%';
# 查询当前有多少连接
show status like '%connect%';
# 查询当前有多少连接
show processlist;
# 查看最大并发连接数
show variables like '%max_connections%';
2、慢查询
- 慢查询：查询时间超过一定标准的查询，默认10s。
- 查看慢查询设置的时间：{show variables like '%long_query%';}。
- 找出哪些语句是慢查询，要开启慢查询日志。开启后，慢查询语句会记录到日志。慢查询日志默认关闭。
# 查看慢查询是否开启
show variables like '%log_slow_queries%';
# 开启慢查询方法：再配置文件添加如下内容
long-slow-queries=slow.log //开启慢查询，记录日志位置，默认为/var/lib/mysql
long-slow-not-using-index=on //开启表不使用索引也会计入日志
long-query-time=15 //定义为慢查询的时间
# mysqldumpslow -s c -t 10 //输出记录次数最多的10条sql语句
3、sql语句优化
(1) 对于表设计，尽量使用范围小类型的数据类型，且字段尽量设置为not null；能用数值型就不用字符型；
(2) 对于插入语句，提高插入数据速度，先关闭索引，再开启索引；
(3) 对于查询语句，应考虑在where及order by涉及的列上建立索引；
(4) 对于查询语句，应尽量避免在where子句中做如下操作，否则将引擎放弃使用索引而进行全表扫描；
# 使用!=或<>操作符
# or操作符
# null值判断
# %模糊查询
# 对字段进行表达式操作
# 对字段进行函数操作
(5) 对于查询语句，慎用in和not in，否则会导致全表扫描；对于连续的数值，能用between就不用in；
(6) 如果在 where 子句中使用参数，也会导致全表扫描,因为SQL只有在运行时才会解析局部变量;
例如下面查询语句：
select id from t where num=@num;
可强制使用索引，改为:
select id from t with(index(索引名)) where num=@num;
(7) 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致；
(8) 在连接查询中，主表比子表数据量大时，使用in，反之用exists，相差不大无区别；使用not exits任何时候都比not in效率高。in是把外表和内表作hash连接，而exists是对外表作loop循环；
(9) 并不是所有索引对查询都有效，当索引列有大量数据重复时，SQL查询可能不会去利用索引;
(10) 索引并不是越多越好，索引固然可以提高相应的select的效率，但同时也降低了insert及update的效率，一个表的索引数最好不要超过6个;
(11) 任何地方都不要使用 select * from t；
(12) 避免频繁创建和删除临时表，以减少系统表资源的消耗;
(13) 在存储过程中，如果使用了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定；
(14) 在存储过程中，尽量不使用游标，因为游标的效率较差。
(15) 在所有的存储过程和触发器的开始处设置SET NOCOUNT ON，在结束时设置SET NOCOUNT OFF,无需在执行每个语句后向客户端发送 DONE_IN_PROC消息。
