Mysql索引讲解

原创
2019/03/07 20:53
阅读数 69

一、 创建索引

  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子句后面跟随的列名进行分组,然后对每一个分组而不是整个表进行操作。
  • 10select 语句完整语法:
    1) select 目标表的列名或列表达式序列

    2)  from 基本表名和(或)视图序列

    3)  [where 条件表达式]

    4)  [group by 列名序列]    [having 组条件表达式]

    5) [order by 列名[asc | desc]]sql 语句的执行顺序是: 正确答案: 2->3->4->1->5

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

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)

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部