索引

最新推荐文章于 2020-03-14 16:57:31 发布

weixin_34362790

最新推荐文章于 2020-03-14 16:57:31 发布

阅读量87

点赞数 1

文章标签：数据库 python

原文链接：https://my.oschina.net/jackguo/blog/138882

版权

2019独角兽企业重金招聘Python工程师标准>>>

索引：是针对数据所建立的目录。
作用：加快查询速度。
负面影响：降低增删改的速度。

案例：
设有新闻表有15列，其中有10列有索引，共500w行数据，如何快速导入？
首先：将空表的索引全部删除；
然后：集中导入数据；
最后：集中建立索引。

索引的创建原则：
1、不要过度索引；
2、在where条件最频繁的列上加；
3、尽量索引散列值，过于集中的值加索引，没有意义。

索引类型：
普通索引
唯一索引
主键索引
全文索引

一、基本概念：

1、普通索引(index)：仅仅是为了加快查询速度。

2、唯一索引(unique)：行上的值不能重复。

3、主键索引(primary key)：不能重复。

1>主键索引与唯一索引的区别。
主键必唯一，但是唯一索引不一定是主键；
一张表上，只能有一个主键，但是可以有一个或多个唯一索引。

4、全文索引(fulltext)：

二、查询、创建、修改、删除索引的语法：

2、建立索引：
alter table 表名 add index/unique/fulltext [索引名] (列名)

alter table 表名 add primary key (列名) ###不要加索引名，因为主键只有一个。

example:

1>建立测试表
mysql> create table member (
    -> id int,
    -> email varchar(30),
    -> tel varchar(11),
    -> intro text
    -> )engine myisam charset utf8;

2>建立索引
给tel建立普通索引： mysql> alter table member add index   tel (tel);
给email建立唯一索引：mysql> alter table member add unique      (email);
给intro建立全文索引：mysql> alter table member add fulltext    (intro);
给id建立主键索引：   mysql> alter table member add primary key (id);

结果：
mysql> show index from member \G
*************************** 1. row ***************************
        Table: member
   Non_unique: 0
     Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
    Collation: A
Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: member
   Non_unique: 0
     Key_name: email
Seq_in_index: 1
Column_name: email
    Collation: A
Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: member
   Non_unique: 1
     Key_name: tel
Seq_in_index: 1
Column_name: tel
    Collation: A
Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 4. row ***************************
        Table: member
   Non_unique: 1
     Key_name: intro
Seq_in_index: 1
Column_name: intro
    Collation: NULL
Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment:
Index_comment:
4 rows in set (0.00 sec)

3、删除索引：alter table 表名 drop index 索引名

example：
删除非主键索引：
alter table member drop index intro;
alter table member drop index tel;
alter table member drop index email;
删除主键索引：
alter table member drop primary key;

三、全文索引(fulltext)

1、关于全文索引(fulltext)的用法
match(全文索引名) against('keyword');

2、关于全文索引(fulltext)的停止词
全文索引不针对非常频繁的词做索引，例如this，is，my等。

3、全文索引在mysql默认情况下，对中文意义不大
因为英文有空格，标点符号来拆成单词，进而对单词进行索引；
而对于中文，没有空格来隔开单词，因此mysql无法识别每一个中文词。

4、查看全文索引中查询关键字的匹配度
select match(全文索引名) against('keyword') from 表名;

example：

1、给“intro”添加全文索引：
mysql> alter table member add fulltext (intro);
mysql> show index from member \G
*************************** 1. row ***************************
        Table: member
   Non_unique: 1
     Key_name: intro
Seq_in_index: 1
Column_name: intro
    Collation: NULL
Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment:
Index_comment:
1 row in set (0.00 sec)

2、插入数据：
mysql> select * from member;
+----+--------------+-------------+----------------------------------------------+
| id | email        | tel         | intro                                        |
+----+--------------+-------------+----------------------------------------------+
| 1 | jack@163.com | 13311287839 | hello andy, i am jack                        |
| 2 | andy@163.com | 15811305125 | hello lucy , i am andy                       |
| 3 | jack@163.com | 13311287839 | hello Oracle, i am mysql DataBase            |
| 4 | andy@163.com | 15811305125 | hello windows sql server2012 , i am Database |
| 5 | jack@163.com | 13311287839 | Oracle mysql DataBase                       |
| 6 | andy@163.com | 15811305125 | windows sql server2012 Database             |
| 7 | jack@163.com | 13311287839 | Oracle mysql DataBase (核心语句)                       |
+----+--------------+-------------+----------------------------------------------+

3、针对“intro”的值进行常规查询：
mysql> select intro from member where intro like '%hello%';
+-----------------------+
| intro |
+-----------------------+
| hello andy, i am jack |
+-----------------------+

4、针对“intro”的值进行全文索引查询：
mysql> select * from member where match(intro) against ('lucy');
Empty set (0.00 sec)

结果为空的原因是intro中查询'jack'的匹配度为0：

mysql> select id,email,tel,match(intro) against('jack') from member;
+----+--------------+-------------+------------------------------+
| id | email        | tel         | match(intro) against('jack') |
+----+--------------+-------------+------------------------------+
| 1 | jack@163.com | 13311287839 |                            0 |
| 2 | andy@163.com | 15811305125 |                            0 |
+----+--------------+-------------+------------------------------+
2 rows in set (0.00 sec)

正常的查询结果：
mysql> select * from member where match(intro) against('Oracle');
+----+--------------+-------------+-----------------------------------+
| id | email        | tel         | intro                             |
+----+--------------+-------------+-----------------------------------+
| 3 | jack@163.com | 13311287839 | hello Oracle, i am mysql DataBase |
| 5 | jack@163.com | 13311287839 | Oracle mysql DataBase            |
| 7 | jack@163.com | 13311287839 | Oracle mysql DataBase             |
+----+--------------+-------------+-----------------------------------+
3 rows in set (0.00 sec)