一、 Cardinality（索引基数）

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

二、索引失效的原因总结

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

三、 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
锁相关

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

四、 Sql编写的注意事项

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

五、避免踩坑

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

六、SQL高效查询建议

尽量不使用null当默认值
在有索引的列上如果存在null值，那么索引将失效，降低查询速度，优化方法就是将null值设成0或者其他的默认值，列如
select * from table where price is null 改成 select * from table where price =0这样查询效率会快很多。
尽量不在where条件中使用!=或<>，在where条件中使用!=和<>会使索引失效进行全表扫描。
尽量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
in和not in 要慎用，遇到连续确切的时候可以使用 between and来优化，列如
select * from table where num in（5,6,7,8)
改成
select * fron tablename where num between 5 and 8
子查询的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）
模糊查询尽量使用前缀匹配，这样会走索引，减少查询时间，列如
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’ 查看慢查询数量