。

存储引擎是基于表的#xff0c;而不是库的#xff0c;所以存储引擎也可被称为表类型。

1#xff09;在创…

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。

存储引擎是基于表的而不是库的所以存储引擎也可被称为表类型。

1在创建表时指定存储引擎

-----------------------------------------------------------------------------------------------------------------------------

Engine

-----------------------------------------------------------------------------------------------------------------------------

ndbcluster

-----------------------------------------------------------------------------------------------------------------------------

rows

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎在Mysql

2)特点

:xxx是文件名InnoDB引擎的每张表都对应这样一个表空间文件存储该表的表结构frm、sdi、数据和索引

ibd2sdi

Memory存储引擎的表数据是存储在内存中的受到断电问题、或硬件问题的影响只能将这些表作为临时表或者缓存使用。

2特点

根据应用系统特点选择存储引擎对于复杂应用可以根据实际情况选择多种存储引擎的组合。

InnoDBmysql默认存储引擎支持事务和外键。

如果应用对事务的完整性有较高要求在并发条件下要求数据一致性数据操作除了插入和查询之外还有很多更新和删除操作那么innoDB存储引擎是比较合适的选择。

MyISAM如果数据以插入和查询为主对数据的更新和删除较少并且对事务的完整性和并发性要求不高选择这个存储引擎比较合适。

Memory:将所有数据保存到内存中访问速度快通常用于临时表及缓存。

memory对表的大小有限制太大的表无法缓存到内存中而且无法保证数据的安全性。

二、索引

索引是帮助mysql高效获取数据的一种数据结构有序的。

在数据之外数据库还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。

优缺点

优势劣势提高数据检索效率降低数据库的IO成本索引也是要暂用存储空间的通过索引列对数据进行排序降低数据排序的成本降低CPU的消耗索引大大提高了查询效率同时降低了更新表的速度如对表进行INSERT、UPDATE、DELETE时效率降低。

2、索引结构

索引结构描述BTree索引最常见的索引类型大部分存储引擎都支持B树索引Hash索引底层数据结构使用hash表实现的只有精确匹配索引列的查询才有效不支持范围查询R-Tree空间索引空间索引是MyISAM引擎的一种特殊索引类型主要用于地理空间数据类型用的较少Full-text全文索引是一种通过建立倒排索引快速匹配文档的方式。

类似于Lucene、Solr、ES

索引支持情况

索引InnoDBMyISAMMemoryBTree索引你支持支持支持Hash索引不支持不支持支持R-Tree索引不支持支持不支持Full-text全文索引5.6版本之后支持支持不支持

1二叉树

以一颗最大度数max-degree为55阶的B-Tree为例每个节点最多存储4个key5个指针

4B树

Mysql索引数据结构对经典的BTree进行了优化在原BTree的基础上增加了一个指向相邻叶子节点的链表指针就形成了一个带有顺序指针的BTree提高了区间访问的性能。

5Hash

哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在哈希表中。

如果两个或者多个键值映射到一个相同的槽位上他们就产生了哈希冲突也成为哈希碰撞可以通过链表来解决。

hash索引特点

3查询效率高通常只需要一次检索就可以了效率通常要高于BTree索引

存储引擎支持

在mysql中支持hash索引的是memory引擎而InnoDB中具有自适应hash功能hash索引是存储引擎根据BTree索引在指定条件下自动构建的。

2对于B-树无论是叶子结点还是非叶子节点都会保存数据这样导致一页存储的键值减少指针跟着减少要同样保存大量数据只能增加数的高度导致性能下降。

3、索引分类

分类含义特点关键字主键索引针对于表中主键创建的索引默认自动创建只能有一个primary唯一索引避免表中某个列的值数据重复可以有多个unique普通索引快速定位特定数据可以有多个全文索引全文索引查找的是文本中的关键词而不是比较索引中的值可以有多个fulltext

分类含义特点聚集索引Clustered

Index将数据存储于索引结构放到一块索引结构的叶子节点保存了行数据必须有而且只有一个二级索引Secondary

Index将数据与索引分开存储索引结构的叶子节点关联的是对应的主键可以存在多个

聚集索引选取规则

3如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引

一行数据大小为1k一页中可以存储16行这样的数据InnoDB的指针暂用6个字节的空间主键即使是BigInt暂用字节数为8

高度为2

可以提供服务器状态信息通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT访问频次。

如果没有select查询或者很少的查询操作就没必要做优化。

show

慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有sql语句日志。

2开启慢查询

mysql的慢查询日志默认没有开启需要在MYSQL配置文件/etc/my.cnf中配置如下信息:

--开启mysql慢查询日志开关

--设置慢日志的时间为2秒sql语句执行时间超过两秒就会视为慢查询记录慢查询日志

long_query_time2

配置完毕后通过以下指令重启mysql服务器进行测试查看慢日志记录文件中记录的信息/var/lib/mysql/localhost-slow.log

三profile详情

profiles命令在做sql优化时能够帮我们了解每一条sql时间都耗费到哪里去了。

通过have_profiling参数能够看到当前mysql是否支持profile操作

select

sec)--查看profiling开关是否开启默认是关闭状态。

mysql

开启profiling开关之后通过如下指令查看指令的执行耗时。

show

query_id;--查看指定query_id的SQL语句CPU的使用情况

show

-------------------------------------------------------------

Query_ID

-------------------------------------------------------------

0.01071200

-------------------------------------------------------------

rows

------------------------------------------

Status

------------------------------------------

starting

------------------------------------------

rows

----------------------------------------------------------------

Status

----------------------------------------------------------------

starting

----------------------------------------------------------------

rows

EXPLAIN或者DESC命令获取mysql如何执行select语句的信息包括在select执行过程中表如何连接及连接的顺序。

直接在select

----------------------------------------------------------------------------------------------------------

select_type

----------------------------------------------------------------------------------------------------------

SIMPLE

----------------------------------------------------------------------------------------------------------

row

----------------------------------------------------------------------------------------------------------

select_type

----------------------------------------------------------------------------------------------------------

SIMPLE

----------------------------------------------------------------------------------------------------------

row

idselect查询的序列号表示查询中执行select子句或者操作表的顺序id相同执行顺序从上到下id不同值越大越先执行

select_type表示查询类型常见的有SIMPLE简单表即不使用表连接或子查询、PRIMARY主查询即外层的查询、UNIONUNION中的第二个或者后面的语句、SUBQUERYselect或者where之后包含了子查询

type表示连接类型性能由好到差的连接类型是NULLsystem、const、eq_ref、ref、range、index、all

possible_keys显示可能应用在这张表的上索引一个或多个。

key_lens表示索引中使用的字节数该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下长度越短越好。

rowsmysql认为必须要执行查询的行数在InnoDB引擎的表中是一个估计值可能并不总是准确的。

filtered:表示返回结果的行数占需读取行数的百分比filtered的值越大越好。

mysql

---------------------------------------

name

---------------------------------------

红小豆

---------------------------------------

rows

----------------------------------------------------------------------------------------------------------------------------------------------------------------

select_type

----------------------------------------------------------------------------------------------------------------------------------------------------------------

SIMPLE

----------------------------------------------------------------------------------------------------------------------------------------------------------------

rows

---------------------------------------------------------------------------------------------------------------------------------------------------------

select_type

---------------------------------------------------------------------------------------------------------------------------------------------------------

PRIMARY

---------------------------------------------------------------------------------------------------------------------------------------------------------

rows

如果索引了多列联合索引要遵守最左前缀法则。

最左前缀法则指的是查询从最左列开始并且不跳过索引中的列。

下图说明跟查询条件的位置没关系只要查询中包含了索引字段就行包含了三个字段则使用三个列的索引

1范围查询

联合索引中出现范围查询,范围查询右侧的列索引失效。

使用,替代,

explain

--索引idx_user_pro_age_sta只能使用profession

explain

如果仅仅是尾部进行模糊匹配索引不会失效如果是头部模糊匹配索引失效。

用or分割开的条件如果or前的条件中的列有索引or后面的条件中的列没索引那么涉及的索引都不会被引用到。

6数据分布影响

数据的分布可能会影响使用索引还是不使用。

例如某个字段如果大部分都是null

则条件is

sql提示是优化数据库的一个重要手段简单来说就是在sql语句中加入一些认为的提示来达到优化操作的目的。

use

尽量使用覆盖索引查询使用了索引并且需要返回的列在该索引中已经全部能够找到减少select

知识小贴士

index查询使用了索引但是需要的数据都在索引列中能找到所以不需要回表查询数据

前缀索引

当字段类型为字符串varchar、text时有时候需要索引很长的字符串这会让索引变得很大查询时浪费很多磁盘IO影响查询效率。

此时可以只将字符串的一部分建立索引这样可以大大节约索引空间从而提高索引效率

2》语法:

可以根据索引的选择性来决定而选择性是指不重复的索引值基数和表中记录总数的比值索引选择性越高则查询效率越高。

唯一索引的选择性是1这是最好的索引选择性效率也是最好的。

select

根据前5个字符找到对应数据的id根据id回表找到对应行再把表中数据跟条件对比

如下图

在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引而非单列索引。

单列索引情况

多条件联合查询时mysql优化器会评估哪个索引的效率更高会使用该索引完成本次查询。

联合索引情况