Mysql学习笔记系列（一）

        本次mysql系列不会讲解具体的查询语句，而是放在mysql的一些性能优化和一些特性上，是学习笔记，供大家参考补充。

慢查询

        MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是记录运行10秒以上的语句。默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件和数据库表。   

定位慢查询

开源的运维工具Prometheus 、Skywalking和mysql的日志：linux下的mysql系统配置文件： /var/lib/mysql/localhost-slow.log。

导致慢查询的原因

聚合查询，多表查询，表大数据量查询，深度分页查询等。

如何分析

explain select col from table where condition

可以采用MySQL自带的分析工具 EXPLAIN 通过key和key_len检查是否命中了索引（索引本身存在是否有失效的情况）

通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描

通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

存储引擎

定义

        存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

分类

常见的有myisam，innodb，memory等

只有innodb支持事务安全，其他的都不支持。

mysql体系结构

innodb

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。

支持外键，行级锁，提高并发性能。遵行dml操作的acid原则，支持事务。

InnoDB存储引擎是mysql5.5之后是默认的引擎，它支持事务、外键、表级锁和行级锁。使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的；

MyISAM是早期的引擎，它不支持事务、只有表级锁、也没有外键，用的不多。MyISAM是非聚集索引，它也是使用B+Tree作为索引结构，但是索引和数据文件是分离的，索引保存的是数据文件的指针。

Memory主要把数据存储在内存，支持表级锁，没有外键和事务，用的也不多

索引

        索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引作用

         索引能够提高数据检索的效率，降低数据库的IO成本（不需要全表扫描） 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

B树B+树

        B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构

        B树与B+树对比: ①：磁盘读写代价B+树更低；②：查询效率B+树更加稳定；③：B+树便于扫库和区间查询。

聚簇索引和非聚簇索引

聚簇索引

将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，必须有且只有一个。

选举规则：

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

非聚簇索引（二级索引）

将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键。可以有多个。

节点上面带数据。

回表

通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表

覆盖索引

覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

如果返回的列中没有涵盖已有的索引，有可能会触发回表查询，尽量避免使用select *

mysql超大分页处理

可以使用覆盖索引解决。

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

select * from tb_sku t,     (select id from tb_sku order by id limit 10000000,10) a where t.id = a.id;

覆盖索引+子查询解决。

索引创建的原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。 10w+

2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。