使用前缀索引对MySQL优化的方法

下文给大家带来有关使用前缀索引对MySQL优化的方法内容，相信大家一定看过类似使用前缀索引对MySQL优化的方法的文章。我们给大家带来的有何不同呢？一起来看看正文部分吧，相信看完你一定会有所收获。


1.查看表结构(root@localhost) [prod_db]> show create table t_file_info\G;*************************** 1. row ***************************       Table: t_file_infoCreate Table: CREATE TABLE `t_file_info` (  `id` varchar(36) NOT NULL DEFAULT '',  `devid` varchar(64) DEFAULT NULL,  `areaid` int(11) DEFAULT NULL,  `fileid` varchar(256) NOT NULL,  `filename` varchar(256) DEFAULT NULL,  `filesize` int(11) DEFAULT NULL,  `filemd5` varchar(40) DEFAULT NULL,  `extend` varchar(4000) DEFAULT NULL,  `status` int(11) NOT NULL DEFAULT '0',  `createdate` datetime DEFAULT NULL,  `fileurl` varchar(256) DEFAULT NULL,  `businessid` bigint(20) NOT NULL DEFAULT '0',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.fileid是我们查询的一个条件，正常是需要创建索引的。

 select char_length('63f2a078018649ca9948f5469550bf2a/group1/M00/00/DA/wKgj2FcMquGAVuJcAAAI4FL7ZCA388.jpg') ;+-----------------------------------------------------------------------------------------------------+| char_length('63f2a078018649ca9948f5469550bf2a/group1/M00/00/DA/wKgj2FcMquGAVuJcAAAI4FL7ZCA388.jpg') |+-----------------------------------------------------------------------------------------------------+|                                                                                                  84 |+-----------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)--经过和开发沟通了解，前32位相当于uuid可以确定唯一值。

3.这样的字段，我们怎么创建索引，是不是有规律可循。继续查看

--查看选择率select count(distinct(fileid))/count(*) AS Selectivity from t_file_info;select count(distinct left(fileid,32))/count(*) from t_file_info;(root@localhost) [prod_db]> select count(distinct(fileid))/count(*) from t_file_info;+----------------------------------+| count(distinct(fileid))/count(*) |+----------------------------------+|                           1.0000 |+----------------------------------+1 row in set (0.17 sec)(root@localhost) [prod_db]> select count(distinct left(fileid,32))/count(*) from t_file_info;+------------------------------------------+| count(distinct left(fileid,32))/count(*) |+------------------------------------------+|                                   0.9999 |+------------------------------------------+1和0.9999几乎可以等同，其实这里因为点特殊情况，正常应该都是1才对的。

4.查看无索引的执行计划

explain select id,fileid from prod_db.t_file_info where fileid='63f2a078018649ca9948f5469550bf2a/group1/M00/00/DA/wKgj2FcMquGAVuJcAAAI4FL7ZCA388.jpg';(root@localhost) [prod_db]> explain select id,fileid from prod_db.t_file_info where fileid='63f2a078018649ca9948f5469550bf2a/group1/M00/00/DA/wKgj2FcMquGAVuJcAAAI4FL7ZCA388.jpg';+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows  | Extra       |+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+|  1 | SIMPLE      | t_file_info | ALL  | NULL          | NULL | NULL    | NULL | 35109 | Using where |+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+1 row in set (0.00 sec)

5.创建前缀索引，查看执行计划

alter table `prod_db`.`t_file_info` add index idx_t_file_info_fileid(fileid(32));(root@localhost) [prod_db]> explain select id,fileid from prod_db.t_file_info where fileid='63f2a078018649ca9948f5469550bf2a/group1/M00/00/DA/wKgj2FcMquGAVuJcAAAI4FL7ZCA388.jpg';+----+-------------+-------------+------+------------------------+------------------------+---------+-------+------+-------------+| id | select_type | table       | type | possible_keys          | key                    | key_len | ref   | rows | Extra       |+----+-------------+-------------+------+------------------------+------------------------+---------+-------+------+-------------+|  1 | SIMPLE      | t_file_info | ref  | idx_t_file_info_fileid | idx_t_file_info_fileid | 98      | const |    1 | Using where |+----+-------------+-------------+------+--返回1行才是我们想看到的

6.创建索引

(root@localhost) [prod_db]> alter table `prod_db`.`t_file_info` add index idx_t_file_info_fileid(fileid(32));Query OK, 0 rows affected (5 min 36.03 sec)Records: 0  Duplicates: 0  Warnings: 0创建索引观察系统资源使用情况，内存机会没有变化，但是CPU单核几乎跑满(root@localhost) [prod_db]> select count(fileid) from t_file_info;+---------------+| count(fileid) |+---------------+|      12299419 |+---------------+1 row in set (14.94 sec) --千万行

小结：
1.了解前缀索引的实用场景。
2.要和开发沟通，了解业务，才能创建最合适的索引。
3.创建索引对系统性能会有很大的影响，要选择一个合适的时间点去创建，评估好影响。任何事情不要想当然，当你没经验，还想当然的时候很容易出问题。

对于上文关于使用前缀索引对MySQL优化的方法，大家觉得是自己想要的吗？如果想要了解更多相关，可以继续关注我们的行业资讯板块。