MySQL相关

---

1.定位慢查询

开启MySQL慢日志
在MySQL配置文件(/etc/my.cnf)配置信息

# 开启MySQL慢日志查询开关
slow_query_log=1 # 0为未开启
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

慢日志文件记录的信息存放在/var/lib/mysql/localhost-slow.log

2.分析慢查询

采用EXPLAIN命令

EXPLAIN 原SQL命令

字段	含义
possible_keys	可能使用到的索引
key	实际用到的索引
key_len	索引占用的大小
Extra	优化建议
type	sql的性能

可以通过key和key_len判断SQL语句是否失效
type性能由好到差为：NULL、system、const、eq_ref、ref、range、index、all
system：根据系统中的表
const：根据主键查询
eq_ref：主键索引查询或唯一索引查询 # 只能返回一条数据
ref：索引查询 # 可以返回多条数据
range：范围查询
index：索引树查询
all：全表查询

3.索引

索引是帮助MySQL高效获取数据的数据结构
提高数据检索的效率，降低数据库的IO成本（不需要全盘扫描）
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU消耗

MySQL的InnoDB采用B+树来存储索引

1.聚集索引（聚簇索引）

将数据存储和索引放到了一块，索引结构的叶子节点保存了数据
选取规则：
如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
如果表没有主键或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

2.非聚集索引（非聚簇索引、二级索引）

将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键

3.回表查询

通过二级索引找到相应的主键，到聚集索引中查找整行数据，这个过程就是回表

select * from user where id = 1
select id,name from user where name = '张三'
select id,name,gender from user where name = '张三'

这三条SQL语句中前面两条都不是回表查询第三条是回表查询
第一条因为id是聚集索引，索引和数据存放在一起可以直接查询到所有数据
第二条查询id和name，name作为二级索引可以通过name查询到对应的主键id，包含了所需要查询的id和name所以不是回表查询
第三条查询id、name和gender，name作为二级索引可以通过name查询到对应的主键id但不包含gender字段需要通过主键id再次查询gender此时就是回表查询

4.覆盖查询

覆盖索引是值查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到

select * from user where id = 1
select id,name from user where name = '张三'
select id,name,gender from user where name = '张三'

这三条SQL语句前面两条就是用到了覆盖索引
简单来说没有回表查询就是用到了覆盖索引

MySQL超大分页

mysql> select * from tb_sku limit 0,10
10 rows in set (0.00 sec)
mysql> select * from tb_sku limit 9000000,10
10 rows in set (11.05 sec)

在数据量大时越往后分页查询效率越低可以通过覆盖索引和子查询的方式解决

select * from tb_sku t,
	(select id from tb_sku order by id limit 9000000,10) a
where t.id = a.id

5.索引创建原则

1.针对于数据量较大，且查询比较频繁的表建立索引（单表超过10万数据）
2.针对与常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
4.如果时字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
6.要控制索引的数量，索引并不是越多与好，索引越多，维护索引结构的代价越大，会影响增删改的效率
7.如果索引列不能存储NULL值，在创建表的时候使用NOT NULL约束，当优化器知道每列是否包含NULL值时，可以更好的确定那个索引更有效的用于查询

6.索引失效

1.违反最左前缀法则
2.范围查询右边的列，不能使用索引
3.在索引列上进行运算操作
4.字符串不加单引号
5.以%开头的Like模糊查询