1、性能监控
开启消耗：set profiling = 1；
查询消耗：show profiles；
查询细节：show profile for query {2}；
查询 CPU：show profile cpu；
查询所有：show profile all；
profile statement 会被逐渐替代掉。目前推荐的是performance schema。它以比较低的级别来监控数据库系统，统计运行过程中的资源消耗、资源等待等情况，所以会对数据库性能降低比较小。
所有schema：show databases；
所有系统表：show databasetables；
默认开启：show varibales like 'performance_schema'； 修改在配置文件中修改。
具有如下特点：
1、提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema数据库中的表使用performance_schema存储引擎。该数据库关注数据库运行过程中的性能相关数据。与information_schema不同，infor 是关注server运行过程中的元数据信息。
2、performance_schema通过监视server的事件来监控server内部运行情况，事件就是server内部活动中所做的任何事情以及对应的时间消耗。
常用sql：
1.哪类SQL执行最多 selectDIGEST_TEXT,COUNT_STAR,FIRST...
2.哪类SQL的平均响应时间最多 SELECTDIGEST_TEXT,AVG_TIMER...
3.哪类SQL排序记录数最多 selectdigest_text,sum_sort_rows...
4.哪类SQL扫描记录数最多 selectdigest_text,sum_rows...
5.哪类SQL使用临时表最多 selectdigest_text,sum_created...
显示connection 状态及数量：show processlist
2、schema与数据类型优化
1、数据类型的优化
更小的通常更好
尽量使用可以正确存储的最小数据类型，更小的数据类型意味着更少的内存消耗和缓存存储。
简单就好、
简单类型的数据操作通常需要更少的CPU周期，例如整形比字符串带价更低，因为字符串和校对规则是字符比较比整形更复杂。
尽量避免null
在数据库中null 也具有存储空间，并且带有null 的列回友更复杂的索引维护，索引比较也会复杂。
实际细则。
2、合理的使用范式和反范式
范式的目的是：减小数据的冗余性、提高效率
第一范式，每一列不可分割、第二范式：属性必须完全依赖主键、第三范式：所有的非主属性不依赖于其他的非主属性
3、主键的选择
(针对InnoDB引擎)我们实际生产环境可能会使用四类属性作为主键：
(1). 自增序列；
(2). UUID()函数生成的随机值；
(3). 用户注册的唯一性帐号名称，字符串类型，一般长度为：40个字符；
(4). 基于一套机制生成类似自增的值，比如序列生成器；
那么我们接下来，再分析下这四类属性各自作为表主键的优缺点：
(1). 自增序列：从小到大 或从大到小的顺序模式增加新值；数据类型也利于进行主键值比较；存储空间占用也相对最小，一般设置为：4个字节的INT类型或 8个字节的BIGINT类型；若是想进行数据水平拆分的话，也可以借助设置mysqld实例的2个参数：auto_increment_increment 和 auto_increment_offset；另外，唯一缺点就是自增序列是一个表级别的全局锁，在5.0系列大规模并发写的时候，因锁释放机制的问题容易出现瓶颈，但是5.1系列做了改进，基本上不存在此问题；
(2). UUID()函数：值为随机性+固定部分，其值产生是无序的，且同一台服务器上产生的值相同部分为77.8%；产生的值字符个数为36，按utf8编码计算，占用的存储空间为36个字节；对于数据水平拆分支持，无需特殊设置；
(3). 使用用户注册的帐号名称，字符串类型，其值的产生依赖用户输入，为此数据基本上为无序增加，字符串的长度也是不定的，只能通过前段技术控制最短最大长度值的限制，对水平拆分支持，无需做特殊设置；
(4). 序列生成器的架构，类似自增序列，不过需要借助额外的开发工作量，以及提供一个第三方的服务，可以规避自增序列的字增全局锁的问题，提高并发，对数据水平拆分可以更好地支持；
(5). 双主复制架构的概率性碰到的场景：主服务器的数据执行成功，而没有复制到在线备用服务器时，出问题的概率确实存在，其他类型的做法，也必须人工干涉解决，都无简单且合理的自动化办法，以上四种办法都无法规避；
通过四种属性值作为主键的优缺点分析，以及对比前面我们阐述的主键需要的优秀素质，若是不考虑水平拆分的问题，带来额外设置上的麻烦，则自增序列是最佳的主键字段选择；用户的注册帐号本身要求唯一性且非空的场景下，则可以作为主键字段的选择；若是考虑水平拆分的问题，则采用自增序列生成器的架构，非常易用和可靠的实现方式，产生的值是最佳主键字段的选择；
4、字符集的选择
对数据库来说，字符集更加重要，因为数据库存储的数据大部分都是各种文字，字符集对数据库的存储，处理性能，以及日后系统的移植，推广都会有影响。
MySQL5.6目前支持几十种字符集，包括UCS-2，UTF-16，UTF-16LE,UTF-32，UTF-8和utf8mb4等Unicode字符集。
根据应用的需求，考虑以下几方面的因素。
满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择Unicode字符集。对MySQL来说，目前就是UTF-8
如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。假如已有数据是GBK文字，如果选择GB2312-80为数据库字符集，就很有可能出现某些文字无法正确导入的问题
如果数据库只支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节长编码的中文字符集，比如GBK。因为，相对于UTF-8而言，GBK比较“小”，每个汉字只占2个字节，而UTF-8汉字编码需要3个字节，这样可以减少磁盘I/O，数据库Cache以及网络传输的时间，从而提高性能。相反，如果应用主要处理英文字符，仅有少量汉字数据，那么选择UTF-8更好，因为GBK,UCS-2，UTF-16的西文字符编码都是2个字节，会造成很多不必要的开销。
如果数据库需要做大量的字符运算，如比较，排序等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。
如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集，这样可以避免因字符集转换带来的性能开销和数据损失
5、存储引擎的选择
6、适当的数据冗余
互联网数据量很大的业务场景，往往数据库需要进行水平切分来降低单库数据量。水平切分会有一个partition key，通过partition key的查询能够直接定位到库，但是非partition key上的查询可能就需要扫描多个库了。
此时常见的架构设计方案，是使用数据冗余这种反范式设计来满足分库后不同维度的查询需求。
常见方案有：服务同步双写、服务异步双写、线下异步双写。
7、适当的拆分
当数据库比较庞大，读写操作特别是写入操作过于频繁，很难由一台服务器支撑的时候，我们就要考虑进行数据库的切分。所谓数据库的切分，就是我们按照某些特定的条件，将一台数据库上的数据分散到多台数据库服务器上。因为使用多台服务器，所以当一台服务器宕机后，整个系统只有部分数据不可用，而不是全部不可用。因此，数据库切分不仅能够用多台服务器分担数据库的负载压力，还可以提高系统的总体可用性。
1、垂直切分
垂直切分就是按照系统功能模块，将每个模块访问的数据表切分到不同的数据库中。
2、水平切分
水平切分就是对数据量超大的数据表，按照其中数据的逻辑关系，根据某个字段的某种规则，将其中的数据切分到多个数据库上。
3、整合切分结果
数据在经过垂直和水平切分被存放在不同的数据库服务器上之后，系统面临的最大问题就是如何来让这些来自不同数据库服务器上的数据得到较好的整合。解决这个问题有两种方式：
第一种：在系统的每个模块中配置管理该模块需要的一个或者几个数据库及其所在服务器的信息，数据在模块中进行整合；
第二种：通过中间代理层来统一管理所有的数据源，数据库集群对系统应用透明。
3、执行计划阅读
1、mysql整个查询的过程：
• 客户端向 MySQL 服务器发送一条查询请求
• 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
• 服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划
• MySQL 根据执行计划，调用存储引擎的 API 来执行查询
• 将结果返回给客户端，同时缓存查询结果
注意：只有在8.0之前才有查询缓存，8.0之后查询缓存被去掉了
2、如何开启执行计划
explain select 投影列 FROM 表名 WHERE 条件
3、分析示例
EXPLAIN select * from actor;
4、explain的 两个变种
explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows *filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。
EXPLAIN EXTENDED select * from actor where id=1;show WARNINGS;
explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。
5、explain中的列
id：
id 值相同时表示从上向下执行，id 值相同被视为一组，如果是子查询，id 值会递增，id 值越高，优先级越高，id为NULL最后执行。
select_type：
simple：表示查询中不包含子查询或者 union
primary：当查询中包含任何复杂的子部分，最外层的查询被标记成 primary
derived：在 from 的列表中包含的子查询被标记成 derived
subquery：在 select 或 where 列表中包含了子查询，则子查询被标记成 subquery
union：两个 select 查询时前一个标记为 PRIMARY，后一个标记为 UNION。union 出现在 from 从句子查询中，外层 select 标记为 PIRMARY，union 中第一个查询为 DERIVED，第二个子查询标记为 UNION
unionresult：从 union 表获取结果的 select 被标记成 union result。
table：
显示这一行的数据是关于哪张表的。
当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。
type：
这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为 system > const > eq_reg > ref > range > index > ALL。一般来说，得保证查询达到range级别，最好达到ref。
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
explain select min(id) from film;
system：表中只有一行数据。属于 const 的特例。如果物理表中就一行数据为 ALL
const ：查询结果最多有一个匹配行。因为只有一行，所以可以被视为常量。const 查询速度非常快，因为只读一次。一般情况下把主键或唯一索引作为唯一条件的查询都是 const
explain select * from (select * from film where id = 1) tmp;
eq_ref：查询时查询外键表全部数据。且只能查询主键列或关联列。且外键表中外键列中数据不能有重复数据，且这些数据都必须在主键表中有对应数据（主键表中数据可以有没有用到的）
explain select * from film_actor left join film on film_actor.film_id = film.id
ref：比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
range：把这个列当作条件只检索其中一个范围。常见 where 从句中出现 between、<、>、>=、in 等。主要应用在具有索引的列中
explain select * from actor where id > 1;
index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些
explain select * from film;
ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。
explain select * from actor;
possible_keys：
查询条件字段涉及到的索引，可能没有使用。
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果
key：
实际使用的索引。如果为 NULL，则没有使用索引。
如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 forceindex、ignore index。
key_len：
表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len 是根据表定义计算而得的，不是通过表内检索出的。
ref
显示索引的哪一列被使用了，如果可能的话，是一个常量 const。
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。注意这个不是结果集里的行数。
fitered
显示了通过条件过滤出的行数的百分比估计值。
Extra ： MYSQL 如何解析查询的额外信息。
1、Distinct:MySQL 发现第 1 个匹配行后,停止为当前的行组合搜索更多的行。
2、Not exists:MySQL 能够对查询进行 LEFT JOIN 优化,发现 1 个匹配 LEFT JOIN 标准的行后,不再为前面的的行组合在该表内检查更多的行。
3、range checked for each record (index map: #):MySQL 没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
4、Using filesort:MySQL 需要额外的一次传递,以找出如何按排序顺序检索行。将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
4.1. actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录
4.2. film.name建立了idx_name索引,此时查询时extra是using index
explain select * from film order by name;
5、Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。(使用覆盖索引)
覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值
explain select film_id from film_actor where film_id = 1;
6、Using temporary:为了解决查询,MySQL 需要创建一个临时表来容纳结果。
6.1. actor.name没有索引，此时创建了张临时表来distinct
explain select distinct name from actor;
6.2. film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表
explain select distinct name from film;
7、Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户，并且查询的列未被索引覆盖
explain select * from actor where name = 'a';
8、Using sort_union(…), Using union(…), Using intersect(…): 这 些 函 数 说 明 如 何 为index_merge 联接类型合并索引扫描。
9、Using index for group-by:类似于访问表的 Using index 方式,Using index for group-by 表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。
4、索引
什么是索引？
索引是帮助Mysql高效获取数据的排序的数据结构。
索引数据结构：
二叉树、红黑树、Hash表、B-Tree
如果不存在索引，当我们需要查找数据时，就需要按照顺序逐个比较，而每次读取数据都是一次磁盘的IO，这无疑将会带来极大的性能问题尤其是数据量庞大时，但是如果通过二叉树对原有数据进行存储，当查询时，我们只需要按照树的查询次数就可以获取到对应的值，从而完成搜索。
二叉树练习：https://www.cs.usfca.edu/~galles/visualization/BST.html
所有数据结构：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
二叉树在单边增长情况下和链表没有区别，二叉平衡树仍然存在这个单边增长的问题，树的高度仍然很高。
可以在节点上增加索引来降低树的高度，这便是B树结构：
B+-Tree具有如下特点：叶节点具有相同深度，叶节点的指针为空、叶节点中数据索引从左到右递增
B-Tree查找数据的顺序是，从根节点开始每次load所有元素（一页数据）然后在RAM中经过二分算法（上图的空白处对应下个元素的文件位置）进行快速比对。以此类推逐级向下。
show global status like 'innodb_page_size'；可以查询一页数据的的大小（default 16KB）。
数据库常见存储引擎：InnoDB MyISAM。存储引擎是用来修饰表的。
myisam：
数据库的数据是存储在mysql安装目录下的data目录。.frm、结构文件.MYD数据文件、.MYI index文件。
innodb：
相同的目录但是文件格式不一样：.frm、结构文件 .ibd、数据和索引都存在里面。
InnoDB索引实现（聚集）
表数据文件本身就是按照B-Tree组织的一个索引结构文件、聚集索引-叶子节点包含了完整的数据结构。
两者区别就是叶子节点存储的不一样，一个是文件的位置，一个是文件本身。
但是无论哪种存储引擎背后都是B+ Tree结构。
B+ Tree （B树的变种，增强版）：
非叶子节点不存储data，只存储索引，可以放更多的索引，树的高度大大降低。
叶子节点包含所有的索引字段。
叶子节点相邻节点具有相互指针存储相邻元素的位置（这也是它能支持范围查找的根本所在）。
为什么建议InnoDB表必须建主键，并且推荐使用整形的自增主键？
如果不建主键，聚集索引会在表中找一列不会重复的数据，如果没有找到会建造隐藏数据，来构造数据结构。自增整形数据结构，比较快，存储小（SSD还是很贵的，更小的空间成本更低）。如果不是自增元素，每次插入元素则需要从新维护索引结构。（既要分裂又要平衡树结构，极其影响写入性能）。
为什么非主键索引结构叶子节点存储的是主键值？
1. 保持一致性：当数据库表进行DML操作时，同一行记录的页地址会发生改变，因非主键索引保存的是主键的值，无需进行更改。
2. 节省存储空间：Innodb数据本身就已经汇聚到主键索引所在的B+树上了， 如果普通索引还继续再保存一份数据，就会导致有多少索引就要存多少份数据。
Hash 索引：
对索引的key进行一次hash计算就可以定位出数据储存的位置、很多时候hash索引要比B+树更高效、仅能满足=，in、不支持范围查询。存在hash 冲突问题。
索引最左前缀原则：
联合主键结构：
从左到右按照联合主键的字段进行排序。
select * from student where name = 'zhangsan' and age=30; 命中索引。
select * from student where age=30 and position = 'dev'；不会命中索引。
如果不从第一个元素开始，索引结构是不具备有序这一特点的。
mysql explain 最佳实践：
MVCC（可重复读如何实现？）
在read commited、repeated committed这两种事务隔离等级下执行普通的select操作时访问记录的版本链过程，使得不同事务的读写 、写渎操作并发执行。
Buffer Pool（load 的数据存放的位置）：
5、优化案例：
1、单表联合索引失效
我们预先在table test 上的column1、column2、column3建立联合索引(idx_column1_column1_column3)。然后执行如下query：
select id,author_id from test where column1 =1 and column2>2 order by column3;
select id,author_id from test where column1 =1 and column2=2 order by column3;
结果是第二个命中索引但是第一个不会。这是因为按照B+Tree索引的工作原理，先按照c1排序，如果遇到相同的c1再按照c2排序，如果相同则继续，当中间字段是条件范围时，对于第三个列无法按照索引结构扫描从而只能全表扫描。
解决方案，移除需要排序的列在联合索引中建立新的索引（idx_column1_column3）。
1、根据sql实际解析的顺序，调整联合索引的顺序。
2、将查询列放置于联合索引可以提升速度，因为避免的去数据库。
3、清除废弃索引，方式干扰。
4、最左前缀原则
5、将含有in 的范围查询放置于条件最后，避免in提前失效
2、双表索引失效
假设存在 table test1、test2，使用字段c1关联。
select * from test1 join test2 on test1.c1 = test2.c1;
在左表和右表建立index后结果不同，这是由左连接特性决定的，left join 条件用于确定如何从右表搜索行，左边一定会有。所以右边是关键点，一定要有索引，这就是小表驱动大表。
最佳实践：
1、全值匹配最合适
2、最佳左前缀原则、存储引擎不能使用索引中范围条件右边的列
3、不在索引上做什么操作，否则会导致全表扫描，函数或者计算
5、尽量使用覆盖索引，只访问索引的查询，减少select *
6、mysql 在使用!=的时候无法使用索引，is null is not null
7、like百分号 加右边，mysql会引起全表扫描
8、使用 or引起索引失效
