MySQL性能影响因素

本章将带你完成查询和schema的调优。数据库就是用于执行查询的，提高查询速度是调优的最终目标。数据库的性能取决于许多因素，主要是查询、schema、配置项和硬件。

1. 商业需求的影响

不合理需求造成资源投入产出比过低，这里我们就用一个看上去很简单的功能来分析一下。

需求：一个论坛帖子总量的统计，附加要求：实时更新。

从功能上来看非常容易实现，执行一条 SELECT COUNT(*) from 表名的 Query 就可以得到结果。但是如果存放帖子的表中已经有上千万的帖子的时候，执行这条 Query语句需要多少成本？恐怕再好的硬件设备，恐怕都不可能在 10 秒之内完成一次查询。

既然这样查询不行，那我们是不是该专门为这个功能建一个表，就只有一个字段，一条记录，就存放这个统计量，每次有新的帖子产生的时候，都将这个值增加 1，这样我们每次都只需要查询这个表就可以得到结果了，这个效率肯定能够满足要求了。确实，查询效率肯定能够满足要求，可是如果帖子产生很快，在高峰时期可能每秒就有几十甚至上百个帖子新增操作的时候，恐怕这个统计表又要成为大家的噩梦了。要么因为并发的问题造成统计结果的不准确，要么因为锁资源争用严重造成整体性能的大幅度下降。

其实这里问题的焦点不应该是实现这个功能的技术细节，而是在于这个功能的附加要求“实时更新”上面。当一个论坛的帖子数量很大了之后，到底有多少人会关注这个统计数据是否是实时变化的？有多少人在乎这个数据在短时间内的不精确性？恐怕不会有人会盯着这个统计数字并追究当自己发了一个帖子然后回头刷新页面发现这个统计数字没有加1吧？所以只要去掉了这个“实时更新”的附加条件，就可以非常容易的实现这个功能了。就像之前所提到的那样，通过创建一个统计表，然后通过一个定时任务每隔一定时间段去更新一次里面的统计值，这样既可以解决统计值查询的效率问题，又可以保证不影响新发贴的效率，一举两得。

2. 系统架构及实现的影响

所有数据都是适合在数据库中存放的吗？数据库为我们提供了太多的功能，反而让很多并不是太了解数据库的人错误的使用了数据库的很多并不是太擅长或者对性能影响很大的功能，最后却全部怪罪到数据库身上。实际上，以下几类数据都是不适合在数据库中存放的：

二进制多媒体数据

这种数据主要包括图片，音频、视频和其他一些相关的二进制文件。将二进制多媒体数据存放在数据库中，一个问题是数据库空间资源耗用非常严重，另一个问题是这些数据的存储很消耗数据库主机的CPU资源。这些数据的处理本不是数据库的优势，如果我们硬要将他们塞入数据库，肯定会造成数据库的处理资源消耗严重。

超大文本数据

对于 5.0.3 之前的 MySQL 版本，VARCHAR 类型的数据最长只能存放 255 个字节，如果需要存储更长的文本数据到一个字段，我们就必须使用 TEXT 类型（最大可存放 64KB）的字段，甚至是更大的 LONGTEXT 类型（最大 4GB）。而 TEXT 类型数据的处理性能要远比VARCHAR类型数据的处理性能低下很多。从 5.0.3 版本开始，VARCHAR 类型的最大长度被调整到 64KB
了，所以，超大文本数据存放在数据库中不仅会带来性能低下的问题，还会带来空间占用的浪费问题。

是否合理利用了应用层cache机制？

对于 Web 应用，活跃数据的数据量总是不会特别的大，有些活跃数据更是很少变化。对于这类数据，我们是否有必要每次需要的时候都到数据库中去查询呢？如果我们能够将变化相对较少的部分活跃数据通过应用层的 Cache 机制 Cache 到内存中，对性能的提升肯定是成数量级的，而且由于是活跃数据，对系统整体的性能影响也会很大。

3. 查询语句对性能的影响

SQL 语句的优劣是对性能有影响的，每个SQL语句在优化之前和优化之后的性能差异也是各不相同。在数据库管理软件中，最大的性能瓶颈就是在于磁盘 IO，也就是数据的存取操作上面。而对于同一份数据，当我们以不同方式去寻找其中的某一点内容的时候，所需要读取的数据量可能会有天壤之别，所消耗的资源也自然是区别很大。功能完全相同的两条SQL的在性能方面的差异。

我们在执行 sql 语句时可以用 explain 来查看执行计划；

也可以打开profiling功能，来查看SQL语句执行的整个过程中各项资源的使用情况：

  1. mysql> set profiling=1;
  3. mysql> show variables like '%profiling%';
  4. +------------------------+-------+
  5. | Variable_name          | Value |
  6. +------------------------+-------+
  7. | have_profiling         | YES   |
  8. | profiling              | ON    |
  9. | profiling_history_size | 15    |
  10. +------------------------+-------+
  11. 3 rows in set (0.00 sec)
  13. mysql> select * from employees;
  15. mysql> show profiles;
  16. +----------+------------+-----------------------------------+
  17. | Query_ID | Duration   | Query                             |
  18. +----------+------------+-----------------------------------+
  19. |        1 | 0.49405350 | select * from employees           |
  20. |        2 | 0.05112575 | show variables like '%profiling%' |
  21. |        3 | 0.00111100 | show variables like '%profiling%' |
  22. |        4 | 0.50444975 | select * from employees           |
  23. +----------+------------+-----------------------------------+
  24. 4 rows in set, 1 warning (0.00 sec)
  26. mysql> show profile CPU,BLOCK IO for query 1;
  27. +--------------------------------+----------+----------+------------+--------------+---------------+
  28. | Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
  29. +--------------------------------+----------+----------+------------+--------------+---------------+
  30. | starting                       | 0.000095 | 0.000014 |   0.000059 |            0 |             0 |
  31. | Executing hook on transaction  | 0.000006 | 0.000001 |   0.000004 |            0 |             0 |
  32. | starting                       | 0.000010 | 0.000002 |   0.000009 |            0 |             0 |
  33. | checking permissions           | 0.000008 | 0.000001 |   0.000006 |            0 |             0 |
  34. | Opening tables                 | 0.000084 | 0.000021 |   0.000087 |            0 |             0 |
  35. | init                           | 0.000010 | 0.000002 |   0.000007 |            0 |             0 |
  36. | System lock                    | 0.000019 | 0.000004 |   0.000015 |            0 |             0 |
  37. | optimizing                     | 0.000006 | 0.000001 |   0.000004 |            0 |             0 |
  38. | statistics                     | 0.000015 | 0.000003 |   0.000013 |            0 |             0 |
  39. | preparing                      | 0.000014 | 0.000002 |   0.000011 |            0 |             0 |
  40. | executing                      | 0.000003 | 0.000001 |   0.000002 |            0 |             0 |
  41. | Sending data                   | 0.493714 | 0.402596 |   0.000000 |            0 |             0 |
  42. | end                            | 0.000020 | 0.000012 |   0.000000 |            0 |             0 |
  43. | query end                      | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
  44. | waiting for handler commit     | 0.000010 | 0.000010 |   0.000000 |            0 |             0 |
  45. | closing tables                 | 0.000011 | 0.000011 |   0.000000 |            0 |             0 |
  46. | freeing items                  | 0.000010 | 0.000010 |   0.000000 |            0 |             0 |
  47. | cleaning up                    | 0.000013 | 0.000012 |   0.000000 |            0 |             0 |
  48. +--------------------------------+----------+----------+------------+--------------+---------------+
  49. 18 rows in set, 1 warning (0.00 sec)

4. 数据库schema设计对性能的影响

5. 硬件选择对性能的影响

首先，数据库主机是存取数据的地方，所以数据库主机的IO性能肯定是需要最优先考虑的一个因素，这一点不管是什么类型的数据库应用都是适用的。在主机中决定IO性能部件主要由磁盘和内存所决定，当然也包括各种与 IO 相关的板卡。

其次，由于数据库主机和普通的应用程序服务器相比，资源要相对集中很多，单台主机上所需要进行的计算量自然也就比较多，所以数据库主机的 CPU 处理能力也不能忽视。

最后，由于数据库负责数据的存储，与各应用程序的交互中传递的数据量比其他各类服务器都要多，所以数据库主机的网络设备的性能也可能会成为系统的瓶颈。

优化总结

数据库应用系统的优化，实际上是一个需要多方面配合，多方面优化的才能产生根本性改善的事情。简单来说，可以通过下面的话来简单的概括数据库应用系统的性能优化：商业需求合理化，系统架构最优化，逻辑实现精简化，硬件设施理性化。

explain计划

MySQL执行查询的方式是影响数据库性能的主要因素之一。可以使用EXPLAIN命令来验证MySQL的执行计划。执行EXPLAIN FORMAT=JSON命令，将得到详细信息。

使用EXPALIN

explain计划提供了关于查询优化器如何执行查询的信息。只需要将EXPLAIN关键字前缀加到查询中即可：

  1. mysql> EXPLAIN SELECT dept_name FROM dept_emp JOIN employees ON \
  2.     -> dept_emp.emp_no = employees.emp_no JOIN departments ON \
  3.     -> departments.dept_no=dept_emp.dept_no \
  4.     -> WHERE employees.first_name='Aamer' \G
  5. *************************** 1. row ***************************
  6.            id: 1
  7.   select_type: SIMPLE
  8.         table: employees
  9.    partitions: NULL
  10.          type: ref
  11. possible_keys: PRIMARY,name
  12.           key: name
  13.       key_len: 58
  14.           ref: const
  15.          rows: 228
  16.      filtered: 100.00
  17.         Extra: Using index
  18. *************************** 2. row ***************************
  19.            id: 1
  20.   select_type: SIMPLE
  21.         table: dept_emp
  22.    partitions: NULL
  23.          type: ref
  24. possible_keys: PRIMARY,dept_no
  25.           key: PRIMARY
  26.       key_len: 4
  27.           ref: employees.employees.emp_no
  28.          rows: 1
  29.      filtered: 100.00
  30.         Extra: Using index
  31. *************************** 3. row ***************************
  32.            id: 1
  33.   select_type: SIMPLE
  34.         table: departments
  35.    partitions: NULL
  36.          type: eq_ref
  37. possible_keys: PRIMARY
  38.           key: PRIMARY
  39.       key_len: 16
  40.           ref: employees.dept_emp.dept_no
  41.          rows: 1
  42.      filtered: 100.00
  43.         Extra: NULL
  44. 3 rows in set, 1 warning (0.00 sec)

id:

选择标识符

select_type:

表示查询的类型

table:

显示这一行的数据是关于哪张表的

partitions:

匹配的分区

type:

这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index 和 ALL；

possible_keys:

表示查询时，可能使用的索引。指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示null）；该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询；

key:

表示实际使用的索引。key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len:

索引字段的长度。表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的），不损失精确性的情况下，长度越短越好。

ref:

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值；

rows:

扫描出的行数(估算的行数)，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数；

filtered:

按表条件过滤的行百分比

Extra:

执行情况的描述和说明，关于MYSQL如何解析查询的额外信息

使用EXPLAIN JSON

以JSON格式使用explain计划，能提供有关查询执行情况的完整信息：

  1. mysql> EXPLAIN FORMAT=JSON SELECT dept_name FROM dept_emp JOIN employees ON \
  2.     -> dept_emp.emp_no = employees.emp_no JOIN departments ON \
  3.     -> departments.dept_no=dept_emp.dept_no \
  4.     -> WHERE employees.first_name='Aamer' \G
  5. *************************** 1. row ***************************
  6. EXPLAIN: {
  7.   "query_block": {
  8.     "select_id": 1,
  9.     "cost_info": {
  10.       "query_cost": "557.62"
  11.     },
  12.     "nested_loop": [
  13.       {
  14.         "table": {
  15.           "table_name": "employees",
  16.           "access_type": "ref",
  17.           "possible_keys": [
  18.             "PRIMARY",
  19.             "name"
  20.           ],
  21.           "key": "name",
  22.           "used_key_parts": [
  23.             "first_name"
  24.           ],
  25.           "key_length": "58",
  26.           "ref": [
  27.             "const"
  28.           ],
  29.           "rows_examined_per_scan": 228,
  30.           "rows_produced_per_join": 228,
  31.           "filtered": "100.00",
  32.           "using_index": true,
  33.           "cost_info": {
  34.             "read_cost": "4.49",
  35.             "eval_cost": "22.80",
  36.             "prefix_cost": "27.29",
  37.             "data_read_per_join": "121K"
  38.           },
  39.           "used_columns": [
  40.             "emp_no",
  41.             "first_name"
  42.           ]
  43.         }
  44.       },
  45.       {
  46.         "table": {
  47.           "table_name": "dept_emp",
  48.           "access_type": "ref",
  49.           "possible_keys": [
  50.             "PRIMARY",
  51.             "dept_no"
  52.           ],
  53.           "key": "PRIMARY",
  54.           "used_key_parts": [
  55.             "emp_no"
  56.           ],
  57.           "key_length": "4",
  58.           "ref": [
  59.             "employees.employees.emp_no"
  60.           ],
  61.           "rows_examined_per_scan": 1,
  62.           "rows_produced_per_join": 251,
  63.           "filtered": "100.00",
  64.           "using_index": true,
  65.           "cost_info": {
  66.             "read_cost": "228.12",
  67.             "eval_cost": "25.19",
  68.             "prefix_cost": "280.59",
  69.             "data_read_per_join": "7K"
  70.           },
  71.           "used_columns": [
  72.             "emp_no",
  73.             "dept_no"
  74.           ]
  75.         }
  76.       },
  77.       {
  78.         "table": {
  79.           "table_name": "departments",
  80.           "access_type": "eq_ref",
  81.           "possible_keys": [
  82.             "PRIMARY"
  83.           ],
  84.           "key": "PRIMARY",
  85.           "used_key_parts": [
  86.             "dept_no"
  87.           ],
  88.           "key_length": "16",
  89.           "ref": [
  90.             "employees.dept_emp.dept_no"
  91.           ],
  92.           "rows_examined_per_scan": 1,
  93.           "rows_produced_per_join": 251,
  94.           "filtered": "100.00",
  95.           "cost_info": {
  96.             "read_cost": "251.85",
  97.             "eval_cost": "25.19",
  98.             "prefix_cost": "557.62",
  99.             "data_read_per_join": "45K"
  100.           },
  101.           "used_columns": [
  102.             "dept_no",
  103.             "dept_name"
  104.           ]
  105.         }
  106.       }
  107.     ]
  108.   }
  109. }
  110. 1 row in set, 1 warning (0.00 sec)

总结

  • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
  • EXPLAIN不考虑各种Cache
  • EXPLAIN不能显示MySQL在执行查询时所作的优化工作
  • 部分统计信息是估算的，并非精确值
  • EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

基准查询和服务器

假设你想知道哪个查询执行起来更快。explain计划给了你一个结果，但有时并不能依据它来做决定。如果查询时间为几十秒左右，可以在服务器上执行这些查询以判断哪个更快。但如果查询时间大约为几毫秒，则很难基于单次执行来做判断。

可以使用mysqlslap工具模拟MySQL服务器的客户端负载，并报告每个阶段所耗费的时间，就像多个客户端正在访问该服务器一样。

如何操作

如果想评估一条查询的执行时间。如果在MySQL客户端中执行该操作，则能够知道大致的执行时间（精确到100ms）：

  1. mysql> pager grep rows
  2. PAGER set to 'grep rows'
  3. mysql> SELECT e.emp_no,salary FROM salaries s JOIN employees e ON \
  4.     -> s.emp_no=e.emp_no WHERE first_name='Adam';
  5. 2384 rows in set (0.00 sec)

可以使用mysqlslap模拟客户端负载，并在多个迭代中并行运行上述SQL语句：

  1. [root@www ~]# mysqlslap -uroot -pCom.123456 --create-schema=employees \
  2. > --query="SELECT e.emp_no,salary FROM salaries s JOIN employees e ON  s.emp_no=e.emp_no WHERE first_name='Adam';" \
  3. > -c 1000 -i 100
  4. mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  5. Benchmark
  6.     Average number of seconds to run all queries: 1.614 seconds
  7.     Minimum number of seconds to run all queries: 0.826 seconds
  8.     Maximum number of seconds to run all queries: 6.896 seconds
  9.     Number of clients running queries: 1000
  10.     Average number of queries per client: 1

以上查询是用1000个并发和100个迭代执行的，平均花费了1.614秒。

可以在文件中指定多个SQL并指定分隔符。mysqlslap会运行文件中的所有查询：

  1. [root@www script]# vim queries.sql
  2. SELECT e.emp_no,salary FROM salaries s JOIN employees e ON  s.emp_no=e.emp_no WHERE first_name='Adam';
  3. SELECT * FROM employees WHERE first_name='Adam' OR last_name='Adam';
  4. SELECT * FROM employees WHERE first_name='Adam';

运行结果

  1. [root@www script]# mysqlslap -uroot -pCom.123456 --create-schema=employees \
  2. > -c 10 -i 10 --query=queries.sql --delimiter=";"
  3. mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  4. Benchmark
  5.     Average number of seconds to run all queries: 0.922 seconds
  6.     Minimum number of seconds to run all queries: 0.843 seconds
  7.     Maximum number of seconds to run all queries: 1.039 seconds
  8.     Number of clients running queries: 10
  9.     Average number of queries per client: 3

可以自动生成表和SQL语句。通过这种方式，可以将结果与之前的服务器设置进行比较：

  1. [root@www script]# mysqlslap -uroot -pCom.123456 --create-schema=employees -c 10 -i 10 \
  2. > --number-int-cols=4 --number-char-cols=10 --auto-generate-sql
  3. mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  4. Benchmark
  5.     Average number of seconds to run all queries: 0.049 seconds
  6.     Minimum number of seconds to run all queries: 0.042 seconds
  7.     Maximum number of seconds to run all queries: 0.059 seconds
  8.     Number of clients running queries: 10
  9.     Average number of queries per client: 0

索引

关于 MySQL 索引的好处，如果正确合理设计并且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。

简单测试

做一个简单测试，假如我们创建了一个tb03表，向表中插入20000行数据，表的创建和数据插入用如下脚本实现。

  1. [root@www script]# cat mysql3.sh 
  2. #!/bin/bash
  3. HOSTNAME="localhost"
  4. PORT="3306"
  5. USERNAME="root"
  6. PASSWORD="Com.123456"
  8. DBNAME="test1"
  9. TABLENAME="tb03"
  11. # create database
  12. mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"
  13. create_db_sql="create database if not exists ${DBNAME}"
  14. mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
  16. #create table
  17. create_table_sql="create table if not exists ${TABLENAME} (stuid int not null primary key,stuname
  18.  varchar(20) not null,stusex char(1) not null,cardid varchar(20) not null,birthday datetime,
  19.  entertime datetime,address varchar(100) default null)"
  20. mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"
  22. #insert data to table
  23. i=1
  24. while [ $i -le 20000 ]
  25. do
  26. insert_sql="insert into ${TABLENAME} values($i,'zhangsan','1','1234567890123456','1999-10-10',
  27. '2016-9-3','zhongguo hebeisheng baodingshi')"
  28. mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"
  30. let i=i+1
  31. done
  33. #select data
  34. select_sql="select count(*) from ${TABLENAME}"
  35. mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"

再手动插入一行数据，如：

  1. mysql> insert into tb03 values \
  2.     -> (20001,'admin','0','123456789009877','2000-01-01','2019-09-01','hebeisheng baodingshi lianchiqu');
  3. Query OK, 1 row affected (0.01 sec)

下面开始测试，查询stuname=’admin’的记录

情况1：stuname列上没有创建索引的情况

  1. mysql> explain select stuid,stuname,stusex,cardid,entertime from tb03 \
  2.     -> where stuname='admin'\G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: tb03
  7.    partitions: NULL
  8.          type: ALL
  9. possible_keys: NULL
  10.           key: NULL
  11.       key_len: NULL
  12.           ref: NULL
  13.          rows: 20046
  14.      filtered: 10.00
  15.         Extra: Using where
  16. 1 row in set, 1 warning (0.00 sec)

情况2：stuname列上创建索引的情况

  1. mysql> create index index_stuname on tb03(stuname);
  2. Query OK, 0 rows affected (0.07 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  5. mysql> explain select stuid,stuname,stusex,cardid,entertime from tb03 \
  6.     -> where stuname='admin'\G
  7. *************************** 1. row ***************************
  8.            id: 1
  9.   select_type: SIMPLE
  10.         table: tb03
  11.    partitions: NULL
  12.          type: ref
  13. possible_keys: index_stuname
  14.           key: index_stuname
  15.       key_len: 82
  16.           ref: const
  17.          rows: 1
  18.      filtered: 100.00
  19.         Extra: NULL
  20. 1 row in set, 1 warning (0.00 sec)

在查找 stuname=”admin”的记录时，如果在stuname上已经建立了索引，MySQL 无须任何扫描全表，即准确可找到该记录。相反，MySQL会扫描所有记录。

所以在数据库表中，对字段建立索引可以大大提高查询速度。

索引概述

什么是索引？索引（Index）是帮助 MySQL 高效获取数据的数据结构，这是一种比较官方的说法。它的存在形式是文件。索引能够帮助我们快速定位数据。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

使用索引产生的代价？
  1. 索引本身是以文件的形式存放在硬盘，需要的时候才加载至内存，所以添加索引会增加磁盘的开销；
  2. 写数据：需要更新索引，对数据库是个很大的开销，降低表更新、添加和删除的速度

不建议使用索引的情况：
  1. 表记录较少
  2. 索引的选择性较低，所谓索引的选择性，是指不重复的索引值与表记录数的比值，取值范围（0-1）。选择性越高，索引的价值越大。

索引的分类

  • 从存储结构上来划分
    ：
    • BTree索引（B-Tree或B+Tree索引）
    • Hash索引
    • full-index全文索引
    • R-Tree索引。
  • 从应用层次来分
    ：
    • 普通索引
    • 唯一索引
    • 复合索引
  • 根据表中数据的物理顺序与键值的逻辑（索引）顺序关系
    ：
    • 聚集索引
    • 非聚集索引。

平时讲的索引类型一般是指在应用层次的划分。

索引的数据结构

InnoDB存储引擎采用B+Tree

一个B+树有以下特征：

  • 有n个子树的中间节点包含n个元素，每个元素不保存数据，只用来索引，所有数据都保存在叶子节点;
  • 所有叶子节点包含元素的信息以及指向记录的指针，且叶子节点按关键字自小到大顺序链接;
  • 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

4. 性能调优1 - 图1

所有的数据都在叶子节点，且每一个叶子节点都带有指向下一个节点的指针，形成了一个有序的链表。为什么要有序呢？其实是为了范围查询。比如说select * from Table where id > 1 and id < 100; 当找到1后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

B+树的优势：

  • 单一节点存储更多元素，减少IO;
  • 所有查询都要找到叶子节点，查询稳定;
  • 所有叶子节点形成有序链表，方便范围查询

一般性情况，数据库的B+树的高度一般在2~4层，这就是说找到某一键值的行记录最多需要2到4次逻辑IO，相当于0.02到0.04s

添加索引

如果没有索引，MySQL在查找相关行时就必须逐行扫描整个表。如果这个表在你要筛选的列上有索引，MySQL就可以快速找到大数据文件中的行而无须扫描整个文件。

MySQL可以使用索引来筛选WHERE、ORDER BY和GROUP BY子句中的行，也可以用索引来连接表。如果一个列上有多个索引，MySQL会选择给出了最多筛选行的索引。

可以执行ALTER TABLE命令来添加或删除索引。索引的添加和删除都是在线操作，不会妨碍表上的DML，但在大表上这么做会花费大量时间。

主键（聚簇索引）和二级索引

了解什么是主键（或聚簇索引）和二级索引是非常重要的。

聚集索引是按表的主键构造的B+树，叶子节点存放的为整张表的行记录数据，每张表只能有一个聚集索引。优化器更倾向采用聚集索引。因为直接就能获取行数据。

为了提升对涉及主键列的查询和排序的速度，InnoDB基于主键来存储行。按照oracle的说法，这也被称为index-orgnized表。其他所有的索引都被称为辅助键，它们存储主键的值（不直接引用行）。

假设有这样的表：

  1. mysql> CREATE TABLE index_example( \
  2.     -> col1 int PRIMARY KEY, \
  3.     -> col2 char(10), \
  4.     -> KEY col2 (col2));

这个表的行是根据col1的值进行排序和存储的。如果搜索col1的任何值，它可以直接指向物理行，这就是聚簇索引非常快的原因。col2上的索引也包含了col1的值，如果搜索col2，则会返回col1的值，反过来在聚簇索引中搜索col1就可以返回实际行的值。

关于主键的选择，有如下一些小技巧：

  • 它应该是UNIQUE(唯一)和NOT NULL(非空)的；
  • 选择最小的可能键，因为所有的二级索引都会存储主键。所以如果主键很大，整个索引也会占用更多的空间；
  • 选择一个单调递增的值。物理行是根据主键进行排序的。所以，如果你选择一个随机键，需要做多次行重排，这会导致性能下降。auto_increment非常适合主键；
  • 最好选择一个主键。如果找不到任何主键，请添加一个auto_increment列。如果你不选择任何内容，InnoDB会在内部生成一个带有6字节行ID的隐藏聚簇索引。

添加索引

可以通过执行ALTER TABLE ADD INDEX命令来添加索引。

  1. mysql> ALTER TABLE employees ADD INDEX name(first_name,last_name);
  2. Query OK, 0 rows affected (0.73 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  5. mysql> SHOW CREATE TABLE employees\G
  6. *************************** 1. row ***************************
  7.        Table: employees
  8. Create Table: CREATE TABLE `employees` (
  9.   `emp_no` int(11) NOT NULL,
  10.   `birth_date` date NOT NULL,
  11.   `first_name` varchar(14) NOT NULL,
  12.   `last_name` varchar(16) NOT NULL,
  13.   `gender` enum('M','F') NOT NULL,
  14.   `hire_date` date NOT NULL,
  15.   PRIMARY KEY (`emp_no`),
  16.   KEY `name` (`first_name`,`last_name`)
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  18. 1 row in set (0.00 sec)

如果不指定索引的名字，则最左边的前缀将被用作索引名。如果发生重名的情况，则会在名字后面附加上_2、_3等，依次类推。

唯一索引

如果你希望索引是唯一的，可以指定关键字UNIQUE。例如:

  1. mysql> ALTER TABLE employees ADD UNIQUE INDEX unique_name (last_name,first_name);
  2. ERROR 1062 (23000): Duplicate entry 'Ritzmann-Erez' for key 'unique_name'

前缀索引

对于字符串列，可以创建仅使用列值的前导部分而非整个列的索引。你需要指定前导部分的长度：

  1. mysql> desc employees;
  2. +------------+---------------+------+-----+---------+-------+
  3. | Field      | Type          | Null | Key | Default | Extra |
  4. +------------+---------------+------+-----+---------+-------+
  5. | emp_no     | int(11)       | NO   | PRI | NULL    |       |
  6. | birth_date | date          | NO   |     | NULL    |       |
  7. | first_name | varchar(14)   | NO   |     | NULL    |       |
  8. | last_name  | varchar(16)   | NO   |     | NULL    |       |
  9. | gender     | enum('M','F') | NO   |     | NULL    |       |
  10. | hire_date  | date          | NO   |     | NULL    |       |
  11. +------------+---------------+------+-----+---------+-------+
  12. 6 rows in set (0.00 sec)
  14. mysql> ALTER TABLE employees ADD INDEX (last_name(10));
  15. Query OK, 0 rows affected (0.61 sec)
  16. Records: 0  Duplicates: 0  Warnings: 0

last_name的最大长度是16个字符，但索引仅基于其前10个字符创建。

删除索引

可以使用ALTER TABLE 命令来删除索引：

  1. mysql> SHOW CREATE TABLE employees\G
  2. *************************** 1. row ***************************
  3.        Table: employees
  4. Create Table: CREATE TABLE `employees` (
  5.   `emp_no` int(11) NOT NULL,
  6.   `birth_date` date NOT NULL,
  7.   `first_name` varchar(14) NOT NULL,
  8.   `last_name` varchar(16) NOT NULL,
  9.   `gender` enum('M','F') NOT NULL,
  10.   `hire_date` date NOT NULL,
  11.   PRIMARY KEY (`emp_no`),
  12.   KEY `last_name` (`last_name`(10))
  13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  14. 1 row in set (0.00 sec)
  16. mysql> ALTER TABLE employees DROP INDEX last_name;
  17. Query OK, 0 rows affected (0.01 sec)
  18. Records: 0  Duplicates: 0  Warnings: 0

生成列的索引

对于封装在函数中的列不能使用索引。假设你在hire_date上添加一个索引：

  1. mysql> ALTER TABLE employees ADD INDEX(hire_date);
  2. Query OK, 0 rows affected (0.31 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0

hire_date上的索引可用于在WHERE子句中带有hire_date的查询：

  1. mysql> EXPLAIN SELECT COUNT(*) FROM employees \
  2.     -> WHERE hire_date > '2000-01-01' \G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: employees
  7.    partitions: NULL
  8.          type: range
  9. possible_keys: hire_date
  10.           key: hire_date
  11.       key_len: 3
  12.           ref: NULL
  13.          rows: 12
  14.      filtered: 100.00
  15.         Extra: Using where; Using index
  16. 1 row in set, 1 warning (0.00 sec)

相反，如果将hire_date放入函数中，MySQL就必须扫描整个表：

  1. mysql> EXPLAIN SELECT COUNT(*) FROM employees \
  2.     -> WHERE YEAR(hire_date) >=2000 \G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: employees
  7.    partitions: NULL
  8.          type: index
  9. possible_keys: NULL
  10.           key: hire_date
  11.       key_len: 3
  12.           ref: NULL
  13.          rows: 299202
  14.      filtered: 100.00
  15.         Extra: Using where; Using index
  16. 1 row in set, 1 warning (0.05 sec)

所以，尽量避免将已被索引的列放入函数中。如果无法避免使用函数，可以创建一个虚拟列并在虚拟列上添加一个索引：

  1. mysql> ALTER TABLE employees ADD hire_date_year YEAR AS (YEAR(hire_date)) \
  2.     -> VIRTUAL, ADD INDEX (hire_date_year);
  3. Query OK, 0 rows affected (0.40 sec)
  4. Records: 0  Duplicates: 0  Warnings: 0
  6. mysql> SHOW CREATE TABLE employees \G
  7. *************************** 1. row ***************************
  8.        Table: employees
  9. Create Table: CREATE TABLE `employees` (
  10.   `emp_no` int(11) NOT NULL,
  11.   `birth_date` date NOT NULL,
  12.   `first_name` varchar(14) NOT NULL,
  13.   `last_name` varchar(16) NOT NULL,
  14.   `gender` enum('M','F') NOT NULL,
  15.   `hire_date` date NOT NULL,
  16.   `hire_date_year` year(4) GENERATED ALWAYS AS (year(`hire_date`)) VIRTUAL,
  17.   PRIMARY KEY (`emp_no`),
  18.   KEY `hire_date` (`hire_date`),
  19.   KEY `hire_date_year` (`hire_date_year`)
  20. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  21. 1 row in set (0.00 sec)

现在，无需在查询中使用YEAR()函数，可以直接在WHERE子句中使用hire_date_year:

  1. mysql> EXPLAIN SELECT COUNT(*) FROM employees \
  2.     -> WHERE hire_date_year >= 2000 \G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: employees
  7.    partitions: NULL
  8.          type: range
  9. possible_keys: hire_date_year
  10.           key: hire_date_year
  11.       key_len: 2
  12.           ref: NULL
  13.          rows: 13
  14.      filtered: 100.00
  15.         Extra: Using where; Using index
  16. 1 row in set, 1 warning (0.00 sec)

注意：这时候，即使使用YEAR(hire_date),优化器也会认识到表达式YEAR()与hire_date_year的定义匹配，并且hire_date_year已被索引，所以它在构建执行计划时会考虑该索引：

  1. mysql> EXPLAIN SELECT COUNT(*) FROM employees \
  2.     -> WHERE YEAR(hire_date) >= 2000 \G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: employees
  7.    partitions: NULL
  8.          type: range
  9. possible_keys: hire_date_year
  10.           key: hire_date_year
  11.       key_len: 2
  12.           ref: NULL
  13.          rows: 13
  14.      filtered: 100.00
  15.         Extra: Using where
  16. 1 row in set, 1 warning (0.00 sec)

不可见索引

如果想删除未使用的索引，可以不立即删除，而是先将其标记为不可见，然后监控应用程序的行为，稍后再删除它。之后，如果还需要该索引，则可以将其标记为可见，这与先删除索引再重新添加相比会快很多。

先添加一个正常索引（如果还没有的话）。

  1. mysql> ALTER TABLE employees ADD INDEX (last_name);
  2. Query OK, 0 rows affected (0.85 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0

查询优化器会使用索引

  1. mysql> EXPLAIN SELECT * FROM employees \
  2.     -> WHERE last_name='Aamodt' \G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: employees
  7.    partitions: NULL
  8.          type: ref
  9. possible_keys: last_name
  10.           key: last_name
  11.       key_len: 66
  12.           ref: const
  13.          rows: 205
  14.      filtered: 100.00
  15.         Extra: NULL
  16. 1 row in set, 1 warning (0.00 sec)

设置索引为不可见

  1. mysql> ALTER TABLE employees ALTER INDEX last_name INVISIBLE;
  2. Query OK, 0 rows affected (0.02 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  5. mysql> EXPLAIN SELECT * FROM employees  WHERE last_name='Aamodt'\G
  6. *************************** 1. row ***************************
  7.            id: 1
  8.   select_type: SIMPLE
  9.         table: employees
  10.    partitions: NULL
  11.          type: ALL
  12. possible_keys: NULL
  13.           key: NULL
  14.       key_len: NULL
  15.           ref: NULL
  16.          rows: 299290
  17.      filtered: 0.06
  18.         Extra: Using where
  19. 1 row in set, 1 warning (0.00 sec)
  21. mysql> SHOW CREATE TABLE employees\G
  22. *************************** 1. row ***************************
  23.        Table: employees
  24. Create Table: CREATE TABLE `employees` (
  25.   `emp_no` int(11) NOT NULL,
  26.   `birth_date` date NOT NULL,
  27.   `first_name` varchar(14) NOT NULL,
  28.   `last_name` varchar(16) NOT NULL,
  29.   `gender` enum('M','F') NOT NULL,
  30.   `hire_date` date NOT NULL,
  31.   `hire_date_year` year(4) GENERATED ALWAYS AS (year(`hire_date`)) VIRTUAL,
  32.   PRIMARY KEY (`emp_no`),
  33.   KEY `hire_date` (`hire_date`),
  34.   KEY `last_name` (`last_name`) /*!80000 INVISIBLE */,
  35.   KEY `hire_date_year` (`hire_date_year`)
  36. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  37. 1 row in set (0.00 sec)

可以看到，将其标记为不可见后，它就无法使用了。可以再次将其标记为可见：

  1. mysql> ALTER TABLE employees ALTER INDEX last_name VISIBLE;
  2. Query OK, 0 rows affected (0.01 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  5. mysql> SHOW CREATE************* 1. row ***************************
  6.        Table: employees
  7. Create Table: CREATE TABLE `employees` (
  8.   `emp_no` int(11) NOT NULL,
  9.   `birth_date` date NOT NULL,
  10.   `first_name` varchar(14) NOT NULL,
  11.   `last_name` varchar(16) NOT NULL,
  12.   `gender` enum('M','F') NOT NULL,
  13.   `hire_date` date NOT NULL,
  14.   `hire_date_year` year(4) GENERATED ALWAYS AS (year(`hire_date`)) VIRTUAL,
  15.   PRIMARY KEY (`emp_no`),
  16.   KEY `hire_date` (`hire_date`),
  17.   KEY `last_name` (`last_name`),
  18.   KEY `hire_date_year` (`hire_date_year`)
  19. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  20. 1 row in set (0.00 sec)

降序索引

在MySQL 8之前，索引的定义中可以包含顺序（升序或降序），但它只是被解析并没有被实现。索引值始终以升序存储。MySQL 8.0引入了对降序索引的支持。因此，索引定义中指定的顺序不会被忽略。降序索引实际上按降序存储关键值。请记住，对于降序查询，反向扫描升序索引效率不高。

在多列索引中，可以指定某些列降序。这样做对同时具有升序和降序的ORDER BY子句的查询很有用。

假设想要按照first_name升序和last_name降序对employees表进行排序。MySQL不能使用first_name和last_name上的索引。

如果没有降序索引：

  1. mysql> SHOW CREATE TABLE employees\G
  2. *************************** 1. row ***************************
  3.        Table: employees
  4. Create Table: CREATE TABLE `employees` (
  5.   `emp_no` int(11) NOT NULL,
  6.   `birth_date` date NOT NULL,
  7.   `first_name` varchar(14) NOT NULL,
  8.   `last_name` varchar(16) NOT NULL,
  9.   `gender` enum('M','F') NOT NULL,
  10.   `hire_date` date NOT NULL,
  11.   `hire_date_year` year(4) GENERATED ALWAYS AS (year(`hire_date`)) VIRTUAL,
  12.   PRIMARY KEY (`emp_no`),
  13.   KEY `hire_date` (`hire_date`) /*!80000 INVISIBLE */,
  14.   KEY `last_name` (`last_name`) /*!80000 INVISIBLE */,
  15.   KEY `name` (`first_name`,`last_name`),
  16.   KEY `hire_date_year` (`hire_date_year`) /*!80000 INVISIBLE */
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  18. 1 row in set (0.00 sec)

在explain计划中，可以看到索引（first_name和last_name）没有被使用：

  1. mysql> EXPLAIN SELECT * FROM employees ORDER BY first_name ASC, \
  2.     -> last_name DESC LIMIT 10 \G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: employees
  7.    partitions: NULL
  8.          type: ALL
  9. possible_keys: NULL
  10.           key: NULL
  11.       key_len: NULL
  12.           ref: NULL
  13.          rows: 299290
  14.      filtered: 100.00
  15.         Extra: Using filesort
  16. 1 row in set, 1 warning (0.00 sec)

添加降序索引

  1. mysql> ALTER TABLE employees ADD INDEX name_desc (first_name ASC, \
  2.     -> last_name DESC);
  3. Query OK, 0 rows affected (0.78 sec)
  4. Records: 0  Duplicates: 0  Warnings: 0

添加降序索引后，查询就可以使用该索引了：

  1. mysql> EXPLAIN SELECT * FROM employees ORDER BY \
  2.     -> first_name ASC,  last_name DESC LIMIT 10\G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: employees
  7.    partitions: NULL
  8.          type: index
  9. possible_keys: NULL
  10.           key: name_desc
  11.       key_len: 124
  12.           ref: NULL
  13.          rows: 10
  14.      filtered: 100.00
  15.         Extra: NULL
  16. 1 row in set, 1 warning (0.00 sec)

同一个索引可以用于其他排序方式

即通过向后索引扫描以first_name降序和last_name升序进行排序：

  1. mysql> EXPLAIN SELECT * FROM employees ORDER BY \
  2.     -> first_name DESC,  last_name ASC LIMIT 10\G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: employees
  7.    partitions: NULL
  8.          type: index
  9. possible_keys: NULL
  10.           key: name_desc
  11.       key_len: 124
  12.           ref: NULL
  13.          rows: 10
  14.      filtered: 100.00
  15.         Extra: Backward index scan
  16. 1 row in set, 1 warning (0.00 sec)