mysql索引及其优化

测试数据个数要大于2个

“索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据结构。”

一、索引类型：

1.1索引类型：可以使用SHOW INDEX FROM table_name;查看索引详情

1.2索引创建

索引类型	特点	插入
PRIMARY KEY（主键索引）	它是一种特殊的唯一索引，不允许有空值。	ALTER TABLE table_name ADD PRIMARY KEY ( col )
UNIQUE(唯一索引)	与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。	ALTER TABLE table_name ADD UNIQUE (col)
INDEX(普通索引)	最基本的索引，没有任何限制	ALTER TABLE table_name ADD INDEX index_name (col)
FULLTEXT(全文索引)	仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。	ALTER TABLE table_name ADD FULLTEXT ( col )
组合索引	为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。	ALTER TABLE table_name ADD INDEX index_name (col1, col2, col3 )

二、索引优化实战

2.1用于测试的page表结构：

2.2 explain作用

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。使用方法，在select语句前加上explain就可以了。

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
一般来说，得保证查询至少达到range级别，最好能达到ref。

2.3查询优化

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。

explain SELECT * FROM page WHERE name LIKE '%陈%';

优化方式：尽量在字段后面使用模糊查询。

explain SELECT * FROM page WHERE name LIKE '陈%';

如果需求是要在前面使用模糊查询

使用MySQL内置函数INSTR(str,substr) 来匹配,查询字符串出现的角标位置

2.where条件仅包含复合索引非前置列

复合（联合）索引包含key_part1，key_part2l俩列，但SQL语句没有包含索引前置列"key_part1"，按照MySQL联合索引的最左匹配原则，不会走联合索引。

explain SELECT * FROM page WHERE status =1;

3.隐式类型转换造成不使用索引

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

explain SELECT * FROM page WHERE iphone = 13595224995;

4.查询条件不能用 !=、<>

注意：需要扩大数据的数量：一般要大于2个数据

explain SELECT * FROM page WHERE id >2;

5.当数据量大时，避免使用where 1=1的条件。

通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。

 explain SELECT * FROM page WHERE 1=1;

6.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

可以将表达式、函数操作移动到等号右侧。

-- 全表扫描
explain select * from page where id+1=3;
-- 走索引
 explain select * from page where id=2+1;

7.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

如：select id from table where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from table where num=0

8.应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。

explain select * from page where id=5 or id=6 or id=2;
 explain select * from page where id=5 union all select * from page where id=6 union all select * from page where id=2;

9.in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 代替 in 。

explain select * from page where id  in (2,3,4,5);
explain select * from page where id  between 2 and 5;

10.很多时候用 exists 代替 in 是一个好的选择

select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)

11.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

12.避免出现select *

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。建议提出业务实际需要的列数，将指定列名以取代select 。