mysql优化

执行计划:explain select

0.perror errorcode  查看错误原因

1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

4、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

5、下面的查询也将导致全表扫描：(不能前置百分号)

6、in 和 not in 也要慎用，否则会导致全表扫描

7、如果在where子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

10、不要在 where 子句中的“="左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。

12、不要写一些没有意义的查询，如需要生成一个空表结构

13、很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

19、任何地方都不要使用 select * from t ，用具体的字段列表代替“*"，不要返回用不到的任何字段

20.建立多个列的索引时，只要索引用到了前面的列，一般都会用到索引。

21、将一表数据插入到另一表中：

insert into table1(col1,col2,col3,col4) select distinct col1,col2,col3,col4 from table2 where col1=1;

22、查看分区表： EXPLAIN PARTITIONS SELECT * FROM table_name

23、消除外键依赖：set foreign_key_checks=0

24、防止unique或者primary key 则会按照对应的值进行更新，不会出现冲突问题：

insert into table set key1=values2, …… ON DUPLICATE KEY UPDATE key1=values1,……

25. 查看事件调度器开关状态： show variables like 'event_scheduler';

触发器查看 SELECT * FROM information_schema.triggers

存储过程状态  show procedure status

事件调度器状态 SHOW PROCESSLIST;  SELECT * FROM information_schema.EVENTS;

26 查看表信息：show create table table_name

27.清空查询缓存： a. FLUSH QUERY CACHE; // 清理查询缓存内存碎片。b. RESET QUERY CACHE; // 从查询缓存中移出所有查询。

28. 查看语句执行时间:  SHOW VARIABLES LIKE '%pro%';  1. set  profiling = 1;  2. show profiles

查看某一个语句的时间：SHOW PROFILE FOR QUERY n，这里的n就是对应SHOW PROFILES输出中的Query_ID。

SHOW PROFILE CPU FOR QUERY n

29.导出數據库為dbname的表结构（其中用戶名為root,密码為dbpasswd,生成的脚本名為db.sql）

mysqldump -uroot -pdbpasswd -d dbname >db.sql;

导出數據库為dbname某张表(test)结构

mysqldump -uroot -pdbpasswd -d dbname test>db.sql;

导出數據库為dbname所有表结构及表數據（不加-d）

mysqldump -uroot -pdbpasswd  dbname >db.sql;

导出數據库為dbname某张表(test)结构及表數據（不加-d）

mysqldump -uroot -pdbpasswd dbname test>db.sql;

导出数据到txt文件

mysqldump -uroot -proot mysqltest tablename -t -T /tmp --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --lines-terminated-by="\n";

导出：

mysqlimport -uroot -proot mysqltest /tmp/Map.txt --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --lines-terminated-by="\n";

30.msyql.sock丢失：mysql -protocol=TCP -uroot -p -P3006 -hlocalhost

load data infile '/var/lib/mysql/bak/qcell.csv' into table tablename FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';

select * from tblname into outfile '/var/lib/mysql/bak/test.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';