Loading... <p><span style="font-size: 20px"><strong>一、安装以及初始化</strong></span></p> <p><span style="font-size: 18px"><strong> <span style="font-size: 16px"> 软件包来源</span></strong></span></p> <p><span class="Apple-tab-span"> </span> 1. vendor, rpm </p> <p><span class="Apple-tab-span"> </span> 2. 官网,source code 编译</p> <p><span class="Apple-tab-span"> </span> 3. 官网, 二进制包 </p> <p><strong><span style="font-size: 18px"> </span></strong><span style="font-size: 18px"><strong><span style="font-size: 16px"> 二进制包安装过程 </span></strong></span></p> <p><span class="Apple-tab-span"> </span> 1. 下载二进制包,解压缩到</p> <pre class="brush:bash;toolbar:false"> # tar -xf mariadb-5.5.46-linux-x86_64.tar.gz -C /usr/local/</pre> <p> 2. 创建链接</p> <pre class="brush:bash;toolbar:false"> # ln -sv mariadb-5.5.46-linux-x86_64.tar.gz mysql</pre> <p> 3. 库导出, 在ld.so.conf.d 下面穿件文件,写有/usr/local/mysql/lib, 然后重新导入库</p> <pre class="brush:bash;toolbar:false"> # idconfig -v</pre> <p><span class="Apple-tab-span"> </span>4. 二进制文件导出, 在/etc/profile.d/下面创建一个写有二进制目录的文件即可 </p> <p> 5. 头文件导出, 把/usr/local/mysql/include/mysql 链接至/usr/local/include 下面</p> <p><strong><span style="font-size: 18px"> </span></strong><span style="font-size: 18px"><strong><span style="font-size: 16px">初始化数据库, 运行安装目录,/usr/local/mysql/scripts 下的mysql_install_db </span></strong></span></p> <p><span class="Apple-tab-span"> </span> 1. mysql_install_db: 下面三个选项通常要指定</p> <pre class="brush:bash;toolbar:false"> --basedir : 安装根目录 --datadir : 数据库存放目录 --user : 服务器进程用户, 要提前创建用来运行mysqld服务的系统用户</pre> <p><span class="Apple-tab-span"> </span> 2. 预备服务脚本: 如果是用二进制文件安装,在support-files 目录下可以找到</p> <pre class="brush:bash;toolbar:false"> # cp usr/local/mysql/support_files/mysql.server /etc/rc.d/init.d/mysqld</pre> <pre class="brush:bash;toolbar:false"> 修改其中一下变量: basedir = datadir =</pre> <p><span class="Apple-tab-span"> </span> 3. 预备配置文件</p> <pre class="brush:bash;toolbar:false"> # cp /usr/local/mysql/support_files/my_huge.cnf /etc/</pre> <p><span class="Apple-tab-span"> </span>4. 配置文件默认读取顺序 </p> <p><span class="Apple-tab-span"> </span> /etc/mysql/my.cnf –> /etc/my.cnf –> ~/my.cnf </p> <p><span class="Apple-tab-span"> </span> 生效规则: </p> <p><span class="Apple-tab-span"> </span> 1) 不同变量结果取所有文件的并集 </p> <p><span class="Apple-tab-span"> </span> 2) 同一变量去最后一个配置文件的取值生效(~/my.cnf)</p> <p><strong><span style="font-size: 18px"> </span></strong><span style="font-size: 18px"><strong><span style="font-size: 16px">启动服务后删除匿名用户 </span></strong></span></p> <pre class="brush:bash;toolbar:false"> mysql> DROP user ""@"localhost"; mysql> DROP user ""@"127.0.0.1"; mysql> DROP user ""$"::1";</pre> <p><strong><span style="font-size: 18px"> </span></strong><span style="font-size: 18px"><strong><span style="font-size: 16px">设置管理员账户和密码</span></strong></span></p> <p><span class="Apple-tab-span"> </span> 第一种方式</p> <pre class="brush:bash;toolbar:false"> mysql> SET PASSWORD FOR "root"@"localhost" = PASSWORD("root")</pre> <p><span class="Apple-tab-span"> </span> 第二种方式 </p> <pre class="brush:bash;toolbar:false"> mysql> USE user; mysql> UPDATE user SET password = PASSWORD('root') WHERE User = "root"; mysql> FLUSH PRIVILEGES;</pre> <p><span class="Apple-tab-span"> </span> 第三种, 使用mysqladmin 命令 </p> <p><strong><span style="font-size: 18px"> </span></strong><span style="font-size: 18px"><strong><span style="font-size: 16px"> 添加远程管理账号;</span></strong></span> </p> <pre class="brush:bash;toolbar:false"> mysql> GRANT ALL on *.* TO 'root'@'192.168.98.129' IDENTIFIED BY "rootpass";</pre> <p><span style="font-size: 20px"><strong>二、数据库客户端连接命令 </strong></span></p> <p> <span style="font-size: 18px"><strong><span style="font-size: 16px">mysql: 常用选项如下</span></strong></span></p> <pre class="brush:bash;toolbar:false"> -u 用户名 -h 服务器主机名或IP -p 用户密码 --port 端口号 --socket 套接字文件所在目录</pre> <p> <span style="font-size: 18px"><strong><span style="font-size: 16px">常用服务器端命令以及快捷键: </span></strong></span></p> <pre class="brush:bash;toolbar:false"> CLEAR \c : 取消当前行 HELP \h: 帮助文档 SYSTEM \!: 执行系统shell命令 QUIT\q : 退出 STATUS\s : 返回状态 EGO \G : 列显示结果 SOURCE \. : 读取sql 脚本 CHARSET \C : 修改字符集</pre> <p> <span style="font-size: 18px"><strong><span style="font-size: 16px"> mysql shell 的组合快捷键 </span></strong></span></p> <pre class="brush:bash;toolbar:false"> ctrl + u: 删除光标之前的所有内容 ctrl + w: 删除光标之前的一个单词 ctrl + y: 粘贴使用上面两个命令删除的内容 ctrl + a: 跳到行首 ctrl + e: 跳到行尾</pre> <p></p> <p> <span style="font-size: 18px"><strong><span style="font-size: 16px">远程控制命令mysqladmin </span></strong></span></p> <p><span class="Apple-tab-span"> </span> mysqladmin [OPTIONS] command command </p> <pre class="brush:bash;toolbar:false"> command: create DB_NAME drop DB_NAME debug:打开调试日志并记录于error log中; status:显示简要状态信息 --sleep #: 间隔时长 --count #: 显示的批次 extended-status:输出mysqld的各状态变量及其值,相当于执行“mysql> SHOW GLOBAL STATUS” variables:输出mysqld的各服务器变量 flush-hosts:清空主机相关的缓存:DNS解析缓存,此前因为连接错误次数过多而被拒绝访问mysqld的主机列表 flush-logs:日志滚动,二进制日志和中继日志 refresh:相当于同时使用flush-logs和flush-hosts flush-privileges: :载入授权 reload:功能同flush-privileges flush-status:重置状态变量的值 flush-tables:关闭当前打开的表文件句柄 flush-threads:清空线程缓存 kill:杀死指定的线程,可以一次杀死多个线程,以逗号分隔,但不能有多余空格 password:修改当前用户的密码; ping processlist:显示mysql线程列表 shutdown:关闭mysqld进程; start-slave :启动从服务器 stop-slave: :关闭从服务器 mysql数据类型</pre> <p><span class="Apple-tab-span"></span><span style="font-size: 20px"><strong>三、数据类型相关</strong></span></p> <p><span style="font-size: 20px"><strong> <span style="font-size: 16px">数据类型</span><br /></strong></span></p> <p><span class="Apple-tab-span"> </span> 1. 数值型</p> <pre class="brush:bash;toolbar:false"> TINYINT SMALINT MEDIUMINT INT BIGINT DECIMAL FLOAT DOUBAL BIT</pre> <p> 2. 字符型 </p> <pre class="brush:bash;toolbar:false"> CHAR VARCHAR TINYTEXT MEDIUMTEXT TEXT LONGTEXT</pre> <p><span class="Apple-tab-span"> </span> 3. 二进制型 </p> <pre class="brush:bash;toolbar:false"> BINARY VARBINARY TINYBLOB BLOB MEDIUMBLOB LONGBLOB ENUM SET</pre> <p><span class="Apple-tab-span"> </span>4.日期时间型</p> <pre class="brush:bash;toolbar:false"> DATE TIME DATETIME TIMESTAMP YEAR</pre> <p><span class="Apple-tab-span"> </span><strong><span style="font-size: 16px">数据类型修饰符 </span></strong></p> <p><span class="Apple-tab-span"> </span> 1. CHAR, VARCHAR, TEXT 常用修饰 </p> <pre class="brush:bash;toolbar:false"> NOT NULL NULL DEFAULT 'strings' CHARACTER SET 'charset' mysql> SHOW VARIABLES LIKE '%CHAR%' ; ## 显示当前字符集 COLLATION '某排序规则' mysql> SHOW COLLATION ; ## 显示所有排序规则</pre> <p><span class="Apple-tab-span"> </span>2. BINARY, TEXT, BLOB 常用修饰</p> <pre class="brush:bash;toolbar:false"> NOT NULL NULL DEFAULT, 不适用与BLOB</pre> <p><span class="Apple-tab-span"> </span>3. 整型类: </p> <pre class="brush:bash;toolbar:false"> AUTO_INCREMENT UNSIGNED NOT NULL NULL DEFAULT</pre> <p> 4. 浮点类 </p> <pre class="brush:bash;toolbar:false"> UNSIGNED NOT NULL NULL DEFAULT</pre> <p><span class="Apple-tab-span"> </span>5.时间日期类 </p> <pre class="brush:bash;toolbar:false"> NOT NULL NULL DEFAULT</pre> <p><span class="Apple-tab-span"> </span> 7. ENUM , SET </p> <pre class="brush:bash;toolbar:false"> NOT NULL NULL DEFAULT</pre> <p><span class="Apple-tab-span"></span><strong><span style="font-size: 20px">四、mysql内置变量查询和修改</span></strong></p> <p><span class="Apple-tab-span"> </span> <strong>变量类型: </strong></p> <p><span class="Apple-tab-span"> </span> 1. GLOBAL: 全局变量,当前会话中修改以后不生效,只有开启新会话才生效</p> <p><span class="Apple-tab-span"> </span> 2. SESSION: 只在当前会话中生效</p> <p><span class="Apple-tab-span"> </span> 无论以上哪种,在重启服务后都消失,若要永久生效,需要写入配置文件的服务器段</p> <p> 3. STATUS VARIABLES: 状态变量,无法修改,但是记录的服务器各个统计状态参数</p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"></span> <strong>变量查看方式:</strong></p> <pre class="brush:bash;toolbar:false"> mysql> SHOW [SESSION|GLOBAL|STATUS] LIKE "%engine%"; mysql> SELECT @@GLOBAL.variable_name ; mysql> SELECT @@SESSION.variable_name ; mysql> SELECT * FROM INFORMATION_SHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE "PATTEN" ; mysql> SELECT * FROM INFORMATION_SHEMA.SESSION_VARIABLES WHERE SESSION_VARIABLES LIKE "PATTEN" ;</pre> <p></p> <p><span class="Apple-tab-span"></span> 修改变量: </p> <p><span class="Apple-tab-span"> </span> 1. 临时修改: </p> <pre class="brush:bash;toolbar:false"> mysql> SET [GLOBAL|SESSION] VARIABLE_NAME == "value" GOLBAL: 修改后需要开启新的会话才能生效 SESSION: 只在当前会话生效</pre> <p><span class="Apple-tab-span"> </span>2.永久修改: 编辑配置文件的server段</p> <p><span class="Apple-tab-span"> </span></p> <p><strong><span style="font-size: 20px">五、数据库增删查改基本操作</span></strong></p> <p> <strong><span style="font-size: 16px">数据库创建删除修改</span></strong></p> <pre class="brush:bash;toolbar:false"> mysql> CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name ; mysql> DROP {DATABASE | SCHEMA} [IF EXISTS] db_name mysql> ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']</pre> <p><span class="Apple-tab-span"> </span><strong>表创建删除</strong></p> <p> 1. 直接创建表: </p> <pre class="brush:bash;toolbar:false"> CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [create_specification] create_definition</pre> <p><span class="Apple-tab-span"> </span>2.复制已经存在表的表结构: </p> <pre class="brush:bash;toolbar:false"> CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb1_name [create_specification] LIKE old_tbl_name</pre> <p><span class="Apple-tab-span"> </span>3. 复制表数据: </p> <pre class="brush:bash;toolbar:false"> CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb1_name [create_specification] [create_definition] select_statement</pre> <p><span class="Apple-tab-span"> </span><strong>create_definition: </strong></p> <p><span class="Apple-tab-span"> </span> 1) 字符集,排序规则 </p> <pre class="brush:bash;toolbar:false"> CHARACTER SET [=] charset_name | COLLATE [=] collation_name</pre> <p> 2) column_definition: </p> <p><strong> 表修改</strong></p> <pre class="brush:bash;toolbar:false;"> UPDATE: UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1=val1 [, col_name2=val2] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]</pre> <p><span class="Apple-tab-span"> </span> UPDATE通常情况下,必须要使用WHERE子句,或者使用LIMIT限制要修改的行数;</p> <p> – -safe-updates 可以只修改符合条件的第一个</p> <p><strong> 删除行</strong></p> <pre class="brush:bash;toolbar:false"> DELETE: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]</pre> <p> <strong>清空表</strong></p> <pre class="brush:bash;toolbar:false"> TRUNCATE tb_name : 用于清空表,但是保留表结构</pre> <p><span class="Apple-tab-span"> </span><strong> MySQL SQL_MODE: SQL模式</strong></p> <pre class="brush:bash;toolbar:false"> 通过修改全局变量修改 TRADITIONAL: STRICT_TRANS_TABLES: 仅对支持事物的表使用严格模式 STRICT_ALL_TABLES: 对于所有表使用严格模式 严格模式: 如果插入的数据超出表定义的范围,对插入内容进行截短,使其满足字段定义时的属性</pre> <p><strong> 表属性修改</strong></p> <pre class="brush:bash;toolbar:false"> ALTER TABLE tb_name CHANGE [column_name] old_name new_name col_defination [FIRST AFTER column_name] MODIFY [column_name] col_name column_definition [FIRST AFTER column_name] ADD [COLUMN] col_name column_defination [FIRST AFTER col_defination] ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option</pre> <p><span class="Apple-tab-span"> </span>当然这个命令其他字命令很多</p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span><strong> mysql的基本查询:</strong> </p> <p> 1. 选择语句基本格式;</p> <p><span class="Apple-tab-span"> </span> 投影: SELECT col_name1,col_name2 …. FROM tb_name; </p> <p><span class="Apple-tab-span"> </span> 选择: SELECT * FROM tb_name WHERE clause</p> <p><span class="Apple-tab-span"> </span> WHERE clasue: </p> <p><span class="Apple-tab-span"> </span> 2. 布尔表达式: </p> <pre class="brush:bash;toolbar:false"> < > =[=] >= <= IS NULL IS NOT NULL LIKE: 支持的通配符: %(任意长度的任意字符), _(任意单个字符) RLIKE,REGEXP: 支持使用正则表达式 IN BETWEEN ... AND ..... : 在某一范围内</pre> <p><span class="Apple-tab-span"> </span><strong>组合条件测试</strong>: </p> <pre class="brush:bash;toolbar:false"> NOT ! AND && OR ||</pre> <p><span class="Apple-tab-span"> </span><strong>统计函数:</strong></p> <pre class="brush:bash;toolbar:false"> SUM(), AVG(), MAX(), MIN(), COUNT()</pre> <p> <strong>排序</strong></p> <pre class="brush:bash;toolbar:false"> ORDER BY col_name [DESC] DESC : 是否倒叙</pre> <p><span class="Apple-tab-span"> </span><strong>SELECT语句的执行流程:</strong></p> <pre class="brush:bash;toolbar:false"> FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> ORDER BY ... --> SELECT --> LIMIT</pre> <p><span class="Apple-tab-span"> </span> <strong>SELECT语句的一些选项:</strong></p> <pre class="brush:bash;toolbar:false"> DISTINCT:指定的结果相同的只显示一次; SQL_CACHE:缓存于查询缓存中; SQL_NO_CACHE:不缓存查询结果;</pre> <p></p> <p> <strong>举例导入hellodb.sql,以下操作在students表上执行</strong></p> <pre class="brush:bash;toolbar:false"> 1、以ClassID分组,显示每班的同学的人数; mysql>SELECT ClassID,COUNT(*) FROM students GROUP BY ClassID; 2、以Gender分组,显示其年龄之和; mysql>SELECT Gender,SUM(Age) FROM students GROUP BY Gender; 3、以ClassID分组,显示其平均年龄大于25的班级; mysql>SELECT ClassID FROM students GROUP BY ClassID HAVING AVG(Age) >25; 4、以Gender分组,显示各组中年龄大于25的学员的年龄之和; mysql>SELECT Gender, SUM(Age) FROM students WHERE Age > 20 Group by Gender ;</pre> <p></p> <p><span class="Apple-tab-span"> </span> <strong> mysql多表查询 </strong></p> <p><span class="Apple-tab-span"> </span> 1. 左外联结:只保留出现在左外连接运算之前(左边)的关系中的元组;</p> <pre class="brush:bash;toolbar:false"> left_tb LEFT JOIN right_tb ON 连接条件</pre> <p> 2. 右外联结:只保留出现在右外连接运算之后(右边)的关系中的元组;</p> <pre class="brush:bash;toolbar:false"> left_tb RIGHT JOIN right_tb ON 连接条件</pre> <p> 3. 别名:表别名 </p> <pre class="brush:bash;toolbar:false"> 字段别名 使用AS 关键字定义</pre> <p> 举例 :导入hellodb.sql,完成以下题目:</p> <pre class="brush:bash;toolbar:false"> 1、显示前5位同学的姓名、课程及成绩; mysql>SELECT Name,Course FROM (SELECT Name, CourseID, Score FROM ( students AS st LEFT JOIN scores AS sc ON st.StuID = sc.StuID) WHERE Score IS NOT NULL ORDER BY Score DESC ) AS ts LEFT JOIN courses AS cs ON ts.CourseID = cs.CourseID LIMIT 5 2、显示其成绩高于80的同学的名称及课程; mysql>SELECT Name,Course FROM (SELECT StuID, Course FROM scores AS sc LEFT JOIN courses AS cs ON sc.CourseID = cs.CourseID WHERE Score > 80) AS cc LEFT JOIN students as st ON cc.StuID = st.StuID ; 3、求前8位同学每位同学自己两门课的平均成绩,并按降序排列; mysql>SELECT Name,AVG(Score) FROM (SELECT * FROM students LIMIT 8) AS st LEFT JOIN scores AS sc ON st.StuID = sc.StuID GROUP BY Name ORDER BY AVG(Score) DESC ; 4、显示每门课程课程名称及学习了这门课的同学的个数; mysql>SELECT Course,COUNT(Name) FROM (SELECT Name,CourseID FROM (SELECT * FROM students LIMIT 8) AS st LEFT JOIN scores AS sc ON st.StuID = sc.StuID ) tp1 LEFT JOIN courses AS cs ON tp1.CourseID = cs.CourseID GROUP BY Name ;</pre> <p></p> <p></p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span><strong> MySQL的联合查询</strong></p> <pre class="brush:bash;toolbar:false"> SELECT clauase UNION SELECT clause UNION ... 把两个或多个查询语句的结果合并成一个结果进行输出; 按照字段列拼接</pre> <p></p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span><strong> MySQL视图: </strong>储下来的SELECT语句;</p> <pre class="brush:bash;toolbar:false"> CREATE VIEW view_name [(column_list)] AS select_statement 看起来像一个表,不过使用 SHOW TABLE STATUS 查看表属性时所有都为空</pre> <p><strong><span style="font-size: 20px">六、MySQL·的锁 </span></strong></p> <p> <strong>什么是锁</strong></p> <p> 1. 锁主要实现多用户访问相同资源时,实现并发访问控制。例如两个用户同时修改同一表等</p> <p><span class="Apple-tab-span"> </span> 2. 执行操作时施加的锁模式</p> <p><span class="Apple-tab-span"> </span> 读锁:共享锁,可同时实加操作</p> <p><span class="Apple-tab-span"> </span> 防止其他人修改正在查询的数据</p> <p><span class="Apple-tab-span"> </span> 写锁:独占锁,排它锁</p> <p><span class="Apple-tab-span"> </span> 如果一个用户在执行写操作,则其他读写都需等待</p> <p><span class="Apple-tab-span"> </span><strong> 锁粒度:</strong></p> <p><span class="Apple-tab-span"> </span> 1. 表锁:table lock</p> <p><span class="Apple-tab-span"></span> 锁定了整张表</p> <p><span class="Apple-tab-span"></span> 2.行锁:row lock</p> <p><span class="Apple-tab-span"> </span> 锁定了需要的行</p> <p> 粒度越小,开销越大,但并发性越好;</p> <p><span class="Apple-tab-span"> </span> 粒度越大,开销越小,但并发性越差;</p> <p><span class="Apple-tab-span"> </span><strong> 锁的实现位置:</strong></p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"></span> 1.MySQL锁:可以使用显式锁</p> <p><span class="Apple-tab-span"></span> 2.存储引擎锁:自动进行的(隐式锁);</p> <p></p> <p><span class="Apple-tab-span"> </span> <strong>显式锁(表级锁):</strong></p> <p><span class="Apple-tab-span"> </span> 1. 手动施加锁</p> <pre class="brush:bash;toolbar:false"> LOCK TABLES tbl_name lock_type [, tbl_name lock_type] ... lock_type锁类型:READ|WRITE</pre> <p><span class="Apple-tab-span"> </span> 2. 取消枷锁UNLOCK TABLES</p> <p><strong> 显示行级锁</strong></p> <p> 1. InnoDB存储引擎也支持另外一种显式锁(锁定挑选出的部分行,行级锁 ):</p> <pre class="brush:bash;toolbar:false"> SELECT ... LOCK IN SHARE MODE; SELECT ... FOR UPDATE;</pre> <p> 2. 只是在这个SELECT语句执行过程中施加所, 当这个语句执行结束后,释放所</p> <p><strong><span style="font-size: 20px">七、transaction 事务的支持:</span></strong><strong><span style="font-size: 20px"> </span></strong></p> <p> <strong>事务定义:</strong></p> <p> Mysql所支持的事务: 事务简单来说,是一组Mysql*查询语句*的语句,要么全部执行,要么全部不执行。把多个查询语句当做一个工作单元。 </p> <p> 事务所满足的ACID测试: </p> <p><span class="Apple-tab-span"> </span> 1)Atomicity: 一个事物是不可分割的, 包含的语句要么同时执行,要么同时不执行</p> <p><span class="Apple-tab-span"> </span> 2)Consistency: 数据库总是,从一个一致性状态到另一个一致性状态</p> <p><span class="Apple-tab-span"></span> 3)Isolation: 一个事物所做的操作,在提交之前是不可见的。 </p> <p><span class="Apple-tab-span"></span> 4)Durability: 一旦事物提交了,其所作的修改就将永远保存在数据库中而永久有效, 不会因为其他操作产生数据丢失,即便是数据库崩溃。 </p> <p><span class="Apple-tab-span"> </span>从内存同步硬盘的时间差还是有可能丢失的。 </p> <p><span class="Apple-tab-span"> </span> 事物主要保证数据安全性,数据安全性越高并发性越差。主要体现在隔离性。 </p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span><strong> mysql的隔离级别:</strong>修改tx_isolation来调整隔离级别</p> <p><span class="Apple-tab-span"></span> 1. READ-UNCOMMITTED: 读未提交,脏读, 不可重复读, 幻读。 在同一事物中,可以读到未提交的数据修改。 </p> <p><span class="Apple-tab-span"></span> 2.READ-COMMITTED: 在一个事物中, 不能读到尚未提交的事物。 但是当另一个事物提交修改后,即便在同一事物中,依然可以读到其他事物提交后的修改。这就是不可重复读</p> <p><span class="Apple-tab-span"></span> 3.REPEATABLE-READ: 在同一事物中,即便另一事物调提交了对当前数据的修改, 读到的数据是相同的。 但开启新的事物时,会发现数据已经被修改, 这个是所谓的幻读。 </p> <p><span class="Apple-tab-span"></span> 4.SERIALIZABLE : 串行化,事物和事物之间严格隔离, 当一个事物在对某一表操作时,另一事物对此表无论读写都将阻塞,直到前一事物操作完成。 </p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span><strong> MVCC:</strong>多版本并发控制</p> <p><span class="Apple-tab-span"> </span> 1. 每个事务启动时,InnoDB为会每个启动的事务提供一个当下时刻的快照;</p> <p><span class="Apple-tab-span"> </span> 2. 为了实现此功能,InnoDB会为每个表提供两隐藏的字段,一个用于保存行的创建时间,一个用于保存行的失效时间;里面存储的是系统版本号;(system version number)</p> <p><span class="Apple-tab-span"> </span> 3. MVCC只在两个隔离级别下有效:READ COMMITTED和REPEATABLE READ<span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span> <strong>跟事务相关的常用命令</strong></p> <pre class="brush:bash;toolbar:false"> mysql> START TRANSACTION mysql> COMMIT mysql> ROLLBACK mysql> SAVEPOINT identifier mysql> ROLLBACK [WORK] TO [SAVEPOINT] identifier 如果没有显式启动事务,每个语句都会当作一个独立的事务,其执行完成后会被自动提交; mysql> SELECT @@global.autocommit; mysql> SET GLOBAL autocommit = 0; 关闭自动提交,请记得手动启动事务,手动进行提交;</pre> <p><span class="Apple-tab-span"> </span><strong> 查看MySQL的事务隔离级别</strong></p> <pre class="brush:bash;toolbar:false"> mysql> SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; mysql> SELECT @@global.tx_isolation;</pre> <p><span class="Apple-tab-span"></span><strong><span style="font-size: 20px">八、MySQL存储引擎 </span></strong></p> <p><span class="Apple-tab-span"> </span> <strong>查看MySQL所支持的存储引擎和表使用引擎</strong></p> <p><strong> </strong> 1. 查询所支持的引擎<strong><br /></strong></p> <pre class="brush:bash;toolbar:false"> mysql> SHOW ENGINES ; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</pre> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span> 2. 查看表属性</p> <pre class="brush:bash;toolbar:false"> mysql> SHOW TABLE STATUS IN db_name [LIKE pattern] [WHERE clause]; mysql> SHOW TABLE STATUS IN hellodb LIKE "stu%" \G;</pre> <pre class="brush:bash;toolbar:false"> Name: students Engine: InnoDB Version: 10 Row_format: Compact Rows: 25 Avg_row_length: 655 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 26 Create_time: 2015-11-23 16:06:55 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment:</pre> <pre class="brush:bash;toolbar:false"> 各项解释: Name: 表名 Engine: 存储引擎 Version: 版本(表的当前版本) Row_format: 行格式,创建表的命令中可以定义 {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} Rows: 表中的行数 Avg_row_length: 平均每行所包含的字节数; Data_length: 表中数据总体大小,单位是字节 Max_data_length: 表能够占用的最大空间,单位为字节,0表示没有上限。 Index_length: 索引的大小,单位为字节 Data_free: 对于MyISAM表,表示已经分配但尚未使用的空间,其中包含此前删除行之后腾出来的空间 Auto_increment: 下一个AUTO_INCREMENT的值; Create_time: 表的创建时间; Update_time:表数据的最近一次的修改时间; Check_time:使用CHECK TABLE或myisamchk最近一次检测表的时间; Collation: 排序规则 Checksum: 如果启用,则为表的checksum; Create_options: 创建表时指定使用的其它选项; Comment: 表的注释信息</pre> <p></p> <p><span class="Apple-tab-span"> </span><strong> Innodb存储引擎,所产生的文件格式:</strong> </p> <p><span class="Apple-tab-span"> </span> 1.参数innodb_file_per_table=OFF时: </p> <p><span class="Apple-tab-span"> </span> 每张表具有单独的表结构文件tb_name.frm </p> <p><span class="Apple-tab-span"> </span> 数据内容使用共享表空间文件, ibdata# </p> <p><span class="Apple-tab-span"> </span> 2.参数innodb_file_per_table=ON时候:</p> <p><span class="Apple-tab-span"> </span> 每张表具有两个独立文件</p> <p><span class="Apple-tab-span"> </span> tb.name.frm 表空间文件</p> <p><span class="Apple-tab-span"> </span> tb_name.ibd </p> <p><span class="Apple-tab-span"> </span> 表空间文件(table space): 由innodb管理的特有格式的数据文件,内部存储索引和数据,支持聚簇索引</p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span> <strong>MyISAM存储引擎产生的文件: </strong>每个表都在数据库目录下存储三个文件:</p> <p><span class="Apple-tab-span"> </span> 1. tb_name.frm : 表结构</p> <p><span class="Apple-tab-span"> </span> 2. tb_name.MYD : 数据 </p> <p><span class="Apple-tab-span"> </span> 3. tb_name.MYI : 索引</p> <p><span class="Apple-tab-span"> </span> 通过修改default_storage_engine来修改默认存储引擎,需要写在配置文件中。</p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span> <strong>各引擎特性</strong></p> <p><span class="Apple-tab-span"> </span> 1. Innodb: </p> <pre class="brush:bash;toolbar:false;"> 事物: 事务日志 外键: MVCC: 多版本并发机制,主要是用于支持事务 聚簇索引: 索引和表存在一起,创建表时候必须创建一个聚簇索,索引怎么排序,数据也会跟着怎么排序 聚簇索引只能有一个 聚簇索引之外的其他索引,通常称为辅助索引 所有的辅助索引是指向聚簇索引的。而非指向元数据。 通常使用主键用于聚簇索引 无论据簇索引还是辅助索引都是B+树索引 行级锁: 间隙锁,用来格隔离行 支持辅助索引: 自适应的hash索引: 支持热备份:</pre> <p> 2 MyISAM: </p> <pre class="brush:bash;toolbar:false"> 全文索引 支持表压缩,但是压缩后不能修改,用于制作数据仓库,可节约空间提高性能 空间索引 表级锁 延迟更新索引: 每当数据更新时,不需要立即更新索引,以降低I/O压力 不支持事务,外键和行级锁 崩溃无法安全可靠的恢复数据。 适用场景: 只读数据, 较小的表, 崩溃后可以忍受数据恢复时间和数据丢失。</pre> <p><span class="Apple-tab-span"></span> 3.ARCHIVE:</p> <pre class="brush:bash;toolbar:false"> 仅支持INSERT和SELECT,支持很好压缩功能; 适用于存储日志信息,或其它按时间序列实现的数据采集类的应用; 不支持事务,不能很好的支持索引;</pre> <p> <span class="Apple-tab-span"> </span>4. <span class="Apple-tab-span"></span>CSV:</p> <pre class="brush:bash;toolbar:false"> 将数据存储为CSV格式;不支持索引;仅适用于数据交换场景,另外貌似精度很难保持;</pre> <p> <span class="Apple-tab-span"> </span>5. BLACKHOLE:</p> <pre class="brush:bash;toolbar:false"> 没有存储机制,任何发往此引擎的数据都会丢弃;其会记录二进制日志,因此,常用于多级复制架构中作中转服务器;</pre> <p> <span class="Apple-tab-span"> </span>6. <span class="Apple-tab-span"></span>MEMORY:</p> <pre class="brush:bash;toolbar:false"> 保存数据在内存中,内存表;常用于保存中间数据,如周期性的聚合数据等;也用于实现临时表</pre> <pre class="brush:bash;toolbar:false"> 支持hash索引,使用表级锁,不支持BLOB和TEXT数据类型</pre> <p> <span class="Apple-tab-span"> </span>7.MRG_MYISAM:</p> <pre class="brush:bash;toolbar:false"> 是MYISAM的一个变种,能够将多个MyISAM表合并成一个虚表;</pre> <p> <span class="Apple-tab-span"> </span>8.<span class="Apple-tab-span"></span>NDB:</p> <pre class="brush:bash;toolbar:false"> 是MySQL CLUSTER中专用的存储引擎</pre> <p><span class="Apple-tab-span"> </span></p> <p><strong> 第三方的存储引擎:</strong></p> <p> <span class="Apple-tab-span"> </span>1. OLTP类:</p> <pre class="brush:bash;toolbar:false"> XtraDB: 增强的InnoDB,由Percona提供; 编译安装时,下载XtraDB的源码替换MySQL存储引擎中的InnoDB的源码 PBXT: MariaDB自带此存储引擎 支持引擎级别的复制、外键约束,对SSD磁盘提供适当支持; 支持事务、MVCC</pre> <p> 2. <span class="Apple-tab-span"></span>TokuDB: 使用Fractal Trees索引,没有碎片问题,性能与换从无关,适用存储大数据,拥有很压缩比;已经被引入MariaDB;</p> <pre class="brush:bash;toolbar:false"> 列式存储引擎: Infobright: 目前较有名的列式引擎,适用于海量数据存储场景,如PB级别,专为数据分析和数据仓库设计; 如果用于MySQL, 需要对Mysql做定制</pre> <p><strong> 开源社区存储引擎:</strong></p> <p> <span class="Apple-tab-span"> </span>1. Aria:前身为Maria,可理解为增强版的MyISAM(支持崩溃后安全恢复,支持数据缓存)</p> <p> <span class="Apple-tab-span"> </span>2.Groona:全文索引引擎,Mroonga是基于Groona的二次开发版,适用于搜索引擎</p> <p> <span class="Apple-tab-span"> </span>3.OQGraph: 由Open Query研发,支持图结构的存储引擎</p> <p> <span class="Apple-tab-span"> </span>4.SphinxSE: 为Sphinx全文搜索服务器提供了SQL接口</p> <p> <span class="Apple-tab-span"> </span>5.Spider: 能数据切分成不同分片,比较高效透明地实现了分片(shared),并支持在分片上支持并行查询;<span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span> <strong> 如何选择数据存储引擎:</strong> </p> <p> <span class="Apple-tab-span"> </span>1.是否需要事务</p> <p> <span class="Apple-tab-span"> </span>2.备份的类型的支持</p> <p> <span class="Apple-tab-span"> </span>3.崩溃后的恢复</p> <p> <span class="Apple-tab-span"> </span>4.特有的特性</p> <p><span class="Apple-tab-span"> </span></p> <p></p> <p><strong><span style="font-size: 20px">九、MySQL的用户以及权限管理:</span></strong> </p> <p><span class="Apple-tab-span"> </span> <strong>mysql用户管理: </strong></p> <p><span class="Apple-tab-span"></span> 1.用户格式: </p> <p><span class="Apple-tab-span"> </span> username@{host_IP|host_name} </p> <p><span class="Apple-tab-span"> </span> {host_IP|host_name} : 需要登录mysql服务器的主机IP </p> <p><span class="Apple-tab-span"></span> 2.mysql用户管理的相关命令: </p> <pre class="brush:bash;toolbar:false"> mysql> CREATE USER user_name IDENTIFIED BY {'auth_string' | PASSWORD 'hash_string'} mysql> DROP USER user_name mysql> RENAME USER old_user TO new_user mysql> SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password');</pre> <p><span class="Apple-tab-span"> </span><strong>mysql的权限控制:</strong></p> <p><span class="Apple-tab-span"> </span> 1. 权限管理命令: </p> <p><span class="Apple-tab-span"> </span> 权限授予: </p> <pre class="brush:bash;toolbar:false"> GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ... [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] [WITH {GRANT OPTION | resource_option} ...]</pre> <pre class="brush:bash;toolbar:false"> GRANT PROXY ON user_specification TO user_specification [, user_specification] ... [WITH GRANT OPTION]</pre> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span> 权限回收:<span class="Apple-tab-span"> </span></p> <pre class="brush:bash;toolbar:false"> REVOKE priv_type [(column_list)][, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ... REVOKE PROXY ON user FROM user [, user] ...</pre> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span> 查看用户能够使用的权限:</p> <pre class="brush:bash;toolbar:false"> mysql>SHOW GRANTS FOR username@'hostname'</pre> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span> <strong>作用对象类型(priv_type):</strong></p> <p><span class="Apple-tab-span"> </span> 1. TABLE(默认)</p> <p><span class="Apple-tab-span"></span> 2.FUNCTION</p> <p><span class="Apple-tab-span"></span> 3.PROCEDURE</p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span><strong> 权限目标级别(priv_level)</strong></p> <p><span class="Apple-tab-span"> </span> 1. *<span class="Apple-tab-span"> </span>所有: 库,表,函数</p> <p><span class="Apple-tab-span"></span> 2. *.*<span class="Apple-tab-span"> </span>所有库的所有[TABLE|FUNCTION|PROCEDURE]</p> <p><span class="Apple-tab-span"></span> 3. db_name.*<span class="Apple-tab-span"> </span>db_name库的所有</p> <p><span class="Apple-tab-span"></span> 4. db_name.tbl_name</p> <p><span class="Apple-tab-span"></span> 5. tbl_name</p> <p><span class="Apple-tab-span"></span> 6. db_name.routine_name</p> <p><span class="Apple-tab-span"> </span> <strong>WITH GRANT OPTION:<span class="Apple-tab-span"> </span>权限授予选项</strong></p> <p><span class="Apple-tab-span"> </span> 1. MAX_QUERIES_PER_HOUR count</p> <p><span class="Apple-tab-span"></span> 2. MAX_UPDATES_PER_HOUR count</p> <p><span class="Apple-tab-span"></span> 3.MAX_CONNECTIONS_PER_HOUR count</p> <p><span class="Apple-tab-span"></span> 4.MAX_USER_CONNECTIONS count</p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span> <strong>权限类型(priv_type): </strong></p> <p><span class="Apple-tab-span"> </span> 1. 管理类权限:</p> <p><span class="Apple-tab-span"> </span></p> <pre class="brush:bash;toolbar:false"> CREATE TEMPORARY TABLES:使用或者创建临时表 CREATE USER:创建,删除,重命名用户 FILE:在服务器上读或者写, 包括备份,以及source 文件 LOCK TABLES:是否可以锁表 PROCESS:是否能执行SHOW PROCESSLIST 命令查看mysql内部运行的线程 RELOAD:是否能使用FLUSH 和 RESET 命令 REPLICATION SLAVE:是否可以查询主服务器有哪些从服务器 REPLICATION CLIENT:是否有权限成为从服务器 SHOW DATABASES:是否可以查询服务器中有哪些数据库 SHUTDOWN:关闭 SUPER:其它</pre> <p><span class="Apple-tab-span"> </span>2.库级别和表级别:</p> <pre class="brush:bash;toolbar:false"> ALTER:是否可以执行ALTER TABLE 命令 ALTER ROUTINE:修改存储历程,包括存储函数 CREATE:创建表和库 CREATE ROUTINE:创建历程和函数 CREATE VIEW:创建视图 DROP:删除表和库 EXECUTE:执行存储历程 GRNAT OPTION:把自己的权限转赠给他人 INDEX:建立索引 SHOW VIEW:查看视图</pre> <p></p> <p><span class="Apple-tab-span"></span> 3.数据操作(表级别):</p> <pre class="brush:bash;toolbar:false"> SELECT:查询 INSERT:增加 UPDATE:修改 DELETE:删除 字段级别: SELECT(col1,...) UPDATE(col1,...) INSERT(col1,...)</pre> <p></p> <p><span class="Apple-tab-span"></span> 4.所有权限:</p> <pre class="brush:bash;toolbar:false"> ALL [PRIVILEGES]</pre> <p><strong><span class="Apple-tab-span"> </span>ssl选项(ssl_option):</strong> </p> <pre class="brush:bash;toolbar:false"> SSL X509:证书格式 CIPHER 'cipher'加密方式 ISSUER 'issuer'证书颁发者 SUBJECT 'subject'拒绝某证书</pre> <p><strong>几个存储用户权限信息的表,全都存储在mysql库中</strong></p> <pre class="brush:bash;toolbar:false"> db: 库级别权限; host: 主机级别权限,已废弃 tables_priv: 表级别权限 colomns_priv:列级别的权限 procs_priv:存储过程和存储函数相关的权限 proxies_priv:代理用户权限</pre> <p><strong><span class="Apple-tab-span"> </span>事例:</strong><span class="Apple-tab-span"> </span></p> <pre class="brush:bash;toolbar:false"> 1. 授予testuser能够通过192.168.98.0/24网络内的任意主机访问当前mysql服务器的权限; mysql> GRANT SELECT ON TABLE *.* TO 'testuser'@'192.168.98.%' IDENTIFIED BY 'testuser'; 2. 让此用户能够创建及删除testdb数据库,及库中的表; GRANT CREATE,DROP ON TABLE testdb.* TO 'testuser'@'192.168.98.%' ; 3. 让此用户能够在testdb库上执行创建和删除索引; GRANT INDEX ON TABLE testdb.* TO 'testuser'@'192.168.98.%' ; 4. 让此用户能够在testdb.t2表上查询id和name字段,并允许其将此权限转授予其他用户; GRANT GRANT OPTION,SELECT(id,name) ON TABLE testdb.t2 TO 'testuser'@'192.168.98.%' ; 5、让此用户能够在testdb库中的t1表中执行查询、删除、更新和插入操作; GRANT SELECT,DELETE,UPDATE,INSERT ON TABLE testdb.tb1 TO 'testuser'@'192.168.98.%';</pre> <p><strong><span style="font-size: 20px">十、MySQL查询缓存</span></strong></p> <p><span class="Apple-tab-span"> </span> 用于保存MySQL查询语句返回的完整结果。被命中时,MySQL会立即返回结果,省去解析、优化和执行等阶段。</p> <p><span class="Apple-tab-span"> </span> <strong>如何检查缓存</strong></p> <p><span class="Apple-tab-span"> </span> 1. MySQL保存结果于缓存中:</p> <p><span class="Apple-tab-span"> </span> 2. 把SELECT语句本身做hash计算,计算的结果作为key,查询结果作为value</p> <p><span class="Apple-tab-span"> </span><strong> 什么样的语句不会被缓存?</strong></p> <p><span class="Apple-tab-span"> </span> 查询语句中有一些不确定数据时,不会缓存:例如NOW(), CURRENT_TIME();一般来说,如果查询中包含用户自定义函数、存储函数、用户变量、临时表、mysql库中系统表、或者任何包含权限的表,一般都不会缓存;</p> <p><span class="Apple-tab-span"> </span><strong> 缓存会带来额外开销</strong>:</p> <p><span class="Apple-tab-span"> </span> 1、每个查询都得先检查是否命中;</p> <p><span class="Apple-tab-span"> </span> 2、查询结果要先缓存;</p> <p> <strong>缓存相关的服务器变量</strong></p> <pre class="brush:bash;toolbar:false"> MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'query_cache%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_strip_comments | OFF | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ query_cache_type: 查询缓存类型;是否开启缓存功能,开启方式有三种{ON|OFF|DEMAND}; DEMAND:意味着SELECT语句明确使用 SQL_CACHE 选项时才会缓存; query_cache_size: 总空间,单位为字节,大小必须是1024的整数倍。MySQL启动时,会一次分配并立即初始化这里指定大小的内存空间;这意味着,如果修改此大小,会清空缓存并重新初始化的。 query_cache_min_res_unit: 存储缓存的最小内存块;(query_cache_size - Qcache_free_memory)/Qcache_queries_in_cache能够获得一个理想的值。 query_cache_limit: 单个缓存对象的最大值,超出时则不预缓存;手动使用SQL_NO_CACHE可以人为地避免尝试缓存返回结果超出此参数限定值的语句。 query_cache_wlock_invalidate: 如果某个表被其它用户连接锁住了,是否仍然从缓存中返回结果。OFF表示返回。</pre> <p><strong> 如何判断命令率(缓存相关的状态变量):</strong></p> <pre class="brush:bash;toolbar:false"> MariaDB [hellodb]> SHOW GLOBAL STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16757008 | | Qcache_hits | 4 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 18 | | Qcache_queries_in_cache | 2 | | Qcache_total_blocks | 6 | +-------------------------+----------+ Qcache_hits: 命中次数 Qcache_free_memory: 剩余缓存空间,尚未划分成块的空间 Qcache_free_blocks: 空闲的块数,划分完成但还没使用的空间 Qcache_total_blocks: 总块数 Qcache_queries_in_cache: 在缓存中,缓存插入的次数。 Qcache_not_cached: 没有缓存的 Qcache_lowmem_prunes: 因为内存太少而修剪内存的次数。 碎片整理:FLUSH QUERY_CACHE 清空缓存:RESET QUERY_CACHE</pre> <p><span class="Apple-tab-span"> </span><strong> 计算命中率:</strong></p> <pre class="brush:bash;toolbar:false"> MariaDB [hellodb]> SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits' OR Variable_name='Com_select'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select | 24 | | Qcache_hits | 4 | +---------------+-------+ Com_select: 非缓存查询次数 Qcache_hits: 缓存命中次数 Qcache_hits/(Com_select+Qcache_hits) 也应该参考另外一个指标:命中和写入的比率,即Qcache_hits/Qcache_inserts的值,此比值如果能大于3:1,则表明缓存也是有效的。能达到10:1,为比较理想的情况。</pre> <p><span class="Apple-tab-span"> </span><strong>缓存优化使用思路:</strong></p> <p><span class="Apple-tab-span"> </span> 1、批量写入而非多次单个写入;</p> <p><span class="Apple-tab-span"> </span> 2、缓存空间不宜过大,因为大量缓存同时失效时会导致服务器假死;</p> <p><span class="Apple-tab-span"> </span> 3、必要时,使用SQL_CACHE和SQL_N0_CACHE手动控制缓存;</p> <p><span class="Apple-tab-span"> </span> 4、对写密集型的应用场景来说,禁用缓存反而能提高性能;</p> <p><span style="font-size: 20px">十一、MySQL日志:</span></p> <p><span class="Apple-tab-span"></span><strong> 日志分类 </strong></p> <p> 1.查询日志: 繁忙的服务器不建议记录查询日志</p> <p><span class="Apple-tab-span"> </span> 2.慢查询日志:查询执行时长超过指定时长的查询,即为慢查询</p> <p><span class="Apple-tab-span"></span> 3.错误日志: </p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"></span> 4.事务日志:ib_logfile0 ib_logfile1</p> <p><span class="Apple-tab-span"> </span> 随机I/O转换为顺序I/O从而保证ACID的持久性。 只要事务提交,马上写入事务日志中。 </p> <p><span class="Apple-tab-span"> </span> 事务日志有可能承担读操作,innodb_buffer可能会把装不下的内容放入事务日志</p> <p><span class="Apple-tab-span"></span> 5.日志文件组:</p> <p><span class="Apple-tab-span"> </span> <strong>特性: </strong></p> <p><span class="Apple-tab-span"></span> 1)至少应该有两个日志文件。 </p> <p><span class="Apple-tab-span"></span> 2)第一个满了以后,启动第二个。 第一个日志文件开始向磁盘同步。 </p> <p><span class="Apple-tab-span"></span> 3)如果事务刚写到事务日志中数据库崩溃,在重启后,会把事务日志继续同步到数据文件,从而达到一致性。 </p> <p> 4)但如果事务日志所在的硬盘损坏导致崩溃,则无法恢复。所以要保证事务日志所在的存储足够可靠</p> <p><span class="Apple-tab-span"></span> 5)为了分摊事务日志I/O和同步数据文件的I/O, 数据文件和事务日志要分开存放 </p> <p><span class="Apple-tab-span"> </span> <strong>相关参数:</strong> </p> <p><span class="Apple-tab-span"> </span> innodb_log_file_size <span class="Apple-tab-span"> </span>事务日志大小 </p> <p><span class="Apple-tab-span"> </span> innodb_log_files_in_group<span class="Apple-tab-span"> </span>事务日志组个数</p> <p><span class="Apple-tab-span"> </span> innodb_log_group_home_dir<span class="Apple-tab-span"> </span>事务日志所在位置,"./"是安装目录</p> <p><span class="Apple-tab-span"> </span> innodb_flush_log_at_trx_commit 是否事务提交后马上同步日志</p> <p><span class="Apple-tab-span"> </span></p> <p><span class="Apple-tab-span"> </span> 可以放在固态硬盘上从而提高性能。</p> <p><span class="Apple-tab-span"> </span> <strong>注意:</strong>尽可能使用小事务以提升事务引擎的性能。以保证尽量在回滚时,降低硬盘I/O开销</p> <p><span class="Apple-tab-span"> </span></p> <p></p> <p><span class="Apple-tab-span"> </span> <strong>查询查询日志</strong>: 繁忙的服务器不建议开启</p> <pre class="brush:bash;toolbar:false"> log={ON|OFF}:是否记录所有语句的日志信息于一般查询日志文件(general_log),5.6以后弃用,重复选项; log_output={TABLE|FILE|NONE} TABLE和FILE可以同时出现,用逗号分隔即可; TABLE : 记录到表中,在mysql库中的general_log 表 FILE : 记录到文件中,在general_log_file 选项指定日志存放位置 general_log:是否启用查询日志; general_log_file:定义一般查询日志保存的文件</pre> <p></p> <p><span class="Apple-tab-span"> </span><strong> 慢查询日志:</strong> 用于评估系统性能,procona有工具用来分析慢查寻日志</p> <pre class="brush:bash;toolbar:false"> long_query_time: 10.000000 定义多长时间算是慢,单位为秒 slow_query_log={ON|OFF} 设定各用户级别是否启用慢查询日志;它的输出位置也取决log_output={TABLE|FILE|NONE}; slow_query_log_file=www-slow.log 定义日志文件路径及名称; log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 哪些查询不记录日志 log_slow_queries=ON 只有管理员才能修改,全局是否记录慢查日志 log_slow_rate_limit=1 记录速率 log_slow_verbosity 是否记录详细的慢查日志</pre> <p><strong> 错误日志: </strong></p> <p><span class="Apple-tab-span"></span> 1.服务器启动和关闭过程中的信息;</p> <p><span class="Apple-tab-span"></span> 2. 服务器运行过程中的错误信息;</p> <p><span class="Apple-tab-span"></span> 3. 事件调度器运行一个事件时产生的信息;</p> <p><span class="Apple-tab-span"></span> 4. 在复制架构中的从服务器上启动从服务器线程时产生的信息;</p> <pre class="brush:bash;toolbar:false"> log_error = /path/to/error_log_file 直接指向日志文件 log_warnings = {1|0} 是否记录警告信息于错误日志中;</pre> <p> <strong>中继日志:</strong></p> <p> 从主服务器复制了来的二进制日志</p> <p><span class="Apple-tab-span"> </span><strong> 二进制日志:有以下用途</strong></p> <p> 1.引起mysql服务器改变的任何操作。</p> <p><span class="Apple-tab-span"></span> 2.复制功能依赖于此日志。</p> <p><span class="Apple-tab-span"></span> 3.从服务器通过复制主服务器的二进制日志完成主从复制,在执行之前保存于中继日志中。 </p> <p><span class="Apple-tab-span"></span> 4.从服务器通常可以关闭二进制日志以提升性能。</p> <p><span class="Apple-tab-span"></span> 5.主要用于时间点恢复</p> <p><span class="Apple-tab-span"></span> 5.数据库复制</p> <p></p> <p>总结: 这部分内容总结,基本上来源于听课的视频和笔记。比较琐碎,读起来估计也没有美感可言,希望读者见谅。 后面会继续总结备份,主从复制,高可用等内容</p> 最后修改:2021 年 12 月 10 日 10 : 53 AM © 允许规范转载 赞赏 如果觉得我的文章对你有用,请随意赞赏 赞赏作者 支付宝微信