13. 知道什么是左前缀原则嘛 ?
在mysql建立联合索引时会遵循左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,组合索引的第一个字段必须出现在查询组句中,这个索引才会被用到 ;
例如 : create index index_age_name_sex on tb_user(age,name,sex);
上述SQL语句对 age
,name
和sex
建一个组合索引index_age_name_sex
,实际上这条语句相当于建立了(age) , (age,name) , (age,name,sex)
三个索引 .
select * from tb_user where age = 49 ; -- 使用索引
select * from tb_user where age = 49 and name = 'Alice' ; -- 使用索引
select * from tb_user where age = 49 and name = 'Alice' and sex = 'man'; -- 使用索引
select * from tb_user where age = 49 and sex = 'man'; -- 使用索引 , 但是只有 age 匹配索引 sex没有走索引
select * from tb_user where name = 'Alice' and age = 49 and sex = 'man' ; -- 使用索引 , 因为MySQL的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引
select * from tb_user where name = 'Alice' and sex = 'man' ; -- 不会使用索引
14. 什么情况下索引会失效 ?
MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度,编写合理化的SQL能够提高SQL的执行效率
-
在列上使用函数和进行运算会导致索引失效
-
使用 != 或 not in或 <> 等否定操作符会导致索引失效
-
尽量避免使用 or 来连接条件
or关键词二边 , 只要有一个条见不满足索引, 就会全表扫描
-
多个单列索引并不是最佳选择,建立组合索引代替多个单列索引, 可以避免回表查询
-
使用 > , < 等比较运算符号 , 比较运算符后面的条件索引会失效
-
当查询条件左右两侧类型不匹配的时候会发生隐式转换,隐式转换带来的影响就是可能导致索引失效而进行全表扫描。
-
like 语句的索引失效问题 like 的方式进行查询,在 like “value%” 可以使用索引,但是对于 like “%value%” 这样的方式,执行全表查询
-
数据库在执行的过程中, 如果判断执行索引的效率还没有全表扫描的效率高, 也会走全表扫描
15. 索引是越多越好嘛? 什么样的字段需要建索引, 什么样的字段不需要 ?
需要创建索引情况
-
主键自动建立主键索引
-
频繁作为查询条件的字段应该创建索引
-
多表关联查询中,关联字段应该创建索引 (on 两边都要创建索引)
-
查询中排序的字段,应该创建索引
-
频繁查找字段 , 应该创建索引
-
查询中统计或者分组字段,应该创建索引
不要创建索引情况
-
表记录太少
-
经常进⾏行行增删改操作的表
-
频繁更新的字段
-
where条件里使用频率不高的字段
-
区分度不高的字段
16. mysql的性能优化
-
从设计方面 选择合适的存储引擎 , 合适的字段类型 , 遵循范式(反范式设计)
-
存储引擎 : 不需要事务, 不需要外键读写较多的的使用MyIsam 需要事务, 需要外键的使用InnoDB
-
合适的字段类型 , 例如 : 定长字符串用char , 不定长用varchr 状态, 性别等有限数量值的用tinyint
-
遵循范式 : 第一范式1NF,原子性 第二范式2NF,消除部分依赖 第三范式3NF,消除传递依赖
-
2.从功能方面可以对索引优化,采用缓存缓解数据库压力,分库分表。
3.从架构方面可以采用主从复制,读写分离,负载均衡
17. MYSQL超大分页怎么处理 ?
MYSQL 不是跳过offset行, 而是取offset+N行, 然后放弃前offset行 , 返回N行, 所以当offset比较法的情况下分页效率很低
正确的处理方法是 : 先快速定位需要获取的id再关联查询获取数据
18. 如何定位慢查询 ?
可以在MYSQL配置文件中开启慢查询 , 有两种方式可以开启慢查询
方式一 : 修改my.ini
配置文件 , 重启 MySQL 生效
[mysqld]
log_output='FILE,TABLE'
slow_query_log='ON'
long_query_time=0.001
方式二 : 设置全局变量
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL long_query_time = 0.001;
19. 一个SQL语句执行很慢, 如何分析
首先可以开启慢查询, 通过慢查询日志或者命令, 获取到执行慢的SQL语句 , 其次可以使用EXLPAIN
命令分析SQL语句的执行过程
EXLPAIN命令, 比较重要的字段(加黑加粗的是重要的) :
select_type重点解读
type重点解读:查询性能从上到下依次是最好到最差
extra重点解读
20. Mysql锁和分库分表
MYSQL锁按照锁的粒度分,分为以下三类:
-
全局锁:锁定数据库中的所有表。
全局锁就是对整个数据库实例加锁,加锁后整个实例就处于只读状态
-
表级锁:每次操作锁住整张表。
表级锁,每次操作锁住整张表。锁定粒度大,发生锁冲突的概率最高,并发度最低 , 对于表级锁,主要分为以下三类:
-
表锁 :
-
表共享读锁 lock tables 表名 read/write
-
表排他写锁 lock tables 表名 write
-
-
元数据锁 :
-
元数据锁加锁过程是系统自动控制,无需显式使用,在访问一张表的时候会自动加上 , 主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作
-
-
意向锁 : 为一条数据加行锁的情况下, 同时获取表的意向锁 , 其他事物再来获取表锁 , 可以方便的判断是否可以获取锁 , 使用意向锁来减少表锁的检查。
-
意向共享锁(IS): 由语句 select ... lock in share mode添加 。 与 表锁共享锁(read)兼容,与表锁排他锁(write)互斥。
-
意向排他锁(IX): 由insert、update、delete、select...for update添加 。与表锁共享锁(read)及排他锁(write)都互斥,意向锁之间不会互斥。
-
-
-
行级锁:每次操作锁住对应的行数据。
行级锁,每次操作锁住对应的行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高 , 行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁。
-
行锁(Record Lock):锁定单个行记录的锁,防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
-
间隙锁(Gap Lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持
-
临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。在RR隔离级别下支持。
-