Mysql索引和查询优化

blacksanji

已于 2022-07-06 10:46:21 修改

阅读量146

点赞数

文章标签： mysql

于 2020-05-07 23:05:30 首次发布

本文链接：https://blog.csdn.net/blacksanji/article/details/105984038

版权

聚簇索引和费聚簇索引

1、所谓聚簇索引，就是将索引和数据放到一起，找到索引也就找到了数据，我们刚才看到的B+树索引就是一种聚簇索引，而非聚簇索引就是将数据和索引分开，查找时需要先查找到索引，然后通过索引回表找到相应的数据。InnoDB有且只有一个聚簇索引，而MyISAM中都是非聚簇索引。

2、InnoDB中只要有主键被定义，那么主键列被作为一个聚簇索引，而其它索引都将被作为非聚簇索引。

查询优化器

一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。

优化过程大致如下：

1、根据搜索条件，找出所有可能使用的索引

2、计算全表扫描的代价

3、计算使用不同索引执行查询的代价

4、对比各种执行方案的代价，找出成本最低的那一个

覆盖索引

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。

查询优化

1、<>的优化

<>不能使用索引，是用union all聚合查询解决

(select id from orders where amount > 100) union all(select id from orders where amount < 100 and amount > 0)

2、or的优化

innoDB下 or不能使用组合索引,union 组合查询

(select id，product_name from orders where mobile_no = '13421800407') union(select id，product_name from orders where user_id = 100);

3、执行批量sql，需要分批执行

int pageNo = 1;

int PAGE_SIZE = 100;

while(true) {

    List<Integer> batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');

    if (CollectionUtils.isEmpty(batchIdList)) {

    return;

    }

    update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')

    pageNo ++;

}

4、索引

1、普通索引：最基本的索引。

2、组合索引：多个字段上建立的索引，能够加速复合查询条件的检索。

3、唯一索引：与普通索引类似，但索引列的值必须唯一，允许有空值。

4、组合唯一索引：列值的组合必须唯一。

5、主键索引：特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用primary key约束。

6、全文索引：用于海量文本的查询，MySQL5.6之后的InnoDB和MyISAM均支持全文索引。由于查询精度以及扩展性不佳，更多的企业选择Elasticsearch。

索引优化

1、分页查询很重要，如果查询数据量超过30%，MYSQL不会使用索引。

2、单表索引数不超过5个、单个索引字段数不超过5个。

3、字符串可使用前缀索引，前缀长度控制在5-8个字符。

4、字段唯一性太低，增加索引没有意义，如：是否删除、性别。

5、合理使用覆盖索引，如下所示：

select loginname, nickname from member where login_name = ?

loginname, nickname两个字段建立组合索引，比login_name简单索引要更快