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数据库服务器的参数调优