MySQL性能优化 - 索引简介
原创
今日推荐： 如何用Redis高效实现点赞功能？用Set？还是Bitmap？
文章地址： https://cloud.tencent.com/developer/article/2464859
推荐语： 这篇文章从实际需求出发，演示了redis的使用场景，并在实现该需求的过程中对比两个不同实现方案的优缺点演示了两个不同数据结构的优缺点，实际项目开发中可以有所借鉴。
下面开始今天对于MySQL索引的一些实践
索引简介
在一张没有索引的表里，当我们使用SELECT语句的时候MySQL需要从第一行开始扫描整张表来获取所需要的行，可想而知，数据表存储的数据越多，需要的时间越长。
提高SELECT性能最好的方式是创建索引, 索引就像指向所选行的一个指针让MySQL不需要从头扫描就能定位到所需要的行。
但是索引也不是越多越好:
- 浪费空间，索引越多，需要越多的空间来存储索引项。
- 减慢响应， 索引越多，SELECT需要越多时间来决定使用哪一个索引, INSERT, UPDATE, DELETE等操作也需要越多的时间，因为在做这些操作时需要更新每一个索引。
所以，需要在成本和收益之间找到一个平衡点。另外因为在有索引的情况下需要先访问索引再找到所需要的数据行，所以以下情况考虑不使用索引:
- 如果表本身并没有很多数据，那么索引起的作用不大，因为即使全表扫描也不需要太久。
- 如果大部分的查询需要表里面的所有行或者大多数行，索引的作用也不大，直接进行顺序访问需要的时间更少。
如上我们可知索引是提高性能的一个重要手段，该文介绍了在执行某个查询时是否用到了所定义的索引以及不走索引的一些原因。
接下来我们来看几个索引以及如果增加删除索引和索引对查询的性能影响。 以employees数据库中的salaries表为例
mysql> show index from salaries \G
*************************** 1. row ***************************
Table: salaries
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: emp_no
Collation: A
Cardinality: 280478
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: salaries
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: from_date
Collation: A
Cardinality: 2706265
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.00 sec)从该结果看该表只有一个PRIMARY的索引。也就是主键，一个表只能有一个主键，所以一般我们定义最重要最常查询的列为主键; 主键可以包括一个或者多个列， 有多个列的主键为复合主键; 主键所在的列的值必须是非空并且唯一的，这样才能保证每行都可以被唯一识别。 从该结果中我们还可以得出以下信息:
- 主键由emp_no和from_date两列组成
- 主键中emp_no列有280478个唯一值, from_date有2706265个唯一值(Cardinality的值), 越大越好，不会超过表格中行总数
- Visible为Yes表示该索引是可见的，能在查询中被使用
- Index_type为BTREE表示使用B树结构来存储索引, 其它可能是的索引结构包括FULLTEXT, HASH, RTREE), BTREE是最
- 该索引的排序(Collation)为升序(A)
- 该索引的值不为NULL(如果允许为NULL则值则为Null:'Yes')
- 该索引的值唯一(Non_unique为0表示唯一，为1则表示不唯一）
检查某个查询是否用到索引
现在已经知道索引并不是越多越好，需要根据实际的查询需求来确定给哪些列建立索引，那么怎么知道某个查询是否用到索引呢， MySQL为我们提供了EXPLAIN语句。接下来是两个对于employees表的示例查询。
mysql> show index from employees \G
*************************** 1. row ***************************
Table: employees
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: emp_no
Collation: A
Cardinality: 299290
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.00 sec)从以上结果中可以看出该表有一个PRIMARY主键, 所在的列是emp_no列且该索引可以用于查询。
mysql> explain select * from employees where emp_no = 11001 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)从结果中我们可以看出
- 该查询为简单(SIMPLE)查询，没有子查询(SUBQUERY)或者联合查询(UNION)等
- 该查询通过主键或者唯一索引(const)查找数据，性能非常好, 因为它可以在常量时间内检索
- 该表没有进行分区(partitions为NULL), 如果表是分区的，这里将显示使用的分区信息
- 该查询可能使用和实际使用的索引都是PRIMARY
- 该查询的rows为1，表示只需要读取一行就找到了所需要的结果集
- 基于条件过滤后，符合条件的行全部被返回(filtered)
- 该查询用到的索引使用4个字节的空间来存储索引值，通常是字段类型的长度加上其它部分
mysql> explain select * from employees where first_name = "Baziley" and last_name = "Buchter" \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 299290
filtered: 1.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)- 该查询为简单(SIMPLE)查询，没有子查询(SUBQUERY)或者联合查询(UNION)等
- 该查询将进行全表(ALL)扫描，这说明查询效率低，需要检查表中的每一行来找到所需结果
- 该查询没有可能用到的索引(possible_keys)和实际用到索引（keys)
- 该查询的rows为299290，表示可能需要扫描299290才能找到所需结果
- 该查询会用到where子句来过滤结果集
检查索引效率
接下来我们将比较一下在使用索引列和没有使用索引列的耗时的不同
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)启用和查看查询的执行时间
mysql> select * from employees where emp_no = 11001 \G
*************************** 1. row ***************************
emp_no: 11001
birth_date: 1956-04-16
first_name: Baziley
last_name: Buchter
gender: F
hire_date: 1987-02-23
1 row in set (0.00 sec)
mysql> select * from employees where first_name = "Baziley" and last_name = "Buchter" \G
*************************** 1. row ***************************
emp_no: 11001
birth_date: 1956-04-16
first_name: Baziley
last_name: Buchter
gender: F
hire_date: 1987-02-23
1 row in set (0.16 sec)
执行查询，分别用emp_no和first_name，last_name的组合来查询数据
mysql> SHOW PROFILES;
+----------+------------+--------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------+
| 1 | 0.00034200 | select * from employees where emp_no = 11001 |
| 2 | 0.16409500 | select * from employees where first_name = "Baziley" and last_name = "Buchter" |
+----------+------------+--------------------------------------------------------------------------------+查看查询语句的执行时间，duration以秒为单位，我们可以看到用到索引的查询所耗时为0.342毫秒, 而没用到索引的查询耗时为164.095毫秒，可谓差距十分巨大。
增加索引
我们可以试着给employees表格增加以first_name, last_name的值的索引，
mysql> CREATE INDEX idx_name ON employees (first_name, last_name);
Query OK, 0 rows affected (1.16 sec)
Records: 0 Duplicates: 0 Warnings: 0增加索引后我们再使用first_name和last_name来查询
mysql> SHOW PROFILES \G
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.16409500
Query: select * from employees where first_name = "Baziley" and last_name = "Buchter"
*************************** 7. row ***************************
Query_ID: 7
Duration: 0.00042000
Query: select * from employees where first_name = "Baziley" and last_name = "Buchter"对比来看增加了first_name和last_name的组合索引之后，同样的查询耗时变成0.42毫秒，比之前的164.095毫秒快了390倍。
删除索引
由于我们提到对于有索引的列在INSERT, UPDATE, DELETE操作时都需要更新索引，所以过多的索引会减慢性能，在查询时应尽量使用已经有索引的列。 对于不那么需要的索引我们可以将之删除
mysql> DROP INDEX idx_name ON employees;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0删除刚刚建立的关于first_name, last_name的索引
另外由于使用profiling会造成一定的性能影响，建议在非生产环境才使用profiling并在完成分析后关闭profiling。
mysql> SET profiling = 0;
Query OK, 0 rows affected, 1 warning (0.00 sec)原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
