目录
mysql简介
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。
mysql与redis的区别
1 mysql和redis的数据库类型
mysql是关系型数据库，主要用于存放持久化数据，将数据存储在硬盘中，读取速度较慢。
mysql支持sql查询，可以实现一些关联的查询以及统计；
redis是NOSQL，即非关系型数据库，也是内存数据库，即将数据存储在内存中，内存的读取速度快，能够大大的提高运行效率，但是保存时间有限。
2 mysql和redis的作用
mysql用于持久化的存储数据到硬盘，功能强大，速度较慢，基于磁盘，读写速度没有Redis快，但是不受空间容量限制，性价比高
redis用于存储使用较为频繁的数据到缓存中，读取速度快，基于内存，读写速度快，也可做持久化，但是内存空间有限，当数据量超过内存空间时，需扩充内存，但内存价格贵
3 mysql和redis的需求
mysql和redis因为需求的不同，一般都是配合使用。
需要高性能的地方使用Redis，不需要高性能的地方使用MySQL。存储数据在MySQL和Redis之间做同步。
4 redis和mysql选型
redis适合放一些频繁使用，比较热的数据，因为是放在内存中，读写速度都非常快，一般会应用在下面一些场景
排行榜、计数器、消息队列推送、好友关注、粉丝
首先要知道mysql存储在磁盘里，redis存储在内存里，redis既可以用来做持久存储，也可以做缓存，而目前大多数公司的存储都是mysql + redis，mysql作为主存储，redis作为辅助存储被用作缓存，加快访问读取的速度，提高性能
那么为什么不直接全部用redis存储呢？
因为redis存储在内存中，如果存储在内存中，存储容量肯定要比磁盘少很多，那么要存储大量数据，只能花更多的钱去购买内存，造成在一些不需要高性能的地方是相对比较浪费的，所以目前基本都是mysql(主) + redis(辅)，在需要性能的地方使用redis，在不需要高性能的地方使用mysql，好钢用在刀刃上
MySQL逻辑架构
如果能在头脑中构建出一幅MySQL各组件之间如何协同工作的架构图，就会有助于深 人理解MySQL服务器。图展示了MySQL的逻辑架构图。
最上层的服务井不是MySQL所独有的，大多数基于网络的客户端／服务器的工具或者 服务都有类似的架构。比如连接处理、授权认证、安全等等。
第二层架构是MySQL比较有意思的部分。大多数MySQL的核心服务功能都在这一层， 包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加 密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。和GNU/Linux下 的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过API与存储引擎 进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程 透明。存储引擎API包含儿十个底层函数，用于执行诸如“开始一个事务”或者”根据 主键提取一行记录”等操作。但存储引擎不会去解析SQL, 不同存储引擎之间也不会 相互通信，而只是简单地响应上层服务器的请求。
连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的 线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。
当客户端（应用）连接到MySQL服务器时，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。如果使用了安全套接字(SSL)的方式连接，还可以使用证书认证。一且客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限（例如，是否允许客户端对world数据库的Country表执行SELECT语句）。
优化与执行
MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重 写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示 (hint)优化器，影响它的决策过程。也可以请求优化器解释(explain)优化过程的各个 因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户 重构查询和schema、修改相关配置，使应用尽可能高效运行。
优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化 器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。例 如，某些存储引擎的某种索引，可能对一些特定的查询有优化。
对于SELECT语句，在解析查询之前，服务器会先检查查询缓存(Query Cache) , 如果能 够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而 是直接返回查询缓存中的结果集。
查询缓存
QueryCache介绍
MySQL查询缓（QC：QueryCache）在MySQL 4.0.1中引入，查询缓存存储SELECT语句的文本以及发送给客户机的结果集，如果再次执行相同的SQL，Server端将从查询缓存中检索结果返回给客户端，而不是再次解析执行SQL，查询缓存在session之间共享，因此，一个客户端生成的缓存结果集，可以响应另一个客户端执行同样的SQL。
可以通过 sql_cache 和 sql_no_cache 来控制某个查询语句是否需要缓存：
select sql_no_cache count(*) from usr;
如何判断SQL是否共享？
通过SQL文本是否完全一致来判断，包括大小写，空格等所有字符完全一模一样才可以共享，共享好处是可以避免硬解析，直接从QC获取结果返回给客户端，下面的两个SQL是不共享滴，因为一个是from，另一个是From。
QueryCache配置
mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF || Variable_name | Description |
|---|---|
| have_query_cache | 查询缓存是否可用，YES-可用；NO-不可用，如果用标准二进制MySQL，值总是YES。 |
| query_cache_limit | 控制单个查询结果集的最大尺寸，默认是1MB。 |
| query_cache_min_res_unit | 查询缓存分片数据块的大小，默认是4KB，可以满足大部分业务场景。 |
| query_cache_size | 查询缓存大小，单位Bytes，设置为0是禁用QueryCache，注意：不要将缓存的大小设置得太大，由于在更新过程中需要线程锁定QueryCache，因此对于非常大的缓存，您可能会看到锁争用问题。 |
| query_cache_type | 当query_cache_size>0；该变量影响qc如何工作，有三个取值0，1，2 0：禁止缓存或检索缓存结果；1：启用缓存，SELECT SQL_NO_CACHE的语句除外；2：只缓存以SELECT SQL_CACHE开头的语句。 |
query_cache_min_res_unit说明
默认大小是4KB，如果有很多查询结果很小，那么默认数据块大小可能会导致内存碎片，由于内存不足，碎片可能会强制查询缓存从缓存中删除查询。
在这种情况下，可以减小query_cache_min_res_unit的值，由于修剪而删除的空闲块和查询的数量由Qcache_free_blocks和Qcache_lowmem_prunes状态变量的值给出，如果大量的查询有较大的结果集，可以增大该参数的值来提高性能。
通常开启QueryCache方式
# 修改MySQL配置文件/etc/my.cnf，添加如下配置，重启MySQL server即可。
[mysqld]
query_cache_size = 32M
query_cache_type = 1
QueryCache使用
先搞点测试数据，分别对禁用和开启QueryCache下的场景进行测试。
--创建一个用户表users，并且插入100w数据。
CREATE TABLE `users` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
`age` tinyint NOT NULL DEFAULT '0' COMMENT 'age',
`gender` char(1) NOT NULL DEFAULT 'M' COMMENT '性别',
`phone` varchar(16) NOT NULL DEFAULT '' COMMENT '手机号',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
select count(*) from users;
+----------+
| count(*) |
+----------+
| 1000000 |禁用queryCache场景
在不使用QueryCache的时候，每次执行相同的查询语句，都要发生一次硬解析，消耗大量的资源。
#禁用QueryCache的配置
query_cache_size = 0
query_cache_type = 0
重复执行下面查询，观察执行时间。
--第一次执行查询语句
mysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| id | name | age | gender | phone | create_time | update_time |
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 rows in set (0.89 sec)
--第二次执行同样的查询语句
mysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| id | name | age | gender | phone | create_time | update_time |
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 rows in set (0.90 sec)
-- profile跟踪情况
mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| preparing | 0.000022 | 0.000017 | 0.000004 | 0 | 0 |
| Sorting result | 0.000014 | 0.000009 | 0.000005 | 0 | 0 |
| executing | 0.000011 | 0.000007 | 0.000004 | 0 | 0 |
| Sending data | 0.000021 | 0.000016 | 0.000004 | 0 | 0 |
| Creating sort index | 0.906290 | 0.826584 | 0.000000 | 0 | 可以看到，多次执行同样的SQL查询语句，执行时间都是0.89s左右，几乎没有差别，同时时间主要消耗在Creating sort index阶段。
开启queryCache场景
开启查询缓存时，查询语句第一次被执行时会将SQL文本及查询结果缓存在QC中，下一次执行同样的SQL执行从QC中获取数据返回给客户端即可。
#禁用QueryCache的配置
query_cache_size = 32M
query_cache_type = 1
--第一次执行查询语句
mysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| id | name | age | gender | phone | create_time | update_time |
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 rows in set (0.89 sec)
--第二次执行查询语句
mysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| id | name | age | gender | phone | create_time | update_time |
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 rows in set (0.00 sec)
-- profile跟踪数据
mysql> show profile cpu,block io for query 3;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| Waiting for query cache lock | 0.000016 | 0.000015 | 0.000001 | 0 | 0 |
| checking query cache for query | 0.000007 | 0.000007 | 0.000000 | 0 | 0 |
| checking privileges on cached | 0.000004 | 0.000003 | 0.000000 | 0 | 0 |
| checking permissions | 0.000034 | 0.000033 | 0.000001 | 0 | 0 |
| sending cached result to clien | 0.000018 | 0.000017 | 0.000001 | 0 | 0 |可以看到，第一次执行QueryCache里没有缓存SQL文本及数据，执行时间0.89s，由于开启了QC，SQL文本及执行结果被缓存在QC中，第二次执行执行同样的SQL查询语句，直接命中QC且返回数据，不需要发生硬解析，所以执行时间降低为0s，从profile里看到sending cached result to client直接发送QC中的数据返回给客户端。
查询缓存命中率
查询缓存相关的status变量
mysql>SHOW GLOBAL STATUS LIKE 'QCache\_%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 | --查询缓存中可用内存块的数目。
| Qcache_free_memory | 33268592 | --查询缓存的可用内存量。
| Qcache_hits | 121 | --从QC中获取结果集的次数。
| Qcache_inserts | 91 | --将查询结果集添加到QC的次数，意味着查询已经不在QC中。
| Qcache_lowmem_prunes | 0 | --由于内存不足而从查询缓存中删除的查询数。
| Qcache_not_cached | 0 | --未缓存的查询数目。
| Qcache_queries_in_cache | 106 | --在查询缓存中注册的查询数。
| Qcache_total_blocks | 256 | --查询缓存中的块总数。查询缓存命中率及平均大小
Qcache_hits
Query cache hit rate = ------------------------------------------------ x 100%
Qcache_hits + Qcache_inserts + Qcache_not_cached
query_cache_size = Qcache_free_memory
Query Cache Avg Query Size = ---------------------------------------
Qcache_queries_in_cache更新操作对QC影响
举个例子，支付系统的里转账逻辑，先要锁定账户再修改余额，主要步骤如下：
| Query_ID | Query | Description |
|---|---|---|
| 1 | reset query cache | 清空查询缓存。 |
| 2 | select balance from account where id = 121 | 第一次执行，未命中QC，添加到QC。 |
| 3 | select balance from account where id = 121 | 命中QC，直接返回结果。 |
| 4 | update account set balance = balance - 1000 where id = 121 | 更新，锁定query cche进行更新，缓存数据失效。 |
| 5 | select balance from account where id = 121 | 缓存已失效，未命中，添加到QC。 |
| 6 | select balance from account where id = 121 | 命中QC，直接返回结果。 |
对于这种情况来说，QC是不太适合的，因为第一次执行查询SQL未命中，返回结果给客户端，添加SQL文本及结果集到QC之后，下一次执行同样的SQL直接从QC返回结果，不需要硬解析操作，但是每次Update都是先更新数据，然后锁定QC然后更新缓存结果，会导致之前的缓存结果失效，再次执行相的查询SQL还是未命中，有得重新添加到QC，这样频繁的锁定QC->检查QC->添加QC->更新QC非常消耗资源，降低数据库的并发处理能力。
为何放弃QueryCache
一般业务场景
从业务系统的操作类型，可以分为OLTP（OnLine Transaction Processing 联机事务处理系统）和OLAP（OnLine Analysis Processing联机分析处理系统），对于政企业务，也可以分为BOSS（Business Operation Support System-业务操作支撑系统，简称业支）和BASS（Business Analysis Support System-业务分析支撑系统，简称经分），来总结下这两类系统的特点。
适合QueryCache的场景
首先，查询缓存QC的大小只有几MB，不适合将缓存设置得太大，由于在更新过程中需要线程锁定QueryCache，因此对于非常大的缓存，可能会看到锁争用问题。那么，哪些情况有助于从查询缓存中获益呢？以下是理想条件：
相同的查询是由相同或多个客户机重复发出的。
被访问的底层数据本质上是静态或半静态的。
查询有可能是资源密集型和/或构建简短但计算复杂的结果集，同时结果集比较小。
并发性和查询QPS都不高。
这4种情况只是理想情况下，实际的业务系统都是有CRUD操作的，数据更新比较频繁，查询接口的QPS比较高，所以能满足上面的理想情况下的业务场景实在很少，我能想到就是配置表，数据字典表这些基本都是静态或半静态的，可以时通过QC来提高查询效率。
不适合QueryCache的场景
如果表数据变化很快，则查询缓存将失效，并且由于不断从缓存中删除查询，从而使服务器负载升高，处理速度变得更慢，如果数据每隔几秒钟更新一次或更加频繁，则查询缓存不太可能合适。
同时，查询缓存使用单个互斥体来控制对缓存的访问，实际上是给服务器SQL处理引擎强加了一个单线程网关，在查询QPS比较高的情况下，可能成为一个性能瓶颈，会严重降低查询的处理速度。因此，MySQL 5.6中默认禁用了查询缓存。
删除QueryCache
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. Deprecation includes query_cache_type，可以看到从MySQL 5.6的默认禁用，5.7的废弃以及8.0的彻底删除，Oracle也是综合了各方面考虑做出了这样的选择。
上面聊了下适合和不适合的QueryCache的业务场景，发现这个特性对业务场景要求过于苛刻，与实际业务很难吻合，而且开启之后，对数据库并发度和处理能力都会降低很多，下面总结下为何MySQL从Disabled->Deprecated->Removed QueryCache的主要原因。
同时查询缓存碎片化还会导致服务器的负载升高，影响数据库的稳定性，在Oracle官方搜索QueryCache可以发现，有很多Bug存在，这也就决定了MySQL 8.0直接果断的Remove了该特性。
字符集(Character set)
是多个字符(英文字符，汉字字符，或者其他国家语言字符)的集合，字符集种类较多，每个字符集包含的字符个数不同。
特点：
字符编码方式是用一个或多个字节表示字符集中的一个字符
每种字符集都有自己特有的编码方式，因此同一个字符，在不同字符集的编码方式下，会产生不同的二进制
常见字符集：
ASCII字符集：基于罗马字母表的一套字符集，它采用1个字节的低7位表示字符，高位始终为0。
LATIN1字符集：相对于ASCII字符集做了扩展，仍然使用一个字节表示字符，但启用了高位，扩展了字符集的表示范围。
GBK字符集：支持中文，字符有一字节编码和两字节编码方式。
UTF8字符集：Unicode字符集的一种，是计算机科学领域里的一项业界标准，支持了所有国家的文字字符，utf8采用1-4个字节表示字符。
MySQL的字符集
只要涉及到文字的地方，就会存在字符集和编码方式。MySQL系统变量值：
数据库服务端的字符集具体要看存储什么字符
以上这些参数如何起作用：
1.库、表、列字符集的由来
建库时，若未明确指定字符集，则采用character_set_server指定的字符集。
建表时，若未明确指定字符集，则采用当前库所采用的字符集。
新增时，修改表字段时，若未明确指定字符集，则采用当前表所采用的字符集。
2.更新、查询涉及到得字符集变量
更新流程字符集转换过程：character_set_client-->character_set_connection-->表字符集。
查询流程字符集转换过程：表字符集-->character_set_result
3.character_set_database
当前默认数据库的字符集，比如执行use xxx后，当前数据库变为xxx，若xxx的字符集为utf8，那么此变量值就变为utf8(供系统设置，无需人工设置)。
MySQL客户端与字符集
1.对于输入来说：
客户端使用的字符集必须通过character_set_client、character_set_connection体现出来：
在客户端对数据进行编码（Linux：utf8、windows：gbk）
MySQL接到SQL语句后(比如insert)，发现有字符，询问客户端通过什么方式对字符编码：客户端通过character_set_client参数告知MySQL客户端的编码方式(所以此参数需要正确反映客户端对应的编码)
当MySQL发现客户端的client所传输的字符集与自己的connection不一样时，会将client的字符集转换为connection的字符集
MySQL将转换后的编码存储到MySQL表的列上，在存储的时候再判断编码是否与内部存储字符集（按照优先级判断字符集类型）上的编码一致，如果不一致需要再次转换
2.对于查询来说：
客户端使用的字符集必须通过character_set_results来体现，服务器询问客户端字符集，通过character_set_results将结果转换为与客户端相同的字符集传递给客户端。(character_set_results默认等于character_set_client)
MySQL字符编码转换原理
问：若character_set_client为UTF8，而character_set_database为GBK，则会出现需要进行编码转换的情况，字符集转换的原理是什么？
答：假设gbk字符集的字符串“你好”，需要转为utf8字符集存储，实际就是对于“你好”字符串中的每个汉字去utf8编码表里面查询对应的二进制，然后存储。
图解字符集转换过程：
MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection;
进行内部操作前将请求数据从character_set_connection转换为内部操作字符集
确定步骤：
使用每个数据字段的CHARACTER SET设定值；
若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值；
若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；
若上述值不存在，则使用character_set_server设定值；
将操作结果从内部操作字符集转换为character_set_results。
字符集常见处理操作
1.查看字符集编码设置
mysql> show variables like '%character%';
2.设置字符集编码
mysql> set names 'utf8';
相当于同时：
set character_set_client = utf8;
set character_set_results = utf8;
set character_set_connection = utf8;
3.修改数据库字符集
mysql> alter database database_name character set xxx;
只修改库的字符集，影响后续创建的表的默认定义；对于已创建的表的字符集不受影响。（一般在数据库实现字符集即可，表和列都默认采用数据库的字符集）
4.修改表的字符集
mysql> alter table table_name character set xxx；
只修改表的字符集，影响后续该表新增列的默认定义，已有列的字符集不受影响。
mysql> alter table table_name convert to character set xxx;
同时修改表字符集和已有列字符集，并将已有数据进行字符集编码转换。
5.修改列字符集
格式：
ALTER TABLE table_name MODIFY
column_name {CHAR | VARCHAR | TEXT} (column_length)
[CHARACTER SET charset_name]
[COLLATE collation_name]
mysql> alter table table_name modify col_name varchar(col_length) character set xxx;
字符集的正确实践
MySQL软件工具本身是没有字符集的，主要是因为工具所在的OS的字符集（Windows：gbk、Linux：utf8），所以字符集的正确实践非常重要：
1.对于insert来说，character_set_client、character_set_connection相同，而且正确反映客户端使用的字符集
2.对于select来说，character_set_results正确反映客户端字符集
3.数据库字符集取决于我们要存储的字符类型
4.字符集转 换最多发生一次，这就要求character_set_client、character_set_connection相同
5.所有的字符集转换都发生在数据库端
综述：
1、建立数据库的时候注意字符集（gbk、utf8）；
2、连接数据库以后，无论是执行dml还是select，只要涉及到varchar、char列，就需要设置正确的字符集参数。
校对规则collation校对
字符集是一套符号和对应的编号
查看数据库支持的所有字符集(charset)：
mysql> show character set;
校对规则(collation)：
是在字符集内用于字符比较和排序的一套规则，比如有的规则区分大小写，有的则无视。
mysql> create table t1(id int,name varchar(20)); #t1建表没有指定校对规则
mysql> show collation; #查看数据库支持的所有校对规则
mysql> show variables like 'collation_%'; #查看当前字符集和校对规则设置
校对规则特征：
两个不同的字符集不能有相同的校对规则；
每个字符集有一个默认校对规则；
存在校对规则命名约定：以其相关的字符集名开始，中间包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束。
注意：
系统使用utf8字符集，若使用utf8_bin校对规则执行SQL查询时区分大小写，使用utf8_general_ci不区分大小写(默认的utf8字符集对应的校对规则是utf8_general_ci)。
示例：
mysql> create table t2(id int,name varchar(20)) character set=gbk collate=gbk_bin; #t2建表指定校对规则（区分大小写）
池化思想与数据库连接池
池化思想
池化设计应该不是一个新名词。我们常见的如java线程池、jdbc连接池、redis连接池等就是这类设计的代表实现。这种设计会初始预设资源,解决的问题就是抵消每次获取资源的消耗,如创建线程的开销,获取远程连接的开销等。就好比你去食堂打饭,打饭的大妈会先把饭盛好几份放那里,你来了就直接拿着饭盒加菜即可,不用再临时又盛饭又打菜,效率就高了。除了初始化资源,池化设计还包括如下这些特征:池子的初始值、池子的活跃值、池子的最大值等,这些特征可以直接映射到java线程池和数据库连接池的成员属性中。
数据库连接池
数据库连接本质就是一个socket的连接。数据库服务端还要维护一些缓存和用户权限信息之类的所以占用了一些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存,以便将来需要对数据库的请求时可以重用这些连接。
为每个用户打开和维护数据库连接,尤其是对动态数据库驱动的网站应用程序的请求,既昂贵又浪费资源。在连接池中,创建连接后,将其放置在池中,并再次使用它,因此不必建立新的连接。如果使用了所有连接,则会建立一个新连接并将其添加到池中。连接池还减少了用户必须等待建立与数据库的连接的时间。
数据库连接池应该设置多大
为什么nginx只用4个线程发挥出的性能就大大超越了100个进程的Apache HTTPD？回想一下计算机科学的基础知识，答案其实是很明显的。
即使是单核CPU的计算机也能“同时”运行数百个线程。但我们都[应该]知道这只不过是操作系统用时间分片玩的一个小把戏。一颗CPU核心同一时刻只能执行一个线程，然后操作系统切换上下文，核心开始执行另一个线程的代码，以此类推。给定一颗CPU核心，其顺序执行A和B永远比通过时间分片“同时”执行A和B要快，这是一条计算机科学的基本法则。一旦线程的数量超过了CPU核心的数量，再增加线程数系统就只会更慢，而不是更快。
有限的资源
上面的说法只能说是接近真理，但还并没有这么简单，有一些其他的因素需要加入。当我们寻找数据库的性能瓶颈时，总是可以将其归为三类：CPU、磁盘、网络。把内存加进来也没有错，但比起磁盘和网络，内存的带宽要高出好几个数量级，所以就先不加了。
如果我们无视磁盘和网络，那么结论就非常简单。在一个8核的服务器上，设定连接/线程数为8能够提供最优的性能，再增加连接数就会因上下文切换的损耗导致性能下降。数据库通常把数据存储在磁盘上，磁盘又通常是由一些旋转着的金属碟片和一个装在步进马达上的读写头组成的。
读/写头同一时刻只能出现在一个地方，然后它必须“寻址”到另外一个位置来执行另一次读写操作。所以就有了寻址的耗时，此外还有旋回耗时，读写头需要等待碟片上的目标数据“旋转到位”才能进行操作。使用缓存当然是能够提升性能的，但上述原理仍然成立。
在这一时间段（即"I/O等待"）内，线程是在“阻塞”着等待磁盘，此时操作系统可以将那个空闲的CPU核心用于服务其他线程。所以，由于线程总是在I/O上阻塞，我们可以让线程/连接数比CPU核心多一些，这样能够在同样的时间内完成更多的工作。
那么应该多多少呢？这要取决于磁盘。较新型的SSD不需要寻址，也没有旋转的碟片。可别想当然地认为“SSD速度更快，所以我们应该增加线程数”，恰恰相反，无需寻址和没有旋回耗时意味着更少的阻塞，所以更少的线程[更接近于CPU核心数]会发挥出更高的性能。只有当阻塞创造了更多的执行机会时，更多的线程数才能发挥出更好的性能。
网络和磁盘类似。通过以太网接口读写数据时也会形成阻塞，10G带宽会比1G带宽的阻塞少一些，1G带宽又会比100M带宽的阻塞少一些。不过网络通常是放在第三位考虑的，有些人会在性能计算中忽略它们。
计算公式
下面的公式是由PostgreSQL提供的，不过我们认为可以广泛地应用于大多数数据库产品。你应该模拟预期的访问量，并从这一公式开始测试你的应用，寻找最合适的连接数值。
连接数 = ((核心数 * 2) + 有效磁盘数)
核心数不应包含超线程(hyper thread)，即使打开了hyperthreading也是。如果活跃数据全部被缓存了，那么有效磁盘数是0，随着缓存命中率的下降，有效磁盘数逐渐趋近于实际的磁盘数。这一公式作用于SSD时的效果如何尚未有分析。
按这个公式，你的4核i7数据库服务器的连接池大小应该为((4 * 2) + 1) = 9。取个整就算是是10吧。是不是觉得太小了？跑个性能测试试一下，我们保证它能轻松搞定3000用户以6000TPS的速率并发执行简单查询的场景。如果连接池大小超过10，你会看到响应时长开始增加，TPS开始下降。扩展：用了这么久的数据库连接池，你知道原理吗？
这一公式其实不仅适用于数据库连接池的计算，大部分涉及计算和I/O的程序，线程数的设置都可以参考这一公式。我之前在对一个使用Netty编写的消息收发服务进行压力测试时，最终测出的最佳线程数就刚好是CPU核心数的一倍。
公理：你需要一个小连接池，和一个充满了等待连接的线程的队列
如果你有10000个并发用户，设置一个10000的连接池基本等于失了智。1000仍然很恐怖。即是100也太多了。你需要一个10来个连接的小连接池，然后让剩下的业务线程都在队列里等待。连接池中的连接数量应该等于你的数据库能够有效同时进行的查询任务数（通常不会高于2*CPU核心数）。
我们经常见到一些小规模的web应用，应付着大约十来个的并发用户，却使用着一个100连接数的连接池。这会对你的数据库造成极其不必要的负担。
请注意：
连接池的大小最终与系统特性相关。
比如一个混合了长事务和短事务的系统，通常是任何连接池都难以进行调优的。最好的办法是创建两个连接池，一个服务于长事务，一个服务于短事务。
再例如一个系统执行一个任务队列，只允许一定数量的任务同时执行，此时并发任务数应该去适应连接池连接数，而不是反过来。
mysql性能
性能指标概念
1 QPS
QPS Queries Per Second 是每秒查询率 ,是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准, 即每秒的响应请求数，也即是最大吞吐能力。
2 TPS
TPS Transactions Per Second也就是事务数/秒。一个事务是指一个客户机向服务器发送请求然后服务器做出反应的过程。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务个数，
3 QPS和TPS区别
Tps即每秒处理事务数，包括了
用户请求服务器
服务器自己的内部处理
服务器返回给用户
这三个过程，每秒能够完成N个这三个过程，Tps也就是N；
Qps基本类似于Tps，但是不同的是，对于一个页面的一次访问，形成一个Tps；但一次页面请求，可能产生多次对服务器的请求，服务器对这些请求，就可计入“Qps”之中。
例如：访问一个页面会请求服务器3次，一次放，产生一个“T”，产生3个“Q”例如：一个大胃王一秒能吃10个包子，一个女孩子0.1秒能吃1个包子，那么他们是不是一样的呢？答案是否定的，因为这个女孩子不可能在一秒钟吃下10个包子，她可能要吃很久。这个时候这个大胃王就相当于TPS，而这个女孩子则是QPS。虽然很相似，但其实是不同的。
4 并发数
并发数（并发度）：指系统同时能处理的请求数量，同样反应了系统的负载能力。这个数值可以分析机器1s内的访问日志数量来得到
5 吐吞量
吞吐量是指系统在单位时间内处理请求的数量，TPS、QPS都是吞吐量的常用量化指标。
系统吞吐量要素
一个系统的吞吐量（承压能力）与request（请求）对cpu的消耗，外部接口，IO等等紧密关联。
单个request 对cpu消耗越高，外部系统接口，IO影响速度越慢，系统吞吐能力越低，反之越高。
重要参数
QPS(TPS),并发数，响应时间
QPS(TPS)：每秒钟request/事务 数量
并发数：系统同时处理的request/事务数
响应时间：一般取平均响应时间
关系
QPS(TPS)=并发数/平均响应时间
一个系统吞吐量通常有QPS(TPS),并发数两个因素决定，每套系统这个两个值都有一个相对极限值，在应用场景访问压力下，只要某一项达到系统最高值，系统吞吐量就上不去了，如果压力继续增大，系统的吞吐量反而会下降，原因是系统超负荷工作，上下文切换，内存等等其他消耗导致系统性能下降。
6 PV
PV（Page View）：页面访问量，即页面浏览量或点击量，用户每次刷新即被计算一次。可以统计服务一天的访问日志得到。
7 UV
UV（Unique Visitor）：独立访客，统计1天内访问某站点的用户数。可以统计服务一天的访问日志并根据用户的唯一标识去重得到。响应时间（RT）：响应时间是指系统对请求作出响应的时间，一般取平均响应时间。可以通过Nginx、Apache之类的Web Server得到。
8 DAU
DAU(Daily Active User)，日活跃用户数量。常用于反映网站、互联网应用或网络游戏的运营情况。DAU通常统计一日（统计日）之内，登录或使用了某个产品的用户数（去除重复登录的用户），与UV概念相似
9 MAU
MAU(Month Active User)：月活跃用户数量，指网站、app等去重后的月活跃用户数量
扫一扫
110
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
