MySql优化

大家好，又见面了，我是你们的朋友全栈君。

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。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/106407.html原文链接：https://javaforall.cn