mysql索引创建和使用优缺点讲解
索引是一种特殊的文件, 它包含着对数据表里所有记录的引用. 如果把数据库看做一本书的话, 那么索引就相当于这本书的目录.
创建索引:
create index 索引名称 on 表名 (字段,字段....);
删除索引:
DROP INDEX 索引名称 ON 表名;
显示索引信息
SHOW INDEX FROM table_name;
索引的优点和缺点
优点: 大大加快了数据源的查询
缺点: 创建和维护索引需要先消耗时间,并且随着索引数量的增加,所耗费的时间也会增加..
索引的使用原则:
对经常有更新 的表, 要避免建过多的索引, 对经常用于查询的子弹应该创建索引
数据量小的最好不要使用索引, 由于数据较小, 可能查询全部数据所花费的时间比遍历索引的时间还要短, 索引就不回产生效果,
在同一值少的列不要建立索引,比如学生表的”性别”字段
注意: 索引是在存储引擎中实现的, 也就是说不同的存储引擎, 会使用不同的索引,
索引分类
Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引
单列索引: 一个索引包含单个列,但一个表中可以有多个单列索引,
单列索引可以分为: 普通索引, 唯一索引, 主键索引
普通索引:
mysql中基本索引类型, 没有什么限制, 允许在定义索引的列中插入重复值和空值,只是为了查询数据快一点
唯一索引:
索引列中的值必须是唯一的, 但是允许为空值
主键索引:
是一种特殊的唯一索引, 不允许有空值
组合索引:又称为联合索引
在表中的多个字段组合上创建的索引, 只有在查询条件中使用了这些字段的左边字段时, 索引才会被使用,
比如在mysql表中创建了一个联合索引, myindex(A,B,C)
create index myindex on 表名 (A,B,C);
当where 查询条件 包含A时, 才后悔调用myindex 索引,也就是说,查询条件为BC或者B或者C时,都不会经过myindex.
当查询条件为ABC, AB,AC的时候,都会调用myindex索引
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时耗空间。
介绍下mysql提供的很有用的一个工具：执行计划
格式：explain + SQL语句;
如下图:
注释：
table: 显示这条sql是关于哪张表的
type: 显示这条sql的查询类型属于哪种,从最好的到最差
const < eq _reg < ref < range < index SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是const最好
possible_keys: 可能用到的索引
key:实际用到的索引
key_len:显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好 。
ref: 显示索引的哪一列被使用了,如果可能的话, 是一个常数
rows:估计需要扫描的行数
extra;显示以上信息之外的其他信息
Using index–查询使用了覆盖索引(Covering Index)，即通过索引就能返回结果，无需访问表
Using where– MySQL 服务器从存储引擎收到行后再进行“后过滤”(Post-filter)。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃
Using temporary-使用到临时表
Using filesort–若查询所需的排序与使用的索引的排序一致，因为索引是已排序的，因此按索引的顺序读取结果返回，否则，在取得结果后，还需要按查询所需的顺序对结果进行排序，这时就会出现 Using filesort加索引的原则
1) 确定数据量：一般数据规模在10W以上需要考虑索引(并不绝对，当添加索引后，查询效率明显提升)
2) 较频繁的作为查询条件的字段应该创建索引
有时间查询，给日期列添加索引
有字符串列，尽量避免加索引，可以进行类型调整，或加前缀索引
列值范围小的列不用加索引，如枚举列，状态列
在业务上有唯一意义的列，要加唯一索引
在有联合索引的基础上，如果不满足最左原则的查询场景较多，给单列再加索引
索引不宜过多，一般不超过5个
更新非常频繁的字段不适合创建索引
不会出现在where之后的字段不适合创建索引嗯, 先写到这, 后续我了解的多了继续补充