MySQL索引(一)

MySQL索引(一)

分类： 数据库相关 2012-02-08 18:47 24人阅读 评论 (0) 收藏 举报

             数据库开发中索引的使用占了很重要的位置，好的索引会使数据库的读写效率加倍，烂的索引则会拖累整个系统甚至引发灾难。

             索引分三类：

             index ----普通的索引,数据可以重复

             unique ----唯一索引,唯一索引,要求所有记录都唯一

             primary key ----主键索引,也就是在唯一索引的基础上相应的列必须为主键

             一般的创建索引的语句如下：

 

  
ALTER
 
TABLE
 tb_name 
ADD
 
INDEX
 index_name (column_list)

ALTER
 
TABLE
 tb_name 
ADD
 
UNIQUE
 index_name (column_list)

ALTER
 
TABLE
 tb_name 
ADD
 
PRIMARY
 
KEY
 index_name (column_list)

             MySQL 中会对 <，<=，=，>，>=，BETWEEN，IN 以及不以% _ 开头的like 语句使用索引。

 

 

             为证实like处理方法以下为一个简单的测试：

 

create
 
table
 tb_test(fval 
varchar
(50));

            先不添加索引，随机插入100W 条记录，并测试like 的查询速度。

 

CREATE
 DEFINER=`root`@`localhost` 
PROCEDURE
 `P_teset`()
BEGIN
        DECLARE
 v_val 
VARCHAR
(20);
    DECLARE
 v_str 
VARCHAR
(20);
        DECLARE
 v_i 
INT
 ;
        DECLARE
 v_j 
INT
;

    SET
 v_str =
'abcdefghijklmnopqrstuvwxyz'
;
        SET
 v_i=0;
        SET
 v_j=0;

        WHILE v_i<1000000 DO
           SET
 v_val =
''
;

           WHILE v_j< 13 DO
              SET
 v_val= CONCAT(v_val,
SUBSTRING
(v_str,1,FLOOR(1+RAND()*26)));
              SET
 v_j=v_j+1;
           END
 WHILE;
           SET
 v_j=0;
           INSERT
 
INTO
 tb_test(fval)
           VALUES
(v_val);

           SET
 v_i =v_i+1;
        END
 WHILE;
    END
$

DELIMITER ;

         使用前后% 测试like 查询速度：

SELECT
 *
FROM
 tb_test
WHERE
 fval 
LIKE
 
'%ab'
;

70231条 用时0.531s

SELECT
 *
FROM
 tb_test
WHERE
 fval 
LIKE
 
'ab%'

961406条用时1.422s

 

下面加入索引：

ALTER
 
TABLE
 tb_test 
ADD
 
INDEX
  my_index(fval);

再次测试like 的查询速度：

SELECT
 *
FROM
 tb_test
WHERE
 fval 
LIKE
 
'%ab'
;

70231条 用时1.094s

SELECT
 *
FROM
 tb_test
WHERE
 fval 
LIKE
 
'ab%'

961406条用时1.485s

表为MyISAM格式。 可以看到加了索引后查询的效率并未得到提升。再使用 explain 关键字查看 like 'ab%' 的运行情况：

EXPLAIN
SELECT *
FROM tb_test IGNORE INDEX(my_index)
WHERE fval LIKE '%ab';

EXPLAIN SELECT *
FROM tb_test
WHERE fval LIKE '%ab';