mysql优化

一：存储引擎

答：常用的主要分为两种，一种是innodb,一种是myisam,两者的主要区别是

①myisam不支持事务处理，而innoDB支持事务处理

②myisam 不支持外键，innoDB支持外键

③myisam支持全文检索，而innoDB在MySQL5.6版本之后才支持全文检索

④数据的存储形式不一样，mysiam表存放在三个文件：结构、索引、数据，innoDB存储把结构存储为一个文件，索引和数据存储为一个文件

⑤myisam在查询和增加数据性能更优于innoDB，innoDB在批量删除方面性能较高。

⑥myisam支持表锁，而innoDB支持行锁

二：字段类型

（1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
（2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
（3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
（4）尽可能使用not null定义字段
（5）尽量少用text，非用不可最好分表

 

举例：（1）价钱：decimal

(2) 手机号：char(10)

(3) 

三：索引

答：索引主要有：

      主键索引：数据记录里面不能有null,数据内容不能重复，在一张表里面不能有多个主键索引。

      普通索引：使用字段关键字建立的索引，主要是提高查询速度

      唯一索引：字段数据是唯一的，数据内容里面能否为null,在一张表里面，是可以添加多个唯一索引。

      全文索引：在比较老的版本中，只有myisam引擎支持全文索引，在innodb5.6后引擎也支持全文索引，在mysql中全文索引不支持中文。我们一般使用sphinx集合coreseek来实现中文的全文索引。

      复合索引：最左原则

 

四：分区分表

分区

list :条件值为一个数据列表。

Range（范围） 

这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分

数据量打的时候便于查询

分表

(1) 纵向分表  数据字段较多，把常用的字段和不长用的字段分开

(2) 横向分表  和分区一样

五：主从复制

 

原理：主从复制一共有三个进程，从库生成两个线程，一个I/O线程，一个SQL线程；

i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；

主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；

SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致;

 

 

 

 

 

 

 

 

 

 

一.sql语句优化

☆选择合适的数据类型

（2）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
（2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
（3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
（4）尽可能使用not null定义字段
（5）尽量少用text，非用不可最好分表

 ☆ 加索引

注意

  1.对于创建的多列(复合)索引，只要查询条件使用了最左边的列，索引一般就会被使用。 （最左原则）；

 2.对于使用like的查询，查询如果是”%aaa”,不会使用到索引，‘aaa%’会使用到索引。

 3.搜索中文 如：根据电影的剧情查找电影的名称，根据歌词查找歌名。like ‘%爱请%’

使用全文索引来解决，可以使用第三方查询工具，sphinx

4. 如果条件中有or，则要求or的索引字段都必须有索引，否则不能用到索引。

5. 如果列类型是字符串，一定要在条件中将数据使用引号引用起来，否则不使用索引。

6. 优化group by语句

   默认情况下， mysql对所有的group by col1,col2进行排序。这与在查询中指定order by col1,col2类型，如果查询中包括group by 但用户想要避免排序结果的消耗，则可以使用order by null禁止排序。

7. 当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描。

8.前缀索引

利用字段数据的前部分作为索引，称为前缀索引。减少索引长度，提高索引效率。

比如：统计密码的前7个字符，作为不相同匹配条件，几乎可以做到1:1

此时，就可以利用前7个字符做索引关键字即可（离散程度高）

 

语法：

alter table 表名 add index (passwd(7)) 指定前7位作为索引关键字。

不使用索引前缀，索引的长度。

9.使用limit对查询结果的记录进行限定
10.避免select *，将需要查找的字段列出来
11.使用连接（join）来代替子查询
12.拆分大的delete或insert语句

二．分页优化

 翻页的sql语句：

select * from table_name   limit  offset，length

使用如上语句，在翻页时，翻到最后，越来越慢，

原因：并不是跨过offset行，取出n条，

是取出offset+N条数据，舍弃前面的offset行，只取出n条数据。

如何解决？

（1）从业务上去解决：

办法：不允许翻过100页，

以百度为例，一般翻页到70页左右，谷歌40页左右

（2）不用offset，用条件查询，条件中使用id查询，使用到了索引，

select * from user limit 10000,10;

select * from user where id>10000 limit 10;     

下一页：select * from user where id>10000+10 limit 10

该种方式要注意：如果有数据被删除，会导致select * from user limit 10000,10;

和select * from user where id>10000 limit 10;语句取出的结果不一样，。

 因为数据在磁盘中被删除掉了（物理删除），结构发生改变，两者的查询的结果不一致，

 要想使结果一致，不能进行物理删除，给表增加一个字段来区分是否删除。

3）假如不能使用限制翻页到100页，数据有删除，还要求翻页，速度不能受影响。

思路：通过翻页，先取出id（主键），在根据id取出数据。

select name,age,email from  user inner join (select id from user limit 10000,10) as tmp on tmp.id=user.id

非要物理删除，还要用offset精确查询，还不限制用户分页，怎么办

我们现在必须要查，则只查索引，不查数据，得到id 

再用id去查具体条目，这种技巧就是延迟索引。 

三．分区.分表

四．主从复制、读写分离

14：优化需要用到哪些中间件？

主从复制：

主库挂掉：MHA

原理：

（1）从宕机崩溃的master保存二进制日志事件（binlog events）;

（2）识别含有最新更新的slave；

（3）应用差异的中继日志（relay log）到其他的slave；

（4）应用从master保存的二进制日志事件（binlog events）；

（5）提升一个slave为新的master；

（6）使其他的slave连接新的master进行复制；

一主多从如何读数据：atlas

负载均衡一台服务器挂掉如何切换：keepalived