mysql数据库查询优化

1.建立合适的索引：索引创建时选择数据项的差异性高，联合索引查询要遵循前缀原则等。

创建索引：

CREATE INDEX indexName ON table_name (column_name)

修改表结构(添加索引):

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定:

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))   
);

删除索引的语法:

DROP INDEX [indexName] ON mytable; 

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

2.开启慢查询日志，记录执行速度慢的SQl，对慢SQL语句进行优化。

开启慢查询日志
1.通过配置文件开启

在MySQL的配置文件（通常是my.cnf或my.ini）中，可以设置以下选项来开启慢查询日志：

[mysqld]
slow_query_log = 1
slow_query_log_file = /path/to/your/slow-query.log
long_query_time = 2
字段解释
slow_query_log：设置为1以开启慢查询日志。
slow_query_log_file：指定慢查询日志的文件路径。
long_query_time：设置查询执行的时间阈值（秒）。执行时间超过此值的查询将被记录。例如，long_query_time = 2表示记录所有执行时间超过2秒的查询。
修改配置后，需要重启MySQL服务以使更改生效。

2.通过MySQL命令行动态开启

也可以在不重启MySQL服务的情况下，通过命令行动态设置慢查询日志：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/path/to/your/slow-query.log';
SET GLOBAL long_query_time = 2;
这种方法的好处是立即生效，但重启MySQL服务后设置将丢失，除非也在配置文件中进行了相应的设置。

查看慢查询日志
慢查询日志本质上是一个文本文件。可以使用任何文本编辑器或命令行工具查看：
less /path/to/your/slow-query.log

3.优化SQL语句执行效率

(1) 优化查询过程中的数据访问
1.访问数据太多导致查询性能下降
确定应用程序是否在检索大量查过需要的数据，可能是太多行或列
确认MySQL服务器是否在分析大量不必要的数据行
查询时有大量的记录，使用LIMIT分页解决。
多表关联返回全部列，指定列名。
总是取出全部列，SELECT * 会让优化器无法完成索引覆盖扫描的优化
重复查询相同的数据，可以缓存数据，下次直接读取缓存
2.是否在扫描额外的记录：
使用explain来进行分析，如果发现查询需要扫描大量的数据但只返回少数的行可以通过如下技巧去优化：
① 使用索引覆盖扫描，把所有用到列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果；
② 改变数据库和表的结构；
③ 修改数据表范式；
④ 重写SQL语句，让优化器可以以更优的方式执行查询

(2) 优化长难的查询语句
① 一个复杂查询好哪还是多个简单查询好哪
MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多。
使用尽可能少的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。

 ② 切分查询
	将一个大的查询分为多个小的相同的查询。
	比如：一次性删除1000万的数据要比一次删除1万暂停一会的方案更加损耗服务器开销

 ③ 分解关联查询
	可以将一条关联语句分解成多条SQL来执行，让缓存的效率更高，执行单个查询可以减少锁的竞争， 在应用层做关联可以更容易对数据库进行拆分，查询效率		    				       会大幅提升，较少冗余记录的查询。 

(3) 优化特定类型的查询语句
①优化count()查询
count() 和 count(1)和count(列名)区别：
count()中的 * 会忽略所有的列，直接统计所有的行数，因此不要使用count(列名)
执行效率上：
列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（）最优
MyISAM是默认存储引擎（Mysql5.1前）.MyISAM中，没有任何WHERE条件的count(*)非常快，当有WHERE条件，MyISAM的count统计不一定比其他表引擎快

② 优化关联查询

确定ON或者USING子句的列上有索引

确保GROUP BY和ORDRY BY 中只有一个表中的列，这样MySQL才可能使用索引

③ 优化子查询(嵌套查询)

尽可能使用关联查询来替代

④优化GROUP BY 和 DISTINCT

这两种查询均可以用索引来优化，是最有效的优化方法
如果不需要ORDER BY，进行GROUP BY时使用ORDER BY NULL，MySQL不会再进行文件排序。
WITH ROLLUP 超级聚合，可以挪到应用程序处理

⑤ 优化LIMIT分页

LIMIT偏移量大的时候，查询效率较低
可以记录上次查询的最大ID，下次查询时直接根据ID来查询

⑥优化UNION查询

UNION ALL的效率高于UNION

mysql 执行计划详解

什么是Sql执行计划？
执行计划，简单的来说，是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景。在MySQL使用 explain 关键字来查看SQL的执行计划。（这里我的理解是，存储引擎在执行sql的时候，把一条sql分解，列出来每一步需要干什么，并按照步骤依次执行，这样我们就能看出来哪个步骤耽误了时间）
通过EXPLAIN SELECT * from A where X=? and Y=?
执行计划一般包括如下：

1.id
id相同,执行顺序由上而下，id不同,值越大越先被执行

2.selectType
SIMPLE： 表示此查询不包含 UNION 查询或子查询
PRIMARY： 表示此查询是最外层的查询
SUBQUERY： 子查询中的第一个 SELECT
UNION： 表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT, UNION 的结果
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询）

3.table
table表示查询涉及的表或衍生的表

4.type
这个字段是优化sql的重要字段，也是我们判断sql性能和优化程度重要指标。他的取值类型范围：

const:当查询最多匹配一行时，常出现于where条件是＝的情况，一般常用于等值扫描或者唯一性索引扫描，比如select * from A where A.id=2，这种非常快
system: 是const的特殊情况，此时说明表中只有一行数据，直接返回。
eq_ref:这种相当于多表之间关联查询，select A.* from A,B where A.id=B.userId,其中A中id对于B.userId是一一对应的，此时查询效率较高
ref: 此类型通常出现在多表的联合 查询，使用了非唯一或非主键索引，或者匹配最左前缀规则索引的语句。比如select A.* from A where A.name=‘a’ and A.sex=‘m’，name和sex构成一个组合索引
range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。比如select A.* from A where A.time between a and c
index: 表示全索引扫描(full index scan)， index 类型扫描所有的索引， 而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据，但是索引本身数量很多，内容很大，那么执行效率很低。
ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。 那么基本就是随着表的数量增多，执行效率越慢。
执行效率：

ALL < index < range< ref < eq_ref < const < system。对于我们平时查询执行计划，对于优化sql来说，最好是避免ALL和index

5.possible_keys
它表示Mysql在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一定会用到。

6.key
此字段是 mysql 在当前查询时所真正使用到的索引。 他是possible_keys的子集

7.key_len
表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，这也是我们优化sql时，评估索引的重要指标

8.ref 连接匹配条件,如果走主键索引的话,该值为: const, 全表扫描的话,为null值
表示哪些列或常量被用于查找索引列上的值
该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引（换句话说，就是连接程序无法根据键值只取得一条记录）的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。 ref还可以用于检索字段使用=操作符来比较的时候。以下的几个例子中，MySQL将使用 ref 来处理ref_table，和eq_ref的区别是-用到的索引是否唯一性

9.rows
rows 也是一个重要的字段，mysql 查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化之后，扫描读取的行数越多，说明要么是索引设置不对，要么是字段传入的类型之类的问题，说明要优化空间越大

10.extra
using filesort ：表示 mysql 对结果集进行外部排序，不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大，延时大。
using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。
using temporary：查询有使用临时表, 一般出现于排序， 分组和多表 join 的情况， 查询效率不高，建议优化。
using where ：sql使用了where过滤,效率较高。