Mysql索引讲解

一、 创建索引

• 主键索引创建

主键索引的创建有两种形式：

• 是在创建表的时候，直接指定某列或者某几列为主键；

• 先创建表，再指定主键索引

增加主键： ALTER TABLE 表名 ADD PRIMARY KEY（列名1，列名2…）；

• 主键索引的特点

1. 一个表最多只能有一个主键
2. 一个主键可以指向多列（复合主键）
3. 主键索引的效率最高，一般都是id字段（自增id）
4. 主键索引列不能重复，也不能为空 null

• 唯一索引创建

• 创建表时候直接指定哪些列为唯一索引
• 先创建表，再指定某列或者某几列为唯一索引

1. 使用CREATE INDEX语法（必须指定索引名）
2. 使用ALTER TABLE语法（可以指定索引名，也可以不指定）推荐使用

• 唯一索引的特点

1. 一张表可以有多个唯一索引
2. 唯一索引不能重复，但是如果没有指定not null，唯一索引列可以为null，而且可以有多个null，不能有多个‘’
3. 当数据不会出现重复是才使用唯一索引
4. 唯一索引效率也很高，可以优先考虑使用

• 普通索引的创建

普通索引必须是先创建表，再指定某列或者某几列为普通索引，创建方式有两种：

1. 使用CREATE INDEX语法（必须指定索引名）
2. 使用ALTER TABLE语法（可以指定索引名，也可以不指定）推荐使用

• 普通索引的特点

1. 一张表中可以有多个普通索引，一个普通索引可以指向多个列
2. 普通索引的数据可以重复
3. 效率相对于主键索引和唯一索引较低

• 全文索引创建

概述：全文索引是针对文章，汉字，英文的检索，可以快速的检索到文章的某个关键字

• 全文索引的特点

1. Mysql默认的全文索引，只在myISam引擎下边可用
2. Mysql默认的全文索引，只支持英文
3. 停止词：对于特别普通的字母，不会建立索引
4. 匹配度：全文索引是按照一定概率来匹配
5. 请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高

• 全文索引的使用

Like用法不能使用mysql的默认全文索引，需要使用match（字段名，…）against（关键字）方法才可以

• 如何解决mysql的全文索引不支持中文的问题

1. 使用mysql的中文检索插件 （mysqlcft）
2. 使用专门的中文检索sphinx（中文版） Sphinx是一个基于SQL的全文检索引擎，可以结合MySQL,PostgreSQL做全文搜索，它可以提供比数据库本身更专业的搜索功能，使得应用程序更容易实现专业化的全文检索。

二 索引的查询

• Desc 表名
• Show keys from table(表名) \G
• Show index from table(表名) \G
• Show indexes from table (表名) \G

三 、索引的修改

    通常是先把现有的删除然后创建新的索引

四、索引的删除

• DROP INDEX 索引名 ON 表名
• ALTER TABLE 表名 DROP INDEX 索引名

五、索引的原理（BTREE二叉树结构）

  

用了索引之后，对表的增，删，改操作也会影响，而且占用内存比较多。

六、索引的注意事项：

• 比较频繁的作为查询条件的字段应该创建索引

    Select * from emp where empno=1

• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

    Select * from emp where sex=‘男‘

• 更新非常频繁的字段不适合创建索引

    Updatetime，logincount（用户状态）等类似数据

• 不会出现再where子句中的字段不应该创建索引

 

七、SQL语句的优化和索引的使用

• 下列几种条件会使用到索引

1. 对于创建的多列（复合）索引，只要查询条件使用了最左边的列，索引一般就会被使用。                                             说明：dname是复合索引中最左边的列，因此使用到了索引，而下边的语句没有用到dname字段，故没有使用索引。

2. 对于使用了like的查询，查询如果是‘%aaa’或者‘_aaa’则不会使用到索引，而‘aaa%’和‘aaa_’则会使用到索引。

        说明：在like语句中，如果‘’中最前边有_或者%的话就不使用索引，如果在中间或者最后有_或者%可以使用到索引。（采用sphinx）

• 下列几种条件不会使用到索引

1. 如果条件中有or，则要求or的所有字段都必须有索引，否则不能使用索引。

因为deptno字段没有创建索引，故没有使用索引。如果给deptno字段添加索引则可用使用索引（如下）。

1. 对于多列（复合）索引，不说使用的左边第一部分，则不会使用索引

2. Like查询以‘%’开头的不使用索引

3. 如果列类型是字符串，那一定要在条件中将数据使用引号引起来，否则不使用索引。（添加时，字符串必须使用‘’） 字符串类型字段查询都要加引号吧where条件引起来。

4. 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

• 优化group by语句

如果查询中没有使用索引字段直接group by则不使用索引

默认情况下，mysql对所有的group by col1，col2进行排序。这与在查询指定order by col1，col2类似，如果查询中包括group by，但用户想要避免排序结果的消耗（using filesort mysql自己的内部排序），则可以使用order by null来禁止排序，如下：

• 能使用连接查询的使用连接查询代替子查询

说明：使用子查询mysql会创建临时表，临时表比较占内存空间，而使用连接查询（join等）mysql不需要在内存中创建临时表。

八、索引的使用情况

• 如何查看创建的索引的使用情况的好坏

1. show status like ‘handler_read%’;具体值意义参考mysql手册：注意：handler_read_key 这个值越高越好，越高表示使用索引查询到的次数越多。handler_read_rnd_next 这个值越高，使用索引效率越低，表示未使用索引查询到的次数越多。

• 如果要大批量插入数据（通常是DBA执行），按照下面操作会提高效率--

1. 对于MyISAM引擎：
   alter table table_name disable keys；
   loading data/insert 语句；
   alter table table_name enable keys；
2. 对于Innodb引擎：
   首先将要导入的数据按照主键排序；
   然后set unique_checks=0，关闭唯一性校验；
   最后还要set autocommit=0，关闭自动提交。

九、SELECT 语句规范

• SELECT 子句的顺序

SELECT   FROM   WHERE   GROUP BY    HAVING     ORDER BY      LIMIT

• 对GROUP BY子句后面跟随的列名进行分组，然后对每一个分组而不是整个表进行操作。

举例：在产品表中，检索每一个供应商提供的商品的数量。

mysql> SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id;  

结果：

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
4 rows in set (0.01 sec)

分析：首先根据vend_id进行分组，然后对每一个分组在进行COUNT聚集。当检索的目的是针对每一个记录进行检索的时候，想到用GROUP BY，例如这里是针对每一个供应商。

• GROUP BY的规定：

1. GROUP BY 后面可以包含多个列，这就是嵌套。
2. 如果GROUP BY进行了嵌套，数据将在最后一个分组上进行汇总。
3. GROUP BY子句中列出来的每个列必须是检索列或有效的表达式（但不能是聚集函数），如果在SELECT中使用了表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
4. 除了聚集语句外，SELECT语句中的每一个列都必须在GROUP BY子句中给出。
5. 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多个NULL，它们将作为一个分组返回。
6. GROUP BY子句必须在WHERE 子句之后，ORDER BY 子句之前。

• 过滤分组结果

我们知道WHERE 子句用于过滤结果，但是对于分组的过滤WHERE子句不行。因为WHERE子句，是针对行的过滤。要对分组结果进行过滤，必须使用HAVING子句，HAVING子句能针对分组的结果进行过滤。

举例：在订单表中，检索出具有两个以上订单的客户id以及订单数量。

分析：在这个检索需求中，需要先根据客户id进行分组，然后过滤出订单数量大于2的分组。

mysql> SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING orders>=2;  

结果：

+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
1 row in set (0.00 sec)

• 与WHERE组合使用（先用WHERE过滤）

有的时候，GROUP BY和WHERE子句也要组合使用。比如：在产品表中检索出能提供2个以上商品，并且价格高于10的供应商。

分析：

1. 首先，检索的是供应商，因此SELECT子句应该是SELECT vend_id；
2. 其次，产品表中有价格这一列，因此对于价格高于10的条件的过滤要使用WHERE子句。SELECT vend_id FROM prodcuts WHERE prod_price>=10.
3. 接着，对vend_id进行分组，这样就可以得到每个vend_id的价格高于10的商品数量，GROUP BY放到WHERE子句后。SELECT vend_id FROM prodcuts WHERE prod_price>=10 GROUP BY vend_id.
4. 最后，对分组的结果过滤，过滤出2个以上商品的分组

mysql> SELECT vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price>=10 GROUP BY vend_id HAVING COUNT(*)>=2; 

结果：

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
2 rows in set (0.00 sec)

• 对分组结果进行排序

在订单明细表中，检索出订单总价格高于等于50的订单号以及订单总价格并按照订单总价格排序

mysql> SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;  

结果是：

+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+
4 rows in set (0.08 sec)

转载于:https://my.oschina.net/u/3432745/blog/3019482