Mysql索引优化

如何选择合适的列建立索引

1.在where从句,group by从句,order by从句,on从句中出现的列

2.索引字段越小越好

3.离散度大的列放到联合索引的前面

select * from payment where staff_id = 2 and customer_id = 584;

由于customer_id的离散度更大,所以应该使用index(customer_id,staff_id),而不是使用index(staff_id,ustomer_id)。

如何判断离散度:

Mysql> select count(distinct customer_id),count(distinct staff_id) from payment;
+-----------------------------+--------------------------+
| count(distinct customer_id) | count(distinct staff_id) |
+-----------------------------+--------------------------+
|                         599 |                        2 |
+-----------------------------+--------------------------+

可以看出customer_id的离散度更大

索引的维护及优化

正常情况下,我们认为建立索引可以优化我们的查询效率,但是会降低写入语句的效率。实际情况下,过多的索引不但会对写入语句有影响,还会影响查询语句的效率。这是因为数据库在使用索引查询的时候,首先要分析使用哪一个索引进行查询,索引越多,分析的时间就越多。所以我们需要维护我们的索引。

重复及冗余的索引

重复索引是指相同的列以相同的顺序建立的同类型的索引,如下表中primary key和id列上的索引就是重复索引:

create table test(
id int not null primary key,//主键
name varchar(10) not null,
unique(id)//唯一索引
)engine = innodb;

冗余索引是指多个索引的前缀列相同,或是在联合索引中包含了主键的索引,下面这个例子中key(name,id)就是一个冗余索引:

create table test(
id int not null primary key,//主键
name varchar(10) not null,
key(name,id)//索引
)engine = innodb;

查询重复及冗余索引

用sql的方式查询索引问题:

use information_schema;
select a.table_schema as '数据名',a.table_name as '表名',a.index_name as '索引1',b.index_name as '索引2',a.column_name as '重复列名' from statistics a join statistics b on a.table_schema = b.table_schema and a.table_name = b.table_name and a.seq_in_index = b.seq_in_index and a.column_name = b.column_name where a.seq_in_index = 1 and a.index_name <> b.index_name;

使用pt-duplicate-key-checker工具检查重复及冗余索引

1
pt-duplicate-key-checker -uxxx -pxxx -hxxx

业务变更导致不再使用的索引(删除)

通过慢查日志配合pt-index-usage工具来进行索引使用情况的分析

1
pt-index-usage -uxxx -pxxx /xxx/Mysql-slow.log

mysql 怎么建索引优化

10-31

如下语句rnrnrnSELECT pi.id, pi.User_Id, pi.Pay_Amount, pi.Product_Name, pi.Pay_Date, pi.Pay_Stat, pi.Pay_ChannelId, mui.User_Name, mui.User_Mobilenum FROM Pay_Info_201210 as pi LEFT JOIN mcp_user_info as mui ON mui.User_Id = pi.User_Id WHERE 1=1 and pi.Pay_Date >= '2012-10-31' and pi.Pay_Date <= '2012-11-01' order by Pay_Date DESC limit 0,10 rnrn怎么建索引优化,mcp_user_info 400W+,Pay_Info_201210 30W+rnrn原来Pay_Info有4000w+,现在拆开,按照月分隔,但是一个月也得1000W+,rn原来用4000W+跑着十条数据得4-50秒,求大侠给提点建议,本人新手,同时希望大侠能说一点建索引的原理,小弟在学习中rnrn一下是这两个表的建表语句rnrnCREATE TABLE `Pay_Info_201210` (rn `Id` varchar(100) NOT NULL,rn `User_Id` int(11) NOT NULL,rn `Pay_TypeId` int(3) NOT NULL COMMENT '支付类型',rn `Pay_Amount` int(11) NOT NULL COMMENT '支付金额(酷点,分)',rn `Product_Name` varchar(100) NOT NULL COMMENT '商品名称',rn `Product_Id` varchar(30) NOT NULL COMMENT '商品ID',rn `Product_Num` varchar(10) NOT NULL COMMENT '商品数量',rn `Use_PayPwd` int(2) NOT NULL COMMENT '是否使用支付密码0-未使用,1-使用',rn `Pay_Stat` int(2) NOT NULL DEFAULT '0' COMMENT '0-支付未成功,1-支付成功',rn `Pay_Date` datetime NOT NULL COMMENT '支付时间',rn `Pay_ChannelId` int(11) NOT NULL COMMENT '支付频道',rn `Product_TypeId` int(3) NOT NULL COMMENT '商品类型Id',rn PRIMARY KEY (`Id`),rn KEY `User_Id` (`User_Id`),rn KEY `user_time` (`User_Id`,`Pay_Date`),rn KEY `Pay_Date` (`Pay_Date`)rn) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='支付记录表' rnrnrnCREATE TABLE `mcp_user_info` (rn `User_Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',rn `User_Name` varchar(20) NOT NULL COMMENT '用户唯一标识名,4-14位字符,字母或数字(区分大小写)',rn `User_Pwd` varchar(50) NOT NULL COMMENT '用户密码 6-16位字符,字母或数字(区分大小写)',rn `User_Extid` int(11) NOT NULL DEFAULT '0' COMMENT '扩展信息id',rn `User_mstatus` int(2) NOT NULL DEFAULT '0' COMMENT '是否绑定手机,1是,0否',rn `User_Mobilenum` varchar(20) NOT NULL DEFAULT '0' COMMENT '用户手机号,绑定手机状态为1时必填',rn `User_Type` int(2) NOT NULL DEFAULT '0' COMMENT '注册类型:1 短信 2 手机号注册 3用户名 4第三方注册(其它渠道)',rn `User_Logindate` datetime DEFAULT '0000-00-00 00:00:00' COMMENT '最后一次登陆时间',rn `User_Mdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',rn `User_Regdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',rn `User_Enable` int(2) NOT NULL DEFAULT '0' COMMENT '是否有效:1有效,0无效',rn `User_ThirdId` int(11) NOT NULL DEFAULT '0' COMMENT '第三方代注册用户id',rn `Fr` varchar(20) NOT NULL DEFAULT '0',rn PRIMARY KEY (`User_Id`),rn UNIQUE KEY `User_Name` (`User_Name`),rn KEY `User_Mobilenum` (`User_Mobilenum`),rn KEY `User_ThirdId` (`User_ThirdId`),rn KEY `User_Regdate` (`User_Regdate`)rn) ENGINE=MyISAM AUTO_INCREMENT=20949520 DEFAULT CHARSET=utf8 COMMENT='用户注册信息表'rnrn

请教关于MySQL索引优化的问题

04-09

[size=14px]表oso_user_ratings的结构如下rnCREATE TABLE `oso_user_ratings` (rn `user_id` varchar(32) NOT NULL,rn `item_id` int(11) NOT NULL,rn `rating` decimal(14,4) NOT NULL DEFAULT '0.0000',rn KEY `item_id` (`item_id`),rn KEY `user_id` (`user_id`,`item_id`)rn) ENGINE=MyISAM DEFAULT CHARSET=utf8rnrn在应用中经常有这样的查询:rnfor ($i = 1; $i < 100000; $i++) rn SELECT a.item_id AS item_id1,b.item_id AS item_id2,COUNT(*) AS times, SUM(a.rating-b.rating) AS rating FROM oso_user_ratings a,oso_user_ratings b WHERE rna.item_id = '$i' AND b.item_id != a.item_id AND a.user_id=b.user_id GROUP BY a.item_id,b.item_id;rnrnrnEXPLAIN SELECT a.item_id AS item_id1,b.item_id AS item_id2,COUNT(*) AS times, SUM(a.rating-b.rating) AS rating FROM oso_user_ratings a,oso_user_ratings b WHERE a.item_id = '1' AND b.item_id != a.item_id AND a.user_id=b.user_id GROUP BY a.item_id,b.item_id;rnexplain上述的sql语句输出如下:rnid select_type table type possible_keys key key_len ref rows Extrarn1 SIMPLE b ALL user_id \N \N \N 2589649 Using temporary; Using filesortrn1 SIMPLE a ref item_id,user_id user_id 102 music.b.user_id,const 10 Using wherernrn请问大家怎样对表oso_user_ratings建立合理的索引,才能使查询更加快(或者索引保持不变,改变sql语句的写法也可以)。目前表oso_user_ratings中有2百多万条记录,以后还会一直增加。rnrn我现在是每天晚上定时将sql语句中的写到缓存中(用的是Tokyo Tyrant+Tokyo Cabinet),但是每次执行完毕需要好几个小时(往tt中保存很快,问题主要在MySQLP这儿),对服务器压力很大,尽管是在晚上执行,但是压力仍然还是不小的,而且晚上也会有用户在使用,肯定还有优化余地的。rnrn希望大家帮帮我,谢谢了![/size]

mysql SQL的索引优化使用.

03-02

以下SQL查询在本地需要大概2秒时间,服务器上时间更长.rnexplain select tz.id,tz.uid,tz.czlx,tz.qihao as tzqh,tz.addtime,tz.wanfa,tz.beishu,tz.ms,tz.money,tz.zt,qh.czlx,qh.qihao,qh.endtime from tz as tz inner join qihaoinfo as qh on tz.czlx = qh.czlx and tz.qihao = qh.qihao where tz.uid='796' and tz.zt =0 and qh.endtime > FROM_UNIXTIME( UNIX_TIMESTAMP(),'%Y-%m-%d %H:%i:%s');rnrn结束如下:rn+----+-------------+-------+-------+---------------+---------+---------+-------+-------+---------------------------------------------+rn| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |rn+----+-------------+-------+-------+---------------+---------+---------+-------+-------+---------------------------------------------+rn| 1 | SIMPLE | tz | ref | index_1 | index_1 | 4 | const | 16 | Using where |rn| 1 | SIMPLE | qh | index | NULL | qihao | 435 | NULL | 94296 | Using where; Using index; Using join buffer |rn+----+-------------+-------+-------+---------------+---------+---------+-------+-------+---------------------------------------------+rn2 rows in setrnrnqh表的字段:rnCREATE TABLE `NewTable` (rn`id` int(11) NOT NULL AUTO_INCREMENT ,rn`qihao` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,rn`ctype` smallint(4) NOT NULL ,rn`czlx` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,rn`kjhm` char(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,rn`starttime` datetime NULL DEFAULT NULL ,rn`endtime` datetime NOT NULL ,rn`addtime` date NOT NULL ,rn`stoptime` int(4) NULL DEFAULT NULL ,rn`zt` tinyint(4) NULL DEFAULT 0 ,rnPRIMARY KEY (`id`),rnINDEX `qihao` (`id`, `qihao`, `czlx`, `endtime`, `zt`) USING BTREE rn)rnENGINE=InnoDBrnDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_cirnAUTO_INCREMENT=94251rnROW_FORMAT=COMPACTrn;rnrn

没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试