Mysql Index COLLATE utf8_unicode_ci

Question

Mysql Index COLLATE utf8_unicode_ci

Я обновил базу данных от freeradius, и теперь есть SQL-предложение, выполнение которого занимает более 30 секунд, в то время как раньше - 0,5 секунды.

Это старые таблицы определения:

    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;

Это НОВЫЕ таблицы определений:

 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=latin1, в то время как в новых таблицах по определению используются разные charset и collate: CHARSET=utf8 COLLATE=utf8_unicode_ci и CHARSET=latin1. Это должно быть разница.

Теперь это предложение, которое я запускаю:

 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;

И это ОПИСАНИЕ для запроса OLD TABLES - FAST (0,4 с):

   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

И это ОПИСАНИЕ для запроса NEW TABLES - SLOW (30 с):

   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:

 alter table radius.userinfo convert to character set utf8 collate utf8_unicode_ci;

Теперь запрос NEW - Slow выполняется за 3 секунды, но все же не так быстро, как база данных OLD.

Кроме того, я изменяю длину VARCHAR, чтобы она была одинаковой в обоих запросах и все еще не идет по индексу, как это происходит с базой данных OLD.

  ALTER TABLE radius.userinfo CHANGE username username VARCHAR(64);

Я просто запускаю подзапрос, который идет медленно:

 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

Это ОПИСАТЬ СТАРЫЙ - быстро:

 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

Это ОПИСАНИЕ НОВОГО - медленно:

 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: Добавить новое определение с новым COLLATION-CHARSET.

 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;

1

mysql indexing collate

Источник

user4255780 01 июл '15 в 19:31

1 ответ

Другие вопросы по тегам mysql indexing collate

user1766831 01 июл '15 в 23:50 2015-07-01 23:50 · Answer 1 · 2015-07-01 23:50

Я вижу 3 вопроса:

company = 98
`company` varchar(200) DEFAULT NULL,

Если это число, используйте числовой тип данных.

замещать

KEY `company` (`company`)

с

INDEX(company, username)

Но, возможно, настоящая проблема заключается в следующем:

radacct.username = userinfo.username

один из которых латинский1, а другой - utf8.