- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章快速学习MySQL索引的入门超级教程由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.
所谓索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找。而用的最多,并且是mysql默认的就是二叉树算法 BTREE,通过BTREE算法建立索引的字段,比如扫描20行就能得到未使用BTREE前扫描了2^20行的结果,具体的实现方式后续本博客会出一个算法专题里面会有具体的分析讨论,
Explain优化查询检测 。
EXPLAIN可以帮助开发人员分析SQL问题,explain显示了mysql如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句. 。
使用方法,在select语句前加上Explain就可以了:
1
|
Explain
select
*
from
blog
where
false
;
|
mysql在执行一条查询之前,会对发出的每条SQL进行分析,决定是否使用索引或全表扫描如果发送一条select * from blog where falseMysql是不会执行查询操作的,因为经过SQL分析器的分析后MySQL已经清楚不会有任何语句符合操作,
Example 。
1
|
mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` <
"1990/2/2"
;
|
-- 结果: 。
1
|
id: 1
|
select_type: SIMPLE -- 查询类型(简单查询,联合查询,子查询) 。
select_type 。
Extra与type详细说明 。
索引 。
索引的类型 。
(1)UNIQUE唯一索引 。
不可以出现相同的值,可以有NULL值 。
(2)INDEX普通索引 。
允许出现相同的索引内容 。
(3)PRIMARY KEY主键索引 。
不允许出现相同的值,且不能为NULL值,一个表只能有一个primary_key索引 。
(4)fulltext index 全文索引 。
上述三种索引都是针对列的值发挥作用,但全文索引,可以针对值中的某个单词,比如一篇文章中的某个词, 然而并没有什么卵用,因为只有myisam以及英文支持,并且效率让人不敢恭维,但是可以用coreseek和xunsearch等第三方应用来完成这个需求 。
MySQL支持很多数据类型,选择合适的数据类型存储数据对性能有很大的影响。通常来说,可以遵循以下一些指导原则:
(1)越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。 (2)简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。 (3)尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。 对于任何DBMS,索引都是进行优化的最主要的因素。对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧下降。 如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效的查找。例如: 假设存在组合索引it1c1c2(c1,c2),查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是,查询语句select * from t1 where c2=2不能够使用该索引,因为没有组合索引的引导列,即,要想使用c2列进行查找,必需出现c1等于某值.
索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的.
在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表:
1
2
3
4
5
6
7
|
CREATE
TABLE
mytable(
ID
INT
NOT
NULL
,
username
VARCHAR
(16)
NOT
NULL
);
|
我们随机向里面插入了10000条记录,其中有一条:5555, admin.
在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描所有记录,即要查询10000条记录.
。
索引的创建 。
1
|
ALTER
TABLE
|
适用于表创建完毕之后再添加 。
ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)[索引名](字段名) 。
1
2
3
4
5
|
ALTER
TABLE
`table_name`
ADD
INDEX
`index_name` (`column_list`)
-- 索引名,可要可不要;如果不要,当前的索引名就是该字段名;
ALTER
TABLE
`table_name`
ADD
UNIQUE
(`column_list`)
ALTER
TABLE
`table_name`
ADD
PRIMARY
KEY
(`column_list`)
ALTER
TABLE
`table_name`
ADD
FULLTEXT
KEY
(`column_list`)
CREATE
INDEX
|
CREATE INDEX可对表增加普通索引或UNIQUE索引 。
--例,只能添加这两种索引,
1
2
|
CREATE
INDEX
index_name
ON
table_name (column_list)
CREATE
UNIQUE
INDEX
index_name
ON
table_name (column_list)
|
另外,还可以在建表时添加 。
1
2
3
4
5
6
7
8
9
10
|
CREATE
TABLE
`test1` (
`id`
smallint
(5) UNSIGNED AUTO_INCREMENT
NOT
NULL
,
-- 注意,下面创建了主键索引,这里就不用创建了
`username`
varchar
(64)
NOT
NULL
COMMENT
'用户名'
,
`nickname`
varchar
(50)
NOT
NULL
COMMENT
'昵称/姓名'
,
`intro` text,
PRIMARY
KEY
(`id`),
UNIQUE
KEY
`unique1` (`username`),
-- 索引名称,可要可不要,不要就是和列名一样
KEY
`index1` (`nickname`),
FULLTEXT
KEY
`intro` (`intro`)
) ENGINE=MyISAM AUTO_INCREMENT=4
DEFAULT
CHARSET=utf8 COMMENT=
'后台用户表'
;
|
索引的删除 。
1
2
3
4
5
|
DROP
INDEX
`index_name`
ON
`talbe_name`
ALTER
TABLE
`table_name`
DROP
INDEX
`index_name`
-- 这两句都是等价的,都是删除掉table_name中的索引index_name;
ALTER
TABLE
`table_name`
DROP
PRIMARY
KEY
-- 删除主键索引,注意主键索引只能用这种方式删除
|
索引的查看 。
1
|
show
index
from
tablename \G;
|
索引的更改 。
更改个毛线,删掉重建一个既可-- 。
创建索引的技巧 。
1.维度高的列创建索引 。
数据列中 不重复值 出现的个数,这个数量越高,维度就越高 。
如数据表中存在8行数据a ,b ,c,d,a,b,c,d这个表的维度为4 。
要为维度高的列创建索引,如性别和年龄,那年龄的维度就高于性别 。
性别这样的列不适合创建索引,因为维度过低 。
2.对 where,on,group by,order by 中出现的列使用索引 。
3.对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键 。
4.为较长的字符串使用前缀索引 。
5.不要过多创建索引,除了增加额外的磁盘空间外,对于DML操作的速度影响很大,因为其每增删改一次就得从新建立索引 。
6.使用组合索引,可以减少文件索引大小,在使用时速度要优于多个单列索引 。
组合索引与前缀索引 。
注意,这两种称呼是对建立索引技巧的一种称呼,并非索引的类型,
组合索引 。
MySQL单列索引和组合索引究竟有何区别呢?
为了形象地对比两者,先建一个表:
1
2
3
4
5
6
7
8
|
CREATE
TABLE
`myIndex` (
`i_testID`
INT
NOT
NULL
AUTO_INCREMENT,
`vc_Name`
VARCHAR
(50)
NOT
NULL
,
`vc_City`
VARCHAR
(50)
NOT
NULL
,
`i_Age`
INT
NOT
NULL
,
`i_SchoolID`
INT
NOT
NULL
,
PRIMARY
KEY
(`i_testID`)
);
|
假设表内已有1000条数据,在这 10000 条记录里面 7 上 8 下地分布了 5 条 vc_Name="erquan" 的记录,只不过 city,age,school 的组合各不相同。来看这条 T-SQL:
SELECT `i_testID` FROM `myIndex` WHERE `vc_Name`='erquan' AND `vc_City`='郑州' AND `i_Age`=25; -- 关联搜索; 首先考虑建MySQL单列索引:
在 vc_Name 列上建立了索引。执行 T-SQL 时,MYSQL 很快将目标锁定在了 vc_Name=erquan 的 5 条记录上,取出来放到一中间结果集。在这个结果集里,先排除掉 vc_City 不等于"郑州"的记录,再排除 i_Age 不等于 25 的记录,最后筛选出唯一的符合条件的记录。虽然在 vc_Name 上建立了索引,查询时MYSQL不用扫描整张表,效率有所提高,但离我们的要求还有一定的距离。同样的,在 vc_City 和 i_Age 分别建立的MySQL单列索引的效率相似.
为了进一步榨取 MySQL 的效率,就要考虑建立组合索引。就是将 vc_Name,vc_City,i_Age 建到一个索引里:
ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age),
建表时,vc_Name 长度为 50,这里为什么用 10 呢?这就是下文要说到的前缀索引,因为一般情况下名字的长度不会超过 10,这样会加速索引查询速度,还会减少索引文件的大小,提高 INSERT 的更新速度.
执行 T-SQL 时,MySQL 无须扫描任何记录就到找到唯一的记录!.
如果分别在 vc_Name,vc_City,i_Age 上建立单列索引,让该表有 3 个单列索引,查询时和上述的组合索引效率一样吗?答案是大不一样,远远低于我们的组合索引。虽然此时有了三个索引, 但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引,另外两个是用不到的,也就是说还是一个全表扫描的过程 .
建立这样的组合索引,其实是相当于分别建立了 。
这样的三个组合索引!为什么没有 vc_City,i_Age 等这样的组合索引呢?这是因为 mysql 组合索引 "最左前缀" 的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个 T-SQL 会用到:
1
|
SELECT
*
FROM
myIndex WHREE vc_Name=
"erquan"
AND
vc_City=
"郑州"
SELECT
*
FROM
myIndex WHREE vc_Name=
"erquan"
|
而下面几个则不会用到:
1
|
SELECT
*
FROM
myIndex WHREE i_Age=20
AND
vc_City=
"郑州"
SELECT
*
FROM
myIndex WHREE vc_City=
"郑州"
|
也就是,name_city_age(vc_Name(10),vc_City,i_Age) 从左到右进行索引,如果没有左前索引Mysql不执行索引查询 。
前缀索引 。
如果索引列长度过长,这种列索引时将会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行索引前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值就可以创建) 。
SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- 这个值大于0.31就可以创建前缀索引,Distinct去重复 ALTER TABLE `user` ADD INDEX `uname`(title(10)); -- 增加前缀索引SQL,将人名的索引建立在10,这样可以减少索引文件大小,加快索引查询速度 。
什么样的sql不走索引 。
要尽量避免这些不走索引的sql 。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SELECT
`sname`
FROM
`stu`
WHERE
`age`+10=30;
-- 不会使用索引,因为所有索引列参与了计算
SELECT
`sname`
FROM
`stu`
WHERE
LEFT
(`
date
`,4) <1990;
-- 不会使用索引,因为使用了函数运算,原理与上面相同
SELECT
*
FROM
`houdunwang`
WHERE
`uname`
LIKE
'后盾%'
-- 走索引
SELECT
*
FROM
`houdunwang`
WHERE
`uname`
LIKE
"%后盾%"
-- 不走索引
-- 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因
-- 字符串与数字比较不使用索引;
CREATE
TABLE
`a` (`a`
char
(10));
EXPLAIN
SELECT
*
FROM
`a`
WHERE
`a`=
"1"
-- 走索引
EXPLAIN
SELECT
*
FROM
`a`
WHERE
`a`=1
-- 不走索引
select
*
from
dept
where
dname=
'xxx'
or
loc=
'xx'
or
deptno=45
--如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字
-- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
|
多表关联时的索引效率 。
1
2
3
|
SELECT
`sname`
FROM
`stu`
WHERE
LEFT
(`
date
`,4) <1990;
-- 不会使用索引,因为使用了函数运算,原理与上面相同
SELECT
*
FROM
`houdunwang`
WHERE
`uname`
LIKE
'后盾%'
-- 走索引
SELECT
*
FROM
`houdunwang`
WHERE
`uname`
LIKE
"%后盾%"
-- 不走索引
|
从上图可以看出,所有表的type为all,表示全表索引;也就是6 6 6,共遍历查询了216次,
除第一张表示全表索引(必须的,要以此关联其他表),其余的为range(索引区间获得),也就是6+1+1+1,共遍历查询9次即可,
所以我们建议在多表join的时候尽量少join几张表,因为一不小心就是一个笛卡尔乘积的恐怖扫描,另外,我们还建议尽量使用left join,以少关联多.因为使用join 的话,第一张表是必须的全扫描的,以少关联多就可以减少这个扫描次数. 。
索引的弊端 。
不要盲目的创建索引,只为查询操作频繁的列创建索引,创建索引会使查询操作变得更加快速,但是会降低增加、删除、更新操作的速度,因为执行这些操作的同时会对索引文件进行重新排序或更新,
但是,在互联网应用中,查询的语句远远大于DML的语句,甚至可以占到80%~90%,所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引,
最后此篇关于快速学习MySQL索引的入门超级教程的文章就讲到这里了,如果你想了解更多关于快速学习MySQL索引的入门超级教程的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
Hive —— 入门 Hive介绍 Apache Hive是一款建立在Hadoop之上的开源数据仓库系统,可以将存储在Hadoop文件中的结构化、半结构化数据文件映射为一张数据库表,基于表提供了一
HBase —— 入门 HBase介绍 HBase是一个分布式的、面向列的开源数据库,该技术来源于 Fay Chang 所撰写的Google论文“Bigtable:一个结构化数据的分布式存储系统”
零:前端目前形势 前端的发展史 HTML(5)、CSS(3)、JavaScript(ES5、ES6):编写一个个的页面 -> 给后端(PHP、Python、Go、Java) ->
在本教程中,您将了解在计算机上运行 JavaScript 的不同方法。 JavaScript 是一种流行的编程语言,具有广泛的应用程序。 JavaScript 以前主要用于使网页具有交
我曾经是一个对编程一窍不通的小白,但因为对互联网世界的好奇心和求知欲的驱使,我踏入了编程的殿堂。在学习的过程中,我发现了一门神奇的编程语言——Python。Python有着简洁、易读的语法,让初学者能
嗨,亲爱的读者们! 今天我要给大家分享一些关于Python爬虫的小案例。你是否曾为了获取特定网页上的数据而烦恼过?或者是否好奇如何从网页中提取信息以供自己使用?那么,这篇文章将会给你一些启示和灵感。
关闭。这个问题是opinion-based 。目前不接受答案。 想要改进这个问题吗?更新问题,以便 editing this post 可以用事实和引文来回答它。 . 已关闭 8 年前。 Improv
我想创建一个像https://apprtc.appspot.com/?r=04188292这样的应用程序。我对 webrtc 了解一点,但无法掌握 google app-engine。如何为 java
我刚刚开始使用 Python 并编写了一个简单的周边程序。但是,每当我在终端中键入 python perimeter.py 时,都会收到以下错误,我不知道如何解决。 >>> python perime
Redis有5个基本数据结构,string、list、hash、set和zset。它们是日常开发中使用频率非常高应用最为广泛的数据结构,把这5个数据结构都吃透了,你就掌握了Redis应用知识的一半了
创建发布web项目 具体步骤: 1.在开发工具中创建一个dynamic web project helloword 2.在webContent中创建index.html文件 3.发布web应用到
如果你在 Ubuntu 上使用终端的时间很长,你可能会希望调整终端的字体和大小以获取一种良好的体验。 更改字体是一种最简单但最直观的 Linux 的终端自定义 的方法。让我
1. 前言 ADODB 是 Active Data Objects Data Base 的简称,它是一种 PHP 存取数据库的函式组件。现在 SFS3 系统 (校园自由软件交流网学务系统) 计划的
我对 neo4j 完全陌生,我很抱歉提出这样一个基本问题。我已经安装了neo4j,我正在使用shell“localhost:7474/webadmin/#/console/” 我正在寻找一个很好的例子
我正在阅读 ios 4 的核心音频,目的是构建一个小测试应用程序。 在这一点上,我对所有 api 的研究感到非常困惑。理想情况下,我想知道如何从两个 mp3 中提取一些样本到数组中。 然后在回调循环中
关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。 要求我们推荐或查找工具、库或最喜欢的场外资源的问题对于 Stack Overflow 来说是无关紧要的,因
我下载了 GNUStep并安装了它,但是我不确定在哪里可以找到 IDE。有谁知道什么程序可以用作 GNUStep IDE/从哪里获取它们?否则,有没有人知道有关如何创建和编译基本 GNUStep 程序
我正在尝试开始使用 Apache Solr,但有些事情我不清楚。通读tutorial ,我已经设置了一个正在运行的 Solr 实例。我感到困惑的是 Solr 的所有配置(架构等)都是 XML 格式的。
请问有没有关于如何开始使用 BruTile 的文档? 我目前正在使用 SharpMap,我需要预缓存切片以加快进程 最佳答案 我今天正在研究这个:)Mapsui项目site严重依赖 SharpMap
尽我所能,我无法让 CEDET 做任何事情。 Emacs 24.3。我下载了最新的 CEDET 快照。我从他的底部(不是这样)Gentle Introduction 中获取了 Alex Ott 的设置
我是一名优秀的程序员,十分优秀!