mysql
2020-12-15 11:09:24 35 举报AI智能生成
登录查看完整内容
mysql所有知识点合集
作者其他创作
大纲/内容
CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
创建数据库
SHOW DATABASES
查询所有数据库
SHOW CREATE DATABASE db_name;
查询数据库建表时的sql
查询数据库
DROP DATABASE db_name;
删除数据库
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
修改数据库的字符编码和排序方式
修改数据库
USE db_name;
选择数据库
SET NAMES UTF8
命令行设置之后操作的编码格式
数据库操作
SQL : CREATE TABLE tb_name (建表的字段、类型、长度、约束、默认、注释)
NOT NULL
非空
UNSIGNED
非负
PRIMARY KEY
主键
AUTO_INCREMENT
自增
DEFAULT
默认
COMMENT
注释
约束
非负最大值255
1个字节
TINYINT
极小整形
非负最大值65535
2个字节
SMALLINT
小整形
非负最大值4 294 967 295
4个字节
INT
整形
FLOAT
单精度
最大保存255个字节
如果值没有到给定长度用空格补充
CHAR
定长字符串
最大保存65535个字节
用多大占多大
VARCHAR
变长字符串
TEXT
文本
常用类型
CREATE UNIQUE INDEX index_name ON tb_name (account);
创建索引
ALTER TABLE tb_name ADD UNIQUE index_name(field_name);
表字段修改
添加
DROP INDEX 索引名称 ON 表名
删除
唯一索引
ALTER TABLE 表名 ADD INDEX 索引名称(字段名称);
CREATE INDEX Index_name ON tb_name(`account`);
CREATE INDEX 索引名称 ON 表名(字段名);
普通索引
ALTER TABLE tb_name ADD PRIMARY KEY (field_name);
ALTER TABLE 表名 ADD PRIMARY KEY (字段名称)
ALTER TABLE tb_name DROP PRIMARY KEY;
DROP INDEX 索引名称 ON 表名
联合索引
联合索引最左
字符串 \"abc\
字段索引最左
索引最左前缀原理：
表字段索引
创建表
ALTER TABLE tb_name ADD address VARCHAR (100) NOT NULL DEFAULT '' COMMENT '地址';
ALERT TABLE tb_name ADD 添加字段 字段类型 非空约束 默认 注释
字段添加
ALTER TABLE tb_name MODIFY address VARCHAR (50) NOT NULL DEFAULT '' COMMENT '地址';
ALERT TABLE tb_name MODIFY 字段名称 新的字段类型 非负 非空 默认 注释
字段类型修改
ALTER TABLE tb_name CHANGE address addr VARCHAR (100) NOT NULL DEFAULT '' COMMENT '地址';
ALTER TABLE tb_name CHANGE 旧的字段名 新的字段名 新的类型 约束 默认 注释
字段名称类型修改
DESC tb_name;
字段类型查询
ALTER TABLE tb_name DROP addr;
ALTER TABLE 表名 DROP 删除的字段名
字段删除
表字段的增删改查
ALTER TABLE tb_name RENAME TO new_tb_name;
ALTER TABLE 旧表名 RENAME TO 新表名
表名修改
ALTER TABLE tb_name ENGINE = InnoDB;
ALTER TABLE 表名 ENGINE = 新的引擎名称
引擎修改
表修改
修改表
DROP TABLE tb_name;
删除表
SHOW TABLES;
查询所有表
SHOW CREATE TABLE tb_name;
查询建表时的sql
查询表
数据库表操作
ABS()绝对值
SQRT(x) 求二次方根
CEIL 和 CEILING 向上取整
FLOOR 向下取整
SIGN(x) 返回参数的符号，x 的值为负、零和正时返回结果依次为 -1、0 和 1。
SIN(x) 函数计算正弦值
ASIN(X) 求反正弦值，与函数 SIN 互为反函数
COS(X) 求余弦值
ACOS(X) 求反余弦值，与函数 COS 互为反函数
TAN(X) 求正切值
ATAN(X) 求反正切值，与函数 TAN 互为反函数
COT(X) 求余切值
PI() π值
1.数学函数
MAX(X) 查询指定列的最大值
MIN(X) 查询指定列的最小值
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
SUM(col)返回指定列的所有值之和
2.聚合函数
TRIM(str)去除字符串首部和尾部的所有空格
RTRIM(str) 返回字符串str尾部的空格
LTRIM(str) 从字符串str中切掉开头的空格
LENGTH(s)返回字符串str中的字符数
ASCII(char)返回字符的ASCII码值
CONCAT(sl，s2，...)\t合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT(s1，x，len，s2) 返回字符串 s1，子字符串起始于 x 位置，并且用 len 个字符长的字符串代替 s2。
UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果
LOWER(str) 将字符串 str 中的字母字符全部转换成小写。
LEFT(s，n) 返回字符串 s 最左边的 n 个字符。
RIGHT(s，n) 返回字符串 s 最右边的 n 个字符。
REPLACE(s，s1，s2) 使用字符串 s2 替换字符串 s 中所有的字符串 s1。
SUBSTRING(s，n，len) 带有 len 参数的格式，从字符串 s 返回一个长度同 len 字符相同的子字符串，起始于位置 n。
REVERSE(s) 字符串 s 反转，返回的字符串的顺序和 s 字符串的顺序相反。
3.字符串函数
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
NOW 和 SYSDATE\t两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP()获取系统当前的时间戳
FROM_UNIXTIME(时间戳) 格式化传入的时间戳，转成日期格式
MONTH\t获取指定日期中的月份
MONTHNAME\t获取指定日期中的月份英文名称
DAYNAME\t获取指定曰期对应的星期几的英文名称
DAYOFWEEK\t获取指定日期对应的一周的索引位置值
WEEK\t获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR\t获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH\t获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR\t获取年份，返回值范围是 1970〜2069
TIME_TO_SEC\t将时间参数转换为秒数
SEC_TO_TIME\t将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE\t两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE\t两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME\t时间加法运算，在原始时间上添加指定的时间
SUBTIME\t时间减法运算，在原始时间上减去指定的时间
DATEDIFF\t获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT\t格式化指定的日期，根据参数返回指定格式的值
WEEKDAY\t获取指定日期在一周内的对应的工作日索引
4.日期和时间函数
MD5() 计算字符串str的MD5校验和
PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
SHA() 计算字符串str的安全散列算法(SHA)校验和
5.加密函数
CASE value WHEN [compare-value1] THEN result1 [WHEN [compare-value2] THEN result2 [ELSE result3] END
6.控制流程函数
FORMAT()
7.格式化函数
CAST(expr AS type) 将任意类型的表达式expr转换成指定类型type的值。
8.类型转化函数
DATABASE() 或SCHEMA() 返回当前数据库名
USER()、 SYSTEM_USER()、 SESSION_USER()、 CURRENT_USER() 和CURRENT_USER() 返回当前登陆用户名
VERSION() 返回MySQL服务器的版本
9.系统信息函数
MySQL函数
语法explain select * from xxl_job_log l where l.job_id in (select id from xxl_job_info)
CONNECTION_ID() 返回当前客户的连接ID
id：表示查询中执行select子句或操作表的顺序
SIMPLE:简单的select查询，查询中不包括子查询或者UNION
PRIMARY：查询中若包括任何复杂的子部分，最外层查询则被标记为PRIMARY
DEPENDENT UNION:dependent union出现在union或union all 形成的集合查询中。此处的dependent表示union或union all联合而成的单位查询受外部影响。
UNION RESULT: union result为包含union结果的数据表。MariaDB中，union all或union（DISTINCT）查询会将所有union结果创建为临时表。执行计划中，该临时表所在行为select_type为union result。由于union result在实际查询中不是单位查询，所以没有单独的id值。
SUBQUERY:在select或where列表中包含了子查询
select_type：表示查询的类型，主要用于区别普通查询，联合查询，子查询等复杂查询https://www.cnblogs.com/danhuangpai/p/8475458.html
table：查询的表
partitions:匹配的分区
ALL：全部扫描，效率低
index：Full Index Scan，index与ALL区别为index类型只遍历索引树explain select * from film; file中有name字段为索引
range：只检索给定范围的行，使用一个索引来选择行explain select * from employee where rec_id < 3其中rec_id为主键
ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给employee表中的name列添加一个普通的key（值允许重复）explain select * from film where name = \"film1\" ref name是film中的普通索引
eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件eq_ref 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的explain select * from film_actor left join film on film_actor.film_id= film.id eq_ref 根据主键或唯一索引查询film
const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用systemselect 1 from actor where id =1 constexplain extended select * from (select * from film where id =1) tmp 临时表中只有一条记录，system
NULL： MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成explain select min(id) from film id是主键
type：在表中找到所需行的方式 ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null），可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mys ql认为索引对此查询帮助不大，选择了全表查询
key：显示MySQL实际决定使用的键（索引)
key_len计算规则如下：字符串char(n)：n字节长度varchar(n)：2字节存储字符串长度，如果是utf-8，则长度3n + 2数值类型tinyint：1字节smallint：2字节int：4字节bigint：8字节 时间类型 date：3字节timestamp：4字节datetime：8字节如果字段允许为 NULL，需要1字节记录是否为 NULL索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好符提取出来做索引。
ref：列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows：估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
filtered：一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）
Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by;distinct
Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
Using index : 表示覆盖索引
Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能
Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used：Query语句中使用from dual 或不含任何from子句
Extra
字段说明
缺点：• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况• EXPLAIN不考虑各种Cache• EXPLAIN不能显示MySQL在执行查询时所作的优化工作• 部分统计信息是估算的，并非精确值• EXPLAIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划
explain
SHOW WARNINGS 在explain执行后执行，查看翻译后的sql
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
最佳左前缀法则
like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描操作
字符串不加单引号索引失效EXPLAIN SELECT * FROM employees WHERE name = '1000';EXPLAIN SELECT * FROM employees WHERE name = 1000;
or 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的
union all 不去重复，union去重复，union使用了临时表，应尽量避免使用临时表
order by如果根据多个值进行排序，那么排序方式必须保持一致，要么同时升续，要么同时降续，排序方式不一致不走索引
索引优化规则
不同的数据类型的存储和检索方式不同，对应的性能也不同，所以说要合理的选用字段的数据类型。比如人的年龄用无符号的unsigned tinyint即可，没必要用integer
字段的数据类型
数据类型的长度
表的存储引擎
优化数据库表结构的设计
分库
垂直分表
水平分表
分表
分库分表
数据库参数配置优化
主从复制，读写分离
数据库编码: 采用utf8mb4而不使用utf8
字段名
不同存储引擎对char和varchar的使用原则不同，myisam:建议使用国定长度的数据列代替可变长度。innodb：建议使用varchar，大部分表都是使用innodb，所以varchar的使用频率更高
字符类型
金额类型的字段尽量使用long用分表示，尽量不要使用bigdecimal，严谨使用float和double因为计算时会丢失经度如果需要使用小数严谨使用float，double，使用定点数decimal，decimal实际上是以字符串的形式存储的，所以更加精确，java中与之对应的数据类型为BigDecimal如果值为非负数，一定要使用unsigned，无符号不仅能防止负数非法数据的保存，而且还能增大存储的范围不建议使用ENUM、SET类型，使用TINYINT来代替
数值类型
日期类型
是否为null
字段类型
where中如果有多个过滤条件，在没有索引的情况下将过滤多的写在前面，过滤少的写在后面
实例
尽量少使用select *，需要什么字段就去取哪些字段
不要使用count(列名)或 count(常量)来替代 count()，count()是SQL92定义的标准统计行数的语法，跟数据库无关，跟 NULL和非NULL无关。 说明:count(*)会统计值为NULL 的行，而count(列名)不会统计此列为NULL值的行
禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。避免使用存储过程、触发器
优化方式
优化
根据版本号控制
乐观锁
针对同一份数据，多个读操作可以同时进行而不会互相影响
不能进行写操作
读锁(共享锁)
当前写操作没有完成前，它会阻断其他写锁和读锁
写锁(排他锁)
锁定表或者行，让其他数据操作等待
悲观锁
乐观锁和悲观锁
lock table表名称 read(write)unlock tables
表锁偏向MyISAM存储引擎，开销小，加锁快，无思索，锁定粒度大，发生锁冲突的概率最高，并发度最低当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞
表锁
行锁偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MYISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁
行锁
表锁和行锁
分类
当前正在等待锁定的数量
Innodb_row_ lock_current_wait
从系统启动到现在锁定总时间长度
Innodb_row_ lock_time
每次等待所花平均时间
Innodb_row_ lock_time_avg
从系统启动到现在等待最长的一次所花时间
Innodb_row_ lock_time_max
系统启动后到现在总共等待的次数
Innodb_row_ lock_waits
show status like'innodb_row_lock%';
行锁分析
Session _1执行：select *from account where i d= 1 for update;Session _2执行：select *from account where i d= 2 for update;Session _1执行：select *from account where i d= 2 for update;Session _2执行：select *from account where i d= 1 for update;查看近期死锁日志信息：show engine inno db statu s\\G;
死锁
锁
添加单条
添加多条
增
DELETE FROM tb_name WHERE ...
sql
删除时必须加where条件
注意
删
修改时必须加where条件
改
SELECT * FROM tb_name
基础的查询
大于、小于、等于、不等于、大于等于、小于等于
SELECT * FROM tb_name WHERE user_id >10;
比较运算符
逻辑运算符是用来拼接其他条件的。用and或者or来连接两个条件，如果用or来连接的时候必须使用小括号
SELECT * FROM tb_name WHERE user_id > 10 AND sex = '男'
逻辑运算符
%（百分号）匹配零个或者多个任意字符
_（下划线）匹配一个任意字符
通配符
SELECT * FROM tb_name WHERE username LIKE '张%';查找username开头是张的数据
SELECT * FROM tb_name WHERE username LIKE '%张%';查询username中含有张的数据
SELECT * FROM tb_name WHERE username LIKE '%张';查询username字段的数据以张结尾的
SELECT * FROM tb_name WHERE username LIKE '张_';查询username以张开头后边有一个字符的数据
LIKE模糊查询
IN字段指定多个值查询
field BETWEEN value1 AND value2
SELECT * FROM user WHERE user_id BETWEEN 2 AND 9;查询user表中user_id大于等于2小于等于9的所有值
BETWEEN AND 区间查询
where子句
count(field)获取符合条件出现的非null值的次数
SUM(field)获取所有符合条件的数据的总和
AVG(field)或者平均值
配合函数
group by会创建临时表
GROUP BY 分组查询
ORDER BY field DESC;降序查询
ORDER BY field ASC;升序查询
查询顺序
SELECT * FROM tb_name ORDER BY id DESC; 查询tb_name表中所有数据，按id的降序来查找
ORDER BY 查询排序
LIMIT 后边可以跟两个参数，如果只写一个表示从零开始查询指定长度，如果两个参数就是从第一个参数开始查询查询长度是第二个参数的值，俩个参数必须是整形。
参数
SELECT * FROM tb_name LIMIT 5;查询tb_name表中的所有数据，只要前边的5条数据
LIMIT 查询结果截取
SELECT * FORM tb_name1 LEFT JOIN tb_name2 ON tb_name1.t2_id = tb_name2.t2_id;用表一的t2_id和表二的t2_id来关联，查询所有的值。
FROM 之后的表是主表
左关联
SELECT * FORM tb_name1 JOIN tb_name2 ON tb_name1.t2_id = tb_name2.t2_id;用表一的t2_id和表二的t2_id来关联，查询所有的值。
中关联没有主表
中关联
SELECT * FORM tb_name1 RIGHT JOIN tb_name2 ON tb_name1.t2_id = tb_name2.t2_id;用表一的t2_id和表二的t2_id来关联，查询所有的值。
在ON后边的表是主表
右关联
外关联
内管理的关联条件是用where来说关联的，多张表之间用AND来拼接where条件
内关联
主表关联副表，如果副表数据不够用NULL来补全，但是中关联的时候，如果不够了，左边的数据或者右边的数据不会显示。直接去掉
外关联的说明
union会去重，union all不会
横向连接：两个表字段一样，数据合并
关联查询
BEGIN开启事务
ROLLBACK;事务回滚
COMMIT;事务提交
关键词
表的引擎为InnoDB
必备条件
事务
查
表数据操作
1.打开cmd命令
2.打开到mysql文件夹下的bin目录
3.通过mysqldump来执行导出
4.命令：mysqldump -u root -p 数据库（class15） > 要导出的文件名如：（test.sql）
5.导出之后的文件会出现在bin目录下
数据库导出
1.cmd打开到mysql的bin目录下
2.通过 mysql -uroot -p 输入密码的形式进入到数据库中
3.选择数据库 USE db_name;
4.执行导入命令： source d:\\datafilename.sql 后边路径是sql文件存放的物理路径
sql文件导入
数据库导入导出
innodb主键使用自增bigint效率比uuid高1.方便比较大小2.不会破坏B+TREE结构
聚集索引：索引和数据在同一张表非聚集索引：索引在一张表，数据在一张表
innodb使用b+tree存索引和数据 不使用hash的原因：范围查找使用hash不合适，需要全表扫描，hash(主键)直接存储到位置，因此一般使用B+Tree
支持事务
支持行锁
innoDB
不支持事务
只支持表锁
myisam
show engines查看所有引擎
引擎
持久性(Durable)
原子性(Atomicity)
隔离性(Isolation)
一致性(Consistent)
ACID属性
两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新
更新丢失（Lost Update）
事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求
脏读（Dirty Reads）
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。 一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性
不可重读（Non-Repeatable Reads）
个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”
幻读（Phantom Reads）
并发事务处理带来的问题
脏读/不可重复读/幻读都可能
读未提交(Read uncommitted)
脏读不可能，不可重复读/幻读都可能
读已提交(Read uncommitted)
可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）insert、update和delete会更新版本号，是当前读（当前版本）
要避免幻读可以用间隙锁在Session _1下面执行update acc ount se t name ='zhuge'where i d> 10and id<= 20;，则其他Session没法插入这个范围内的数据
不可重复读/脏读不可能，幻读都可能
可重复读(Repeatable read)
mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到
脏读/不可重复读/幻读都不可能
可串行化(Serializable)
show variables like 'transaction_isolation';
select @@transaction_isolation;
查看事务隔离级别
默认的事务隔离级别(Repeatable read)
事务隔离级别
横向：表字段相同，数据量太大
join
纵向：一个表存基本信息，另外一个表存详情
和单个磁盘或者文件系统分区相比，可以存储更多数据优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时在涉及sum()和count()这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据跨多个磁盘来分散数据查询，以获得更大的查询吞吐量
优点
优缺点
1）、当需要删除一个分区上的\"旧的\
基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义
Range
设置若干个固定值进行分区，如果某个字段的值在这个设置的值列表中就会被分配到该分区。适用于字段的值区分度不高的，或者值是有限的，特别是像枚举这样特点的列
List
range columns
常规hash分区
常规hash分区在管理上带来了的代价太大，不适合需要灵活变动分区的需求。为了降低分区管理上的代价，mysql提供了线性hash分区，分区函数是一个线性的2的幂的运算法则。同样线性hash分区的记录被存在那个分区也是能被计算出来的。线性hash分区的优点是在分区维护(增加、删除、合并、拆分分区)时，mysql能够处理的更加迅速，缺点是：对比常规hash分区，线性hash各个分区之间数据的分布不太均衡
线性hash分区使用的是一个线性的2的幂运算法则
线性hash分区
hash分区
partition by key(expr) partitions num;-- 不指定默认首选主键作为分区键，在没有主键的情况下会选择非空唯一键作为分区键partition by key() partitions num;-- linear keypartition by linear key(expr)
按照key进行分区非常类似于按照hash进行分区，只不过hash分区允许使用用户自定义的表达式，而key分区不允许使用用于自定义的表达式，需要使用mysql服务器提供的hash函数，同时hash分区只支持整数分区，而key分区支持使用出blob or text类型外的其他类型的列作为分区键
key分区
是分区表中对每个分区的再次分割，又被称为复合分区，支持对range和list进行子分区，子分区即可以使用hash分区也可以使用key分区。复合分区适用于保存非常大量的数据记录
子分区
分区方式
管理分区
分区
执行mysql需要连接的socket
socket
host
执行mysql的端口
port
执行mysql 时默认的密码
password
client mysql所有客户端执行时配置
关闭命令补全
no-auto-rehash
开启命令补全
auto-rehash
mysql
myisamchk
导出全部数据库
all-databases
导出全部表空间/不导出全部表空间信息
all-tablespaces/no-tablespaces
每个数据库创建之前添加drop数据库语句。
add-drop-database
每个数据表创建之前添加drop数据表语句。
add-drop-table
每个触发器创建之前添加drop语句
add-drop-trigger
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。
add-locks
允许创建是关键词的列名字。这由表名前缀于每个列名做到。
allow-keywords
在'CHANGE MASTER'前添加'STOP SLAVE'，并且在导出的最后添加'START SLAVE'。
apply-slave-statements
字符集文件的目录
character-sets-dir
附加注释信息。默认为打开，
comments
导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，要使用几个值，用逗号将它们隔开。它并不保证能完全兼容，而是尽量兼容。
compatible
导出更少的输出信息(用于调试)。去掉注释和头尾等结构。
compact
使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
complete-insert
在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)
create-options
导出几个数据库。参数后面所有名字参量都被看作数据库名。
databases
设置默认字符集，默认值为utf8
default-character-set
master备份后删除日志. 这个参数将自动激活--master-data。
delete-master-logs
对于每个表，用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地导入dump出来的文件，因为它是在插入所有行后创建索引的。该选项只适合MyISAM表，默认为打开状态。
disable-keys
该选项将导致主的binlog位置和文件名追加到导出数据的文件中。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，在命令前增加说明信息。该选项将会打开--lock-all-tables，除非--single-transaction被指定。该选项会自动关闭--lock-tables选项。默认值为0。
dump-slave
导出事件。
events
使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用--skip-extended-insert取消选项。
extended-insert
导出文件中忽略给定字段。与--tab选项一起使用，不能用于--databases和--all-databases选项
fields-terminated-by
输出文件中的各个字段用给定字符包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项
fields-enclosed-by
输出文件中的各个字段用给定字符选择性包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项
fields-optionally-enclosed-by
输出文件中的各个字段忽略给定字符。与--tab选项一起使用，不能用于--databases和--all-databases选项
fields-escaped-by
开始导出之前刷新日志。请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。
flush-logs
在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
flush-privileges
在导出过程中忽略出现的SQL错误。
force
使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB。
hex-blob
需要导出的主机信息
不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
ignore-error
在--dump-slave产生的'CHANGE MASTER TO..'语句中增加'MASTER_HOST=<host>，MASTER_PORT=<port>'
include-master-host-port
在插入行时使用INSERT IGNORE语句.
insert-ignore
输出文件的每行用给定字符串划分。与--tab选项一起使用，不能用于--databases和--all-databases选项。
lines-terminated-by
提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
lock-all-tables
开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。
lock-tables
附加警告和错误信息到给定文件
log-error
该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。
master-data
使用autocommit/commit 语句包裹表。
no-autocommit
只导出数据，而不添加CREATE DATABASE 语句。
no-create-db
只导出数据，而不添加CREATE TABLE 语句。
no-create-info
不导出任何数据，只导出数据库表结构。
no-data
如果存在主键，或者第一个唯一键，对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效，但会使得导出工作花费很长时间。
order-by-primary
quick
使用（`）引起表和列名。默认为打开状态，使用--skip-quote-names取消该选项。
quote-names
使用REPLACE INTO 取代INSERT INTO.
replace
导出存储过程以及自定义函数。
routines
添加'SET NAMES default_character_set'到输出文件。默认为打开状态，使用--skip-set-charset关闭选项。
set-charset
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。
single-transaction
将导出时间添加到输出文件中。默认为打开状态，使用--skip-dump-date关闭选项。
dump-date
为每个表在给定路径创建tab分割的文本文件。注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上。
tab
导出触发器。该选项默认启用，用--skip-triggers禁用它。
triggers
在导出顶部设置时区TIME_ZONE='+00:00' ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。
tz-utc
只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
where
导出项
通过哪个ip来连接mysql服务器
bind-address
在客户端和服务器之间启用压缩传递所有信息
compress
服务器发送和接受的最大包长度。
max-allowed-packet
TCP/IP和socket连接的缓存大小。
net-buffer-length
mysql服务器端口
指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock
指定连接的用户名。
user
连接项
客户端插件的目录，用于兼容不同的插件版本。
plugin-dir
客户端插件默认使用权限。
default-auth
enable-cleartext-plugin
其他
ssl
ssl-verify-server-cert
ssl-ca
ssl-capath
ssl-cert
ssl-cipher
ssl-key
ssl-crl
ssl-crlpath
tls-version
server-public-key-path
get-server-public-key
mysqldump
客户端配置
mysql服务监听的端口
server-id
监听端口
监听的socket
mysql进程启动后的进程id存放位置
pid-file
mysql的安装路径
basedir
mysql数据的存放路径
datadir
mysql临时文件的存放路径
tmpdir
默认时区
default-time-zone
服务配置项
禁止 MySQL 对外部连接进行 DNS 解析，使用这一选项可以消除 MySQL 进行 DNS 解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用 IP 地址方式，否则 MySQL 将无法正常处理连接请求！
skip-name-resolve
不能使用连接文件，多个客户可能会访问同一个数据库，因此这防止外部客户锁定 MySQL 服务器。 该选项默认开启
skip-symbolic-links
skip-external-locking
skip-networking
接受队列，对于没建立 tcp 连接的请求队列放入缓存中，队列大小为 back_log，受限制与 OS 参数，试图设定 back_log 高于你的操作系统的限制将是无效的。默认值为 50。对于 Linux 系统推荐设置为小于512的整数。如果系统在一个短时间内有很多连接，则需要增大该参数的值
back_log
指定MySQL允许的最大连接进程数。如果在访问数据库时经常出现\"Too Many Connections\"的错误提 示，则需要增大该参数值。
max_connections
如果某个用户发起的连接 error 超过该数值，则该用户的下次连接将被阻塞，直到管理员执行 flush hosts ; 命令或者服务重启， 防止黑客 ， 非法的密码以及其他在链接时的错误会增加此值
max_connect_errors
MySQL打开的文件描述符限制，默认最小1024;当open_files_limit没有被配置的时候，比较max_connections*5和ulimit-n的值，哪个大用哪个，当open_file_limit被配置的时候，比较open_files_limit和max_connections*5的值，哪个大用哪个。
open_files_limit
connect-timeout
等待关闭连接的时间
wait-timeout
interactive-timeout
如果某个通信端口的读操作中断了，在放弃前重试多次
net_retry_count
包消息缓冲区初始化为 net_buffer_length 字节，但需要时可以增长到 max_allowed_packet 字节
net_buffer_length
max_allowed_packet
所有线程所打开表的数量. 增加此值就增加了mysqld所需要的文件描述符的数量这样你需要确认在[mysqld_safe]中 “open-files-limit” 变量设置打开文件数量允许至少4096
table_cache
thread_stack
thread_cache_size
thread_concurrency
query_cache_limit
查询缓存分配的最小块大小.默认是 4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%如果查询缓存碎片率超过 20%，可以用 FLUSH QUERY CACHE 整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size *100%查询缓存利用率在 25%以下的话说明 query_cache_size 设置的过大，可适当减小;查询缓存利用率在 80%以上而且 Qcache_lowmem_prunes > 50 的话说明 query_cache_size 可能有点小，要不就是碎片太多。查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
query_cache_min_res_unit
query_cache_size
系统资源项
skip-innodb
# InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间独立表空间优点：1．每个表都有自已独立的表空间。2．每个表的数据和索引都会存在自已的表空间中。3．可以实现单表在不同的数据库中移动。4．空间可以回收（除drop table操作处，表空不能自已回收）缺点：1.单表增加过大，如超过100G结论：共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整：innodb_open_files
innodb_file_per_table
#启用InnoDB的status file，便于管理员查看以及监控等
innodb_status_file
限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300
innodb_open_files
设置InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。
innodb_additional_mem_pool_size
innodb_buffer_pool_size
innodb_write_io_threads/innodb_read_io_threads
设置此选项如果你希望 InnoDB 表空间文件被保存在其他分区.默认保存在 MySQL 的 datadir 中.
innodb_data_home_dir
innodb_data_file_path
innodb_file_io_threads
innodb_thread_concurrency
innodb_flush_log_at_trx_commit
innodb_log_buffer_size
innodb_log_file_size
在日志组中的文件总数.通常来说 2~3 是比较好的.
innodb_log_files_in_group
innodb_log_group_home_dir
InnoDB 事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB 在它自己的 锁定表中自动检测事务死锁并且回滚事务。 InnoDB 用 LOCK TABLES 语句注意到锁定设置。默认值是 50 秒
innodb_lock_wait_timeout
innodb_flush_method
innodb_force_recovery
innodb_fast_shutdown
innodb
指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。如果是以InnoDB引擎为主的DB，专用于MyISAM引擎的 key_buffer_size 可以设置较小，8MB 已足够 如果是以MyISAM引擎为主，可设置较大，但不能超过4G. 在这里，强烈建议不使用MyISAM引擎，默认都是用InnoDB引擎.注意：该参数值设置的过大反而会是服务器整体效率降低！
key_buffer_size
sort_buffer_size
join_buffer_size
read_buffer_size
MyISAM 以索引扫描(Random Scan)方式扫描数据的 buffer大小
read_rnd_buffer_size
bulk_insert_buffer_size
myisam_sort_buffer_size
mysql重建索引时允许使用的临时文件最大大小
myisam_max_sort_file_size
myisam_repair_threads
myisam_recover
default_table_type
server 级别字符集
character-set-server
默认存储引擎
default-storage-engine
tmp_table_size
数据配置项
binlog_cache_size
log-bin
二进制的索引文件名
log-bin-index
超过 30 天的 binlog 删除
expire_logs_days
binlog
如果二进制日志写入的内容超出给定值，日志就会发生滚动。你不能将该变量设置为大于1GB或小于4096字节。 默认值是1GB。如果你正使用大的事务，二进制日志还会超过max_binlog_size
max_binlog_size
relay-log
relay-log的索引文件名
relay_log_index
标记relaylog允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小；
max_relay_log_size
是否自动清空不再需要中继日志时。默认值为1(启用)
relay-log-purge
relaylog
log-warnings
错误日志路径
参数 log_output 指定了慢查询输出的格式，默认为 FILE，你可以将它设为 TABLE，然后就可以查询 mysql 架构下的 slow_log 表了
log_output
指定是否开启慢查询日志(该参数要被slow_query_log取代，做兼容性保留)
log_slow_queries
slow_query_log
long-query-time
在慢速日志中记录更多的信息.一般此项最好打开，打开此项会记录使得那些没有使用索引的查询也被作为到慢速查询附加到慢速日志里
log_long_format
指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
slow_query_log_file
如果运行的SQL语句没有使用索引，则mysql数据库同样会将这条SQL语句记录到慢查询日志文件中。
log-queries-not-using-indexes
记录那些由于查找了多余n次而引发的慢查询
min_examined_row_limit
记录那些慢的optimize table，analyze table和alter table语句
long-slow-admin-statements
记录由Slave所产生的慢查询
log-slow-slave-statements
general_log
general_log路径
general_log_file
日志项
当 slave 执行 load data infile 时用
slave-load-tmpdir
skip-slave-start
slave-net-timeout
net_read_timeout
net_write_timeout
表示slave将复制事件写进自己的二进制日志
log_slave_updates
replicate-wild-ignore-table
slave_skip_errors
集群
sysdate-is-now
杂项
mysqld
#增加每个进程的可打开文件数量.确认你已经将全系统限制设定的足够高!打开大量表需要将此值设大
open-files-limit
mysqld_safe
服务端配置
数据库配置 my.cnf
MySQL
0 条评论
回复 删除
下一页
