推荐:MySQL性能优化的最佳21条经验今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的
创建2张用户表user、user2,表结构相同,但user表使用InnoDB存储引擎,而user2表则使用 MyISAM存储引擎。
-- Table "user" DDL
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`nickname` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
KEY `name` (`name`),
KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table "user2" DDL
CREATE TABLE `user2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`nickname` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
KEY `name` (`name`),
KEY `age` (`age`)
) ENGINE=MyISAM AUTO_INCREMENT=131610 DEFAULT CHARSET=utf8;
分别插入10W条测试数据到表user& user2。
<?php
$example = array(
'@qq.com',
'@sina.com.cn',
'@163.com',
'@126.com',
'@gmail.com',
'@yahoo.com',
'@live.com',
'@msn.com',
'@cisco.com',
'@microsoft.com',
'@ibm.com',
'@apple.com');
$con = mysql_connect("localhost", "root", "your_mysql_password");
mysql_select_db("index_test", $con);
//添加10W测试数据到表 user & user2
for($i=0; $i<100000; $i++)
{
$temp = md5(uniqid());
$name = substr($temp, 0, 16);
$email = substr($temp, 8, 12).$example[array_rand($example, 1)];
$age = rand(18, 99);
$nickname = substr($temp, 16, 16);
mysql_query("INSERT INTO user(name,email,age,nickname) VALUES('$name','$email',$age,'$nickname')");
mysql_query("INSERT INTO user2(name,email,age,nickname) VALUES('$name','$email',$age,'$nickname')");
}
mysql_close($con);
echo 'success';
?>
对索引的使用分析
Explain Select * from user where id>100 \G;
![](http://cdn-img.sm-tc.cn/?src=l4uLj8XQ0IiIiNGSncqK0ZyQktCKj5OQnpuM0J6Tk5aSmNDNz87N0M7P0M3J0MrGx8bOzMrOzc3IzcjH0ZWPmA%3D%3D&restype=3&from=derive&pi=&v=1)
图1
Explain Select * from user2 where id>100 \G;
![](http://cdn-img.sm-tc.cn/?src=l4uLj8XQ0IiIiNGSncqK0ZyQktCKj5OQnpuM0J6Tk5aSmNDNz87N0M7P0M3J0MzKxsjOzMrOzc3IzcjH0ZWPmA%3D%3D&restype=3&from=derive&pi=&v=1)
图2
User 表中的数据和 User2 表中的数据是一样的,索引结构也是一样的,只不过它们的存储引擎不同。在图1中,查询用到了PRIMARY主键索引,而查询优化器预估的结果大概在65954行左右(实际是131513);在图2中,查询却没有使用索引,而是全表扫描了,返回的预估结果在131608行(实际是131509)。
Explain Select * from user where id>100 and age>50 \G;
![](http://cdn-img.sm-tc.cn/?src=l4uLj8XQ0IiIiNGSncqK0ZyQktCKj5OQnpuM0J6Tk5aSmNDNz87N0M7P0M3J0MnIxsnOzMrOzc3IzcjH0ZWPmA%3D%3D&restype=3&from=derive&pi=&v=1)
图3
Explain Select * from user where id>100 and age=50 \G;
![](http://cdn-img.sm-tc.cn/?src=l4uLj8XQ0IiIiNGSncqK0ZyQktCKj5OQnpuM0J6Tk5aSmNDNz87N0M7P0M3J0M7NxsjOzMrOzc3IzcjH0ZWPmA%3D%3D&restype=3&from=derive&pi=&v=1)
图4
Explain Select * from user2 where id>100 and age>50 \G;
![](http://cdn-img.sm-tc.cn/?src=l4uLj8XQ0IiIiNGSncqK0ZyQktCKj5OQnpuM0J6Tk5aSmNDNz87N0M7P0M3J0MzLzM%2FOzMrOzc3IzcjG0ZWPmA%3D%3D&restype=3&from=derive&pi=&v=1)
图5
Explain Select * from user2 where id>100 and age=50 \G;
![](http://cdn-img.sm-tc.cn/?src=l4uLj8XQ0IiIiNGSncqK0ZyQktCKj5OQnpuM0J6Tk5aSmNDNz87N0M7P0M3J0MbJzMbOzMrOzc3IzcjG0ZWPmA%3D%3D&restype=3&from=derive&pi=&v=1)
图6
分享:MySql优化指南MySql优化的一般步骤: 1.通过show status 命令了解各种sql的执行效率 SHOW STATUS提供msyql服务器的状态信息 一般情况下,我们只需要了解以”Com”开头的指令 show session status like ‘Com%’:显示当前的连接的统计结果 show global status like ‘Com%’ :显示