Mysql优化-索引
查看MYSQL版本
select version();InnoDB引擎的要求MYSQL版本5.6及以上支持全文索引 MyISAM各版本均支持全文索引
概念
Innodb和Myisam是两种类类型
下面介绍一下他们的区别：
区别 | Innodb | Myisam |
|---|---|---|
事务 | 安全 | 非安全 |
锁 | 行级 | 表级 |
效率 | 低 | 高 |
索引 | 聚集索引 | 非聚集索引 |
外键 | 支持 | 不支持 |
使用环境 | 需要事务，大量增，改 | 多查询，不需要事务 |
下面说说他们的区别 聚集索引：
- 属于Innodb。
- 按照主键B+树的排列方式存放，子节点存放的就是数据。 如果没有主键，以第一列为聚集索引.
- 只有一个聚集索引。
- 普通索引指向聚集索引。
非聚集索引：
- 属于MyIsam。
- 普通索引和非聚集索引没什么区别。
- 存放的是地址。
聚集索引与非聚集索引
- 聚集索引，常见就是主键，一个表中只能拥有一个聚集索引。一个表中可以拥有多个非聚集索引。
- 使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
- 非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
- 不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。
单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上; 用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引);
禁用缓存
因为我们要测试添加索引的效果，所以就先禁用缓存，防止影响测试结果。
show global variables like '%query_cache%';
set global query_cache_size=0;
set global query_cache_type=0;查询表引擎：
show variables like '%storage_engine%';表引擎使用innodb.第一次查询也会走数据文件，第二次直接走buffer_pool,也比直接查询数据文件要快
哪些字段可以加索引？
- 表的主键、外键必须有索引；
- 数据量超过300的表应该有索引；
- 经常与其他表进行连接的表，在连接字段上应该建立索引；
- 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
- 索引应该建在较高选择性的字段上；
- 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
- 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
- 复合索引中的主列字段，要是使用较高选择性的字段；
- 复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
- 如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
- 如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
- 如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
- 频繁进行数据操作的表，不要建立太多的索引；
- 删除无用的索引，避免对执行计划造成负面影响；
较高选择性：就是通过该字段就可以筛选出满足条件的尽可能少的数据
以上是一些普遍的建立索引时的判断依据。 一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。 因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。 另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的,不但影响查询性能 还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大
索引的生效规则
对于一个不包含子查询的SQL来说，最终只可能有一个索引生效。
独立索引生效规则
- 一条sql语句只能使用一个索引是错误的。
- 生效的索引跟建立索引的顺序以及查询的顺序均无关，和字段的选择性有关，较高选择性的字段对应的索引优先生效。
复合索引的生效规则
如果第一个条件不能单独提供较高的选择性，复合索引将会非常有用。
较高选择性：就是通过该字段作为条件就可以筛选出满足条件的尽可能少的数据。
在复合索引中，索引第一位的column很重要，只要查询语句包含了复合索引的第一个条件,基本上就会使用到该复合索引（可能会使用其他索引）。我们在建符合索引的时候应该按照column的重要性从左往右建。
生效规则
- 多列索引发挥作用，需要满足左前缀要求
- 只要包含第一个条件，索引都生效，跟顺序无关
以index(a,b,c)为例
语句 | 索引是否发挥作用 |
|---|---|
where a=3 | 是 |
where a=3 and b=5 | 是 |
where a=3 and b=5 and c=4 | 是 |
where b=3 | 否 |
where c=4 | 否 |
where a=3 and c=4 | a列能用到索引,c不能 |
where a=3 and b>10 and c=7 | a能,b能,c不能 |
where a=3 and b like ‘xxx%’ and c=7 | a能,b能,c不能 |
where b=5 and c=4 and a=3 | 能 |
where b=5 and c=4 | 不能 |
where b=2 and c=3 and d=5 and a=1 | 能 |
复合索引的选择
比如我们有这样一个SQL
select * from userresult where askid=800808 and uid=110996854;我们有两种复合索引选择：
- idx_1 (askid,uid)
- idx_2 (uid,askid)
那到底用哪一个呢，利用【sarg】方法：
select sum(askid=800808),sum(uid=110996854) from userresult;比如结果如下
- sum(askid=800808): 6
- sum(uid=110996854): 2
因为通过uid过滤后的数据更少，所以我们用idx_2
索引越多越好？
- 大多数情况下索引能大幅度提高查询效率，但是过多的索引反而会影响速度。
- 过小的表，建索引可能会更慢哦。
- 已有某字段为主索引的多列索引，就没必要添加该字段的独立索引。
索引什么时候生效？
生效的情况
- like ‘xxxx%’
- <，<=，=，>，>=，BETWEEN
索引什么时候会失效?
- 最佳左前缀法则
- 在索引列上做任何操作（计算、函数、（手动或自动）类型转换），会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- <>，not in ，!=
- is null，is not null
- like ‘%xxxx%’
- 字符串不加单引号索引失效（自动类型转换）
- or左边有索引、右边没索引也会失效
IN是否能用到索引？
IN能使用到索引，但是当表内存在多个单列索引时，MySQL不会自动选择in条件使用的索引，即使它是最优索引。
使用IN查询时，数据为相同类型的数据是可以正常使用索引的
但是，当IN里面嵌套子查询时索引就失效了！
EXPLAIN
SELECT SQL_NO_CACHE
*
FROM t_question
WHERE testid IN (
SELECT
questionid
FROM
t_question_knowledge_point
WHERE
`pointid` = 105
)
LIMIT 0,10;索引的使用情况
原因是当使用select之后使用了函数内部转换，mysql是不支持函数索引的。
怎么解决呢？
直接与子查询进行关联，这种写法相当于IN子查询写法，而且效率有不少的提高
EXPLAIN
SELECT SQL_NO_CACHE
a.*
FROM
t_question a,(
SELECT
questionid
FROM
t_question_knowledge_point
WHERE
`pointid` = 105
) t2
WHERE
a.testid = t2.questionid
LIMIT 0,10;索引的使用情况
不要用下面的写法
EXPLAIN
SELECT SQL_NO_CACHE
a.*
FROM
t_question a LEFT JOIN(
SELECT
questionid
FROM
t_question_knowledge_point
WHERE
`pointid` = 105
) t2
ON
a.testid = t2.questionid
LIMIT 0,10;索引使用情况
全文索引(LIKE优化)
优化的方式就是建立全文检索FULLTEXT
使用Mysql全文检索FULLTEXT的先决条件
- MyISAM 引擎表和 InnoDB 引擎表（MySQL 5.6 及以上版本）都支持中文全文检索。
- 建立全文检索的字段类型必须是char,varchar,text
- InnoDB引擎的要求mysql版本5.6及以上 MyISAM没有版本约束
- 表没有进行分区，进行分区的表是无法创建全文索引的
参数设置
# | 参数名称 | 默认值 | 最小值 | 最大值 | 作用 |
|---|---|---|---|---|---|
1 | ft_min_word_len | 4 | 1 | 3600 | MyISAM 引擎表全文索引包含的最小词长度 |
2 | ft_query_expansion_limit | 20 | 0 | 1000 | MyISAM引擎表使用 with query expansion 进行全文搜索的最大匹配数 |
3 | innodb_ft_min_token_size | 3 | 0 | 16 | InnoDB 引擎表全文索引包含的最小词长度 |
4 | innodb_ft_max_token_size | 84 | 10 | 84 | InnoDB 引擎表全文索引包含的最大词长度 |
show global variables like 'ft_%'; --查看 MyISAM 引擎表全文检索相关参数
show global variables like 'innodb_ft%'; --查看 InnoDB 引擎表全文检索相关参数建立全文检索 在建表中用FullText关键字标识字段,已存在的表用 ALTER TABLE (或 CREATE INDEX) 创建索引
CREATE fulltext INDEX index_name ON table_name(colum_name);比如我们要搜索这样的题目
关于静摩擦力，下列说法正确的是我们会用到这样的语句
select * from t_question where `docHtml` like '%关于静摩擦力%' limit 0,10;优化方式 对docHtml2字段添加FULLTEXT 用以下语句查询
SELECT * FROM t_question WHERE MATCH (docHtml) AGAINST ('+关于静摩擦力*' IN BOOLEAN MODE) limit 0,10;MATCH() 函数的所有参数必须是从来自于同一张表的列，同时必须是同一个FULLTEXT索引中的一部分，除非MATCH()是IN BOOLEAN MODE的。
多个字段建立复合全文索引，MATCH() 函数的参数也必须是多个字段
比如 A+B建立索引
SELECT * FROM t_question WHERE MATCH (A,B) AGAINST ('+关于静摩擦力*' IN BOOLEAN MODE) limit 0,10;FULLTEXT解析器用“ ”（空格）、“,”（逗号）“.”（点号）作为默认的单词分隔符，因此对于不使用这些分隔符的语言如汉语来说FULLTEXT解析器不能正确的识别单词，对于这种情况需做额外处理。
如果我们用
AGAINST ('+关于静摩擦' IN BOOLEAN MODE)是搜索不到的， 因为原语句被拆分为了关于静摩擦力和下列说法正确的是要想查询到就要添加*号 如：AGAINST ('+关于静摩擦力*' IN BOOLEAN MODE)。 另外我们想要用AGAINST ('+静摩擦力*' IN BOOLEAN MODE)是搜索不到的，前面加*号也没用。
order by关键字优化
- 尽量使用index方式排序，避免使用filesort方式。
- order by满足两种情况会使用index排序：
- ①、order by语句使用索引最左前列，
- ②、使用where子句与order by子句条件列组合满足索引最左前列
- 双路排序：MySQL4.1之前，两次扫描磁盘
- 单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列进行输出，效率更高一点，但是它会使用更多的空间，因为它把每一行都保存在内存中了
优化策略: 增大sort_buffer_size参数的设置、增大max_length_for_sort_data参数的设置
group by关键字优化
实质是先排序后进行分组，遵照索引键的最佳左前缀，
当无法使用索引列时，增大sort_buffer_size+max_length_for_sort_data参数的设置
怎样查看索引是否生效？
explain显示了MySQL如何使用索引来处理select语句以及连接表。
可以帮助选择更好的索引和写出更优化的查询语句。
使用方法，在select语句前加上explain就可以了：
如：
explain select surname,first_name form a,b where a.id=b.idEXPLAIN列的解释：
字段 | 含义 |
|---|---|
id | id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询。 |
select_type | A：simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个B：primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个C：union：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是unionD：dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响E：union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为nullF：subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subqueryG：dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响H：derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select |
table | 显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。 |
type | 依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引 |
possible_keys | 显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句。 |
key | 查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。 |
key_len | 用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。 |
ref | 如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。 |
rows | 这里是执行计划中估算的扫描行数，不是精确值。 |
Extra | 关于MYSQL如何解析查询的额外信息。但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢。 |
filtered | 使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。 |
type列返回的描述的意义
依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL。 除了all之外，其他的type都可以使用到索引。 除了index_merge之外，其他的type只可以用到一个索引。
值 | 含义 |
|---|---|
system | 表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index |
const | 使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描 |
eq_ref | 出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref |
ref | 不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。 |
fulltext | 全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引 |
ref_or_null | 与ref方法类似，只是增加了null值的比较。实际用的不多。 |
unique_subquery | 用于where中的in形式子查询，子查询返回不重复值唯一值 |
index_subquery | 用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。 |
range | 索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。 |
index_merge | 表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range |
index | 索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。 |
all | 这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。 |
Extra列返回的描述的意义
关键词 | 含义 |
|---|---|
Distinct | 在select部分使用了distinct关键字。 |
Not exists | MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。 |
Range checked for each Record | 没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。 |
Using filesort | 排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中。 |
Using index | 查询时不需要回表查询，直接通过索引就可以获取查询的数据。 |
Using temporary | 看到这个的时候，查询需要优化了。创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。 |
Using where | 表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。 |
using sort_union，using_union，using intersect，using sort_intersection | using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。 |
using join buffer（block nested loop），using join buffer（batched key accss） | 5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。 |
firstmatch(tb_name) | 5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个。 |
loosescan(m..n) | 5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个。 |
查看索引数据大小
所有表
SELECT
TABLE_NAME,
concat(truncate(data_length/1024/1024,2),' MB') '库大小',
concat(truncate(index_length/1024/1024,2),' MB') '索引大小'
FROM
information_schema. TABLES
WHERE
TABLE_SCHEMA = 'xhkjedu_ques_new'
GROUP BY
TABLE_NAME
ORDER BY
data_length DESC;某个表总大小
SELECT
TABLE_SCHEMA '库名',
concat(TRUNCATE (sum(data_length) / 1024 / 1024,2),' MB') '库大小',
concat(TRUNCATE (sum(index_length) / 1024 / 1024,2),'MB') '索引大小'
FROM
information_schema. TABLES
WHERE
table_schema = 'xhkjedu_ques_new'
AND table_name = 't_question';
GROUP BY
TABLE_SCHEMA
ORDER BY
data_length DESC;某个表每个索引大小
SELECT
table_name 表名,
index_name 索引名,
sum(stat_value) 数据页页数,
concat(round(sum(stat_value)* @@innodb_page_size/10000000,2),'M') 大小
FROM
mysql.innodb_index_stats
WHERE
database_name = 'xhkjedu_question'
AND table_name = 't_question'
AND stat_description LIKE 'Number of pages in the index'
GROUP BY
table_name, index_name;删除无用索引后索引大小不变
当您的库中删除了大量的数据或者索引后，您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。不但占用空间，还会影响查询速度。
在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。
注意该操作会锁定表，数据量大时所需时间较长。
optimize table t_question;