1 概述

在利用开发的过程中，因为后期数据量少，开发人员编写的SQL语句或者数据库整体解决方案都更器重在性能上的实现， 然而当利用零碎正式上线后，随着生成数据量的急剧增长，很多SQL语句和数据库整体计划开始逐步透出了性 能问题，对生成的影响也越来越大，此时Mysql数据库的性能问题成为零碎利用的瓶颈，因而须要进行Mysql数据库的性能优化。

1.1 性能降落的体现

执行工夫长等待时间长

1.2 性能降落的起因

查问语句写的不好，各种连贯，各种子查问导致用不上索引或者没有建设索引建设的索引生效，建设了索引,在真正执行时,没有用上建设的索引关联查问太多join服务器调优及配置参数导致，如果设置的不合理,比例不失当,也会导致性能降落,sql变慢 零碎架构的问题

1.3 通用的优化计划

索引优化: 增加适当索引(index）(重点)Sql优化: 写出高质量的sql，防止索引生效 (重点)设计优化: 表的设计合理化(合乎3NF，有时候要进行反三范式操作) 配置优化: 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]架构优化：读写拆散、分库分表硬件优化: 服务器的硬件优化

2.索引原理

2.1 概念

索引是解决SQL性能问题的重要伎俩之一，应用索引能够帮忙用户解决大多数的SQL性能问题。索引就是数据结构，通过这种数据结构能够大大提高mysql的查问效率

磁盘存取示意图

每次从磁盘中查找数据称为磁盘I/O， 而磁盘IO 至多要经验磁盘寻道、磁盘旋转、数据读取等等操作，十分影响性能，所以对于读取数据，最大的优化就是缩小磁盘I/O

2.2 索引的底层实现

MySql底层的数据结构次要是基于Hash 和 B+Tree

2.2.1 底层数据结构剖析

2.2.1.1 二叉树

为了放慢数据的查找,能够保护二叉查找树, 每个节点别离蕴含索引键值和一个指向对应数据记录的物理地址的指针,这样就能够使用二叉查找在肯定的复杂度内获取相应的数据,从而疾速的检索出符合条件 的记录

左子树的键值小于根的键值右子树的键值大于根的键值

从二叉树的查找过程了来看，最坏的状况下磁盘IO的次数由树的高度来决定。从后面剖析状况来看，缩小磁盘IO的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树，所以引出B-Tree强势退场

2.2.1.2 BTree
自均衡多叉查找树度(Degree) 节点的数据存储个数   叶节点具备雷同的深度节点中数据key从左到右递增排列叶节点的指针为空在节点中间接存储了数据 data

疑难：二叉树的节点只存了一个数据? 而BTree的节点因为有度的概念存了多个数据？那么二叉树的节点数据量小是不是在读取的时候效率更高呢？而且读到内存中的遍历速度是不是更快些呢?预读：磁盘个别会程序向后读取肯定长度的数据(页的整数倍)放入内存局部性原理：当一个数据被用到时，其左近的数据也通常会马上被应用存储器读取数据按 磁盘块读取 每个磁盘块的大小为  扇区（页）的2的N次方每个扇区的最小单位 512B 或 4096B 不同的生产厂家不同为了晋升度的长度，还须要对这种数据结构进行优化，所以它的升华版B+Tree诞生了
2.2.1.3 B+Tree

B+树是B树的变体,根本与BTree雷同

特点

非叶子节点不存储data,只存储key,能够增大度叶子节点不存储指针程序拜访指针，进步区间拜访能力

B+Tree索引的性能剖析

个别应用磁盘I/O次数评估索引构造的优劣B+Tree的度个别会超过100，因而h十分小 (个别为3到5之间)，性能就会十分稳固B+Tree叶子节点有程序指针，更容易做范畴查问
2.2.1.4 Hash

应用hash构造存储索引，查找单行数据很快，但毛病也很显著。

1.无奈用于排序2.只反对等值查找3.存在Hash抵触Hash索引只实用于某些特定场景，咱们应用不多

2.2.2 mysql的索引实现

2.2.2.1 存储引擎的概念
mysql的索引对于不同的存储引擎(Engine) ,索引实现各不相同MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都应用不同的存储机制、索引技巧、锁定程度并且最终提供宽泛的不同的性能和能力。通过抉择不同的技术，你可能取得额定的速度或者性能，从而改善你的利用的整体性能。这些不同的技术以及配套的相干性能在 MySQL中被称作存储引擎通过 show engines 能够查看以后数据库反对的引擎、默认的引擎查看以后数据库的默认引擎show VARIABLES like 'default_storage_engine'如：如果你在钻研大量的长期数据，你兴许须要应用内存存储引擎。内存存储引擎可能在内存中存储所有的表格数据。兴许须要一个反对事务处理的数据库(以确保事务处理不胜利时数据的回退能力) 抉择反对事务的存储引擎5.7 默认的引擎是innodb   可通过 SET default_storage_engine=< 存储引擎名 >更改面试时常常被问到的两个引擎：MyISAM 和 innoDB引擎  这两种引擎都是采纳B+Tree和hash 数据结构实现的索引
2.2.2.2 MyISAM 和 innoDB的比照
MyISAMinnoDB
事务不反对反对
外键不反对反对
锁表锁行锁
缓存缓存索引缓存索引和数据
全文索引反对不反对
索引实现非聚簇索引聚簇索引
总的来说：     须要事务： 那必定用innoDB     不须要事务：         myisam的查问效率高，内存要求低， 但因为采纳表锁， 不适宜并发写操作， 读多写少选它         innoDB采纳行锁，适宜解决并发写操作， 写多读少选它
2.2.2.3 MyISAM索引实现
索引特点：  非聚簇索引    采纳B+Tree 作为数据结构    MyISAM 索引文件和数据文件是拆散的(非聚簇)      叶子节点存储的是数据的磁盘地址    非主键索引和主键索引相似

2.2.2.4 InnoDB索引实现
索引特点：    采纳B+Tree 作为数据结构    数据文件自身就是索引文件  (聚簇索引)    表数据文件自身就是依照B+Tree组织的一个索引构造文件    汇集索引-叶节点蕴含了残缺的数据记录    非主键索引 的叶子节点指向主键

2.3 索引的分类

1.一般索引index :减速查找  create index idx_  on 表(字段)2.惟一索引:     主键索引：primary key ：减速查找+束缚（不为空且惟一）    惟一索引：unique：减速查找+束缚 （惟一）3.联结索引（组合索引）     最左匹配准则    where A=? and B=? and C=?     create index  A  on 表(A,B,C)    -primary key(id,name):联结主键索引    -unique(id,name):联结惟一索引    -index(id,name):联结一般索引4.全文索引fulltext :用于搜寻很长一篇文章的时候，成果最好。

2.4 索引的操作

创立索引

create [UNIQUE|primary|fulltext] index 索引名称 ON 表名(字段(长度))
CREATE INDEX emp_name_index ON employee(NAME);

测试脚本

材料中  testemployee.sql文件

没有应用索引查问的工夫如下：

select cus_id from testemployee where cus_id=5   # 工夫: 0.311ms

创立索引后查问的工夫如下：

-- 为num创立一个索引create index idx_cusid on testemployee(cusid)-- 再次查问耗时select cus_id from testemployee where cus_id=5 # 工夫: 0.041ms

查看索引

show index from 表名

删除索引

drop index[索引名称] on 表名DROP INDEX emp_name_index ON employee;

更改索引

alter table tab_name add primary key(column_list)-- 增加一个主键,索引必须是惟一索引,不能为NULLalter table tab_name add unque index_name(column_list)-- 创立的索引是惟一索引,能够为NULLalter table tab_name add index index_name(column_list)-- 一般索引,索引值可呈现屡次alter table tab_name add fulltext index_name(column_list)-- 全文索引

2.5 联结索引 (最左匹配准则)

联结索引：当咱们的where条件中 常常存在多个条件查问的时候，咱们能够为这多个列创立组合索引如：一张员工表，咱们常常会用 工号、名称、入职日期 作为条件查问select * from 员工表 where 工号=10002 and 名称=Staff and 入职日期='2001-09-03'那么咱们能够思考 将(工号、名称、入职日期)创立为一个组合索引疑难: 那为什么咱们不把 这三个字段都独自列一个索引呢？答： 次要是效率问题，对工号、名称、入职日期三列别离创立索引，MySQL只会抉择辨识度高的一列作为索引。假如有100w的数据，一个索引筛选出10%的数据，那么能够筛选出10w的数据；对于组合索引而言： 如果将(工号、名称、入职日期)创立为一个组合索引，那么三个字段的筛选将都会应用上，先按工号排查、工号匹配完在按名称筛选、名称筛选完再按日期筛选，那么筛选的数据就是 100w*10%*10%*10%筛选出1000条数据。最左准则：    （工号、名称、入职日期) 作为一个组合索引，将会生成下图的索引目录构造。        由接口能够看出，  工号是最先须要判断的字段，所以工号这个查问条件必须存在        工号判断完，才会判断名称        名称判断完才会判断入职日期也就是说，组合索引查问条件必须得带有最右边的列：    对于咱们的索引：    条件为： （工号）   （工号，名称）   （工号，名称，入职日期） 这几种状况都是失效的    条件为： （名称）不失效    （名称，入职日期）不失效  （工号，入职日期）局部失效

2.6 索引的优劣势

2.6.1 劣势

1.能够通过建设惟一索引或者主键索引,保障数据库表中每一行数据的唯一性.2.建设索引能够大大提高检索的数据,以及缩小表的检索行数3.在表连贯的连贯条件 能够减速表与表间接的相连4.在分组和排序字句进行数据检索,能够缩小查问工夫中 分组 和 排序时所耗费的工夫(数据库的记录会从新排序)5.建设索引,在查问中应用索引 能够进步性能

2.6.2 劣势

1.索引文件会占用物理空间,除了数据表须要占用物理空间之外,每一个索引还会占用肯定的物理空间2.在创立索引和保护索引 会消耗工夫,随着数据量的减少而减少3.当对表的数据进行INSERT,UPDATE,DELETE 的时候,索引也要动静的保护,这样就会升高数据的保护速度

2.7 索引的抉择

适宜建设索引

    1.主键主动建设惟一索引:primary          2.频繁作为查问条件的字段应该创立索引         where name =           3.查问中与其它表关联的字段,外键关系建设索引        dept  id      employ  dep_id              4.查问中排序的字段,排序的字段若通过索引去拜访将大大晋升排序速度             order by age            5.查问中统计或分组的字段        group by age

不适宜建设索引

    1.记录比拟少        2.where条件里用不到的字段不建设索引        3.常常增删改的表            索引进步了查问的速度，同时却会升高更新表的速度,因为建设索引后, 如果对表进行INSERT,UPDATE 和    DELETE, MYSQL不仅要保留数据,还要保留一下索引文件    4.数据反复的表字段        如果某个数据列蕴含了许多反复的内容,为它建设索引  就没有太大在的实际效果，比方表中的某一个字段为国籍,性别，数据的差别率不高,这种建设索引就没有太多意义。

面试思考:

什么是索引？索引为什么可能晋升查问效率？mysql的索引是基于什么数据结构实现的？为什么抉择这种数据结构?聚簇索引和非聚簇索引有什么区别索引的分类索引的优劣索引的抉择    组合索引的最左准则-- 建设复合索引(a,b,c),请说出下列条件对于索引的应用状况select * from table where a=4 aselect * from table where a=4 and b=6a bselect * from table where a=4 and c=5 and b=6 a b cselect * from table where b=4 or b=5不失效 select * from table where a=4 and c=6aselect * from table where a=4 and c=6 and b>5a b select * from table where a=4 and b like '%test' and c=6a

3.sql性能优化

3.1 性能剖析

3.1.1 慢查问日志

mysql的慢查问日志是mysql提供的一种日志记录，它用来记录在mysql中响应工夫超过阀值的语句,mysql 的日志是跟踪mysql性能瓶颈的最快和最间接的形式了，零碎性能呈现瓶颈的时候，首先要关上慢查问日志，进行跟踪，尽快的剖析和排查出执行效率较慢的SQL ,及时解决防止造成不好的影响。**作用**： 记录具体执行效率较低的SQL语句的日志信息。留神：在默认状况下mysql的慢查问日志记录是敞开的。同时慢查问日志默认不记录治理语句和不应用索引进行查问的语句

查看是否开启慢查问日志

show variables like '%slow_query_log%'开启set global slow_query_log=1;只对以后数据库失效,如果重启后，则会生效如果想永恒失效，必须批改配置文件slow_query_log=1slow_query_log_file=地址

设置慢查问的阀值

show variables like 'long_query_time'set global long_query_time=4;

要断开连接后, 能力失效

show global variables like 'long_query_time';select sleep(4)show global status like '%slow_queries%';

慢查问日志剖析

次要性能是, 统计不同慢sql的呈现次数(Count), 执行最长工夫(Time), 累计总消耗工夫(Time), 期待锁的工夫(Lock), 发送给客户端的行总数(Rows), 扫描的行总数(Rows), 用户以及sql语句自身(形象了一下格局, 比方 limit 1, 20 用 limit N,N 示意).

第三方的慢查问日志剖析工具：mysqlsla，myprofi，pt-query-diges等等

3.1.2 Explain (执行打算)

3.1.2.1 概念及作用
应用explain关键字,能够模仿优化器执行的SQL语句从而晓得MYSQL是如何解决sql语句的通过Explain能够剖析查问语句或表构造的性能瓶颈具体作用：    查看表的读取程序    数据读取操作的操作类型    查看哪些索引能够应用    查看哪些索引被理论应用    查看表之间的援用    查看每张表有多少行被优化器执行
3.1.2.2 应用办法
应用Explain关键字 放到sql语句前explain select cus_id from testemployee where cus_id > 10

3.1.2.3 参数详解

id（重要）

 select查问的序列号，蕴含一组数字,示意查问中执行select子句或操作表的程序     值分为三种状况**id值雷同**    执行程序由上到下**id不同**    如果是子查问,id的序号会递增,id值越大优先级越高,优先被执行**id雷同不同,同时存在**    能够认为是一组,从上往下程序执行    在所有组中,id值越大,优先级越高,越先执行    
-- id值雷同EXPLAIN SELECT * from employee e,department d,customer c where e.dep_id = d.id and e.cus_id = c.id;

-- id值不同    EXPLAIN SELECT * from department WHERE id = (SELECT id from employee WHERE id=(SELECT id from customer WHERE id = 1))

-- id值雷同 不同都存在 deriverd 衍生进去的虚表EXPLAIN select * from department d, (select * from employee group by dep_id) t where d.id = t.dep_id;

select_type

查问类型,次要用于区别一般查问,联结查问,子查问等简单查问后果值    SIMPLE   简略select查问,查问中不蕴含子查问或者UNION    PRIMARY  查问中若蕴含任何简单的子查问,最外层查问则被标记为primary    SUBQUERY 在select或where中蕴含了子查问    DERIVED  在from列表中蕴含的子查问被标记为derived(衍生)把后果放在长期表当        UNION    若第二个select呈现的union之后,则被标记为union             若union蕴含在from子句的子查问中,外层select将被标记为deriver    UNION RESULT 从union表获取后果select,两个UNION合并的后果集在最初
-- union 和 union result 示例EXPLAIN select * from employee e LEFT JOIN department d on e.dep_id = d.idUNION select * from employee e RIGHT JOIN department D ON e.dep_id = d.id

table

显示这一行的数据是对于哪张表的

partitions

如果查问是基于分区表的话, 会显示查问拜访的分区

type (重要)

 拜访类型排列    后果值:(最好到最差) system > const > eq_ref > ref > range > index > ALL        
-- system 表中有一行记录(零碎表)  这是const类型的特例,平时不会呈现explain select HOST from mysql.db where HOST='localhost'

-- const  示意通过索引一次就找到了，const用于比拟primary 或者 unique索引.   间接查问主键或者惟一索引，因为只匹配一行数据,所以很快EXPLAIN select id from employee where id=1

-- eq_ref 唯一性索引扫描 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或惟一索引扫描EXPLAIN select * from employee e,department d where e.id=d.id

-- ref 非唯一性索引扫描,返回匹配某个独自值的所有行,实质上也是一种索引拜访,它返回所有匹配某个独自值的行可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体EXPLAIN select e.id,e.dep_id,d.id from employee e,department d where e.dep_id = d.id

-- range 只检索给定范畴的行,应用一个索引来抉择行 个别就是在你的where语句中呈现between\<\>\ in等查问,这种范畴扫描索引比全表扫描要好,因为它只须要开始于索引的某一点.而结束语另一点,不必扫描全副索引explain select * from employee where id>2

-- index index与All区别为index类型只遍历索引树,通常比All要快,因为索引文件通常比数据文件要小all和index都是读全表,但index是从索引中读取,all是从硬盘当中读取explain select id from employee

-- ALL 将全表进行扫描,从硬盘当中读取数据,如果呈现了All 切数据量十分大, 肯定要去做优化explain select * from employee

要求:    一般来说,保障查问至多达到range级别    最好能达到ref

possible_keys

显示可能利用在这张表中的索引,一个或者多个查问波及到的字段上若存在索引,则该索引将被列出,但不肯定被查问理论应用可能本人创立了4个索引,在执行的时候,可能依据外部的主动判断,只应用了3个
--  可能不会用到索引，理论用到索引explain select dep_id from employee

-- 可能会应用索引，理论没用到索引EXPLAIN select * from employee e,department d where e.dep_id = d.id

key （重要）

理论应用的索引,如果为NULL,则没有应用索引,查问中若应用了笼罩索引 ,则该索引仅呈现在key列表possible_keys与key关系,实践应该用到哪些索引  理论用到了哪些索引笼罩索引 查问的字段和建设的字段刚好吻合,这种咱们称为笼罩索引

key_len

-- 示意索引中应用的字节数,可通过该列计算查问中应用的索引长度 .explain select * from employee where dep_id=1 and name='鲁班' and age=10

ref

索引是否被引入到, 到底援用到了哪几个索引
Explain select * from employee e,department d where e.dep_id = d.id and e.cus_id = 1

Explain select e.dep_id from employee e,department d,customer c where e.dep_id = d.id and e.cus_id = c.id and e.name='鲁班'

rows

依据表统计信息及索引选用状况,大抵估算出找到所需的记录所须要读取的行数,每长表有多少行被优化器查问过

filtered

-- 满足查问的记录数量的比例，留神是百分比，不是具体记录数 . 值越大越好，filtered列的值依赖统计信息，并不非常精确Explain select e.dep_id from employee e,department d where e.dep_id = d.id

Extra （重要）

留神：语句中呈现了Using Filesort 和 Using Temporary阐明没有应用到索引     呈现 impossible where阐明条件永远不成立

产生的值:

    /*     Using filesort (须要优化)        阐明mysql会对数据应用一个内部的索引排序,        而不是依照表内的索引程序进行        Mysql中无奈利用索引实现排序操作称为"文件排序"    */        explain select * from employee where dep_id =1 ORDER BY cus_id

    /*     Using temporary (须要优化)        应用了长期表保留两头后果,Mysql在对查问后果排序时, 应用了长期表,        常见于排序orderby 和分组查问group by    */    explain select name from employee where dep_id in (1,2,3) GROUP BY cus_id

    /*    impossible where (须要优化)        where 子句的值总是false 不能用来获取任何元组    */        explain select name from employee where name='鲁班' and name='zs'
    use index        示意相应的select中应用了笼罩索引,防止拜访了表的数据行, 效率很好        如果同时呈现using where  表明索引被用来执行索引键值的查找        如果没有同时呈现using where 表明索引 用来读取数据而非执行查找动作        示例    using where        表明应用了where过滤    using join buffer        应用了连贯缓存

3.2 优化实战

3.2.1 防止索引生效

-- 最左匹配准则 * -- 范畴条件左边的索引生效-- 不再索引列上做任何操作 * -- 应用不等于(!=或者<>)索引生效-- is not null无奈应用索引-- like以通配符结尾(%qw)索引生效 * -- 字符串不加引号索引生效-- 应用or连贯索引生效-- 尽量应用笼罩索引
3.2.1.1 全值匹配
-- 创立组合索引create index idx_name_dep_age on employee(name,dep_id,age)-- 索引字段全副应用上explain select * from employee where name='鲁班' and dep_id=1 and age=10
3.2.1.2 最左匹配准则
-- 去掉name条件 索引全副生效explain select * from employee where dep_id=1 and age=10
-- 去掉dep_id   name索引失效explain select * from employee where name='鲁班' and age=10
-- 程序错乱不会影响最左匹配explain select * from employee where dep_id=1 and  age=10 and name='鲁班'
3.2.1.3 不再索引列上做任何操作
-- 在name字段上 加上去除空格的函数 索引生效explain select * from employee where TRIM(name)='鲁班' and dep_id=1 and age=10
3.2.1.4 范畴条件左边的索引生效
-- 范畴查找 会造成该组合索引字段的右侧索引全副生效explain select * from employee where  name = '鲁班' and dep_id>1 and age=10
3.2.1.5 mysql在应用不等于(!=或者<>)索引生效
explain select * from employee where age != 10
3.2.1.6 is not null无奈应用索引
explain select * from employee where name is not NULL
3.2.1.7 like以通配符结尾(%qw)索引生效
explain select * from employee where name like '%鲁'
3.2.1.8 字符串不加引号索引生效
explain select * from employee where name = 200
3.2.1.9 应用or连贯索引生效
explain select * from employee where name = '鲁班' or age>10
3.2.1.10 尽量应用笼罩索引
explain select * from employee where name = '鲁班' or age>10-- 笼罩索引: 要查问的字段全副是索引字段-- 下面状况会触发全表扫描，不过若应用了笼罩索引，则会只扫描索引文件explain select name,dep_id,age from employee where name = '鲁班' or age>10

3.2.2 排序与分组优化

3.2.2.1 应用order by呈现Using filesort
-- 如果select * 语句未应用到索引，会呈现 filesort 可应用笼罩索引解决 或 主键索引-- 组合索引不满足最左准则 会呈现 filesort-- 组合索引程序不统一(order by的前面) 会呈现 filesort-- 当索引呈现范畴查找时 可能会呈现 filesort-- 排序应用一升一降会造成filesort
-- 没有应用索引排序，服务器须要额定的为数据进行排序的解决-- 如果select语句未应用到索引，会呈现 filesortexplain select * from employee  order by name,dep_id,age-- 组合索引不满足最左准则 会呈现 filesortexplain select * from employee where name='鲁班' order by dep_id,ageexplain select * from employee  order by dep_id,age  -- 组合索引程序不统一(order by的前面) 会呈现 filesortexplain select * from employee where name='鲁班' order by dep_id,age explain select * from employee where name='鲁班' order by age,dep_id-- 当索引呈现范畴查找时 可能会呈现 filesortexplain select * from employee where name='鲁班' and dep_id>1 order by age -- 排序应用一升一降会造成filesortexplain select * from employee where name='鲁班' order by dep_id desc,age
3.2.2.2 应用group by呈现Using temporary
-- 同order by状况相似， 分组必然触发排序-- 组合索引不满足最左准则 会呈现 filesort-- 组合索引程序不统一(order by的前面) 会呈现 filesort-- 当索引呈现范畴查找时 可能会呈现 filesort
3.2.2.3 面试题
-- 建设复合索引(a,b,c),请说出下列条件对于索引的应用状况select * from table where a=4 select * from table where a=4 and b=6select * from table where a=4 and c=5 and b=6 select * from table where b=4 or b=5select * from table where a=4 and c=6select * from table where a=4  and c=6 and b>5select * from table where a=4 and b like '%test' and c=6

3.2.3 大数据量分页优化

-- 分页是咱们常常应用的性能，在数据量少时单纯的应用limit m,n 不会感觉到性能的影响-- 但咱们的数据达到成千盈百万时 ， 就会显著查问速度越来越低
-- 应用存储过程导入数据-- 查看是否开启函数性能show variables like 'log_bin_trust_function_creators';-- 设置开启函数性能set global log_bin_trust_function_creators=1;-- 创立函数用于生成随机字符串delimiter $$ create function rand_string(n int) returns varchar(255) begin   declare chars_str varchar(100) default 'qwertyuiopasdfghjklzxcvbnm';   declare return_str varchar(255) default '';   declare i int default 0;   while i<n do   set return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));   set i=i+1;   end while;   return return_str; end $$-- 创立存储过程用于插入数据delimiter $$create procedure insert_emp(in start int(10),in max_num int(10))begin declare i int default 0; /*把autocommit设置成0*/ set autocommit= 0; repeat set i=i+1; insert into testemployee(name,dep_id,age,salary,cus_id) values(rand_string(6),'2',24,3000,6); until i=max_num end repeat;commit;end $$-- 调用存储过程插入数据call insert_emp(1,1000000);
-- 测试一下分页数据的相应工夫-- limit 0,20 工夫: 0.001msselect * from testemployee limit 0,20-- limit 10000,20 工夫: 0.004msselect * from testemployee limit 10000,20-- limit 100000,20 工夫: 0.044msselect * from testemployee limit 100000,20-- limit 1000000,20 工夫:  0.370msselect * from testemployee limit 1000000,20-- limit 3000000,20 工夫: 1.068msselect * from testemployee limit 3000000,20
子查问优化
-- 子查问优化-- 通过Explain发现，之前咱们没有利用到索引，这次咱们利用索引查问出对应的所有ID-- 在通过关联查问，查问出对应的全副数据，性能有了显著晋升-- limit 3000000,20 工夫: 1.068ms -> 工夫: 0.742msselect * from testemployee e,(select id from testemployee limit 3000000,20) tmp where e.id=tmp.id-- 自增ID也能够用如下形式select * from testemployee where id> (select id from testemployee t limit 3000000,1) LIMIT 10
应用id限定计划
-- 应用id限定计划，将上一页的ID传递过去 依据id范畴进行分页查问-- 通过程序的设计，继续保留上一页的ID，并且ID保障自增-- 工夫: 0.010ms   select * from testemployee where id>3000109 limit 20-- 尽管应用条件有些刻薄 但效率十分高，能够和计划一组合应用 ，跳转某页应用计划一 下一页应用计划2

3.2.4 小表驱动大表

3.2.4.1 表关联查问
explain select e.id from employee e,department d where e.dep_id=d.id MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的后果集作为循环根底数据，而后一条一条地通过该后果集中的数据作为过滤条件到下一个表中查问数据，而后合并后果。如果小的循环在外层，对于数据库连贯来说就只连贯5次，进行5000次操作，如果1000在外，则须要进行1000次数据库连贯，从而浪费资源，减少耗费。这就是为什么要小表驱动大表。
总结：多表查问中，肯定要让小表驱动大表create index idx_dep_id on testemployee(dep_id)explain select e.id from testemployee e LEFT JOIN department d on e.dep_id=d.idexplain select e.id from testemployee e RIGHT JOIN department d on e.dep_id=d.id
3.2.4.2 in和exits查问

应用in 时的explain执行打算 d的数据先被查问进去, 依据d的后果集循环查问a表数据

-- 应用in 工夫: 3.292ms       A                                     B                                       select * from employee where dep_id in (select id from department)应用department表中数据作为外层循环 10次for( select id from department d)    每次循环执行employee表中的查问    for( select * from employee e where e.dep_id=d.id)

应用exits时的explain执行打算 尽管d的查问优先级高，然而当select_type为DEPENDENT_SUBQUERY时，代表以后子查问依赖内部查问，所以能够考到 e表先进行查问

-- 应用exits 工夫: 14.771ms       A                                      Bselect * from employee e where exists (select 1 from department d where d.id = e.dep_id)应用employee表中数据作为外层循环 3000000万次for(select * from employee e)    每次循环执行department表中的查问    for( select 1 from department d where d.id = e.dep_id)
总结：    当A表数据多于B表中的数据时，这是咱们应用in优于Exists    当B表数据多于A表中的数据时,这时咱们应用Exists优于in    如果数据量差不多，那么它们的执行性能差不多    Exists子查问只返回true或false,因而子查问中的select * 能够是select 1或其它

3.2.5 max函数优化

-- 给max函数中的字段增加索引select max(age) from testemployee
索引优化     索引是什么    数据结构     聚簇非聚簇的概念和区别    索引的分类    索引的优缺点 where         索引的抉择     最左匹配准则 sql优化    如何找到慢sql   :  慢查问日志    如何剖析慢sql   :  explain   type  all  range  index             key            extra : using filesort  using temporary     ()  extra  filesort  tomporary     罕用优化伎俩   :   1. 防止索引生效     ==> 什么状况造成索引生效                     2. 排序和分组的优化  ==> extra                       3. 小表驱动大表   ==>  大表 条件 in(小表)                           小表   exists (大表)

4.面试总结

什么是索引

数据库索引的实质是数据结构，这种数据结构可能帮忙咱们疾速的获取数据库中的数据。

索引的作用

当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查问性能优化最无效的伎俩了。索引可能轻易将查问性能进步好几个数量级。有了索引相当于咱们给数据库的数据加了目录一样，能够疾速的找到数据，如果不实用索引则须要一点一点去查找数据简略来说进步数据查问的效率。

索引的分类

- 1.一般索引index :减速查找- 2.惟一索引- 3.联结索引（组合索引）- 4.全文索引fulltext :用于搜寻很长一篇文章的时候，成果最好。

索引原理

索引的实现实质上是为了让数据库可能疾速查找数据，而独自保护的数据结构，mysql实现索引次要应用的两种数据结构：hash和B+树： 咱们比拟罕用的 MyIsam 和 innoDB引擎都是基于B+树的。hash:（hash索引在mysql比拟少用）他以把数据的索引以hash模式组织起来,因而当查找某一条记录的时候,速度十分快.过后因为是hash构造,每个键只对应一个值,而且是散列的形式散布.所以他并不反对范畴查找和排序等性能.B+树:b+tree是(mysql应用最频繁的一个索引数据结构)数据结构以均衡树的模式来组织,因为是树型构造,所以更适宜用来解决排序,范畴查找等性能.绝对hash索引,B+树在查找单条记录的速度尽管比不上hash索引,然而因为更适宜排序等操作,所以他更受用户的欢送.毕竟不可能只对数据库进行单条记录的操作.

索引的长处

1.能够通过建设惟一索引或者主键索引,保障数据库表中每一行数据的唯一性.2.建设索引能够大大提高检索的数据,以及缩小表的检索行数3.在表连贯的连贯条件 能够减速表与表间接的相连4.在分组和排序字句进行数据检索,能够缩小查问工夫中 分组 和 排序时所耗费的工夫(数据库的记录会从新排序)5.建设索引,在查问中应用索引 能够进步性能

索引的毛病

1.在创立索引和保护索引 会消耗工夫,随着数据量的减少而减少2.索引文件会占用物理空间,除了数据表须要占用物理空间之外,每一个索引还会占用肯定的物理空间3.当对表的数据进行INSERT,UPDATE,DELETE 的时候,索引也要动静的保护,这样就会升高数据的保护速度,(建设索引会占用磁盘空间的索引文件。个别状况这个问题不太重大，但如果你在一个大表上创立了多种组合索引，索引文件的会收缩很快)。

索引操作

对索引的简略增删改查语句要记得查看表中索引show index from tableName;创立索引CREATE INDEX 索引名 ON 表名 列名;删除索引DORP INDEX IndexName ON TableName剖析索引应用状况explain select 语句

剖析索引应用状况

explain显示了MySQL如何应用索引来解决select语句以及连贯表。能够帮忙抉择更好的索引和写出更优化的查问语句。简略讲，它的作用就是剖析查问性能。explain关键字的应用办法很简略，就是把它放在select查问语句的后面。mysql查看是否应用索引，简略的看type类型就能够。如果它是all，那阐明这条查问语句遍历了所有的行，并没有应用到索引。 (最简略的说法，心愿能说具体些)

哪些字段适宜加索引

1.在常常须要搜寻的列上,能够放慢索引的速度2.主键列上能够确保列的唯一性3.在表与表的而连贯条件上加上索引,能够放慢连贯查问的速度4.在常常须要排序(order by),分组(group by)和的distinct 列上加索引 能够放慢排序查问的工夫,

哪些字段不适宜加索引

1.查问中很少应用到的列 不应该创立索引,如果建设了索引然而还会升高mysql的性能和增大了空间需要.2.很少数据的列也不应该建设索引,比方 一个性别字段 0或者1,在查问中,后果集的数据占了表中数据行的比例比拟大,mysql须要扫描的行数很多,减少索引,并不能提高效率3.定义为text和image和bit数据类型的列不应该减少索引,4.当表的批改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创立索引,这两个操作是互斥的关系。

哪些状况会造成索引生效

1.如果条件中有or，即便其中有条件带索引也不会应用(这也是为什么尽量少用or的起因)2.索引字段的值不能有null值，有null值会使该列索引生效3.对于多列索引，不是应用的第一局部，则不会应用索引（最左准则）4.like查问以%结尾5.如果列类型是字符串，那肯定要在条件中将数据应用单引号援用起来,否则不应用索引6.在索引的列上应用表达式或者函数会使索引生效

联结索引最左准则

在mysql建设联结索引时会遵循最左前缀匹配的准则，即最左优先，在检索数据时从联结索引的最右边开始匹配，组合索引的第一个字段必须呈现在查问组句中，这个索引才会被用到如创立组合索引 a,b,c     那么查问条件中单纯的应用  b 和 c是应用不到索引的

聚簇索引和非聚簇索引

MyISAM——非聚簇索引MyISAM存储引擎采纳的是非聚簇索引，非聚簇索引的主索引和辅助索引简直是一样的，只是主索引不容许反复，不容许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。非聚簇索引的数据表和索引表是离开存储的。InnoDB——聚簇索引聚簇索引的主索引的叶子结点存储的是键值对应的数据自身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因而主键的值长度越小越好，类型越简略越好。聚簇索引的数据和主键索引存储在一起。

事务的基本要素（ACID）

（1）原子性：整个事务中的所有操作，要么全副实现，要么全副不实现，不可能停滞在两头某个环节。事务在执行过程中产生谬误，会被回滚到事务开始前的状态，就像这个事务素来没产生过一样。例如：A账户中有1000元，B账户中有1000元。A要给B转账500元。A扣款和B加款这两条要么同时执行，要么同时不执行。如果在A扣款后B加款之前，零碎产生故障，会回滚到A扣款之前的状态。（2）一致性：事务开始之前和事务完结后，数据库的完整性束缚没有被毁坏。例如：不管汇款成败，A账户B账户总额是2000元。（3）隔离性：事务的执行互不烦扰。（4）持久性：事务执行胜利后，该事务对数据库的更改是长久保留在数据库中的，不会被回滚。能够应用日志记录或影子副原本实现。

什么是事务？

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组如果任何一个语句操作失败那么整个操作就被失败，当前操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就能够应用事务。要将有组语句作为事务思考，就须要通过ACID测试：即原子性，一致性，隔离性和持久性。- 锁：锁是实现事务的要害，锁能够保障事务的完整性和并发性。 与现实生活中锁一样，它能够使某些数据的拥有者，在某段时间内不能应用某些数据或数据结构。老李 给 老王汇钱老李把钱 ==》 银行 老李账户扣钱银行 ==》 老王 胜利 老王账户加钱不胜利 老账户补钱（银行将钱返给老李）

事务的并发问题

1、脏读：事务A读取了事务B更新的数据，而后B回滚操作，那么A读取到的数据是脏数据2、不可反复读：事务 A 屡次读取同一数据，事务 B 在事务A屡次读取的过程中，对数据作了更新并提交，导致事务A屡次读取同一数据时，后果 不统一。3、幻读：系统管理员A将数据库中所有学生的问题从具体分数改为ABCDE等级，然而系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改完结后发现还有一条记录没有改过来，就如同产生了幻觉一样，这就叫幻读。

事务隔离性的作用

就是保证数据的一致性、完整性。事务隔离级别越高，在并发下会产生的问题就越少，但同时付出的性能耗费也将越大，因而很多时候必须在并发性和性能之间做一个衡量。所以设立了几种事务隔离级别，以便让不同的我的项目能够依据本人我的项目的并发状况抉择适合的事务隔离级别，对于在事务隔离级别之外会产生的并发问题，在代码中做弥补。

事务的隔离级别4个

事务隔离级别读未提交读已提交可反复读串行化
脏读是否否否
------------------------
不可反复读是是否否
------------------------
幻读是是是否

mysql中锁的分类

按操作分    读锁(共享锁)        加了读锁,   其余的过程也能够进行读操作，但写操作会阻塞，所以称为共享锁    写锁(排它锁)        加了写锁，  其余的过程读操作和写操作都会进入阻塞状态    按粒度分    表锁        加锁特点：开销小、加锁快，不会呈现死锁；锁粒度大，锁抵触高，并发低        加锁形式：            lock table tableName read; //读锁            lock table tableName write; //写锁        解锁形式:            unlock tables;//开释全副锁    行锁        开销大，加锁慢；会呈现死锁；锁定粒度最小，产生锁抵触的概率最低，并发度也最高。         加锁形式：            select * from table where id=1 lock in share mode; //读锁            select * from table where id=1 for update; //写锁                    解锁形式:            commit; //提交事务即解锁    页锁        介于下面两个之间，不必特意论述从思维的层面:    乐观锁：    对待事件比拟乐观， 认为他人会批改它的数据，须要上锁来保证数据的平安    select * from employee where id = 1 for update    update --             乐观锁：    对待事件比拟乐观，         id   name  salary version     1     老王   500     2       客户端1      select * from employee where id = 1    版本 = 1      update employee set salary=2000,version=version+1 where id=1 and version = 1         客户端2      select * from employee where id = 1    版本 = 1   // 批改时1.肯定要给版本号+1    2.条件中肯定要有版本条件    update employee set salary=500,version=version+1 where id=1 and version = 1         读操作多 选乐观锁   写操作多 选乐观锁

mysql中的几种连贯查问

内连贯：只有两个元素表相匹配的能力在后果集中显示。    inner join        select * from 表1,表2 where 表1.id = 表2.dep_id    select * from 表1 inner join 表2 on 表1.id = 表2.dep_id外连贯：    左外连贯:右边为驱动表，驱动表的数据全副显示，匹配表的不匹配的不会显示。        left join    右外连贯:左边为驱动表，驱动表的数据全副显示，匹配表的不匹配的不会显示。        right join    全外连贯：连贯的表中不匹配的数据全副会显示进去。         full join

sql的书写程序和执行程序

-- 编写程序select  distinct  查问字段from  表名JOIN 表名ON  连贯条件where 查问条件group by 分组字段having 分组后条件order by  排序条件limit 查问起始地位, 查问条数-- 执行程序from  表名ON  连贯条件JOIN 表名where 查问条件group by 分组字段having 分组后条件select  distinct  查问字段order by  排序条件limit 查问起始地位, 查问条数

mysql优化综合性

1.表的设计优化抉择表适合存储引擎：myisam: 利用时以读和插入操作为主，只有大量的更新和删除，并且对事务的完整性，并发性要求不是很高的。Innodb： 事务处理，以及并发条件下要求数据的一致性。除了插入和查问外，包含很多的更新和删除。尽量 设计 所有字段都得有默认值,尽量避免null。数据库表设计时候更小的占磁盘空间尽可能应用更小的整数类型.然而一般说来，数据库中的表越小，在它下面执行的查问也就会越快。tinyint   int   bigint因而，在创立表的时候，为了取得更好的性能，咱们能够将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库减少了不必要的空间， CHAR (255)            VARCHAR (255)    10   定长                   可变长度    100101甚至应用VARCHAR这种类型也是多余的，因为CHAR(6)就能够很好的实现工作了。同样的，如果能够的话，咱们应该应用TINYINT而不是BIGINT来定义整型字段。应该尽量把字段设置为NOT NULL，这样在未来执行查问的时候，数据库不必去比拟NULL值。对于某些文本字段，例如“省份”或者“性别”，咱们能够将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来解决， 而数值型数据被解决起来的速度要比文本类型快得多。这样，咱们又能够进步数据库的性能。2.索引优化表的主键、外键必须有索引；数据量大的表应该有索引；常常与其余表进行连贯的表，在连贯字段上应该建设索引；经常出现在Where子句中的字段，特地是大表的字段，应该建设索引；索引应该建在选择性高的字段上； （sex 性别这种就不适宜）索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；频繁进行数据操作的表，不要建设太多的索引；删除无用的索引，防止对执行打算造成负面影响；表上建设的每个索引都会减少存储开销，索引对于插入、删除、更新操作也会减少解决上的开销。另外，过多的复合索引，在有单字段索引的状况下，个别都是没有存在价值的；相同，还会升高数据减少删除时的性能，特地是对频繁更新的表来说，负面影响更大。3.sql语句优化SELECT语句务必指明字段名称（防止间接应用select * ）SQL语句要防止造成索引生效的写法SQL语句中IN蕴含的值不应过多当只须要一条数据的时候，应用limit 1如果排序字段没有用到索引，就尽量少排序如果限度条件中其余字段没有索引，尽量少用or尽量用union all代替union防止在where子句中对字段进行null值判断不倡议应用%前缀含糊查问防止在where子句中对字段进行表达式操作Join优化 能用innerjoin 就不必left join right join，如必须应用 肯定要已小表为驱动4.缓存优化 (数据库本身缓存  redis缓存 等等 )为了进步查问速度，咱们能够通过不同的形式去缓存咱们的后果从而进步响应效率。数据库自身也是反对缓存的 --> 查问缓存query_cache , 默认查问缓存是敞开的须要咱们在mysql.ini 配置文件中开启:开启办法： query_cache_type=0   #敞开查问缓存query_cache_type=1   #开启查问缓存,mysql主动帮咱们缓存满足条件的查问数据query_cache_type=2   #开启查问缓存,须要在参数中手动指定要缓存的查问不过因为咱们的课程体系次要解说的是redis,所以在这里能够引入redis的知识点。5.主从复制、读写拆散如果数据库的应用场景读的操作比拟的时候，为了防止写的操作所造成的性能影响 能够采纳读写拆散的架构，读写拆散，解决的是，数据库的写入，影响了查问的效率。读写拆散的基本原理是让主数据库解决事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库解决SELECT查问操作。 数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。参考: https://www.jianshu.com/p/faf0127f1cb26.mysql的分库分表   数据量越来越大时，单体数据库无奈满足要求，能够思考分库分表两种拆分计划：垂直拆分：（分库）业务表太多？ 将业务细化 不同的小业务专门用一个库来保护程度拆分：（分表）单个表存的数据太多，装不下了？ 将该表查分成多个分库分表常常应用的数据库中间件：MyCathttps://www.cnblogs.com/chongaizhen/p/11083226.htmlSharding-JDBChttps://blog.csdn.net/forezp/article/details/94343671