mysql添加索引和sql分析

• mysql索引操作

查看索引
show indexes from students; #students为表名

mysql添加索引命令

创建索引
1.PRIMARY  KEY（主键索引）
mysql>ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column`  ) 
2.UNIQUE(唯一索引)
mysql>ALTER  TABLE  `table_name`  ADD  UNIQUE (`column` ) 
3.INDEX(普通索引)
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column`  )
4.FULLTEXT(全文索引)
mysql>ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` )
5.多列索引
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

删除索引
DROP INDEX indexname ON table_name;

描述：
PRIMARY, INDEX, UNIQUE 这3种是一类
PRIMARY 主键。 就是 唯一 且 不能为空。
INDEX 索引，普通的
UNIQUE 唯一索引。 不允许有重复。
FULLTEXT 是全文索引，用于在一篇文章中，检索文本信息的。

后记：
我今天用SQLyog工具创建索引，只能创建PRIMARY、UNIQUE、FULLTEXT，请教了DBA，她说可以不选择默认普通索引
例子:
      explain SELECT `id`,`last_update_time`,`projectid` FROM yubotuig3_com.v2_article WHERE projectid = 150931 and status = 1 ORDER BY last_update_time desc LIMIT 0,4;


      explain SELECT COUNT(*) AS tp_count FROM yubotuig3_com.v2_article WHERE projectid = 172942 and status = 1 ORDER BY last_update_time desc LIMIT 1;

      给where条件添加了联合索引，又给order by单独建了索引
            ALTER  TABLE  `v2_article`  ADD  INDEX index_projectid_status(`projectid`,`status`);
            ALTER  TABLE  `v2_article`  ADD  INDEX index_last_update_time(`last_update_time`);

• explain分析sql

explain select * from test1 where id=1; #会出现：id  selecttype  table  type possible_keys  key key_len  ref rows  extra各列。

其中
type=const表示通过索引一次就找到了；
key=primary的话，表示使用了主键；
type=all,表示为全表扫描；
key=null表示没用到索引。type=ref,因为这时认为是多个匹配行，在联合查询中，一般为REF。



1.使用恰当的索引，是sql的效率倍增，类似sum的函数还有min(),max(),这些都需要在字段上建索引
2.where 后面的函数不能包含表内容，要把函数放在 = 右连 如( update_time >= date_format(date_sub(curdate(), INTERVAL 90 DAY),
                                  '%Y-%m-%d 00:00:00')
   and  update_time <= date_format(date_sub(curdate(), INTERVAL 90 DAY),
                                  '%Y-%m-%d 23:59:59') )
3.虚表是没有索引的
SELECT
 count(1) 
FROM
 (
  SELECT
   a.*
  FROM
   (
    SELECT
     a.id,
     a.customer_id customerId,
     a.user_name userName,
     a.user_code userCode,
     a.type_code typeCode,
     a.type_name typeName,
     a.coupon_type couponType,
     a. STATUS,
     a.amount,
     a.leverage_ratio leverageRatio,
     a.effect_time effectTime,
     a.invest_id,
     a.use_time useTime,
     a.valid_days validDays,
     a.create_time createTime,
     a.update_time updateTime,
     a.invalid_time invalidTime,
     a.batch_no batchNo,
     c.id adviserId,
     c. NAME adviserName
    FROM
     pccb_coupon a
    LEFT JOIN customer_extend b ON a.customer_id = b.id
    LEFT JOIN pccb_adviser c ON b.agent_id = c.id
    WHERE
     1 = 1
   ) a
  WHERE
   1 = 1
  AND a.adviserName = '江左'   #a是虚表，没有索引
 )


原文地址:http://database.51cto.com/art/201108/284783.htm