mysql基础-9.索引

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

索引原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

缺点：影响增删改的速度

哪些适合建索引

1. 唯一性强的字段需要建立索引，例如手机号，用户名
2. 不要对重复列表添加索引，例如性别

主键默认增加索引

小试牛刀

这里通过程序给表插入了一千万的数据，对比查索引字段和普通字段的区别

可以看到，用了索引和没用索引的差别还是很大的

如何查看是否命中了索引

explain select * from users where id = 1;

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index>all

type=const:只匹配一行数据,通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。

eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

type=ref:非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行， 然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

type=range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现 了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而 结束语另一点，不用扫描全部索引

type=index:出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。

type=all:遍历全表以找到匹配的行

索引分类

1. 普通索引 index
2. 主键索引，一张表只有一个主键索引
3. 唯一索引
4. 外键索引
5. 联合索引

# 添加索引
alter table users add index index_users_name(name);
alter table users add index  index_users_nameAgeSex(name,age,sex);


# 删除索引
drop index index_users_nameAgeSex on users ;

索引失效

1. 不使用索引列进行查询
2. 数据类型不匹配
3. 前缀索引的使用不当，例如模糊匹配以%开头
4. 使用函数或表达式进行查询
5. 联合索引违反了最左前缀原则

ALTER TABLE users ADD INDEX index_users_nameAgeSex(name,age,sex);

name                      √
name age                   √
name age pos           √
name pos              √

最左边匹配上就行

参考文档 

mysql索引失效： mysql索引什么情况下会失效_MySQL索引失效的几种情况-常见问题-PHP中文网

mysql索引失效原因： 百度安全验证

查看索引命中： mysql查看索引是否命中_mob64ca12ea4e24的技术博客_51CTO博客

最左前缀：原创：史上最全最通俗易懂的，索引最左前缀匹配原则（认真脸）-CSDN博客

explain介绍： MYSQL关键字Explain解析_mysql explain type ref-CSDN博客