mysql的性能优化简介

mysql性能下降的原因

  • sql语句本身有问题,或没建索引
  • 索引失效,索引失效的原因本文后面会叙述
  • 关联了过多的表,可能是前期设计缺陷,或者太奇葩的需求
  • 服务器调优及参数设置,例如缓冲、线程等

 

mysql多表连接查询的模式

1. 左表和右表的共有部分,即内连接

SELECT fileds

FROM TableA AS A

INNER JOIN TableB AS B

ON A.key1 = B.key2;

2. 左表和右表的共有部分+左表的全部,即左连接

SELECT fileds

FROM TableA AS A

LEFT JOIN TableB AS B

ON A.key1 = B.key2;

3. 左表和右表的共有部分+右表的全部,即右连接

SELECT fileds

FROM TableA AS A

RIGHT JOIN TableB AS B

ON A.key1 = B.key2;

4. 左表独有的部分

SELECT fileds

FROM TableA AS A

LEFT JOIN TableB AS B

ON  A.key1 = B.key2

WHERE B.key2 IS NULL;

5. 右表独有的部分

SELECT fields

FROM TableA AS A

RIGHT JOIN TableB AS B

ON  A.key1 = B.key2

WHERE A.key1 IS NULL;

6. 左表的全部+右表的全部

mysql不支持full outer join,只能用union来实现

SELECT fields

FROM TableA AS A

LEFT JOIN TableB AS B

ON A.key1 = B.key2

UNION

SELECT fields

FROM TableA AS A

RIGHT JOIN TableB AS B

ON A.key1 = B.key2;

7. 左表独有的部分+右表独有的部分,即内连接的补集

SELECT fields

FROM TableA AS A

LEFT JOIN TableB AS B

ON A.key1 = B.key2

WHERE B.key2 IS NULL

UNION

SELECT fields

FROM TableA AS A

RIGHT JOIN TableB AS B

ON A.key1 = B.key2

WHERE A.key1 IS NULL;

 

索引

1. 索引的概念

官方定义:索引是帮助mysql高效获取数据的数据结构。划重点:数据结构。在数据之外,数据库系统还维护了一套满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这种数据结构就是索引,可以简单的理解为"排好序的快速查找数据结构"。索引本身也很大,不可能全部存储在内存,通常以索引文件的形式存储在磁盘中。

2. 索引按结构分类及其检索原理

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

一般Java开发工程师用到的是BTree索引,或者称为B树索引,其检索原理如下图所示

mysql的性能优化简介

如果要查找数字93,在第一层判断93<100,因此找左边的指针;在第二层判断93>80,因此找右边的指针;在第三层的磁盘块中找到了存储的93。B树索引的纵向层次决定了一次检索需要几次IO,因此纵向层次越少,性能越优。一般三层B树可以处理100万数据,如果不使用索引,可能需要几万到几十万次IO,但使用索引只需要3次。

 3. 索引按字段分类

  • 单值索引:索引只包含1个列,一个表可以建多个单值索引
  • 复合索引:索引包含多个列
  • 唯一索引:索引列的值必须是唯一的,但允许有空值

4. 新建索引

CREATE [UNIQUE] INDEX indexName ON tableName(columnName);

ALTER TABLE tableName ADD [UNIQUE] INDEX indexName (columnName);

以上两个语句都可以用于新建索引。其中,indexName是索引名称,tableName是表名,columnName是列名,如果是多列索引中间用逗号分隔,如果新建的是唯一索引,需要加UNIQUE。

5. 删除索引

DROP INDEX indexName ON tableName;

其中,indexName是索引名称,tableName是表名,表示删除指定表的指定索引。

6. 查看索引

SHOW INDEX FROM tableName\G

其中,tableName是表名,\G是为了显示格式优化。

7. 索引的优势

  • 提高数据检索效率,降低数据库的IO成本
  • 通过索引对数据进行排序,降低CPU消耗

因此,索引有两个功能,分别作用在WHERE字句和ORDER BY子句上。

8. 索引可能引起的问题

  • 索引也是一张表,保存了主键和索引字段,并指向实体表的记录
  • 索引提高了读表速度,却降低了写表的速度,因为在进行INSER、DELETE、UPDATE操作时,不仅要保存数据,还要保存因更新表带来的索引信息变化

索引并不是随便加,也不是越多越好,过多的或者不恰当的索引,反而会降低数据库的效率,一般一个表不应超过5个索引。

9. 适合建索引的情况

  • 主键自动建立唯一索引
  • 连表查询时,对外连接的字段
  • 频繁用作查询条件的字段,即WHERE filedName = 'xxx'
  • 需要排序的字段,即ORDER BY fieldName

10. 不适合建索引的情况

  • 频繁更新的字段,因为每次更新都需要更新索引信息
  • WHERE子句里极少用到的字段
  • 表记录数量太少,一般低于百万数据的表,建索引意义不大,超过300万性能才开始下降
  • 数据大量重复且平均分布的字段,建索引意义不大,例如一个字段表示性别,值不是男就是女,且出现的概率差不多,就没有必要建索引。

关于最后一条,涉及一个概念:索引的选择性。索引的选择性是指一个字段的不同的值的数量跟表的记录数的比值,例如一个字段可能存在8888个值,这个表共有10000条记录,那么在这个字段上建的索引的选择性就是0.8888。索引选择性越接近1,它的效率就越高。上面提到的"性别"字段,可能存在的值只有2个,如果这个表有10000条记录,那么如果在这个字段上建索引,它的选择性只有0.0002。

上一篇:数据库表链接的几种方式


下一篇:oracle多表关联删除的两种方法