MySQL(三) —— MySQL性能优化之索引优化

分手后的思念是犯贱 2022-05-15 16:35 451阅读 0赞

## MySQL索引优化 ##

最后， 关于索引失效问题

### 一、如何选择合适的列建立索引？ ###

1、在where从句、group by 从句、order by 从句、on 从句中出现的列

2、索引字段越小越好。可减少I/O操作，提高

3、离散度大的列放在**联合索引**的前面，如

**如何判断列的离散度？**

去重查询看列的唯一值，唯一值越多则离散度越大。

举例：

查看account.user\_info表的数据结构如下：

mysql> desc account.user_info;
    +---------------+--------------+------+-----+---------+----------------+
    | Field         | Type         | Null | Key | Default | Extra          |
    +---------------+--------------+------+-----+---------+----------------+
    | id            | int(11)      | NO   | PRI | NULL    | auto_increment |
    | account       | varchar(128) | YES  | MUL | NULL    |                |
    | serverid      | int(11)      | YES  | MUL | 0       |                |
    | channel       | varchar(128) | YES  | MUL | NULL    |                |
    | sub_channel   | varchar(128) | YES  |     | NULL    |                |
    | plat          | varchar(128) | YES  |     | NULL    |                |
    | udid          | varchar(128) | YES  |     | NULL    |                |
    | ip            | varchar(512) | YES  |     | NULL    |                |
    | logdate       | datetime     | YES  |     | NULL    |                |
    | createdate    | datetime     | YES  |     | NULL    |                |
    | vip_lv        | int(11)      | YES  |     | 0       |                |
    | recharge      | int(11)      | YES  |     | 0       |                |
    | last_serverId | int(11)      | YES  | MUL | 0       |                |
    +---------------+--------------+------+-----+---------+----------------+
    13 rows in set (0.00 sec)

查询serverid，和channel的唯一值，唯一值越多，离散度越大。

SELECT COUNT(DISTINCT serverid),count(distinct channel) FROM account.user\_info;

mysql> SELECT COUNT(DISTINCT serverid),count(distinct channel) FROM account.user_info;
    +--------------------------+-------------------------+
    | COUNT(DISTINCT serverid) | count(distinct channel) |
    +--------------------------+-------------------------+
    |                       97 |                      12 |
    +--------------------------+-------------------------+
    1 row in set (0.00 sec)

由此看出，serverid的离散度更大，所以建立联合索引时，则选择index（serverid，channel）。

mysql>  SELECT COUNT(1) FROM account.user_info WHERE channel='mmo_no_ajzz' AND serverid=1;
    +----------+
    | COUNT(1) |
    +----------+
    |       39 |
    +----------+
    1 row in set (0.00 sec)

### **一、索引的维护及优化  --  重复及冗余索引** ###

重复索引：指相同的列以相同的顺序建立的同类型的索引，如primary key id 和 unique id 为重复索引。

冗余索引：指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，如primary key id 和 key(name，id)冗余。

删除不用索引。

1、全值匹配索引失效

2、最佳左前缀索引失效

2、不在索引列上做任何操作，索引失效

3、存储引擎不能使用索引中范围条件右侧的列

4、尽量使用覆盖索引，减少select\*

5、Mysql在使用不等于（!=或者<>）的时候无法使用索引会导致索引失效而全表扫描

6、is null、is not null 也无法使用索引

7、Like 以通配符开头（‘%abc...’）mysql索引失效会变成全表扫描的操作