文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >Mysql索引整理utf8_unicode_ci

问Mysql索引整理utf8_unicode_ci
EN

Stack Overflow用户
提问于 2015-07-02 03:31:40
回答 1查看 2.8K关注 0票数 1

我从freeradius更新了数据库，现在有一个sql语句需要30秒以上的时间来执行，而以前只需要0.5秒来执行。

以下是旧的定义表：

代码语言：javascript
运行
复制
    CREATE TABLE `radacct` (
   `radacctid` bigint(21) NOT NULL AUTO_INCREMENT,
   `acctsessionid` varchar(64) NOT NULL DEFAULT '',
   `acctuniqueid` varchar(32) NOT NULL DEFAULT '',
   `username` varchar(64) NOT NULL DEFAULT '',
   `groupname` varchar(64) NOT NULL DEFAULT '',
   `realm` varchar(64) DEFAULT '',
   `nasipaddress` varchar(15) NOT NULL DEFAULT '',
   `nasportid` varchar(15) DEFAULT NULL,
   `nasporttype` varchar(32) DEFAULT NULL,
   `acctstarttime` datetime DEFAULT NULL,
   `acctstoptime` datetime DEFAULT NULL,
   `acctsessiontime` int(12) DEFAULT NULL,
   `acctauthentic` varchar(32) DEFAULT NULL,
   `connectinfo_start` varchar(50) DEFAULT NULL,
   `connectinfo_stop` varchar(50) DEFAULT NULL,
   `acctinputoctets` bigint(20) DEFAULT NULL,
   `acctoutputoctets` bigint(20) DEFAULT NULL,
   `calledstationid` varchar(50) NOT NULL DEFAULT '',
   `callingstationid` varchar(50) NOT NULL DEFAULT '',
   `acctterminatecause` varchar(32) NOT NULL DEFAULT '',
   `servicetype` varchar(32) DEFAULT NULL,
   `framedprotocol` varchar(32) DEFAULT NULL,
   `framedipaddress` varchar(15) NOT NULL DEFAULT '',
   `acctstartdelay` int(12) DEFAULT NULL,
   `acctstopdelay` int(12) DEFAULT NULL,
   `xascendsessionsvrkey` varchar(10) DEFAULT NULL,
   PRIMARY KEY (`radacctid`),
   KEY `username` (`username`),
   KEY `framedipaddress` (`framedipaddress`),
   KEY `acctsessionid` (`acctsessionid`),
   KEY `acctsessiontime` (`acctsessiontime`),
   KEY `acctuniqueid` (`acctuniqueid`),
   KEY `acctstarttime` (`acctstarttime`),
   KEY `acctstoptime` (`acctstoptime`),
   KEY `nasipaddress` (`nasipaddress`)
 ) ENGINE=InnoDB AUTO_INCREMENT=3514770 DEFAULT CHARSET=latin1;

 CREATE TABLE `userinfo` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `username` varchar(128) DEFAULT NULL,
   `firstname` varchar(200) DEFAULT NULL,
   `lastname` varchar(200) DEFAULT NULL,
   `email` varchar(200) DEFAULT NULL,
   `department` varchar(200) DEFAULT NULL,
   `company` varchar(200) DEFAULT NULL,
   `workphone` varchar(200) DEFAULT NULL,
   `homephone` varchar(200) DEFAULT NULL,
   `mobilephone` varchar(200) DEFAULT NULL,
   `address` varchar(200) DEFAULT NULL,
   `city` varchar(200) DEFAULT NULL,
   `state` varchar(200) DEFAULT NULL,
   `country` varchar(100) DEFAULT NULL,
   `zip` varchar(200) DEFAULT NULL,
   `notes` varchar(200) DEFAULT NULL,
   `changeuserinfo` varchar(128) DEFAULT NULL,
   `portalloginpassword` varchar(128) DEFAULT '',
   `enableportallogin` int(32) DEFAULT '0',
   `creationdate` datetime DEFAULT '0000-00-00 00:00:00',
   `creationby` varchar(128) DEFAULT NULL,
   `updatedate` datetime DEFAULT '0000-00-00 00:00:00',
   `updateby` varchar(128) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `username` (`username`),
   KEY `company` (`company`)
 ) ENGINE=MyISAM AUTO_INCREMENT=188493 DEFAULT CHARSET=latin1;

新的定义表如下：

代码语言：javascript
运行
复制
 CREATE TABLE `radacct` (
   `radacctid` bigint(21) NOT NULL AUTO_INCREMENT,
   `acctsessionid` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `acctuniqueid` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `username` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `groupname` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `realm` varchar(64) COLLATE utf8_unicode_ci DEFAULT '',
   `nasipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `nasportid` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
   `nasporttype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `acctstarttime` datetime DEFAULT NULL,
   `acctstoptime` datetime DEFAULT NULL,
   `acctsessiontime` int(12) DEFAULT NULL,
   `acctauthentic` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `connectinfo_start` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   `connectinfo_stop` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   `acctinputoctets` bigint(20) DEFAULT NULL,
   `acctoutputoctets` bigint(20) DEFAULT NULL,
   `calledstationid` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `callingstationid` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `acctterminatecause` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `servicetype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `framedprotocol` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `framedipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `acctstartdelay` int(12) DEFAULT NULL,
   `acctstopdelay` int(12) DEFAULT NULL,
   `xascendsessionsvrkey` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`radacctid`),
   KEY `username` (`username`),
   KEY `framedipaddress` (`framedipaddress`),
   KEY `acctsessionid` (`acctsessionid`),
   KEY `acctsessiontime` (`acctsessiontime`),
   KEY `acctuniqueid` (`acctuniqueid`),
   KEY `acctstarttime` (`acctstarttime`),
   KEY `acctstoptime` (`acctstoptime`),
   KEY `nasipaddress` (`nasipaddress`)
 ) ENGINE=MyISAM AUTO_INCREMENT=3519495 DEFAULT CHARSET=utf8    COLLATE=utf8_unicode_ci;

 CREATE TABLE `userinfo` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `username` varchar(128) DEFAULT NULL,
   `firstname` varchar(200) DEFAULT NULL,
   `lastname` varchar(200) DEFAULT NULL,
   `email` varchar(200) DEFAULT NULL,
   `department` varchar(200) DEFAULT NULL,
   `company` varchar(200) DEFAULT NULL,
   `workphone` varchar(200) DEFAULT NULL,
   `homephone` varchar(200) DEFAULT NULL,
   `mobilephone` varchar(200) DEFAULT NULL,
   `address` varchar(200) DEFAULT NULL,
   `city` varchar(200) DEFAULT NULL,
   `state` varchar(200) DEFAULT NULL,
   `country` varchar(100) DEFAULT NULL,
   `zip` varchar(200) DEFAULT NULL,
   `notes` varchar(200) DEFAULT NULL,
   `changeuserinfo` varchar(128) DEFAULT NULL,
   `portalloginpassword` varchar(128) DEFAULT '',
   `enableportallogin` int(32) DEFAULT '0',
   `creationdate` datetime DEFAULT '0000-00-00 00:00:00',
   `creationby` varchar(128) DEFAULT NULL,
   `updatedate` datetime DEFAULT '0000-00-00 00:00:00',
   `updateby` varchar(128) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `username` (`username`),
   KEY `company` (`company`)
 ) ENGINE=MyISAM AUTO_INCREMENT=188894 DEFAULT CHARSET=latin1;

请注意，旧表具有两个字符集，而新表根据定义具有不同的字符集和排序规则: CHARSET=utf8 COLLATE=utf8_unicode_ci和CHARSET=latin1。这应该是不同之处。

下面是我运行的句子：

代码语言：javascript
运行
复制
 select TIMESTAMPDIFF(SECOND,max(acctstarttime),now()) as segons,(department) as idClient,(userinfo.username)
        from
        (select radacct.username as id
            from radacct,userinfo
            where userinfo.company=98
            and radacct.username = userinfo.username 
            group by userinfo.username
            order by max(radacct.radacctid) desc limit 0,6) as tbl,radacct,userinfo
        where radacct.username=tbl.id and radacct.username = userinfo.username 
        group by radacct.username
        order by max(radacct.radacctid) desc;

这是对旧表的描述-快速查询(0.4s)：

代码语言：javascript
运行
复制
   id * select_type *   table   type    possible_keys   key key_len ref rows    Extra *
   1    PRIMARY <derived2>  ALL {null}  {null}  {null}  {null}  6   Using temporary; Using filesort
   1    PRIMARY radacct ref username    username    66  tbl.id  5   
   1    PRIMARY userinfo    ref username    username    131 radius.radacct.username 1   Using where
   2    DERIVED radacct index   username    username    66  {null}  28768   Using index; Using temporary; Using filesort
   2    DERIVED userinfo    ref username,company    username    131 radius.radacct.username 1   Using where

这是对新表的描述-慢查询(30s)：

代码语言：javascript
运行
复制
   id * select_type *   table   type    possible_keys   key key_len ref rows    Extra *
   1    PRIMARY <derived2>  ALL {null}  {null}  {null}  {null}  6   Using temporary; Using filesort
   1    PRIMARY radacct ref username    username    194 tbl.id  11  
   1    PRIMARY userinfo    ALL {null}  {null}  {null}  {null}  188911  Using where; Using join buffer
   2    DERIVED userinfo    ALL company {null}  {null}  {null}  188911  Using where; Using temporary; Using filesort
   2    DERIVED radacct ref username    username    194 func    11  Using where

谢谢。

编辑：

我在userinfo上更改了字符集：

代码语言：javascript
运行
复制
 alter table radius.userinfo convert to character set utf8 collate utf8_unicode_ci;

现在，新的-慢查询在3秒内运行，但仍然没有旧数据库快。

此外，我将VARCHAR长度更改为两个查询的长度相同，并且仍然不像对旧数据库所做的那样使用索引。

代码语言：javascript
运行
复制
  ALTER TABLE radius.userinfo CHANGE username username VARCHAR(64);

我只是在运行运行速度较慢的子查询：

代码语言：javascript
运行
复制
 select radacct.username as id
        from radacct,userinfo
        where userinfo.company=98
        and radacct.username = userinfo.username 
        group by userinfo.username
        order by max(radacct.radacctid) desc limit 0,6

这是DESCRIBE OLD - Fast：

代码语言：javascript
运行
复制
 id select_type table   type    possible_keys   key key_len ref rows    Extra
 1  SIMPLE  radacct index   username    username    66      37545   Using index; Using temporary; Using filesort
 1  SIMPLE  userinfo    ref username,company    username    131 radius.radacct.username 1   Using where

这是DESCRIBE NEW - Slow：

代码语言：javascript
运行
复制
 id select_type table   type    possible_keys   key key_len ref rows    Extra
 1  SIMPLE  radacct ALL username                56879   Using temporary; Using filesort
 1  SIMPLE  userinfo    ref company,username    username    195 radius.radacct.username 1   Using where

为什么没有得到radacct.username索引？

编辑2:使用新的排序规则-字符集添加新的定义。

代码语言：javascript
运行
复制
 CREATE TABLE `radacct` (
   `radacctid` bigint(21) NOT NULL AUTO_INCREMENT,
   `acctsessionid` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `acctuniqueid` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `username` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `groupname` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `realm` varchar(64) COLLATE utf8_unicode_ci DEFAULT '',
   `nasipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `nasportid` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
   `nasporttype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `acctstarttime` datetime DEFAULT NULL,
   `acctstoptime` datetime DEFAULT NULL,
   `acctsessiontime` int(12) DEFAULT NULL,
   `acctauthentic` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `connectinfo_start` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   `connectinfo_stop` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   `acctinputoctets` bigint(20) DEFAULT NULL,
   `acctoutputoctets` bigint(20) DEFAULT NULL,
   `calledstationid` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `callingstationid` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `acctterminatecause` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `servicetype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `framedprotocol` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `framedipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `acctstartdelay` int(12) DEFAULT NULL,
   `acctstopdelay` int(12) DEFAULT NULL,
   `xascendsessionsvrkey` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`radacctid`),
   KEY `framedipaddress` (`framedipaddress`),
   KEY `acctsessionid` (`acctsessionid`),
   KEY `acctsessiontime` (`acctsessiontime`),
   KEY `acctuniqueid` (`acctuniqueid`),
   KEY `acctstarttime` (`acctstarttime`),
   KEY `acctstoptime` (`acctstoptime`),
   KEY `nasipaddress` (`nasipaddress`),
   KEY `username` (`username`)
 ) ENGINE=MyISAM AUTO_INCREMENT=3607301 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 CREATE TABLE `userinfo` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `username` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
   `firstname` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `lastname` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `email` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `department` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `company` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `workphone` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `homephone` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `mobilephone` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `address` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `city` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `state` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `country` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
   `zip` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `notes` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `changeuserinfo` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
   `portalloginpassword` varchar(128) COLLATE utf8_unicode_ci DEFAULT '',
   `enableportallogin` int(32) DEFAULT '0',
   `creationdate` datetime DEFAULT '0000-00-00 00:00:00',
   `creationby` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
   `updatedate` datetime DEFAULT '0000-00-00 00:00:00',
   `updateby` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `company` (`company`),
   KEY `username` (`username`)
 ) ENGINE=MyISAM AUTO_INCREMENT=191546 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
mysql
indexing
collate
EN

回答 1

Stack Overflow用户

发布于 2015-07-02 07:50:40

我认为有3个问题：

代码语言：javascript
运行
复制
company = 98
`company` varchar(200) DEFAULT NULL,

如果是数字，则使用数字数据类型。

替换

代码语言：javascript
运行
复制
KEY `company` (`company`)

使用

代码语言：javascript
运行
复制
INDEX(company, username)

但也许真正的问题是：

代码语言：javascript
运行
复制
radacct.username = userinfo.username

一个是latin1，另一个是utf8。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/31170091

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档