Mysql调优小总结

profile

说明:profile可以查看sql具体步骤执行时间

# 开启profile
set profiling=1；

# 查询所有sql执行明细
show profiles 

# 查看最后一条sql具体步骤执行时间,for query 2 表示Query_ID为2的语句
show profile （for query 2）

# 查看所有 all表示所有执行参数, cpu表示与cpu相关的参数
show profile all # cpu

数据类型

1.尽量使用最少的空间存储数据,如能用tinyint不用int
2.数据格式类型简单就行,例如IP可以使用INET_ATON和INET_NTOA函数

3.整数类型:bigint(64)/tinyint(8)/int(32)/smallint(16)/mediumint(24)
4.char:
最大255，会自动删除末尾空格，效率比varchar高
一般存储密码或者短字符串
5.varchar
存储长度波动很大的数据/存储字符串很少变更的场景/多字节字符
6.text/blob实际中很少用
7.date:占3个字节,时间区间:1000-01-01—9999-12-31
8.timestamp:占用四个字节,精确到秒,整形存储
依赖数据库设置的时区,时间区间:1970-01-01至2039-01-19
9.datetime:占用八个字节,与时区无关(底层时区配置无效)
可以保存到毫秒,可保存的时间范围大
注意:!!!不要使用字符串存储时间，不仅占用空间还破坏函数便捷性
10用枚举类型代替字符串

create table test(e enum (‘女’，’男‘));
select e+0 from test;

合理使用范式与反范式

数据库三范式:列不可分,不能存在传递依赖,表的其他列必须唯一的依赖于主键
数据库范式最初是为了解决数据冗余
但是实际中要在表中加一些冗余字段,这样可以避免:
(1)关联查询带来的效率过低
(2)也可以利于实际排序

主键

数据库除了要有业务主键(例如:客户号,openid等),一定也要有代理主键(id,之前搞过一个项目,oracle数据库,没有id之前sql是0.5s,加上之后是20ms,别看都是1s之下,并发高了就差的太多了)

字符集

能使用latin1的尽量使用,以为linux中latin1占的空间少呀,
如果非使用u8的时候一定要用utf8_mb4

储存引擎engine

默认innodb
InnoDB
数据和索引放在一起
B+树第三层存储的是实际数据
聚簇
有覆盖索引
MyISAM
B+树第三层存储的是地址，多了一次IO
非聚簇
有覆盖索引
memory
用的hash散列
基于内存非常快，不能进行持久化

执行计划

1.sql语句前加explain就是和查看执行计划

explain select * from test;

2.执行顺序

id值越小越先执行,相同的id值从上往下执行,图中就是从上往下执行
3.属性值

select_type sql类型
type 类型，以何种方式访问数据
	system > const(匹配行) > ref > range(范围) > index(全索引扫描) > all
table 表名
possible_keys 可能用到的主键
key 用到的索引
key_len 越小越好;索引越长,io量越大
ref
	显示索引那一列被引用了，是一个常数
	const表示常量值
rows 预估值
extra
	出现并不太好
	using filesort/using where/using index(索引覆盖)/using index condition

索引

B树 , B+树 , B*树
B树 :

mysql读取数据用的是磁盘预读,一般是页(4K)的整数倍,默认读取16K,如果每条数据1K大小(忽略p和键值的空间占用),3层B树总共4000+数据
B+树 :

假设p每个键值占用10B空间第一层就会1600个位置,同理第一层至第二层就会有1600^2的空间,3层B+树的数据量会是4000w+, Mysql默认使用B+树
B* 树 :
B*树是在B+树的基础上非叶子节点之间增加横向指针
Myisam和innodb在B+树上的区别
Myisam第三层节点存储的是记录在磁盘文件中的地址
Mysql创建索引的顺序
主键->唯一键->row_id
索引的优点

1.减少数据库需要扫描的数据量
2.帮助服务器避免排序和临时表
3.将随机io变为顺序io

索引的用处

1.快速查找匹配where子句中的行
2.使用最少行的索引
3.左前缀查找行
4.当有表连接的时候,从其他表检索行数据
5.查找特定索引列的min和max的值
6.如果排序或者分组时可用索引的最左前缀完成的,则对表进行排序和分组
7.可以优化查询以检索数据值而无需查找数据行

索引的类型
主键索引
唯一索引 : 默认建立的索引是给唯一键建立的
普通索引
全文索引
组合索引
名词

回表:从name列的B+树找到主键(1)，再从主键的B+树找到最终的数据(2)
覆盖索引:select id ，id的值在回表的第一步就已经有了，就不需要第二步了，就叫覆盖索引
最左匹配:多个列创建的联合索引,会根据从左往右的顺序匹配索引,当所有字段被用到时,where条件的字段顺序不会影响索引的使用
索引下推:有组合索引的前提下,取数据的时候进行数据的过滤(存储引擎层进行判断)
谓词下推:取数据的时候进行数据的过滤

索引的匹配方式

全值匹配
最左前缀匹配
匹配列前缀
匹配范围值
精确匹配某一列，并范围匹配另一列
只访问索引的查询

聚簇索引与非聚簇索引(数据存放方式)
聚簇索引 : 数据行跟相邻的键值紧凑的存储在一起
非聚簇索引 : 先关掉索引，导入数据，打开索引，防止索引更新
覆盖索引
1、不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引
2、如果个素引包含所有需要查询的字段的值，我们称之为覆盖索引
3、不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值
优化细节

1.当使用索引列进行查询的时候，尽量不要使用表达式，应该把计算放到业务层
2.尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询
3.使用前缀索引
	alter table table1 add key (city(7)) #添加前缀索引
	select count(distinct left(city,3))/count(1), count(distinct left(city,4))/count(1) from test #前缀索引区分度
	另:Cardinality 基数 ≈ count(distinct(xx))   hyperloglog算法用来计算基数
	基数越小,关联时效率越高
	mysql> show index from test1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test1 |          1 | i1       |            1 | a           | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4.使用索引进行排序
	a.如果where 里面的条件和 order by的条件能组成最左匹配的话，就用索引排序；如果where里面是范围就不能用了
	b.如果order by的顺序和作组合索引不一样，就没法进行排序
5.union 
	union all(union还得去重) , in(oracle有个数限制为1000), or都能进行排序，推荐使用in
	and优先级比or高
	exist比in快，但是写起来麻烦
6.范围列可以用到索引
	但是范围列后面的列无法用到索引 , 索引最多用于一个范围列<，>，<=，>=，between
7.强制类型转换会全表扫描
	如果phone为varchar，使用select * from table where phone=1123123123；不会触发索引
8.更新十分频繁，数据区分度不高的字段上不宜建立索引
	更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
	类似于性别这类，不能有效过滤数据
	一般区分度在80%以上就可以建立索引，区分度可以使用count(distinct(a))/count(1)
9.创建索引的列不允许为null，可能会得到不符合预期的结果
10.join
	a.当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段数据类型必须一致
	b.map join（小表先放内存对大表join）
	c.如果明确知道只有一条数据返回，可以使用limit 1 提高效率
	d.A constraint join B     强制先执行A，再执行B
	e.使用小表 join 大表
	f.join的方式
		simple nested-loop join 效率低，数据库开销大
		index nested-loop join
		block nested-loop join
			join buffer大小256K，可以调，在Mysql5.1.22之前是4G，之后在64位系统下可以设置为>4G
			使用join buffer需要设置optimizer_switch的block nested-loop join为on，默认开启
			show variables like ‘%optimizer_switch%’ 可以查询具体参数
	h.大表join大表
		使用分区表
		先使用where筛选
	i.使用相同的连接键
11.能使用limit的时候尽量使用limit
12.单表索引尽量控制在5个以内
	索引数目多，对应的磁盘空间大，IO量就会大
13.单索引字段数不允许超过五个（组合索引）

查询优化

查询慢的原因
网络/CPU/IO/上下文切换/系统调用/生成统计信息/锁等待时间
优化数据访问
1.查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据我们可以通过减少访问数据量的方式进行优化——IO
2.避免查询不需要的记录,能使用limit,一定要使用
3.严格避免*，表关联要加别名
4.避免重复查询相同的一些数据
优化器的优化策略
静态优化:直接对解析树进行优化并完成优化
动态优化:Mysql对查询的静态优化只需要一次，但对动态优化每次执行前都需要重新评估
排序优化
两次传输排序 : 两次IO，现先根据排序字段排序,再将排好序的结果按照需要返回
一次传输排序 : 直接把所有查询的列取出来排序
当排序的列的总大小+order by列的大小超过 max_length_for_sort_data,就用双次排序，反之。当然用户可以自定义
特定类型的优化

1.count:count(1),count(*),没有任何where条件count会更快
2.优化关联条件
	确保on或者using上有索引，创建索引时要考虑关联的顺序
	确保order by 和group by只涉及到一个列
3.优化子查询
	尽可能使用关联代替子查询,因为子查询会有临时表
4.group by 和distinct
	如果对关联查询做分组，并且是按照查询表中的某个列进行分组，那个可以采用查询表的标识列（关联字段）分组的效率比其他列高,简单的理解就是select a,b,count(1) group by a,b 可以换成a,b,count(1) group by id #其中id为主键列
4.使用用户自定义变量(当前会话有效)
	@p表示自定义变量，
	@@p表示系统变量；
	示例:set @i:=1;
		select @i;
		select @@autocommit；
		
6.优化limit分页
	id>xxx
	between...and...
	inner join
	使用覆盖索引
7.优化union
	行转列(join，union，case  when）

分区表

分区表的限制

1.一个表只能有1024个分区,在5.7的时候可以支持8196个分区
2.5.5的Mysql可以直接使用列进行分区
3.如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
4.分区表无法使用外键约束，因为数据文件在不同的物理设备上

分区表底层原理

类型	执行过程
select	当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据
insert	当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表
delete	当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作
update	当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对底层表进行写入操作，并对源数据所在的底层表进行删除操作

分区表的类型

列分区:低版本int，高版本没限制
hash分区
key分区key()
子分区
范围分区RANGE
列表分区LIST

redo log

innodb,前滚日志,循环写，随机写

执行:当发生数据修改的时候，innodb引擎会先将记录写到redo log中，
并更新内存，同时innodb引擎会在合适的时机将记录操作到磁盘中
其中,Redolog是固定大小的，是循环写的
有了redolog之后，innodb就可以保证即使数据库发生异常重启，
之前的记录也不会丢失，叫做crash-safe

undo log

innodb,回滚日志

操作:在操作任何数据之前，首先将数据备份到一个地方(Undo Log)。
然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，
系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态

binlog

server层级,默认不开启,顺序写(写入速度极快)

锁

MyISAM

表共享读锁

对一个表加读锁，那么除了这个表之外的其他的表不能访问，
目的:是为了防止死锁，因为假如用户a锁住A表想查询B表，用户b锁住B表想查看A表会出现死锁

表独占写锁

myisam并发插入问题

可以通过lock table xxx read local;支持查询和插入操作的并发执行

如果Table_locks_waited比较高，则说明存在着比较严重的表级锁争用情况

Innodb

innodb默认加锁是行级,但是当表中没有索引,默认加锁由行级退化成表锁

行锁(也叫记录锁)

针对单条记录进行锁定,并发度大,加锁慢

表锁

锁定整个表,并发度小,加锁快

自增锁,表级

auto_increament自增列，表级锁

间隙锁

是Innodb在可重复读提交下为了解决幻读问题时引入的锁机制,针对某个区间进行锁定

临键锁

是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

共享锁(读锁),lock in share model
排它锁(写锁),for update