SQL性能优化常见措施
目 录
1、mysql中explain命令使用
2、mysql中mysqldumpslow的使用
3、mysql中修改my.ini配置文件记录日志
4、mysql中如何加索引
5、需求分析中考虑程序性能及配置事务
6、解决行思索的常用命令
一、mysql中explain命令使用
使用explain显示的信息可以帮助选择更好的索引和写出更优化的查询语句。MySQL的EXPLAIN语法常运行在SELECT语句上。
EXPLAIN SELECT * FROM assets_check_outer_order_res WHERE id = '1468289'
该语句为sql生成一个执行计划Query Execution Plan（QEP）。explain用于解释sql的执行计划，后边的sql不执行。在查询得到的结果中，possible_keys表示应用在这张表中的索引；
EXPLAIN SELECT * FROM assets_check_outer_order_res GROUP BY id
当前没有加任何索引，如果数据量大的话，查询时间明显会很长
EXPLAIN SELECT * FROM assets_check_outer_order_res USE INDEX (id) GROUP BY id
使用上述语句，添加了索引之后，查询速度明显变快很多。同时可以通过rows显示的行数，可以看到查询得到了很大成都上的优化。数据库具体是如何使用索引来执行的，有待进一步研究。
二、mysql中mysqldumpslow的使用
如果不知道是哪个sql慢，就开启mysql的慢查询日志。对记录的日志文件用mysql安装目录下的bin目录下的 mysqldumpslow查看。具体命令是 mysqldumpslow -s c -t 10 /path/to/slow.log。 可以提取出top10慢的sql语句模式。这样就找到了哪些语句慢。
- -s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
- -t, 是top n的意思，即为返回前面多少条的数据；
- -g, 后边可以写一个正则匹配模式，大小写不敏感的；
使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化的第一步，也是非常重要的一步。
三、mysql中修改my.ini配置文件记录日志
Windows下开启MySQL慢查询，MySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下面加上
log-slow-queries = F:\MySQL\log\mysqlslowquery.log和long_query_time = 2。
log-slow-queries = F:\MySQL\log\mysqlslowquery.log为慢查询日志存放的位置，一般这个目录要有MySQL的运行帐号的可写权限，一般都将这个目录设置为MySQL的数据存放目录；long_query_time=2中的2表示查询超过两秒才记录.
log-slow-queries=/data/mysqldata/slowquery.log long_query_time=2 log-queries-not-using-indexes 添加log-queries-not-using-indexes参数，表示记录下没有使用索引的查询
四、mysql中如何加索引
通过SQL来添加索引，或者Navicat视图中添加索引。
五、需求分析中考虑程序性能及配置事务
@Transcational（progration=Progration.supports rollback=Exception.class）
六、解决行思索的常用命令
如果在某一个业务逻辑中，需要更新多个表，涉及到多次与数据库中表的修改交互操作。那么，很可能在操作表中同一行数据的时候出现lock wait timeout exceeded异常，这个异常产生的原因是前一个JDBC事务占用改行的锁，后一个事务也一直试图去占用该行的锁，后一个事务一直去占用，等到好久还是没有拿到这个锁的话，就会出现这个异常，出现了这种死锁的情况。,Mysql的 InnoDB存储引擎是支持事务的，事务开启后没有被主动Commit。导致该资源被长期占用，其他事务在抢占该资源时，因上一个事务的锁而导致抢占失败！因此出现锁等待超时。
当在本机安装好mysql之后，会发现本地默认的有一个information_schema数据库，其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。它们实际上是视图，而不是基本表。
快捷键：选中当前行：shift+Home 或 Shift + End 执行当前行：ctrl + shift + R SHOW PROCESSLIST SHOW FULL PROCESSLIST SELECT * FROM information_schema.`PROCESSLIST` 当死锁发生时，用于显示当前跟数据连接的所有线程 kill 7658932 kill 线程ID：7658932，即可以杀死死锁的线程 SHOW CREATE TABLE assets_check_temp 显示assets_check_temp建表的SQL语句，同DDL效果 SHOW TABLE STATUS LIKE 'assets_check_%' SHOW ENGINE INNODB STATUS 显示了指定表的结构，创建时间、表的总列数 SELECT * FROM information_schema.INNODB_TRX SELECT * FROM information_schema.INNODB_LOCKS SELECT * FROM information_schema.INNODB_LOCK_WAITS 分别表示：当前运行的所有事务、当前出现的锁、锁等待的对应关系；trx是事务transaction的缩写。当有JDBC事务时，第一个表有数据。当产生锁等待的时候，第二个表中有数据。可以用于排查错误。 START TRANSACTION;UPDATE assets_check_temp SET id = '1' WHERE id = '1468300' 开启了了一个JDBC事务
附：参考博客
1. http://my.oschina.net/quanzhong/blog/222091 详细解释了innodb_trx innodb_locks innodb_lock_waits 三个表各个字段的含义。
2.
