typora-root-url: 复习文件\数据库索引优化\inages
SQL语言共分为四大类:数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL。
druiddatasource druid的连接池
combopooledDateSource c3p0的连接池
driverManagerDataSource 默认的连接池
mysql系统体系架构: 1.连接池 数据源 2.管理服务和工具组件:系统管理和控制工具 例如 备份恢复,Mysql复制,集群等 3.sql接口:接受用户的sql命令,并且返回用户查询的结构 存储引擎:存储逻辑数据结构 4.查询解析器: SQL命令传递到解析器的时候会被解析器验证和解析(权限。语法校验);
5. 查询优化器: SQL语句在查询之前会使用查询优化器对查询进行优化 a.select id, name from user wher age =40; b.先根据where语句进行选取,而不是先将表全部查询出来在进行age过滤 c.select 查询先根据id 和name 进行属性映射,而不是将属性全部取出进行过滤 6缓存 如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据 7.插拔式存储引擎 :存储引擎说白了就是如何管理操作数据(存储数据,如何更新,查询数据等)的一种方式。 因为关系型数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(存储和操作此表的类型) **存储引擎类型**: **innoDB:支持行级锁 transactionas foreign keys 比myisam存储更占用磁盘空间,**(索引:B+Tree 数据结构) **myISAM :只支持表级锁 访问速度快,因此当对事务完整性没有要求并且以访问为主的应用适合使用该存储引擎**(索引:B+Tree 数据结构) **memory:存储在内存中 因此访问速度快,但是在安全上没有保障,适合用于快速的访问临时表。****(Hash 索引 ) 8.物理文件: 物理文件包括:日志文件,数据文件,配置文件,pid文件,socket文件等 日志文件:bin log 二进制日志文件 记录增删改或者表级修改记录 DDL DML Relay_log :中继日志: 主从复制的 从服务器的io线程定时读取主服务器的日志文件, 将变化的信息记录在中继日志中,从服务器的sql线程会读取中继文件的变化,将其重放为SQL语句回或者行数据 进行主从复制 错误日志:排错 slow log :慢查询日志: 调优 查询时间超过指定值
存储引擎概念:
使用不同的机制存取表文件,包括存储方式,索引技巧,锁定水平等,这些不同的技术以及配套的功能成为存储引擎。
如何查看数据库支持的搜索引擎 show engines;
InnoDB存储引擎逻辑存储结构:**(mqsql5.5版本后默认使用innodb)** 表空间-> 段 -》 区 (大小为1M)-》 页 (mysql存储数据的最小单元 大小为16k) ——》行数据 row
一个区中存储64个页
索引: 是帮助MYSQL高效获取数据的数据结构(有序)
优缺点: 提高查询效率 降低增删改的效率 索引不是建立的越多越好 (索引是一种数据结构)因为在DML的过程中会导致数据结构重新建立 ,导致效率降低
B- tree B+Tree 二叉树 degree 度数为3的就是二叉树 度数为每个节点存储的个数为(度数-1)
(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210829094344048.png)
B-tree和B+tree的区别:
1)所有的数据都会出现在叶子节点
2)叶子节点形成一个单向列表
3)减少磁盘的io次数
MYSQL为什么选择B+Tree数数据结构?
二叉树: 容易形成歪脖子数
红黑树: 数据量大的数层级深
B-Tree :每个节点不仅包含key值,还有数据,会增加查询数据时的IO次数。
不利于范围搜索(B+tree数据都存储在叶子节点,利于范围搜索)
B+Tree:非叶子节点只存储key值
所有的数据存储在叶子结点
所有的叶子节点之间有连接指针
mysql的索引数据结构相对经典的 B+tree进行优化,在原有的B+tree的基础上,增加一个执行相邻叶子节点的链表指针,就形成了有顺序指针的B+tree,提高区间访问的性能。(可以进行范围查询)
B+Tree数据结构好处:
减少io次数 提高查询效率 数型结构较小
所有的
索引类型:
2.按照属性分:
主键索引:针对表中的主键创建的索引,默认自动创建,只有一个, 关键字primary key
唯一索引:避免同一列数据的值重复 unique 可以有多个
普通索引: 快速定位特定数据 可以有多个
全文索引: 类似于es 对文本数据进行分词 FULLTEXT
3.按照存储类型分:
聚簇/聚集索引: 将数据存储与索引放在一块,索引结构的叶子节点保存行数据 (必须有,而且只有一个)Clustered Index
非聚簇索引/二级索引: 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键,(可以存在多个) Secondard Index
聚簇索引必须有:主键----》第一个唯一索引---》伪劣rowId
聚簇/聚集索引结构:
kafka为什么会吞吐量这么大
explain:属性
id: 查询执行的顺序: 值越大越先执行
select_type : 查询的类型;主要区别普通查询,还是联合查询,还是子查询 的复杂语句;
SIMPLE: 查询中不包含子查询或者UNION(联合查询语句)
查询中若包含任何复杂的子部分,最外部的查询被标记为;PROMARY
在SELECT或者WHERE 列表中包含了子查询,该子查询Hier标记为:SUBQUERY;
table: 查询的表;
type: 访问类型
all, index , range, ref, eq_ref, const, system, null
从左往右性能越好
1.All ;全表扫描;
2.index: 扫描全部的索引数; 比all快一些但性能还是低
3.range: 扫描部分索引,(范围查询 )索引范围扫描,对索引的扫描开始与某一点,返回匹配值域的行,常见于between、 in , notint 等查询
4.ref: 使用非唯一索引或非唯一索引前缀进行查询
5.eq_ref: 唯一性索引扫描,对于每一个索引建,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
6.const,system: 单表中最多有一个匹配行,查询起来非常村塾,例如根据主键或者唯一索引查新,system是const的特例,当查询的表只有一行的情况的下,使用system;
在sql启动的时候将一些系统文件添加到缓存中,查询就是system
select * from mysql.time_zone;
7.NULL: 不用访问表或者索引的查询 一些系统 信息的查询
select now();查系统时间不走表;
possible_keys: 表示查询时可能使用的索引。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引
key: 显示MySQL实际决定使用的索引。如果没有索引被选择,是NULL
key_len: 使用到索引字段的长度
ref: 显示哪个字段或常数与key一起被使用
rows: 这个数表示mysql要遍历多少数据才能找到,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,在innodb上可能是不准确的(扫描行的数量)
Extra: 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。
-
Using index:表示使用索引,如果只有 Using index,说明他没有查询到数据表,只用索引表就完成了这个查询,这个叫覆盖索引。
-
Using where:表示条件查询,如果不读取表的所有数据,或不是仅仅通过索引就可以获取所有需要的数据,则会出现 Using where。
索引优化:
最左前缀法则:应用于复合索引,在where语句中,(与查询书写顺序无关,只要索引不中断,都可以使用)
1)(当都是 等号 连接 的查询) 最左法则不关心复合索引书写的顺序 ,mysql优化器会自动进行排序 ,(只要索引是连续的就可以生效,ab ,ba, abc,cab 等都可以生效)
2)范围查询:范围查询条件不加等号的 右边的列,不能使用索引
最左优先,以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)则范围查询后面就会停止匹配(与书写的顺序无关)
例如:select * from tb_seller where id = 1 and status >10 and name = '张三';
(那么 status的索引也会生效,status后面的索引失效 )最好带上等号那么后面的就可以生效(status >= 10)
3)索引列运算:substring(列名,第几个开始切,切几位)
select * from th_seller where substring(name,3 ,2 )= '科技';索引失效
4)字符串不加单引号(该列索引失效)
select * from tb_seller where name= '小米科技' and status = 1;
5)or连接的条件
用or分割开的条件。如果or前面的条件中的列有索引。而后面的列乜有索引,那么不走索引 (or是或者的意思,并集 后面的没有索引 就会全表扫描)
sql优化:
1) 查看Sql中的查询 和 索引的顺序, 是够满足最左前缀法则。
2)范围查询的时候 ,在索引列上把等号加上
3)查询的时候。尽量减少在索引列进行运算(java代码的运行在毫秒级比数据库快,不要再数据库进行运算)
4)如果查询的字段是字符串类型,必须加上引号
5)使用or进行查新 ,要想使用索引,要保证or前后的字段都有索引 否则索引失效
5.1)多个单列索引进行联合使用or 当数据量不大的情况的mysql的优化器会进行评估,可能会进行全表扫描比使用联合索引快**(会出现索引失效的情况)**
6)做模糊查询的时候,尽量不要使用前百分号
7)某列字段数据重复度较高,不建议创建索引 如性 城市列 ,当重复度较高是,索引会失效
mysql会评估使用索引比全表扫描man,则不会使用索引。
7)数据分布会对索引影响,索引是用来检索少量数据 (is not null 、is
null 在执行select 之前mysql会评判走索引还是全表扫描快 ,可能会导致不走索引, 故不建议对该列创建索引,会导致增删改 效率降低)
insert :批量添加
主键按照顺序查入: 顺序写 有效避免页分裂
select 后面不写* 号 会造成回表查询
MySQL优化
###学习目标
MySQL-体系结构
MySQL-存储引擎
MySQL-索引
MySQL-执行计划
MySQL-索引的使用
MySQL-常见SQL优化
MySQL-体系结构
MySQL-存储引擎
-
和大多数的数据库不同,MySQL中有一个存储引擎的概念,针对不同的存储需求可以选择最优的存储引擎。
-
存储引擎就是存储数据,建立索引,更新查询数据等等技术的实现方式。存储引擎是基于表的,而不是基于数据库的。所以存储引擎也可被成为表类型。
-
如何查看数据库支持的存储引擎: show engines;
-
InnoDB和MyISAM的区别?
-
InnoDB存储引擎逻辑存储结构
-
Tablespace:表空间
-
Segment:段
-
Extent:区(1M)
-
Page:页(16K)
-
Row:行
MySQL-索引
-
索引概述
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。
索引如何提升查询速度?
未建立索引的查询方式:
建立索引的查询方式:
-
索引的优缺点
提供查询效率,降低增删改的效率,索引不是建立的越多越好
-
索引结构
生成树的网站地址: https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
什么是B-Tree?
什么是B+Tree?
B-Tree和B+Tree的区别:
-
所有的数据都会出现在叶子节点
-
叶子节点形成一个单向链表
MySQL的索引数据结构对经典的B+Tree进行优化。在原B+Tree的基础上,增加一个执行相邻叶子节点的链表指针,就形成了代有顺序指针的B+Tree,提高区间访问的性能。
MySQL为什么需要选择B+Tree呢?
二叉树:容易产生歪脖子树
红黑树:数据量大的话层级深
B-Tree:不利于范围搜索
-
索引分类
按照属性分:
按照存储类型分:
官方给的名称为: Clustered Index 和 Secondard Index
聚簇/聚集索引必须有:主键->第一个唯一索引->伪列rowid
聚簇/聚集索引的结构:
InnoDB是基于索引来存储数据的
非聚簇索引/二级索引的结构:
-
索引的语法
查看索引
show index from tableName
删除索引
drop index indexName on tableName
创建索引
create [UNIQUE|FULLTEXT] index idx_tableName_column on tableName(column,...)
思考:
哪些表需要建立索引?
数据量大(几十万上百万以上)
创建单键索引还是复合索引?
复合索引
到此索引相关的知识就已经介绍完了,接下来,看一个面试题:
一张表,有四个字段(id,username,password,status),由于数据量大,需要对以下SQL语句进行优化,该如何进行才是最优的方案:
select id,username,password,status from tb_user where username = 'itcast'
MySQL-执行计划
通过EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。
重点
-
id: select查询的序列号,是一组数字,值相同由上往下执行,值不同越大越先执行
-
select_type: SELECT的类型
-
SIMPLE(简单表,不使用UNION或子查询等)
-
PRIMARY(主查询,即外层的查询)
-
UNION(UNION中的第二个或者后面的查询语句)
-
SUBQUERY(子查询中的第一个SELECT)
-
......
-
-
table: 输出结果的表
-
type: 表示表的连接类型,数据库引擎查找表的一种方式,有人将其称之为访问类型。
-
常用的类型有: ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好)
-
NULL: 不查询表的SQL
-
system: 系统表,少量数据,往往不需要进行磁盘IO
explain select * from mysql.time_zone;
-
const:常量连接,一般根据主键索引或唯一索引来查询
-
ref_eq: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配
-
ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
explain select * from tb_dept where name='人事部'; //根据索引字段搜索,但是索引字段不是主键也不是唯一索引
-
range:只检索给定范围的行,使用一个索引来选择行
explain select * from tb_dept where id between 1 and 10; // in not in
-
index: 扫描全表的索引,比ALL快一些
select * from tb_dept order by id;
-
ALL:全表扫描
select * from tb_dept;
-
-
possible_keys: 表示查询时,可能使用的索引
-
Key: 表示实际使用的索引
-
key_len: 索引字段的长度
-
Rows: 扫描行的数量
-
Extra: 执行请求说明和描述
MySQL-索引的使用
-
环境准备
create table `tb_seller` ( `sellerid` varchar (100), `name` varchar (100), `nickname` varchar (50), `password` varchar (60), `status` varchar (1), `address` varchar (100), `createtime` datetime, primary key(`sellerid`) )engine=innodb default charset=utf8mb4; insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00'); insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00'); insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00'); insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00'); insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00'); insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00'); insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00'); insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00'); insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00'); insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00'); insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00'); insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
-
最左前缀法则
如果索引了多行,要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且跳过索引中的列。
A B C
A B C 查询的时候,会走索引么? v
A B 查询的时候,会走索引么? v
A 查询的时候,会走索引么? v
A C 查询的时候,会走索引么? v
B C 查询的时候,会走索引么? x
C 查询的时候,会走索引么? x
C B A 查询的时候,会走索引么? v
如果符合最左法则,但是出现跳跃某一列,只有最左索引生效:
1.1
explain select * from tb_seller where name ='小米科技'; v
1.2
explain select * from tb_seller where name ='小米科技' and status='1'; v
1.3
explain select * from tb_seller where name ='小米科技' and status='1' and address='西安市'; v
1.4
explain select * from tb_seller where status='1'; x
1.5
explain select * from tb_seller where status='1' and address='西安市'; x
1.6
explain select * from tb_seller where name ='小米科技' and address='西安市'; v [name]
1.7
select * from tb_seller where status = '1' and address='北京市' and name='小米科技' v
-
范围查询
范围查询右边的列,不能使用索引
2.1
explain select * from tb_seller where name ='小米科技' and status >'1' and address='西安市'; explain select * from tb_seller where name ='小米科技' and status >='1' and address='西安市';
2.2
explain select * from tb_seller where status > '1' and address='西安市' and name ='小米科技' ;
-
索引列运算
3.1
explain select * from tb_seller where substring(name,3,2) = '科技'
-
字符串不加单引号
4.1
explain select * from tb_seller where name ='小米科技' and status=1;
-
or连接的条件
5.1
explain select * from tb_seller where sellerid='alibaba' or sellerid='baidu';
5.2
explain select * from tb_seller where sellerid in('alibaba','baidu');
5.3
explain select * from tb_seller where sellerid='alibaba' or nickname='千度小店';
5.4
explain select * from tb_seller where sellerid='alibaba' or name='小米科技';
小结: 用 or分隔开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么不走索引
-
模糊匹配
6.1
explain select * from tb_seller where name like '小米科%';
6.2
explain select * from tb_seller where name like '%米科技';
6.3
explain select * from tb_seller where name like '%米科%';
-
数据分布影响
create index idx_seller_addr on tb_seller(address);
7.1
explain select * from tb_seller where address='北京市';
7.2
explain select * from tb_seller where address='西安市';
结论:MySQL会评估使用索引比全表更慢,则不使用索引。
-
is null / is not null
索引是来索引少量数据。
8.1
explain select * from tb_seller where name is null
8.2
explain select * from tb_seller where name is not null
小结: is null , is not null 有时索引失效
-
覆盖索引
尽量减少使用select *
为什么?
防止出现回表查询。
MySQL-常见SQL优化
-
insert优化
1.1批量删除
insert into table values(); insert into table values(); .... insert into table values(),(),...
1.2手动提交事务
mysql数据库,特点: 增删改的时候,会自动提交事务 start transaction insert... commit;
1.3主键顺序插入
主键乱序插入 6,1,2,33,5,88,3,7,4,8,9 主键顺序插入 1,2,3,4,5,6,7,8,9,33,88
-
大批量插入数据load
创建表:
create table tb_user ( id bigint primary key auto_increment, username varchar(35), password varchar(100), name varchar(35), birthday date )
准备数据:
public class MyTest { public static void main(String[] args) throws Exception{ File file = new File("C:\\Users\\Windows\\Desktop\\data.txt"); FileWriter fw = new FileWriter(file); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); for (int i = 0; i < 1000000; i++) { fw.write((i+1)+",'TOM"+i+"','123456','张"+i+"','"+sdf.format(new Date())+"'\n"); } fw.close(); } }
MySQL导入数据:
load data local infile 'C:/Users/Windows/Desktop/data.txt' into table tb_user fields terminated by ',' lines terminated by '\n';
主键顺序插入比乱序插入的性能高。
为什么呢?
顺序写,可以有效避免页分裂
-
order by
3.1 Using filesort: 第一种是通过对返回数据进行排序,也就是通常说的filesort排序,所有不是通过索引直接返回排序结果的排序都叫FileSort排序
3.2 Using index: 第二种是通过有序索引顺序扫描直接返回的有序数据,这种情况即为using index,不需要额外排序,操作效率高
环境准备:
CREATE TABLE `emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `age` int(3) NOT NULL, `salary` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300'); insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500'); insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800'); insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500'); insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200'); insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300'); insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700'); insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500'); insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400'); insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100'); insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900'); insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500'); create index idx_emp_age_salary on emp(age,salary);
3.3排序优化
explain select * from emp order by age
explain select id from emp order by age;
explain select id,age from emp order by age; V explain select id,age,salary from emp order by age; V explain select id,age,salary,name from emp order by age; X
-
group by 优化
drop index idx_emp_age_salary on emp;
select age ,count(*) from emp group by age;
上述查询方式,会默认进行排序,影响性能,可以进行如下优化:
select age ,count(*) from emp group by age order by null;
但是如果需要排序呢?
可以给分组的字段建立索引.
...............