索引

1.概念：相当于书的目录，快速找到数据

　　数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍.
　　好处：可以帮助你提高查询效率，数据量越大越明显
　　缺点: 新增和删除数据时，效率较低(需重新排列索引)

　　把一个字段设置为索引，就能够利用这个字段实现快速查找

索引类型

1 hash

hash就是一种（key=>value）形式的键值对,允许多个key对应相同的value，但不允许一个key对应多个value,为某一列或几列建立hash索引，就会利用这一列或几列的值通过一定的算法计算出一个hash值，对应一行或几行数据. hash索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率.

2 btree(默认方式)

就是一种将索引值按一定的算法，存入一个树形的数据结构中. 如二叉树一样

聚集索引和辅助索引

在数据库中，B+树的高度一般都在2~4层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO，这倒不错。因为当前一般的机械硬盘每秒至少可以做100次IO，2~4次的IO意味着查询时间只需要0.02~0.04秒。

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

聚集索引

#InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，
同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
    
#如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。
    
#如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

#由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录

聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

辅助索引

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。

由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。

覆盖索引

InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。联系上面不需要对聚集索引树进行查找

索引合并:利用多个单例索引查询

3.索引分类

1. 普通索引 create INDEX 别名 on 表名(字段名);

-- 1 CREATE index 索引名 on 表名(字段名) ---直接创建索引
-- 2 alter table 表名 add index 别名（字段名） --- 修改表结构
-- 3 index 别名 (字段名) --- 建表时在最后指定

2. 唯一索引 create unique INDEX 别名 on 表名(字段名);

　　索引列的值必须唯一，但允许有空值(只能有一个)。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

-- 1 CREATE UNIQUE index 索引名 on 表名(字段名) --- 直接创建索引
-- 2 alter table 表名 add unique 别名(字段名) --- 修改表结构
-- 3 unique 别名 (字段名) --- 建表时最后指定

3. 主键索引 -就是主键

主键索引是唯一索引的特殊类型。
数据库表通常有一列或列组合，其值用来唯一标识表中的每一行。该列称为表的主键。
在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

-- 1 alter table 表名 add primary key(id);
-- 2 添加之间索引
-- 3 注意:主键索引只能有一个

建表实例

CREATE TABLE `wb_blog` (  
`id` smallint(8) unsigned NOT NULL,   
`catid` smallint(5) unsigned NOT NULL DEFAULT '0',  
`title` varchar(80) NOT NULL DEFAULT '',   
`content` text NOT NULL,  
PRIMARY KEY (`id`),  
UNIQUE KEY `catename` (`catid`)  
) ;

4. 组合索引(最左前缀)

-- 建表后组合索引
ADD INDEX `索引名` (`字段`, `字段`) ;

-- 建表后组合唯一索引
ADD UNIQUE INDEX `索引名` (`字段`, `created`) ;

并不是只要包含这两列的查询都会用到该组合索引,包含字段1的会使用索引

字段1and字段2---使用索引

字段1 ---使用索引

字段2 ---不使用索引

5. 全文索引 full text ：原理是分词查找

　　目前只有MyISAM引擎支持。而且只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引.

　　FULLTEXT索引是按照分词原理建立索引的。西文中，大部分为字母文字，分词可以很方便的按照空格进行分割。但很明显，中文不能按照这种方式进行分词。那又怎么办呢？这个向大家介绍一个Mysql的中文分词插件Mysqlcft，有了它，就可以对中文进行分词，想了解的同学请移步Mysqlcft，当然还有其他的分词插件可以使用。
删除索引

drop index 索引名 on 表明；

索引触发

- 无法命中索引
- like '%xx'
	select * from tb1 where name like '%cn';
- 使用函数
	select * from tb1 where reverse(name) = 'wupeiqi';
- or
	select * from tb1 where nid = 1 or email = 'seven@live.com';
	特别的：当or条件中有未建立索引的列才失效，以下会走索引
		select * from tb1 where nid = 1 or name = 'seven';
		select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- 类型不一致
		如果列是字符串类型，传入条件是必须用引号引起来，不然...
		select * from tb1 where name = 999;
- !=
	select * from tb1 where name != 'alex'
	特别的：如果是主键，则还是会走索引
		select * from tb1 where nid != 123
- >
	select * from tb1 where name > 'alex'
	特别的：如果是主键或索引是整数类型，则还是会走索引
		select * from tb1 where nid > 123
		select * from tb1 where num > 123
- order by
		select email from tb1 order by name desc;
		当根据索引排序时候，选择的映射如果不是索引，则不走索引
		特别的：如果对主键排序，则还是走索引：
			select * from tb1 order by nid desc;
			 
- 组合索引最左前缀
		如果组合索引为：(name,email)
		name and email       -- 使用索引
		name                 -- 使用索引
		email                -- 不使用索引

建索引的几大原则

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2″ c=””> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

查询优化神器 – explain命令 --查看执行计划

关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

配置MySQL自动记录慢日志

slow_query_log = OFF                            是否开启慢日志记录
long_query_time = 2                              时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log        日志文件
log_queries_not_using_indexes = OFF     为使用索引的搜索是否记录
查看当前配置信息：
　　     show variables like '%query%'
修改当前配置：
　　　　set global 变量名 = 值

查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

参数信息

"""
--verbose    版本
--debug      调试
--help       帮助
 
-v           版本
-d           调试模式
-s ORDER     排序方式
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time
              ar: average rows sent
              at: average query time
               c: count
               l: lock time
               r: rows sent
               t: query time
-r           反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM       显示前N条just show the top n queries
-a           不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正则匹配；grep: only consider stmts that include this string
-h HOSTNAME  mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           总时间中不减去锁定时间；don't subtract lock time from total time
"""

慢查询优化基本步骤

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析

posted @ 2017-12-11 20:57 瓜田月夜阅读(95) 评论(0) 收藏举报

刷新页面返回顶部

瓜田月夜

索引