sql语句优化小技巧

优化group by语句

group by分组查询时，默认分组后，还会进行排序，可能会降低速度。
explain select * from table_name group by column;
extra :use filesort //默认使用了文件排序

• 解决方法
  在group by后面添加order by null防止默认排序

尽量使用join

• 子查询会产生临时表
• 使用join，MySQL不需要在内存中创建临时表

数据库存储引擎

MYISAM

• select ,insert速度比较快，锁机制采用表级锁，支持全文索引。由于是表级锁，对高并发性的update效率较低
• 对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

InnoDB

• 锁机制采用行级锁，支持事务，查询和写入速度低于myisam，但是安全性较好，不支持全文索引。由于是行级锁，对高并发性的update效率相对较高

两者区别

1. 事务安全
2. 查询和添加速度
3. 全文索引的支持
4. 锁机制
5. 外键MyISAM不支持外键，InnoDB支持外键(通常不设置外键，基本都是在程序中保持数据的一致)

Mysql数据库文件扩展名

• frm、MYD、MYI是myisam引擎表的结构文件，数据文件，索引文件
• CSM、CSV是和csv引擎表相关的文件， .csv是可以直接查看的

备注

表锁状态

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺

碎片整理

如果你的数据的存储引擎是MYISAM, 一定要定时进行碎片整理
命令：optimize table table_name

假设一个项目中的某张MyISAM表有100万条数据，其中有1/3重复，delete后，/data/table_name.MYD 并没有减少，导致项目越来越慢，所以应该对Myisam进行碎片整理