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

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索引失效会变成全表扫描的操作

8、字符串不加单引号索引失效

9、少用or，用or来连接时会索引失效