网站建设解决,在线照片编辑工具,wordpress系统邮箱地址,做公众号推广的公司目录
MySQL的Linux安装
存储引擎
MySQL的体系结构
存储引擎简介
存储引擎特点
InnoDB
逻辑存储结构
MyISAM
Memory
对比
存储引擎选择
索引
介绍
索引结构
BTree索引
Hash索引
索引分类
索引语法
SQL性能分析
SQL执行频率
慢查询日志
profile详情
expla…目录
MySQL的Linux安装
存储引擎
MySQL的体系结构
存储引擎简介
存储引擎特点
InnoDB
逻辑存储结构
MyISAM
Memory
对比
存储引擎选择
索引
介绍
索引结构
BTree索引
Hash索引
索引分类
索引语法
SQL性能分析
SQL执行频率
慢查询日志
profile详情
explain执行计划
索引的使用
最左前缀法则
范围查询
索引列运算
字符串加引号
模糊查询
or连接的条件
数据分布影响
SQL提示
覆盖索引 MySQL的Linux安装
在MySQL官网下载压缩包MySQL :: Download MySQL Community Server (Archived Versions)
下载完成后将压缩包上传到Linux中。我这里是下的CentOS的压缩包并且用的是FinalShell工具 可以选择压缩包直接上传。
上传完毕后新建mysql文件夹并解压到该文件夹中
mkdir mysql#切换成你的mysql版本
#-c是指定解压文件夹
tar -xvf mysql-8.0.26-1.el7.x86_64.rpm-bundle.tar -C mysql 解压完成后进入mysql目录安装mysql cd mysqlrpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpmyum install openssl-devel# 如果执行该命令报错请先执行 yum install 的命令
rpm -ivh mysql-community-devel-8.0.26-1.el7.x86_64.rpmrpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpmrpm -ivh mysql-community-server-8.0.26-1.el7.x86_64.rpm 启动MySQL systemctl start mysqld
systemctl stop mysqld
systemctl restart mysqld 启动后我们需要查看默认生成的Mysql密码是什么 grep temporary password /var/log/mysqld.log 然后登录MySQL并修改密码
需要注意的是MySQL8之后添加了密码校验器默认的密码等级是中等需要密码长度不低于8位并且需要数字与字母搭配。具体文档6.4.3.2 密码验证选项和变量_MySQL 8.0 参考手册 登录过后我们需要对密码校验器进行修改 set global validate_password.policy 0;
set global validate_password.length 6;
#修改完成后设置新密码
ALTER USER rootlocalhost IDENTIFIED BY 123456; 接下来创建一个新用户可以用于远程连接 create user root% IDENTIFIED WITH mysql_native_password BY 123456;
#分配权限
grant all on *.* to root%;
之后就可以使用Navicat连接Linux中的MySQL了。
存储引擎
MySQL的体系结构 连接层最上层是一些客户端和链接服务主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层第二层架构主要完成大多数的核心服务功能如SQL接口并完成缓存的查询SOL的分析和优化部分内置函数的执行。所有跨存储引擎的功能也在这一层实现如 过程、函数等。
引擎层存储引擎真正的负责了MySQL中数据的存储和提取服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能这样我们可以根据自己的需要来选取合适的存储引擎。
存储层主要是将数据存储在文件系统之上并完成与存储引擎的交互。
存储引擎简介
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引警是基于表的而不是基于库的所以存储引警也可被称为表类型。
在创建表的时候我们可以指定存储引擎如果不指定默认为INNODB引擎 create table 表明{ 字段1, 字段类型, …… }ENGINE 引擎名; 如果想查看当前数据库支持哪些存储引擎我们可以使用语句
show engines; 存储引擎特点
InnoDB
一种兼顾高可靠性与高性能的通用引擎在Mysql5.5之后InnoDB是MySQL的默认存储引擎。
特点是
DML操作遵循ACID模型支持事务;支持行级锁提高并发访问性能支持外键约束保证数据的完整性和正确性
存储文件
xxx.ibdxxx代表的是表名innoDB引擎的每张表都会对应这样一个表空间文件存储该表的表结构(frm、sdi)、数据和索引。该文件可以在Windows目录下C:\ProgramData\MySQL\MySQL Server 8.0\Data下找到每个文件夹对应的是一个数据库。
如果需要通过idb文件查看表结构可以通过命令
ibd2sdi 表名.ibd 逻辑存储结构 磁盘操作的最小单元为page每个page是16K。而每个Extent大小为1M。也就是说一个Extent包含64个page
MyISAM
MyISAM是MySQL早期的默认存储引擎
特点
不支持事务不支持外键支持表锁不支持行锁访问速度快
存储文件
xxx.sdi存储表结构信息xxx.MYD存储数据xxx.MYI存储索引
Memory
Memory引擎的表数据是存储在内存当中的只能将使用Memory引擎的表作为临时表或缓存使用
特点
内存存放访问速度快采用hash索引
存储文件
xxx.sdi存储表结构信息
对比 特点 InnoDB MyISAM Memory 存储限制 64TB 有 有 事务安全 支持 - - 锁机制 行锁 表锁 表锁 Btree索引 支持 支持 支持 Hash索引 - - 支持 全文索引 5.6版本后支持 支持 - 空间使用 高 低 N/A 内存使用 高 低 中等 批量插入速度 低 高 高 支持外键 支持 - -
InnoDB与MyISAM最大的区别在于InnoDB支持事务支持行锁支持外键
存储引擎选择
InnoDB是Mysql的默认存储擎支持事务、外键。如果应用对事务的完整性有比较高的要求在并发条件下要求数据的一致性数据操作除了插入和查询之外还包含很多的更新、删除操作那么InnoDB存储引擎是比较合适的选择。
MyISAM如果应用是以读操作和插入操作为主只有很少的更新和删除操作并且对事务的完整性、并发性要求不是很高那么选择这个存储引擎是非常合适的。
MEMORY将所有数据保存在内存中访问速度快通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制太大的表无法缓存在内存中而且无法保障数据的安全性。
通常来讲我们使用InnoDB就足够了MyISAM引擎用来记录日志等丢失几条消息也无所谓的信息MEMORY因为无法存储太多数据用来做缓存就可以了。但是MyISAM与MEMORY都可以被其他非关系型数据库来替代比如说Redis。
索引
介绍
索引是帮助MySQL高效获取数据的数据结构(有序)。在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用(指向)数据 这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。 比如说存在这样一张表接下来我们要找到age为45的用户在没有对age字段添加索引的清空下是通过id进行全表扫描一行一行对比age45的数据。 如果对age字段建立索引在插入时数据库会维护索引信息查找时从根节点开始查找查询速度快。
优点
提高数据库查询效率减少数据库IO成本通过索引列对数据进行排序降低数据排序的成本降低CPU消耗
缺点
索引会占用存储空间索引大大提高了查询效率同时也降低了Insert、Update、Delete效率
索引结构
MySQL的索引是在存储引擎层实现的不同的存储引擎有不同的结构主要包含以下几种 索引结构 描述 BTree索引 最常见的索引大部分引擎都支持 B 树索引 Hash索引 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效不支持范围查询 R-Tree空间索引 空间索引是MvISAM引擎的一个特殊索引类型主要用于地理空间数据类型通常使用较少 Full-text空间索引 是一种通过建立倒排索引快速匹配文档的方式。类似于Lucene,Solr,ES
引擎对索引结构的支持情况 索引 InnoDB MyISAM Memory BTree索引 支持 支持 支持 Hash索引 不支持 不支持 支持 R-Tree索引 不支持 支持 不支持 Full-text 5.6之后支持 支持 不支持
BTree索引
默认都是使用BTree索引但是MySQL又对经典的BTree做了一个优化增加了一个指向相邻叶子节点的链表指针形成了带有顺序指针的BTree提高区间访问的性能。 为什么选择BTree索引结构 相对于二叉树层级更少搜索效率高。
对于B-Tree无论是叶子节点还是非叶子节点都存储数据这样会导致一页中的存储的键值减少指针跟着减少如果需要保存大量数据B-Tree的高度会比BTree高导致性能降低。其次对BTree优化后形成一个双线链表对于范围查询更具有优势。
Hash索引
哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中 hash索引的特点 只能用于对等比较、in不支持范围查询between,等无法利用索引完成排序操作查询效率高通常只需要一次检索。
索引分类 分类 含义 特点 关键字 主键索引 针对于表中主键创建的索引 默认自动创建只能有一个 PRIMARY 唯一索引 避免同一个表中某数据列中的值重复 可以有多个 UNIQUE 常规索引 快速定位特定数据 可以有多个 全文索引 全文索引查找的是文本中的关键词而不是比较索引中的值 可以有多个 FULLTEXT
在InnoDB中根据索引的存储形式又可以分为以下两种 分类 含义 特点 聚集索引 将数据存储与索引放到了一块索引结构的叶子节点保存了行数据 必须有而且只有一个 二级索引 将数据与索引分开存储索引结构的叶子节点关联的是对应的主键 可以存在多个
聚集索引的选取规则
如果存在主键主键索引就是聚集索引。如果不存在主键将使用第一个唯一(UNIQUE)索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。 当需要对name字段进行查询时先通过二级索引查找到对应的name键值然后获取对应的id值然后拿着id值取聚集索引中查找行信息该操作叫做回表查询 InnoDB的主键索引BTree高度为多少 BTree的高度取决于主键数据类型首先我们知道BTree的非叶子节点是存储的只有键值与InnoDB的指针而一个区块只能存放16K的内容转化为字节为16*1024个字节。
首先InnoDB的指针占用6个字节假设主键数据类型是bigint需要占用8个字节的大小在高度为2的情况下计算公式为 n*8(n1)*6 16*1024 计算出n约等于1170。
如果一行数据大小为1k。那么在高度为2的情况下可以存储 1171*16 18736 数据。
在高度为三的情况可以存储1171*1171*16 21939856 条数据
索引语法
创建索引
CREATE [ UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,…);
字段可以同时选取多个这被称为联合索引或组合索引需要注意的是创建索引时字段名的顺序会影响查找的效率这个我们后面 查看索引 SHOW INDEX FROM table_name; 删除索引 DROP INDEX index_name ON table_name;
SQL性能分析
SQL执行频率
在对SQL进行优化的时候我们需要知道该数据库主要是哪些语句执行次数多将优化重心就放在执行次数多的语句当中查询SQL执行次数语句如下
SHOW [GLOBAL|SESSION] STATUS LIKE Com_______; 慢查询日志
慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有SQL语句的日志。MySQL的慢查询日志默认关闭需要在MySQL的配置文件etc/my.cnf中配置如下信息
# 开启MySQL慢日志开关
slow_query_log 1
# 设置慢日志的超时时间为2s
long_query_time 2 慢日志记录位置为/var/lib/mysql/localhost-slow.log profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have profiling参数能够看到当前MySQL是否支持profile操作:
SELECT have_profiling;
默认profiling是关闭的可以通过set语句在session/global级别开启profiling:
SET profiling 1; 当开启之后执行 # 会查看到每一条SQL的执行时间
show profiles;
# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的SQL语句CPU使用情况
show profile cpu for query query_id; explain执行计划
EXPLAIN或者DESC命令可以获取MySQL如何执行SELECT语句的信息包括在SELECT语句执行过程中如何连接和连接顺序。
语法
# 在select语句前直接加EXPLAIN或是DESC
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件; 字段解释 id select查询的序列号表示查询中执行select子句或是操作表的顺序执行顺序从上到下id相同从上到下id不同值越大越先执行 select_type 表示 SELECT 的类型常见的取值有 SIMPLE(简单表即不使用表连接或者子查询)、PRIMARY(主查询即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等 type 表示连接类型性能由好到差的连接类型为NULL、system、const、eg_ref、ref、range、index、all。
NULL不查询表的时候性能为NULL项目中不可能优化到NULLsystem查询系统表的时候为systemconst查询主键或唯一索引时为constref查询非唯一性的索引为refindex使用了索引但还是遍历了整个索引树all全表扫描 possible_key 显示可能应用在这张表上的索引一个或多个 key 表示实际使用的索引如果为NULL则没有使用索引。 key_len 表示索引中使用的字节数该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下长度越短越好 rows MySQL认为必须要执行查询的行数在innodb引擎的表中是一个估计值可能并不总是准确的。 filtered 表示返回结果的行数占需读取行数的百分比filtered 的值越大越好 extra 额外信息
索引的使用
最左前缀法则
如果索引了多列(联合索引)要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将部分失效(后面的字段索引失效)[索引字段存在即可在语句中的位置顺序不重要]
比如说将一张表的nameagesex字段创建一个联合索引那么在查找时name字段必须存在查询条件中如果不包含name字段只查询age与sex字段那么将会全表扫描。如果查询name与sex字段那么只有name字段会走索引而sex字段的索引失效因为跳过了age字段。
范围查询
联合索引中出现范围查询范围查询右侧的索引失效。
比如说在查询条件中加入的age18的条件那么sex的索引将会失效。
解决方法在业务允许的情况下能够使用或的情况不要使用和。
索引列运算
不要在索引列上进行运算操作索引将失效。
字符串加引号
如果字符串类型的字段在使用时不添加引号那么索引失效。
模糊查询
如果仅仅是尾部模糊匹配索引不会失效如果是头部模糊匹配那么索引失效。
or连接的条件
用or分隔开的条件如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到。
解决方法对没有索引的字符也添加索引。
数据分布影响
如果MySQL评估使用索引比全表更慢则不使用索引。
比如说age18如果表中大多数数据都满足这个条件那么即使age字段存在索引那么也不会使用。
当age18这个条件表中大多数数据不满足时才会走索引。
SQL提示
SQL提示是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
比如说name字段存在单列索引也和age、sex存在联合索引那么在只查询name字段时可能会走联合索引。此时我们可以人为干预name字段走单例索引。 使用语法 # use index 在搜索时使用该索引
explain select * from 表名 use index(索引名) where ……;
# ignore index 在搜索时不使用该索引名
explain select * from 表名 ignore index(索引名) where ……;
# force index 强制使用该索引
explain select * from 表名 force index(索引名) where ……;
覆盖索引
尽量使用覆盖索引查询中使用了索引并且需要返回的列在该索引中已经全部能够找到减少select *的使用。
比如说name与age字段建立了索引在查找语句时where里对name与age进行条件查询在返回的字段中只填写name、age字段那么在使用explain查看SQL执行计划时extra字段的信息显示的时using where;using index可能会随着MySQL版本不同而显示不同。
如果返回的字段为name、age与sex字段但是没有在where里使用sex字段即使sex建立了索引extra显示的信息也为using index condition。
using where;using index使用了索引但是需要的数据在索引列中可以找到不需要进行回表查询using index condition使用了索引但是需要回表查询
