mysql 概述

1. 三范式

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

 

2.binblog 

录入模式：statement，row和mixed。

• statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
• row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。

• mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

 

数据类型：

1、整数类型，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。

长度：整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。

例子，假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。

2、实数类型，包括FLOAT、DOUBLE、DECIMAL。

DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。

而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。

计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

3、字符串类型，包括VARCHAR、CHAR、TEXT、BLOB

VARCHAR用于存储可变长字符串，它比定长类型更节省空间。

VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。

VARCHAR存储的内容超出设置的长度时，内容会被截断。

CHAR是定长的，根据定义的字符串长度分配足够的空间。

CHAR会根据需要使用空格进行填充方便比较。

CHAR适合存储很短的字符串，或者所有值都接近同一个长度。

CHAR存储的内容超出设置的长度时，内容同样会被截断。

使用策略：

对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。

对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。

使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。

尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

4、枚举类型（ENUM），把不重复的数据存储为一个预定义的集合。

有时可以使用ENUM代替常用的字符串类型。

ENUM存储非常紧凑，会把列表值压缩到一个或两个字节。

ENUM在内部存储时，其实存的是整数。

尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。

排序是按照内部存储的整数

5、日期和时间类型，尽量使用timestamp，空间效率高于datetime，

用整数保存时间戳通常不方便处理。

如果需要存储微妙，可以使用bigint存储。

 

三、innode db ,myisam 区别

 

四、

创建索引的原则（重中之重）

索引虽好，但也不是无限制的使用，最好符合一下几个原则

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）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

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

 

五、数据库 ACID:

“原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）。”

原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；

隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

sql生命周期：

1.connect  db 

2、数据库进程拿到请求sql

3、解析并生成执行计划，执行

4、读取数据到内存并进行逻辑处理

5、通过步骤一的连接，发送结果到客户端

6、关掉连接，释放资源

 

 

六、慢查询日志

用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。

开启慢查询日志

配置项：slow_query_log

可以使用show variables like 'slow_query_log’查看是否开启:

# 查看
show variables like 'slow_query_log';
# 开启
set GLOBAL slow_query_log = on

使用set GLOBAL slow_query_log = on来开启，会在datadir产生一个xxx-slow.log的文件

慢查询日志：

用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。

6.1开启慢查询日志

配置项：slow_query_log

可以使用show variables like 'slov_query_log’查看是否开启，如果状态值为OFF，可以使用set GLOBAL slow_query_log = on来开启，它会在datadir下产生一个xxx-slow.log的文件：

 

6.2慢查询阈值设置

配置项：long_query_time

查看：show VARIABLES like 'long_query_time'，单位秒

show VARIABLES like 'long_query_time';
#设置
set long_query_time  =  0.5

实操时应该从长时间设置到短的时间，即将最慢的SQL优化掉

查看日志，一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中

优化：

所以优化也是一般针对这三个方向来的，

1. select * 滥用 否load了额外的数据，可能是查询了多余的行并且抛弃掉了

2. 索引失效,或者未命中，没加索引

3. 表数据量过于庞大考虑分库分表，如果是的话可以进行横向或者纵向的分表。

 

大表数据查询，怎么优化

1、优化shema、sql语句 索引；

2、第二加缓存，memcached, redis；

3、主从复制，读写分离；

4、垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；

5、水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表

 

为什么要尽量设定一个主键？

主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键，也建议添加一个自增长的ID列作为主键。设定了主键之后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全。

主键使用自增ID还是UUID？

推荐使用自增ID，不要使用UUID。

因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B 树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。

关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

字段为什么要求定义为not null？

null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

设置临界时间

 

七、现场MySQL性能问题分析报告

1.导入事故数据

2.开启profiling

show variables like 'profiling';
set  profiling =1 

3.执行原sql:

4. show profiles 查看sql耗时

 

5.查询profile结果（MySQL 5.7之后profiling被移动到了information_schema下，所以show profile for query 2;已经无效）

5.7以前可以使用

show profile  all f or query  id  2 ;

select
	query_id,
	-- 查询id 它用于标识一个查询
	seq,
	-- 显示序号
(
	select
		sum(duration)
	from
		information_schema.profiling as innert
	where
		innert.query_id = outert.query_id) as total_cost ,
	-- 总用时in seconds
	state,
	-- 状态
	duration,
	-- 持续时间
	cpu_user,
	-- 用户空间的cpu 使用量
	cpu_system,
	-- 内核空间的cpu 使用量
	-- context_voluntary, -- 自愿上下文切换
	-- context_involuntary, -- 非自愿上下文切换
	block_ops_in,
	-- 块调入次数
	block_ops_out,
	-- 块调出次数
	swaps
	-- 发生swap 的次数
from
	information_schema.profiling as outert
where
	query_id = 2
order by
	seq;

数据分析：

 

定位cpu高占用

 

八、

、MySQL参数

1）连接请求的参数：max_connections

MySQL的最大连接数，增加该值增加mysqld要求的文件描述符的数量。连接请求量大时，建议调高此值调的越高，内存开销越大。

mysql>show variables like 'max_connections';

+-------------------------+----------+

|Variable_name|Value|

+-------------------------+----------+

|max_connections|512 |

+-------------------------+----------+

mysql>show status like 'max%connections';

+---------------------------+----------+

|Variable_name|Value|

+---------------------------+----------+

|max_used_connections|512 |

+---------------------------+----------+

 

2）全局缓存参数

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。Key_reads是内存中没有找到索引直接从硬盘读取索引的数量。

mysql>show variables like' key_buffer_size'; 

+-------------------------+-------------+

|Variable_name|Value|

+-------------------------+-------------+

|key_buffer_size|536870912 |

+-------------------------+-------------+

mysql>show status like 'key_read%'; 

+-------------------------+---------------+

|Variable_name|Value|

+-------------------------+---------------+

|Key_read_requests|178306331520 |

|Key_reads|67 |

 +-------------------------+---------------+

 

使用查询缓冲，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。

mysql>show variables like ' key_buffer_size';

mysql>show status like ' key_read%';

查询缓存碎片率= Qcache_free_blocks/ Qcache_total_blocks* 100%

查询缓存利用率= (query_cache_size–Qcache_free_memory) / query_cache_size* 100%

查询缓存命中率= (Qcache_hits–Qcache_inserts) / Qcache_hits* 100%

 

3）每个连接的缓存参数

① Sort_buffer_size

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M)，可改为16777208 (16M)。

② Join_buffer_size

联合查询操作所能使用的缓冲区大小。

record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size为每个线程独占，也就是说，如果有100个线程连接，则占用为16M*100。

③ table_open_cache

表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。

mysql> show global status like 'open%tables%';

+-----------------+-------+

| Variable_name| Value |

+-----------------+-------+

| Open_tables| 1024 |

| Opened_tables| 1465 |

+-----------------+-------+

mysql>showvariableslike'table_open_cache';

+----------------------+-------+

|Variable_name|Value|

+----------------------+-------+

|table_open_cache|1024|

+----------------------+-------+

 

④ tmp_table_size

临时表大小。通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。

mysql>showglobal statuslike' created_tmp%';

+-----------------------------+----------+

|Variable_name|Value |

+-----------------------------+----------+

|Created_tmp_disk_tables|21197|

| Created_tmp_files| 58|

| Created_tmp_tables| 1771587 |

+-----------------------------+----------+

mysql> show variables like 'tmp_table_size';

+-----------------+------------+

| Variable_name| Value |

+-----------------+------------+

| tmp_table_size| 16777216 |

+-----------------+------------+

 

⑤ thread_cache_size

可以复用的保存在缓冲区中的线程的数量。当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。

mysql>show global status like 'Thread%';

+----------------------+-------+

|Variable_name|Value|

+----------------------+-------+

|Threads_cached|31|

|Threads_connected|239|

|Threads_created|2914|

|Threads_running|4|

+----------------------+-------+

mysql>show variables like 'thread_cache_size';

+---------------------+-------+

|Variable_name|Value|

+---------------------+-------+

|thread_cache_size|32|

+---------------------+-------+

 

4）配置InnoDB的参数

① Innodb_buffer_pool_size

InnoDB使用该参数指定大小的内存来缓冲数据和索引，其对InnoDB的重要性等于key_buffer_size对MyISAM的重要性。

② Innodb_log_buffer_size

Innodb_log缓存大小，一般为1-8M，默认为1M，对于较大的事务，可以增大缓存大小。可设置为4M或8M。