MySQL索引
分类:MYSQL 热度:

MySQL索引
========================================================
索引介绍
创建索引
创建表时创建索引
CREATE在已存在的表上创建索引
ALTER TABLE在已存在的表上创建索引
查看并测试索引
删除索引
 
一、索引简介
    索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。不同的数据引擎,它们的索引不尽相同。
    索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
    索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。
    关于快速定义的数据结构:比如二插数的算法,即找到中间值,然后把值分化在树的不同节点上。比如字典算法。它们都是优化查询的技术,这些技术被独立出来。
 
二、索引的分类
    普通索引 (key或index允许为空,允许重复)
    唯一索引 (unique 允许为空,但不允许重复)
    主键索引 (primary 不允许为空,将空转换成0,不允许重复)
    全文索引
    单列索引
    多列索引
    空间索引
 
三、准备实验环境
1. 准备表
    mysql> create table school_2.t2( id int, name varchar(20) );
    mysql> desc t2;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(20) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
 
2. 创建存储过程,实现批量插入记录
    mysql> delimiter $$
    mysql> create procedure autoinsert1()
        -> BEGIN
        -> declare i int default 1;
        -> while(i<20000)do
        -> insert into school.t2 values(i,'ccc');
        -> set i=i+1;
        -> end while;
        -> END$$
    mysql> delimiter ;
 
    mysql> show procedure status\G
    *************************** 1. row ***************************
                      Db: school
                    Name: autoinsert1
                    Type: PROCEDURE
                 Definer: root@localhost
                Modified: 2014-04-16 09:55:00
                 Created: 2014-04-16 09:55:00
           Security_type: DEFINER
                 Comment: 
    character_set_client: utf8
    collation_connection: utf8_general_ci
      Database Collation: utf8_general_ci
    1 row in set (0.00 sec)
 
    mysql> show create procedure autoinster2\G
    *************************** 1. row ***************************
               Procedure: autoinsert1
                sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
        Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `autoinster2`()
    BEGIN
    declare i int default 1;
    while(i<30000)do
    insert into school_2.t1 values(i,'ccc');
    set i=i+1;
    end while;
    END
    character_set_client: utf8
    collation_connection: utf8_general_ci
      Database Collation: utf8_general_ci
    1 row in set (0.00 sec)
 
3. 调用存储过程
    mysql> call autoinsert1();
 
批量插入大量数据,也可以执行以下shell语句:
    for ((i=1;i<=100000;i++));do mysql -p123456 test -e "insert into t1 values($i,floor($i+rand()*$i),md5($i))" ;done
 
四、创建索引
===创建表时
语法:
CREATE TABLE 表名 (
字段名1  数据类型 [完整性约束条件…],
字段名2  数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
[索引名]  (字段名[(长度)]  [ASC |DESC]) 
);
 
创建普通索引示例:索引可以重复
CREATE TABLE department (
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
INDEX index_dept_name (dept_name)
);
 
创建唯一索引示例:索引不可以重复
CREATE TABLE department1 (
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
UNIQUE INDEX index_dept_name (dept_name)
);
 
创建全文索引示例:大型的文章,比如日志,适合全文索引
CREATE TABLE department (
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
log text,
FULLTEXT INDEX index_log (log)
);
 
创建多列索引示例:
CREATE TABLE department (
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
INDEX index_dept_name_comment (dept_name, comment)
);
 
===CREATE在已存在的表上创建索引
语法:
CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
  ON 表名 (字段名[(长度)]  [ASC |DESC]) ;
 
创建普通索引示例:
CREATE INDEX index_dept_name ON department  (dept_name);
 
创建唯一索引示例:
CREATE UNIQUE INDEX index_dept_name ON department (dept_name);
 
创建全文索引示例:
CREATE FULLTEXT INDEX index_dept_name ON department (dept_name);
 
创建多列索引示例:
CREATE INDEX index_dept_name_ comment ON department (dept_name, comment);
 
===ALTER TABLE在已存在的表上创建索引
语法:
ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
   索引名 (字段名[(长度)]  [ASC |DESC]) ;
 
创建普通索引示例:
ALTER TABLE department ADD INDEX index_dept_name (dept_name);
 
创建唯一索引示例:
ALTER TABLE department ADD UNIQUE INDEX index_dept_name (dept_name);
 
创建全文索引示例:
ALTER TABLE department ADD FULLTEXT INDEX index_dept_name (dept_name);
 
创建多列索引示例:
ALTER TABLE department ADD INDEX index_dept_name_comment (dept_name, comment);
 
四、管理索引
查看索引
   SHOW CRETAE TABLE 表名\G
   show index from table_name;
   show keys from table_name;
 
测试示例
   EXPLAIN SELECT * FROM department WHERE dept_name=‘hr’;
 
删除索引
  DROP INDEX 索引名 ON 表名;
 
五、Sphinx
    Sphinx是一个基于SQL的全文检索引擎,可以结合MySQL,PostgreSQL做全文搜索,它可以提供比数据库本身更专业的搜索功能,使得应用程序更容易实现专业化的全文检索。Sphinx特别为一些脚本语言设计搜索API接口,如PHP,Python,Perl,Ruby等,同时为MySQL也设计了一个存储引擎插件。
    Sphinx 单一索引最大可包含1亿条记录,在1千万条记录情况下的查询速度为0.x秒(毫秒级)。Sphinx创建索引的速度为:创建100万条记录的索引只需 3~4分钟,创建1000万条记录的索引可以在50分钟内完成,而只包含最新10万条记录的增量索引,重建一次只需几十秒。
    
 
Coreseek
    
    Coreseek 是一个可供企业使用的、基于Sphinx(可独立于Sphinx原始版本运行)的中文全文检索引擎,按照GPLv2协议发行。商业使用(例如, 嵌入到其他程序中)需要获得商业授权。
    Coreseek是一个支持中文的全文搜索引擎,意图为其他应用提供高速、低空间占用、高相关度结果的中文全文搜索能力。CoreSeek可以非常容易的与SQL数据库和脚本语言集成。
    Sphinx/Coreseek配置
    
索引测试实验:
    mysql> create table school.t2(id int,name varchar(30));
    Query OK, 0 rows affected (1.33 sec)
 
    mysql> desc school.t2;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(30) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
 
    mysql> delimiter $$
    mysql> create procedure autoinsert1()
        -> BEGIN
        -> declare i int default 1;
        -> while(i<100000)do
        -> insert into school.t2 values(i,'ccc');
        -> set i=i+1;
        -> end while;
        -> END$$
    ERROR 1046 (3D000): No database selected
    mysql>
    mysql> use school
    Database changed
    mysql> delimiter ;
 
    mysql> call autoinsert1();
 
    mysql> explain select * from school.t2 where id=20000;
    +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
    +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
    |  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 44848 | Using where |
    +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
    1 row in set (0.00 sec)
 
    mysql> create index index_id on school.t2(id);
    Query OK, 0 rows affected (0.91 sec)
    Records: 0  Duplicates: 0  Warnings作用: 0
 
    mysql> explain select * from school.t2 where id=20000;
    +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra       |
    +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
    |  1 | SIMPLE      | t2    | ref  | index_id      | index_id | 5       | const |    1 | Using where |
    +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
    1 row in set (0.00 sec)
 
    SHOW CREATE TABLE 表名\G
    EXPLAIN: 命令的作用是查看查询优化器如何决定执行查询
 
花费时间比较:
创建索引前
    mysql>  select * from school.t2 where id=20000;
    +-------+------+
    | id    | name |
    +-------+------+
    | 20000 | ccc  |
    +-------+------+
    1 row in set (0.03 sec)
 
创建索引后
    mysql> create index index_id on school.t2(id);
    Query OK, 0 rows affected (0.39 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql>  select * from school.t2 where id=20000;
    +-------+------+
    | id    | name |
    +-------+------+
    | 20000 | ccc  |
    +-------+------+
    1 row in set (0.00 sec)
========================================================
六、索引的设计原则:
    索引并非越多越好
    数据量不多时,不建议使用索引
    列中的值变化不多时,不建议使用索引
    经常进行分组与排序的列最好使用索引
    唯一性约束的列建立索引时使用唯一索引
========================================================
上一篇:MySQL单表查询 SELECT 下一篇:没有了
猜你喜欢
各种观点
热门排行
精彩图文