7
2011
mysql优化工作学习笔记
mysql -r -u xxx -p 进入
show databases; 显示数据库
use databases; 切换数据库
show tables; 显示数据表
explain select * from test;
explain select * from test where “id=1″;
explain查询出来结果使用意义（key查询方式，是否基于索引，extra查询方式，是否使用索引，临时表，排序）
key: This column defi nes the index that is used for the given table. By default, MySQL usesonly one index per table. There are a small number of exceptions; however, it is best todesign your queries to use one optimal index per table.
➤ type: The type of index match that is being used. A few common values include:
➤ const: An exact index match
➤ ref: An index reference scan
➤ range: An index range scan
➤ ALL: A full table scan
➤ eq_ref: An equals reference on join tables
➤ unique_subquery: A subquery
extra: This includes various pieces of information including:
➤ Using index: This actually means that the index is all that is needed to satisfy the resultant columns of the SELECT. This is the most optimal solution.
➤ Using temporary: This indicates that MySQL needed to create an internal temporary table. It does not indicate a memory or on disk table.
➤ Using filesort: This indicates that MySQL has to internally sort the results.
SHOW CREATE TABLE [tablename]; 显示创建表语句
SHOW INDEXES FROM [tablename]; 显示表索引情况
查询结果分析查询未使用有效索引，给表新增有效索引
ALTER TABLE TABLENAME ADD INDEX (COLUMN1);
或许需要删除之前旧的索引，新增一个多列索引
ALTER TABLE TABLENAME DROP INDEX COLUMN1, ADD INDEX (COLUMN1, COLUMN2);
使用SHOW INDEXES FROM [tablename]; 显示表索引情况的时候
出现的结果中Cardinality 显示了索引使用的频率
select conv(substr(md5(‘this is a test’),1,16),16, 10);使用这种方式替代新建索引，flickr方式
优化索引建议：
创建多列索引
使用覆盖索引
使用部分索引（前缀，后缀索引）
使用覆盖索引的时候，注意where语句中如果是查询索引的后面列，可能导致无法使用索引，需要explain判断，重建覆盖索引
查看查询效率：
mysql> set profiling=1;
mysql>select * from test;
mysql> SHOW PROFILE;
SHOW PROFILE SOURCE;
+———————-+———–+
| Status | Duration |
+———————-+———–+
| (initialization) | 0.0000850 |
| checking permissions | 0.000013 |
| Opening tables | 0.000021 |
| System lock | 0.000008 |
| Table lock | 0.000027 |
| init | 0.00003 |
| optimizing | 0.000008 |
| statistics | 0.000026 |
| preparing | 0.00002 |
| executing | 0.000007 |
| Sending data | 0.00071 |
| end | 0.000011 |
| query end | 0.000006 |
| freeing items | 0.000011 |
| closing tables | 0.000011 |
| logging slow query | 0.000005 |
+———————-+———–+
16 rows in set (0.02 sec)
+———————-+———–+
| Status | Duration |
+———————-+———–+
| (initialization) | 0.0000850 |
| checking permissions | 0.000013 |
| Opening tables | 0.000021 |
| System lock | 0.000008 |
| Table lock | 0.000027 |
| init | 0.00003 |
| optimizing | 0.000008 |
| statistics | 0.000026 |
| preparing | 0.00002 |
| executing | 0.000007 |
| Sending data | 0.00071 |
| end | 0.000011 |
| query end | 0.000006 |
| freeing items | 0.000011 |
| closing tables | 0.000011 |
| logging slow query | 0.000005 |
+———————-+———–+
16 rows in set (0.02 sec)
UPDATE,DELETE优化与SELECT的类似，都可以转换为explain select来验证索引的使用，以做出调整
查询比较语句：
set profiling = 1;
select * from child where parent_id not in (select id from parent);
select parent_id, child_id from child left join parent on (parent.id = child.parent_id) where parent.id is NULL;
select query_id,count(*) as ‘# ops’, sum(duration) from information_schema.profiling group by query_id; //得出结果，2次查询执行操作与所用时间比较。
插入优化：
使用大容量插入方式：（不足：一条插入失败，后面的都失败）
INSERT INTO t1(c1,c2,c3,d) VALUES
(‘a’,’b’,’c’,CURDATE()),
(‘1’,’2’,’3’,CURDATE()),
(‘x’,’y’,’z’,CURDATE());
replace优化（replace的内部实现是使用的insert 和 update)
最好使用insert和update替代（replace由于意外的删除操作导致数据碎片）
INSERT … ON DUPLICATE KEY UPDATE
INSERT INTO film (film_id, description, title) VALUES
(500,‘Epic story featuring Krishna and Arjuna on the battlefield of Kurukshetra’,‘Mahabarata’)
ON DUPLICATE KEY UPDATE
film_id = 500,title = ‘Mahabarata’,description = ‘Epic story featuring Krishna and Arjuna on the Battlefield of Kurukshetra’;
error-log
my.cnf log-error定义
使用slow query log
在my.cnf中添加
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
列出数据库操作状态
SHOW PROCESSLIST
SHOW FULL PROCCESSLIST
kill id（processlist中的id来结束操作）
SHOW STATUS/SHOW GLOBAL STATUS: Shows the complete status of a given session or database
as a whole, and provides a wealth of information pertaining to the running status of
your MySQL instance.
➤ SHOW TABLE STATUS: Shows the status of tables on your MySQL instance.
➤ SHOW INNODB STATUS: Shows the status of the InnoDB storage engine. For any tables where
you are using InnoDB, this provides information pertaining to how those tables function.
➤ SHOW MASTER STATUS: Shows you the status of your binary log, if turned on.
➤ SHOW SLAVE STATUS: Shows you the status of the slave — information such as which relay
log the SQL slave thread is reading and which binary position the IO slave thread is reading
on the master, and the lag that exists between the slave and master, as well as other information
pertaining to the running state of replication.
本文连接地址: http://www.fresker.com/old2/archives/400 （转载注明出处）
目前暂无评论