《MYSQL语句和表的优化》由会员分享，可在线阅读，更多相关《MYSQL语句和表的优化（11页珍藏版）》请在金锄头文库上搜索。
1、MYSQL语句和表的优化- 优化 SQL 的一般步骤- 一、通过 show status 和应用特点了解各种 SQL 的执行频率/*通过 SHOW STATUS 可以提供服务器状态信息，也可以使用 mysqladmin extended-status 命令获得。SHOW STATUS 可以根据需要显示 session 级别的统计结果和 global 级别的统计结果。以下几个参数对 Myisam 和 Innodb 存储引擎都计数：1.Com_select 执行 select 操作的次数，一次查询只累加 1；2.Com_insert 执行 insert 操作的次数，对于批量插入的 insert 操
2、作，只累加一次；3.Com_update 执行 update 操作的次数；4.Com_delete执行 delete 操作的次数；*/SHOW STATUS WHERE Variable_name = Com_select;/*以下几个参数是针对 Innodb 存储引擎计数的，累加的算法也略有不同：1.Innodb_rows_read select 查询返回的行数；2.Innodb_rows_inserted 执行 Insert 操作插入的行数；3.Innodb_rows_updated 执行 update 操作更新的行数；4.Innodb_rows_deleted 执行 delete 操作删
3、除的行数；通过以上几个参数，可以很容易的了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会累加。对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。此外，以下几个参数便于我们了解数据库的基本情况：1.Connections 试图连接 Mysql 服务器的次数2.Uptime 服务器工作时间3.Slow_queries 慢查询的次数二、定位执行效率较低的 SQL 语句可以通过
4、以下两种方式定位执行效率较低的 SQL 语句：1.可以通过慢查询日志定位那些执行效率较低的 sql 语句，用-log-slow-queries=file_name选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。可以链接到管理维护中的相关章节。2.慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看 SQL 执行情况，同时对一些锁表操作进行优化。*/SHOW
5、 PROCESSLIST;/*三、通过 EXPLAIN 分析低效 SQL 的执行计划通过以上步骤查询到效率低的 SQL 后，我们可以通过 explain 或者 desc 获取 MySQL 如何执行 SELECT 语句的信息，包括 select 语句执行过程表如何连接和连接的次序。explain 可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的 SELECT。*/EXPLAIN SELECT * FROM message a LEFT JOIN mytable b ON a.id = b.id WHERE a.id=1;/*返回结果+-+-+-+-+-+-+-+-+-+-+|
6、 id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra |+-+-+-+-+-+-+-+-+-+-+| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 | | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | | 9999 | |+-+-+-+-+-+-+-+-+-+-+select_type：select 类型table： 输出结果集的表type： 表示表的连接类型当表中仅
7、有一行是 type 的值为 system 是最佳的连接类型；当 select 操作中使用索引进行表连接时 type 的值为 ref；当 select 的表连接没有使用索引时，经常会看到 type 的值为 ALL，表示对该表进行了全表扫描，这时需要考虑通过创建索引来提高表连接的效率。possible_keys：表示查询时,可以使用的索引列.key： 表示使用的索引key_len： 索引长度rows： 扫描范围Extra：执行情况的说明和描述四、确定问题，并采取相应的优化措施经过以上步骤，基本可以确认问题出现的原因，可以根据情况采取相应的措施，进行优化提高执行的效率。例如上面的例子，我们确认是对
8、b 表的全表扫描导致效率的不理想，我们对 b表的 id 字段创建了索引，查询需要扫描的行数明显较少。返回结果+-+-+-+-+-+-+-+-+-+-+| id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra |+-+-+-+-+-+-+-+-+-+-+| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 | | 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 4 | con
9、st | 1 | |+-+-+-+-+-+-+-+-+-+-+大批量插入数据时优化 SQL 语句=一、对于 Myisam 类型的表，可以通过以下步骤快速的导入大量的数据。 ALTER TABLE tablename DISABLE KEYS;批量插入数据ALTER TABLE tablename ENABLE KEYS;前后两个命令用来打开或者关闭 Myisam 表非唯一索引的更新。在导入大量的数据到一个非空的 Myisam 表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的 Myisam 表，默认就是先导入数据然后才创建索引的，所以不用进行设置。*/ALTER TABL
10、E mytable DISABLE KEYS;INSERT INTO mytable(id, username, city, age) VALUES(1, name1, city1, 10),(2, name2, city2, 20),(3, name3, city3, 30);ALTER TABLE mytable ENABLE KEYS;/*二、而对于 Innodb 类型的表，这种方式并不能提高导入数据的效率。对于 Innodb 类型的表，我们有以下几种方式可以提高导入的效率：因为 Innodb 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效
11、率。如果 Innodb 表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行 SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。*/SET UNIQUE_CHECKS=0;SET UNIQUE_CHECKS=1;SET AUTOCOMM
12、IT=0;SET AUTOCOMMIT=1;/*优化 insert 语句=1、如果同时插入很多行，请使用多个值的 INSERT 语句。这比使用分开 INSERT语句快(在一些情况中几倍)。Insert into test values(1,2),(1,3),(1,4)2、如果从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。 Delayed 的含义是让 insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入；3、将索引文件和数据文件分
13、在不同的磁盘上存放（利用建表中的选项）；4、如果批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是，这只能对 myisam 表使用；5、当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍；6、根据应用情况使用 replace 语句代替 insert；7、根据应用情况使用 ignore 关键字忽略重复记录。*/INSERT DELAYED INTO mytable(id, username, city, age) VALUES(4, name4, city4, 40);INSERT LOW_PRIORITY INTO mytable(id, username, city, age) VALU
