SQL优化

使用show status了解数据库的操作执行频率

通过show [session（当前连接）|global(从上次启动开始)] status like
‘Com_[select/insert/update/delete/commit/rollback]%’;查看当前库走插入多还是查询多
status like ‘Innodb_[rows_read/rows_inserted/rows_update/rows_deleted]%’;查看当前InnoDB引擎操作的累计次数

定位执行效率较低的SQL语句

1. 开启记录数据库慢查询日志 应用使用Druid记录慢SQL日志
2. 使用show full processlist查看当前MySQL在进行的线程，以及线程的状态、是否表锁，以及SQL的执行情况，同时对锁表情况优化。
3. 确定慢SQL后，使用EXPLAN分析SQL执行计划,查询类型(type)是简单表查询（SIMPLE）、主查询（PRIMARY）、连接查询（UNION） 观察SQL的各个步骤的执行顺序
   查看SQL的访问类型是否合理 const < eq_ref < ref < range < index<all
4. 查看是否能用到索引，实际用没有用索引
5. 查看Extra额外字段信息描述，是否合理 Extra分析（转）

通过show profile分析SQL

使用select @@have_profiling;查看是否支持profile
开启profiling,set profiling=1;session级别开启profiling的支持

show profiles; 查看所有查询的耗时

show profile for query [query ID];查看单个SQL各个阶段耗时

大批量插入数据

1. 在MyISAM导入数据时先DISABLE KEYS，导入之后再ENABLE KEYS打开索引
2. 在InnoDB引擎中，按照主键顺序导入
3. 在导入数据前SET UNIQUE_CHECKS = 0,关闭唯一性校验，导入结束后SET UNIQUE_CHECKS=1恢复唯一性校验
4. 在导入前执行SET AUTOCOMMIT=0,关闭自动提交，导入之后再执行SET AUTOCOMMIT=1

优化INSERT语句

1.使用多值插入代替单个INSERT语句执行
2.通过文件载入表，LOAD DATA INFILE

优化ORDER BY语句

Mysql有两种排序方式

尽量减少额外的排序，通过索引直接返回有序数据

1. 通过有序索引顺序扫描，Extra显示Using Index不需要额外排序
2. 对返回数据进行排序（文件排序），Extra显示Using filesort不需要额外排序

##可以使用索引

#索引字段排序顺序一致
select * from tablename order by key_part1,key_part2,..
#索引字段与order字段按照索引顺序
select * from tablename where key_part1 = constant order by key_part1 desc,key_part2 desc
#索引字段排序顺序一致
select * from tablename order by key_part1 desc,key_part2 desc
##---------------------------------------------------------------
##不使用索引

#索引字段排序不是相同顺序
select * from tablename order by key_part1 asc,key_part2 desc,..

Mysql文件排序
两次扫描算法：先根据条件取出排序字段和行指针，之后在缓冲器区排序。缓冲区内存不够，在临时表中存储数据进行排序，完成后根据行指针取出数据。
优点：排序内存开销比较小 一次扫描算法： 一次取出索引字段，然后在缓冲区排序后直接返回，排序的内存开销比较大。
通过max_length_for_sort_data的大小和Query语句取出的数据字段大小。

使用group by做分组查询时，可以指定order by null来避免排序

优化嵌套查询

将子查询转换为关联查询，这样减少临时表的的创建

对OR进行优化

对OR修饰的字段分别添加单值索引，Extra:Using union(index_aaa,index_bbb)

分页优化

limit offset,num，Mysql先取出offset+num条记录，再返回offset+1到offset+num条记录
将limit m,n转换为limit n的查询

使用SQL提示

select * from tablename [keywos] where …

• 使用USE INDEX使引擎参考指定索列表
• 使用IGNORE INDEX使引擎忽略指定索引
• 使用FORCE INDEX使引擎强制使用指定索引