MySQL学习笔记(五)

作者:神秘网友 发布时间:2021-02-23 13:56:05

MySQL学习笔记(五)

倒数第二天!冲冲冲!!!

一、索引

一个表里面可以有多个索引。

1. 索引的作用:约束与加速查找

  无索引:从前到后依次查找

  有索引:会为索引列创造一个额外文件(以某种格式存储)。在使用索引进行查找时,会优先在该文件里面进行查找,所以查询时很快。——因此也会占据硬盘的空间。

  不足:索引查询快,但是对索引进行更新和删除时慢。

  命中索引:对索引的正确引用才能加速查找。select * from student where sid = 1

2. 索引类型

某种格式——   

  hash索引:索引表。在索引表中将数据以“哈希值”进行存储,同时保存该数据存储地址。需要注意的是,在索引表中,哈希值的排列顺序与原来表中的数据顺序是不一样的。

  缺点:因此在hash表中如果查找where id 3 之类的数据,即使id列为索引列,其查找速度在大数据的情况下也会变慢,因为hash中的排列时无序的,不能一顺溜的取出来。简而言之,查找连续的数值很慢。

  优点:查找单值速度非常快。

  btree索引(常用):将值转化为数字,以二叉树的形式进行存储(按顺序存储)。——(在innodb中使用的是btree)

  a. 普通索引:加速查找(没有唯一性的限制)

   创建索引:create index ix_name on tiny_dataemail(ix_name为索引名称)

   删除索引:drop index ix_name on tiny_data

   查看索引:show index from tiny_data;

   在创建表时创建索引:

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)
创建表+索引

   对于创建索引时如果是BLOB和TEXT类型,必须指定length:create index ix_extra on in1(extra(32))

  b. 主键索引:加速查找+不能为空+不能重复 (就是创建时的primary key)

   在建表之后创建主键索引:alter table 表名 add primary key(列名)

  c. 唯一索引:加速查找+不能重复(可以为空)

   创建索引:create unique index 自定义索引名称 on 表名(列名)(就是多加个unique)

   删除索引:drop 自定义索引名 on 表名

   在创建表同时创建索引:(注意此时关键词是 unique 不是 unique index)

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)
创建表+索引

  d. 组合索引(多列组合成一个索引):联合普通索引,联合主键索引,联合唯一索引

   创建索引:create unique index 自定义索引名称 on 表名(列名,列名)

   删除索引:drop 自定义索引名 on 表名

   最左前缀匹配:假设 设立sid 与 sname 都是 student 表的索引。create index ix_sid_sname on student(sid, name)

select * from student where sid = 1;                         -- 可以在索引文件中执行,使用索引
select * from student where sid = 1 and sname = "alex";      -- 可以在索引文件中执行
select * from student where sname = "alex";                  -- 不可以在索引文件中执行

3. 索引补充

(1)覆盖索引:(名词解释)在索引文件中可以直接获取数据(指索引值)。如select sid from student where sid = 1(在sid为student表的索引的情况下)。

(2)索引合并:(名词解释)把多个单列索引合并使用。如select sid, sname from student where sid = 1 and sname = 'alex (在sid与sname都是索引的情况下)。不过虽然“索引合并”使用起来比“联合索引”方便,但是“联合索引”效率更高。

二、命中索引(重点***)

索引内容参考——武沛齐博客

1.正确使用索引

(1)避免使用“%like%”语句进行查询,因为速度非常慢。

解决办法:使用第三方工具,搜集分词结果并存储该分词所在ID(索引),在查询时程序会优先在第三方工具的分析结果中进行查询,获取ID后再使用索引查询。

(2)避免使用函数

(3)or:查询条件是(索引列 or 非索引列)的组合时,会导致索引失效。

(4)类型不一致:如果列是字符串类型,传入条件时必须用引号引起来,否则会执行一个类型转换过程,使查询速度变慢。

(5)普通索引使用“!=”进行查询时,无法命中索引,即没有使用索引查询。Especially, 主键除外,如果是对主键使用“!=”进行查询,还是会使用索引进行查询。

普通索引使用“”进行查询时,无法命中索引,即没有使用索引查询。Especially,如果是主键或索引是整数类型,则还是会走索引。

“order by”当根据索引排序时候,选择的映射如果不是索引,则不走索引。如select email from tb1 order by name desc;。Especially,如果对主键排序,则还是走索引。

2.其他注意事项(1,2,3,9最基础)

(1)避免使用select *

(2)count(1)或count(列)代替count(*)

(3)创建表时尽量时 char 代替 varchar

(4)表的字段顺序固定长度的字段优先

(5)组合索引代替多个单列索引(经常使用多个条件查询时)

(6)尽量使用短索引。可以创建局部索引

create index ix_name on tb1(title(16))
-- 表示用title的前16个字符做索引

(7)使用连接(JOIN)来代替子查询(Sub-Queries)——不过这个在MySQL中是一样的,在SQLServer中有差别

(8)连表时注意条件类型需一致

(9)引散列值(重复少)不适合建索引,例:性别

三、MySQL执行计划

执行计划:让MySQL预估执行操作需要的时间。

#输入,假设索引是(id, email)
explain select * from tiny_data; --没有走索引(all)
explain select * from tiny_data where id = 3; -- 主键索引(const)
explain select * from tiny_data where email = "alex@qq.com"; -- 普通索引(ref)

预估时间判断——查看type(仅供参考)

type:all(全表扫描)——没有走索引,速度很慢

   const(走了主键索引)——速度很快

  ref(走了索引)——速度很快

 查询时的访问方式,性能:all  index  range  index_merge  ref_or_null  ref  eq_ref  system/const

四、DBA工作

1. 慢日志:在服务端进行设置

    a.执行时间过慢,如大于10秒

    b.未命中索引

    将以上记录记录到指定的日志文件路径。

2. 配置

  (1)基于内存

  查看当前配置信息:show variables like '%query%'

  在配置信息的结果中重点关注:

slow_query_log = OFF                              -- 是否开启慢日志记录
long_query_time = 2                               -- 时间限制,超过此时间,则记录
slow_query_log_file = /usr/slow.log               -- 日志文件路径
log_queries_not_using_indexes = OFF               -- 为使用索引的搜索是否记录

  修改当前配置:set global 变量名 = 值

  (2)基于配置文件

  a.自建配置文件:在启动服务端的时候设置配置文件路径,mysql慢日志就会使用配置文件中的配置

  mysqld --defaults-file='D:\my.conf'

  b.在已有的自带的配置文件下进行更改(my-default.ini)。(我没有找到这个文件,,,)

  ps:修改配置文件后,需要重启服务。(在修改前记得备份,如果修改后出了问题,可以还原)

五、MySQL分页性能相关方案(重点***)

limit分页时,如(limit 30,10),是先扫描40条数据,再取最后十条,所以越往后,需要扫描的数据量越大,会越慢。

解决方案:

1.设定允许查询的页数

2. a.从索引表中进行扫描,实行覆盖索引 b.从覆盖索引中查找 (下面这个mysql用不了)

select * from tiny_data where id in (           -- 利用索引进行查找
    select id from tiny_info limit 20000, 10    -- 覆盖索引
)

3.最优方案(单纯基于数据库的最优操作):

  记录当前页的最大与最小id(假设每页记录条数为10)

  (1)上一页/下一页

#min_id, max_id
-- 下一页
SELECT * FROM userinfo3 WHERE id  max_id limit 10
-- 上一页
SELECT * FROM userinfo3 WHERE id  min_id ORDER BY id desc limit 10

  (2)跳转型:上一页 192 193 194 【195】 196 197 198下一页

SELECT * FROM userinfo3 WHERE id in (
(SELECT * FROM userinfo3 WHERE id  max_id limit 30) as N ORDER BY N.id DESC LIMIT 10
)
-- 这么讲了一通,理解了大致操作,但是没有实操

MySQL学习笔记(五) 相关文章

  1. Elasticsearch分布式检索

    前言 本篇笔记主要简单记录一下Elasticsearch在分布式环境中是怎么执行的(Elasticserch权威指南读书笔记) 查询阶段 在初始查询阶段时,查询会广播到索引中每一个分片拷贝(主分片或者副本分片)。每个分片在本地执行搜索并构建一个匹配文档的优先队列。 这

  2. Asp.Net Core学习笔记:(二)视图、模型、持久化、文件、错误处理、日志

    TagHelper 入门 优点:根据参数自动生成,不需要手写超链接,类似Django模板里面的url命令。 在ViewImport中添加TagHelper @addTagHelper *,Microsoft.AspNetCore.Mvc.TagHelpers 比如,链接TagHelper使用 a class="btn btn-outline-primary" asp-controller

  3. Mysql Utilities

    MySQL Utilities 高可用工具体验 简介: MySQL Utilities 高可用工具体验 MySQL Utilities是MySQL官方的工具集,其中包括高可用相关的几个工具。 以下是对当前最新版本1.6的使用体验。 MySQL Utilities 高可用工具体验 MySQL Utilities是MySQL官方的工具集,

  4. [剑指offer] 11-23做题笔记

    JZ11 二进制中1的个数 题目描述 输入一个整数,输出该数32位二进制表示中1的个数。其中负数用补码表示。 示例1 输入 10 返回值 2 思路 直接比较 使用String类的 toBinaryString 函数将数字转换为二进制字符串,将字符串直接拆分后逐个与1进行比较。 public c

  5. mysql 设置double列累加

    最近做工厂项目,测试提的一个bug在本地测了好久一直没复现;直接连测试线的数据库,又经过一系列流程模拟最终在本地复现了这个问题;由于有消息日志的定时输出,只能打点介入来追踪bug,最后发现问题出在对double列累加的SQL语句上,真是一顿好找。 百度‘m

  6. Python3 --pymysql模块

    一.安装 #安装pip3 install pymysql PyMySQL 安装在使用 PyMySQL 之前,我们需要确保 PyMySQL 已安装。PyMySQL 下载地址:https://github.com/PyMySQL/PyMySQL。如果还未安装,我们可以使用以下命令安装最新版的 PyMySQL:$ pip3 install PyMySQL如果你的系

  7. Nodejs 学习笔记和实例【 一篇全覆盖:写后端 api 接口完全够了】

    本文pdf下载 Nodejs学习笔记 Node.js官方API文档 一、基础知识 1. 命令行窗口 dir命令:列出当前目录下所有的文件夹名字; md命令:创建一个文件夹; rd命令:删除一个文件夹; path环境变量的作用:当我们在命令行窗口中使用命令打开一个文件或者调用一个程

  8. SQL注入(execute()之下)

    在用pymysql模块操作验证注册登陆的时候,涉及字符串拼接时容易出现明明账户密码不对的情况sql语句还是可以被执行,这样就导致用户可以绕开账户密码就能进入数据库。废话不说,看图: sql代码为: user = input('账户:').strip() pwd = input('密码:').str

  9. 《面向对象》读书笔记3

    第三章 理解OOP:编程语言的历史 机器语言、汇编语言、高级语言、结构化编程、GOTO语句、全局变量、局部变量 本章介绍OOP出现之前的编程语言。 OOP结构非常简练,但另一方面也非常复杂,因此理解其结构和用途并不简单。不过,理解OOP也由捷径可循,那就是先

  10. deepin20.1系统安装MySQL8.0.23(超详细的MySQL8安装教程)

    下载MySQL-8.0.23 点击下载:mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz 解压MySQL的安装包到一个目录(这里我选择的是:/usr/local) 1 sudo tar -Jxv -f /home/×××shanlin/Downloads/mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz -C /usr/local/ 注意

每天更新java,php,javaScript,go,python,nodejs,vue,android,mysql等相关技术教程,教程由网友分享而来,欢迎大家分享IT技术教程到本站,帮助自己同时也帮助他人!

Copyright 2020, All Rights Reserved. Powered by 跳墙网(www.tqwba.com)|网站地图|关键词