Mysql索引优化分析（1）｜ 8月更文挑战

1、SQL性能下降的原因分析

• SQL语句的问题

• 索引失效：如查询最多的字段没有索引，select * from user where name = "";

  • 单值索引：create index idx_user_name on user(naem);
  • 复合索引：create index idx_user_nameEmali on user(naem,email);

• 关联查询的join太多

• 服务器问题

2、7种Join

• 左外连接：select * from table_a a left join table_b b on a.key = b.key
• 内连接：select * from table_a a inner join table_b b on a.key = b.key
• 右外连接：select * from table_a a right join table_b b on a.key = b.key
• 左连接：select * from table_a a left join table_b b on a.key = b.key where b.key = null
• 右连接：select * from table_a a right join table_b b on a.key = b.key where a.key = null
• 全连接(MySQL不支持)：select * from table_a a full outer join table_b b on a.key = b.key
• 两种表单都没有数据的连接(MySQL不支持)：select * from table_a a full outer join table_b b on a.key = b.key where a.key = null or b.key = null

针对无法在MySQL使用的full outer join语句解决方法。

全连接，查询A、B的所有，使用左外连接拼上右外连接

SELECT
	*
FROM
	table_a a
LEFT JOIN table_b b ON a.key = b.key
UNION
	SELECT
		*
	FROM
		table_a a
	RIGHT JOIN table_b b ON a.key = b.key;

查询A、B的独有，左连接拼上右连接

SELECT
	*
FROM
	table_a a
LEFT JOIN table_b b ON a.key = b.key
WHERE
	b.key IS NULL
UNION
	SELECT
		*
	FROM
		table_a a
	RIGHT JOIN table_b b ON a.key = b.key
	WHERE
		a.key IS NULL;

3、索引是什么

• 索引是帮助Mysql高效获取数据的数据结构。

• 如果没有索引，查询某个数据，需要从头开始一个一个的查询，直到找到目标数据，而索引的出现可以直接定位到需要查找的数据的位置，提高了查找数据的效率。索引就是排好序的快速查找数据结构。

• 除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这种数据结构以某种方式指向数据，这样就可以在这些数据结构基础上实现高级查找算法，这种数据结构就是索引。

• 索引本身占内存也很大，所以是以索引文件的形式存储在磁盘上。

• 平常所说的索引，一般都是B+树结构的索引，除了B树索引还有hash索引。

• 索引的优势：

  • 提高检索效率，减少数据库的IO成本。
  • 通过索引对数据排序，降低了排序成本，减少CPU的销毁。

• 索引的劣势：

  • 索引也是一张表，需要占用存储空间。
  • 虽然索引提高了查询速度，但是会降低增删改的速度，每次都数据的修改，数据库不仅保存数据，还要保存变化后的索引文件。