清空表的所有数据
【低效方式：一条一条的删除】 DELETE FROM table_name; 【推荐方式:效率高】
TRUNCATE table_name;
正则表达式
SELECT name FROM t1 WHERE email REGEXP "@163[.,].com$";
提取随机行
SELECT * FROM t1 ORDER BY RAND() LIMIT 3;【使用rand()函数会使程序的效率降低】
显示表结构
SHOW CREATE TABLE table_name;
利用GROUP BY 的 WITH ROLLUP（不可于ORDER BY 同时使用）
SELECT city_name,province_name,count(province_name) FROM t1 GROUP BY city_name,province_name WITH ROLLUP;【能够检索出来更多的信息，例如province_name为NULL的行也会检索出来，注意count(province_name)不可少】
SQL语句优化
优化SQL语句的一般步骤 1.通过show status 命令 了解各种SQL语句的执行频率 show [session|golbal]status; 其中session（默认）表示当前连接，global表示自数据库启动至今 show status; show global status; show status like 'Com_%';
一般查询以Com_insert%,Com_select%,Com_delete%,Com_update%开头的状态
show status like 'Com_select%';
【Com_XX】
Com_select 执行select的操作次数
Com_update 执行update的操作次数
Com_insert 执行insert的操作次数
Com_delete 执行delete的操作次数
【针对innodb存储引擎的】
InnoDB_rows_read 执行select操作的次数
InnoDB_rows_updated 执行update操作的次数
InnoDB_rows_inserted 执行insert操作的次数
InnoDB_rows_deleted 执行delete操作的次数
【其他】
connections 连接MYSQL的数量
Uptime 服务器已工作的秒数
Slow_queries 慢查询次数
2.定位执行效率较低的语句
1)explain select * from table where id = 1000;
2)desc select * from table where id = 1000;
打印sql语句的执行信息
重点查看 possible keys（可能的索引），rows，Extra，key（索引）
3.学会使用慢查询日志
索引 一般用在条件后的字段 例如 where ,order by , having, group by 使用like查询时 后面如果是常量并且只有%号不再第一个字符,索引才有可能被使用 如果对大文本进行搜索,使用全文索引 而 不使用 like "%..%" 如果列名是索引 使用column_name is null 将使用索引 使用desc select * from t1 where... 来查看索引的使用情况 存在索引 但不使用索引 1. 如果mysql估计使用索引比全表扫描更慢,则不使用索引,例如 如果列key_part1 均匀分布在1到100之间,查询使用索引就不是很好 2. 如果使用MEMORY/HEAP表并且where条件中不使用"="进行索引列,那么讲不会用到索引 3. 用or或者and分割开的条件,如果or前的条件中的列有索引,而后面的列没有索引,那么涉及的索引都不会被用到,除非or两边的条件字段全都有索引那么索引就会被使用 4.如果列类型是字符串,但在查询时把一个数值型常量赋值给了一个字符串列名name,那么虽然在name列上有索引,将不会被使用 如果索引正在工作 那么handler_read_key的值将很高,这个值代表了一个行被索引值读取的次数,handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引,补救 show status like "handler_read%" 检测表是否有错误的语法 check table table_name 定期优化表(不要在负载很大的时候使用) OPTIMIZE table table_name 如果已经删除了表的一大部分,或者如果已经对含有可变长度行的表进行了很多的改动,则需要做定期优化,这个命令可以将表空间的碎片进行合并,但是此命令只对MyISAM,BDN和InnoDB表起作用. 大批量导入导出数据 当用load命令导入数据的时候,适当设置可以提高导入的速度 ,对于MyISAM引擎的表 可以通过一下方式快速导入大量的数据 infile,outfile 导出 mysql> select name from t1 into outfile "tmp/test.txt"; 导入数据 mysql> load data infile "/tmp/test.txt" into table t1(name) 关闭索引再导入数据 也能加速(对INNODB无效)(不建议关闭唯一索引) alter table table_name disable keys alter table table_name enable keys 优化insert 语句 insert into table_name values (),(),(),...语句可以得到更高的效率 优化group by 语句 关闭group by 的自动排序(升序) select * from t1 group by name order by null 优化嵌套查询 尽量避免使用子查询 如果使用联结查询能达到同样效果 优先用联结查询 子查询会导致主表可能无法使用索引 例子: select * from t1 where uid in (select id from t2) select * from t1 a,t2 b where a.uid=b.id 数据库优化 提高统计查询的速度 :利用视图当作中间表来提高效率 MYSQL服务器优化 1.读锁 lock table t1 read(同时间只有一个人能修改数据,其他用户可以读取数据) 2.写锁 lock table t1 write只有本人能够修改数据(其他用户无法读取或者修改) 3.解锁 unlock tables; 4.四种字符集 [mysqld] Server characterset Db characterset [client] Client characterset Conn. characterset 5.bin-log日志 查看是否开启show variables like "%bin%"; 在/etc/my.cnf 里加入log-bin 的配置 6.slow-queries-log 修改my.cnf 在[mysqld]下加入 log_slow_queries = slow.log long_query_time = 5 (大于5秒的将写入慢查询日志) 7.socket /tmp/mysql.sock 登录的时候会用到这个文件
