共计 10065 个字符，预计需要花费 26 分钟才能阅读完成。
这篇文章将为大家详细讲解有关 mysql 中索引配置如何优化，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
2 索引及查询优化
索引的类型
普通索引：这是最基本的索引类型，没唯一性之类的限制。
唯一性索引：和普通索引基本相同，但所有的索引列值保持唯一性。
主键：主键是一种唯一索引，但必须指定为 PRIMARY KEY。
全文索引：MYSQL 从 3.23.23 开始支持全文索引和全文检索。在 MYSQL 中，全文索引的索引类型为 FULLTEXT。全文索引可以在 VARCHAR 或者 TEXT 类型的列上创建。
大多数 MySQL 索引 (PRIMARY KEY、UNIQUE、INDEX 和 FULLTEXT) 使用 B 树中存储。空间列类型的索引使用 R - 树，MEMORY 表支持 hash 索引。
单列索引和多列索引（复合索引）
索引可以是单列索引，也可以是多列索引。对相关的列使用索引是提高 SELECT 操作性能的最佳途径之一。
多列索引：
MySQL 可以为多个列创建索引。一个索引可以包括 15 个列。对于某些列类型，可以索引列的左前缀，列的顺序非常重要。
多列索引可以视为包含通过连接索引列的值而创建的值的排序的数组。一般来说，即使是限制最严格的单列索引，它的限制能力也远远低于多列索引。
最左前缀
多列索引有一个特点，即最左前缀（Leftmost Prefixing）。假如有一个多列索引为 key(firstname lastname age)，当搜索条件是以下各种列的组合和顺序时，MySQL 将使用该多列索引：
firstname，lastname，age
firstname，lastname
firstname
也就是说，相当于还建立了 key(firstname lastname)和 key(firstname)。
索引主要用于下面的操作：
快速找出匹配一个 WHERE 子句的行。
删除行。当执行联接时，从其它表检索行。
对具体有索引的列 key_col 找出 MAX()或 MIN()值。由预处理器进行优化，检查是否对索引中在 key_col 之前发生所有关键字元素使用了 WHERE key_part_# = constant。在这种情况下，MySQL 为每个 MIN()或 MAX()表达式执行一次关键字查找，并用常数替换它。如果所有表达式替换为常量，查询立即返回。例如：
SELECT MIN(key2), MAX (key2) FROM tb WHERE key1=10;
如果对一个可用关键字的最左面的前缀进行了排序或分组(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。如果所有关键字元素后面有 DESC，关键字以倒序被读取。
在一些情况中，可以对一个查询进行优化以便不用查询数据行即可以检索值。如果查询只使用来自某个表的数字型并且构成某些关键字的最左面前缀的列，为了更快，可以从索引树检索出值。
SELECT key_part3 FROM tb WHERE key_part1=1
有时 MySQL 不使用索引，即使有可用的索引。一种情形是当优化器估计到使用索引将需要 MySQL 访问表中的大部分行时。(在这种情况下，表扫描可能会更快些）。然而，如果此类查询使用 LIMIT 只搜索部分行，MySQL 则使用索引，因为它可以更快地找到几行并在结果中返回。例如：
合理的建立索引的建议：
(1) 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和 CPU 缓存中都需要更少的空间，处理起来更快。
(2) 简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在 MySQL 中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储 IP 地址。
(3) 尽量避免 NULL：应该指定列为 NOT NULL，除非你想存储 NULL。在 MySQL 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用 0、一个特殊的值或者一个空串代替空值
这部分是关于索引和写 SQL 语句时应当注意的一些琐碎建议和注意点。
1. 当结果集只有一行数据时使用 LIMIT 1
2. 避免 SELECT *，始终指定你需要的列
从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与 WEB 服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
3. 使用连接（JOIN）来代替子查询(Sub-Queries)
连接（JOIN）.. 之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
4. 使用 ENUM、CHAR 而不是 VARCHAR，使用合理的字段属性长度
5. 尽可能的使用 NOT NULL
6. 固定长度的表会更快
7. 拆分大的 DELETE 或 INSERT 语句
8. 查询的列越小越快
Where 条件
在查询中，WHERE 条件也是一个比较重要的因素，尽量少并且是合理的 where 条件是很重要的，尽量在多个条件的时候，把会提取尽量少数据量的条件放在前面，减少后一个 where 条件的查询时间。
有些 where 条件会导致索引无效：
where 子句的查询条件里有！=，MySQL 将无法使用索引。
where 子句使用了 Mysql 函数的时候，索引将无效，比如：select * from tb where left(name, 4) = xxx
使用 LIKE 进行搜索匹配的时候，这样索引是有效的：select * from tbl1 where name like xxx%，而 like %xxx% 时索引无效
三、 配置优化
安装 MySQL 后，配置文件 my.cnf 在 /MySQL 安装目录 /share/mysql 目录中，该目录中还包含多个配置文件可供参考，有 my-large.cnf，my-huge.cnf， my-medium.cnf，my-small.cnf，分别对应大中小型数据库应用的配置。win 环境下即存在于 MySQL 安装目录中的.ini 文件。
下面列出了对性能优化影响较大的主要变量，主要分为连接请求的变量和缓冲区变量。
1. 连接请求的变量：
1) max_connections
MySQL 的最大连接数，增加该值增加 mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于 MySQL 会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
数值过小会经常出现 ERROR 1040: Too many connections 错误，可以过 conn% 通配符查看当前状态的连接数量，以定夺该值的大小。
show variables like max_connections 最大连接数
show status like max_used_connections 响应的连接数
如下：
mysql show variables like lsquo;max_connections lsquo;;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| max_connections | 256 |
+———————–+——-+
mysql show status like lsquo;max%connections lsquo;;
+———————–+——-+
| Variable_name | Value |
+—————————-+——-+
| max_used_connections | 256|
+—————————-+——-+
max_used_connections / max_connections * 100%（理想值 asymp; 85%）
如果 max_used_connections 跟 max_connections 相同 那么就是 max_connections 设置过低或者超过服务器负载上限了，低于 10% 则设置过大。
2) back_log
MySQL 能暂存的连接数量。当主要 MySQL 线程在一个很短时间内得到非常多的连接请求，这就起作用。如果 MySQL 的连接数据达到 max_connections 时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即 back_log，如果等待连接的数量超过 back_log，将不被授予连接资源。
back_log 值指出在 MySQL 暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的 TCP/IP 连接的侦听队列的大小。
当观察你主机进程列表（mysql show full processlist），发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大 back_log 的值了。
默认数值是 50，可调优为 128，对于系统设置范围为小于 512 的整数。
3) interactive_timeout
一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。
默认数值是 28800，可调优为 7200。
2. 缓冲区变量
全局缓冲：
4) key_buffer_size
key_buffer_size 指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值 Key_read_requests 和 Key_reads，可以知道 key_buffer_size 设置是否合理。比例 key_reads / key_read_requests 应该尽可能的低，至少是 1:100，1:1000 更好（上述状态值可以使用 SHOW STATUS LIKE lsquo;key_read% rsquo; 获得）。
key_buffer_size 只对 MyISAM 表起作用。即使你不使用 MyISAM 表，但是内部的临时磁盘表是 MyISAM 表，也要使用该值。可以使用检查状态值 created_tmp_disk_tables 得知详情。
举例如下：
mysql show variables like lsquo;key_buffer_size lsquo;;
+——————-+————+
| Variable_name | Value |
+———————+————+
| key_buffer_size | 536870912 |
+———— ———-+————+
key_buffer_size 为 512MB，我们再看一下 key_buffer_size 的使用情况：
mysql show global status like lsquo;key_read% lsquo;;
+————————+————-+
| Variable_name | Value |
+————————+————-+
| Key_read_requests| 27813678764 |
| Key_reads | 6798830 |
+————————+————-+
一共有 27813678764 个索引读取请求，有 6798830 个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：
key_cache_miss_rate =Key_reads / Key_read_requests * 100%，设置在 1 /1000 左右较好
默认配置数值是 8388600(8M)，主机有 4GB 内存，可以调优值为 268435456(256MB)。
5) query_cache_size
使用查询缓冲，MySQL 将查询结果存放在缓冲区中，今后对于同样的 SELECT 语句（区分大小写），将直接从缓冲区中读取结果。
通过检查状态值 Qcache_*，可以知道 query_cache_size 设置是否合理（上述状态值可以使用 SHOW STATUS LIKE lsquo;Qcache% rsquo; 获得）。如果 Qcache_lowmem_prunes 的值非常大，则表明经常出现缓冲不够的情况，如果 Qcache_hits 的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果 Qcache_hits 的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在 SELECT 语句中加入 SQL_NO_CACHE 可以明确表示不使用查询缓冲。
与查询缓冲有关的参数还有 query_cache_type、query_cache_limit、query_cache_min_res_unit。
query_cache_type 指定是否使用查询缓冲，可以设置为 0、1、2，该变量是 SESSION 级的变量。
query_cache_limit 指定单个查询能够使用的缓冲区大小，缺省为 1M。
query_cache_min_res_unit 是在 4.1 版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为 4K。检查状态值 Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小 query_cache_min_res_unit。
举例如下：
mysql show global status like lsquo;qcache% lsquo;;
+——————————-+—————–+
| Variable_name | Value |
+——————————-+—————–+
| Qcache_free_blocks | 22756 |
| Qcache_free_memory | 76764704 |
| Qcache_hits | 213028692 |
| Qcache_inserts | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| Qcache_not_cached | 13385031 |
| Qcache_queries_in_cache | 43560 |
| Qcache_total_blocks | 111212 |
+——————————-+—————–+
mysql show variables like lsquo;query_cache% lsquo;;
+————————————–+————–+
| Variable_name | Value |
+————————————–+———–+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 203423744 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+————————————–+—————+
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过 20%，可以用 FLUSH QUERY CACHE 整理缓存碎片，或者试试减小 query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在 25% 以下的话说明 query_cache_size 设置的过大，可适当减小；查询缓存利用率在 80%以上而且 Qcache_lowmem_prunes 50 的话说明 query_cache_size 可能有点小，要不就是碎片太多。
查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例服务器查询缓存碎片率=20.46%，查询缓存利用率=62.26%，查询缓存命中率=1.94%，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。
每个连接的缓冲
6) record_buffer_size
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。
默认数值是 131072(128K)，可改为 16773120 (16M)
7) read_rnd_buffer_size
随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL 会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但 MySQL 会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
一般可设置为 16M
8) sort_buffer_size
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速 ORDER BY 或 GROUP BY 操作。
默认数值是 2097144(2M)，可改为 16777208 (16M)。
9) join_buffer_size
联合查询操作所能使用的缓冲区大小
record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size 为每个线程独占，也就是说，如果有 100 个线程连接，则占用为 16M*100
10) table_cache
表高速缓存的大小。每当 MySQL 访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值 Open_tables 和 Opened_tables，可以决定是否需要增加 table_cache 的值。如果你发现 open_tables 等于 table_cache，并且 opened_tables 在不断增长，那么你就需要增加 table_cache 的值了（上述状态值可以使用 SHOW STATUS LIKE lsquo;Open%tables rsquo; 获得）。注意，不能盲目地把 table_cache 设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
1G 内存机器，推荐值是 128-256。内存在 4GB 左右的服务器该参数可设置为 256M 或 384M。
11) max_heap_table_size
用户可以创建的内存表 (memory table) 的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变，即 set @max_heap_table_size=#
这个变量和 tmp_table_size 一起限制了内部内存表的大小。如果某个内部 heap（堆积）表大小超过 tmp_table_size，MySQL 可以根据需要自动将内存中的 heap 表改为基于硬盘的 MyISAM 表。
12) tmp_table_size
通过设置 tmp_table_size 选项来增加一张临时表的大小，例如做高级 GROUP BY 操作生成的临时表。如果调高该值，MySQL 同时将增加 heap 表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的 MyISAM 表。
mysql show global status like lsquo;created_tmp% lsquo;;
+——————————–+———+
| Variable_name | Value |
+———————————-+———+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+——————————–+———–+
每次创建临时表，Created_tmp_tables 增加，如果临时表大小超过 tmp_table_size，则是在磁盘上创建临时表，Created_tmp_disk_tables 也增加,Created_tmp_files 表示 MySQL 服务创建的临时文件文件数，比较理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% = 25% 比如上面的服务器 Created_tmp_disk_tables / Created_tmp_tables * 100% =1.20%，应该相当好了
默认为 16M，可调到 64-256 最佳，线程独占，太大可能内存不够 I / O 堵塞
13) thread_cache_size
可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。
通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。
默认值为 110，可调优为 80。
14) thread_concurrency
推荐设置为服务器 CPU 核数的 2 倍，例如双核的 CPU, 那么 thread_concurrency 的应该为 4；2 个双核的 cpu, thread_concurrency 的值应为 8。默认为 8
15) wait_timeout
指定一个请求的最大连接时间，对于 4GB 左右内存的服务器可以设置为 5 -10。
3. 配置 InnoDB 的几个变量
innodb_buffer_pool_size
对于 InnoDB 表来说，innodb_buffer_pool_size 的作用就相当于 key_buffer_size 对于 MyISAM 表的作用一样。InnoDB 使用该参数指定大小的内存来缓冲数据和索引。对于单独的 MySQL 服务器，最大可以把该值设置成物理内存的 80%。
根据 MySQL 手册，对于 2G 内存的机器，推荐值是 1G（50%）。
innodb_flush_log_at_trx_commit
主要控制了 innodb 将 log buffer 中的数据写入日志文件并 flush 磁盘的时间点，取值分别为 0、1、2 三个。0，表示当事务提交时，不做日志写入操作，而是每秒钟将 log buffer 中的数据写入日志文件并 flush 磁盘一次；1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush 磁盘的操作，确保了事务的 ACID；设置为 2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次 flush 磁盘操作。
实际测试发现，该值对插入数据的速度影响非常大，设置为 2 时插入 10000 条记录只需要 2 秒，设置为 0 时只需要 1 秒，而设置为 1 时则需要 229 秒。因此，MySQL 手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
根据 MySQL 手册，在允许丢失最近部分事务的危险的前提下，可以把该值设为 0 或 2。
innodb_log_buffer_size
log 缓存大小，一般为 1 -8M，默认为 1M，对于较大的事务，可以增大缓存大小。
可设置为 4M 或 8M。
innodb_additional_mem_pool_size
该参数指定 InnoDB 用来存储数据字典和其他内部数据结构的内存池大小。缺省值是 1M。通常不用太大，只要够用就行，应该与表结构的复杂度有关系。如果不够用，MySQL 会在错误日志中写入一条警告信息。
根据 MySQL 手册，对于 2G 内存的机器，推荐值是 20M，可适当增加。
innodb_thread_concurrency=8
推荐设置为 2*(NumCPUs+NumDisks)，默认一般为 8
关于“mysql 中索引配置如何优化”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
