Mysql的一些常见题

请写出数据类型(int char varchar datetime text)的意思；请问 varchar 和 char有什么区别？
Int 整数char 定长字符 Varchar 变长字符 Datetime 日期时间型Text 文本型 Varchar与char的区别 char是固定长度的字符类型，分配多少空间，就占用多长空间。Varchar是可变长度的字符类型，内容有多大就占用多大的空间，能有效节省空间。由于varchar类型是可变的，所以在数据长度改变的时，服务器要进行额外的操作，所以效率比char类型低。
MyISAM和 InnoDB 的基本区别？索引结构如何实现？
A、MyISAM类型不支持事务，表锁，易产生碎片，要经常优化，读写速度较快，适合用于频繁查询的应用；
B、InnoDB类型支持事务，行锁，有崩溃恢复能力，读写速度比MyISAM慢，适合于插入和更新操作比较多的应用，空间占用大，不支持全文索引等。创建索引：alert table tablename add index 索引名 (字段名)
什么是事务？及其特性？
答：事务：是一系列的数据库操作，是数据库应用的基本逻辑单位。
事务特性：A、原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。B、一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态C、隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，D、持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。
或者这样理解：事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。
什么是锁？
答：数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。
基本锁类型：锁包括行级锁和表级锁
索引的作用？和它的优点缺点是什么？
索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。
怎么防止sql注入？
1）过滤掉一些常见的数据库操作关键字：select,insert,update,delete,and,*等，或者通过系统函数：addslashes(需要被过滤的内容)来进行过滤。
2）在PHP配置文件中
Register_globals=off;设置为关闭状态 //作用将注册全局变量关闭。
比如：接收POST表单的值使用$_POST[‘user’],如果将register_globals=on;直接使用$user可以接收表单的值。
3）SQL语句书写的时候尽量不要省略小引号(tab键上面那个)和单引号
4）提高数据库命名技巧，对于一些重要的字段根据程序的特点命名，取不易被猜到的
5）对于常用的方法加以封装，避免直接暴漏SQL语句
6）开启PHP安全模式
Safe_mode=on;
7）打开magic_quotes_gpc来防止SQL注入
Magic_quotes_gpc=off;默认是关闭的，它打开后将自动把用户提交的sql语句的查询进行转换，把’转为'，这对防止sql注入有重大作用。
因此开启：magic_quotes_gpc=on;
8）控制错误信息
关闭错误提示信息，将错误信息写到系统日志。
9）使用mysqli或pdo预处理。
数据库优化策略？
1. 合理的表设计。
  1. 依据三范式，设计表.
  三范式：1.原子性，每个字段都是不可在分的。
  1. 在1方式的基础上，表中每一列必须有唯一性，其他字段依赖主键。
  2. 在2方式的基础上，表中的每一列只与主键直接相关，而不是间接相关。
  3. 选择合适的字段。 I.尽量使用TYPEINT、SMALLINT、MEDIUM_INT代替INT的使用，一般索引，并且是字段递增，可以考虑设置为UNSIGNED.
  II.使用枚举代替字符串类型。
  III.将少null的使用，null很难优化，并且还占用额外的空间。
  iv.varchar长度分配给真正需要的空间。
```
 v.建立合适的索引。
```
  1. 选择合适的引擎。
2. sql优化
  1. 减少*的使用，只查询需要的字段。
  2. 使用关联查询，代替子查询。
  3. like使用后匹配。
  4. 合理使用索引。
  5. 减少对null字段的判断、否则引擎放弃索引，对全表进行扫描。
  6. 减少！=，<>的使用。
  7. 减少where 条件中使用or来连接条件 select id from t where num=10 or Name = ‘admin’ #可以这样查询： select id from t where num = 10 union select id from t where Name = ‘admin’
3. 减少数据库访问的次数。
  1.将不经常变化的数据，进行缓存（分类、权限等），可以使用redis和mememcha,我个人建议，不要使用文件缓存，它也是对iO进行操作。
4. 硬件方面。
  1.可以考虑分库、分表。
  2.可以采用主从复制，读写分离.（mysql服务器根据sql，去判断是读还是写
更多
Mysql 主从复制原理和数据一致性见 Mysql主从复制原理及保证数据一致性
MySQL 索引失效的常见情况有哪些？
1. 最左前缀原则违反
2. 对索引列使用函数或表达式
3. 使用 OR 条件（OR 条件涉及不同索引、OR 条件中有一列无索引）
4. LIKE 查询以通配符开头（如’%John%、’%John’）
5. 数据类型不匹配（如 ‘1’ 用1）
6. 使用 NOT、!=、<> 操作符
7. 范围查询后的列失效（如复合索引department, salary, name 查询salary > 5000 AND name = ‘John’，这里name无法使用索引）
8. IS NULL 和 IS NOT NULL
9. IN 子查询
10. 表连接条件数据类型不匹配
11. 索引列参与计算
12. 其他

主键索引和复合索引的区别？

主键索引: 唯一且非空，每张表只能有一个，自动创建聚簇索引（InnoDB）

-- 数据按主键顺序物理存储
-- 叶子节点包含完整数据行

-- 物理存储示意：
-- 索引节点        -> 叶子节点（完整数据）
-- [id:1]         -> [id:1, name:'张三', email:'zhang@example.com']
-- [id:2]         -> [id:2, name:'李四', email:'li@example.com']
-- [id:3]         -> [id:3, name:'王五', email:'wang@example.com']

复合索引: 可以不唯一，一张表可以有多个，是非聚簇索引（二级索引）

-- 叶子节点只包含索引列 + 主键值
-- 需要回表查询完整数据

-- 复合索引: (user_id, product_id)
-- 物理存储示意：
-- 索引节点                    -> 叶子节点
-- [user_id:100, product_id:1] -> [主键值: order_id:1]
-- [user_id:100, product_id:2] -> [主键值: order_id:2]
-- [user_id:101, product_id:1] -> [主键值: order_id:3]

持续更新

Mysql的一些常见题#

Mysql的一些常见题