说到sql优化首先要说的就是sql查询的一个机制,回表。
一般查询都会根据索引找到指定的记录所在行后,再根据rowid再次到数据块里取数据的操作。这个根据rowid再次取数据的过程就叫回表。也就是说有回表操作时消耗性能至少翻倍。所以避免了回表我们就能大大的优化这个sql。
这里就要用到索引,索引的添加方法如下:
1.添加PRIMARY KEY(主键索引)
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (
`column`
)
3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
那么怎么避免回表呢,查询的时候只查索引,不要用select *就能避免回表。但是我们不可能所有的字段都设成索引,所以怎么样在查询索引以外的字段情况下避免回表呢?可以使用子查询来实现:
例如:
-- 优化前SQL
SELECT 各种字段
FROM `table_name`
WHERE 各种条件
LIMIT 0,10;
-- 优化后SQL
SELECT 各种字段
FROM `table_name` main_tale
RIGHT JOIN
(
SELECT 子查询只查主键
FROM `table_name`
WHERE 各种条件
LIMIT 0,10;
) temp_table ON temp_table.主键 = main_table.主键
使用like时尽量避免%*%的用法,使用*%为宜。将导致全表扫描导致速度较慢,如果必须要使用的话,可以考虑分库分表来降低数据库的数据量。
分表:将数据分为多个表的方式存储数据。分为横切和竖切。横切就是字段不变,将数据分为多分放在多个表中。例如根据时间段分离。竖切就是数据量不变,字段分为若干份。