mysql日志、索引、事务、内外连接查询、连接池

由于现在我手上的需求并发量和数据量都不算很大--所以很少遇到相关优化问题，但是整理一下还是好的;

mysql查询日志

1. 开启慢查询日志：

先查看一下当前是否开启:show variables like "%query%"

开启:windows环境下可以修改my.ini配置

在mysqld下面加上

log_bin=mysql-bin 

slow_query_log=on 
slow_query_log_file=mysql-slow 
long_query_time=2

    重启mysql服务

2.查询慢查询日志

cat mysql-slow

3.慢查询日志文件

slow_query_log_file  慢查询日志存放地点

4.索引相关

set global log_queries_not_using_indexes = on;

set global long_query_time = 1;    【设置查询超过一秒的没有使用索引的将会被记录到慢查询日志中】

参考:

MySQL 之 slow log

Mysql 启动慢查询日志 （不用重启）

开启MySQL慢查询 查找影响效率的SQL语句

MySQL 慢查询日志(Slow Query Log)

mysql查看历史执行的sql

MySQL开启日志记录查询/执行过的SQL语句

mysql explain

EXPLAIN列的解释：

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

extra列返回的描述的意义

Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

system 表只有一行：system表。这是const连接类型的特殊情况

const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

MySql 中的 explain 语法可以帮助我们改写查询,优化表的结构和索引的设置,从而最大地提高查询效率。当然,在大规模数据量时,索引的建立和维护的代价也是很高的,往往需要较长的时间和较大的空间,如果在不同的列组合上建立索引,空间的开销会更大。因此索引最好设置在需要经常查询的字段中 

参考:

MySQL中EXPLAIN解释命令 查看索引是否生效

mysql索引

建立索引的原则

我们回头来看一开始提到的慢查询，当我们了解完索引原理之后，对慢查询的优化应该有一些想法，这里我们先总结一下建立索引的一些原则：

1，最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配，比如： a = 1 AND b = 2 AND c > 3 AND d = 4，如果建立 （a,b,c,d）顺序的索引，d是用不到索引的，如果建立（a,b,d,c）的索引，则都可以用到，a,b,d的顺序可以任意调整。

2，等于（=）和in 可以乱序。比如，a = 1 AND b = 2 AND c = 3 建立（a,b,c）索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的模式。

3，尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度是0。可能有人会问，这个比率有什么经验么？使用场景不同，这个值也很难确定，一般需要JOIN的字段我们要求在0.1以上，即平均1条扫描10条记录。

4，索引列不能参与计算，尽量保持列“干净”。比如，FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time = UNIX_TIMESTAMP('2016-06-06')。

5，尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。

6，单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引。

慢查询的优化步骤

1，查看运行效果，是否真的很慢，主要设置SQL_NO_CACHE。

2，WHERE条件单表查询，锁定最小返回记录表。这句话的意思是，把查询语句的WHERE都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

3，EXPLAIN查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

4，ORDER BY LIMIT 形式的SQL语句，让排序的表优先查

5，多去了解业务的使用场景

6，加索引时，要参照建立索引的几大原则

7，观察结果，不符合预期，则重新从1开始分析。

索引的优化方法

1，何时使用聚簇索引或非聚簇索引：

2，索引不会包含有NULL值的列：只要列中包含有NULL值，都将不会被包含在索引中，组合索引中只要有一列有NULL值，那么这一列对于此条组合索引就是无效的。所以我们在数据库设计时，不要让索引字段的默认值为NULL。

3，使用短索引：假设，如果有一个数据类型为CHAR(255)的列，在前10个或20个字符内，绝大部分数据的值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省I/O操作。

4，索引列排序：MySQL查询只使用一个索引，因此如果WHERE子句中已经使用了索引的话，那么ORDER BY中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下，不要使用排序操作；尽量不要包含多个列的排序，如果需要，最好给这些列也创建组合索引。

5，LIKE语句操作：一般情况下，不建议使用LIKE操作；如果非使用不可，如何使用也是一个研究的课题。LIKE "%aaaaa%"不会使用索引，但是LIKE "aaa%"却可以使用索引。

6，不要在索引列上进行运算：在建立索引的原则中，提到了索引列不能进行运算，这里就不再赘述了。

参考:

MySQL学习之——索引(普通索引、唯一索引、全文索引、索引匹配原则、索引命中等)

MySQL 索引

B树，B+树，红黑树 数据库常见面试题

浅谈AVL树,红黑树,B树,B+树原理及应用

Mysql索引为啥要用B+树？

MySQL B+树索引和Hash索引的区别

MySQL Hash索引实际使用场景？

没有索引时mysql是如何查询到数据的

mysql事务

概述：

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等（我感觉这个场景不太可能可能，删除是不能删除的），这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

• 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

使用：

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交

SET AUTOCOMMIT=1 开启自动提交

3、使用 SAVEPOINT

SAVEPOINT savepoint_name;    // 声明一个 savepoint

ROLLBACK TO savepoint_name;  // 回滚到savepoint

删除 SAVEPOINT

保留点再事务处理完成（执行一条 ROLLBACK 或 COMMIT）后自动释放。

MySQL5 以来，可以用:

RELEASE SAVEPOINT savepoint_name;  // 删除指定保留点

参考：

MySQL 事务

深入学习MySQL事务：ACID特性的实现原理

『MySQL』深入理解事务的来龙去脉

对mysql事务提交、回滚的错误理解

mysql锁

参考：

mysql并发控制——共享锁、排他锁

MySQL/InnoDB中，乐观锁、悲观锁、共享锁、排它锁、行锁、表锁、死锁概念的理解

全面了解mysql锁机制（InnoDB）与问题排查

MySQL中的锁（表锁、行锁）

MySQL优化系列（八）--锁机制超详细解析（锁分类、事务并发、引擎并发控制）

mysql内外连接

参考:

MySQL中的各种join

Mysql Join语法解析与性能分析

MySQL 连接的使用

mysql连接池

参考:

Mybatis框架的连接池配置

Mybatis深入之数据库连接池原理

hMybatis数据库连接池解析

Mybatis原理——数据源和连接池

《深入理解mybatis原理》 Mybatis数据源与连接池

druid wiki

Druid SqlParser理解及使用入门

Druid数据库连接池及内置监控的配置和使用

spring boot 整合 druid + Mysql + Mybatis