mysql查询优化

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认为它执行查询时必须检查的行数。