MySQL基本知识点梳理和查询优化

一、 Cardinality（索引基数）

索引基数是MySQL索引的很重要的概念，索引基数是数据列所包含的不同值的数量，列如，某个数据列包含值1,2,3,4,5,1，那么它的索引基数就是5。索引基数相对于数据表行数较高（也就是说列中包含很多不同的值，重复值很少）的时候，它的工作效果就最好；如果某列数据含有很多不同的年龄，索引会很快的分辨数据行，如果某列数据用于记录性别（只有M和F两种值）那么这样的话，索引的用处将不大。如果值出现的几率几乎相等，那么无论搜索那个值都可能得到一般的数据行。在这些情况下，最好不要使用索引，因为查询优化器发现某个值出现在表中数据行把粉笔很高的时候，他一般会忽略索引，进行全表扫描。管用的百分比界限是30%。

二、 索引失效的原因总结

1. 对索引列运算，运算包含（+、-、*、/、！、<>、%、like’%_’(%放在前面)）
2. 类型错误，如字段类型类varchar，where条件用number
3. 对索引应用内部函数，这种情况应建立基于函数的索引
   Eg：select * from temptable t where ROUND(t.login_id) = 1;这个时候应将 ROUND(t.login_id)建立为索引。
4. 如果条件使用or，即使有条件带索引也不会使用（如果使用or又想索引有效，那么需要每个条件加上索引）
5. 如果列类型是字符串，那么一定要在条件中数据加上引号，否则不使用索引
6. B-tree索引is null 不会走，is not null会走；位图索引 is null和is not null 都会走
7. 组合索引遵循最左规则

三、 sql语句总结

1.如果有主键或唯一主键冲突则不插入：insert ignore into
2.如果有主建或者唯一主键冲突则更新，注意这个会影响自增的增量
Insert into temptable(id,remarks) value(1,”sd”) on duplicate key update remarks = “sf”
3. 如果有就用新的代替，values如果不包含自增列，自增列的值会变化
Replace into temptable(id,remarks)value(1,”sd”)
4. 备份表 create table userInfo select * from temptable
5. 复制表结构：create table userInfo like temptable
6. 从查询语句中导入：insert into userInfo select * from tmptable、insert into（ID，name）select ID，name from temptable
7. 联表更新：update user a,room b set a.name =’wer’ where a.id = b.id
8. 联表删除：delete user from user，balck where user.id = balck.id
锁相关

1. 共享锁
   Select id from test where id = 1 lock in share mode
2. 排他锁
   Select id from test where id =1 for update
   优化：
3. 强制使用索引
   Select * from tble force index(user_id) limit2;
4. 禁止使用索引
   Select * from table ignore index（user_id）limit 2
5. 禁止用缓存
   Select SQL_NO_CACHE from table limit 2

四、 Sql编写的注意事项

1、 where语句的解析顺序是从右到左，尽量使用where不使用having
2、 采用延迟关联技术（deferred join）优化超多分页场景，延迟关联可以避免回表
3、 Distinct 语句非常损耗性能，可以通过group by来优化
4、 联表尽量不要超过三个

五、 避免踩坑

1. 如果有自增列，truncate语句会将自增列的基数重置为0
2. 聚合函数会自动滤空，列如a列的类型是int且全部为null，sum（a）返回的是null不是0

六、SQL高效查询建议

1. 尽量不使用null当默认值
   在有索引的列上如果存在null值，那么索引将失效，降低查询速度，优化方法就是将null值设成0或者其他的默认值，列如
   select * from table where price is null 改成 select * from table where price =0这样查询效率会快很多。
2. 尽量不在where条件中使用!=或<>，在where条件中使用!=和<>会使索引失效进行全表扫描。
3. 尽量where条件使用or，遇到or可以使用union all来改写，如
   select * from table where num =0 or num = 1
   改写成
   select * from table where num =0
   union all
   select * from table where num = 1
4. in和not in 要慎用，遇到连续确切的时候可以使用 between and来优化，列如
   select * from table where num in（5,6,7,8)
   改成
   select * fron tablename where num between 5 and 8
5. 子查询的in可以使用exists来代替，列如
   select * from testtable where order_id in(select order_id from order where pricr =20);
   select * from testtable as a where exists (select 1 from order as b where
   a.order_id = b.order_id and b.price = 20）
6. 模糊查询尽量使用前缀匹配，这样会走索引，减少查询时间，列如
   select * from T1 where name like’%王五%’
   select * from T1 where name like’%王‘
   这样都不会走索引，只有当下面这样才会走索引
   select * from T1 where name like '王%’

七、 慢查询日志

如果线上请求超时，应该去关注一下慢查询日志，慢查询日志分析很简单，先找到慢查询日志的位置，然后利用mysqldumpslow分析，查询慢查询日志可以直接通过sql命令
–slow_query_log 慢查询日志是否开启
–slow_query_log_file 值记录慢查询日志到文件
–long_query_time 慢查询的阈值
–long_queries_not_using_indexes是否记录所有没利用索引的查询
SHOW VARIAVLES LIKE ‘%query%’ 查看慢查询是日志还是表形式
SHOW VARIABLES LIKE ‘log_output’ 查看慢查询数量

八、 查看sql进程和杀死进程

Show processlist –查看进程
Kill 19823 –杀死指定进程