1.数据库索引的作用:
(1)提高查询速度
(2)确保数据的一致性
(3)可以加速表和表之间的连接,实现表与表之间的参照完整性
(4)使用分组和排序字句进行数据检索时,可以显著减少分组和排序的时间
(5)全文检索字段进行搜索优化
2.分类:
(1)主键索引(PRIMARY KEY)
(2)唯一索引(UNIQUE)
(3)常规索引(INDEX)
(4)全文索引(FULLTEXT)
3.主键索引:
某一个属性组能唯一标识一条记录
特点:
最常见的索引类型
确保数据记录的唯一性
4.唯一索引:
作用:避免同一个表中某数据列中的值重复
与主键索引的区别:
主键索引只能有一个
唯一索引可有多个
5.常规索引:
作用:快速定位特定数据
注意:
index和key关键字都可以设置常规索引
应加在查找条件的字段
不宜添加太多常规索引,影响数据的插入,删除和修改操作
6.全文索引:
作用:快速定位特定数据
注意:
只能用于MyISAM类型的数据表
只能用于CHAR,VARCHAR,TEXT数据列类型
适合大型数据集
7.管理索引
(1)创建索引
创建表时添加
建表后追加
语法:ALERT TABLE 表名 ADD 索引类型 (数据列名)
(2)删除索引:
DROP INDEX 索引名 ON 表名
ALTER TABLE 表名 DROP INDEX 索引名
ALTER TABLE 表名 DROP PRIMARY KEY
(3)查看索引
SHOW INDEX(或KEYS) FROM 表名
8.索引准则:
(1)索引不是越多越好
(2)不要对经常变动的数据加索引
(3)小数据量的表建议不要加索引
(4)索引一般应加在查找条件的字段
9.SQL语句的执行性能
EXPLAIN 表名 (DESC表名)
EXPLAIN SELECT语句
提示:使用\G结尾能竖排显示
10.什么时候添加索引:
在WHERE,ORDER BY 字句中经常使用的字段
字段的值是多个
字段内容不是经常变化的
*经常变化的字段,添加索引反而降低性能
不宜过多添加索引
*每添加一条索引都会占用磁盘空间
索引有好有坏,索引中的字段被更新的时候,不仅仅要更新表中的数据,还要更新索引数据,以确保索引信息是准确的,这个问题致使IO访问量较大增加,不仅仅影响还要更新索引数据,以确保索引信息是准确的,还影响了整个存储系统的资源消耗,加大了整个存储的负载.
常用的聚合函数
使用规范时 默认的是all
distinct
count
select count (
sum
select
分组
select
过滤聚合函数(having)
要注意的是
课后练习
select * from nobel
-- 1获奖总人数
select count (distinct winner) from nobel
-- 2 诺贝尔 物理奖的获奖总次数
select count(subject)from nobel where subject='Physics'
-- 3 显示每个奖项的获奖总次数
select subject,count(subject)from nobel group by subject
-- 4显示每个奖项第一次获得的年份
select min(yr) 年份,subject 奖项 from nobel group by subject
-- 5 显示每个奖项在2000年来获得的人数
select subject 奖项,yr 年份 from nobel where yr=2000 group by subject ,yr
-- 6 显示每个奖项不同获奖者的人数
select subject 奖项,count (distinct winner)from nobel group by subject
-- 7
select subject 奖项,count (distinct yr)from nobel group by subject
-- 8 显示当年有三个Physic的年份
select subject 奖项,yr 年份 from nobel where
-- 9 得奖大于1的winner
select winner 获奖者 from nobel group by winner having count(subject)>1
--10 得到多个奖项的winner
select winner 获奖者 from nobel group by winner having count(distinct subject)>1