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树索引，其检索原理如下图所示
如果要查找数字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。