MySQL优化
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进行更改(默认不开启) 2) 如果不调优的话,最好不要开启 (3)show profile 1)概念:show profile是MySQL提供的用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量(默认保存最近15次的运行结果) 2)功能:能够记录每条数据的运行时间及操作 (4)全局查询日志 配置全局查询日志:编写的所有的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数据库服务器的参数调优
做自己的太阳,成为别人的光!