mysql+优化+源码_MySQL性能优化

1 #下面是查询语句中不使用索引和使用索引的对比。首先，分析未使用索引的查询情况，EXPLAIN语句执行如下：2 explain select * from t_fruits where f_name='apple';3 explain select * from t_fruits where f_name='apple';4 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

5 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

6 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

7 | 1 | SIMPLE | t_fruits | NULL | ALL | NULL | NULL | NULL | NULL | 16 | 10.00 | Using where |

8 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

9 1 row in set (0.02 sec)

可以看到，rows列的值是15，说明“select * from t_fruits where f_name='apple';”这个查询语句扫描了表中的15条记录。

然后，在t_fruits表中f_name字段上加上索引。

1 create index index_name ont_fruits(f_name)2 >OK3 > 时间: 0.295s

现在，再分析上面的查询语句。执行的explain语句及结果如下：

1 +----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+

2 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

3 +----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+

4 | 1 | SIMPLE | t_fruits | NULL | ref | index_name | index_name | 765 | const | 1 | 100.00 | NULL |

5 +----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+

6 1 row in set (0.02 sec)

2.使用索引查询

2.1使用LIKE关键字的查询语句

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为‘%’，索引不会起作用。只有'%'不在第一个位置，索引才会起作用。

1 explain select * from t_fruits where f_name like '%x';2 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

3 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

4 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

5 | 1 | SIMPLE | t_fruits | NULL | ALL | NULL | NULL | NULL | NULL | 16 | 11.11 | Using where |

6 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

7 1 row in set (0.02sec)8

9 explain select * from t_fruits where f_name like 'x%';10 +----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+

11 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

12 +----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+

13 | 1 | SIMPLE | t_fruits | NULL | range | index_name | index_name | 765 | NULL | 4 | 100.00 | Using index condition |

14 +----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+

15 1 row in set (0.02 sec)

2.2使用多列缩影的查询语句

MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

例：本例在表t_fruits中f_id，f_price字段创建多列索引，验证多列索引的使用情况。

1 mysql> explain select * from t_fruits where f_id = 'a1';2 +----+-------------+----------+------------+-------+------------------------+---------+---------+-------+------+----------+-------+

3 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

4 +----+-------------+----------+------------+-------+------------------------+---------+---------+-------+------+----------+-------+

5 | 1 | SIMPLE | t_fruits | NULL | const | PRIMARY,index_id_price | PRIMARY | 30 | const | 1 | 100.00 | NULL |

6 +----+-------------+----------+------------+-------+------------------------+---------+---------+-------+------+----------+-------+

7 1 row in set (0.04sec)8

9 mysql> explain select * from t_fruits where f_price = '2.2';10 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

11 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

12 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

13 | 1 | SIMPLE | t_fruits | NULL | ALL | NULL | NULL | NULL | NULL | 16 | 10.00 | Using where |

14 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

15 1 row in set (0.04 sec)

从第一条语句查询结果可以看出，“f_id='a1'”的记录有1条。第1条语句共扫描了1条记录，并且使用了索引index_id_price。从第2条语句查询结果可以看出，rows列的值16，说明查询语句共扫描了16条记录，并且key列值为null，说明第2条记录没有使用索引，因为f_price字段是多列索引的第2个字段。

2.3使用OR关键字的查询语句

查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。

3.优化数据库结构

3.1 将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

3.2 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为中间表的查询，以此来提高查询效率。

3.3 增加冗余字段

3.4 优化插入记录的速度

3.4.1 禁用索引

1 #禁用索引的语句如下：2 ALTER TABLEtable_name DISABLE KEYS;3 #开启索引4 ALTER TABLE table_name ENABLE KEYS;

3.3.2 禁用唯一性检查

1 #禁用唯一性检查的语句2 SET UNIQUE_CHECKS=0;3 #开启唯一性检查的语句4 SET UNIQUE_CHECKS=1;

3.3.3 使用批量插入