mysql进阶,存储引擎与索引

mysql进阶

存储引擎

mysql体系结构

1. 连接层
2. 服务层
3. 存储引擎层
4. 存储层

存储引擎定义

存储引擎就是存储数据,建立索引,更新/查询数据等技术的实现方式.存储引擎是基于表的,而不是基于库的,所以存储引擎也被称为表类型.

#查询当前数据库支持的存储引擎
show engines;
#常见存储引擎有innodb,myisam,memory等,其中myisam在mysql5.5版本之前为表的默认引擎,5.5版本之后更换为innodb
#查看当前表的建表语句,会显示当前表的引擎
show create table 表名;
#创建表时能直接指定表存储引擎
create table 表名{
	字段一,
	字段二
}engine = 存储引擎

innodb存储引擎

innodb是一种兼顾高可靠性,高性能的通用存储引擎,

特点

DML操作遵循ACID模型,支持事务;

行级锁,提高并发访问性能

支持外键约束,保证数据的完整性和正确性

文件

每一张表都对应一个xxx.idb表空间文件文件,存储该表的表结构(frm,sdi),数据和索引

innodb逻辑存储结构

Tablespace:表空间   Segment:段  Extent:区  Page:页  Row:行
一个区大小固定1M,一个页大小固定16K

myisam存储引擎

不支持事务,支持表锁,不支持行锁,访问速度快
xxx.sdi:存储表结构信息
xxx.MYD:存储结构
xxx.MYI:存储索引

memeory存储引擎

memory引擎的表数据是存储在内存中的,由于受到硬件问题,或断电问题的影响,只能将这些表作为临时表或缓存使用
支持hash索引
xxx.sdi:存储表结构信息

索引

索引的定义

索引是一种帮助mysql高效获取数据的数据结构(有序).在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这种数据结构以某种方式指向数据.
当然提高效率的同时意味着需要空间作为代价,索引也是要占空间的,insert ,update ,delete 效率页降低了.

索引结构

mysql的索引是在存储引擎层实现的,不同存储引擎有不同的结构,主要包含以下几种:

索引结构	描述
B+Tree索引	最常见的索引,大部分引擎都支持B+树索引,innodb,myisam,memory都支持
Hash索引	底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围索引,只有memory引擎才支持
R-tree(空间索引)	空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少,只有myisam支持
Full-test(全文索引)	是一种通过建立倒排索引,快速匹配文档的方式,类似与LUCENE,Solr,ES,myisam支持,innodb在5.6版本后支持

二叉树

二叉树缺点:顺序插入时,会形成一个链表,查询性能大大降低,大数据量情况下,层级较深,检索数度慢

B-Tree(多路平衡查找树)

度数:指一个节点的子节点个数,例如最大度数为5的b-tree,每个节点最多存储4个key,5个指针,所有key下面都挂载数据

B+Tree

所有元素都会出现在叶子节点,叶子节点形成了一个单向链表

只有叶子节点key才会挂载数据

在mysql中,索引数据结构对B+Tree进行了优化,增加了指向相邻叶子节点的链表指针,形成了双向链表

hash

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

先算出每一行的hash值,然后在对某一字段值通过hash函数计算,映射到对应槽位上,然后存储在hash表中,表中值为字段值和当前值对应行的hash值

索引分类

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

在innodb存储引擎中,根据索引的存储形式,又可以分为以下两种:

分类	含义	特点
聚集索引	数据存储与索引放到一块,索引结构的叶子节点保存了数据	必须有,且只有一个
二级索引	数据与索引分开存储,索引结构的叶子节点关联的是对应的主键	可以存在多个

//索引选取规则
如果存在主键,主键索引就是聚集索引,如果不存在主键,第一个唯一索引作为聚集索引,如果没有主键,也没有唯一索引,innodb会自动生成一个rowid作为隐藏的聚集索引
    
//如果根据二级索引查询数据,先查询到对应主键值,在到聚集索引中拿到数据.这个过程我们称为回表查询

一个节点存储在一个页中,一个页大小16K

假设主键类型bigint,占8个字节,innodb中指针占6个字节,有n个key.
高度为2的B+Tree
n*8+(n+1)*6 = 16*1024,算出n约为1170,即指针数为1171,有1171页数据,一页数据16k,1171*16 = 18736k
高度为3的B+Tree
1171*1171*16 = 21939856k

索引语法

创建索引,根据哪个字段创建什么类型索引
create [unique|fulltext] index 索引名 on 表名(字段列表);
查看索引,查看指定表所有的索引
show index fromitit 表名;
删除索引
drop index 索引名 on 表名;

性能分析

1.sql执行频率
mysql客户端连接成功后,通过show[session|global] status命令可以提供服务器状态信息.通过如下指令,可以查看当前数据库的insert,update,delete,select的访问频次
show [session|global] status like 'Com_______';
2.慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志.mysql的慢查询日志默认没有开启,需要在mysql的配置文件(/etc/my.cnf)中配置如下信息
#查看慢查询是否开启
show variables like 'slow_query_log';
#在/etc/my.cnf配置以下信息
#开启mysql慢日志查询开关
#slow_query_log=on 
#设置慢日志的时间为2秒,sql语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
#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获取mysql如何执行select语句的信息,包括在select语句执行过程中表如何链接的顺序

#直接在select语句前加explain或desc
explain select 字段列表 from 表 where 条件;

explain执行计划各字段	含义
id	select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下,id不同,值越大,越先执行)
select_type	表示查询类型,常见的有simple(简单表,不使用表连接或子查询).primary(主查询,即外层查询)等
type	表示连接类型,性能由好到差的连接类型为null(不查询表),system(查询系统表),const(主键或唯一索引),eq_ref,ref(常规索引),range,index,all(不使用索引)
possible_key	显示可能应用在这张表上的索引,一个或多个
key	实际使用的索引,如果为null,则没有使用索引
key_len	表示索引中使用的字节数,该值为索引字段最大的可能长度,并非实际长度,在不损失精确性的前提下,长度越短越好
rows	必须要执行的行数,在innodb引擎表中,是一个估计值
filtered	表示是返回结果的行数占需读取行数的百分比,值越大越好
extra	额外的信息

索引的使用规则

最左前缀法则

#最左前缀法则
如果索引包含了多列(联合索引),要遵循最左前缀法则,最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列,如果跳过某一列,索引将部分失效(后面字段索引失效)
#创建一个a,b,c三个字段的联合索引
create index idx_a_b_c on emp(a,b,c)
#a字段为最左索引,所以查询条件必须含有a字段,如果没有a字段,索引无效,走全表扫描
select * from emp where a='' and c='';
该查询会走索引,但是只有a索引,因为跳过了b索引,c索引就会失效
#a索引在条件的任意位置即可,在根据剩余索引顺序看索引是否失效

范围查询

#范围查询
联合索引中,出现范围查询(<,>),范围查询右侧的列索引失效

select * from emp where a='' and b>'' and c='';
其中c索引会失效,如果想避免,使用>=和<=条件

索引失效情况

1.不要在索引列上进行运算操作,索引将失效
2.字符串类型字段使用时,不加引号,索引将失效
3.如果在尾部进行模糊匹配,索引不会失效,如果是头部模糊匹配,索引失效
4.or连接的条件,如果or前的条件中的列有索引,而后面的列中没有索引(联合索引只有第一个列算有索引),那么涉及的索引都不会被用到

5.数据分布影响,如果mysql评估使用索引比全表更慢,则不使用索引

sql提示

#sql提示,是优化数据库的一个重要手段,简单来说,就是在sql语句中加入一些人为的提示来达到优化操作目的,当一个字段既有单列索引,又有联合索引的同时,我们可以指定索引的使用
use index: 使用索引,只是建议,mysql不一定接受
explain select * from 表 use index(索引名) where 条件;
ignore index:忽略索引
explain select * from 表 ignore index(索引名) where 条件;
force index:必须按这个索引
explain select * from 表 force index(索引名) where 条件;

覆盖索引

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

前缀索引

#当前字段类型为字符串(varchar,test等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘io,影响查询效率.此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率
create index 索引名 on 表(字段(n)),n代表取前几位字符建立索引
select count(distinct substring(字符串字段,m,n))/count(*) from 表;
表示m到n这些字符在当前字符串字段不重复率,值越高重复率越低

索引设计原则

索引设计原则
1.针对于数锯量较大，且查询比较频繁的表建立索引。
2.针对于常作为查询条件(where）、排序(order by)、分组(group by)操作的字段建立素引。
3.尽量选择区分度高的列作为索引，尽量建立准一索引，区分度越高，使用索引的效率越高。
4.如果是字符申类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀紫引。
5.尽量使用联合索引，减少单列索引，查询时，联合泰引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6.要控制索引的数量，索引并不是多多益善，素引越多，维护索引结构的代价也就越大，会影响增剧改的效率。
7.如果素引列不能存储NU儿L值，请在创速表时使用NOT NU儿L约束它。当优化器知道每列是否包含NU儿L值时，它可以更好地确定思
索引最有效地用于查询。