资讯专栏INFORMATION COLUMN

上云采购季！| 爆款云服务器低至4.6元/月，首单享0.9折

免费试用
首页/文章专栏/Mysql索引设计原则：

Mysql索引设计原则：

Ocean / 1963人阅读

摘要：使用索引合并策略选择合理的索引列顺序索引列的顺序意味着索引首先按照最左列进行排序，然后是第二列，以此类推。如就会按照进行排序，然后才是根据这次的这个查询条件来说最好的索引是。

推荐书籍：http://pan.baidu.com/s/1sjJIyRV 任务描述：

假设一高频查询如下
SELECT * FROM user WHERE area="amoy" AND sex=0 ORDER BY last_login DESC limit 30;
如何建立索引？描述考虑的过程

user表如下：
初始化100W条数据，其中，area要通过IP查询生成，sex为 0,1 随机

CREATE TABLE user (
id int(10) NOT NULL AUTO_INCREMENT COMMENT "自增编号",
username varchar(30) NOT NULL DEFAULT "0" COMMENT "用户名",
password varchar(30) NOT NULL DEFAULT "0" COMMENT "密码",
area varchar(30) NOT NULL COMMENT "地址",
sex int(10) NOT NULL COMMENT "性别1，男；2，女。",
last_login int(10) NOT NULL COMMENT "最近一次登录时间戳",
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=892013 DEFAULT CHARSET=latin1

最终我的索引
(last_login,area)

索引原则： 1.where和order by等的字段建立索引 2.使用唯一索引：对于last_login,area等字段重复的次数比较少，可以使用索引；而sex无非就两个值：性别1，男；2，不值得索引 3.多列索引：不要为每一个列多带带建立索引，这样并不能将mysql索引的效率最大化。使用“索引合并策略” 4.选择合理的索引列顺序：索引列的顺序意味着索引首先按照最左列进行排序，然后是第二列，以此类推。如(last_login,area)会先按照 last_login 进行排序，然后才是area。 5.将选择性最高的索引放到前面，也就是会所按照这个条件搜索到的数据最少，选择性就越高，比如选择性：last_login> area> sex。 6.索引不是越多越好，适合的索引可以提高查询效率，但是会降低写入效率，根据项目保持两者的平衡性最好了。 总结上面，首先sex不适合建立索引，有没有索引对于效率的提升意义不大，其次索引会按照最左列进行排序，因此将last_login放到最前面 测试过程：

user表
没有任何索引的查询相关日志：
SELECT * FROM user WHERE area="美国ATT用户" AND sex=0 ORDER BY last_login DESC limit 30; 0.57s
SELECT * FROM user WHERE area="泰国" AND sex=0 ORDER BY last_login DESC limit 30; 0.56s
SELECT * FROM user WHERE area="台湾省台湾大宽频" AND sex=0 ORDER BY last_login DESC limit 30; 0.55s
SELECT * FROM user WHERE area="美国弗吉尼亚州" AND sex=0 ORDER BY last_login DESC limit 30; 0.59s
SELECT * FROM user WHERE area="德国奔驰汽车" AND sex=0 ORDER BY last_login DESC limit 30; 0.55s
SELECT * FROM user WHERE area="台湾省中华电信" AND sex=0 ORDER BY last_login DESC limit 30; 0.55s
SELECT * FROM user WHERE area="韩国" AND sex=0 ORDER BY last_login DESC limit 30; 0.57s
SELECT * FROM user WHERE area="拉美地区" AND sex=0 ORDER BY last_login DESC limit 30; 0.58s
SELECT * FROM user WHERE area="美国纽约(Prudential)" AND sex=0 ORDER BY last_login DESC limit 30; 0.57s
SELECT * FROM user WHERE area="印度尼西亚" AND sex=0 ORDER BY last_login DESC limit 30; 0.57s
共花费时间：5.66s

建立索引area:
ALTER TABLE user ADD INDEX index_area (area) ;
SELECT * FROM user WHERE area="美国ATT用户" AND sex=0 ORDER BY last_login DESC limit 30; 0.06s
SELECT * FROM user WHERE area="泰国" AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area="台湾省台湾大宽频" AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area="美国弗吉尼亚州" AND sex=0 ORDER BY last_login DESC limit 30; 0.10s
SELECT * FROM user WHERE area="德国奔驰汽车" AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
SELECT * FROM user WHERE area="台湾省中华电信" AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area="韩国" AND sex=0 ORDER BY last_login DESC limit 30; 0.11s
SELECT * FROM user WHERE area="拉美地区" AND sex=0 ORDER BY last_login DESC limit 30; 0.20s
SELECT * FROM user WHERE area="美国纽约(Prudential)" AND sex=0 ORDER BY last_login DESC limit 30; 0.07s
SELECT * FROM user WHERE area="印度尼西亚" AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
共花费时间：0.66s
可见，建立area以后对性能的影响是巨大的(5.66/0.66 约为8.5758倍)
删除索引：ALTER TABLE user DROP INDEX index_area;
删除area索引发现时间又变成了0.57s

建立last_login索引：
SELECT * FROM user WHERE area="美国ATT用户" AND sex=0 ORDER BY last_login DESC limit 30; 0.03s
SELECT * FROM user WHERE area="泰国" AND sex=0 ORDER BY last_login DESC limit 30; 0.09s
SELECT * FROM user WHERE area="台湾省台湾大宽频" AND sex=0 ORDER BY last_login DESC limit 30; 0.51s
SELECT * FROM user WHERE area="美国弗吉尼亚州" AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area="德国奔驰汽车" AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
SELECT * FROM user WHERE area="台湾省中华电信" AND sex=0 ORDER BY last_login DESC limit 30; 0.07s
SELECT * FROM user WHERE area="韩国" AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area="拉美地区" AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area="美国纽约(Prudential)" AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
SELECT * FROM user WHERE area="印度尼西亚" AND sex=0 ORDER BY last_login DESC limit 30; 0.06s
共花费时间：0.87s
同样能够提升性能（5.66/0.87 约为6.5057倍）

建立sex索引：
ALTER TABLE user ADD INDEX index_sex (sex) ;
SELECT * FROM user WHERE area="美国ATT用户" AND sex=0 ORDER BY last_login DESC limit 30; 0.87s
SELECT * FROM user WHERE area="泰国" AND sex=0 ORDER BY last_login DESC limit 30; 0.87s
SELECT * FROM user WHERE area="台湾省台湾大宽频" AND sex=0 ORDER BY last_login DESC limit 30; 0.87s
SELECT * FROM user WHERE area="美国弗吉尼亚州" AND sex=0 ORDER BY last_login DESC limit 30; 0.89s
SELECT * FROM user WHERE area="德国奔驰汽车" AND sex=0 ORDER BY last_login DESC limit 30; 0.88s
SELECT * FROM user WHERE area="台湾省中华电信" AND sex=0 ORDER BY last_login DESC limit 30; 0.87s
SELECT * FROM user WHERE area="韩国" AND sex=0 ORDER BY last_login DESC limit 30; 0.86s
SELECT * FROM user WHERE area="拉美地区" AND sex=0 ORDER BY last_login DESC limit 30; 0.88s
SELECT * FROM user WHERE area="美国纽约(Prudential)" AND sex=0 ORDER BY last_login DESC limit 30; 0.87s
SELECT * FROM user WHERE area="印度尼西亚" AND sex=0 ORDER BY last_login DESC limit 30; 0.87s
共花费时间：8.73s
同样能够提升性能（5.66s/8.73 约为0.6483倍）效率反而降低了？？求解？
建立这个sex索引还不如不建。

删除索引：
ALTER TABLE user DROP INDEX index_sex;
发现时间又变成了0.57s左右，

建立两个多带带的索引：
ALTER TABLE user
ADD INDEX index_area (area) ,
ADD INDEX index_last_login (last_login) ;

SELECT * FROM user WHERE area="美国ATT用户" AND sex=0 ORDER BY last_login DESC limit 30; 0.09s
SELECT * FROM user WHERE area="泰国" AND sex=0 ORDER BY last_login DESC limit 30; 0.33s
SELECT * FROM user WHERE area="台湾省台湾大宽频" AND sex=0 ORDER BY last_login DESC limit 30; 0.21s
SELECT * FROM user WHERE area="美国弗吉尼亚州" AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area="德国奔驰汽车" AND sex=0 ORDER BY last_login DESC limit 30; 0.28s
SELECT * FROM user WHERE area="台湾省中华电信" AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area="韩国" AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area="拉美地区" AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area="美国纽约(Prudential)" AND sex=0 ORDER BY last_login DESC limit 30; 0.03s
SELECT * FROM user WHERE area="印度尼西亚" AND sex=0 ORDER BY last_login DESC limit 30; 0.67s

发现建立两个多带带的索引还不如只建立一个索引
删除索引：
发现时间又变成了0.57s左右，

建立一个的联合索引：
ALTER TABLE user
ADD INDEX index_last_login_area (last_login,area) ,
SELECT * FROM user WHERE area="美国ATT用户" AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area="泰国" AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area="台湾省台湾大宽频" AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area="美国弗吉尼亚州" AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area="德国奔驰汽车" AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area="台湾省中华电信" AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area="韩国" AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area="拉美地区" AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area="美国纽约(Prudential)" AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area="印度尼西亚" AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
额，第二条数据这是怎么了，我测试了5次都在这附近晃悠哈！
这尼玛，找对索引啦！就该这么建立，查询不出来需要的时间啦！估计就是我们需要的索引啦！！！！

删除索引：
发现时间又变成了0.57s左右，

建立一个的联合索引：
ALTER TABLE user
ADD INDEX index_sex_last_login_area (sex,last_login,area)
SELECT * FROM user WHERE area="美国ATT用户" AND sex=0 ORDER BY last_login DESC limit 30; 0.18s
SELECT * FROM user WHERE area="泰国" AND sex=0 ORDER BY last_login DESC limit 30; 0.17s
SELECT * FROM user WHERE area="台湾省台湾大宽频" AND sex=0 ORDER BY last_login DESC limit 30; 0.81s
SELECT * FROM user WHERE area="美国弗吉尼亚州" AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area="德国奔驰汽车" AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area="台湾省中华电信" AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
SELECT * FROM user WHERE area="韩国" AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area="拉美地区" AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area="美国纽约(Prudential)" AND sex=0 ORDER BY last_login DESC limit 30; 0.03s
SELECT * FROM user WHERE area="印度尼西亚" AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
sex怎么总是你在拖后腿啊！把你调整到索引的最后一个吧！
删除索引：
发现时间又变成了0.57s左右，

建立一个的联合索引：
ALTER TABLE user
ADD INDEX index_last_login_area_sex (area,last_login,sex)
SELECT * FROM user WHERE area="美国ATT用户" AND sex=0 ORDER BY last_login DESC limit 30; 0.03s
SELECT * FROM user WHERE area="泰国" AND sex=0 ORDER BY last_login DESC limit 30; 0.07s
SELECT * FROM user WHERE area="台湾省台湾大宽频" AND sex=0 ORDER BY last_login DESC limit 30; 0.50s
SELECT * FROM user WHERE area="美国弗吉尼亚州" AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area="德国奔驰汽车" AND sex=0 ORDER BY last_login DESC limit 30; 0.05s
SELECT * FROM user WHERE area="台湾省中华电信" AND sex=0 ORDER BY last_login DESC limit 30; 0.06s
SELECT * FROM user WHERE area="韩国" AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area="拉美地区" AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area="美国纽约(Prudential)" AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
SELECT * FROM user WHERE area="印度尼西亚" AND sex=0 ORDER BY last_login DESC limit 30; 0.06s

综上所述：1.建立索引不一定能够加快查询效率如sex这种给重复次数特别多的列增加索引如sex这种会降低查询效率，具体的原因有待查找
2.给重复次数比较少的列增加u讴吟还是能够大幅度提高效率
3.给where和orderby之后的字段添加索引才会加快查询效率
4.为每一个列多带带建立索引，不能将索引的效率最大化，应该使用索引合并策略，即根据查询条件，建立联合索引
5.联合索引的顺序问题：将选择性高的索引放到前面
6.根据资料建立索引意味着索引按照最左列进行排序，然后事第二列，以此类推。如（last_login ,area）就会按照last_login进行排序，然后才是area
7.根据这次的这个查询条件来说最好的索引是：ALTER TABLE userADD INDEX index_last_login_area (last_login,area)。

专线服务 超融合服务器 设计原则 网络设计原则 MySQL索引 mysql建立索引

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/8702.html

相关文章

  • MySQL深入浅出（二）：索引的设计原则、SQL优化、MySQL日志、备份与恢复

    摘要：不用访问表或者索引，直接就能得到结果，如表示查询时可能使用的索引。表示为了得到结果，使用了临时表，这通常是出现在多表联合查询，结果排序的场合。此日志对于灾难时的数据恢复起着极其重要的作用。 上一篇：MySQL深入浅出（一）：SQL基础、数据类型、运算符 一、 索引的设计原则 查看字段散列度/离散度：select count(distinct col_name),... from ta...

    LeexMuller 评论0 收藏0

  • MySQL 数据库设计总结

    摘要：一数据库索引规则业务需要的相关索引是根据实际的设计所构造语句的条件来确定的，业务不需要的不要建索引，不允许在联合索引或主键中存在多于的字段。 本文由云+社区发表作者：漆洪凯 规则1：一般情况可以选择MyISAM存储引擎，如果需要事务支持必须使用InnoDB存储引擎。 注意：MyISAM存储引擎 B-tree索引有一个很大的限制：参与一个索引的所有字段的长度之和不能超过1000字节。另...

    YanceyOfficial 评论0 收藏0

  • MySQL 数据库设计总结

    摘要：一数据库索引规则业务需要的相关索引是根据实际的设计所构造语句的条件来确定的，业务不需要的不要建索引，不允许在联合索引或主键中存在多于的字段。 本文由云+社区发表作者：漆洪凯 规则1：一般情况可以选择MyISAM存储引擎，如果需要事务支持必须使用InnoDB存储引擎。 注意：MyISAM存储引擎 B-tree索引有一个很大的限制：参与一个索引的所有字段的长度之和不能超过1000字节。另...

    mdluo 评论0 收藏0

  • PHPer面试指南-MySQL 篇

    摘要：常见索引类型有主键唯一索引复合索引全文索引。什么是死锁如何有效降低死锁死锁死锁一般是事务相互等待对方资源，最后形成环路，而无法继续运行。 本书的 GitHub 地址：https://github.com/todayqq/PH... 什么是索引，作用是什么？常见索引类型有那些？Mysql 建立索引的原则？ 索引是一种特殊的文件,它们包含着对数据表里所有记录的引用指针，相当于书本的目录...

    ThreeWords 评论0 收藏0

  • 你应该知道的数据库数据类型及其设计原则

    摘要：整数类型整数类型有，分别使用位存储空间。实数类型实数类型有，分别占用，字节。简单数据类型的操作需要更少的周期。 1. 整数类型 整数类型有：tinyint、smallint、mediumint、int、bigint，分别使用 8、16、24、32、64 位存储空间。它们可以存储的值范围从 -2 的 (n-1) 次方到 2 的 (n-1) 次方 -1，n 是存储空间的位数。 整数有可选的...

    MudOnTire 评论0 收藏0

  • MySQL的优化细节

    摘要：减少数据冗余，避免维护异常，节约存储空间。第二范式数据库的表中不存在非关键字段对任意候选关键字段的部分函数依赖。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关主要针对联合主键而言。 数据库设计 目的 结合DBMS(数据库管理系统)实现有效存储、高效访问。减少数据冗余，避免维护异常，节约存储空间。 大概的步骤 需求分析->逻辑设计->物理设计(考虑数据库系统...

    imccl 评论0 收藏0

发表评论

登陆后可评论

0条评论

Ocean

男|高级讲师
我要私信

TA的文章

阅读更多

云社区相关服务

提问 提问 学习 学习 认证 认证 产品 产品 技术服务 技术服务 售前咨询 售前咨询
最新活动
阅读需要支付1元查看
<