MySQL高级之第二天: 性能下降及索引

小鱼儿 2021-05-12 11:55 484阅读 0赞

# **(一) MySQL性能下降** #

也许在现在大家的数据库表存储数据还停留在几千条, 几万条的样子, 所以编写一般的正确的SQL语句在性能上完全看不出有什么瑕疵, 而当数据到达了三百万条左右及以上时, 类似于 "select \* from table where condition"的操作会暴露出性能下降的问题, 下面首先来讨论一下MySQL性能下降的原因 :

执行时间长、等待时间长：

1. 查询语句写的烂：各种连接，各种子查询导致不能用索引，或者没有建立索引。

2. 索引失效：建了索引没用上。

3. 关联查询太多join（设计缺陷或不得已的需求）

4. 服务器调优及各个参数设置（缓冲、线程数等）

--------------------

# **(二) 索引\[重要!\]** #

## 1. 首先我们先来认识索引是什么? ##

索引(Index)是一种帮助MySQL高效获取数据的**数据结构**

**索引的本质: 它是一种数据结构, 不是数据!**

## 2. 索引的目的 ##

提高查询效率, 可以类比到字典上, 索引用于"排序" 和 "快速查找" !

## 例子(有助于你理解索引的作用) :  ##

如果要查"mysql"单词, 我们肯定要定位到m字母, 继续往下找到y字母, 再找剩下的mysql

如果没有索引,那么你可能需要a----z,一个个查找.

如果建立了索引, 可以快速直接定位到字母m, y, s, q, l这几个字母.

# #

### 3. 索引的优势和劣势 ###

### 3.1 优势: ###

1. 大学图书馆建立书目索引, **提高数据检索的效率, 降低数据库的IO成本(IO)**

2. 通过索引列**对数据进行排序,降低数据排序的成本, 降低了CPU的消耗.(运算CPU)**

### 3.2 劣势: ###

1. 实际上索引也是一张表, 该表保存了主键和索引字段, 并指向实体表的记录, 所以**索引也是要占空间的**

2. **虽然索引大大提高了查询速度,同时却会降低更新表的速度**, 例如**对表进行INSERT, UPDATE和DELETE.** 因为更新表时, MySQL不仅要保存数据, 还要保存一下索引文件每次更新添加了索引列的字段, 都会调整因为更新所带来的键值变化后的索引信息

3. 索引只是提高效率的一个因素, **如果你的MySQL有大数据量的表, 就需要花时间研究建立最优秀的索引, 或优化查询.**

## **4. 索引分类和建索引命令语句** ##

### 4.1 索引可分为以下两种类型： ###

**1. 单值索引：**

即**一个索引只包含单个列,** 一个表可以有多个单列索引.

一张表最多的索引**不要超过五个**

**例子:**

select \* from user where name = '';

create index idx\_user\_name on user(name);

**2. 复合索引：**

即一个索引**包含多个列**

**例子:**

select \* from user where name = '' and email='';

create index idx\_user\_nameEmail on user(name, email);

3. 唯一索引:

索引列的**值必须唯一,但允许有空值**.(银行数据表里的银行卡号)

4. 基本语法:

创建: CREATE \[UNIQUE\] INDEX indexName ON myTbale(columnName(length));

ALTER myTable ADD \[UNIQUE\] INDEX \[indexName\] on (columnName(length))

删除: DROP INDEX \[indexName\] on myTable;

查看: SHOW INDEX FROM table\_name\\G

使用ALTER命令:

1.ALTER TABLE tbl\_name ADD PRIMARY KEY(column\_list);

**该语句添加一个主键, 这意味着索引值必须是唯一的, 且不能为NULL**

2.ALTER TABLE tbl\_name ADD UNIQUE index\_name(column\_list)

**该语句创建唯一索引(除了NULL外, NULL可能会出现多次)**

3.ALTER TABLE tbl\_name ADD INDEX index\_name(column\_list);

**该语句创建普通索引, 索引值可出现多次.**

4. ALTER TABLE tbl\_name ADD FULLTEXT index\_name (column\_list);

**该语句指定了索引为FULLTEXT, 用于全文索引**

**构造索引的索引名格式:idx\_表名\_字段名 on 表名(字段名);**

## **5. MySQL索引结构** ##

主攻BTree索引,知道还有Hash索引, full-text全文索引, R-Tree索引

## **BTree检索原理** ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTk0OTMyOA_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTk0OTMyOA_size_16_color_FFFFFF_t_70 1][]

## **6. 哪些情况需要创建索引?** ##

1. 主键自动建立唯一索引.

2. 频繁作为查询条件的字段应该创建索引

3. 查询中与其它表关联的字段, 外键关系建立索引

4. 频繁更新的字段不适合创建索引

5. Where条件里用不到的字段不创建索引

6. 单键/组合索引的选择问题, who?(在高并发下倾向于创建组合索引)

7. 查询中排序的字段, 排序字段若通过索引去访问将大大提高排序速度(order by 排序字段)

8. 查询中统计或分组字段(group by 分组字段)

## 7. 哪些情况不适合创建索引? ##

1. 表记录太少(300万条以上数据,MySQL性能开始下降)

2. 经常在增删改的表(因为提高了查询速度, 同时却会降低更新表的速度, 如对表进行INSERT, UPDATE, 和DELETE.因为更新表时, MySQL不仅要保存数据, 还要保存一下索引文件)

3. 注意, 如果某个数据列包含许多重复的内容, 为它建立索引就没有太大的实际效果.(男/女 中国国籍)

例子: 假如一个表有10万行记录, 有一个字段A只有True和False两种值, 且每个值得分布概率大约为50%, 那么对这种表A字段建立索引一般不会提高数据库的查询速度.

索引的选择性是指索引列中不同值得数目与表中记录数的比. 如果一个表中有2000条记录, 表索引列有1980个不同的值, 那么这个索引的选择性就是1980/2000 = 0.99. 一个索引的选择性越接近于1, 这个索引的效率就越高.

今天就记录这么多吧, 主要是让大家理解MySQL性能下降的原因还有索引相关的基础知识, 希望能与你们一起进步!如有疑问可以留言, 我很乐意与你们交流!

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTk0OTMyOA_size_16_color_FFFFFF_t_70]: /images/20210511/2d5e40d3e8024dab8f4f3e7e6afcb365.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTk0OTMyOA_size_16_color_FFFFFF_t_70 1]: /images/20210511/f93e9fd9bb404bc1868dead8362cb926.png