TRUNCATE TABLE 比 DELETE速度快,且使用的系统和事务日志资源较少,也可以直接释放磁盘空间,但TRUNCATE无事务且不触发trigger,有可能造成事故,故不建议在代码中使用此语句。
TRUNCATE TABLE在功能上与不带where子句的delete语句相同。
4.6 避免使用SELECT *如果不必要取出所有数据,不要用 * 来代替,应给出字段列表。
4.7 使用索引做条件查询count(*)innodb引擎在统计方面和myisam是不同的,Myisam内置了一个计数器,所以在使用 select count(*) from table 的时候,直接可以从计数器中取出数据。而innodb必须全表扫描一次方能得到总的数量。每执行一次扫描一次,代价非常高。需要进行count(*)统计表记录总数时,加上secondary index扫描条件,可以加快扫描速度。例如:SELECT COUNT(*) FROM sbtest1 WHERE id>=0;
4.8 避免IN子句使用 IN 或 NOT IN 子句时,特别是当子句中有多个值且表数据较多时,速度会明显下降。可以采用连接查询或外连接查询来提高性能。
4.9 避免不必要的排序不必要的数据排序大大的降低系统性能。
比如:在使用group by col的时候,mysql会默认order by col ,在只需要分组不需要排序的情况下,可以使用GROUP BY col ORDER BY NULL提升执行效率,仅仅对col列分组,而不排序。
4.10 合理利用最左索引组合索引的生效原则是:从前往后依次使用生效,如果中间某个索引没有使用,那么断点前面的索引部分起作用,断点后面的索引没有起作用。对于组合索引,注意索引的使用顺序,where子句中将最左索引放在第一列。
比如:(a,b,c) 三个列上加了联合索引(是联合索引,不是在每个列上单独加索引)where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点,全部发挥作用 where a=3 and c=5... 这种情况下b就是断点,a发挥了效果,c没有效果where b=3 and c=4... 这种情况下a就是断点,在a后面的索引都没有发挥作用,这种写法联合索引没有发挥任何效果where b=45 and a=3 and c=5 .... 这个跟第一个一样,全部发挥作用,abc只要用上了就行,跟写的顺序无关 。
4.11 多表连接做多表操作时,应该给每个表取一个别名,每个表字段都应该标明其所属哪个表。
为关联操作的字段建立索引,并使用统一数据类型,不同数据类型做关联时,MySQL会进行隐式转换,导致无法用到索引,开销较大。
多表连接个数建议不超过3个。
4.12 避免在where后的索引字段上使用函数在where后的索引字段上使用函数会导致索引失效,严重情况下会拖慢整个数据库实例的速度。
例如:
SELECT orderid
FROM order_detail
WHERE from_unixtime(create_time)>\'2017-12-04 12:00:00\';
这样使用函数会导致查询条件不使用索引,使查询性能下降。应改为:
SELECT orderid
FROM order_detail
WHERE create_time>unix_timestamp(\'2017-12-04 12:00:00\');
4.13 尽量不要做’%’前缀模糊查询col like “abc%” 能用上索引,而col like “%abc”不能用上索引
4.14 使用UNION ALL代替UNIONUNION合并两个或多个SELECT语句的结果集,并消去表中任何重复行。而UNION ALL不会消除重复行。从效率上说,UNION ALL要比UNION快很多,所以如果可以确认合并的多个结果集中不包含重复数据时,建议使用UNION ALL。
4.15 尽量避免OR操作通常情况下,如果条件中有or,即使其中有条件带索引也不会使用,所以除非每个列都建立了索引,否则不建议使用OR。在多列OR中,建议用UNION ALL替换。
比如:
select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067
and (f_mobile =\'1234567891\' or f_phone =\'1234567891\' );
应改为:
select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067
and f_mobile =\'1234567891\'
UNION ALL
select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067
and f_phone =\'1234567891\'
相同字段or可改成 in,如 f_id=1 or f_id=100 --> f_id in (1,100)。
4.16 MySQL 在否定条件中不能使用索引例如,where 条件里面有<>、not in 、not exists的时候,即便是在这些判断字段上加有索引,也不会起作用。
4.7 MySQL 在JOIN中连接字段类型如果不一致,则不能使用索引