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、索引
3、拆分表

SQL优化之索引：
1、在表中建立索引，优先考虑where、group by使用到的字段
2、尽量避免使用select *,返回无用的字段会降低查询效率。
优化方法：使用具体的字段代替*，只返回使用到的字段。
3、尽量避免使用in和not 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

 

未完，待续