【性能优化】MySQL 数据库性能优化笔记

1、常用的引擎有两种：InnoDB和MyISam

InnoDB：事务优先 适合高并发场景 5.4以后是mysql的默认引擎

MyISam：性能优先

需要注意的是：数据库语句的书写过程与执行过程是不同的

2、SQL优化

1>索引

        索引是一种数据结构，B树是一种类似于二叉树的结构，使用B树可以有效降低树的高度。降低树的高度可以有效提高检索的效率。B+树是B树的一种升级，它的所有数据都存放在叶子结点并且叶子结点之间增加了指针形成链表。它可以在符合多条结果时，由于数据都在叶子结点，且同时是链表结构，只需要首尾相接，通过链表就可以把所有数据取出来了。

    索引弊端：

        1、索引本身很大，占用内存/磁盘空间

        2、不是所有情况都适合建立索引 a)少量数据 b)频繁更新的数据 c) 很少使用的字段

        3、索引会提高查询效率，但降低增删改的效率

    索引优势：

        1、提高查询效率（降低了io使用率）

        2、降低CPU使用率，因为本身B树是一个排好序的结构，因此使用order by 不需要重新排序

3层B树可以存放数百万条数据，这里的B树指的是B+树

B+树中查找数据的效率均是n次，n是B+树的高度

2>索引

    分类：

         单值索引：单列 一个表可以有多个单值索引

         唯一索引：不能重复 一般是id字段  （主键索引和唯一索引区别是 前者不能是NULL）

         复合索引：多个列构成的索引 （相当于书的二级目录）

创建方式一： create 索引类型 索引名 on 表(字段)

    例：

         单值：create index dept_index on tb(dept);

         唯一：create unique index name_index on tb(name);

         复合：create index dept_name_index on tb(dept,name);

修改：alter table 表名 索引类型 索引名(字段)

创建方式二：

         单值：alter table tb add index dept_index(dept);

         唯一：alter table tb add unique index name_index(name);

         复合：alter table tb add index dept_name_index(dept,name);

         注意：如果一个字段是primary key，则默认是主键索引

删除索引：

        drop index 索引名 on 表名

查询索引：

        show index from 表名

3>SQL性能分析

    a、分析SQL的执行计划explain，可以模拟sql优化器执行sql语句

    b、mysql查询优化器会干扰我们的优化

explain执行计划中各属性含义：

  possible_keys：预测用到的索引

  key:实际使用的索引

  key_len：实际使用索引的长度

  ref：表之间的引用，指明当前表所参考的字段，就是引用了哪张表的什么字段（const常量）

  rows：通过索引查询的数据量

  extra：额外信息

       （1）using filesort：性能消耗大，需要“额外”一次排序（查询） 一半出现在order by  对于单索引，如果排序和查找是同一个字段，则不会出现using filesort

如何避免：where 哪些字段，就order by哪些字段

复合索引：不能跨列（最佳左前缀）

如何避免：where和order by 按照复合索引的顺序使用，不要跨列或无序使用

        （2）using temporary：性能损耗大，用到了临时表。一半出现在group by

如何避免：查询哪些列，就根据哪些分组

        （3）using index: 性能提升 索引覆盖 原因：不读取原文件，从索引表中获取。使用的索引中，所有都在索引表中，称索引覆盖 会对possible_keys和key造成影响

        （4）using where：需要回表查询 假设age是索引列，单查询语句中含有name，则必须回原表查name

总结：如果(a,b,c,d)复合索引，和使用的顺序全部一致(且不跨列使用)，则复合索引全部使用

where和order by拼起来不能跨列

表的执行顺序：因数量的个数改变而改变的原因：笛卡尔积

tb3--tb4--tb6

数量从小到大 因为 3*4=12 12*6=72 中间的12数据内存储是最小的

id值相同，数据量最小优先查询 从上向下 顺序执行

id值不同，id值越大越优先（在嵌套子查询时，先查内层，再查外层）

子查询 多表查询可以等价子查询

继续explain的属性：

select_type：查询类型

primary：包含子查询SQL中的主查询

subquery：包含子查询SQL中的子查询

simple：简单查询（不包含子查询、union）

derived：衍生查询（使用到了临时表） a、在from子查询中只有一张表 b、在from子查询中，如果有table1 union table2 则table1就是derived table2是union

union union result

type：索引类型 类型.要对type优化前提是有索引

system>const>eq_ref>ref>range>index>all

system：只有一条数据的系统表或衍生表只有一条数据的主查询

const:仅仅能查到一条数据的SQL，用于primary key或unique索引（类型于索引类型有关）

eq_ref:对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多，不能为0） 常见于唯一索引和主键索引

（下面是容易达成的条件 ， 上面的type几乎很难达到）

ref:非唯一性索引，对于每个索引键的查询，返回匹配的所有行

range:检索指定范围的行，where后面是一个范围查询(between,in,>,<) in 有时候会失效

index: 查询全部索引数据

all：查询全部表数据

 

单表优化：

例：查询authorid=1且typeid=2或3的bid

explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc;

以上语句执行效率很慢，没有任何优化

1、加索引

alter table book add index idx_bta(bid,typeid,authorid)；

再次执行，好一点点

一旦进行升级优化，需要将废弃的索引删掉，防止干扰

drop index idx_bta on book;

根据SQL实际的解析顺序，调整索引的顺序

alter table book add index idx_bta(typeid,authorid,bid)；

再次优化：in 可能失效，将顺序颠倒

alter table book add index idx_bta(authorid,typeid,bid)；

explain select bid from book where authorid=1 and typeid in (2,3) order by typeid desc;

小结：索引不能跨列使用（最佳左前缀）保持索引的定义和使用的顺序一致性

索引是需要逐步优化的

将含in的范围查询 放到where 条件的最后 防止失效

可以通过key_len 证明in可以使索引失效

两表优化：

左连接：select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java'

如何加索引：小表驱动大表 小表放左边 (where 小表.x10 = 大表.x300 )。索引建立经常使用的字段上

【一般情况对于左外连接给左表加索引，右外连接给右表加索引】

双层循环来说：外层循环建议小，内存循坏建议大依赖于程序设计原则

Altertable teacher2 add index index_teacher2_cid(cid);

Alter table course2 add index index_course2_cname(cname);

三表优化：

1、小表驱动大表

2、索引建立在经常查询的字段上[where后面的 经常用到的]

 

避免索引失效的一些原则：

1、复合索引，不要跨列或无序使用（最佳左前缀）

2、尽量使用全索引匹配

 

3、不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效。对于复合索引，如果左边失效

右侧全部失效

 

4、复合索引不能使用不等于（！= <>）或isnull is not null 否则自身以及右侧所有全部失效

复合索引中如果有> 则自身及右侧全部失效

SQL优化概率：服务层中存在mysql优化器，可能影响优化情况

一般而言，范围查询之后的索引失效

 

5、尽量使用索引覆盖 （usingindex）永远成立

 

6、like尽量以“常量”开头，不要以“%”开头，否则索引失效

Select * from xx where name like '%x%' -- name索引失效。如果必须使用like '%x%' 进行模糊查询，可以使用索引覆盖index 不需要回表查询

 

7、尽量不要使用类型转换（显式、隐式）比如让数据库自行进行的隐式数据类型转换索引会失效

 

8、尽量不要使用or，否则索引失效。 or会使左边的索引也均失效 ，可以使用UNION或UNION ALL来替换。

 

常见慢SQL及优化方法：

1、exist和in  如果主查询的数据集大，则使用In如果子查询的数据集大，则使用exist  将主查询结果，放到子查询中进行条件校验（如果有数据），如果符合，则保留

2、orderby

Using filesort 有两种算法：

双路排序：扫描两次磁盘  mysql4.1以前 第一次扫描排序字段并进行排序(buffer) 第二次扫描其他字段

单路排序（根据IO的次数）：只读取一次（全部字段），在buffer中进行排序（不一定真的是单路，如果数据量特别大，则无法将所有字段一次性读完，会进行分片读取）【可以调大buffer的大小】

Set max_length_for_sort_data = 1024

 

提高order by查询的策略：

a、选择使用单路、双路；调整buffer的容量大小

b、避免select*  … 

c、复合索引不要跨列使用

d、保证全部的排序字段排序的一致性 都是升序 或 降序

 

慢查询日志：mysql提供的一种日志记录，用于记录mysql中响应时间超过阀值的SQL语句（超过10秒）

1、慢查询日志默认是关闭的。开发时应该打开

 

检查是否开启了慢查询日志 ：show variables like '%slow_query_log%'

临时开启：

set global slow_query_log = 1; --在内存中开启. 重启mysql服务后会关闭

永久开启：

/etc.my.cnf 中追加配置 

[mysqld]

slow_query_log=1

slow_query_log_file=/var/lib/mysql/localhost-slow.log

 

 

慢查询阀值：show variables like '%long_query_time%'

临时设置：

set global long_query_time =5; --设置完毕后，重新登录后起效

永久设置：

/etc.my.cnf 中追加配置 

[mysqld]

long_query_time =3

 

查询慢查询的SQL ：show global status like '%slow_queries%'

1、慢查询日志：/var/lib/mysql/localhost-slow.log

2、通过mysqldumpslow工具查看慢SQL：mysqldumpslow --help

S:排序方式

r：逆序

l：锁定时间

g：正则匹配模式

 

---获取返回记录最多的3个SQL

例：mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

---获取访问次数最多的3个SQL

例：mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

--按照时间排序，前10条包含left join查询语句的SQL

例：mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

 

 

-----分析海量数据

1、Show profiles; --默认是关闭的

Show variables like ‘%profiling%’

Set profiling = on;

 

Show profiles: 会记录全部SQL语句打开的时间  不够精确，只能看到全局的时间

2、精确分析：sql诊断

Showprofile all for query 2;

 

 

3、全局查询日志：很费性能 只在开发时打开。  general_log =1

--执行的所有SQL记录在表中

set global general_log =1

set global log_output='table'

 

--执行的所有SQL记录在文件中

Set global log_output='file';

Set global general_log = on;

Set global general_log_file='/tmp/general.log';

 

-------------------

锁机制：

因资源共享而造成的并发问题

分类：

操作类型：

a、读锁（共享锁）对同一个数据，多个读操作可以同时进行，互不干扰

b、写锁（互斥锁）如果当前写操作没有完毕，则无法进行其他的读操作、写操作

操作范围：

a、表锁：一次性对一张表整体加锁如MyISAM 开销小、加锁快，无死锁；但锁的范围大，容易发生锁冲突，并发度低

b、行锁：一次性对一条数据加锁如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁；锁的范围较小，不易发生锁冲突，并发高

 

lock table 表1 read/write 

unlock tables;

查看加锁的表：

show open tables；

 

加读锁：

会话0：只能对加锁的表读可以写不可以

如果某一个会话，对A表加了read锁，则该会话可以对A表进行读操作、不能进行写操作，不能对其他表进行读、写操作

会话1：对加锁的表可以读，写会一直等待

会话2：对其他表可以读写操作

 

加写锁：

会话0：当前会话可以对加写锁的表进行任何操作（增删改查），但是不能操作其它表

其他会话：对会话0中加写锁的表可以进行增删改查的前提是：等待会话0释放写锁

 

 

Show open tables; 1代表上了锁

Showstatus like ‘table%’

Table_locks_immediate：即可能获取到的锁数

Table_locks_waited：需要等待的表锁数（值越大，竞争越大）

 

前者/后者>5000 建议使用InnoDB引擎，否则MyISAM引擎

行锁：

1、如果会话x对某条数据a进行增删改操作（关闭了自动commit情况），则其他会话必须等待会话x结束事务后，才能对数据进行操作

2、表锁是通过unlock tables ；行锁是通过事务解锁commit rollback

 

行锁注意事项：

a、如果没有索引，行锁会转为表锁  比如隐式转换后 索引失效 行锁变表锁

b、行锁的一种特殊：间隙锁---值在范围内，但却不存在。范围中没有此行的数据，mysql自动给间隙加锁

行锁：如果有where，则实际加锁的范围就是where后面的范围不管有没有值

 

Show status like '%innodb_row_lock%'

Innodb_row_lock_current_waits: 当前正在等待锁的数量

Innodb_row_lock_time:等待总时长

Innodb_row_lock_time_avg:平均等待时长

Innodb_row_lock_time_max:最大等待时长

Innodb_row_lock_waits:等待次数

 

for update : 对查询语句进行加锁