SQL优化策略
mysql添加索引
1、主键索引
LATER TABLE ‘table_neme’ ADD PRIMARY KEY(‘column’);
2、唯一索引
unique空串(null)可以放多个 如果是具体的内容则不能重复
a: 肯定在where条经常使用 ;
b: 该字段的内容不是唯一的几个值(sex); (只有三个数据形成2级二叉树)
c: 字段内容不是频繁变化.
ALTER TABLE ‘table_name’ ADD UNIQUE (‘column’);
3、普通索引
ALTER TABLE ‘table_name’ ADD INDEX (‘column’);
4、全文索引(主要是针对对文件,文本的检索, 比如文章, 全文索引针对MyISAM有用)
ALTER TABLE ‘table_name’ ADD FULLTEXT (‘column’);
5、多列索引
ALTER TABLE ‘table_name’ ADD INDEX index_name(‘column1’,‘column2’,‘column3’);
更详细的添加索引的方法
mysql中可以使用alter table这个sql语句来为表中的字段添加索引。
ALTER TABLE <表名> ADD INDEX <字段>;
ag:我们来尝试为test中t_name字段添加一个索引
alter table test add index(t_name);
这个会产生结果,t_name字段的key这一览由原来的空白变成了MUL.
这个的含义就是该列的值是可以重复的,该列是一个非唯一索引的签到列,或者是一个唯一索引的组成部分但是可以含空值NULL。
SQL优化分三个方面:
1、慢查询
2、索引
SQL优化之索引:
1、在表中建立索引,优先考虑where、group by使用到的字段
2、尽量避免使用select ,返回无用的字段会降低查询效率。
优化方法:使用具体的字段代替,只返回使用到的字段。
3、尽量避免使用in和not in,会导致数据库引擎放弃索引,进行全表扫描。
特别说明,使用 in 超过四五个就不会使用索引了,也要避免使用。
如下:SELECT * FROM t WHERE id IN(2,3)
SELECT * FROM t1 WHERE usename IN(SELECT username FROM t2)
优化方法:如果是连续数值,可以用between代替,如下:
SELECT * FROM t WHERE id BETWEEN 2 AND 3
如果是子查询,可以使用exists代替,如下:
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM WHERE t1.username=t2.username);
4、尽量避免使用or,会导致数据库引擎放弃索引查询而进行全表扫描。如下:
SELECT * FROM t WHERE id = 1 OR id = 3;
优化方式:可以使用union代替or。如下:
SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3;
(ps:如果or两边的字段是同一个,如例子中的这样。貌似两者效率差不多,其实union扫描的索引,or扫描的是全表)
5、尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描,如下:
SELECT * FROM t WHERE username LIKE ‘%li%’;
优化方式:尽量在字段后面使用模糊查询,如下:
SELECT * FROM t WHERE username LIKE ‘li%’;
6、尽量避免进行null值的判断,会导致数据可引擎放弃索引而进行全表扫描。如下:
SELECT * FROM t WHERE score IS NULL;
优化方式:可以给字段添加默认值0,对0值进行判断。
如下:
SELECT * FROM t WHERE score = 0;
7、尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。如下:
SELECT * FROM t2 WHERE SCORE/10 = 9;
SELECT * FROM t2 WHERE SUBSTR(USERNAME,1,2)=‘li’;
优化方式:可以将表达式、函数操作移动到等号右侧。如下:
SELECT * FROM t2 WHERE score = 10*9;
SELECT * FROM t2 WHERE username LIKE ‘li%’;
8、当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描。
如下:
SELECT * FROM t WHERE 1=1;
优化方式:用代码拼装sql时进行判断,没where加where,有where加and.
SQL优化之慢查询:
1)、数据库中设置sql慢查询
一、第一步,开启mysql慢查询
方式一:修改配置文件,在my.ini增加几行,主要是慢查询的定义时间(超过2秒就是慢查询),以及慢查询log日志记录(slow_query_log)
//定义超过多少秒的查询是慢查询,这里定义的是2秒
long_query_time=2
//记录下没有使用索引的query
long_query_not_using_indexes
第二步:通过mysql数据库开启慢查询
mysql->set global slow_query_log=ON
mysql->set global long_query_time = 3600
mysql->set global long_querise_not_using_indexes = ON
执行计划
navicat可视化工具使用explain函数查看sql执行信息
type结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
all:全表扫描
index:另一种形式的全表扫描,只不过他的扫描方式是按照索引的顺序
range:有范围的索引扫描,相对于index的全表扫描,他有范围限制,因此要优于index
ref: 查找条件列使用了索引而且不为主键和unique。其实,意思就是虽然使用了索引,但该索引列的值并不唯一,有重复。这样即使使用索引快速查找到了第一条数据,仍然不能停止,要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表,因为索引是有序的,即便有重复值,也是在一个非常小的范围内扫描。
const:通常情况下,如果将一个主键放置到where后面作为条件查询,mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化,这个取决于优化器
一般来说,得保证查询至少达到range级别,最好能达到ref,type出现index和all时,表示走的是全表扫描没有走索引,效率低下,这时需要对sql进行调优。
extra:出现Using filesor或Using temproary时,表示无法使用索引,必须尽快做优化。
key:显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL
rows: 显示MySQL认为它执行查询时必须检查的行数。