栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

MYSQL存储引擎、索引及优化

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

MYSQL存储引擎、索引及优化

文章目录
  • 一、存储引擎
  • 二、索引创建与设计原则
    • 2.1、索引分类
    • 2.2、索引的创建
    • 2.3、索引设计原则
      • 2.3.1、适合创建索引的情况
      • 2.3.2、不适合创建索引的情况
  • 三、索引优化与查询优化
    • 3.1、索引失效
    • 3.2、关联查询优化 join
    • 3.3、子查询优化
    • 3.4、排序优化 order by
    • 3.5、分组优化 group by
    • 3.6、分页查询优化 limit
    • 3.7、count 优化
    • 3.8、EXISTS 和 IN 的区分
    • 3.9、select *

一、存储引擎

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

  • 查看所有的存储引擎类型
show engines;


数据库默认是InnoDB存储引擎,5.5版本之前是MyISAM存储引擎,5.5之后是InnoDB存储引擎。

  • MyISAM存储引擎和InnoDB存储引擎的区别
    • InnoDB存储引擎:具备外键支持功能的事务存储引擎
      • 兼顾高可靠性和高性能的通用存储引擎;支持事务、行级锁,提高并发访问性能;
      • 用来处理大量的短期事务,确保事务的完整提交和回滚;
      • 除增加和查询外,若还需要更新删除操作,优先选取InnoDB存储引擎;
      • InnoDB存储引擎是处理巨大数据量的最大性能设计;
      • 缺点:写的处理效率差一些,并会占用更多的磁盘空间保存数据和索引,内存要求高;
    • MyISAM存储引擎:主要的非事务处理存储引擎
      • 与InnoDB存储引擎不同,MyISAM存储引擎只保存索引,不保存真实数据;
      • 支持表锁,但不支持事务、行级锁、外键,崩溃后无法完全恢复;
  • 区别总结:
对比项MyISAMInnoDB
外键不支持支持
事务不支持支持
行表锁表锁,即操作一条记录也会锁住整表,不适合高并发的操作行锁,操作只锁住一行,不对其他行产生影响,适合高并发
缓存只缓存索引缓存索引和数据,内存要求高
自带系统表使用是否
关注点性能:节省资源、消耗少、简单业务事务:并发写、事务、更大资源
默认安装是是
默认使用否是
  • 创建表指定存储引擎
create table account(

) engine = InnoDB ;
二、索引创建与设计原则
  • 使用索引的原因
    • 索引是存储引擎用于快速查找数据记录的一种数据结构,类似于课本的目录部分,可以快速定位到需要查找的内容的位置。不使用索引的查找是通过全表查询,一行一行的进行搜索,直至找到符合条件的;而索引不需要进行一行一行的搜索,大大提升了查询速度。
  • 优点
    • 提高查询数据效率,降低数据库的IO成本(创建索引主要原因);
    • 通过创建唯一索引,可以保证每行数据的唯一性;
    • 对于有依赖性的子表和父表间的联合查询,可以提高查询速度;
    • 在使用分组和排序子句进行数据查询时,可以减少分组和排序的时间,降低CPU消耗;
  • 缺点
    • 创建索引和维护索引耗费时间,随着数据量的增加,耗费时间也会增加;
    • 索引占磁盘空间,若有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸;
    • 降低更新表的速度,当增加,删除和修改数据时,索引也会跟着更新,降低了数据的维护速度。
2.1、索引分类
类型说明特点关键字
主键索引针对表中主键创建的索引默认自动创建,只有一个,列值不能为nullprimary
唯一索引索引列的值需唯一,可以有多个,允许有nullunique
单值索引(单列、普通)一个索引仅含一列可以有多个,允许有null
联合索引一个索引含有多个列可以有多个
全文索引可以在char、varchar、text类型中创建可以有多个,只有MyISAM支持fulltext
  • 聚簇索引和非聚簇索引
索引说明
聚簇索引将数据存储与索引放到一起,索引结构的叶子节点保存了行数据 ;一表仅有一聚簇索引
非聚簇索引将数据与索引分开存储,索引结构的叶子节点指向了数据对应的值

  • InnoDB:聚簇索引,将主键组织到一棵B+树中,而行数据储存在叶子节点,直接根据主键id即可获取行数据。聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。

  • MyISM:非聚簇索引,建立两颗B+树,将索引和数据分开存储,一棵树的叶子节点存储索引,另一颗树存储数据;找到了索引并不一定能够找到数据。若where的不是主键,则需先从索引B+树中查找到其对应的索引,然后再回表查询,在存储数据的B+树中查询对应节点的数据。简单说就是进行多次查询(先查到索引,再根据索引查询相关数据)。

  • 例子说明

    • 下面我们创建了一个学生表
create table student (
    id bigint,
    sex varchar(20) ,
    name varchar(20) ,
    address varchar(20) ,
    phone varchar(20),
    PRIMARY KEY (id) USING BTREE,
    UNIQUE KEY index_phone (phone) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

1、根据主键获取行数据,此时主键是聚簇索引,因为主键id = 1对应的索引叶子节点存储了完整的行数据。

select * from student where id = 1

2、根据phone查询name和sex,phone是唯一索引,但查询的列包含了name和sex。当根据phone查询时,先在叶子节点存储的索引B+树上查找到对应的主键id,再根据主键id回表查询,获取叶子节点存储数据的B+树的所有数据,所以这种查询下phone不是聚簇索引

select name.sex from student where phone = '12345678912'

3、我们根据phone查询phone(有人会问知道编号了还要查询?要,你可能需要验证该编号在数据库中是否存在),若数据库中存在查询的phone,直接返回phone,因为所需要的数据就是该索引,不需要回表查询,这种场景下phone是聚簇索引

select phone from student where phone = '12345678912'
  • 聚簇索引优点:
    • 数据访问快,因为数据和索引都保存在同一棵B+树中;
    • 对于主键的排序查询和范围查找更快;
    • 由于聚簇索引排序,数据紧密相连,查询一定范围的数据时,数据库不用从多个数据块中提取数据,节省了IO操作。
  • 聚簇索引缺点:
    • 插入速度依赖于插入顺序,按照主键的顺序插入是最快的,否则出现页分裂,影响性能;对于InnoDB,一般会定义一个自增的ID为主键;
    • 更新主键的代价高,因为将会导致被更新的行移动,因此一般定义主键不被更新;
    • 二级访问需要再次索引查找,第一次查找到主键值,第二次根据主键查找行数据。
2.2、索引的创建
  • 复合索引符合最左前缀原则:在利用索引字段时,查询字段必须包含复合索引的一个列名(列名顺序不能改变,不能跳列名查询),否则索引不生效;为了防止出现这种情况,mysql引擎在查询为了更好的利用索引,在查询过程中会动态调整查询字段顺序以便利用索引查询(但包含索引中的首个列名是必须满足的)。
  • 例子
# 创建复合索引
create index index_name_sex_age on user_info(name,sex,age);

explain # 查看是否使用索引等信息
select * from user_info
where name='xb' and sex='男'  # 使用了索引前两字段 

where name='xb' and age=23   # 使用索引的第一个字段 因为跳过了中间的sex,故age并未使用复合索引中的

where sex='男' and age=23    #  未使用索引,因为没有使用索引的第一个字段,不符合最左前缀原则

where name='xb' and age=34 and sex='男'  # 使用复合索引,虽然顺序不同,但mysql内部引擎动态调整
  • 索引语法:创建、删除、查看索引
# 创建索引
create unique/fulltext/primary/ index 索引名 on 表名(列名) ;

# 修改的方式创建索引
alter table 表名 add index 索引名(列名)

# 在创建表时创建索引 
create table user_info(
number varchar(20) primary key,
`name` varchar(20),
unique(name)  # 指定name列为唯一索引
);

# 查看索引
show index from 表名;

# 删除索引
drop index 索引名 on 表名;
  • 示例
# 创建表
create table user_info(
number varchar(20) primary key,
`name` varchar(20),
age int(10),
sex varchar(10),
phone bigint(11),
`local` varchar(20)
);

  -- 查看索引
show index from user_info;  # 因为创建表时指定number为主键了,自动创建了索引名为primary的索引

	-- 删除索引
drop index  `PRIMARY` on user_info;  # 删除自动创建的主键索引

	-- 给表user_info的字段name创建索引       普通索引
create index index_name on user_info(name); 

	-- 给表user_info的字段phone创建          唯一索引 
create unique index index_phone on user_info(phone);

	-- 给表user_info的字段name,sex,age创建   联合索引   
create index index_name_sex_age on user_info(name,sex,age);

	-- 给表user_info的字段number创建         主键索引   
create index index_number on user_info(number);

2.3、索引设计原则 2.3.1、适合创建索引的情况
  • 1、字段的数值有唯一性的限制
    • 索引本身起到约束的作用,比如主键索引和唯一索引都可以起到唯一性约束的,因此我们遇到表中含有唯一性约束的字段,可以直接将其设为唯一索引或主键索引,提高查询速度。
    • 虽然索引会降低插入修改速度,但这些可以忽略,因为它提高的查询速度是很明显的。
  • 2、频繁作为where查询条件的字段
    • 某个字段经常被用到时,需将其设为索引,提高速度。
  • 3、经常group by和order by的列
    • group by和order by取其一:创建索引后,数据都是按照该字段排序好的,因此分组时候,每组数据都是挨着的并有序;
    • 同时有group by和order by并且各自字段不同时,创建联合索引,索引字段顺序按照group by和order by的字段顺序
  • 4、update、delete的where条件列
    • 修改或删除某字段的值时,先根据where查找到这条记录,然后在对它进行更新或删除;如果进行更新字段是非索引字段,提升效率会更加明显,因为非索引字段更新不需要对索引进行维护。
  • 5、distinct字段需要创建索引
    • 索引会提前进行排序,因此去重查询也会减少查询时间;
  • 6、多表连接、创建索引的注意事项
    • 表连接最多不能超过3张表,严重影响查询效率;where创建索引;连接字段(类型相同)创建索引;
  • 7、使用类型小的字段创建索引
    • 类型大小指的是数据类型,以整数为例,有TINYINT,MEDIUMINT,INT,BIGINT等,它们占的存储空间依次增大,数量范围也依次增大,相对来说,TINYINT属于类型小的数据类型,BIGINT属于类型大的数据类型,若这些数据类型同时存在,优先选取数据类型小的字段创建索引。节省空间存储。
  • 8、使用字符串前缀创建索引
    • 字符串很长会占用大量的存储空间,查询效率也会很低。
  • 9、使用最频繁的列放到联合索引中
    • 减少索引的数量,同时由于最左前缀原则,增加联合索引的使用率。
  • 10、在多个字段需要创建索引的情况下,联合索引优先
    • 单表索引数量不要超过6个,每个索引都需要占用存储空间,并非索引越多越好;
    • 索引会影响insert、delete、update语句的性能,表中大量数据的修改,索引也会跟着进行修改与维护,造成负担。
2.3.2、不适合创建索引的情况
  • 1、在where、order by、group by中使用不到的字段
  • 2、数据量小的表
  • 3、大量重复数据的列
  • 4、经常更新的表
  • 5、不建议用无序的值作为索引
  • 6、删除不再使用或很少使用的列
  • 7、不要定义冗余或重复的索引
三、索引优化与查询优化
  • 数据库调优维度:
    • 索引失效,未利用到索引——索引建立
    • 关联查询太多join ——SQL优化
    • 服务器调优及各个参数设置——调整my.cnf文件
    • 数据量太大——分区分表
3.1、索引失效
  • 1、全值匹配:根据where使用的所有字段创建联合索引,速度最快

  • 2、最左前缀原则:若where中没有使用到联合索引的首个字段、where紧跟着的第一个查询字段不是联合索引首个字段、跳字段使用,索引无效;

  • 3、主键插入顺序:插入数据最好按主键值依次增加顺序,建议使用自增AUTO_INCREMENT成为主键。

  • 4、计算、函数、类型转换导致索引失效:例如使用where left()=‘’、where id+1=3会导致原有索引失效,因为这样等号左边相当于产生了一个新字段。

  • 5、数据类型转换:例如name为数值型数据,但where name=‘123’ 导致name索引失效;

  • 6、范围条件右边列索引失效:范围查询应该放在where语句最后;

  • 7、不等于(!=或<>)索引失效

  • 8、is null可以使用索引、is not null不可以

  • 9、like通配符索引失效:若%在匹配字符串的第一个位置,索引失效,%不在第一个位置,索引不会失效;

  • 10、or前后存在非索引字段

  • 11、数据库和表的字符集统一使用utf8mb4

  • 例子:index(a,b,c)

where语句索引是否被使用
where a=3使用a
where a=3 and b=5使用a,b
where a=3 and b=5 and c=6使用a,b,c
where b=5不使用,不符合最左前缀原则
where b=3 and c=6不使用,不符合最左前缀原则
where a=3 and c=5不使用,跳过了b
where a=3 and b>4 and c=5使用a,b;c在范围查询后
where a is null and b is not null使用a,不使用b
where a<>3不使用
where a=3 or b=4不使用,or后面存在非索引
where abs(a)=3不使用,abs()函数
where a=3 and b like ‘kk%’ and c=5使用a,b,c
where a=3 and b like ‘%kk’ and c=5使用a;b正则%在第一位
where a=3 and b like ‘%kk%’ and c=5使用a
3.2、关联查询优化 join
orderinfo:date, order_id, driver_id, user_id, cost
driverinfo:date, driver_id, city
  • 1、左外连接
create index x on driverinfo(driver_id);
create index y on orderinfo(driver_id);
EXPLAIN select * from orderinfo a left join driverinfo b on a.driver_id = b.driver_id;

明显表b(driverinfo)的type变为了ref并且利用了索引连接 速度快了

  • 2、内连接
create index y on orderinfo(driver_id);
create index x on driverinfo(driver_id);
EXPLAIN select * from orderinfo a inner join driverinfo b on a.driver_id = b.driver_id;

3.3、子查询优化
  • 子查询就是将一个select的查询结果作为另一个select的查询条件。可以帮助我们实现复杂的查询单效率不高。因为子查询相当于建立一张临时表,外层查询从临时表中查询,查询完毕后,再撤销这些表,消耗大量CPU和IO资源,产生慢查询;临时表并不会产生索引,因此也会影响性能;子查询返回结果越大,查询性能越低。
  • MYSQL中,可以利用表连接查询代替子查询,连接查询不要建立临时表,速度比子查询快,使用索引会更快。
  • 例子:查询driver_id不在orderinfo但在driverinfo中的信息
# 使用in子查询 : type为index
create index y on orderinfo(driver_id);
EXPLAIN select * from driverinfo where driver_id not in (select driver_id from orderinfo);

# 使用表连接 :type为ref 明显要快于子查询
EXPLAIN select d.* from driverinfo d left join orderinfo o on d.driver_id=o.driver_id where o.driver_id is null; 


尽量不要使用in,可以用left join on where is not null代替

3.4、排序优化 order by
  • index排序:保证数据的有序性,不再需要进行排序,效率更高;

  • filesort排序:在内存中排序,占CPU多,若待排序结果大,产生临时文件到磁盘中,效率低。

  • 优化建议:

    • SQL中,在where和order by中都使用索引,where目的为了避免全表查询,order by为了避免使用filesort排序。
    • 尽量使用index完成order by排序;如果两个子句后相同列,使用单索引;若不同,可使用联合索引;
    • 无法使用index时,需要对filesort调优。
  • 1、根据cost排序,输出所有字段

# 创建联合索引
create index index_cost_date on orderinfo(cost,date);
# 查看 按照花费排序的所有字段输出 执行计划
EXPLAIN select * from orderinfo order by cost;

  • 比较上下两个执行计划,上面的虽然创建了索引,但并未使用,因为它要求的是输出所有字段,所以需要回表查询完整的行数据,故MySQL不使用索引而是数据全部加载进来进行排序输出(也有可能是这里数据量太小,索引失效了);而下面的查询只输出了cost,不需要回表查询全部字段,因此根据索引排序直接输出了。
  • 2、根据cost排序,只输出cost字段
# 查看 按照花费排序的 只输出花费 执行计划 
EXPLAIN select cost from orderinfo order by cost;

  • 3、根据cost和date不同顺序和排序大小排序执行计划的区别
# 按照cost,date顺序排序 使用索引
EXPLAIN select date,cost from orderinfo order by cost,date;            Using index
# 按照date和cost排序, date不使用索引,cost使用索引
EXPLAIN select date,cost from orderinfo order by date,cost;            Using index; Using filesort
# 按照cost升序,date降序 cost使用索引,date不使用索引,因为索引默认是cost升序,date升序 方向反了
EXPLAIN select date,cost from orderinfo order by cost,date desc;       Using index; Using filesort
# 按照cost降序,date降序 使用索引,但使用的是反索引,因为索引默认是全升序,这里全降序
EXPLAIN select date,cost from orderinfo order by cost desc,date desc;  Backward index scan; Using index
# 按照cost降序,date升序 cost使用索引,date不使用索引
EXPLAIN select date,cost from orderinfo order by cost desc,date asc;   Using index; Using filesort

根据数据量的不同,可能执行计划结果有所不同

3.5、分组优化 group by
  • 使用原则与order by类似,group by即使没有过滤条件用到索引,可以直接使用索引
  • group by 先排序再分组,遵从最左前缀原则
  • 无法使用索引列时,可以增大max_length_for_sort_data和sort_buffer_size参数设置
  • where效率高于having,能用where替代having的,优先where
  • 减少排序,order by 、group by 、distinct语句耗费CPU
3.6、分页查询优化 limit
  • 一般分页查询时,通过创建覆盖索引能够比较好的提高性能。例如:limit 2000000,10;只需要2000000——2000010的十条数据,其他数据丢弃,查询排序代价比较大。
  • 优化方法
# 在索引上完成排序分页,最后根据主键关联回表查询所需要的其他列
select * from orderinfo a, (select id from orderinfo order by id limit 2000000,10) b
where a.id =b.id 
# 用于主键自增的表,降limit查询转化为某个位置的范围查询
select * from orderinfo where id > 2000000 limit 10
  • limit 1 :当只要一条数据时,使用这个语句节省很多时间;因为它只会扫描第一条数据就停止扫描了。
3.7、count 优化
  • count(1):遍历整张表,但不取值。服务层对于返回的每一行,放一个数字’1’进去,直接按行进行累加。
  • count(*):不会把索引的字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。
  • count(主键):InnoDB引擎遍历整张表,把每行的主键id提取出来,返回服务层。拿到主键后直接进行累加(主键不可能为null)。
  • count(字段):
    • 有not null约束:把每行的字段提取出来,返回服务层。直接按行进行累加。
    • 没有not null 约束:把每行的字段提取出来,返回服务层,服务层判断是否为null,不为null,计数累加。
  • 性能比较:count(*) > count(1) > count(主键id) >count(字段)
3.8、EXISTS 和 IN 的区分
  • 小表驱动大表
# 当表A数据量>表B时,使用in, 因为先进行子查询,B的数据量小,查询速度快一些
select * from A where id in (select id from B)   
# 当表A数据量<表B时,使用EXISTS
select * from A where EXISTS (select id from B where A.id=B.id) 
3.9、select *
  • 尽量不要使用select * ,而是使用select 具体字段。因为sql会通过查询数据字典降”*“按需转化为所有的列名,消耗大量的时间。

explain执行计划字段解释可以参考这里:MySQL高级 之 explain执行计划详解

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/955735.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号