Mysql性能优化

Mysql性能优化

正确的创建合适的索引是提升数据库查询性能的基础。你了解索引吗？索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。

为什么要用索引？

1.极大减少存储引擎需要扫描的数据量
2.可以把随机io变为顺序io
3.在分组、排序等操作时，避免使用临时表

索引的数据结构

mysql的采用b+tree的数据结构来存储数据,数据的存储方式请看下图

b+tree有如下特点：
1.节点采用左闭右开区间
2.非叶子节点不保存数据相关信息，只保存关键字和子节点的引用
3.关键字对应的数据，保存在叶子节点
4.叶子节点是顺序排序的，且相邻节点具有顺序引用的关系

选用b+tree的理由

1.扫库、扫表能力强
表数据保存在叶子节点，因此扫库、扫表时从叶子节点查询就可以
2.磁盘读写能力强
节点至保存关键字和子节点引用，因此节点中能够保存更多的关键字，也就是说一次io，能够加载更多的关键字。
3.排序能力强
b+tree的叶子节点保存的数据具有天然有序性，因此排序能力强。
4.查找效率更稳定
表数据只保存在叶子节点中，因此每次查询的路径长度相同，所以更加稳定。

inndodb中b+tree的体现形式

上图中可以看出，各节点存储的是主键值和子节点引用。那如果该表中还有其他索引呢？请看下图

可以看出，辅助索引的叶子节点存储的是主键索引的值。这是因为inndodb认为，我们常用的查询是通过主键索引的查询的。

索引使用

前面讲了索引的结构，那我们在实际应用中如何使用索引？该注意些什么？

1.避免在离散型很差的列上建立索引

什么是离散性？ 计算公式：count(distinct(col)):count(col) ，值越大，可选择性越好。
假如你在性别(sex)一列创建索引，由于该列离散性很差，很可能会被优化器优化，不会使用索引而进行全表扫描。

2.最左匹配原则

对索引中关键字进行计算（对比），一定是从左往右依次进行，且不可跳过。

先讲个例子：

问题

经排查发现最常用的sql语句：
Select * from users where name = ? ;
Select * from users where name = ? and phoneNum = ?;

解决方案

机灵的李二狗的解决方案：
create index idx_name on users(name);
create index idx_name_phoneNum on users(name,phoneNum);

分析

你认同李二狗的解决方案吗？
实际上，index idx_name这个索引是重复索引，根据最左匹配原则，关键字的比对是从左往右依次进行的，idx_name_phoneNum 完全满足索引的使用要求。

3.使用覆盖索引

如果查询列能通过节点中的关键字直接返回，那么会大大提高查询效率。
假如表中有几百万条数据，树的深度一定非常深,试想每次都从叶子节点返回数据，会进行大量的io操作，性能会受到很大影响。

4.其他

索引列的数据长度能少则少。
索引一定不是越多越好，越全越好，一定是建合适的。
匹配列前缀可用到索引 like 9999%，like %9999%、like %9999用不到索引；
Where 条件中 not in 和 <>操作无法使用索引；
匹配范围值，order by 也可用到索引；
多用指定列查询，只返回自己想到的数据列，少用select *；
联合索引中如果不是按照索引最左列开始查找，无法使用索引；
联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；
联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引；

如何定位慢sql

1.测试驱动

2.业务驱动

3.慢日志查询

前面两个不难理解，下面着重介绍一下慢日志查询。慢日志是有mysql根据执行结果形成的慢sql日志。慢日志搜集默认是关闭的

show variables like 'slow_query_log' //查看功能是否开启
set global slow_query_log = on //功能开启
set global slow_query_log_file = '/var/lib/mysql/gupaoedu-slow.log' \\设置慢日志存储路径。
set global log_queries_not_using_indexes = on \\查询语句未用到索引会输出到慢日志
set global long_query_time = 0.1 (秒) \\设置查询时间阈值，超过这个时间会输出到慢日志

日志内容分析

Time ：日志记录的时间
User@Host：执行的用户及主机
Query_time：查询耗费时间 Lock_time 锁表时间 Rows_sent 发送给请求方的记录
条数 Rows_examined 语句扫描的记录条数
SET timestamp 语句执行的时间点
select … 执行的具体语句

事务

事务是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作，事务是一组不可再进行分割的操作集合。

典型的事务操作就是转账，要么全部成功，要么全部失败。（不在赘述细节了，大家都懂）

事务具有四大特性 （ACID）

1.原子性 Atomicity
最小的工作单元，整个工作单元要么一起提交成功，要么全部失败回滚
2.一致性 Consistency
事务中操作的数据及状态改变是一致的，即写入资料的结果必须完全符合预设的规则,不会因为出现系统意外等原因导致状态的不一致
3.隔离性 Isolition
一个事务所操作的数据在提交之前，对其他事务的可见性设定（一般设定为不可见）
4.持久性 Durability
事务所做的修改就会永久保存，不会因为系统意外导致数据的丢失

事务的并发带来的问题

1.脏读

事务a中对记录做了修改，但事务a还未提交，另一个事务b读取到了a还未提交的内容，产生脏读。

2.不可重复读

事务a中查询了某条记录，此时事务b对此记录进行了修改并已提交，事务a再查询此条记录时，会发现一个事务中前后两个读取到的内容不一致，产生不可重复读的问题。

3.幻读

事务a中进行一次范围查询，事务b在事务a的查询范围内insert了一条记录，事务a再次进行查询时会产生前后两次查询不一致的结果，产生幻读。

隔离级别

1.Read Uncommitted（未提交读） --未解决并发问题
事务未提交对其他事务也是可见的，脏读（dirty read）
2.Read Committed（提交读） --解决脏读问题
一个事务开始之后，只能看到自己提交的事务所做的修改，不可重复读（nonrepeatable read）
3.Repeatable Read (可重复读) --解决不可重复读问题
在同一个事务中多次读取同样的数据结果是一样的，这种隔离级别未定义解决幻读的问题
4.Serializable（串行化） --解决所有问题
最高的隔离级别，通过强制事务的串行执行

那么这些隔离级别是如何实现的呢？答案是锁

锁（innodb）

锁是用于管理不同事务对共享资源的并发访问。innodb的行锁是通过给索引上的索引项加锁来实现的。这也就意味着，锁记录时，如果条件不是索引列，那意味着会锁表。

锁分类：
1.共享锁

又称为读锁，s锁，作用是加锁后，只能读不能修改。
加锁释锁方式：

  select * from users WHERE id=1 LOCK IN SHARE MODE;
  commit/rollback

2.排他锁

又称为写锁，一个事务获得了记录的排他锁，其他事务不能读，也不能写。
（其他事务要读取数据可来自于快照）
加锁释锁方式：

delete / update / insert 默认加上X锁
SELECT * FROM table_name WHERE ... FOR UPDATE
commit/rollback

3.意向共享锁

表示事务准备给数据行加入共享锁，即一个数据行加共享锁前必须先取得该表的IS锁，意向共享锁之间是可以相互兼容的

4.意向排他锁

表示事务准备给数据行加入排他锁，即一个数据行加排他锁前必须先取得该表的IX锁，意向排它锁之间是可以相互兼容的

5.自增锁

针对自增列自增长的一个特殊的表级别锁，

show variables like 'innodb_autoinc_lock_mode';

默认取值1，代表连续，事务未提交ID永久丢失

6.临键锁

锁住记录+区间（左开右闭）
当sql执行按照索引进行数据的检索时,查询条件为范围查找（between and、<、>等）并有数据命中则此时SQL语句加上的锁为Next-key locks，锁住索引的记录+区间（左开右闭）

7.区间锁

锁住数据不存在的区间（左开右开）
当sql执行按照索引进行数据的检索时，查询条件的数据不存在，这时SQL语句加上的锁即为Gap locks，锁住索引不存在的区间（左开右开）

8.记录锁

当sql执行按照唯一性（Primary key、Unique key）索引进行数据的检索时，查询条件等值匹配且查询的数据是存在，这时SQL语句加上的锁即为记录锁Record locks，锁住具体的索引项

利用锁解决的问题

脏读：
添加排他锁（X锁，写锁），利用排他锁的特性，未提交前别的事务既不能读，也不能写的特性。

不可重复度：
添加共享锁（S锁，读锁），利用共享锁可读，不可改的特性。

幻读：
添加临键值锁，利用临键值锁相邻区间的特性。