MySql优化

最新推荐文章于 2023-07-31 23:13:38 发布

晓宇码匠

最新推荐文章于 2023-07-31 23:13:38 发布

阅读量236

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/qq_37651267/article/details/106048806

版权

数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

mysql多表连接查询的模式

左表和右表的共有部分，即内连接
SELECT fileds
FROM TableA AS A
INNER JOIN TableB AS B
ON A.key1 = B.key2;
左表和右表的共有部分+左表的全部，即左连接
SELECT fileds
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.key1 = B.key2;
左表和右表的共有部分+右表的全部，即右连接
SELECT fileds
FROM TableA AS A
RIGHT JOIN TableB AS B
ON A.key1 = B.key2;
左表独有的部分
SELECT fileds
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.key1 = B.key2
WHERE B.key2 IS NULL;
右表独有的部分
SELECT fields
FROM TableA AS A
RIGHT JOIN TableB AS B
ON A.key1 = B.key2
WHERE A.key1 IS NULL;
左表的全部+右表的全部
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;
左表独有的部分+右表独有的部分，即内连接的补集
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。