Interview title: MySQL Database not seen 9

Summary of some MySQL database surface questionsJune 16, 2016 11:41:18Hits: 4950

One, what security should SQL statements consider?

(1) Prevent SQL injection, escape from special characters, filter or use precompiled SQL statements to bind variables.
(2) The principle of least privilege, especially do not use the root account, for different types of actions or the formation of different accounts.
(3) When SQL error occurs, do not display the error information returned by the database to the user, to prevent the leakage of server and database related information.

Second, a simple description of MySQL, index, primary key, unique index, the difference between the Federated index, the performance of the database has any impact.

(1) An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet.
(2) The only task for a normal index (an index defined by the keyword key or index) is to speed up access to the data.
(3) Ordinary indexes allow indexed data columns to contain duplicate values, and if you can determine that a data column contains only values that are different from each other, you should use the keyword UNIQE to define it as a unique index when you create an index for that data index. Therefore, the unique indexes guarantee the uniqueness of the data records.
(4) Primary key, a special unique index, can only define a primary key index in a table, gradually used to uniquely identify a record, is created with the keyword primary key.
(5) An index can overwrite multiple data columns, such as index indexes, which are federated indexes.
(6) The index can greatly improve the query speed of the data, but it will reduce the speed of inserting and deleting the update table, because when performing these writes, the index file is also manipulated.

Three or one tables, there is an ID from the primary key, when the insert 17 records, delete the 15,16,17 record, and then restart the MySQL, and then insert a record, the record ID is 18 or 15?

(1) If the type of the table is MyISAM, then it is 18.
Because the MyISAM table will record the maximum ID of the self-increment primary key into the data file, the maximum ID of the restart MySQL self-increment primary key will not be lost.
(2) If the type of the table is InnoDB, then it is 15.
The InnoDB table simply logs the maximum ID of the self-increment primary key into memory, so restarting the database or optimize the table will cause the maximum ID to be lost.

Iv. describe the ways in which you optimize the efficiency of SQL statement execution in your project. How is SQL statement performance analyzed?

(1) Try to select a smaller column
(2) Index the more frequent fields in where
(3) Avoid using ' * ' in the SELECT clause
(4) Avoid the use of calculations on indexed columns, not,in and <> operations
(5) Use limit 1 when only one row of data is needed
(6) To ensure that the form data does not exceed 200w, timely segmentation of the table
(7) For a slow query, you can use explain to analyze the specific execution of the statement

V. Differences between mysql_fetch_row () and mysql_fetch_array ()

Both functions return an array, the difference is that the first function returns an array that contains only the values, we can only row[0],row[1], so that the array subscript to read the data, and Mysql_fetch_array () The returned array contains both the first and the form of a key-value pair, We can read the data in this way (if the database field is USERNAME,PASSWD): row[' username '],row[' passwd '.

Interview title: MySQL Database not seen 9