优化MySQL索引的方法总结

一、MySQL索引优化原则

1、索引字段的选择：较常用、较频繁的字段

2、联合索引：可增加索引利用率，但不宜过多

3、索引长度：尽可能短，节省空间，加快查询速度

4、不在索引列上进行操作：避免索引失效

5、适当建立冗余索引：提高查询效率，减少表扫描

二、MySQL索引如何优化

1、删除不必要的索引：无用的索引降低写操作速度，占用磁盘空间。

-- 删除无效的索引
DROP INDEX [index_name] ON [table_name];

2、修改索引类型：将较大的数据类型改为较小的数据类型；将字符串类型改为数字类型；

-- 将varchar(50)改为int类型
ALTER TABLE [table_name] MODIFY [column_name] int;

3、合并重复索引：减少存储空间，提高查询效率

-- 合并多余索引
ALTER TABLE [table_name] DROP INDEX [index_name1], DROP INDEX [index_name2], ADD INDEX [new_index_name]([column_name1], [column_name2]);

4、优化联合索引：快速定位数据，查询数据时为最左前缀匹配的情况

-- 联合索引优化
CREATE INDEX [new_index_name] ON [table_name]([column_name1], [column_name2]);

5、使用覆盖索引：减少数据库二次查找

-- 使用覆盖索引
SELECT [column_name1], [column_name2] FROM [table_name] WHERE [column_name1] = 'x' AND [column_name2] = 'y';

三、MySQL索引优化面试题

1、如何查看索引是否失效？

-- 查看索引是否失效
EXPLAIN SELECT [column_name1], [column_name2] FROM [table_name] WHERE [column_name1] = 'x' AND [column_name3] = 'y';

2、如何查询某个表有哪些索引？

-- 查询某表索引
SHOW INDEXES FROM [table_name];

3、如何判断是否需要建立一个索引？

判断该字段是否作为 where、join、group by、order by 的条件，若是则需要建立索引。

四、MySQL索引优化和查询优化

1、使用limit语句进行分页查询：限制返回结果的数量，减少查询时间

-- 使用limit语句
SELECT [column_name] FROM [table_name] WHERE [column_name] = 'x' LIMIT 0, 10;

2、减少子查询：子查询嵌套越多，查询速度越慢

3、缩小查询数据范围：避免全表扫描

4、避免使用不必要的函数、表达式或转换

5、避免使用大量的UNION操作：UNION查询需要将所有结果集合并后再返回，耗费时间

五、MySQL日期索引优化

1、合理建立时间索引：提高查询效率，过期数据自动清理

-- 建立时间索引
ALTER TABLE [table_name] ADD INDEX [new_index_name]([column_name]);

2、合理使用分区表：按时间段分割数据，减少大表的查询时间

-- 创建分区表
CREATE TABLE [table_name] (
  [column_name1] int(11) NOT NULL,
  [column_name2] varchar(50) NOT NULL,
  [column_name3] date NOT NULL
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS([column_name3]))
(
  PARTITION p201301 VALUES LESS THAN (TO_DAYS('2013-02-01')),
  PARTITION p201302 VALUES LESS THAN (TO_DAYS('2013-03-01')),
  PARTITION p201303 VALUES LESS THAN (TO_DAYS('2013-04-01'))
);

六、MySQL怎么索引优化查询

1、使用索引提示：告诉MySQL使用哪个索引，避免MySQL重选索引

-- 使用索引提示
SELECT [column_name1], [column_name2] FROM [table_name] USE INDEX ([index_name]) WHERE [column_name1] = 'x';

2、优化join查询：尽量让小表作为驱动表，避免全表扫描

3、选择合适的数据类型：避免使用text、blob等大字段类型

4、定期分析表：优化表、索引碎片，减少慢查询

-- 定期分析表
ANALYZE TABLE [table_name];

七、MySQL in不走索引优化

1、将in改为exists：子查询返回结果集为0或1，查询速度较快

-- 将in改为exists
SELECT [column_name1] FROM [table_name] WHERE EXISTS (SELECT [column_name2] FROM [sub_table_name] WHERE [sub_column_name] = [column_name1]);

2、使用union all：将多个in条件查询拆分成多个查询语句，提高查询效率

-- 使用union all
SELECT [column_name] FROM [table_name] WHERE [column_name1] in ('a', 'b', 'c')
UNION ALL
SELECT [column_name] FROM [table_name] WHERE [column_name1] in ('d', 'e', 'f');

八、MySQL优化like查询索引

1、使用左匹配：%value，使用覆盖索引减少数据库二次查找

-- 使用左匹配
SELECT [column_name] FROM [table_name] WHERE [column_name] LIKE 'value%';

2、使用全文本索引：MySQL自带全文本搜索功能，使用关键字匹配查询较快

-- 使用全文本索引
CREATE FULLTEXT INDEX [index_name] ON [table_name]([column_name]);
SELECT [column_name] FROM [table_name] WHERE MATCH ([column_name]) AGAINST ('keyword');

九、MySQL group by索引优化

1、避免使用select *：查询全部字段降低查询效率

2、减少子查询：使用join表连接，避免子查询嵌套

3、使用group by后面所有的字段建立联合索引

-- 建立联合索引
ALTER TABLE [table_name] ADD INDEX [new_index_name]([column_name1], [column_name2]);