MySQL高级(一)–索引

mysql基础部分增删改那些笔记当时全做在 marginNote思维导图上 现在反而觉得不方便了 … 以后再看看要不要搬过来

高级部分内容挺多的 为了提高 阅读效果 把以前偷懒截得图 大量手动码出来…

linux下安装目录

image-20200716084644409

路径 解释 备注
/var/lib/mysql/ mysql数据库文件的存放路径 /var/lib/mysql/atguigu.cloud.pid
/usr/share/mysql 配置文件目录 mysql.server命令及配置文件
/usr/bin 相关命令目录 mysqladmin mysqldump等命令
/etc/init.d/mysql 启停相关脚本

修改默认的配置文件

5.5 版本 :/usr/share/mysql/my-huge.cnf /etc/my.cnf

mysql的默认 字符集编码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#1. 查看字符集
show variables like 'character%';
show variables like '%char%';

#2. 修改
[client]
password = 123456
port = 3306
default-character-set=utf-8

[mysqld]
port = 3306
character_set_server=utf-8
character_set_client=utf-8
collection-server=utf8_general_ci
# 注意linux下mysql安装完是默认:表名区分大小写 列名不区分大小写 0:区分大小写 1:不区分大小写
lower_case_table_names=1
#设置最大连接数 默认151 mysql服务器允许最大连接数16348
max_connections=1000

[mysql]
default-character-set=utf-8

image-20200716083936475

mysql逻辑架构

image-20200716084908301

和其他数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用,主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离.这种架构可以根据业务的需求和实际需要选择合适的存储引擎.

  1. 连接层

    最上层是一些客户端和连接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的类似tcp/ip的通信.主要完成一些类似于连接处理,授权认证,以及相关的安全方案,在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程.同样在该层上可以实现基于SSL的安全链接.服务器也会为安全接入的每个客户端验证它所具有的操作权限.

  2. 服务层

    第二层架构主要完成大多少的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化以及部分内置函数的执行.所有跨存储引擎的功能也在这一层实现,如过程,函数等.在该层,服务器会解析查询并创建相应的内部解析数,并对其完成相应的优化如确定查询表的顺序,是否利用索引等,最后生成相应的执行操作.如果是select语句,服务器还会查询内部的缓存.如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能.

  3. 引擎层

    存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信.不同的存储引擎具有的功能不同,每个存储引擎都有它的优势和劣势,这样我们可以根据自己的实际需要进行选取. 存储引擎不会去解析SQL,不同的存储引擎之间也不会相互通信,只是简单响应上层服务器的需求

  4. 存储层

    数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互.

存储引擎

如何用命令查看

看你的mysql现在提供什么存储引擎

1
show engines;

看你的mysql当前默认的存储引擎

1
show variables like '%storage_engine%';

MyISAM和InnoDB

对比项 MyISAM InnoDB
主外键 不支持 支持
事务 不支持 支持
行表锁 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作 行锁,操作时候只锁一行,不对其它行有影响,适合高并发的操作
缓存 只缓存索引,不缓存真实数据 不仅缓存索引还缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响
表空间
关注点 性能 事务

image-20200716092424264

性能问题

性能下降sql慢等待时间长执行时间长的原因:

  1. 查询语句写的烂
  2. 索引失效
  3. 关联查询太多join(设计缺陷或者不得已)
  4. 服务器调优以及各个参数设置(缓冲,线程池)

建立索引

1
create index idx_user_name on user(name)

常见通用的join查询

sql执行顺序 机读顺序

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. LIMIT

image-20200716101245294

7种join理论

image-20200716101348735

image-20200716101758253

mysql不支持 full out join

索引

是什么

image-20200908225505396

  • MySQL官方对于索引的定义为: 索引(Index)是帮助MySQL高效获取数据的数据结构

  • 可以简单理解为”排好序的快速查找数据结构”

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

  • 一般来说 索引本身也很大 不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上

  • 我们平常说的索引 如果没有特别说明 一般指 B树(多路搜索树,并不一定是二叉的)结构组织的索引 其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引 统称索引.当然除了B+树这种类型的索引之外,还有哈希索引(hash index)等;

做什么

索引的目的在于提高查询效率,可以类比字典

如果要查”mysql”这个单词 我们肯定需要定位到m 然后往下找y 再找到剩下的sql

如果没有索引,那么可能需要a—-z.

索引优势劣势

优势

  • 降低数据库的IO成本
  • 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

劣势

  • 实际上索引也是一张表 保存了主键和索引字段 并指向实体表的记录,所以索引列也是要占用空间的

  • 虽然索引大大提高了查询速度,但同时会降低更新表的速度,如对表进行INSERT,UPDATE和DELETE

    因为更新表的时候 也要同时保存索引文件每次更新添加索引列的字段 调整因为更新带来的键值变化后的索引信息

  • 索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引或优化查询语句

索引分类

  • 单值索引

    一个索引只包含单个列,一个表可以有多个单列索引

  • 唯一索引

    索引列的值必须唯一,但允许有空值

  • 复合索引

    即一个索引包含多个列

基本语法

创建

1
2
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))

删除

1
DROP INDEX [indexName] ON mytable

查看

1
SHOW INDEX FROM table_name\G

使用ALTER命令

索引结构

mysql索引结构:

  • BTree索引
  • Hash索引
  • full-text全文索引
  • R-Tree索引

BTree检索原理

image-20200716111921590

image-20200716111933515

什么情况建索引 ?

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其它表关联的字段,外键关系建立索引
  4. 频繁更新的字段不适合创建索引 因为每次不单单是更新记录 同时更新索引 io负担加重
  5. where 条件里用不到的字段不建立索引
  6. 单键/组合索引的选择问题 :高并发下倾向于创建组合索引
  7. 查询中排序的字段,排序字段如果通过索引去访问将大大提高排序速度
  8. 查询中统计或者分组字段
  9. 表记录太少 不建立索引
  10. 经常增删改的表 不建立索引
  11. 数据重复且分布平均的表字段不建立索引 如果某个数据列包含大量重复内容 建索引没有太大实际效果(性别)

资料参考: 尚硅谷