sql优化

1，SQL查询之语法顺序和执行顺序（重要）
MySQL查询语法顺序
1.SELECT
2.FROM
3.LEFT JOIN
4.ON
5.WHERE
6.GROUP BY
7.HAVING
8.ORDER BY
9.LIMIT
执行顺序
示例SQL：
SELECT * FROM user LEFT JOIN order ON user.id = order.uid WHERE order.price > 1000 GROUP BY user.name HAVING count(1) > 5 ORDER BY user.name LIMIT 0,10
1.FROM（将最近的两张表，进行笛卡尔积）—VT1
2.ON（将VT1按照它的条件进行过滤）—VT2
3.LEFT JOIN（保留左表的记录）—VT3
4.WHERE（过滤VT3中的记录）–VT4…VTn
5.GROUP BY（对VT4的记录进行分组）—VT5
6.HAVING（对VT5中的记录进行过滤）—VT6
7.SELECT（对VT6中的记录，选取指定的列）–VT7
8.ORDER BY（对VT7的记录进行排序）–游标
9.LIMIT（对排序之后的值进行分页）
WHERE条件执行顺序（影响性能）
1.MYSQL：从左往右去执行WHERE条件的。
2.Oracle：从右往左去执行WHERE条件的。
结论：写WHERE条件的时候，优先级高的部分要去编写过滤力度最大的条件语句。

2，查询原理
第一、单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。
第二、两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
第三、多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

3，日志文件
MySQL通过日志记录了数据库操作信息和错误信息。常用的日志文件包括错误日志、二进制日志、查询日志、慢查询日志和 InnoDB 引擎在线 Redo 日志、中继日志等。

1）错误日志（err log）： * 默认是开启的，而且从5.5.7以后无法关闭错误日志 *
记录了运行过程中遇到的所有严重的错误信息,以及 MySQL每次启动和关闭的详细信息。 *
默认的错误日志名称：hostname.err *
错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中log-err是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。

• log_error可以直接定义为文件路径，也可以为ON|OFF；log_warings只能使用1|0来定义开关启动
  2）二进制日志（bin log）： *
  默认是关闭的，需要通过配置：log-bin=mysql-bin进行开启。其中mysql-bin是binlog日志文件的basename，binlog日志文件的名称：mysql-bin-000001.log
• binlog记录了数据库所有的ddl语句和dml语句，但不包括select语句内容，语句以事件的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息，binlog主要作用是用于恢复数据，因此binlog对于灾难恢复和备份恢复来说至关重要。
• 如果是DDL语句，则直接记录到binlog日志，而DML语句，必须通过事务提交才能记录到binlog日志中。 * binlog还用于实现mysql主从复制。 * binlog还用于数据恢复。
  3）通用查询日志（general query log）：
• 默认情况下通用查询日志是关闭的。
• • 由于通用查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。如若不是为了调试数据库的目的建议不要开启查询日志。
    4）慢查询日志（slow query log）：
• 默认是关闭的。需要通过设置：slow_query_log=ON进行开启。
• 记录执行时间超过long_query_time秒的所有查询，便于收集查询时间比较长的SQL语句 事务日志：
  5） 事务日志（InnoDB特有的日志）也叫redo日志。 *
  文件名为"ib_logfile0"和“ib_logfile1”，默认存放在表空间所在目录。
  还有一个日志文件叫undo日志，默认存储在ib_data目录下。
  6）中继日志： * 是在主从复制环境中产生的日志。 * 主要作用是为了从机可以从中继日志中获取到主机同步过来的SQL语句，然后执行到从机中。

4，数据文件
查看MySQL数据文件：SHOW VARIABLES LIKE ‘%datadir%’;
.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
.ibd和.ibdata文件：用来存储InnoDB存储引擎的表数据和索引信息
.myd文件：主要用来存储使用MyISAM存储引擎的表数据信息。
.myi文件：主要用来存储使用MyISAM存储引擎的表数据文件中任何索引的数据树。

5，MySQL索引

• 使用索引的主要目的是为了优化查询速度
• 索引是一种特殊的文件或者叫数据结构(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

6，索引的分类
索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
　　* MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换 * MEMORY/HEAP存储引擎：支持HASH和BTREE索引
索引的分类
* 单列索引：
* 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
* 唯一索引：索引列中的值必须是唯一的，但是允许为空值，
* 主键索引：是一种特殊的唯一索引，不允许有空值。

* 组合索引
	* 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
* 全文索引
	* 全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。
* 空间索引：不做介绍，一般使用不到。

7，索引的存储结构
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B Tree和B+ Tree的特点与区别

• 树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数。
• 如果是三层树结构—支撑的数据可以达到20G，如果是四层树结构—支撑的数据可以达到几十T
• B Tree和B+ Tree的最大区别在于非叶子节点是否存储数据的问题。B Tree是非叶子节点和叶子节点都会存储数据。而B+ Tree只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是由顺序的。
  非聚集索引
• 叶子节点只会存储数据行的指针，简单来说数据和索引不在一起，就是非聚集索引。
• 主键索引和辅助索引都会存储指针的值
  聚集索引（InnoDB）
• 主键索引（聚集索引）的叶子节点会存储数据行，也就是说数据和索引是在一起，这就是聚集索引。
• 辅助索引只会存储主键值
• 如果没有没有主键，则使用唯一索引建立聚集索引；如果没有唯一索引，MySQL会按照一定规则创建聚集索引。

8，使用索引的注意事项
尽量创建组合索引（组合索引其实会默认按照最左前缀原则帮我们创建多组索引）
组合索引（id,name,sex）
索引最左前缀原则
索引覆盖：要查询的列，也要使用索引覆盖住
9，MySQL性能优化之查看执行计划explain
MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.
10，优化的思路
1.首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句
2.其次使用explain命令去查看有问题的SQL的执行计划
3.最后可以使用show profile[s] 查看有问题的SQL的性能使用情况

11，什么是慢查询？
MySQL 数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL
12，慢查询语句
1)查看是否开启慢查询
show variables like ‘%slow_query%’;

查看查询时间
2)show variables like ‘long_query_time%’;

3)临时开启慢查询
Set global slow_query_log=on;
Set global long_query_time=1;
4)永久开启慢查询
在/etc/my.cnf配置文件
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
5）慢查询日志格式

格式说明：

• 第一行,SQL查询执行的时间
• 第二行,执行SQL查询的连接信息，用户和连接IP
• 第三行,记录了一些我们比较有用的信息，如下解析
  Query_time,这条SQL执行的时间,越长则越慢
  Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
  Rows_sent,查询返回的行数
  Rows_examined,查询检查的行数，越长就当然越费时间
• 第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间。
• 第五行及后面所有行（第二个# Time:之前）,执行的sql语句记录信息，因为sql可能会很长
  6）分析慢查询日志
  A:MySQL自带的mysqldumpslow
  常用参数说明：
  -s：是表示按照何种方式排序

-t：是top n的意思，即为返回前面多少条的数据
-g：后边可以写一个正则匹配模式，大小写不敏感的
示例：
得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/localhost_slow.log

B，使用mysqlsla工具
C，percona-toolkit工具

12，索引的使用
1）对于创建的多列索引，只要查询条件使用最左边的列，索引一般都会使用
2）对与like查询，查询如果是’%aaa’，不会使用索引，’aaa%’会使用到索引

13，sql查询不会使用到索引
1）如果条件中有or
2）对于多列索引，不是使用第一部分
3）Like查询%开头
4）如果列类型是字符串，一定要在条件中将数据使用引号引起来

14，回表查询
普通索引因为无法直接定位行记录，其查询过程在通常情况下是需要扫描两遍索引树的
聚集索引：叶子节点存储的是行记录
普通索引：叶子节点是，建立索引的数据和主键的对应值
查询的时候 select * from user where age=10;
由于获取的是所有的数据，那么就会先通过普通索引的B+tree查询主键的值，再去查询聚集索引或者主键索引的B+ tree

15，索引覆盖
索引覆盖是一种避免回表查询的优化策略。具体的做法就是将要查询的数据作为索引列建立普通索引（可以是单列索引，也可以一个索引语句定义所有要查询的列，即联合索引），这样的话就可以直接返回索引中的的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。
覆盖索引的定义与注意事项
如果一个索引覆盖（包含）了所有需要查询的字段的值，这个索引就是覆盖索引。因为索引中已经包含了要查询的字段的值，因此查询的时候直接返回索引中的字段值就可以了，不需要再到表中查询，避免了对主键索引的二次查询，也就提高了查询的效率。
要注意的是，不是所有类型的索引都可以成为覆盖索引的。因为覆盖索引必须要存储索引的列值，而哈希索引、空间索引和全文索引等都不存储索引列值，索引MySQL只能使用B-Tree索引做覆盖索引。
另外，当发起一个被索引覆盖的查询（索引覆盖查询）时，在explain（执行计划）的Extra列可以看到【Using Index】的信息。
覆盖索引的优点
1.索引条目通常远小于数据行的大小，因为覆盖索引只需要读取索引，极大地减少了数据的访问量。
2.索引是按照列值顺序存储的，对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO小很多。
3.一些存储引擎比如MyISAM在内存中只缓存索引，数据则依赖操作系统来缓存，因此要访问数据的话需要一次系统调用，使用覆盖索引则避免了这一点。
4.由于InnoDB的聚簇索引，覆盖索引对InnoDB引擎下的数据库表特别有用。因为InnoDB的二级索引在叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，就避免了对主键索引的二次查询。