时间：2021-07-01 10:21:17 帮助过：26人阅读
MySQL的设计是一个单进程多线程的架构设计，通过线程池来进行管理的每一个用户连接的设计，因此从一开始MySQL对多核心系统的利用就欠佳，直到MySQL5.5开始大规模企业化之后，一直致力于MySQL在多核系统中的表现。但是MySQL在每一个连接中依旧只能利用到单核心，即你的SQL语句哪怕再复杂，也只能用一个CPU去处理。
由于MySQL只有1个进程，而单个进程在Linux系统上能使用的内存是有限制的，因此MySQL没有其他选择，必须使用64位的操作系统，否则内存的限制将是一个无法解决的瓶颈。
MaraiDB的安装方式多种多样，Centos7自带yum源中的就是MariaDB
yum install -y jemalloc jemalloc-devel #依赖于其中的一个共享库文件 (1) rpm: OS: 官方 (2) 源码包 (3) 通用二进制格式的程序包 下面以CentOS7为例，使用通用二进制格式的程序包安装：准备admin用户mysql,mysql组 (1) 准备数据目录； 以/mydata/data目录为例；（最好是创建一个新的分区专门存放数据） chown -R mysql.mysql /mydata/data/ (2) 安装配置mariadb # useradd -r mysql # tar xf mariadb-VERSION.tar.xz -C /usr/local # cd /usr/local # ln -sv mariadb-VERSION mysql # cd /usr/local/mysql # chown -R root:mysql ./* # scripts/mysql_install_db --user=mysql -datadir=/mydata/data 初始化数据，不能使用绝对路径，只能在这个位置 # cp support-files/mysql.server /etc/init.d/mysqld # chkconfig --add mysqld (3) 提供配置文件 ini格式的配置文件；各程序均可通过此配置文件获取配置信息； [program_name] OS Vendor提供mariadb rpm包安装的服务的配置文件查找次序： /etc/mysql/my.cnf --> /etc/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf 通用二进制格式安装的服务程序其配置文件查找次序： /etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf 获取其读取次序的方法： mysqld --verbose --help | less # cp support-files/my-large.cnf /etc/my.cnf 添加三个选项： datadir = /mydata/data innodb_file_per_table = ON skip-name-resolve = ON (4) 启动服务 # service mysqld start 安装完成后，使用mysql_secure_installation进行固化操作： root密码 禁止root远程登录 移除匿名用户
存储引擎说白了就是如何在文件系统上存储数据、建立索引、更新、查询等等，是MySQL的真正实现方案，MySQL是采用一种插件式的存储引擎管理方案，可以通过show engines查看MySQL支持的所有存储引擎，可以使用show table status like [table_name]的方式来查看表的存储引擎。
(1) InnoDB
在mysql5.5之后，InnoDB就被指定为是默认的存储引擎，而MariaDB使用percona提供的XtraDB引擎作为InnoDB的增强版，非常适合大量的短期事务。InnoDB将数据和索引都存储在一所谓的"表空间"(table space)中。
根据配置有2种情况：
第一种所有的InnoDB表中的数据和索引放置于同一表空间中，而表空间文件会放放置datadir指定的目录中，数据文件大致上为ibddata1,ibddata2.....
第二种更为常用，每个表使用独自的表空间，需要在配置文件或者启动参数中设置innodb_file_per_table=ON来启用该功能，此时每个数据文件+索引在tb_name.idb中，表格式定义元数据文件 tb_name.frm
特性：
存储能力64TB
通过MVVC机制来支持高并发
聚集索引机制，聚集索引是指每一行的数据都和当前行的索引信息放在一起，一般聚集索引都是主键，其他的索引称为辅助索引，任何索引想要找到数据信息都要先找到聚集索引，再由聚集索引找到数据信息。
支持预读操作，自适应hash，查询缓存等等
支持热备(xtrabackup)
支持事务
锁粒度支持行级别锁
(2) MyISAM
MySQL自己研发的一个存储引擎，在MariaDB中使用aira作为增强版实现
表格式文件，数据文件，索引文件单独存放，分别是tb_name.frm,tb_name.myd,tb_name.myi
特性：
最大存储能力为256TB
支持全文索引Full Text
只支持表级锁
不支持事务、外键、数据缓存、不支持MVVC
只能手工修复
索引是非聚集索引
支持延迟更新索引
支持表压缩机制
MySQL中其他常见的存储引擎：
csv：将普通的csv作为MySQL使用
MRG_MYISAM:将多个MyISAM表合成为一个虚拟表
BLACKHOLE：黑洞
MMEORY: 内存，支持hash索引，表级别锁
PERFORMANCE_SCHEMA: 伪存储引擎
FEDERTED: 远程MySQL代理
MariaDB中还支持：
OQGraph:
SpinxSE: 可以和spinx集成
TokuDB：海量数据存储
Cassandra：可以和Cassandra集成
...
总结，如何选择存储引擎：
需要事务？InnoDB
需要外键？InnoDB
需要热备？InnoDB
需要Fulltext？MyISAM
MySQL中的并发控制通过锁来实现，MySQL中的lock从大体上分为read lock和write lock，和一般的读锁，写锁一样，写锁和什么都互斥，读锁和读锁不互斥，写锁优先级高于读锁等等。
而从用户的角度看又可以分为显示锁，用户可以显示请求调用的锁称为显示锁，是表级锁，而隐式锁则是由存储引擎自动施加的锁，用户不可见，由存储引擎决定。
从锁的粒度来看分为表级锁和行级锁，锁的粒度精细，实现越复杂，因此锁的使用策略就是在粒度和控制上实现一种平衡。
用户显示调用锁的方法
(1) LOCK TABLE
Syntax: LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: READ [LOCAL] | [LOW_PRIORITY] WRITE UNLOCK TABLES
(2) FLUSH
FLUSH TABLES tb_name[,...] [WITH READ LOCK] [FOR UPDATE]
(3)
SELECT clase [FOR UPDATE] [WITH READ LOACK]
事务： A transaction is a group of SQL queries that one threated atomically, as a single unit of work.
Atomicity:
A transaction must function as a single indivisible unit of work so that the entire transaction is either applied or rolled back
Consistency:
The database should always move from one consistent state to the next
Isolation:
The results of a transaction are usually invisble to the other transactions until the transaction is complete.
Durability:
Once commited, a transaciton‘s changes are permanent.
tx_isolation:
可以使用命令show variables like ‘tx_%‘;查看事务隔离级别tx_isolation
事务隔离级别：(Isolation Levels)
READ-UNCOMMITED:
Transactions can view the results of uncommited transactions
READ-COMMITED:
A transaction will see only those changes made by transactions that were already commited when it began, and its change won‘t be visible to other unitl it has commited
REPEATABLE-READ
It gurantees that any rows a transaction read will "look the same" in subsequent reads within the same transaction, but in theory it still allows another tricky problem: phantom reads.
SERIALIZABLE:
one by one.
Transaction Logging helps make transactions more efficient.
事务日志，可以帮助提高事务的效率。使用事务日志，存储引擎在修改表数据时，只需要修改其内存拷贝，采用追加的方式，之后再同步到磁盘中，这样将写操作由随机IO变成了顺序IO，可以有效的提高性能，这种方式称为预写式日志。
InnoDB中查看事务日志
innodb_log_file_size 文件大小
innodb_log_files_in_group 单组文件个数
innodb_log_group_home_dir 文件home目录
可以在MySQL初始化时在配置文件中指定。注意文件大小要适量。
SELECT [ALL | DISTINCT | DISTINCTROW] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [ FROM table_references [PARTITION (partition_list)] [{USE|FORCE|IGNORE} INDEX [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]) ] [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE ‘file_name‘ [CHARACTER SET charset_name] [export_options] | INTO DUMPFILE ‘file_name‘ | INTO var_name [, var_name] ] [FOR UPDATE | LOCK IN SHARE MODE] ] export_options: [{FIELDS | COLUMNS} [TERMINATED BY ‘string‘] [[OPTIONALLY] ENCLOSED BY ‘char‘] [ESCAPED BY ‘char‘] ] [LINES [STARTING BY ‘string‘] [TERMINATED BY ‘string‘] ]
DISTINCT: 数据去重
SQL_CACHE: 显示指定查询缓存
支持别名
where字句，逻辑操作符等等
支持分组以及分组函数
支持排序
FOR UPDATE 加上写锁
LOCK IN SHARE MODE 加上读锁
支持多表连接以及子查询...
从磁盘中读数据会产生磁盘IO,因此在读多写少的场景下，可以使用缓存。
MySQL天然支持查询缓存，在某些情况下还是可以用的，比如说单台服务器，多台服务器一般采用专门的缓存服务器比如Memcached，redis等等服务器实现。
这里的关注重点在MySQL本身的查询缓存，MySQL可以对每次查询做hash键，查询的数据为value进行缓存，而这里的查询key需要考略多方面因数：
查询本身
查询的数据库
客户端使用的协议版本。。。
注意： 查询语句任何的不同，哪怕是一个空格，一个大小写都会引起查询缓存的不同，因此所有程序员保持一定的SQL书写风格还是有必要的。
在前面的MySQL架构中描述了查询缓存实现的组件，而InnoDB，最常见的存储引擎是支持查询缓存的。
在实战中，查询缓存也有可能成为并发热点问题，这往往是由于多核竞争引起的，由于计算机的核心数较多，高并发场景下产生的竞争问题，这也是查询缓存的内存大小默认不会太大的原因，默认是16M.
同时要注意到，不是所有的数据都会缓存，比如我查询一个select now()，这完全不能缓存，一般包含以下内容的都不会缓存，比如用户自定一函数，存储过程，自定义变量，临时表，mysql库中的系统表，不确定值等等。
variables中的相关变量有：
query_cache_min_res_unit: 查询缓存中内存块的最小分配单位；
较小值会减少浪费，但会导致更频繁的内存分配操作；
较大值会带来浪费，会导致碎片过多；
query_cache_limit：能够缓存的最大查询结果；
对于有着较大结果的查询语句，建议在SELECT中使用SQL_NO_CACHE
query_cache_size：查询缓存总共可用的内存空间；单位是字节，必须是1024的整数倍；
query_cache_type: ON, OFF, DEMAND
query_cache_wlock_invalidate：如果某表被其它的连接锁定，是否仍然可以从查询缓存中返回结果；默认值为OFF，表示可以在表被其它连接淘宝的场景中继续从缓存返回数据；ON则表示不允许；
上述参数可以用来对查询缓存进行调优，调优流程图如下：
而缓存的命中率可以作为一个参考，注意只是一个参考，因为可能命中率很低，10条语句只有3条，但是如果这3条返回的数据量值得缓存，计算方式是Qcache_hits/(Qcache_hits+Com_select)
以上变量都是variables中的值，而相关变量大概如下：
SHOW GLOBAL STATUS LIKE ‘Qcache%‘; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16759688 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+
说起sql优化，大部分都落实在索引优化上，什么是索引，MySQL不可能在每次查询数据时将磁盘的数据全部加载到内存中，需要利用数据结构加载少量数据集到内存中即可，这部分数据集就是索引。索引的优点大致如下：
索引可以降低服务器查询的io次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转换为顺序IO(在硬盘非固态的时候还是有用的)
索引功能主要由存储引擎来实现，不同的引擎可以使用不同的索引结构。
(1) B+树索引
B+树是一种常见的数据结构，是B树的变种，相对于B树，(B树是什么？略)，增加了以下特性
1. 所有的关键字存储在叶子节点中
2. 为所有叶子节点增加了一个链指针
B+树是很适合硬盘读写的数据结构。红黑树等虽然也适合用来实现索引，比如JAVA中的Treemap就是红黑树，适合用来在内存中做索引。
这是由于局部性原理和磁盘预读，什么是局部性原理？时间上，data被用到后，之后的时间很可能再次被用到，空间上，data被用到后，附近的数据很可能被用到，这就是局部性，程序使用的数据在空间上和时间上都是比较集中的。由于磁盘的存取速度很慢，比如在机械硬盘物理上的平均寻址时间，由于linux系统不管什么io模型都必须经历的2个阶段(磁盘->内核空间，内核空间->用户空间)等等...，所以MySQL要尽可能的减少IO的存取次数，而B+树就比较合适，B+树内节点都没有data，只有叶子节点上有data，因此每个节点可以存储更多的数据，范围更大，单次IO的数据量更大，可以直接预读，而且由于新增链指针，可以很方便的进行区间查找，即MySQL中where子句中的between操作。
B+树索引在MySQL中有以下要点：
适用于：
精确值匹配，例如="aa"
最左前缀，例如like "Jin%"
范围匹配，例如 < >
精确匹配之后范围匹配，一般精确匹配都在左侧，而范围适合在右侧，且中间不能跳过其他字段
覆盖索引，索引中就包含要返回信息，不需要查找数据
不适用于:
(2) hash 索引
注意：只有Memory存储引擎支持显式hash索引；
适用场景：
只支持等值比较查询，包括=, IN(), <=>;
不适合使用hash索引的场景：
存储的非为值的顺序，因此，不适用于顺序查询；
不支持模糊匹配；
(3) 空间索引（R-Tree）：
MyISAM支持空间索引；
(4) 全文索引(FULLTEXT)：
在文本中查找关键词；
上面只是常见索引的数据结构以及用法，索引中还存在着许多的细节问题。比如索引要独立使用，不要参与运算，使用函数等等，在选择索引时候使用左前缀考虑左前缀长度等等，多列索引的陷阱等等。
1. 利用索引排序？
由于索引是有序，因此order中的列存在索引时，可以通过索引来排序，否则有可以使用file sorting，这是很不好的。
2. InndoDB中的聚集索引？
聚族(聚集)索引就是叶子节点保存的就是数据本身，索引和数据在一起，一般主键索引才是聚集索引。
优点：数据访问更快，索引扫描无须来回标
缺点：插入速度严重影响插入顺序，比如InnoDB中按照主键逆序；全表扫描会慢；二级索引会变大，且需要二次查找
注意explain返回不是精确值，只是估计值
id: 当前查询语句中，每个SELECT语句的编号；
注意：UNION查询的分析结果会出现一外额外匿名临时表；
select_type：
简单查询为SIMPLE
复杂查询：
SUBQUERY: 简单子查询；
DERIVED: 用于FROM中的子查询；
UNION：UNION语句的第一个之后的SELECT语句；
UNION RESULT: 匿名临时表；
table：SELECT语句关联到的表；
type：关联类型，或访问类型，即MySQL决定的如何去查询表中的行的方式；
ALL: 全表扫描；
index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描；
range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点；
ref: 使用了索引，但是可能返回多行根据索引返回表中匹配某单个值的所有行；
eq_ref：仅返回一个行，但与需要额外与某个参考值做比较；
const, system: 直接返回单个行；
possible_keys：查询可能会用到的索引；
key: 查询中使用了的索引；
key_len: 在索引使用的字节数；
ref: 在利用key字段所表示的索引完成查询时所有的列或某常量值；
rows：MySQL估计为找所有的目标行而需要读取的行数；
Extra：额外信息
Using index：MySQL将会使用覆盖索引，以避免访问表；
Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤；
Using temporary：MySQL对结果排序时会使用临时表；
Using filesort：对结果使用一个外部索引排序；
MySQL Basic
标签：blog 可见 数据文件 阶段 having osi value undo 5.5