mysql索引
1 初识索引
索引在MySQL中也叫是一种“键”,是存储引擎用于快速找到记录的一种数据结构 —— 排好序的快速查找数据结构。
数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。
2 索引的原理
索引的目的在于提高查询效率,与我们查阅图书所用的目录是一个道理:先定位到章,然后定位到该章下的一个小节,然后找到页数。相似的例子还有:查字典,查火车车次,飞机航班等
索引的原理:通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式来锁定数据。
索引的本质:实际上是一张表,该表保存了主键和索引字段,并指向实体表的记录,所以索引列也是要占用空间的一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以文件形式存储在硬盘上。
【优势】
(1)类似大学图书馆建书目索引,提高数据检索效率,降低数据库的IO成本
(2)通过索引列对数据进行排序,降低数据排序成本,降低了CPU的消耗
【劣势】
(1)虽然索引大大提高了查询速度,同时却会降低更新表(增删改)的速度。因为更新表时,MySQL不仅要不存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
(2)索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立优秀的索引,或优化查询语句(需要不断调整优化)。
3 MySQL索引
3.1 索引分类
#### 常用索引 #### #1、单值索引:即一个索引只包含单个列,一个表可以有多个单列索引 #2、唯一索引:索引列的值必须唯一,但允许有空值 -主键索引PRIMARY KEY:加速查找+约束(不为空、不能重复) -唯一索引UNIQUE:加速查找+约束(不能重复) #3、联合索引:即一个索引包含多个列 -PRIMARY KEY(id,name):联合主键索引 -UNIQUE(id,name):联合唯一索引 -INDEX(id,name):联合普通索引
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# 组合索引(最左前缀匹配): - create unique index 索引名称 on 表名(列名,列名) - drop unique index 索引名称 on 表名 - create index ix_name_email on userinfo3(name,email,) - 最左前缀匹配【一定要包含最左边的】 √ select * from userinfo3 where name='alex'; √ select * from userinfo3 where name='alex' and email='asdf'; × select * from userinfo3 where email='alex@qq.com'; # 组合索引效率 > 索引合并 组合索引 - (name,email,) select * from userinfo3 where name='alex' and email='asdf'; select * from userinfo3 where name='alex'; 索引合并【把多个单列索引合并使用】: - name - email select * from userinfo3 where name='alex' and email='asdf'; select * from userinfo3 where name='alex'; select * from userinfo3 where email='asdf';
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#覆盖索引:在索引文件中直接获取数据。 即从辅助索引中就可以得到查询记录,而不需要查询聚集索引中的记录。
3.2 基本语法
(1)建表时添加索引
建表同时建立单索引
CREATE TABLE t_user1(id INT , userName VARCHAR(20), PASSWORD VARCHAR(20), INDEX (userName) #关键字INDEX );
建表同时建立唯一索引(可以是单或多)
CREATE TABLE t_user2(id INT , userName VARCHAR(20), PASSWORD VARCHAR(20), UNIQUE INDEX index_userName(userName) #关键字UNIQUE和INDEX );
建表同时建立联合索引
CREATE TABLE t_user3(id INT , userName VARCHAR(20), PASSWORD VARCHAR(20), INDEX index_userName_password(userName,PASSWORD) );
(2)给已存在表添加索引
-- 单列索引 CREATE INDEX index_userName ON t_user(userName); -- 唯一索引 CREATE UNIQUE INDEX index_userName ON t_user(userName); -- 联合索引 CREATE INDEX index_userName_password ON t_user(userName,PASSWORD);
(3)删除索引
DROP INDEX index_userName ON t_user; DROP INDEX index_userName_password ON t_user;
3.3 MySQL常见瓶颈
-
- CPU:CPU在饱和的时候一般发生在数据装入在内存或从磁盘上读取数据时候
- IO:磁盘I/O瓶颈发生在装入数据远大于内存容量时
- 服务器硬件的性能瓶颈:top,free,iostat和vmstat来查看系统的性能状态
4 正确使用索引
4.1 哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询的条件的字段应该创建索引
- 查询中与其他表关联的字段,外键关系建立索引
- 单间/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序的速度
- 查询中统计或者分组字段
- 哪些字段可以建索引? 一般都where、order by 或者 group by 后面的字段。
4.2 哪些情况不要创建索引
- Where条件里用不到的字段不创建索引
- 表记录太少
- 当修改性能(频繁更新的字段)远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的,因为每次更新不单单是更新了记录还会更新索引,加重IO负担)
- 数据重复且分布平均的表字段,因此应该只为经常查询和经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
- 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。
- 对于那些在查询中很少使用或者参考的列不应该创建索引
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#1、and与or的逻辑 条件1 and 条件2:所有条件都成立才算成立,但凡要有一个条件不成立则最终结果不成立 条件1 or 条件2:只要有一个条件成立则最终结果就成立 #2、and的工作原理 条件: a = 10 and b = 'xxx' and c > 3 and d =4 索引: 制作联合索引(d,a,b,c) 工作原理: 对于连续多个and:mysql会按照联合索引,从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围),加速查询,即按照d—>a->b->c的顺序 #3、or的工作原理 条件: a = 10 or b = 'xxx' or c > 3 or d =4 索引: 制作联合索引(d,a,b,c) 工作原理: 对于连续多个or:mysql会按照条件的顺序,从左到右依次判断,即a->b->c->d
4.3 mysql索引失效case及优化:
1,全值匹配我最爱 -- 【optimizer会对查询顺序自动进行优化调整】 2,最佳左前缀法则 【带头大哥不能死,中间兄弟不能断】 -- 如果索引了多例,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。 3,索引列少计算 -- 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描 4,范围之后全失效 -- 存储引擎不能使用索引中范围条件右边的列 5,覆盖索引代替select * -- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select* 6,少用 is null或is not null -- is null,is not null 也无法使用索引 7,少用!=或<> -- mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描 8,少用or -- 少用or,用它连接时会索引失效【查看上边and/or 工作原理】 9,like %放右边 -- 【like以通配符开头('%abc...')mysql索引失效会变成全表扫描操作】 -- 【放左边是all,连带他本身全失效】 -- 【可用覆盖索引(varchar为380失效)或主键索引】 10,varchar类型要加''单引号,切记!!! -- 【不加会计算-自动转型】 -- 【导致行锁升级为表锁】 11,使用连接(JOIN)来代替子查询(Sub-Queries) 12,使用count(1)
5 查询优化神器-explain
5.1 是什么 —— 查看执行计划
使用EXPLAIN关键字可以模拟优化器执行SQL语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈
优化语句基本上都是在优化rows。具体用法和字段含义可以参考官网 explain-output
5.2 能干嘛
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
- 索引长度
5.3 执行计划包含的信息
Id |
Select_type |
Table |
Type |
Possible_keys |
Key |
Key_len |
Ref |
Rows |
Extra |
【详解】
【1】id :select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序 -- id相同,执行顺序由上至下 -- id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行 -- id相同不同,同时存在 【2】select_type:查询的类型,主要用于区别普通查询、联合查询、子查询等的复杂查询 -- SIMPLE:简单的select查询,查询中不包含子查询或者UNION -- PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为 -- SUBQUERY:在SELECT或者WHERE列表中包含了子查询 -- DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询,把结果放在临时表里。 -- UNION:若第二个SELECT出现在UNION之后,则被标记为UNION; 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED -- UNION RESULT:从UNION表获取结果的SELECT 【3】table:显示这一行的数据是关于哪张表的 【4】type:显示查询使用了何种类型,从最好到最差依次是:-- system>const>eq_ref>ref>range>index>ALL -- system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,这个也可以忽略不计 -- const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。如将主键至于where列表中,MySQL就能将该查询转换为一个常量 -- eq_ref:唯一性索引,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描 -- ref:非唯一索引扫描,返回匹配某个单独值的所有行。 本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体 -- range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引 一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好,因为他只需要开始索引的某一点,而结束语另一点,不用扫描全部索引。 -- index:Full Index Scan,index与ALL区别为index类型只遍历索引树。 这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘中读的) -- all:FullTable Scan,将遍历全表以找到匹配的行 【5】possible_keys:显示可能应用在这张表中的索引,一个或多个。 -- 查询涉及的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用 【6】key:实际使用的索引。如果为null则没有使用索引 -- 查询中若使用了覆盖索引,则索引和查询的select字段重叠(USING index) 【7】key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好 -- key_len显示的值为索引最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的 【8】ref:显示索引那一列被使用了,如果可能的话,是一个常数。那些列或常量被用于查找索引列上的值 【9】rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数 【10】Extra:包含不适合在其他列中显示但十分重要的额外信息 -- Using filesort(不好):说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作成为“文件排序” -- Using temporary:使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by -- Using where:表明使用了where过滤 -- USING index:表示相应的select操作中使用了覆盖索引(Coveing Index),避免访问了表的数据行,效率不错! -- 如果同时出现using where,表明索引被用来执行索引键值的查找; -- 如果没有同时出现using where,表面索引用来读取数据而非执行查找动作。
【type类型说明】:
从最好到最差依次是:system>const>eq_ref>ref>range>index>ALL
一般来说,得保证查询只是达到range级别,最好达到ref
6 慢查询优化的基本步骤
# 0.先运行看看是否真的很慢,注意设置SQL_NO_CACHE # 1.where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高 # 2.explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询) # 3.order by limit 形式的sql语句让排序的表优先查 # 4.了解业务方使用场景 # 5.加索引时参照建索引的几大原则 # 6.观察结果,不符合预期继续从0分析
7 MySQL锁机制
7.1 锁的分类
(1)从数据操作的类型(读、写)分
- 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响
- 写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁。
- 悲观锁:
- 总是假设最坏的情况,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会阻塞直到它拿到锁(共享资源每次只给一个线程使用,其它线程阻塞,用完后再把资源转让给其它线程)。
- 传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。
- 乐观锁:
- 总是假设最好的情况,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号机制和CAS算法实现。
- 乐观锁适用于多读的应用类型,这样可以提高吞吐量,像数据库提供的类似于write_condition机制,其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。
(2)从对数据操作的颗粒度
- 表锁
- 行锁
7.2 表锁(MyISAM)—— 偏读
特点:偏向MyISAM存储引擎,开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发最低
7.3 行锁(InnoDB)—— 偏写
特点:偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
-- InnoDB与MyISAM的最大不同有两点: 一是支持事务(TRANSACTION); 二是采用了行级锁
7.4 页锁(了解即可)
开销和加锁时间界于表锁和行锁之间:会出现死锁;
锁定粒度界于表锁和行锁之间,并发度一般。
7.5 优化建议
- 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
- 合理设计索引,尽量缩小锁的范围
- 尽可能较少检索条件,避免间隙锁
- 尽量控制事务大小,减少锁定资源量和时间长度
- 尽可能低级别事务隔离
8 慢查询日志
查看是否开启及如何开启:
-- 默认: SHOW VARIABLES LIKE '%slow_query_log%' -- 开启: set global slow_query_log = 1
# 慢日志 - 执行时间 > 10 - 未命中索引 - 日志文件路径 配置: - 内存 show variables like '%query%'; show variables like '%queries%'; set global 变量名 = 值 - 配置文件 mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini' my.conf内容: slow_query_log = ON slow_query_log_file = D:/.... # 注意:修改配置文件之后,需要重启服务
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
MySQL日志管理 ======================================================== 错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息 二进制日志: 又称binlog日志,以二进制文件的方式记录数据库中除 SELECT 以外的操作 查询日志: 记录查询的信息 慢查询日志: 记录执行时间超过指定时间的操作 中继日志: 备库将主库的二进制日志复制到自己的中继日志中,从而在本地进行重放 通用日志: 审计哪个账号、在哪个时段、做了哪些事件 事务日志或称redo日志: 记录Innodb事务相关的如事务执行时间、检查点等 ======================================================== # 一、bin-log 1. 启用 # vim /etc/my.cnf [mysqld] log-bin[=dir\[filename]] # service mysqld restart 2. 暂停 //仅当前会话 SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=1; 3. 查看 查看全部: # mysqlbinlog mysql.000002 按时间: # mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" # mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54" # mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" 按字节数: # mysqlbinlog mysql.000002 --start-position=260 # mysqlbinlog mysql.000002 --stop-position=260 # mysqlbinlog mysql.000002 --start-position=260 --stop-position=930 4. 截断bin-log(产生新的bin-log文件) a. 重启mysql服务器 b. # mysql -uroot -p123 -e 'flush logs' 5. 删除bin-log文件 # mysql -uroot -p123 -e 'reset master' # 二、查询日志 启用通用查询日志 # vim /etc/my.cnf [mysqld] log[=dir\[filename]] # service mysqld restart # 三、慢查询日志 3.1 启用慢查询日志 # vim /etc/my.cnf [mysqld] log-slow-queries[=dir\[filename]] long_query_time=n # service mysqld restart MySQL 5.6: slow-query-log=1 slow-query-log-file=slow.log long_query_time=3 单位为秒 3,2 查看慢查询日志 测试:BENCHMARK(count,expr) SELECT BENCHMARK(50000000,2*3);
9 日志分析工具
9.1 mysqldumpshow
查看mysqldumpshow的帮助信息
s:是表示按何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据
g:后边搭配一个正则匹配模式,大小写不敏感的
9.2 Show profiles
(1)是什么
是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量。
官网:http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
默认情况下,参数处于关闭状态,并保存最近15次的运行结果
(2)分析步骤
1.是否支持,看看当前的SQL版本是否支持 2.开启功能,默认是关闭,使用前需要开启 3.运行SQL -- select * from emp group by id%10 limit 150000 -- select * from emp group by id%20 order by 5 4.查看结果,show profiles; 5.诊断SQL,show profile cpu,block io for query 上一步前面的问题SQL 数字号码; 6.日常开发需要注意的结论 -- converting HEAP to MyISAM 查询结果太大,内存都不够用了往磁盘上搬了。 -- Creating tmp table 创建临时表 -- 拷贝数据到临时表 -- 用完再删除 -- Copying to tmp table on disk 把内存中临时表复制到磁盘,危险!!! -- locked
推荐视频:
https://www.bilibili.com/video/av49181542
配套资料:
链接:https://pan.baidu.com/s/1WMqGfH1Kf1aDuQfzHNg4cQ
提取码:edde