MySql调优

查询优化

查询语句分析

其实就是Explain +查询语句。
like里的% 不在前面索引才会起作用
子查询，不如join，因为子查询会搞个临时表。

mysql调优

b+tree 非叶子节点不存储数据，只存储索引，可以放更多的索引

一般3层树就能存两千行了。16k一个节点，索引可以冗余，叶子节点间用双向指针连接，提高区间访问性能。

hash索引为什么没b+tree索引用的多？

范围查找 hash索引不支持，包括排序

mysql 聚簇索引什么的

慢查询优化：首先看走没走索引

myisam索引和数据文件分离的，非聚集

innodb是聚集的

少建单值索引，多搞一些巧妙的联合索引

分区表很少用，一般都分库分表了

simple是简单查询，其他都是复杂查询

type system或者const不用优化，一般不是all就不用优化了

覆盖索引：查的字段都在索引里

distinct 字段 如果这个字段是索引列，也是可以的，直接在索引树里去重。不然的话，会创建临时表

联合索引，3个字段 第一个字段一定不能少。出现1 3的话只会走第一个字段的索引。第二个是范围，第三个字段也不会走索引，相当于前俩字段走索引了。

like没有前面%的话也可以走索引

不要在索引列上缩任何操作，（计算、函数等）

大于等于也可以走索引，不等于不能走索引。

select * 有的字段不在索引里，而且那个字段又是不需要的，所以比较慢。

覆盖索引：SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。查的几个字段刚好是索引。

非聚簇索引又叫二级索引。

is null is not null也不走索引。

字符串不加单引号，索引失效。

or in 不一定走索引，说不准，大多数不走

age字段有索引，范围查询不一定走索引。这个说不准，可能是范围太大，不一定。

b和c两个单值索引，俩都搜索只有一个索引，走哪一个不确定。

范围查询走不走索引太不固定了。

trace工具分析mysql，用完要记得关闭。比cost的值，同一个sql里。这个用的很少

abc三个字段 order by b也是可以走索引的

有asc 也有desc 不走索引，新版本可能会走。

filesort排序方式：

单路排序：一次性取出满足条件行的所有字段，在sortbuffer中进行排序

双路排序：又叫回表排序模式

分页查询优化 limit 100000， 5 这种性能不好，这种会查前面的数据，然后丢掉，要后面5条。如果主键是自增连续的，可以在前面加个where id>100000 就快很多。

更通用一点的分页 先select id 因为id是索引。然后id in就行了

关联可以拆成多个sql在java里执行。

小表驱动大表，这个mysql有时候会优化，不一定。可以把小表的数据放入buffer里，关联字段要求加索引。

深入mysql的锁和事务隔离级别

表锁：并发度低，不会死锁

行锁：开销大，容易死锁，并发大

最好是二进制安装 或者rpm

Mysql 优化的点在哪里 除了索引还有什么

索引快速的原因是什么

一亿条数据怎么分库分表 怎样设计****sql

什么是行锁和表锁

binlog****是什么

索引有哪些类型 怎样才能创建高性能的索引

查询语句怎么优化

读写锁 排他锁 共享锁

锁粒度： 加锁要消耗系统资源

表锁 对系统资源开销最小 写的时候加锁 别的用户不能读也不能写 读的时候 都可以读 读不阻塞

行锁 最大程度支持并发 也带来很大的锁开销 存储引擎来实现的

Read uncommited 读到事务没提交的数据了 没啥用

Read commited 提交前查一次 提交后又查一次 发现结果不一样

Repeatable read 当前事务读的时候 别的事务插入数据了 会幻读 innodb能解决这个问题

Serializable 串行 没啥用

死锁：

a事务和b事务 互相请求锁定对方的资源

Innodb 解决方式 ：最少行级排它锁的事务回滚 让出来

Innodb 两阶段提交

MVCC多版本并发控制

Innodb默认采用repeatable read****隔离级别 并且用间隙锁策略防止幻读的出现 间隙锁会对索引的间隙行进行锁定

Innodb****基于组簇索引建立的，组簇索引对主键查询有很高的性能，但是二级索引（非主键索引）中必须包含主键列，所以 如果主键列很大的话

其他的所有索引都会很大。主键列推荐6个字节好像。

第四章 schema****与数据类型优化

字段选择：

最小的通常更好、简单就好、尽量避免null 特别是要加索引的列

整数类型

可以设置unsigned属性 标示不允许负值 可以使正值的上限提高一倍

整数指定宽度没啥意义 对存储和计算来说 int(1) 和int(12)没区别

实数类型

这个指的是带有小数的

Decimal适用于 财务数据

数据量比较大的时候可以考虑适用bigint来代替

只需要精确的小数位数乘以相应的倍数就行了，这样能避免浮点存计算不精确 和 decimal精确计算代价太高的问题

字符串类型
varchar

可变长 比定长类型更节省空间 需要使用1或者2个的额外字节来记录字符串长度 255以内是一个字节 超过255两个字节

使用场景：字符串列的最大长度 比平均长度大很多 、列更新很少，碎片不是问题，使用了utf-8 每个字符都是用不同的字节数进行存储。

innodb可以把过长的varchar存储为blob

char

定长 、适合存储很短的字符串或者所有的值都接近一个长度。比如存储密码的md5,经常变更的数据 char类型不容易产生碎片。

字符串分配要根据实际需要分配 避免浪费

Blob

二进制存储

Text

字符方式存储

blob和text尽量避免使用 可以使用别的数据库来存储吧 hdfs mongodb hive?

Sql上优化blog和text 使用substring(column ,length) 将列值转化为字符串，order by里面也可以用，但是要确保截取的字符串足够短

maridb支持微秒级别的时间类型 ，mysql默认不支持 但是可以绕过这种限制。可以使用bigint 或者double存储秒之后的小数部分

Datetime

1001-9999 精度为秒 使用8个字节

Timestamp

1970-2038 使用四个字节

推荐timestamp 因为空间效率高

计数器表

搞100行 random去更新 最后sum 快。

第五章 创建高性能索引

索引可以包含一个或者多个列 ，多列情况下 顺序很重要，索引只能高效的使用索引的最左前缀列。

Btree

btree意味着所有的值都是按照顺序存储的，并且每一个叶子页到根的距离相同。

btree索引适用于全键值、键值范围或者键前缀查找。其中键前缀只适用于根据最左前缀的查找。

btree有效的场景：

全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另外一列。

btree在order by中也可以使用只要order by满足上面的场景。

btree的限制：

如果不是按照索引的最左列开始查找，则无法使用。

不能跳过索引中的列

如果查询中有范围查询 比如like in 右边的列都无法使用索引了

hash****索引

基于hash表实现 只有精确匹配索引所有列的查询才有效。

在mysql中 只有memory引擎显式支持hash索引。

hash索引查找很快 但是有限制：

hash索引只包含hash值和行指针 不存字段值，所以也要读取行。

hash索引不能用户orderby 因为他不是按照顺序存储的。

hash索引不支持部分索引列匹配查找，因为这个是基于所有列算出来的。

hash索引只支持等值比较

有hash冲突 就要遍历链表中的所有行指针，如果hash冲突很多，索引维护的代价就会很高。

不要使用sha1()和md5()作为hash函数，这俩hash值太长 浪费空间 还慢。

空间数据索引 rtree

全文索引

这个是查找文本中的关键词

小表没必要索引 中大型表需要索引 特大表需要分区

高性能索引策略

单列索引

不要做任何的计算 比如 where a-1=4 应该是a=3 有计算 索引就失效了。

像邮箱后缀做索引的话 可以把后缀单独一列 或者前后颠倒一下 就能做索引了

多列索引

Order 和and 也可以用索引了 是因为自带的索引合并策略

在一个多列的btree索引中，索引先按照最左列进行排序 然后是第二列，所以多列索引的顺序很重要，将选择性最高的列放在最左边。

聚簇索引

截止5.5的版本 还不能用

主键自增蛮好，但是高并发下，不太好要用redis生成拿来存

Order by要满足最左前缀

避免重复索引 未使用的索引要删掉 用percona server 或者maridb中打开userstatus变量 让服务正常运行一会儿 然后查询 information_schema.index_statistics 就能查到每个索引的使用频率 或者使用 percona toolkit 的pt-index-usage

Rbo 基于规则优化

Cbo 基于成本的优化 用的比较多

Set profileing=1 之后

Show profiles 看sql 各个部分执行时间 最近要一条的

Mysql 性能监控

8.0之后废弃查询缓存 因为命中率不高

Show profiles 以后要废弃 新版本有了更好的替换方式

Innodb

.frm表定义文件

.idb 数据文件

影响性能的因素

OLTP

OLAP

enum可以存男女什么的 排序是根据创建表的时候的顺序排序的 不是按abc 好像只能modify 能update吗

整型 int(1)和int(10)没区别 但是varchar有区别 不能超出

Mysql 执行计划怎么看：explain 后面是sql就行了

合理使用范式 和反范式：

减少数据冗余 更新快 缺点 需要关联

字符集选择：不建议utf8 utf8中文可能占两个或者3个字节 使用的话可以用utf8mb4

Memory 不能持久化

数据文件和索引文件放一起的叫聚簇索引

Like 前面有% 不能用索引 前面没有 就能走索引

存储引擎：数据文件的组织形式

Mysql 没有物化视图 怎么办

垂直切分：根据业务切分

水平切分：一个表里的数据分段存

通过索引进行优化：

索引好处：

减少服务器要扫表的量

将随机io变成顺序io

回表 ：建索引的时候 比如name 这个最后面叶子节点存储的是主键 ，先查name列B+tree 找到主键 再根据主键的B+tree去找

覆盖索引 ：查数据要遍历两次B+tree ，如果只查id 就只遍历一次B+tree ，没有回表的过程 就叫覆盖索引

最左匹配：组合索引 name,age where 后面name不能跨过去 必须先有最左边的 ，只有一个name可以，这俩顺序没关系 优化器会自动优化

索引下推 ：多个条件的时候 直接在存储引擎 直接过滤掉多个条件 ，以前是一层一层过滤的 前提是这多个条件建了组合索引

谓词下推 ：

索引合并

数据结构网站 Datastrure visualgo geeksforgees 三个网站

索引匹配的方式：

全值匹配

匹配最左前缀 聚合索引 左边的列不能少 abc a少了bc用不成 ac 只能a用 因为缺了b 如果a是范围匹配 则bc失效 比如a>1

匹配列前缀 like’a%’

匹配范围值 a>1

索引精确匹配某一列 和另一列的范围 where name=1 and age>11;

只访问索引的查询 本质就是覆盖索引 比如abc 三个字段建了索引 只select这三列

聚合索引不能跳值 三列 第二列不能跳过

hash****索引

扰动函数

crc32做hash

组合索引主要是要注意索引的顺序 要注意兼顾orderby

聚簇索引 和非聚簇索引：怎么用 顺序插入的话可以用用

前缀索引：把一个字段 前面几个字母搞成索引

Order by 只能是都asc或者都asc

union all,in ,or 这三个都能使用索引 但是推荐使用in 原因：union all 看explain计划是分为两阶段执行 数据量大的时候 in 的效率远远大于or

union all 与union也是推荐使用union all 因为union有个distinct的过程 比较耗时 能用union all 就用union all 前提是对重复数据没要求

exisits 经常是用来替换in的 in也挺好的 为什么要替换呢？外层循环 内层循环 existis里面其实就是个true false 除非和外表连接 到时候练sql吧

范围列可以用到索引 但是范围列后面无法用到索引 比如a>1 and b=3 b写前面 b能用到索引不

mysql索引的书

强制类型转化会全表扫描 mybatis会强制类型转换吗

比如 phone varchar 136 where phone='136’会用到索引 where phone=136虽然能查 但是不走索引了

只有建了索引 才能触发吧 到时候写sql试一下

频繁更新的列不适合加索引

一般区分度百分之八十以上的才需要建立索引 比如sex 只有男女 没必要

计算公式 count(distinct(列名))/count(*)

创建索引的列 最好不要有null

join不要超过三张表 数据类型必须一致

join内部实现机制 有很多种方式

a join b 读数据的时候不一定先读a再读b mysql 会优化 a是驱动表 小表join大表

join最好是索引列

左表连接 会把左表的数据全部查出 右表连接会把右表数据全部查出

如果明确只返回一条 limit 会提高效率 limit用来限制输出

mysql会自动把int和字符串转化

jpa代码也看看 long和int之间怎么转的

单表索引控制在5个以内

组合索引字段数不能超过5个

不要过早优化数据库。

索引优化怎么搞

查询优化

网络慢也有影响

自增锁 间隙锁 读锁写锁 行锁 表锁

结果少 会用索引排序 结果太多 可能就用不到索引排序

Limit 起始值太大 可以用子查询来做

多表关联的时候 不需要查出来所有列

内连接的效率要大于外连接

等价变化规则 a!=4与a>4 or a<4 a!=4效率高

优化count() min() max()

min max

Limit 10000 5 这种优化：

Select *from temp a join(select id from temp limit 10000,5) b on a.id =b.id

子查询建议用关联查询代替

自定义变量有什么用

Timestamp 用4个字节存的int值 只能存到21亿 所以是2038年

分区表：

Create table 加个parrern by 分而治之

分区表 名称前面带# 可以把表分为多个文件存储

分区和分库分表的区别

分区表主要是水平切分

引用场景：

表太大，或者部分热点数据 把热点数据摘出来

好维护批量删 就直接删除分区

能够避免某些特殊的瓶颈（索引互斥访问）

限制 1024 个分区 5.7以后 8196 限制的原因：跟liux相关

如果分区中有主键或者唯一索引的列，那么所有主键和唯一索引的列都要包含进来

从分区文件大小不能区分数据放在哪个分区了

没有适配的分区 数据插入不进去

按天分区 按月分区 按年分区 看官网

分区表无法用外键约束

分区表原理：

更新的时候最好不要更新分区列

服务器参数配置

mysql最大支持连接数 根据硬件