2020-08-03发表Mysql4 分钟读完 (大约569个字)0次访问

Mysql索引优化

一、索引设计原则

1、减少大基数范围查询

1）少用!=,<>;

2）少用is null, is not null;

2、等值查询时，避免转换操作

1）避免在等号左侧的索引列上做计算操作，如函数，类型转换；

2）避免等号右侧的类型与索引类不同而引起类型转换；

3、聚合索引查询时的最左前缀法则

1）范围条件的索引会中断左前缀匹配；

4、尽量应用覆盖索引，查询必要的列而非所有列

5、少用范围查询

1）少用or，in

2）Like查询时尽量采用后模糊“xxx%”，而非前模糊“%xxx”;

6、使用orderby或group by时尽量对索引列操作，避免出现filesort

7、in, exist

1）select * from A where id in (select id from B)

以B为驱动表去遍历A；适合于B数据量 < A数据量

2）select * from A where exists (select 1 from B where B.id = A.id)

以A为驱动表去遍历B；适合于B数据量 > A数据量

8、count

count(*) ，count(1)会统计字段为null的行；

count(col)不会统计该列为null的行；

二、索引选择

enum idx_type {
    CLUSTERED_PK,
    UNIQUE,
    NOT_UNIQUE,
    FULLTEXT
};

1、索引选择顺序

聚集索引 > 唯一索引 > 普通索引 > 全文索引

2、索引基数：Cardinality

select index from multiple indexes

To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index).

selectivity

A property of data distribution, the number of distinct values in a column (its cardinality) divided by the number of records in the table. High selectivity means that the column values are relatively unique, and can retrieved efficiently through an index. If you (or the query optimizer) can predict that a test in a WHERE clause only matches a small number (or proportion) of rows in a table, the overall query tends to be efficient if it evaluates that test first, using an index.

通过命令show index from table可查看索引基数。

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| edi_cache |          0 | PRIMARY  |            1 | id          | A         |         965 |     NULL | NULL   |      | BTREE      |         |               |
| edi_cache |          0 | uniq_key |            1 | cache_key   | A         |         966 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+