02 mysql 索引基本命令

阅读数: 次 2019-12-09

如需转载，请根据 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议许可，附上本文作者及链接。
本文作者： 执笔成念
作者昵称： zbcn
本文链接： https://1363653611.github.io/zbcn.github.io/2019/12/09/database_02_sql%E7%B4%A2%E5%BC%95%E5%9F%BA%E6%9C%AC%E5%91%BD%E4%BB%A4/

sql 索引基本命令

# 查看表描述
desc `user`
# 查看索引
show index from user

# 索引一经创建不能修改，如果要修改索引，只能删除重建
# 删除索引
drop INDEX name_3 on `user`

# 主键索引 PRIMARY KEY：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

# 唯一索引 UNIQUE：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
# 添加唯一索引
alter table `user` add UNIQUE unique_name (`name`)

# 添加唯一组合索引
alter TABLE `user` add UNIQUE unique_name_age (name,age)

# 普通索引 INDEX：这是最基本的索引，它没有任何限制
#添加普通索引
alter TABLE `user` add INDEX index_name (name)

# 组合索引 INDEX：即一个索引包含多个列，多用于避免回表查询。
# 添加普通组合索引
alter TABLE `user` add index index_name_age (name,age)

# 全文索引 FULLTEXT：也称全文检索，是目前搜索引擎使用的一种关键技术
# 添加全文索引
alter TABLE `user` add FULLTEXT index_remark (remark)

索引设计的基本原则
- 适合索引的列是出现在where子句中的列。或者连接子句中的列
- 基数小的列，索引效果差，没有必要在此列建立索引
- 使用段索引，如果对长字符串进行索引，应该指定一个前缀长度，这样能够节省大量的索引空间。
- 不要过度索引。索引需要额外的磁盘空间，并降低了写操作的性能。在修改内容的时候会进行索引的更新甚至是重构。索引列越多，这个时间就越长。所以只需要保持索引有利于查询即可。
索引优化实战
- 索引使用情况 SHOW STATUS LIKE 'handle_read%'
  - Handler_read_key：如果索引正在工作，Handler_read_key的值将很高。
  - Handler_read_rnd_next：数据文件中读取下一行的请求数，如果正在进行大量的表扫描，值将较高，则说明索引利用不理想。
索引优化规则
- 如果MySQL估计使用索引比全表扫描还慢，则不会使用索引。（返回数据的比例是重要的指标，比例越低越容易命中索引。记住这个范围值——30%，后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。）
- 前导模糊查询不能命中索引。
  - 用执行计划查看查询语句
    EXPLAIN select * from user where name like '%s%'
- 数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。
  - eg： EXPLAIN SELECT * FROM user WHERE name=1;
- 复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中符合索引。
- union、in、or都能够命中索引，建议使用in。
- 用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。
- 负向条件查询不能使用索引，可以优化为in查询。
  负向条件有：!=、<>、not in、not exists、not like等。
- 范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等。
- 数据库执行计算不会命中索引。
- 利用覆盖索引进行查询，避免回表。
总结
a. 更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的。

b. 区分度不大的字段上不宜建立索引：类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以外的情况下，优化器不会选择使用索引。

c. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响insert速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生。

d. 多表关联时，要保证关联字段上一定有索引。