MySQL优化
1、索引优化
索引本身就很大,索引往往以文件的形式存储在磁盘上
(1)性能下降的原因
1)查询语句写的烂
2)索引失效
3)关联查询太多的join 7种join
4)服务器调优设置(缓冲、线程等)
(2)概念:索引就是帮助MySQL高效的获取数据的数据结构
(3)优势:降低数据库的IO成本,通过索引列数据排序,降低数据排序的成本,降低了CPU的消耗
(4)劣势:索引也需要占据内存空间,虽然提高了查询速度,
但是同时降低了更新表的速度(更新表时也需要更新索引数据,
如果MySQL中有大量的表,就需要花费时间建立更加优秀的索引,或查询优化)
(5)分类:聚焦、次要、覆盖、复合、前缀、唯一索引、hash索引
(6)什么情况下需要创建索引
1) 主键自动建立唯一索引
2) 频繁作为查询条件的字段应该创建索引
3) 查询中与其他字段关联的字段,外键关系建立索引
4) 频繁更新的字段不适合创建索引
5) 因为每次更新不单单是更新了记录还会更新索引
6) Where条件里用不到的字段不创建索引
7) 单键/组合索引的选择问题,who?(在并发下倾向创建组合索引)
8) 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
9) 查询中统计或者分组字段
10) 那些情况不需要创建索引
11) 表记录太少
12) 经常增删改的表
13) 数据重复且分布平均的字段,因此应该只为最经常查询和最经常排序的数据列建立索引
(7)哪些情况不需要创建索引
1)表记录太少的时候()
2)经常增删改的表(更新表的时候,索引需要占据大量的存储空间)
3)数据重复且平均分配的字段(已经很平均了,建立索引就是画蛇添足)
(8)常见的瓶颈
1)CPU:CPU在饱和的时候,数据在从装入到内存或者从磁盘上读取数据的时候
2)IO:磁盘IO流远大于内存容量
3)服务器硬件性能
(9)使用explain查看SQL语句的状态记性优化
1)表的读取顺序
2)数据的读取操作的操作类型
3)哪些索引可以使用
4)哪些索引被实际使用
5)表之间的引用
6)每张表有多少行被优化器查询
(10)Mysql常见瓶颈
1)CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
2)IO:磁盘IO瓶颈发生在装入数据远大于内存容量的时候
3)服务器硬件的性能瓶颈:top、free、iostat和vmstat来查看系统的性能状态
2、查询截取分析(explain)
级别:System>const>eq_ref>ref>ragne>index>all
(1)查询优化
1)小表驱动大表
2)优先使用exists,最后使用in
3)order by排序时,尽量使用index排序,少使用fileSort排序
4)如果使用fileSort排序,可以使用单路排序(避免使用双路排序)
单路排序是把随机IO流,变成顺序IO流
双路排序是两次扫描磁盘,有大量的IO流
但是单路排序有问题,如果把所有的字段都取出,取出的数据有可能会超出buffer的容量,
每次只能处理buffer中的数据,进行排序,同时创建tmp临时文件,在进行合并排序,这样就会得不偿失
5)增大sort_buffer_size缓存的设置
6)增大max_length_for_sort_data参数的设置
7)where能解决的问题,就不要使用having了
8)查询时使用字段代替*来查询数据
(2)慢查询日志
1)开启慢日志查询,查询超过设置的时间,将SQL进行更改(默认不开启)
如果不调优的话,最好不要开启
(4)show profile
1)概念:show profile是MySQL提供的用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量(默认保存最近15次的运行结果)
2)功能:能够记录每条数据的运行时间及操作
(5)全局查询日志
配置全局查询日志:编写的所有的SQL语句都会记录在表中
永远不要在生产环境中开启这个功能
3、mysql锁机制
(1)概念:锁是计算机协调多个进程或线程并发访问某一资源的机制
(2)分类:
1)数据操作类型
读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响
写锁(排他锁):当前写操作哦没有完成前,他会阻断其他写锁和读锁
2)数据操作粒度
表锁
a:特点:偏向MyISAM存储引擎,开销小,加锁块,无死锁,锁定粒度大,发生冲突的概率不高,并发度低
b:对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求,
只用当读锁释放后,才会执行其他进程的写操作
c:对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写操作释放后,才会执行其他进程的读和写操作
d:读锁会阻塞写,但不会阻塞读,写锁,会阻塞读和写操作
行锁
a:偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁,锁定粒度最小,发生锁冲突的概率最低,并发度也最高
b:支持事务,采用了行级锁
c:事务:ACID
A:原子性:一个原子的操作做单元,其对数据的修改,要么全部执行,要么全部不执行
C:一致性:
I:隔离性:
D:持久性:
d:问题
更新数据:两个程序员对同一个java文件进行修改,最后提交的是最终数据
脏读:事务A读取了事务B未提交的数据,不符合事务的一致性
不可重复读:事务A读取了事务B已提交的数据,不符合事务的隔离性
幻读:事务A读取了事务B提交了的数据
*****************************
* 脏读是事务A修改了数据 *
* 幻读是事务A新增了数据 *
*****************************
e:优化建议
(1)尽量让所有数据检索都通过锁定来完成,避免无索引行锁升级为表锁
(2)合理设计索引,尽量缩小多的范围
(3)尽可能减少检索,避免间隙锁
(4)尽量控制事务大小,减少锁定资源量和时间长度
(5)尽可能低级别事务隔离
页锁:开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度结余表锁和行锁之间,并发度一般
4、主从复制(提高数据的高可用)
1、收到爆炸,整顿SQL
2、开启慢查询日志,设置阈值,抓取执行慢的SQL
3、Explain开始分析(应该会找到问题所在)
4、Show profile:存sql在mysql服务器里面的执行细节和生命周期(比explain更加细粒度,基本95%的问题就解决了)
5、运维经理 or DBA,进行SQL数据库服务器的参数调优