一、 创建索引
-
主键索引创建
主键索引的创建有两种形式:
- 是在创建表的时候,直接指定某列或者某几列为主键;
- 先创建表,再指定主键索引
增加主键: ALTER TABLE 表名 ADD PRIMARY KEY(列名1,列名2…);
-
主键索引的特点
- 一个表最多只能有一个主键
- 一个主键可以指向多列(复合主键)
- 主键索引的效率最高,一般都是id字段(自增id)
- 主键索引列不能重复,也不能为空 null
-
唯一索引创建
- 创建表时候直接指定哪些列为唯一索引
- 先创建表,再指定某列或者某几列为唯一索引
- 使用CREATE INDEX语法(必须指定索引名)
- 使用ALTER TABLE语法(可以指定索引名,也可以不指定)推荐使用
-
唯一索引的特点
- 一张表可以有多个唯一索引
- 唯一索引不能重复,但是如果没有指定not null,唯一索引列可以为null,而且可以有多个null,不能有多个‘’
- 当数据不会出现重复是才使用唯一索引
- 唯一索引效率也很高,可以优先考虑使用
-
普通索引的创建
普通索引必须是先创建表,再指定某列或者某几列为普通索引,创建方式有两种:
- 使用CREATE INDEX语法(必须指定索引名)
- 使用ALTER TABLE语法(可以指定索引名,也可以不指定)推荐使用
-
普通索引的特点
- 一张表中可以有多个普通索引,一个普通索引可以指向多个列
- 普通索引的数据可以重复
- 效率相对于主键索引和唯一索引较低
-
全文索引创建
概述:全文索引是针对文章,汉字,英文的检索,可以快速的检索到文章的某个关键字
-
全文索引的特点
- Mysql默认的全文索引,只在myISam引擎下边可用
- Mysql默认的全文索引,只支持英文
- 停止词:对于特别普通的字母,不会建立索引
- 匹配度:全文索引是按照一定概率来匹配
- 请尽量先创建表并插入所有数据后再创建全文索引,而不要在创建表时就直接创建全文索引,因为前者比后者的全文索引效率要高
-
全文索引的使用
Like用法不能使用mysql的默认全文索引,需要使用match(字段名,…)against(关键字)方法才可以
-
如何解决mysql的全文索引不支持中文的问题
- 使用mysql的中文检索插件 (mysqlcft)
- 使用专门的中文检索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语句的优化和索引的使用
-
下列几种条件会使用到索引
-
对于创建的多列(复合)索引,只要查询条件使用了最左边的列,索引一般就会被使用。
说明:dname是复合索引中最左边的列,因此使用到了索引,而下边的语句没有用到dname字段,故没有使用索引。
-
对于使用了like的查询,查询如果是‘%aaa’或者‘_aaa’则不会使用到索引,而‘aaa%’和‘aaa_’则会使用到索引。
说明:在like语句中,如果‘’中最前边有_或者%的话就不使用索引,如果在中间或者最后有_或者%可以使用到索引。(采用sphinx)
-
下列几种条件不会使用到索引
-
如果条件中有or,则要求or的所有字段都必须有索引,否则不能使用索引。
因为deptno字段没有创建索引,故没有使用索引。如果给deptno字段添加索引则可用使用索引(如下)。
-
对于多列(复合)索引,不说使用的左边第一部分,则不会使用索引
-
Like查询以‘%’开头的不使用索引
-
如果列类型是字符串,那一定要在条件中将数据使用引号引起来,否则不使用索引。(添加时,字符串必须使用‘’) 字符串类型字段查询都要加引号吧where条件引起来。
-
如果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不需要在内存中创建临时表。
八、索引的使用情况
-
如何查看创建的索引的使用情况的好坏
- show status like ‘handler_read%’;具体值意义参考mysql手册:注意:handler_read_key 这个值越高越好,越高表示使用索引查询到的次数越多。handler_read_rnd_next 这个值越高,使用索引效率越低,表示未使用索引查询到的次数越多。
-
如果要大批量插入数据(通常是DBA执行),按照下面操作会提高效率--
- 对于MyISAM引擎:
alter table table_name disable keys;
loading data/insert 语句;
alter table table_name enable keys; - 对于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的规定:
- GROUP BY 后面可以包含多个列,这就是嵌套。
- 如果GROUP BY进行了嵌套,数据将在最后一个分组上进行汇总。
- GROUP BY子句中列出来的每个列必须是检索列或有效的表达式(但不能是聚集函数),如果在SELECT中使用了表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
- 除了聚集语句外,SELECT语句中的每一个列都必须在GROUP BY子句中给出。
- 如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列中有多个NULL,它们将作为一个分组返回。
- 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的供应商。
分析:
- 首先,检索的是供应商,因此SELECT子句应该是SELECT vend_id;
- 其次,产品表中有价格这一列,因此对于价格高于10的条件的过滤要使用WHERE子句。SELECT vend_id FROM prodcuts WHERE prod_price>=10.
- 接着,对vend_id进行分组,这样就可以得到每个vend_id的价格高于10的商品数量,GROUP BY放到WHERE子句后。SELECT vend_id FROM prodcuts WHERE prod_price>=10 GROUP BY vend_id.
- 最后,对分组的结果过滤,过滤出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)