MYSQL数据库MySQL的id关联和索引使用的实际优化案例

MYSQL应用
SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10;

MYSQL应用
CREATE TABLE `game_shares_buy_list` (
`tran_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`………..'
PRIMARY KEY (`tran_id`),
KEY `ind_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=3144200 DEFAULT CHARSET=utf8;

MYSQL应用
root@127.0.0.1 : sitevipdb 09:10:22> explain SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10;
+―-+――――-+―――――――-+――-+―――――+―――+―――+――+――+――――-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+―-+――――-+―――――――-+――-+―――――+―――+―――+――+――+――――-+
| 1 | SIMPLE | game_shares_buy_list | index | NULL | PRIMARY | 4 | NULL | 10 | Using where |
+―-+――――-+―――――――-+――-+―――――+―――+―――+――+――+――――-+
1 row in set (0.00 sec)

MYSQL应用
root@127.0.0.1 : sitevipdb 09:17:23> select price,count(*) as cnt from `game_shares_buy_list` group by price order by cnt desc limit 10;
+――-+――-+
| price | cnt |
+――-+――-+
| 1.75 | 39101 |
| 1.68 | 38477 |
| 1.71 | 34869 |
| 1.66 | 34849 |
| 1.72 | 34718 |
| 1.70 | 33996 |
| 1.76 | 32527 |
| 1.69 | 27189 |
| 1.61 | 25694 |
| 1.25 | 25450 |

MYSQL应用
root@127.0.0.1 : sitevipdb 09:09:01> select count(*) from `game_shares_buy_list` where price>'2′;
+―――-+
| count(*) |
+―――-+
| 4087 |
+―――-+
root@127.0.0.1 : sitevipdb 09:17:31> select count(*) from `game_shares_buy_list` ;
+―――-+
| count(*) |
+―――-+
| 1572100 |

MYSQL应用
root@127.0.0.1 : sitevipdb 09:24:53> alter table game_shares_buy_list add index ind_game_shares_buy_list_price(price);
Query OK, 0 rows affected (5.79 sec)

MYSQL应用
root@127.0.0.1 : sitevipdb 09:35:38> SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10;
.....
10 rows in set (7.06 sec)
root@127.0.0.1 : sitevipdb 09:36:00> SELECT * FROM `sitevipdb`.`game_shares_buy_list` force index(ind_game_shares_buy_list_price) WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10;
....
10 rows in set (1.01 sec)

MYSQL应用
root@127.0.0.1 : sitevipdb 09:44:45> select * from game_shares_buy_list t1,
-> ( SELECT tran_id FROM sitevipdb.game_shares_buy_list WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10) t2
-> where t1.tran_id=t2.tran_id;
10 rows in set (0.00 sec)

MYSQL应用
CREATE TABLE `game_session` (
`session_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ,
`session_expires` int(10) unsigned NOT NULL DEFAULT '0′,
`client_ip` varchar(16) DEFAULT NULL,
`session_data` text,
…………………….
PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MYSQL应用
CREATE TABLE `game_session` (
id int auto_increment,
`session_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ,
`session_expires` int(10) unsigned NOT NULL DEFAULT '0′,
`client_ip` varchar(16) DEFAULT NULL,
`session_data` varchar(200),
PRIMARY KEY (id),
key ind_session_id(session_id,session_data, session_expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;